Skip to Content

JDBC - Insert and then calling a Stored procedure

Hi all.

I have a RFC to JDBC scenario.

My requirements are as follows:

  1. Insert rows to the DB(SQL).
  2. After all the rows were inserted to the DB, call a Stored procedure.

The first step was configured and it is working fine. How do i add the second step?

Thx.

Motty.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Jan 07, 2014 at 03:00 PM

    Hello,

    If the table and SP are in the same database then why not just use single SP and let DB call the other one (SP) once all the records are updated - it would provide better flexibility.

    Other solution would be to insert rows using JDBC lookup inside mapping and once records are updated call SP - could be performance costly solution as records are inserted using lookup

    Or u can also think of using BPM where u can request either transport ack (in case of async call) or response from DB and call SP if all the records are updated successfully.

    So, If i were u then i would have opted first solution (in case both table and SP are in the same DB) and if in case both are from different DBs then solution 3

    Thanks

    Amit Srivastava

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 07, 2014 at 12:14 PM

    You need two stored procedure. The first one, insert into database and then call the second stored procedure (your second step) .

    Regards,

    Carme.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 07, 2014 at 12:37 PM

    Hi Gal,

    From my point of view you have two options:

    1. Modify the stored procedure in the DB, then you make the INSERT before to do the actual stored procedure.

    2. Modify the DB table including a status field. Then you can do a second scenario JDBC - JDBC that reads the empty status in the sender adapter and later in the receiver adapter call the store procedure.

    Regards.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 07, 2014 at 02:55 PM

    Hi Gal - Instead of the inserting the records directly into the database table, insert them using a stored procedure. Inside the stored procedure call the second stored procedure (after your insert statements)

    http://stackoverflow.com/questions/15802511/execute-a-stored-procedure-in-another-stored-procedure-in-sql-server

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.