Database insert query

[former_member474221]

Hi guys,

I have a proxy to database scenario...

Proxy is sending me 100 materials in one call ..

1. .Is there a way that i insert all of them in one transaction OR

2. Is it that all the INSERT statements will be separate. and there are chances that 80 will get inserted and 20 will fail

If this is the case..how would i handle...i do not want to use BPM....

Should I ask the proxy team to send 1 material at a time.

[baskar_gopalakrishnan2]

IMO, Sending inserts one at a time is better solution. JDBC treats entire query as one transaction. We do in our projects a batch process (about 5000 inserts per day) and no issues. This way only inserts that has problem will not be inserted in table. Rest will be updated. If you go for bulk inserts and few records have data issues then entire transaction will not happen.

If you want bulk inserts/updates go for Stored Procedures. That is feasible option too.

[Former Member]

1) if u want to insert entire source data at a single go then use Stored procedures..create a SP and and pass source payload as xml input to the SP.

Ur target structure will be something like this:

<StatementName>

<storedProcedureName action=u201D EXECUTEu201D>

<table>realStoredProcedureeName</table>

<param1 type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName>

pass ur source data as xml input to param1.

If u r using PI7.1 then chk this:

http://www.sappi.sapag.co.in/flat-file-to-file-senario/convert-the-input-xml-to-string-in-pi-7-1-usi...

on the database side parse the string (xml document) and then insert the data in the table.. Chk with ur DB team..

2) you can catch the exception in the stored procedure while inserting the errorneous data (i have done the same in SQL) and log it to the exception table (chk with ur DB team regarding the same) and continue the SQL process