Using SQL to control a batch file

Whenever a developer starts the Foxhound build process, this exhortation appears:



Here's what it's saying: Go to this page in Foxhound and check to see if there are any run-time error messages that need to be dealt with:



Here's the code that produces the exhortation:

ECHO OFF
ECHO *************************************************************************
ECHO ***** 
ECHO ***** Have you looked at Foxhound Options - Display Exceptions? 
ECHO ***** 
ECHO ***** Before you rebuild your Foxhound database and wipe out what was in 
ECHO ***** the old copy, you might want to see if there were any errors you
ECHO ***** need to look at... or at least copy-and-paste into your to-do list.
ECHO ***** 
ECHO *************************************************************************
ECHO ON
PAUSE

Wouldn't it be easier for the developer if the build process checked to see if there actually ARE any exceptions?

Here's a Windows batch file that demonstrates how to use dbisql.com in batch mode, together with the ISQL EXIT statement, to do just that:

ECHO OFF

"%SQLANY12%\bin64\dbisql.com"^
  -c "ENG=foxhound1;DBN=f;UID=ADHOC;PWD=SQL"^
  -q^
  CREATE VARIABLE rc INT; SET rc = 0; IF EXISTS ( SELECT * FROM rroad_exception ) THEN SET rc = 999 ENDIF; EXIT rc; 

ECHO ERRORLEVEL = %ERRORLEVEL%

IF %ERRORLEVEL% == 999 ( ECHO rroad_exception is known to have rows ) ELSE ( ECHO rroad_exception is not known to have any rows )

PAUSE

The dbisql.com command on lines 3 through 6 does the following:

• it connects to the Foxhound database if it's running, using the ADHOC user id provided by Foxhound for (you guessed it) ad-hoc reporting,

• it specifies -q to suppress all display output except for error messages, and

• it runs the SQL statements on line 6 instead of executing a script via the ISQL READ statement. Another alternative to coding out all the statements is to CALL a stored procedure, but this dbisql.com command is standalone; i.e., it doesn't require a separate SQL script or stored procedure to be created ahead of time.

The SQL statements on line 6 are all squished onto a single line because dbisql.com can't cope with multiple lines of SQL on the command line... or at least, I haven't figured out how to code line breaks in the SQL statement section.

All the carp code on line 6 to CREATE and use a connection-level variable is necessary because EXIT is an ISQL statement, not a SQL statement, and it can't be coded inside an IF THEN ELSE control block. The IF THEN block is sent to the SQL Anywhere engine to be processed, and the engine would choke throw an error if it saw an EXIT statement.

The IF %ERRORLEVEL% == 999 command on line 10 displays "rroad_exception is known to have rows" if the EXIT rc returned 999. There are two other likely %ERRORLEVEL% values: 0 for "it's all good, there's nothing to look at" and 9 for "can't connect, and therefore the number of rows is unknown"... that's why the ELSE ECHO message is so vague.

Note: The Windows documentation of the IF command talks a bit about %ERRORLEVEL% versus ERRORLEVEL, but it doesn't actually show an example of how IF %ERRORLEVEL% is so much easier think about, code and read than the old-fashioned IF ERRORLEVEL... folks who code batch files for a living know what I'm talkin' about.

Here's what the batch file displays in all three cases, rc = 999, 0 and 9:







Here's the snippet of code that was added to the Foxhound build process:

"%SQLANY12%\bin64\dbisql.com"^
  -c "ENG=foxhound1;DBN=f;UID=ADHOC;PWD=SQL"^
  -q^
  CREATE VARIABLE rc INT; SET rc = 0; IF EXISTS ( SELECT * FROM rroad_exception ) THEN SET rc = 999 ENDIF; EXIT rc; 

IF %ERRORLEVEL% == 999 ( GOTO rroad_exception_is_known_to_have_rows ) ELSE ( GOTO rroad_exception_is_not_known_to_have_any_rows )

:rroad_exception_is_known_to_have_rows
ECHO OFF
ECHO *************************************************************************
ECHO ***** 
ECHO ***** The rroad_exception table is NOT EMPTY !!! 
ECHO ***** 
ECHO ***** Before you rebuild your Foxhound database and wipe out what was in 
ECHO ***** the old copy, you might want to see if there were any errors you
ECHO ***** need to look at... or at least copy-and-paste into your to-do list.
ECHO ***** 
ECHO *************************************************************************
ECHO ON
PAUSE

:rroad_exception_is_not_known_to_have_any_rows

Here's the new improved exhortation:



BTW, the new exhortation was added to the build process, it didn't replace the old one... you can never have too many exhortations when you're looking for bugs!

---