Start a new topic

error handling in dbviscmd.bat command in windows

 Hi,

I have a batch file with series of call to dbviscmd like this

call "dbviscmd.bat" - connection "XYZ" sqlfile "1.sql" -output result

call "dbviscmd.bat" - connection "XYZ" sqlfile "2.sql" -output result

call "dbviscmd.bat" - connection "XYZ" sqlfile "3.sql" -output result


All the sql create csv file with @export on and @export off. On ideal case 1.sql,2.sql,3.sql creates 3 file. Sometime 2.sql file is not created because the table was not there. Now it is 3 so easily can be found out. I have 100 sql which creates 100 file if it miss something i am able to find which one, manually need to check everything.


Is there a way to check if dbviscmd.bat completed successfully or sql had an error. Can we error out into another text file in batch command something like 2>>log.txt. anything? Thanks


You can use the return code of DbVisualizer. DbVisualizer does return a return code that is not 0 on errors. 


Here is an example:


   call dbviscmd.bat -connection H2 -sql "select * from IDONOTEXIST"

   echo ERRORLEVEL  %ERRORLEVEL%

   IF %ERRORLEVEL% NEQ 0 Echo An error was found

   IF %ERRORLEVEL% EQU 0 Echo No error found


I'm afraid there is no way of redirecting the errors to a separate file. 

I did try %ERRORLEVEL%  and it is always EQU 0 irrespective of the query whether the table exist or not


my sql script file  something like this

@export on;

@export set filename ="abc.csv";

select * from temp.table1;

@export off

Maybe it has something to do with the way you are calling the script. I just did the following:

  • Create a file testbat.bat with the following content

call dbviscmd.bat -connection H2 -sqlfile example.sql

echo ERRORLEVEL  %ERRORLEVEL%

IF %ERRORLEVEL% NEQ 0 Echo An error was found

IF %ERRORLEVEL% EQU 0 Echo No error found


  • The example.sql has the following content

@export on;

@export set filename ="abc.csv";

select * from table1;

@export off


When I run this I get the result according to the snapshot below. I see the following causes why this might not work

  • It has to do with the way you are calling the script. In this case please consult a colleague or similar as this is not the area of our expertise. 
  • You are not running the latest version of DbVisualizer. In that case could you try with the latest version?



 

Probably won't solve this problem but I noticed that teeing standard out to a file makes errorlevel be the result of the pipe/teeing being most probably 0 all the time. Hope other find this helpful.

Login or Signup to post a comment