Contributor

Windows cmd shell script for dynamically generating JDBC.Properties file (A.C. 1.4)

Use Case: 

Batch import automation scripts using a SQL query in Anaplan Connect (AC) 1.4 require a companion .properties file that contains a lot of information about the query (connect string, login credentials, SQL statement, parameters).  As a best practice, you should avoid hardcoding usernames and passwords in plaintext or having redundant information that may change in multiple .properties files. A Windows shell script named createJDBC.properties is provided that dynamically creates a temporary jdbc.properties file on the fly from secure, centralized information.  

Disclaimer:

Any computer code in this post is provided "as-is", there is no guarantee of fitness for purpose. As the implementer, you are responsible for understanding it, modifying it to your situation, testing it, and taking precautions to avoid any damages that may result from use.  

 

Background:

Anaplan Connect 1.4 uses a .properties file when connecting to most relational databases such as Oracle, MySQL, and many more. (AC connections to MS SQL Server are the exception, those do not use a .properties file as of the time this was written.) The contents of the .properties file need to look something like this:

 

jdbc.connect.url= jdbc:oracle:thin:server:server.domain.com:1521:db_edw

jdbc.username=sysaccount

jdbc.password=sTr0ngP4ssW0rdHerE

jdbc.fetch.size=10

jdbc.isStoredProcedure=false

jdbc.query= Select Descr || ', ' || State || ': ' StoreNum FROM Stores WHERE Open_Date>(Select sysdate from Dual)

As you can see, there are Oracle login credentials and a SQL statement that may require periodic maintenance. A batch process for updating your data hub might involve a dozen queries, each stored in a separate .properties file.  Do you really want to hardcode this information in a dozen files? It would be better to generate the .properties file on-the-fly as your process requires it and to delete it immediately after use. 

Another nuisance issue:  For SQL queries to be maintainable they must be readable by people and readability requires line breaks.  Which code would you rather maintain:  

SELECT A.Descr || ', ' || A.State || ': ' A.StoreNum as Name, B.Region as Parent, A.StoreNum as Code FROM Stores A inner join StoreStatus B on A.StoreNum=B.StoreNum WHERE B.Open_Date<(Select sysdate from Dual)

or 

SELECT 
   A.Descr || ', ' || A.State || ': ' A.StoreNum as Name,
   B.Region as Parent,
   A.StoreNum as Code
FROM
   Stores A inner join Store_Info B on A.StoreNum=B.StoreNum
WHERE
   B.Open_Date<(Select sysdate from Dual)

Both are the same query, but the second one is much easier to understand and update.

You may be asking "Why not use Views in the relational database?" I agree, and I will generally advise anyone to store complicated SQL statements in their relational database as a view.  This allows you to have VERY simple query logic in your AC script:  SELECT * FROM <name of view>.  However, on a recent project the client felt it would be too cumbersome to work with their Oracle DBA to change the view definition whenever they needed minor changes to the query logic. Anaplan models can be quite flexible, they felt they needed the same flexibility to modify the data source for their import processes. For them, it would be better to keep control of their complex SQL queries in the business.

 

A Dynamic Alternative:

