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

There are no comments yet