Dear reader, I writing this post because every time I need to programmatically set a variable with quotes around it (Like a date or a member name with a space) I have to look it up to remember how to do it. So this post is more for me to have a place to look it up. You get the benefit of me posting this to remind me.
A couple of things to know.
- The code you see was tested on Essbase 21c, but should work on other version
- For this to work, the variable must already exist.
- This sample is for a system variable, the code is not much different for a application of database level variable , you just need to change the alter System statement to an Alter Application or Alter Database statement and put in the application and/or the application.database name.
- This sample is passing today's date to the variable specifically in the format "mm/dd/yyyy" including the quotes
- The key to this is the statement set CurDate="'\"%mm%/%dd%/%yyyy%\"'" I kow it is hard to read, so I'll make the right hand side easy Double quote single quote backslash double quote the date in the format I want %mm%/%dd%/%yyyy% backslash double quote single quote double quote. The beginning and ending double quotes are to enclose the variable when it is passed to MaxL. They will get stripped off. The single quotes are to enclose the variable within MaxL. the backslash is an escape character to make sure the double quote following it is passed to within the statement. In MaxL it looks like '"12/19/2022"' (Note that is single quote double quote at the beginning and double quote single quote at the end)
- This batch file was stolen for something bigger and has a little extra stuff in it
- I did not put error checking in the bat file Call StartMaxl or in the MaxL file itself
So here is the simple batch file
@ECHO OFF
REM =============================================================================
REM
REM Batch Name: Test.bat
REM Created By: Glenn Schwartzberg, Argano
REM Creation Date: 19-Dec-2022
REM Purpose:
REM update substitution variable
REM
REM =============================================================================
REM =============================================================================
REM GLOBAL VARIABLE SETTING
REM =============================================================================
%~d0
set SCRIPTNAME=%~n0
CALL :GetDate
echo %CurDate%
REM Unless the path to the MaxL is in the system environment variables change to the MAxL directory
cd C:\Oracle\19cMaxL
REM you might need to use essmsh instead Also set the path to where the MaxL file exists
startmaxl.bat C:\clientfiles\XXXXX\Automation\Set_today.mxl %CurDate%
goto :eof
:GetDate
set yyyy=
set $tok=1-3
for /f "tokens=1 delims=.:/-, " %%u in ('date /t') do set $d1=%%u
if "%$d1:~0,1%" GTR "9" set $tok=2-4
for /f "tokens=%$tok% delims=.:/-, " %%u in ('date /t') do (
for /f "skip=1 tokens=2-4 delims=/-,()." %%x in ('echo.^|date') do (
set %%x=%%u
set %%y=%%v
set %%z=%%w
set $d1=
set $tok=))
if "%yyyy%"=="" set yyyy=%yy%
if /I %yyyy% LSS 100 set /A yyyy=2000 + 1%yyyy% - 100
set CurDate="'\"%mm%/%dd%/%yyyy%\"'"
goto :eof
The MaxL file
Login 'USERID' 'PASSWORD' on S`ERVER;
Spool on to C:/XXXXX/Automation/set_Date.log;
Alter System Set variable TodayDate $1;
Exit
Posts
