cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC - Insert and then calling a Stored procedure

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Answers (3)

Answers (3)

former_member184720
Active Contributor
0 Kudos

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-i...

iaki_vila
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

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

Regards,

Carme.

Former Member
0 Kudos

Do you mean calling the second SP from the first SP or doing so in two step from the PI? because if it is the latest, i would appreciate an example.

Regards.

Motty