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:
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?
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL