Batch insert problem in JDBC

[former_member206760]

Hi

I am currently working on a file to JDBC scenario...

in file we have abt 5000 records ....and we are sending them in one query to database ( UPDATE_INSERT)

my problem is if 1 of the records is having a problem ( say length issue ) all the records are failing and we cannot even find out from the error of receiver jdbc adapter like which record failed...

Can i do multimapping so that each record creates a separate message??or it would not be advisable

[former_member854360]

You can do multimapping.

repeat the statement tag itself and map only one record per statement

and UNCHECK the maintain order at runtime option in interface determination

then if your 2nd record fails then 3rd record will continue

[former_member206760]

Repeating the statement node doesnot work because even then PI puts all the individual UPDATE_INSERT queries in one transaction .../

I am asking if we can change the ocuurence of the message type itself....like 1: n mapping

but that would create say if there are 5000 materials...5000 messages in the system

is it advisable and will it work??

[former_member854360]

Ya i mean to say change the occurence in messagemapping signature tab and go with multimapping

Dont forget to UNCHECK the "maintain order at runtime option" in interface determination then it will work as EO

Message spliting will be done in Adapter level .In SXMB_MONI it will come as single message not 5000

[former_member206760]

Guys any more info on this.

Is multimapping the righ way to go

[former_member854360]

Hi Tarang,

if you use, Batch mode then it will try to insert all the records or none.

you can also try normal mapping with repeating statement tag but without BATCH MODE on

[Former Member]

i am not a database expert but i am having one suggestion...u can use stored procedure...

so if any exception occurs while executing a Stored Procedure that has been called by PI for accessing the database ( for ex. SQL) it will stop the process and an exception is thrown back to PI but in order to avoid such a situation and to continue with the execution of SP, we can catch and handle the exeception in the SP and continue proessing of rest the messages.....

in the JDBC receiver adapter: chk Database auto commit

chk with ur DB team..they will be knowing abt this catching and handling of exeception at the SP level...

but lets see what experts have to say on this...

[Former Member]

Hi,

A stored procedure should beable to handle your requirement. Refer this link for a similar issue

.

Other than this, using multimapping (target structure made to 0..unbounded) would also work as each target message would then have its own commit.

Regards