Anaplan Connect - Export via JDBC
Hi guys,
This is my first time really getting to grips with Anaplan Connect and I'm trying to PoC exporting out a simple table of "Region Name" & "Region Code" using Anaplan Connect via JDBC into a SQL table. My scripts are as follows:
---------.bat script-----------
@echo off
rem
rem
rem
set AnaplanUser="USER@user.com:PASSWORD"
set WorkspaceId="alphanumerical_workspace_ID"
set ModelId="alphanumerical_workspace_ID"
set ServiceUrl="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"
set ExportName="JDBC_Export"
set /path/to/JdbcProperties="C:\path\path2\FILE.properties"
set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -export %ExportName% -execute -jdbcproperties %/path/to/JdbcProperties%
rem *** End of settings - Do not edit below this line ***
setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
set Command=.\AnaplanClient.bat %Credentials% %Operation%
@echo %Command%
cmd /c %Command%
pause
-------------.properties file-----------------------
# JDBC Connection string (Oracle, Mysql, H2, etc.)
jdbc.connect.url= jdbc:sqlserver://SERVERADDRESS.database.windows.net:1433
# JDBC login username
jdbc.username= USERNAME
#JDBC login password
jdbc.password= password
# Use INSERT queries to export your data into database
# Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing
record, then SQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells SQL to
discard it silently without generating an error.
# Use the REPLACE command rather than the INSERT command. If the record is new, it is inserted just as with
INSERT. If it is a duplicate, the new record replaces the old one.
# SQL query example
jdbc.query=INSERT IGNORE INTO `RegionPoCTable` (`Region Name`,`Region Code`) Values (Region Name,Region Code);
# -----NEW PARAMETER-----
# JDBC parameters for parametrized SQL write in {jdbc.query} above.
# NOTE: The number of parameters must be equal to the number of parameters provided in the {jdbc.query} above.
# The parameter names must match column names from Anaplan export action.
# The JDBC parameters below will be mapped starting from left to right to the '?' in {jdbc.query} above.
# In this example, the exported data has columns names column1 and column2. These will be mapped
# starting from left to right to each of the '?' above.
jdbc.params=Region Name, Region Code
---------------Error Message-------------------
When I follow the Anaplan Connect guide (1.4.3) I get the below error:
C:\Users\gtric\Anaplan Connect\JDBC_POC.properties
'"alphanumerical_workspace_ID"' is not recognized as an internal or external command,
operable program or batch file.
.\AnaplanClient.bat -user "USER@user.com:PASSWORD" -debug -service "https://api.anaplan.com" -auth "https://auth.anaplan.com" -workspace
Absolute path to JAVA 8 executable not provided. Will attempt to figure out path from JAVA_HOME or Windows registry...
JAVA_HOME is not defined, will attempt to fetch value from registry...
Checking Windows Registry for Java Version...
ERROR: Cannot determine Java version.
Press any key to continue . . .
-------------------
Could someone give me an idea on how to progress this?
Thanks!
Answers
-
Hi,
you need to install Java:
Try echo %JAVA_HOME% in CMD to check if you have it:
Regards
0 -
Apologies - a managed to omit a key concept of my post...!!
I have Java Version 8 Update 241 (build 1.8.0_241-b07) installed.
Cheers
0 -
So I managed to set "JAVA_HOME" as the location in system properties, but I am now getting the following, which seems more cryptic:
C:\Users\gtric\Anaplan Connect\JDBC_POC.properties
'"alphanumerical_workspace_ID"' is not recognized as an internal or external command,
operable program or batch file.
.\AnaplanClient.bat -user "USER@user.com:PASSWORD" -debug -service "http://api.anaplan.com" -auth "http://auth.anaplan.com" -workspace
\Java\jdk1.8.0_241 was unexpected at this time.
Press any key to continue . . .0 -
This is worth trying
i am assuming your Java is installed at this location
C:\Program Files (x86)\Java\jdk1.8.0_241
if this is the case
Change your JAVA_HOME env variable to below
C:\PROGRA~1\Java\jdk1.8.0_241
Alternatively
install/copy java outside program files so something like
c:/java/....
Reason for this is windows uses short names for Program Files (x86)
Try hopefully it works
0 -
It looks like your editor has word-wrap switch on and it has split some lines in half - the Windows command interpreter will treat this as two separate commands. Reconfigure your editor (or use a different one) and reassemble the split lines. If you have edited and saved AnaplanClient.bat you will also need to fix that or recover the original version.
0
