Windows BAT File - Problem Using SQL Variables in Scripts
a
anonymous
started a topic
almost 11 years ago
[This topic is migrated from our old forums. The original author name has been removed]
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.
Re: Windows BAT File - Problem Using SQL Variables in Scripts
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
a
anonymous
said
about 10 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Windows BAT File - Problem Using SQL Variables in Scripts
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.
Hans Bergsten
said
about 10 years ago
[This reply is migrated from our old forums.]
Re: Windows BAT File - Problem Using SQL Variables in Scripts
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
a
anonymous
said
about 10 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Windows BAT File - Problem Using SQL Variables in Scripts
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.
Hans Bergsten
said
over 10 years ago
[This reply is migrated from our old forums.]
Re: Windows BAT File - Problem Using SQL Variables in Scripts
Great, thanks for the update.
Best Regards,
Hans
a
anonymous
said
over 10 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Windows BAT File - Problem Using SQL Variables in Scripts
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%
Hans Bergsten
said
almost 11 years ago
[This reply is migrated from our old forums.]
Re: Windows BAT File - Problem Using SQL Variables in Scripts
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
anonymous