How to add a parameter to a SQL query?

Question

I would like to add a dynamic parameter in addition to parameters from Maximo inside SQL query.

Something like that :

select * 
from workorder a 
where params["where"] or a.parent = :Param

with params["where"] with wonum='1234' and :Param = '1234'

Is it possible with Birt to get wonum value and put it also to :Param ?

Or maybe another way ?

Thanks

See https://stackoverflow.com/questions/28070809/updating-data-set-query-after-parameter-input-in-birt for the principle of a solution. — hvb, Mar 20 '19 at 15:12
I tried to add log to "beforeOpen" to see my query before modify it as it is explained to the link but query is null. Is it normal ? — Frederic Cantenot, Mar 28 '19 at 14:37
this.queryText contains the SQL statement. Obviously you did something wrong. You should probably post your beforeOpen script... — hvb, Mar 29 '19 at 08:05

score 0 · Answer 1 · answered Mar 29 '19 at 08:45

open is like that (query is more complicated so I simplify it):

maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = new String();
sqlText = "select column1, column2 as woParent, etc... from workorder where  " + params["where"] + " or woParent=:param";

maximoDataSet.setQuery(sqlText);

beforeopen is like that(just to see the query) :

importPackage( Packages.java.io );
out = new PrintWriter( new FileWriter( "c:/birteaump.log", true ) );
out.println( "\nMy Query: " + this.queryText);
out.close();

I had some code to manipulate :param to replace it with wonum but this.queryText is null. I'm a newbie to birt report maybe I have to think differently to solve my problem.

Thanks

score 0 · Answer 2 · answered Apr 12 '19 at 09:23

I used Birt 3.7.1. I saw in a video that we can add a query in a dataset's dialog box. But with my report I only have "scripted data set" when I use the "new data set" button.

Is it possible that my query is null in "beforeopen" is in relation with that ?

If I create another sort of datasource, I will have acces to another sort of dataset ?

thanks

score 0 · Accepted Answer · answered Apr 15 '19 at 06:40

0

ok I solved my problem. I created a JDBC data source and I have access to a new sort of data set. I can put my query in this data set and I have access to "beforeopen" and my query is not null.

Thanks

answered Apr 15 '19 at 06:40

Frederic Cantenot

19
6

How to add a parameter to a SQL query?

3 Answers3