Return Primary key from INSERT action.

[Former Member]

Guys,

I have the following scenario:

SQL Server

Table - Customer

id - int, auto inclement (Primary Key)

name, string

address, string

I am insert in table using JDBC Adapter Receive,

<stmt>

<dbTableName action ="INSERT">

<table>Customer</table>

<access>

<name>Joao</name>

<address>test</address>

</access>

</dbTableName>

</stmt>

I dont have problem in insert.

My question is:

I would like call one jdbc adapter sync and one return the "id" from current insert.

How can to do this?

Do I need create one SELECT or the response of jdbc adapter return the primary key value?

[bhavesh_kantilal · Accepted solution]

One option,

1. create the receiver jdbc adapteer in such a way that it has multiple statement level tags.

2. the first statement level tag does the insert with Action = INSERT

3. The seconds statement level tag will do a Action = SQL_QUERY with select query as "select max(id) from table".

The Jdbc response will have the 2 responses at the statement level tag, one will have the count of insert and other the value of the id as these statements are executed sequentially.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi,

According to Bhavesh's approach, Auto commit feature must be enabled. Then only the sequential select statement would fetch the results correctly.

Bhavesh, Please correct me if I am wrong.

Regards,

Sudharshan N A

[Former Member · Accepted solution]

Sudarshan,

If I am not wrong, when you use multiple statement level tags the first statement (insert here) would already have been executed and the second statement (select) should have no problem selecting this record. But you need to design your target datatype such that it creates multiple statements.

Regards,

Jaishankar.

[Former Member · Accepted solution]

Hi Jai,

You are right.

My doubt was with the transactional behaviour handling at JDBC Adapter.

In this case the insert statement should have been commited before the select statement, else your sequential select statement may not see the records.

I am not a DB expert. Just a doubt.

Regards,

Sudharshan N A

[Former Member]

By standard, JDBC adapter can return the number of rows updated/inserted using the JDBC-xml. Will that suffice your requirement?

Regards,

Jaishankar

[Former Member]

Hi,

Insert count i already know, but i need return the primary key value.

I need catch primary key value and use them for insert in other table.

[VijayKonam]

I never talked about count. When you already have the primary key in the first insert, are you not able to use the same in the mapping to create the second insert statement.. just a thought? In my previous reply I was asking you to get the primary keys them selves in the select query.

VJ

[Former Member]

Try what Vijay has suggested. That is an easy option. But I am not sure about the results.

Another option is to use BPM.

Steps of the BPM.

1. Receive : Receive from sender

2. Send synch - Update db and get the insert count as response.

3. Transformation - Use the msg received in step 1 and jdbc response as source and map it to target.

4. Send the resultant msg to the sender back.

Regards,

Jaishankar

[Former Member]

Hi,

You can do it like that. Write Stored Procedure in database to insert the data in DB, this SP should be able to return the primary key after insertion of recored.

Then you can design your interfaces for synchronous interface. You will call the Stored procedure using JDBC and can receiver the response from it synchronously.

This is one of the method i have implemented.

Kulwinder

reward if helpful

[Former Member]

Hi,

Thank you for information,

But, actually, i can not use SP.

[VijayKonam]

You can try creating a select stament in the same message with same data as you used for INSERT. Make the interface synchronous. Give it a try.

VJ