Batch debugger not stepping correctly

Jun 19, 2019
2
0
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.

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
 
Dec 7, 2009
235
2
Left Coast, USA
I realize this reply doesn't address your question — I don't use the batch debugger myself so I'm no help on that account.

But, a question about your .cmd file: Do you plan to make batch files written in standard cmd.exe fashion so that they can be used, unaltered, in both cmd.exe and Take Command/TCC environments?
 

rconn

Administrator
Staff member
May 14, 2008
11,926
133
I don't see any issues debugging that file (with the limited amount I can do without access to the sites).

What is the exact erroneous sequence you're seeing? (Note that the debugger will treat FOR commands as a single line, and you cannot step into them. This is a Windows / CMD compatibility issue; if you want to step into a loop you need to use the DO command instead of FOR.)

If this batch file is intended only to run under CMD.EXE, did you set the CMD syntx option (Options / CMD Syntax)?
 
Jun 19, 2019
2
0
The CMD syntax option was set. Not being able to step into a FOR command limits the usefulness of this tool. Since the debugger cannot step into FOR commands I don't see the point of using it, for me.. Thanks for looking at this.