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%