BIRT Report Parameter Markers in FROM clause

Suppose I have a simple SQL query:

select * from wmwhse21.itrn where adddate = 20191111

I want the user to be able to choose the database upon which to run the query at run-time. I've tried putting

select * from aa where adddate = 20191111 and aa = ?

i.e. placing a parameter marker in the FROM clause, but after keying in wmwhse21.itrn as aa in the BIRT report web viewer I get the error:

org.eclipse.birt.report.engine.api.EngineException: Cannot get the result set metadata. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object. SQL error #1:ORA-00942: table or view does not exist ; java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

----

I understand that parameter markers are meant for WHERE clauses, but is it possible to use them in the FROM statement?

If not, how should I go about allowing users to key in the desired database to run the query against at run-time?

For reference, the database ID and database alias are different (e.g. "wmwhse21" is named Chicago Warehouse for instance, and you need to input the former to run the query), so I figure a choose-from-dropdown would work best for my users.

Find more posts tagged with

Comments

jfranken

If I understand your requirements, you should be changing the connection parameters dynamically instead of trying to change the query. Here is a discussion with an example:

https://forums.opentext.com/forums/support/discussion/comment/938496#Comment_938496

Bryan Leow

Hi Jeff,

The linked discussion has a similar issue, but in my case all the databases can be accessed via the same centralised data source url, so I don't need to dynamically change the data source connection - just the data set query specifying the database to use.

Luckily, I found the solution for my case - using the beforeOpen method to append to the parameter to my query, as suggested by Jerry here https://www.eclipse.org/forums/index.php?t=rview&goto=1824034#msg_1824034. This way, the full query is only generated after the user inputs the parameter values, which is what I need.

I still learnt something new from the discussion that you linked though, and had my databases exist in separate data sources it'd be what I was looking for. Thanks for the help!

jfranken

Thank you for the update. I'm glad you found an answer.