Naming parameters in BIRT and testing sql

CaptainBob
edited February 11, 2022 in Analytics #1
<p>I would like to use named parameters with Postgres jdbc queries in BIRT instead of sequentially numbered queries.</p>
<p> </p>
<p>Reasons:</p>
<p>1. Less chance of mixing the parameters up</p>
<p>2. Easier to test and debug parts of the the query in another tool e.g. SQL Workbench.</p>
<p> </p>
<p>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.</p>
<p> </p>
<p>select col1, col2 from tablex</p>
<p>where filtercol >= :variable1</p>
<p>   and filtercol <= :variable1 + 10</p>
<p> </p>
<p>But, with BIRT using the Postgres postgresql-9.0-801.jdbc3.jar jdbc drivers, this doesn't work.</p>
<p>With Postgres, you have to do the following.</p>
<p> </p>
<p>select col1, col2 from tablex</p>
<p>where filtercol >= ?::integer</p>
<p>   and filtercol <= ? + 10</p>
<p> </p>
<p>If you want to use a single parameter more than once in the query, you have to do this:</p>
<p>select col1, col2 from tablex</p>
<p>where filtercol >= ?::integer</p>
<p>   and filtercol <= $1 + 10</p>
<p> </p>
<p>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.</p>
<p>select col1, col2 from tablex</p>
<p>where filtercol >= $[variable1]::integer</p>
<p>   and filtercol <= $[variable1]::integer + 10</p>
<p> </p>
<p>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.</p>
<p> </p>
<p>With BIRT and Oracle, the query could be used as is.</p>
<p> </p>
<p>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?</p>
<p> </p>
<p>I've tried other SQL tools, e.g. SQLMaestro, and it uses yet another variable syntax.</p>
<p> </p>
<p>your help will be appreciated</p>
<p>Brian</p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>

Comments

  • <p>Hi CaptainBob,</p>
    <p> </p>
    <p>why don't you simply update your query with script ?</p>
    <p> </p>
    <p>for instance you "default" query is </p>
    <pre class="_prettyXprint _lang-sql">
    select col1, col2 from tablex
    where (1=1)</pre>
    <p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"><span style="color:rgb(34,34,34);font-family:'Helvetica Neue', Arial, Verdana, sans-serif;">select your data set and go to the script sheet and type something like that in the </span><strong>beforeOpen</strong><span style="color:rgb(34,34,34);font-family:'Helvetica Neue', Arial, Verdana, sans-serif;"> event</span></p>
    <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>
    Warning No formatter is installed for the format ipb
  • 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.