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


 


 


 


 


Comments

  • Hi CaptainBob,


     


    why don't you simply update your query with script ?


     


    for instance you "default" query is 



    select col1, col2 from tablex
    where (1=1)

    select your data set and go to the script sheet and type something like that in the beforeOpen event



    myString = "filtercol >= " + params["Variable1"].value;
    myString += " and filtercol <= " + params["Variable1"].value + " + 10";

    this.queryText = this.queryText.replace("1=1",myString);
    Guillaume L.
    Solution Consultant
    OpenText Reporting & Analytics
    http://www.opentext.com
  • 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.

  • 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.

Sign In or Register to comment.