how to return auto-generated primary key from create procedure - Data Service Integrator

I have a physical data service corresponding to a table called ACCOUNT. The primary key on the table is called ACCOUNT_ID. There is a trigger on the database that generates the primary key value and adds it during an insert. I would like to have this primary key value returned from the create procedure of the physical data service. Any ideas on how this can be done? Thanks in advance. 

What type of database?
Oracle, Db2, etc.
What version?
Edited by: mikereiche on Apr 27, 2009 2:33 PM 

What type of database? Oracle 10gR3
ODSI version is also 10gR3.
IDE is Workshop 10gR3.
In my original post on this thread, I said that we have a trigger to generate the primary key during insert. Actually, we would not mind (and would even prefer) that the primary key be automatically obtained by ODSI from a sequence in the DB and used during a create procedure. The use of a trigger is a fallback in case our favored strategy does not work. Thanks for your help. 

that the primary key be automatically obtained by ODSI from a sequence in the DB and used during a create procedureGo to the design View for the physical data service
Click on the column that is populated from the sequence.
In the Properties Tab (bottom window), under General, for AutoNumber, click on the 'Value' column, select sequence. You'll get a pop about some things getting changed, just click OK. Along with setting AutoNumber=sequence, that column will appear as optional (the question mark).
Just below that, for Sequence Object, type the name of the sequence (you can also specify MYSCHEMA.SEQUENCE_NAME in case the sequence is under a different schema).
When you create SDO objects - leave the column to be updated as empty.
i.e. if it was CUSTOMER_ID, your customer would look like this ...
<CUSTOMER>
<FIRST_NAME>John</FIRST_NAME>
<LAST_NAME>Smith</LAST_NAME>
</CUSTOMER>
and when you read him back from the database, he would be ....
<CUSTOMER>
<CUSTOMER_ID>38738</CUSTOMER_ID>
<FIRST_NAME>John</FIRST_NAME>
<LAST_NAME>Smith</LAST_NAME>
</CUSTOMER>
Edited by: mikereiche on Apr 28, 2009 11:40 AM

Related

Control autoincrement + identity generation

Is it possible to setup a column (in logical model) such as it gets AutoIncrement [x] and IdentityColumn [x] when fwd.eng is done?
If not, is there a way using domains or new types or distinct types?
plus: what happens in practice when AutoIncrement is on but IdentityColumn is off?
Edited by: T. on Jun 9, 2011 3:01 AM 
Is it possible to setup a column (in logical model) such as it gets AutoIncrement [x] and IdentityColumn [x] when fwd.eng is done?No these will be set automatically when we add support for automatically generated surrogate keys.
plus: what happens in practice when AutoIncrement is on but IdentityColumn is off?You can test it :). It depends on database - trigger and sequence will be generated for Oracle (with or without identity column on). Nothing for other databases - you can have only one identity column and that column will be generated as identity column for other databases.
Philip

Transformation Script - Create Foreign Key

Hi,
I'm currently trying to write a transformation script that creates a new foreign key, but I can't quite seem to get it to work.
Given the following variables, could someone quickly explain the steps I need to go through?
model // The model variable provided to the script
source_table // a 'Table' object, representing the source table. Assume it has a primary key "id"
target_table // a 'Table' object, representing the target table. Assume it has a primary key "id", and a column "source_id" to be used as the foreign key.
Thanks 
Hi,
you can use search functionality of the forum. Here is one thread How to use transformation script to add FK to relational model
Well it's complicated there here you can find the steps How to create Foreign Keys in relational Model using Transformation feature
Philip
Edited by: Philip Stoyanov on Sep 12, 2012 2:23 AM 
Thank you for your quick reply. I had searched, but I obviously wasn't using the right terms.
Looking at the code in that thread, I was able to create a foreign key, but it adds a column to the source table. Is there any way to create a foreign key without creating a new column? Or, failing that, how can I alter the FKAssociation object so that it points to a column of my choosing?
EDIT:
Looking at the second link, I tried using the addForeignKey method, but it triggers a script evaluation error. I'm assuming my types are wrong. My referred is obtained by target_table.getPK() and the list of colums is:
list = new List();
list.add(column) // where column is a Column object
Edited by: 958587 on 13-Sep-2012 03:42 
may be you can try
list = new java.util.List();Philip 
Hi,
Thanks for your help.
In the end, I used java.util.ArrayList() since java.util.List() is abstract.
It now works as I hoped.

Partial Resultset with Toplink

Hello,
I am new to the toplink.i am looking forward for quick solution for my project work.
i have table with [ id <int>,name <char 20>,data<char large> ] and i do have its corresponding entity according to this.the issue is if i am using ReadAllQuery api it is loading everything into my object.as data is large so , my object become very heavy weight. i do not want to populate data field using toplink ,is there any way to do so.
I am using toplink i.e without JPA.
please suggest me.Thanks in advance.
Edited by: 879119 on Aug 13, 2011 8:26 AM 
You can use a partial object query (ReadAllQuery.addPartialAttribute()), or use fetch groups (ReadAllQuery.setFetchGroup()), to read a partial object.
You may also consider moving the lob to another table (DATA) and having a foreign key / OneToOne to it that is lazy.
---
James : http://www.eclipse.org/eclipselink/

