Skip to Content
avatar image
Former Member

Help required in JDBC

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.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • Sep 20, 2010 at 06:24 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 20, 2010 at 06:31 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 21, 2010 at 04:16 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • avatar image
    Former Member
    Sep 21, 2010 at 09:25 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • Sep 21, 2010 at 07:54 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded