The batch debugger will not follow the code correctly. It skips to random lines and reaches the end prematurely. The code is attached.
The purpose of the code is to accept SQL statements and execute them on each database at the 21 CAL FIRE command centers. This code works very well, however I found a case that gave me incorrect results and I wanted to step through the code rather than put ECHO statements in. I downloaded the 30 day trial and it isn't working.
The purpose of the code is to accept SQL statements and execute them on each database at the 21 CAL FIRE command centers. This code works very well, however I found a case that gave me incorrect results and I wanted to step through the code rather than put ECHO statements in. I downloaded the 30 day trial and it isn't working.
Code:
@echo off
rem Execute an Oracle SQL statement on each of the listed units'
rem CAD database.
setlocal
setlocal EnableDelayedExpansion
set query_set=0
set spool=false
set byUnit=false
set nohead=false
set inpfile=none
set clean=false
set system=live
set pgsize=60
set linesize=80
set site_list=
set temp_file=TEMP_FILE.sql
set SQLOnly=0
set var=
set substring=
set substring2=
rem site password index, for specified sites.
set spindex=0
set all_sites_list=AEU BDU BEU BTU CZU FKU HUU LMU LNU MEU MMU MVU NEU
set all_sites_list=%all_sites_list% RRU SCU SHU SKU SLU TCU TGU TUU
set n=0
for %%p in (%all_sites_list%) do (
set sa[!n!]=%%p
set /A n=!n! + 1
)
rem site database password list <fake passwords for this post.>
set sp=pw1 pw2 pw3 pw4 pw5 pw6
set sp=%sp% pw7 pw8 pw9 pw10 pw11
set sp=%sp% pw12 pw13 pw14 pw15 pw16 pw17
set n=0
for %%p in (%sp%) do (
set sp[!n!]=%%p
set /A n=!n! + 1
)
set progfile=%0
goto command_line_parse
:cmd_line_done
if !query_set!==0 (
goto help
)
if "%site_list%"=="" (
goto help
)
:scriptloop
rem -----------------------------------------------------
rem set the initial SQL script options and
rem then loop through all specified ECCs generating
rem SQL for each one. Finally, call SQLPLUS to
rem execute the script.
rem -----------------------------------------------------
echo set pagesize !pgsize!; >%temp_file%
echo set linesize !linesize!; >>%temp_file%
echo set trimspool on; >>%temp_file%
echo set trimout on; >>%temp_file%
if %nohead%==true echo set heading off; >>%temp_file%
if %spool%==true (
set spoolfile=%sf%
if %byunit%==false (
echo spool !spoolfile!; >>%temp_file%
)
)
if %clean%==true (
echo set feedback off; >>%temp_file%
)
set n=0
for %%s in (!site_list!) do (
rem Call the sql construction subroutine with
rem the site identifier and the index number.
call :sqlscript_generation %%s !n!
if %clean%==true echo prompt end %%s >>%temp_file%
set /A n=!n!+1
)
if %spool%==true (
if %byunit%==false echo spool off; >>%temp_file%
)
echo exit >>%temp_file%
rem type %temp_file%
if NOT SQLOnly==0 sqlplus -s /nolog @%temp_file%
goto eof
:sqlscript_generation
rem ------------------------------------------------
rem sqlscript_generation(unit, pwd_num)
rem create the sql script using the site identifier
rem and the index. The index gives the password for
rem the site. The logon is created from the site
rem identifier and the password.
rem ------------------------------------------------
if %1==CIS (
echo connect aUser/aPasswordt@cis%system%; >>%temp_file%
) else (
call :setvarSITEPWD %2
rem echo !VAR!
echo connect aUser/!var!@cad%system%%1; >>%temp_file%
)
if %clean%==false (
echo prompt %1; >>%temp_file%
)
if %spool%==true (
if %byunit%==true (
echo spool %sfname%_%1.%sfext%; >>%temp_file%
)
)
if NOT "%qf%"=="" (
rem echo "%inpfile%"
if !inpfile!==none (
call :set_site site %1 "%qf%"
if defined substring (
rename rfile.txt rfile2.txt
call :set_site sub %substring% rfile2.txt
del rfile2.txt
)
if defined substring2 (
rename rfile.txt rfile2.txt
call :set_site sb2 %substring2% rfile2.txt
del rfile2.txt
)
copy %temp_file%+rfile.txt %temp_file% >nul
del rfile.txt
) else (
rem echo %qf% %inpfile%
call :set_queryString %qf% %1 !inpfile!
)
) else (
set rep2=%1
if !inpfile!==none (
rem replace subunit with unit designator.
set tmp_query=%query:site=!rep2!%
rem remove double quotes
set tq=!tmp_query:"=!
echo !tq:^^=!; >>%temp_file%
) else (
for /F "tokens=1*" %%c in (!inpfile!) do (
rem echo %%c %%d
set replacement=%%c
set tmp_query=%query:sub=!replacement!%
set tmp_query=!tmp_query:site=%1!
set replacement=%%d
set tmp_query=!tmp_query:sb2=%%d!
set tq=!tmp_query:"=!
echo !tq!; >>%temp_file%
)
)
)
echo disconnect; >>%temp_file%
if !byunit!==true (
if %spool%==true echo spool off >>%temp_file%
)
goto eof
:help
echo.
echo %progfile% /s (^all ^|^| ^<unit^> ) (/q ^|^| /qf ^<filename^>) ^<opts^>
echo.
echo Execute the Oracle SQL statement on
echo each of the listed units' CAD database.
echo.
echo options:
echo /s ^<site^> ................. Specify the site. Specify ALL if all sites
echo are desired. Only ECCs are included.
echo This option may be repeated with individual sites.
echo /q "query string" ......... Put an Oracle SQL statement in the quotes.
echo Use three ^^^ to enter special DOS characters.
echo For example: "ENTRYDATE ^^^> SYSDATE-1/24".
echo /qf ^<filename^> ............ The file contains the SQL statement^(s^) to
echo use.
echo /spool ^<filename^> ......... Put results in a file. The default is
echo SQLPLUS screen output.
echo /byunit ................... Put search results into separate files
echo named ^<spool filename^>_^<site^>. Must be
echo combined with the /spool option.
echo /noheader ................. do not display a header for query results.
echo /h or /? .................. This message.
echo /f ^<filename^> ............. Read "filename" for replacement strings. All
echo instances of "sub" and "sb2" in the query are
echo replaced with strings read from the file. "site"
echo is replaced with the site^(s^) specified.
echo /b "substitution string" .. Use when using a canned SQL statement ^(/qf^),
echo replaces all instances of "sub" with the specified
echo string.
echo /b2 "substitution string" . Use when using a canned SQL statement ^(/qf^),
echo replaces all instances of "sb2" with the specified
echo string.
echo /c ........................ Clean output - no extraneous text. Also sets
echo NoHeader and sets Page size to 0.
echo /line ^<size^> .............. Linesize. Sets the linesize for output.
echo Default is 80 characters.
echo /live ..................... Perform operations on the live system. ^<default^>
echo /test ..................... Perform operations on the test system.
echo /train .................... Perform operations on the train system.
echo substitutions:
echo The word "site" is replaced by the three-character site designator.
echo If using an input file the keywords sub and sb2 are replaced with
echo strings read from the input file.
echo.
goto eof
:command_line_parse
rem -----------------------------------------------------
rem Parse the command line for various options. As an
rem option is identified the strings are shifted out of
rem the command line.
rem
rem This routine operates on global variables and does not
rem execute like a subroutine - it is reached via GOTO.
rem -----------------------------------------------------
:cmd_line_loop
if "%1" == "" (
goto cmd_line_done
)
echo %1 %2
if /i "%1" == "/noheader" ( rem prevent sqlplus from generating a header.
set nohead=true
) else if /i "%1"== "/f" ( rem name of input file for substitutions.
set inpfile=%2
shift
) else if /i "%1" == "/spool" ( rem output file.
set spool=true
set sf=%2
set sfname=%~n2
set sfext=%~x2
shift
goto done_parsing
) else if /i "%1" == "/byunit" ( rem separate output by unit.
set byUnit=true
) else if /i "%1" == "/h" (
goto help
) else if "%1" == "/?" (
goto help
) else if /i "%1" == "/q" ( rem SQL query
set query=%2
set query_set=1
shift
goto done_parsing
) else if /i "%1" == "/qf" ( rem use query file
if NOT EXIST "%2" (
echo %2 does not exist.
goto help
)
set replacement=%2
set qf=!replacement:"=!
set query_set=1
shift
goto done_parsing
) else if /i "%1" == "/s" ( rem specifiy unit
call :set_sitelist %2
shift
goto done_parsing
) else if /i "%1" == "/line" (
set linesize=%2
shift
) else if /i "%1" == "/live" ( rem specify live system
set system=live
) else if /i "%1" == "/test" ( rem specify test system
set system=test
) else if /i "%1" == "/train" ( rem specify train system
set system=train
) else if /i "%1" == "/c" ( rem no extraneous output
set clean=true
set nohead=true
set pgsize=0
) else if /i "%1"=="/SQLOnly" (
set SQLOnly=1
) else if /i "%1"=="/b" (
set substring=%2
shift
) else if /i "%1"=="/b2" (
set substring2=%2
shift
) else (
echo unknown: "%1"
goto help
)
:done_parsing
shift
goto cmd_line_loop
:set_sitelist
rem -----------------------------------------------------
rem set_sitelist (site)
rem set the list of sites and passwords if the option is ALL,
rem or find the password for the given site name.
rem
rem site [IN] - three-letter site identifier.
rem site_pwd[] [OUT] - needed passwords for requested sites.
rem site_list [OUT] - list of requested sites.
rem -----------------------------------------------------
if /i %1==ALL (
rem all_sites_list contains all the site three-letter identifiers.
set site_list=%all_sites_list%
rem create a pseudo-array (variables named like an array)
set n=0
for %%p in (%sp%) do (
set site_pwd[!n!]=%%p
set /A n=!n!+1
)
) else (
rem concatenate the site identifier.
set site_list=%site_list% %1
set index=0
rem search for the matching password.
for %%p in (%sp%) do (
call :setvarSA !index!
if /i !var!==%1 (
call :setvarSP !index!
set site_pwd[!spindex!]=!var!
set /A spindex=!spindex!+1
goto eof
)
set /A index=!index!+1
)
rem if we get here there is no matching
rem site in the list. Check for ITS, RMA,
rem or CIS.
if /i %1==ITS (
set site_pwd[!spindex!]=invite28
set /A spindex=!spindex!+1
) else if /i %1==CIS (
set site_pwd[!spindex!]=cadint
set /A spindex=!spindex!+1
)
)
goto eof
:setvarSA
set var=!sa[%1]!
goto eof
:setvarSP
set var=!sp[%1]!
goto eof
:setvarSITEPWD
set var=!site_pwd[%1]!
goto eof
rem ------------------------------------------------
rem set_queryString(sql_file, unit, inpfile)
rem
rem read the given file and concatenate the separate
rem lines into one string that is enclosed by double
rem quotes. Replace the word "site" with the unit
rem designator. Replace sub and sb2 with values from
rem the inpfile.
rem ------------------------------------------------
:set_queryString
set sqlString="
for /F "tokens=1*" %%c in (%1) do (
rem echo %%c %%d
set sqlString=!sqlString! %%c %%d
)
set sqlString=!sqlString!"
for /F "tokens=1*" %%c in (!inpfile!) do (
set query=!sqlString:site=%2!
set replacement=%%c
set tmp_query=%query:sub=!replacement!%
set tmp_query=!tmp_query:site=%1!
set replacement=%%d
set tmp_query=!tmp_query:sb2=%%d!
set tq=!tmp_query:"=!
echo !tq!; >>%temp_file%
)
set query_set=1
goto eof
:set_site
rem ------------------------------------------------
rem set_site(Oldstr, Newstr, File, rfile)
rem source https://www.dostips.com
rem OldStr [in] - string to be replaced
rem NewStr [in] - string to replace with
rem File [in] - file to be parsed
rem rfile [out] - result file called rfile.txt
rem ------------------------------------------------
if "%~1"=="" findstr "^::" "%~f0"&GOTO:EOF
for /f "tokens=1,* delims=]" %%A in ('"type %3|find /n /v """') do (
set "line=%%B"
if defined line (
call set "line=echo.%%line:%~1=%~2%%"
for /f "delims=" %%X in ('"echo."%%line%%""') do %%~X >>rfile.txt
) ELSE echo.
)
goto eof
:eof