SupportSupport

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

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