Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Additional Stored Proc Question(Parameters and Stored Proc)
Additional Stored Proc Question [message #885249] Tue, 12 June 2012 17:21 Go to next message
Carlos Ferrer is currently offline Carlos Ferrer
Messages: 18
Registered: July 2009
Junior Member
I created a dataset with the main sql which will return a rowid.
SElECT rowid
from table

I then create a stored procedure dataset.
The next step is to pass that rowid to that stored procedure():
{call SAPSCH.XXXXXX(?,0,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}

How do I bind the 1st question mark of the stored proc to the rowid id obtained from the first sql. Have tried to look for documentation to walk me through but haven't ound samples that actually do it.

Thanks.
Re: Additional Stored Proc Question [message #885908 is a reply to message #885249] Wed, 13 June 2012 21:08 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Carlos

The best way to do this is to drag the data set that returns the rowid
onto your report canvas. In the detail row delete the rowid data
element and then drag your stored procedure data set to the detail row.
This will create an inner table. Select the table and in the
properties view select the binding tab and select the parameters button.
You should then be able to bind the first parameter to the outer
table's rowid. The following is an older example but show binding
internal talbes to outer table values.
http://www.eclipse.org/birt/phoenix/examples/reports/birt2.1/subreport/index.php

Jason

On 6/12/2012 1:21 PM, Carlos Ferrer wrote:
> I created a dataset with the main sql which will return a rowid.
> SElECT rowid
> from table
>
> I then create a stored procedure dataset.
> The next step is to pass that rowid to that stored procedure():
> {call SAPSCH.XXXXXX(?,0,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
>
> How do I bind the 1st question mark of the stored proc to the rowid id
> obtained from the first sql. Have tried to look for documentation to
> walk me through but haven't ound samples that actually do it.
>
> Thanks.
Re: Additional Stored Proc Question [message #886279 is a reply to message #885249] Thu, 14 June 2012 15:56 Go to previous messageGo to next message
Carlos Ferrer is currently offline Carlos Ferrer
Messages: 18
Registered: July 2009
Junior Member
Jason,

So I did as you told me.

I dragged my sql with the rowid to the report canvas. No problem encountered.
I then deleted the rowid item from the detail row.
I tried to drag the stored proc dataset to the detail row and it wouldn't let me. Got a circle with a line across it.
So what I did, was drag data from the palette to the column(rowid) I just deleted. Then I bound the column to the sql's rowid. I didn't get any issues so I clicked the preview results and got the following error message:
The following items have errors:

Data (id = 883):
+ Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:[IBM][CLI Driver][DB2] SQL0470N The user defined routine "1" (specific name "") has a null value for argument "" that could not be passed. SQLSTATE=39004

;
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0470N The user defined routine "1" (specific name "") has a null value for argument "" that could not be passed. SQLSTATE=39004
(Element ID:883)

Even though I bound it to the rowid id from the sql, it seems not to be finding it because the error message is saying it has a null value.

Thanks for your help.
Re: Additional Stored Proc Question [message #886336 is a reply to message #886279] Thu, 14 June 2012 18:51 Go to previous messageGo to next message
Carlos Ferrer is currently offline Carlos Ferrer
Messages: 18
Registered: July 2009
Junior Member
Looking at the created xml I see the following as being flagged:
<cell id="1182">
<data id="1213">
<property name="dataSet">GETLOCSPCall</property>
<list-property name="paramBindings">
<structure>
<property name="paramName">ROWKEY</property>
<simple-property-list name="expression">
<value type="javascript">row["XREF_OIMS_SID_NO"]</value>
</simple-property-list>
</structure>
</list-property>
<list-property name="boundDataColumns">
<structure>
<property name="name">UNITOFASGN</property>
<expression name="expression" type="javascript">outputParams["UNITOFASGN"]</expression>
<property name="dataType">string</property>
</structure>
</list-property>
<property name="resultSetColumn">UNITOFASGN</property>
</data>
</cell>
Re: Additional Stored Proc Question [message #886343 is a reply to message #886336] Thu, 14 June 2012 18:59 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Does the stored procedure preview in the data set editor?

Jason

On 6/14/2012 2:51 PM, Carlos Ferrer wrote:
> Looking at the created xml I see the following as being flagged:
> <cell id="1182">
> <data id="1213">
> <property name="dataSet">GETLOCSPCall</property>
> <list-property name="paramBindings">
> <structure>
> <property name="paramName">ROWKEY</property>
> <simple-property-list name="expression">
> <value type="javascript">row["XREF_OIMS_SID_NO"]</value>
> </simple-property-list>
> </structure>
> </list-property>
> <list-property name="boundDataColumns">
> <structure>
> <property name="name">UNITOFASGN</property>
> <expression name="expression"
> type="javascript">outputParams["UNITOFASGN"]</expression>
> <property name="dataType">string</property>
> </structure>
> </list-property>
> <property name="resultSetColumn">UNITOFASGN</property>
> </data>
> </cell>
Re: Additional Stored Proc Question [message #886358 is a reply to message #886343] Thu, 14 June 2012 19:39 Go to previous messageGo to next message
Carlos Ferrer is currently offline Carlos Ferrer
Messages: 18
Registered: July 2009
Junior Member
Jason
If I put a hardcoded input it works. Once I put the ? in lieu of the hard coded input value the same error I reported is thrown on the screen.
Re: Additional Stored Proc Question [message #886366 is a reply to message #886358] Thu, 14 June 2012 19:57 Go to previous messageGo to next message
Carlos Ferrer is currently offline Carlos Ferrer
Messages: 18
Registered: July 2009
Junior Member
User error on my part. Had the input parm in the wrong order. Thanks for all your help.
Re: Additional Stored Proc Question [message #886370 is a reply to message #886366] Thu, 14 June 2012 19:58 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Glad to read. Thanks for the update.

Jason

On 6/14/2012 3:57 PM, Carlos Ferrer wrote:
> User error on my part. Had the input parm in the wrong order. Thanks for
> all your help.
Previous Topic:BIRT features
Next Topic:rpt document generated with large size (900 MB)
Goto Forum:
  


Current Time: Sat Nov 01 10:31:29 GMT 2014

Powered by FUDForum. Page generated in 0.13908 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software