Is it possible to execute a stored procedure using the JDBC adapter?

[Former Member]

Hi all,

Can anybody confirm whether we can execute a stored procedure created in a database using the processing parameters of a JDBC adapter of a communication channel?

If yes, then please let me know how (may be with an example)

Thanks.

[Former Member · Accepted solution]

This is the procedure to be followed if one has to call a stored procedure from the mapping in the IR..

what Amit is asking is a way to do the same from the "processing parameters" section in the "processing tab" in the "reciever JDBC communication channel" in the ID..

[Former Member · Accepted solution]

Barath and Amit,

Suggest you both do spend a bit more time in searching SDN before posting a new question. These topics have been discussed a zillion times. Just search SDN using the terms JDBC adapter Stored procedure.

[Former Member]

Yes, that is possible. You have to chose EXECUTE in the action field to execute the stored procedure. Here's a little piece on the JDBC receiver:

JDBC Receiver

For writing data to an SQL database you also need to define a strict data type. The general format is like this:

<ns:MT_RECORDSET>

<STATEMENT>

<TABLE_NAME ACTION="">

<TABLE/>

<ACCESS>

<FIELD1/>

<FIELD2/>

<FIELDn/>

</ACCESS>

<KEY>

<ID compareOperation=""/>

<FIELDx compareOperation=""/>

</KEY>

</TABLE_NAME>

</STATEMENT>

</ns:MT_RECORDSET>

This recordset represents an SQL statement like "update TABLE1 set FIELD1=123, FIELD2=456, FIELDn='xyz' where ID=12345 and FIELDx is NULL"

MT_RECORDSET is the name of the Message Type used.

For the value of the attribute ACTION you can choose from the following values:

 UPDATE : updates the given fields in the table with their new values

 INSERT : insert the given fields as a new row in the table

 UPDATE_INSERT : insert rows in the table when update is not possible

 DELETE : deletes given fields from the table

 SELECT : selects given fields from the table. Note that this option returns a response in the JDBC Sender form!

 EXECUTE : execution of a stored SQL procedure

 SQL_QUERY | SQL_DML : option to transfer more complex SQL statements to the database

The attribute compareOperation can have the following values:

 EQ : equal

 NEQ : not equal

 LT : less than

 LTEQ : less than or equal to

 GT : greater than

 GTEQ : greater than or equal to

 LIKE : used to compare strings