implementing surrogate keys in dimensions

hello,
First thing, I'm new to ODI! I am using Oracle data integrator 10.1.3.
I have a dimension table 'Dim_Contracts' as target table. The structure is as follows:
PK_Dim_Contract Primary key (surrogate key - to be populated from an Oracle database sequence in the target)
Contract_ID (normal field in target - no constraints in target- to be populated from source - originally a primary key in source)
+ other dimension attributes.
from what i have googled out and read in the forum, i cannot define 'PK_Dim_Contract' as the primary key of my dimension (target) table, to be able to update it from the oracle sequence defined - rather the 'contract_ID', which is the natural key should be the primary key. Is that correct? If yes, isn't it against dimension modelling principle?
More to the point, my question is: How do I populate a sequence in my primary key field in the target table?
Thanks for your help.
Regards,
Anju 
Hello Anju,
Welcome in the ODI community ;).
What I suggest you is to set the UNIQUE KEY on Contract_ID in your target. This way you will be able to use flow control and do Incremental Update Loading.
PK_Dim_Contract (surrogate key) can be your primary key in the dabatase.
To populate PK_Dim_Contract from an Oracle Sequence, create it first in your Oracle DB. Add a new sequence to your project (left pane), choose Natural Sequence, choose your schema and enter the name of your Oracle Sequence.
In your interface, define the mapping of PK_Dim_Contract as
:<ODI_SEQUENCE_NAME>_NEXTVALand execute this mapping on the target.
Note: :<ODI_SEQUENCE_NAME>_NEXTVAL works only for SQL Statements. If you want to use the sequence somewhere else, use the following syntax :
#<ODI_SEQUENCE_NAME>_NEXTVALHope it helps,
Jerome 
Thanks Jerome. Many thanks for your quick response
Another question (maybe dumb!)
i have created my sequence in Oracle db.
"Add a new sequence to your project (left pane), choose Natural Sequence, choose your schema and enter the name of your Oracle Sequence."
when i add a new sequence in the project pane (under my project) i can't find any place where i can insert the Oracle Sequence name.
it does ask for the schema name, table name and column name.
Thanks
Anju 
Your in 10G - Just map the column as <sequence_name>.nextval in your interface. (e.g the name of the database sequence object , you might have to include the owner)
Untick this column for 'update' and make sure its ticked for 'insert'.
Should be OK. 
Unfortunately I'm not used to ODI 10.
Does Native Sequence exists or do you have only the choice between Standard Sequence and Specific Sequence ?
In the second case, you won't be able to do what I explained but you can do the following :
Don't create the sequence in ODI and directly put this in the mapping (on target) :
<SCHEMA_NAME>.<SEQUENCE_NAME>.NEXTVALBut it will only work if your schema_name is not context-dependant.
Don't forget to grant select on this sequence to the schema you use to connect on the target dataserver. 
Hello everyone,
I've followed these instructions step by step and it worked.
http://mhimu.wordpress.com/2009/05/04/odi-incremental-update-and-surrogate-key-using-database-sequence/
Thanks for all your help!
Regards,
Anju
Edited by: 967123 on 24 oct. 2012 06:11 
Nice solution, it will get the good schema name.
Don't forget to mark the question as solved so next people with the same issue will find it easily ;).

Auto generation of Primary keys

Hi All,
Please let me know the way to auto generate the primary keys through the JDBC insert calls.
As my primary keys has no business purpose so i would like to have it as auto generated ones.
Please help 
That would be a job for your dbms.
If using Oracle you'll have to use sequences. 
thanks dwg for the reply.........but do i have to do anything in the code
to generate the sequence for the table? 
So you are using Oracle?
To create a sequence, issue the ddlcreate sequence somesequencename;That will create a sequence starting at 1 and incrementing by 1.
In order to get the next value execute the queryselect somesequencename.nextval from dualEdit: fixed typo.
Edit2:
You can also insert into a table using the sequence directly, I tend to never do that though because I want to know which id was assignedinsert into sometable(idcol, something) values (somesequencename.nextval, 'Foobar')Message was edited by:
dwg 
Consider to use JPA instead of JDBC so you can stick to the OO world. There is also a #GeneratedValue annotation:
http://java.sun.com/developer/technicalArticles/J2SE/Desktop/persistenceapi/
-Puce 
So you are using Oracle?
To create a sequence, issue the ddlcreate
sequence somesequencename;That will create a
sequence starting at 1 and incrementing by 1.
In order to get the next value execute the
queryselect somesequencename.nextval from
dualEdit: fixed typo.
Edit2:
You can also insert into a table using the sequence
directly, I tend to never do that though because I
want to know which id was assignedinsert into
sometable(idcol, something) values
(somesequencename.nextval, 'Foobar')Message was edited by:
dwgthanls dwg..............yes i am using Oracle.
is the somesequencename common sequence for all the tables in the database?.......... 
is the somesequencename common sequence for all the
tables in the database?..........No just a random name I chose.
You should choose your own names, most often one for each key.

Categories

Resources