Help required in JDBC

[Former Member]

Hi All,

i am using stored procedure to query the table and the data to PI. It is working Fine.

when a stored procedure is executed we get many(say 100) records.

Now the issue,if any specific record fails. the client wants the know the feedback which record has failed.

can anyone please help me how to send the feedback?

Thanks,

Hemanth.

[rodrigoalejandro_pertierr]

what you mean with:

if any specific record fails. the client wants the know the feedback which record has failed.

you mean when you process it in PI or when you call the SP in the JDBC sender channel?

[Former Member]

Hi Hemant,

If one record is failed during your 100 records transaction,Generally Database preferred rollback.and it doesn't continue the next records.

Rollback: that means it delete(rollback) all data what we inserted before.

in your case you want to continue the next records, I am n't exactly sure it doesn't continue the next record,You will confirm to your database guys about this.

I just suggest you, create one log table and one temparory data table.

Process is:

First you insert the data in temparory data table using the Insert Statement Structure, and as well Stored procedure structure to call the stored procedure( These both statement structures are in same message type)

first statement is do it can insert the data into your temparory table and after that second statement to call the stored procedure,This statement structures are working sequentially.

Now data is in Temporary table. Then you write stored procedure like this , Insert the data from temparory table to main table,during this transaction if any wrong data it goes to Log table(here you write the error handling).

If any error record found ,it creates the log table entry, and it doesn't insert the single record.

then you get the stored procedure response back to XI with the information of log the table entry, Here you will write the condition in BPM , If "error" found you send the mail to concern person.

Thank you

Sateesh

Edited by: sateesh kumar .N on Sep 21, 2010 11:25 AM

Edited by: sateesh kumar .N on Sep 21, 2010 11:27 AM

[Former Member]

Hi Sateesh,

thanks for your reply. I am not posting the data to database, PI is fetching the data from the databse using stored procedure.

please help me in this scenario.

Thanks,

Hemanth.

[Former Member]

Hi Hemanth,

You can handle this in mapping by including a new node in the target. Consider the below structure,

<row>

<data> 1- unbounded

<error> 1 - unbounded

</row>

As per your validation create the successfully records in DATA node and failed/error in ERROR node. If you want to send the error data to target system then no issues if not you can include new mapping like

Source JDBC - > Intermediate mapping (contains data and error records)

Intermediate mapping -- > Target

Regards

Ramg

[Former Member]

Hi Hemanth,

I would suggest to fetch a single record at a time from database, so that if any error is there, it would be easy to trace.

Also, you can make use of Alerts to know which record has failed.

You can capture the alerts at adapter engine level.

-Supriya.

[Former Member]

Hi,

@ Supriya

I would suggest to fetch a single record at a time from database, so that if any error is there, it would be easy to trace.

Currently he is saying 100 records... say tmrw 100000 records, then if u fetch a single record at a time dont u think its a performance issue and leads to overloading the server with large number of small messages...

Can u please let us know... what do u mean by exactly error record..

1.Is it the content of row is wrong??

2. Are u checking/evalvating this error record in mapping or Stored procedure only??

Babu

[Former Member]

Hi All,

Thanks for your replies ,fetching a single record at a time is not a feasible solution because the number of records are not confirmed. i just said an example of 100 records.

@Babu,

the stored procedure is written in such a way to fetch all the records updated in the table in a particular day.

we need to evaluate the data, for eg..only records which has EMPID below 10000 has to be processed. if incase any record is having EMPID more than 10000, PI should not process only that record and feedback DB team about the record.

This is my requirement, please suggest me a solution or shall i ask them to change the stored procedure.

Thanks,

Hemanth.

[Former Member]

Hi Hemanth,

the stored procedure is written in such a way to fetch all the records updated in the table in a particular day.

we need to evaluate the data, for eg..only records which has EMPID below 10000 has to be processed. if incase any record is having EMPID more than 10000, PI should not process only that record and feedback DB team about the record.

This is my requirement, please suggest me a solution or shall i ask them to change the stored procedure.

Let us say if u want PI to send the error message or Alert for error records as your logic, then PI shold have the error records also.

So what you can do is, let the error records also come to PI and in PI check for the error records and correspondingly send the alert... and valid records only u map to target.

If not u can go for 1:2 multimapping(without BPM) and map all the invalid records to 1 mapping to send it to Database team and 1 mapping for all valid records to target.

If u dont want to transfer the invalid records to PI then,

PI cannot able to inform the DBA about error records. So u need to change Stored Procedure to give only valid records...

Let us know still if u face any prblm...

Babu

[Former Member]

I would Suggest ,

write your Query with and rownum = 1 in where Clause, this will send only 1 record at a time .

so if any error came, you will easily Identify , which record got failed.. and one more thing ..by this those records which are without any error ...will be fetched to XI without any resistance ,

Regards

Prabhat Sharma.

[former_member241146]

Hi Hemanth.

You can do this with other interface, after to process in the Inbound Side you can trigger another interface like Process Return and update JDBC table.

What do you think?

Regards.

Bruno.