DB2 Stored Procedure calling method required

[Former Member]

Hi All,

I have some doubts executing a Stored Procedure within AS400 - DB2. i am using the jt400 driver from IBM and JDBC Sender Adapter.

What should be the Connection String?

and what is the format of the Query Statement?

i have the following details

1) AS400 Server IP

2) Stored Procedure name

3) Library Name (in which the stored procedure resides)

the third question that i have is:

say the stored procedure returns a ResultSet which has EmpId, EmpName and EmpLocation, how should my Data Type look?

Are there any blogs that explain about this scenario?

Thank you,

Regards,

Balaji.M

[moorthy · Accepted solution]

Hi,

For connection details- this thread discussion will help u -

This blog may help u- this is calling stored procedure from XI -

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

For Data type - in sender side it should be a result set/rowser , i.e simple structure.

http://help.sap.com/saphelp_nw2004s/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm --last section

Hope this helps,

Regards,

moorthy

[Former Member]

I am looking at setting up a JDBC Sender Adapter to call a stored procedure in DB2.

The connection string i specified is

jdbc:as400://IP Address/qgpl (where qgpl is the library)

and the query string as

<b>EXECUTE ORDUPDTSP</b> (where ORDUPDTSP is the stored procedure name)

i get an exception for this which is:

Error: SQLException during query 'EXECUTE ORDUPDTSP': java.sql.SQLException: [SQL0084] SQL statement not allowed.:

and i tried <b>EXECUTE qgpl.ORDUPDTSP</b>

the exeception is

java.sql.SQLException: [SQL0104] Token . was not valid.

i tried <b>CALL ORDUPDTSP</b>

and i get this exception:

Error: SQLException during query 'CALL ORDUPDTSP': java.sql.SQLException: Cursor state not valid.:

What could be the problem? i believe the calling of the stored procedure is wrong?

Any suggestions?

Regards,

Balaji.M

[Former Member]

Hi,

That was helpful. But i am looking at setting up a JDBC Sender Adapter to call a stored procedure in DB2. and i think the connection string should be jdbc:as400://IP Address/qgpl (where qgpl is the library)

but should the query string be

EXECUTE <procedure name> ?

or something else?

the main question i had was

<b>say the stored procedure returns a ResultSet which has EmpId, EmpName and EmpLocation,

how should my Data Type look?</b>

should it be somthing like this

<DB2data_DT> (Data type Name)

<row> (or should it be <statement> or <resultset>)

<EmpID>

<EmpName>

<EmpLocation>

Thank you,

Regards,

Balaji.M

[moorthy]

Hi,

The data type looks ok..

Query string should be EXECUTE <proc name>

Hope this helps,

Regards,

Moorthy