cancel
Showing results for 
Search instead for 
Did you mean: 

What order are multiple statements in JDBC adapter executed in?

Former Member
0 Kudos

Hello,

If I put multiple statements in an XML message sent to the JDBC receiver adapter, are they executed sequentially? or in parallel???

In this case I have several inserts followed by a stored procedure execute. I am getting a couple of inserts done then the stored procedure is executed. I would need all the inserts executed before the SP is called.

Would specifying EOIO service level achieve this? Is there a configuration parameter in adapter, driver or somewhere else to achieve serial execution of the statements???

Thank you!

-Sam

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Sam,

Please go through the following link. It should be helpful.

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

reg,

-Naveen.

Answers (3)

Answers (3)

Former Member
0 Kudos

Ok Guys,

So the tests were successful, I made a statement with an insert, and another with an update instead of executing the SP which would just perform the update statement.

The insert and the update were correctly executed in the same sequence as they appear in the message.

I still don't know if separate statements are in the same transactional context. Maybe more on that soon.

Sample message output from the mapper: (For this POC I included just the key field in the insert statement). The constant for comparisons is generated dynamically by the mapper.

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

<ns0:MT_SALES_HISTORY_INSERT xmlns:ns0="urn:com.mxx.Controlling.invoicehistory">

<INSERT>

<SALES_HISTORY_TABLE action="INSERT">

<table>F42119</table>

<access>

<SDAN8>0010000006</SDAN8>

</access>

</SALES_HISTORY_TABLE>

</INSERT>

<UPDATE>

<SALES_HISTORY_TABLE action="SQL_DML">

<access>UPDATE F42119 SET SDAN8 = (SELECT ABAN8 FROM V7JPRCOMM.F0101 WHERE ABURRF = &apos;0010000006&apos;) WHERE SDAN8 = 0010000006</access>

</SALES_HISTORY_TABLE>

</UPDATE>

</ns0:MT_SALES_HISTORY_INSERT>

Thanks all for your kind help.

Points were awarded.

Former Member
0 Kudos

If you are working on complication SQL/ PLSQL's queries, pls work on the staging table first and then after no errors, run it on production..

look for this blog:

/people/saravanakumar.kuppusamy2/blog/2005/01/19/rdbms-system-integration-using-xi-30-jdbc-senderreceiver-adapter

Thanks-GS

former_member619134
Participant
0 Kudos

Hi Sam,

I am working on a scenario in which i need to UPDATE_INSERT data into a temporary DB table and need to execute the stored procedure, will using multi statement with Statement1 for UPDATE_INSERT and Statement2 for EXECUTE work?

I am using PO 7.5

Waiting for your quick response

Regards,

Akash Chauhan

Former Member
0 Kudos

Hi,

As Bhavesh said i also think they are executed sequentially.

If you are inserting in a single table then You can create a single "statement" element for all those inserts. Create one "access" element for each insert within that "statement".

Then create a "satement" element for the Stored procedure.

Regards,

P.Venkat

bhavesh_kantilal
Active Contributor
0 Kudos

Venkat,

Any idea what the difference between having multiple ACCESS level tags is and multiple STATEMENT level tags?

For multiple insertions into the same table, we can have multiple ACCESS level tags under ONE STATEMENT or we can have multiple STATEMENT level tags itself. Both of them work fine, but , what is the internal difference in the wayt they work? Do you have any idea?

Regards,

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

Sorry, Me too have no idea on that. But what i think is, that might be useful in case of failures. If inserts are specified in a single statement, then if a single insert statement fails then all the inserts might be rolled back.

Me too need some more info on this. Experts please give the real working.

Regards,

P.Venkat

Former Member
0 Kudos

Ok thank you guys for your answers, althought we haven't reached the definite answer. (Points awarded). I am going to run some tests today and update this thread later.

Experts please provide your science!

-Sam.

moorthy
Active Contributor
0 Kudos

Hi,

Using BPM, you can send one-by-one statement. But connecting database for multiple times is not preferred and use of BPM if you have huge data

I think it will insert in Sequential manner. i.e first in first insert.

just a thought

Regards,

moorthy

Former Member
0 Kudos

Hi Bhavesh/Venkat,

Any idea what the difference between having multiple ACCESS level tags is and multiple STATEMENT level tags? For multiple insertions into the same table, we can have multiple ACCESS level tags under ONE STATEMENT or we can have multiple STATEMENT level tags itself. Both of them work fine, but , what is the internal difference in the wayt they work? Do you have any idea?

- Were you able to find the proper difference? Because, I need to use update_insert to update/insert into table. But, the problem is with the error handling part. Say if I try to update 100 records in a table and assume 50th record fails. In that case, will all other records be updated except for 50th? Or all will get rolled back if I use multiple ACCESS level tags??

Please clarify.

Thanks,

Hussain

former_member619134
Participant
0 Kudos

Hi Venkat,

I am working on a scenario in which i need to UPDATE_INSERT data into a temporary DB table and need to execute the stored procedure, will using multi statement with Statement1 for UPDATE_INSERT and Statement2 for EXECUTE work?

I am using PO 7.5

Waiting for your quick response

Regards,

Akash Chauhan

bhavesh_kantilal
Active Contributor
0 Kudos

Hi,

AFAIK, the statements will get exectuted sequentially. For every insertion you want , just create the STATEMENT level tag for the same sequentially.

Regards,

Bhavesh

former_member619134
Participant
0 Kudos

Hi Bhavesh,

I am working on a scenario in which i need to UPDATE_INSERT data into a temporary DB table and need to execute the stored procedure, will using multi statement with Statement1 for UPDATE_INSERT and Statement2 for EXECUTE work?

I am using PO 7.5

Waiting for your quick response

Regards,

Akash Chauhan