Error Passing Parameter to Dataset- Plz Help

userjava · 2009-04-13T13:13:41+00:00

Hi , We have a SQL and i am trying to convert to Dataset with 04/01/2009 and 04/23/2009 as parameters and BIRT is throwing errors any idea how to fix this? SQL <hr></hr> Select distinct(UIDS) from USER where DATE(CREATED) between DATE ('04/01/2009') and DATE ('04/23/2009'); Dataset…

userjava

Hi ,

We have a SQL and i am trying to convert to Dataset with 04/01/2009 and 04/23/2009 as parameters and BIRT is throwing errors any idea how to fix this?

SQL

Select distinct(UIDS) from USER
where DATE(CREATED) between DATE ('04/01/2009') and DATE ('04/23/2009');

Dataset

Select distinct(UIDS) from USER
where DATE(CREATED) between DATE (?) and DATE (?)

Any help is much much appreciated.

Thanks,
Us

Find more posts tagged with

Comments

bhanley

What error is being thrown? Have you bound the Data Set parameter to a Report-level parameter so that the query has a value to substitute?

userjava

Yes i did this is the error

A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.2.2.r22x_v20071212
Error Code:odaconsumer.CannotGetResultSetMetaData
Error Message:Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=', DRIVER=3.52.95
SQL error #2: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-206;42703;', DRIVER=3.52.95
SQL error #3: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-206;42703;', DRIVER=3.52.95

userjava

I also put the Dataset SQL in this way

date("'"+params["startRange"]+"'")

but no luck

Error i get is

Error Message:Cannot set the string value ({0}) to parameter 1
Cannot set preparedStatement parameter string value.
SQL error #1: [jcc][10145][10844][3.52.95] Invalid parameter 1: Parameter index is out of range. ERRORCODE=-4461, SQLSTATE=42815

bhanley

You should not need to do anything special to the value in the parameter. the issue below might be with your use of the DATE construct. 
 
I have a working sample for you (attached) that uses Classic Models data, so you should be able to run it. Here is how the query looks: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT *
FROM CLASSICMODELS.ORDERS
WHERE CLASSICMODELS.ORDERS.SHIPPEDDATE BETWEEN ? AND ?
ORDER BY CLASSICMODELS.ORDERS.SHIPPEDDATE</pre>
 
In the sample, also note the configuration on the Parameters group to see how each parameter is bound to the query. 
 
Hope this helps, Good Luck!

userjava

Thank you Very Much it works

I guess BIRT automatically adds the Date to the question mark.

bhanley

If you look closely at the report parameters on the sample, they are data typed to date in the design itself. Then again on the Data Set configuration, the parameter dinding confirms the type as a Date right there in the binding.