Naming parameters in BIRT and testing sql

I would like to use named parameters with Postgres jdbc queries in BIRT instead of sequentially numbered queries.
 
Reasons:
1. Less chance of mixing the parameters up
2. Easier to test and debug parts of the the query in another tool e.g. SQL Workbench.
 
With BIRT using the Oracle driver ojdbc14.jar allowed the use of named queries, such as this. Note the same variable is used once, and variable1 only has to be added once to the dataset parameter list.
 
select col1, col2 from tablex
where filtercol >= :variable1
 and filtercol <= :variable1 + 10
 
But, with BIRT using the Postgres postgresql-9.0-801.jdbc3.jar jdbc drivers, this doesn't work.
With Postgres, you have to do the following.
 
select col1, col2 from tablex
where filtercol >= ?::integer
 and filtercol <= ? + 10
 
If you want to use a single parameter more than once in the query, you have to do this:
select col1, col2 from tablex
where filtercol >= ?::integer
 and filtercol <= $1 + 10
 
This query does not work in other tools, such as SQL Workbench. If I want to test this or far more complex queries, it's not a matter of copying the BIRT query across. All variables have to be changed to the following.
select col1, col2 from tablex
where filtercol >= $[variable1]::integer
 and filtercol <= $[variable1]::integer + 10
 
To move the working query to BIRT, the parameter format has to be changed once more, with the possibility of more errors. This is a nuisance if the query is complex.
 
With BIRT and Oracle, the query could be used as is.
 
Is there a syntax I can use that will work on Postgres with both BIRT and SQL Workbench, or perhaps a JDBC driver you recommend?
 
I've tried other SQL tools, e.g. SQLMaestro, and it uses yet another variable syntax.
 
your help will be appreciated
Brian

Find more posts tagged with

Comments

GLO_FR

Hi CaptainBob,
 
why don't you simply update your query with script ?
 
for instance you "default" query is 
<pre class="_prettyXprint _lang-sql">
select col1, col2 from tablex
where (1=1)</pre>
select your data set and go to the script sheet and type something like that in the beforeOpen event
<pre class="_prettyXprint _lang-js">
myString = "filtercol >= " + params["Variable1"].value;
myString += " and filtercol <= " + params["Variable1"].value + " + 10";

this.queryText = this.queryText.replace("1=1",myString);</pre>

CaptainBob

Hi Guillaume

Thanks for the reply. I see this was long ago.

Unfortunately your solution doesn't help me.
1. run the query in a developer app, e.g. SQLWorkbench and test different parameters
2. I have to script replacement of many parameters, at times as many as 16.

SQLWorkbench supports named parameters, although it seems to be variable substitution in the application.
DBeaver with the same JDBC driver, supports named parameters, using the :variablename syntax.

What does BIRT do differently with the Postgres driver? The Oracle driver uses the :variablename syntax, Postgres not.

jroeterd

Not strictly what you are after for but you could also use a select as query to use the filters once and then reference in further queries with the correct name.
Something like this :

select prompt as
(
select filtercol1 from tablex where filtercol1 = ?
)
select col1, col2 from tablex
where filtercol >= (select filtercol1 from prompt)
and filtercol <= (select filtercol1 from prompt) + 10

I use this sometimes as I have reports using the parameter multiple times (up to 12 times). This makes it manageable.