Eclipse Community Forums: BIRT » Custom SQL as a parameter

Help

Home

Home » Eclipse Projects » BIRT » Custom SQL as a parameter(User can write custom sql where condition in parameter)

Show: Today's Messages :: Show Polls :: Message Navigator

Custom SQL as a parameter [message #1007138]

Mon, 04 February 2013 10:26

Tomas Greif
Messages: 52
Registered: September 2010

Member

Hi,

I would like to create a parameter linked to dataset parameters. This parameter should be used in WHERE clause of SQL statement. In other words I would like to create the following (example) dataset:

select * from table where ?

Where ? is dataset parameter linked to report parameter where default value for report parameter is 1=1

If I use such approach then ? in SQL is replaced correctly (I guess) by parameter value (I have set default value for parameter). However the error I get back is:

Failed to prepare the query execution for the data set: VintageData
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: ERROR: argument of WHERE must be type boolean, not type character varying

Is there a way how can I make this work?

We are using BIRT 2.6.1 and PostgreSQL 9.1

Report message to a moderator

Re: Custom SQL as a parameter [message #1007215 is a reply to message #1007138]

Mon, 04 February 2013 23:41

Michael Williams
Messages: 828
Registered: July 2009

Senior Member

You could instead use a query like, "select * from table". Then, in your beforeOpen script of your dataSet, you'd put something like:

this.queryText = this.queryText + " where " + params["whereParam"];

You'll need to make sure that the sql in the parameter is formatted correctly, somehow, or it will break.

Regards,

Michael Williams
BIRT Exchange
Michael's BIRT Blog

Report message to a moderator