JDBC: Insert complex structure

[Former Member]

Hallo everybody,

asuming I have to DB tables:

a) <b>CUSTOMER</b> with fields

- CUSTOMER_ID

- NAME

b) <b>CONTRACT</b> with fields

- CONTRACT_ID

- CUSTOMER_ID

- CONTRACTDATE

The IDs of both tables (<b>CUSTOMER</b>, <b>CONTRACT</b>) are generated by the DB.

So the INSERT for the CUSTOMER Table would be

<i>INSERT INTO CUSTOMER (NAME) VALUES ('CLONEY')</i>

For the second insert into the CONTRACT table, I need the CUSTOMER_ID from the first insert. How can I do that in XI?

Thanks a lot, Regards Mario

[STALANKI · Accepted solution]

You can use stored procedures to do it?Isnot it?

[bhavesh_kantilal]

Hi mario,

sorry for asking for something that you have already given

One solution to your problem, Design a SYNCHRONOUS JDBC RECEIVER, that will return the CUSTOMER_ID after the insertion for the first table is successsful.

Use the response to map it into the Customer_ID of the 2nd insertion.

Regards,

Bhavesh

[Former Member]

"Use the response to map it into the Customer_ID of the 2nd insertion."

--> This was my first thought.

But in this case I have to use an integration process. This is unfavorably to the performance. That's why I wanted to do this just with ONE receive step.

Regards Mario

[bhavesh_kantilal]

hhhmm... looks like a tricky situation

Will let u know if some other solution does strike me.

if you manage to find some other solution other than the ones discussed, do let us know.

Regards,

Bhavesh

[bhavesh_kantilal]

Hi Mario,

We faced a smiliar situation and found that using <b>STORED</b> <b>PROCEDURE</b> for your execution was the best way to go. You can call your Stored Procedure from your JDBC adapter and then, your stored procedure can use SEQUENCE in your Stored Procedure to create the unique values that you want.

The data format for insertion using a STORED PROCEUDRE is of the following format,

<root>

<StatementName>

<storedProcedureName action=” EXECUTE”>

<table>realStoredProcedureeName</table>

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName5>

</root>

For more info on this, check this link,

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Do get back for any queries,

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

thanks for your answer. With the SEQUENCE statement the stored procedure would for example increment the key for a inserted record. But in fact, there are already keys existing. In my special case the DB is managing the keys, so I can't give the key externally. That's why SEQUENCE is unfortunately not the solution.

Thanks Mario

[bhavesh_kantilal]

Hi Mario,

How would you do a normal insertion into this table from your Database client. DO you have to give the value for the KEY field in the SQL insertion statement?? Can you give us the insert statement that works for the Database table.

If you do not give any value for the key field while doing the insertion into the Databse, then the same way, you can ignore that field while creating the JDBC receiver.

Regards,

Bhavesh