JDBC Problem

[Former Member]

Hi,

I am have configured an IDoc to JDBC(DB) scenario.Since I need to update two table ,I have create two Statment's as shows in the below XML.

<?xml version="1.0" encoding="UTF-8" ?>

<ns0:PartDetails_Request xmlns:ns0="http://xxx.com/target">

<Statement1>

<dbTableName action="INSERT">

<table>Table1</table>

<Access>

<field1></field1>

<field2></field2>

</Access>

</dbTableName>

</Statement1>

<Statement2>

<dbTableName action="INSERT">

<table>Table2</table>

<Access>

<field1></field1>

<field1></field1>

</Access>

</dbTableName>

</Statement2>

</ns0:PartDetails_Request>

I tried inserting records and its works fine.I mean both the tables(Table1 and Table2) get the record.

But there is problem,

In some scenarios,

In payload ,Table1 has values and Table2 doesn't have any values.I mean Table2 payload is empty,so it generats a empty SQL Query and would try to INSERT the table,which is the error.So the data doesn't get inserted to both tables.

So I thought of some condition like ,only if Table1-field2 has some value, then I should insert a value to Table2 ,ie.only then the SQL Query for Table2 should be created.

else,

If a particular Idoc field has some value only then the SQL for table2 should be created.

So how can I achive this? So how to handel this null values in Table2?

Thanks,

Venu

[Former Member · Accepted solution]

Hi Venu,

from a logical pouint of view, you have two transactions:

a) insert table #1

b) insert table #2

So, if there is no logical connection between there two table, why don't you set up two mappings with two inserts.

Under restart aspects this has also benefits.

Regards Mario

[Shabarish_Nair · Accepted solution]

Venu,

I endorse Mario's view.

the best way for you since the tables are different is to segregate the mappings for the table. So even though you have the same receiver, use different inbound interfaces that means two different interface determinations. Note you can reuse the same JDBC recv. CC

~~ ShaBZ

[sabyasachi_mohapatra3]

hi venu

cud u plz provide the idoc structure with occurence & under wht condtions the idoc fields are mapped to fields of both table 1 & table 2.

regards

sabyasachi

[VijayKonam]

Make the statement2 occurances 0..1. Map this to the condition field without which the insert should not work. If that field is not present in the idoc, statement2 node will not get generated and your scenario will work as a single insert statement.

VJ

[Former Member]

VJ's idea works..i did the same..

Prabhakar