Calling a stored procedure from a scripted data source

Waleed

Hi All,

Can any one tell me how to call a stored procedure from maximo dataset which is a scripted data source and return a value from the stored procedure?

ur help will be really appreciated and thnx in advance.

Regards,

ccwood

I'm in the same situation as Waleed. I have been tasked to produce a BIRT 2.3.2 report definition against Maximo 7.1 (DB2) data involving fairly complex calculations. I've been writing SQL for 18 years, but using Ingres/Sybase/MS SQL Server, and DB2 is a very different world.

I managed to write a stored procedure that takes one input parameter and has five output parameters. I need to run the stored procedure as a subquery; the main query selects rows, and then the subquery does the calculation for each row.

The relevant links google has found are:
<http://www.birt-exchange.org/org/forum/index.php/topic/18023-can-we-use-stored-procedure-dataset-in-birt-for-maximo/page__s__efe76d4518e4d88525d702e7777ba292>, I tried the recommended

select isResolutionGoalMet( 'IN5067', eventTime, goalTime, endtime, impact, goalMet) from dual

but DB2 doesn't know about dual:
Category Timestamp Duration Message Line Position
Error 10/2/2010 3:51:17 PM 0:00:00.625 DB2 Database Error: ERROR [42704] [IBM][DB2/AIX64] SQL0204N "MAXIMO.DUAL" is an undefined name. SQLSTATE=42704
1 0


<http://wiki.eclipse.org/BIRT/FAQ/Data_Access#Q:_How_do_I_use_a_stored_procedure.3F>, which reads:
Q: How do I use a stored procedure?
The JDBC ODA plugin included in BIRT currently supports SQL SELECT queries and simple stored procedure queries. That is, those that 1) use scalar input parameters only or no parameters, and 2) retrieve a single result set directly, like those in SQL Server or Sybase (instead of via a cursor output parameter like those in Oracle).

BIRT does support Input/Output Parameters from stored procedures:

{call testProcedure(?)}
If the parameter in this example is an output parameter, you could reference it in the expression builder like:

outputParams["param1"]

---

I'm not sure which version of BIRT this applies to, but I couldn't get the queries to work. I tried
call isResolutionGoalMet( 'IN5067', ?, ?, ?, ?, ?)

and

{call isResolutionGoalMet( 'IN5067', ?, ?, ?, ?, ?)}

and

call isResolutionGoalMet( 'IN5067', eventTime, goalTime, endtime, impact, goalMet)

and

{call isResolutionGoalMet( 'IN5067', eventTime, goalTime, endtime, impact, goalMet)}

and none of them worked. the BIRT logg shows the call statment, followed by a fetch failed and a long stack trace.