Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Support

Thread: How set a variable from user input to a sql command with dbviscmd?


Permlink Replies: 3 - Pages: 1 - Last Post: Mar 20, 2012 11:01 AM Last Post By: Twistleton
Twistleton

Posts: 8
Registered: 03/19/12
How set a variable from user input to a sql command with dbviscmd?
Posted: Mar 19, 2012 1:32 PM
   thread.click_reply Reply
Hi,

how can I set a variable from user input to a sql command with dbviscmd.

Suppose I have this batch file report.bat on a windows system:

dbviscmd -connection Oracle -sqlfile "@run C:\Users\twistleton\report.sql ${startDate||%1}$ ${startDate||%2}$"

For launch this batch file with a start date and an end date I use this command in a dos box:

report.bat "2012-03-01" "2012-03-15"

And now my problem: how can i get the value of the start date and the value of the end date in the sql command file report.sql:

@export on;
@export set settings="c:\Users\twistleton\report_settings.xml" filename="c:\Users\twistleton\report.csv";
@echo ${startDate||???????????}$;
@echo ${endDate||???????????}$;

SELECT a.id

, a.productName

, a.productPrices
FROM orders a
WHERE a.orderdate >= TO_DATE(${startDate}$,'dd.mm.yyyy')

AND a.orderdate <= TO_DATE(${endDate}$,'dd.mm.yyyy')

AND a.status = 40

Thanks in advance!

Pongo

Twistleton

Posts: 8
Registered: 03/19/12
Re: How set a variable from user input to a sql command with dbviscmd?
Posted: Mar 19, 2012 4:23 PM    global.in_response_to.tooltip in response to: Twistleton
   thread.click_reply Reply
I found a solution by myself:

dbviscmd -connection Oracle -sql "@echo ${startDate||%1}$; @echo ${endDate||%2}$; @run C:\Users\twistleton\report.sql;"

But how can I set the values on a linux system?

dbviscmd -connection Oracle -sql sql "@echo ${startDate||${1}}$; @echo ${endDate||${2}}$; @run C:\Users\twistleton\report.sql;"

===> bad substition

Thanks in advance!

Pongo
Hans Bergsten


Posts: 1,567
Registered: 10/11/06
Re: How set a variable from user input to a sql command with dbviscmd?
Posted: Mar 19, 2012 9:47 PM    global.in_response_to.tooltip in response to: Twistleton
   thread.click_reply Reply
Hi Twistleton,

You're on the rich track. For Linux/UNIX, the default DbVisualizer variable prefix and suffix interferes with the shell variables syntax. I suggest you change the DbVisualizer variable prefix and suffix in Tool Properties, in the Variables category, to e.g. "#{" and "}#". You can then write command like this:

dbviscmd -connection Oracle -sql sql "@echo $#startDate||${1}#$; @echo $#endDate||${2}#$; @run C:\Users\twistleton\report.sql;"

Please let us know if this works for you.

Best Regards,
Hans

Twistleton

Posts: 8
Registered: 03/19/12
Re: How set a variable from user input to a sql command with dbviscmd?
Posted: Mar 20, 2012 9:54 AM    global.in_response_to.tooltip in response to: Hans Bergsten
   thread.click_reply Reply
Hi Hans,

I changed the variable prefix and suffix in Tool Properties and now the script works very well.

Here the command for launch the sql script

dbviscmd.sh -connection Oracle -sql "@echo #{startDate||$startDate}#; @echo #{endDate||$endDate}#; @run report.sql;"

Thank you very much.

Cheers,
Pongo

Edited by: Twistleton on 2012-mar-20 10:56

Point your RSS reader here for a feed of the latest messages in all forums