Insert a row, then get the primary identity key for use a foreign key.
Hello All,
I have been bashing my head against this one for a while and I am not coming up with any good solutions. I am performing an extract, transform and load of a legacy database into a new schema. The DB folks chose to build a OO type schema where there is a Parent table with a primary identity key and the child table has a primary key that is also a foreign key containing the identity key of the parent table. So, upon extraction, I need to create a row in the parent table, get the primary key that is auto generated by the identity property in the parent table, then use that key as the primary key as the child table when I map the child table attributes.
Does anyone have suggestions here? I have started thrashing and am no longer making progress.
Thanks,
Jim Long
if you are using mysql as your database you can use tMysqlLastInsertId to retrieve the last autoincrement key that was created on that connection.
this can be found under Databases->MySQL
edit:spelling
Hi,
you may use a tPostgresInput with something like "SELECT MAX(id) FROM table".
To give you some more information a screen shot of your job would be nice.
Bye
Volker
Hello, I am still working on this problem. I am using MS Sql Server 2008 so I have tried using tMSSqlLastInsertID. When I run my job, here is what I get: Starting job DataFlexDump at 14:45 12/03/2009. connecting to socket on port 5285 Exception in component tMSSqlLastInsertId_1 java.lang.NullPointerException at kaptrasnformation.dataflexdump_0_1.DataFlexDump.tDBInput_1Process(DataFlexDump.java:1723) at kaptrasnformation.dataflexdump_0_1.DataFlexDump.runJobInTOS(DataFlexDump.java:2784) at kaptrasnformation.dataflexdump_0_1.DataFlexDump.main(DataFlexDump.java:2690) connected disconnected Job DataFlexDump ended at 14:45 12/03/2009. Here is a screenshot:
Hello Shong,
I do not understand the use of OnSubjobOk. How does this come about?
I will keep digging around in the forums and tutorials to see if I can discover more about this, but any pointers would be greatly appreciated.
Thanks,
Jim Long
Hi Jim, Were you able to find the solution of your problem. I am asking this because I am facing the same problem when trying to insert data in child job and sharing the database connection between the parent and child job. Any help will be highly appreciated. Thanks, Priyanka
