Windows BAT File - Problem Using SQL Variables in Scripts

Trying to create a Windows batch file to run a SQL statement multiple times (like the example on your site), but I cannot get it to work. I have created (3) files.... TABLES.txt (list of tables to run the query against) - ACRP1.COMPANY - ACRP1.BRANCH BATCH.sql (the SQL statement with a variable) - Select * from ${table} RUNBATCHSQL.bat FOR /F "tokens=1-5" %%G IN (tables.txt) DO dbviscmd.bat -connection ACRPROD -sql "@echo \${table||%%G}\$; @run Batch.sql" -outputfile %%G-log.txt When I run the batch file I get the following result: INFO: 16:06:57 Physical database connection acquired for: ACRPROD @echo \${table||ACRP1.COMPANY}\$; @run Batch.sql; INFO: 16:06:57 [@ECHO - 0 row(s), 0.000 secs] \${table||ACRP1.COMPANY}\$; @run Batch.sql SUMMARY: ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [1 successful, 0 warnings, 0 errors] The variable does not get replaced in my SQL statement.

Hi, Does it work if you remove the backslashes before the dollar signs? They are needed on Unix but may cause the problem on Windows. Best Regards, Hans

Working now... SQL FILE: Select * from ${variable}$ VARIABLE LIST FILE: ACRP1.COMPANY ACRP1.BRANCH SQLrun Batch File: @echo off ::CHECK USERNAME AND PASSWORD BEFORE RUNNING CALL Setups\USER.cmd SET CONN=ACRPROD SET SCHEMA=ACRP1 SET OUTFILE=log.txt SET LIST=2-Edit-This-File-VariableList.txt SET SQLFILE=1-Edit-This-File-SQL.txt SET DEBUG=Debug.txt FOR /F "tokens=1-5" %%G IN (%LIST%) DO Setups\dbviscmd.bat -connection %CONN% -userid %USER% -password %PASS% -sql "@echo ${variable||%%G}$; @run %SQLFILE%" -outputfile %%G-%OUTFILE%

Great, thanks for the update. Best Regards, Hans

Hi, I am trying to reproduce the same .bat that it is explained in this post, however, something is wrong. As my understanding, it seems that user.cmd contains the user and password. do you know if this content need to be separated by blanks? I need to use 3 parameters, so, the file that contain the parameters need to be separated by any character or could it be a blank? the command FOR, it contains /F and %%G. it remains as it is, or do I need to change to something else? the Setups\dbviscmd.bat needs to be in the same folder that the .bat and just dbviscmd.bat is necessary in this folder or do I need more files? tks for now.

Hi, > As my understanding, it seems that user.cmd contains the user and password. do you know if this content need to be separated by blanks? The user.cmd file is not shown in the example, but I assume it is a script that sets the %USER% and %PASS% variables. You can do that any way you want, or hardcode them in the dbviscmd.bat call, or pass them in as parameters to the main BAT file. > I need to use 3 parameters, so, the file that contain the parameters need to be separated by any character or could it be a blank? > the command FOR, it contains /F and %%G. it remains as it is, or do I need to change to something else? Not sure I understand, but this is a Windows BAT file question rather than a DbVisualizer question. I suggest you consult the Windows documentation. > the Setups\dbviscmd.bat needs to be in the same folder that the .bat and just dbviscmd.bat is necessary in this folder or do I need more files? The files involved can be in any folder as long as you refer to them with appropriate paths, e.g. absolute paths. > tks for now. Best Regards, Hans

Hi, I got to execute .bat after changing some information, now, as each parameter is generating a text file, I would like to know if there is a way to remove the header that the SQL reproduce in the sysout. tks.

Hi, The sysout format cannot be changed, but you can use the @export command in your script to write result sets to a file even with the command line interface: http://confluence.dbvis.com/display/UG91/Exporting+Query+Results Best Regards, Hans