passing report parmeter

using ibm clear quest report designer
i want to link report paramter (data type string) with data set parameter
the following error occur

The following items have errors:
ReportDesign (id = 1):
- Cannot set the string value (1) to parameter 1
Error setting value for SQL parameter #1
Cannot set the string value (1) to parameter 1
Error setting value for SQL parameter #1 ( 1 time(s) )
detail : org.eclipse.birt.report.data.adapter.api.AdapterException: Cannot set the string value (1) to parameter 1
Error setting value for SQL parameter #1

can any one help me

Find more posts tagged with

Comments

mwilliams

Hi Mahmoud,

What is the type on the dataSet parameter?

esuku

I get the same error stated in this post, both the parameters are of the type String.

esuku

Error Message:

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.1.r22x_v20070925
Error Code:odaconsumer.CannotSetStringParameter
Error Message:Cannot set the string value (9500030) to parameter 1
Cannot set preparedStatement parameter string value.
SQL error #1: The index 1 is out of range.

SQL Query in Data Set:

select convert(varchar(10), cal.start_date, 101) as Start_date, count(distinct T2.entity_dbid) as record_count,
'Pending Verification' as new_state
from history T2,
daycal cal,
Defect T1

where T1.project_number = '"+params["PrjNum"]+"'
and T1.dbid <> 0 and
T2.entitydef_id = 16777224 and (cal.start_date >= {d '2009-03-01'}
and cal.start_date <= GetDate()) and T2.entity_dbid = T1.dbid and
T2.action_timestamp = (select max(S1.action_timestamp) from history S1 where
S1.action_timestamp < {fn timestampadd(SQL_TSI_DAY, 1, cal.start_date)} and
S1.entity_dbid = T2.entity_dbid and S1.entitydef_id = T2.entitydef_id and not S1.old_state = S1.new_state)
and T2.new_state in ('Rejected','Deferred','Fixed-Pending')
group by cal.start_date

UNION ALL select convert(varchar(10), cal.start_date, 101) as Start_date, count(distinct T2.entity_dbid) as record_count,

'Open' as new_state
from history T2,
daycal cal,
Defect T1

where T1.Project_Number= '"+params["PrjNum"]+"'
and T1.dbid <> 0 and T2.entity_dbid = T1.dbid and
T2.entitydef_id = 16777224 and (cal.start_date >= {d '2009-03-01'}
and cal.start_date <= GetDate()) and
T2.action_timestamp = (select max(S1.action_timestamp) from history S1 where
S1.action_timestamp < {fn timestampadd(SQL_TSI_DAY, 1, cal.start_date)} and
S1.entity_dbid = T2.entity_dbid and S1.entitydef_id = T2.entitydef_id and not S1.old_state = S1.new_state)
and T2.new_state in ('Assigned','Disputed','Blocked','Submitted')
group by cal.start_date

UNION ALL select convert(varchar(10), cal.start_date, 101) as Start_date, count(distinct T2.entity_dbid) as record_count,

T2.new_state as new_state
from history T2,
daycal cal,
Defect T1

where T1.Project_Number= '"+params["PrjNum"]+"'
and T1.dbid <> 0 and T2.entity_dbid = T1.dbid and
T2.entitydef_id = 16777224 and (cal.start_date >= {d '2009-03-01'}
and cal.start_date <= GetDate()) and
T2.action_timestamp = (select max(S1.action_timestamp) from history S1 where
S1.action_timestamp < {fn timestampadd(SQL_TSI_DAY, 1, cal.start_date)} and
S1.entity_dbid = T2.entity_dbid and S1.entitydef_id = T2.entitydef_id and not S1.old_state = S1.new_state)
and T2.new_state in ('Fixed-Closed','Rejected-Closed','No-Fix-Planned','Deferred-Approved')
group by cal.start_date, T2.new_state
order by cal.start_date

esuku

Nevermind, I was able to resolve the issue. The Data Set parameter has to be called param1, param2 and param3. I was renaming them and hence was running into issues.

mwilliams

Glad you found a solution!

filbert

<blockquote class='ipsBlockquote' data-author="esuku">Nevermind, I was able to resolve the issue. The Data Set parameter has to be called param1, param2 and param3. I was renaming them and hence was running into issues.</blockquote>
 
Sorry to jump into an old thread but I have the same issue. I have already written a report that uses 4 parameters - and I *have* renamed them, they are not called param1, etc. It runs fine. 
 
Now I am trying to write a second, almost identical report and, as soon as I add any data set parameters (whether or not they are linked to report parameters), I get the same error. ("the index 1 is out of range"). This is thrown when I try to preview results in the dataset editor. 
 
The parameter is integer and the default is 100 (I haven't even tried to link to the query SQL yet!). If I delete the first parameter, I get the same problem with the next (string) parameter. 
 
I am baffled - as far as I can see the details are the same (I have even looked into the XML and it looks the same). 
 
One works and one doesn't. The only major difference is that the working one queries an Oracle d/b and the non-working one is against MSSQL. But I can't believe that's the problem. 
 
Can anyone help? 
 
Rgrds 
 
Peter

mwilliams

Hi Peter,

Can you attach your report design? Also, are you able to recreate this issue with the sample database? Finally, what version of BIRT are you using?

filbert

Thanks for the response - I have resolved the problem. It seems that BIRT didn't like me using the same name for report and datasource parameters. I can understand that but the error message was the same as esuku had and didn't seem to be related to names at all.

However, now I go back and try to reproduce the problem I cant! Anyway, I changed the names and the report works. This might be linked to the issue that esuku had.

Rgrds

Peter

mwilliams

Peter,

That is odd because I ALWAYS name my report and dataSet parameters the same name. Hmmm...not sure what the deal was there. Glad it's working for you now.

filbert

<blockquote class='ipsBlockquote' data-author="mwilliams">Peter, 
 
That is odd because I ALWAYS name my report and dataSet parameters the same name. Hmmm...not sure what the deal was there. Glad it's working for you now.

</blockquote>
 
Yes - the original report that I wrote that was on Oracle has the names the same and runs fine. 
 
I initially managed to make it happen on the Classic Models d/b as well, so that implied I was getting something basic wrong. However, I have gone back and tried to reproduce the problem and failed, both on MSSQL and Classic Models - all very strange! 
 
The original report has another feature that I can't now get to work again, but I'll save that for another post. 
 
Rgrds 
 
Peter

gauglerb

I was receiving the following error:

org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter int value. SQL error #1:Invalid argument: parameter index 1 is out of range

However, there was not an issue with this parameter. There was another issue with my query, and for whatever reason the compiler or renderer just spit that message out.

I was doing a string replace on my environment using a report parameter (not to be confused with the datasource paramter)

select * from environment_variable.TABLE where col = ?

I had an incorrect value for environment_variable, but it gave me this exception complaining about the parameter for (?)

Just thought I would share, just in case others have trouble.