JDBC Sender Adapter - Transaction & Parameterized Query?

[suwandi_cahyadi]

Dear Experts,

I'm curious about the JDBC sender adapter in SAP PI.

As I see from the document and have been searching in the Internet, The default procedure of the sender JDBC adapter is to first run a SELECT/Store Procedure query then update the records that have been read before.

Configuring the Sender JDBC Adapter - Advanced Adapter Engine - SAP Library

What I want to ask is:

- What is the database transaction used for the SELECT and the UPDATE? I mean what if the SELECT is successful and the records have been sent to the IE, but the UPDATE failed. This way, the next polling run, the same records could be read again. Is it possible? Are the SELECT and UPDATE query atomic (if one fails the other fails too)?

- Is it possible to have a parameterized query / stored procedure in sender JDBC adapter? Because seeing at the default procedure, there should be at least a field that will be used as a flag (for example the processed field needs to be updated to '1'). Something like:

          - SELECT * FROM table_a WHERE docno > $last_doc_no

                         $last_doc_no is a paramter or variable from PI

          - EXEC sp_do_something ( $param_a, $param_b )

                         $param_a, $param_b are parameters or variable in PI

Thank you,

Suwandi C.

[Former Member]

Hi Suwandi,

all action to database are in one transaction and thats mean if one failed all failed.

And it is possible to have parameterized storied procedure. You sould send something like

<?xml version="1.0" encoding="UTF-8"?>

<ns0:mt_proc xmlns:ns0="http://aaaa">

   <statementname>

      <stProc action="EXECUTE"/>

      <TABLE>PROCEDURE NAME</TABLE>

      <access>

         <param_in isInput="1" type="some_type">input param</param_in>

         <param_out isOutput="1" type="some_type"></param_out>

      </access>

   </statementname>

</ns0:mt_proc>

[suwandi_cahyadi]

Hi Vedran,

Thank you for the reply.

AFAIK, the JDBC sender works in polling time.

How to set the input parameter in the channel?

Thank you,

Suwandi C.

[Former Member]

Hi Suwandi,

just use structure I sent to you and instead of param_in put your input parameter name (parameter value in example is input param) and instead of param_out put your output parameter.

Also you can take a look at

https://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

for some examples