JDBC Receiver: How to get PK/sequence.

[former_member296836]

Hello,

I try to insert a messge via JDBC receiver adapter into an Oracle database.

I have two tables
table 1)

  PK_ID

  Field1
  ....

table 2)

   ID  (autoincrement)

   PK_ID (from table 1 used as FK)

   fieldA

   ...

I need to create a unique primary key for field  PK_ID that is used as

PK in table1 and FK in table2.

My Oracle admin said, that I could use

SELECT sequence.nextval FROM dual;

but how to do this in the data structure I have to use?
Or is there another solution to generate a unique ID?

The only possible solution I found:

http://www.codeweblog.com/pi-7-1-jdbc-receiver-adapter-configuration-b/

but can I use ".nextval" for any field?

regards

chris

[naveen_chichili]

Hi Chris,

As you said you can use SELECT sequence.nextval FROM dual; and then you can retrive the value from oracle database using JDBC lookup and then assign it to the target element it will work with the sequence and also you can satisfy the PK Violation.

Thanks and Regards,

Naveen.

[former_member296836]

Hi Naveen,

thanks for your answer.

So you mean I should use the JDBC lookup in mapping to get the sequence?

But if I have a huge load of messages for this interface, I will have trouble with this kind of getting the sequence number, because the table will not be locked and there is a time gap between getting the number and inserting or did I get it wrong?

Regards

Chris

[naveen_chichili]

In this case you can try using BPM and make message wait until the other message is processed so that you can avoid such kind of issues.

Thanks,

Naveen