Pass a Multi-value Parameter to BIRT’s SQL Data Set

Key words: BIRT  SQL  IN condition with multi-value parameter

 

Question

I created a SQL data set with a parameter in BIRT by connecting to MySQL database through JDBC:

select * from tag where tag_name in (?)

I passed in multiple comma-separated, string type, quoted values, like 'join','rank','group' to the query, but data isn’t displayed as expected thanks to JDBC’s SQL injection warning that disables this type of parameter value passing.

 

Answer

esProc JDBC supports passing a multi-value parameter to a data set. So the data set can be written in this way: =connect("raqdoc").query("select * from tag where tag_name in ("+arg1+")"). arg1 is the counterpart of your question mark. Now data is displayed:

undefined

The above query is an SPL script. It can be called in BIRT through esProc JDBC. The deployment of esProc JDBC is explained in How to Call an SPL Script in BIRT.

 

You can use an SPL script to handle more complicated computations and report layout for BIRT. More solutions to BIRT performance enhancement can be found in:

How to Write Simple & Powerful Script Data Sources for BIRT Reports

Report Building Hacks for BIRT

 

Want to have a try?

Download esProc