aspose file tools*
The moose likes JDBC and the fly likes After insert statement, how do I get column value populated by DB trigger Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "After insert statement, how do I get column value populated by DB trigger" Watch "After insert statement, how do I get column value populated by DB trigger" New topic
Author

After insert statement, how do I get column value populated by DB trigger

Andy East
Greenhorn

Joined: Feb 02, 2001
Posts: 4
The primary key is generated by a database trigger for a table. A business object supplies the remainder of the column values.
I need to update my business object with the actual primary key that was generated by the DB after the insert. Any suggestions. I'm trying to avoid running a query after the insert to get it.
Brian Nice
Ranch Hand

Joined: Nov 02, 2000
Posts: 195
How are you doing the insert? I was thinking maybe if you used a stored procedure to do the insert it might work. In the stored procedure it could get the next value for the sequence, use it for the key, do the insert with parameters that are passed to the stored procedure, and then the stored procedure could return the value of the sequence to the calling program.
Not sure if that would work, maybe someone else would know for sure. HTH.
Brian
rani bedi
Ranch Hand

Joined: Feb 06, 2001
Posts: 358
Try to write a before insert trigger which would actually generate the primary key and then you can insert the data.


Cheers,<br />Rani<br />SCJP, SCWCD, SCBCD
Chandrasekhar Nadella
Greenhorn

Joined: Feb 07, 2001
Posts: 7
Yes u can do it. here is the way.
1. create the connection
2. setAutoCommit(false), if it already fasle don't do it.
3. do the insert & close the statement
4. use another sql to get the primary key value just inserted. for e.g.DB2(select distinct IDENTITY_VAL_LOCAL from your_table).
Oracle (select your_created_table_sequence.currval from dual).
5. commit the connection using connection.commit.
this logic is worked in both orcale/DB2, bcoz I've coded this in my previous projects.
Hope i have answered ur question.
Originally posted by Andy East:
The primary key is generated by a database trigger for a table. A business object supplies the remainder of the column values.
I need to update my business object with the actual primary key that was generated by the DB after the insert. Any suggestions. I'm trying to avoid running a query after the insert to get it.

Andy East
Greenhorn

Joined: Feb 02, 2001
Posts: 4
Thanks everyone for your comments and suggestions.
Cigdem Cemgil
Greenhorn

Joined: Aug 23, 2001
Posts: 1
In Java 2, SE 1.4 there is a solution for it:
The following executeUpdate() method of the Statement class makes sure that the generated key is returned in the result set:
public int executeUpdate(String sql,
int autoGeneratedKeys)
Executes the given SQL statement and signals the driver with the given flag about whether the auto-generated keys produced by this Statement object should be made available for retrieval.
Parameters:
sql - ...
autoGeneratedKeys - a flag indicating whether auto-generated keys should be made available for retrieval; one of the following constants:
Statement.RETURN_GENERATED_KEYS statement.NO_GENERATED_KEYS
After this method, you can call getGeneratedKeys method of the Statement class to get the generated keys.
public ResultSet getGeneratedKeys()
[This message has been edited by Cigdem Cemgil (edited August 23, 2001).]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Chandrasekhar Nadella:
Yes u can do it. here is the way.
1. create the connection
2. setAutoCommit(false), if it already fasle don't do it.
3. do the insert & close the statement
4. use another sql to get the primary key value just inserted. for e.g.DB2(select distinct IDENTITY_VAL_LOCAL from your_table).
Oracle (select your_created_table_sequence.currval from dual).
5. commit the connection using connection.commit.
this logic is worked in both orcale/DB2, bcoz I've coded this in my previous projects.
Hope i have answered ur question.

Do you ever encounter this problem-->
How do you ensure that no one else has inserted a record between your insert statement and the retrieval of the current sequence value? you could retrieve someone else's primary key!
This is the way we work around that problem-->
In oracle you can "select Sequence.nextval from dual" which will return the new primary key to you. Perform the insert using the value you retrieved from the above sequence. This way, there is no room for error because once you retrieve the next value, it is yours!
I was just wondering if you ever encountered that problem?
As to the original topic-->trigger returnval-->we had to change to a stored procedure because on many occasions we needed a return value.
Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: After insert statement, how do I get column value populated by DB trigger