Here's a dynamic approach that solves several problems:

  1. Maintain connection and login information in a centralized encrypted master file (Globals.bat). This .bat sets temporary variables that are then used in later steps of the process. 
    The contents of Globals.bat look like this:
    SET jdbcConnection=<connect string to database>
    SET jdbcUsername=<database username>
    SET jdbcPassword=<database password>
    SET anaplanUser=<Anaplan username:password>
    For security, it is recommended that this file be encrypted.  Make sure the userid that will run your scheduled batch tasks has the ability / privileges needed to read the encrypted file.
  2. Maintain SQL scripts in another centralized master file (SQLQueries.txt).  The contents of SQLQueries.txt should look like this:
    ::<name of query 1>
    <SQL statement 1>
    ::end
    ::<name of query 2>
    <SQL statement 2>
    ::end

    So for our example SQL Statement above, SQLQueries.txt would include a section like the following.
    ::StoreHierarchy
    SELECT 
       A.Descr || ', ' || A.State || ': ' A.StoreNum as Name,

       B.Region as Parent,
       A.StoreNum as Code
    FROM
       Stores A inner join Store_Info B on A.StoreNum=B.StoreNum
    WHERE

       B.Open_Date<(Select sysdate from Dual)
    ::end
  3. CreateJDBCProperties.bat:  copy-paste this content to create this utility script.  The only modifications you'll need are the three lines that set the variables jdbcPropertiesFilePath, sqlQueriesFilePath, and LogFilePath.


    cls

    rem SETLOCAL EnableDelayedExpansion
    @echo off

    REM Set location of jdbc.properties file, sqlQueries.txt, and log file
    set jdbcPropertiesFilePath=c:\Anaplan\anaplan-connect-1.4\jdbc.properties
    set sqlQueriesFilePath=c:\Anaplan\anaplan-connect-1.4\scripts\sqlQueries.txt
    set LogFilePath=c:\Anaplan\anaplan-connect-1.4\ErrorLogs\jdbcproperties.log

    REM Test for missing required arguments, (1-8 are required.)
    REM If any are missing, show help message
    if "%~1"=="" set msg=Missing required argument(s^)
    if "%~2"=="" set msg=Missing required argument(s^)
    if "%~3"=="" set msg=Missing required argument(s^)
    if "%~4"=="" set msg=Missing required argument(s^)
    if "%msg%"=="" goto resume
    echo ************* %msg% **************
    echo USAGE: CreateJDBCProperties [dbuser] [dbpwd] [jdbcconnection] [sqlqueryname] [fetchsizemb] [sqlparams]
    echo.
    echo dbuser: Userid for relational database [required]
    echo dbpwd: Password for relational database [required]
    echo jdbcconnection: Connect string to database [required]
    echo sqlqueryname: Name identifying sql statement in sqlqueries.txt file. [required]
    echo dbfetchsize: databasefetchsize number of rows integer [optional]
    echo sqlargs: If sql statement uses tokens (?), values to be inserted [optional]
    echo.
    echo EXAMPLE:
    echo CreateJDBCProperties sysaccount syspwd123$  jdbc:oracle:thin:server.domain.com:1521:edw ^"Sales by Division and Period^" 8 ^"2019,Jan,'Div 12'^"
    echo.
    echo SQL Query must be found in sqlqueries.txt file
    echo Query length limited to ~8100 characters
    echo Query must be preceeded by ::<name of query> and must followed by ::end. Case sensitive.
    echo.
    echo Parameters for use in ? tokens in your SQL statement are optional. On the command line, enclose
    echo the complete parameter list in double quotes, put each parameter value with spaces in single quotes ''.
    echo.
    echo Example SQL query:
    echo ::Sales by Division and Period
    echo Select Product_ID, Customer_ID, Div_ID, Year, Period, Units, Amount
    echo From Tbl_Transactions
    echo where Year=? and
    echo Period in (?) and
    echo Div_ID in (?)
    echo ::end
    goto end
    :resume

    call :tee START
    REM Assign command line arguments 1-8 to variables.
    set dbuser=%~1
    set dbpwd=%~2
    set jdbcConnection=%~3
    set sqlqueryname=%~4
    if "%~5"=="" (
       set fetchsizemb=10
       ) else (
       set fetchsizemb=%~5
       )
    REM Get query parameters from arg6. (Extra code needed to handle comma separated list of params)
    ECHO %1 %2 %3 %4 %5 %6 >x&FOR %%? IN (x) DO SET /A strlength=%%~z? - 2&del x
    set queryparams=%~6

    REM if jdbc.properties exists, delete it
    if exist %jdbcPropertiesFilePath% (del %jdbcPropertiesFilePath%)
    REM Create new empty jdbc.properties file
    echo .> %jdbcPropertiesFilePath%

    REM Set User, password, fetch size
    set jdbcUser=jdbc.username=%dbuser%
    set jdbcPwd=jdbc.password=%dbpwd%
    set jdbcFetch=jdbc.fetch.size=%fetchsizemb%
    set jdbcIsStoProc=jdbc.isStoredProcedure=false

    REM **** Find named multiline query in sququeries.txt ************
    set queryfound=false
    REM Loop through file line by line, find named query, write to sqlstring
    setlocal EnableDelayedExpansion
    for /f "usebackq tokens=*" %%a in ("%sqlQueriesFilePath%") do (
    set myline=%%a
    if !myline!==::end set queryfound=false
    if !queryfound!==true set sqlstring=!sqlstring! %%a
    if !myline!==::%sqlqueryname% set queryfound=true
    )

    REM Add escape characters before < > | & and \ characters
    for %%B in (^< ^> ^| ^& ^\) do (
    set sqlstring=!sqlstring:%%B=^^^%%B!
    )
    rem set sqlstring=!sqlstring:"=""!

    set jdbcSQL=jdbc.query="%sqlstring%""
    set jdbcQryParams=jdbc.params="%queryparams%"

    REM ********* Create jdbc.properties file *************
    echo jdbc.connect.url= %jdbcConnection:"=%>%jdbcPropertiesFilePath%
    call :tee jdbc.connect.url= %jdbcConnection:"=%
    echo %jdbcUser%>>%jdbcPropertiesFilePath%
    call :tee %jdbcUser%
    echo %jdbcPwd%>>%jdbcPropertiesFilePath%
    echo jdbcFetch is %jdbcFetch%
    call :tee jdbc.pwd=************
    echo %jdbcFetch%>>%jdbcPropertiesFilePath%
    call :tee %jdbcFetch%
    echo %jdbcIsStoProc%>>%jdbcPropertiesFilePath%
    call :tee %jdbcStoProc%
    echo %jdbcSQL:"=%>>%jdbcPropertiesFilePath%
    call :tee %jdbcSQL%
    if defined %jdbcQryParams% (
    echo %jdbcQryParams%>>%jdbcPropertiesFilePath%
    call :tee %jdbcQryParams%
    )

    :end
    call :tee END
    exit /B 0


    :: a function to write to a log file and write to stdout
    :tee
    @echo off
    For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
    For /f "tokens=1-2 delims=/:" %%a in ('time /t') do (set mytime=%%a%%b)
    echo %mydate%_%mytime% : %* >> %LogFilePath%
    ECHO %*
    EXIT /B 0


  4. Put it all together: each Anaplan Connect script will
    a) Set some local variables,
    b) Call Globals.bat
    c) Call CreateJDBCProperties.bat,
    d) Set the OPERATION string,
    e) Pass all the necessary parts to AnaplanClient.bat. 
    f) Delete the jdbc.properties file

    The contents of a typical Anaplan Connect script would look like this:   

    REM set workspace ID, model ID, and SQL query name
    SET workspaceId=<Anaplan workspace ID>

    SET modelId=<Anaplan model ID>
    SET anaplanFileName=<name of file to replace in Anaplan>

    SET anaplanActionType=<process|import|export>
    SET anaplanImportAction=<name of action (process, import, export) to run in Anaplan>
    SET rejectLogFolder=<path to folder for rejected records logging>
    SET processLogFile=<full path to file where AC 'chatter' will be logged>
    SET sqlQueryName=<name of query in SQLQueries.txt>
    REM *** End of settings - Do not edit below this line ***

    CALL ./Globals.bat
    CALL ./CreateJDBCProperties.bat %jdbcUsername% %jdbcPassword% %jdbcConnection% %sqlQueryName% 

    set Operation=-service "https://api.anaplan.com" -auth "https://auth.anaplan.com" -file %anaplanFileName% -chunksize 5 -jdbcproperties%jdbcPropertiesFilePath% -%anaplanActionType% %anaplanAction% -execute -output %rejectLogFolder%

    setlocal enableextensions enabledelayedexpansion || exit /b 1

    cd %~dp0

    if not %anaplanUser% == "" set Credentials=-user %anaplanUser%

    set Command=.\AnaplanClient.bat %Credentials% -workspace %workspaceId% -model %modelId% %Operation% .> %processLogFile%
    @echo %Command%
    cmd /c %Command%
    set errorCode=%ERRORLEVEL%
    del %jdbcPropertiesFilePath%
    exit /b %errorCode%