Anaplan Connect - Export via JDBC

u06gr8
u06gr8
in Integrations and extensibility

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!

 

 

Tagged:

Answers

  • ABerenguela
    ABerenguela

    Hi,

     

    you need to install Java: 

    ABerenguela_0-1581613589204.png

     

    Try echo %JAVA_HOME% in CMD to check if you have it:

    ABerenguela_1-1581613683365.png

     

     

    Regards

     

     

  • u06gr8
    u06gr8

    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

  • u06gr8
    u06gr8

    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 . . .

  • karank_*4578
    karank_*4578

    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 

  • ben_speight
    ben_speight

    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.

Categories