Auto Commit off and getGeneratedKeys

I have AutoCommit turned off, as I am managing commit and rollback.

I also need to retrieve an identity key. Using the statement.getGeneratedKeys throws the error "The statement must be run before the generated keys are avalilable".

Ok, I can understand this, as I am trying to get the identity key before I am commiting the transaction. However I am inserting the parent row, then based on the identity key, I am inserting several children, all within the same transaction.

So how do I do this?

I d/l the latest JDBC drivers and using Java 1.5Sooooo, I need to commit the parent, then do the children within a transaction, and if anything blows up I rollback the children, then do an explicit delete on the parent? What if the app blows up, I now have a parent without the requisit children.

Sort of defeats the whole transaction thingy.|||

Hi busybee123,

I am a little confused about what you are trying to accomplish. Some sample/pseudo code would be great for helping us understand your algorithm. Are you trying to perform nested transactions or are you using two separate connection objects?

Here is some pseudo code of what I suspect you what to achieve; correct me if I am mistaken:

Connection conn1 = DriverManager.getConnection(...);

Connection conn2 = DriverManager.getConnection(...);

conn1.setAutoCommit(false);

conn2.setAutoCommit(false);

Statement stmt1 = conn1.createStatement();

Statement stmt2 = conn2.createStatement();

String parentUpdates[] = new String[]{all parent updates};

boolean commitParent;

for(int i = 0; i < parentUpdates.length; i++)

{

commitParent = true;

stmt1.executeUpdate(parentUpdates[ i ], Statement.RETURN_GENERATED_KEYS);

ResultSet keys = stmt1.getGeneratedKeys();

while(keys.next())

{

String aKey = keys.getString(1);

stmt2.addBatch(insert the children here given aKey value);

}

try{

int updateCounts[] = stmt2.executeBatch();

}

catch(BatchUpdateException bue)

{

//process success/failures here and decide if to commit conn1 and/or conn2 or not

if(something went wrong)

{

conn2.rollback();

commitParent = false;

}

else

{

conn2.commit();

}

}

if(commitParent)

{

conn1.commit(); //or delete parent now

}

else

{

//break, continue, or throw here

}

}

Thanks,

Jaaved Mohammed - MSFT

|||A few things contributed to my post.

We were using an older JDBC driver which did not support getGeneratedKeys(), so we were using "insert ...;select SCOPE_IDENTITY() as newkey" with a statement.executeQuery(). However when we started using manual commit/rollback, this does not work as it creates an internal virtual connection which cannot be used with manual commit.

So we upgraded to the latest JDBC driver which does support getGeneratedKeys(). Reading through the new documentation we came across your example code. When we tried it out, we got the above noted error message. Searching the web for the error message produced zero hits.

Eventually we found that the statement.executeUpdate() method can take a second parameter with the value of Statement.RETURN_GENERATED_KEYS. This causes MS SQL to return the generated key which can then be retrieved as per the examples. So our bad :-) In retrospect the error message does explain what happened, but hindsight is always 20/20

A few things:
- the error message returned was not entirely clear as to why it was failing, and the error code number was zero.

- No documentation about the error could be found.

- in the HTML documentation under "Using Auto Generated Keys", in the second paragraph, second to last sentence it states "with the returned column name of GENERATED_KEYS". This is incorrect. Inspecting the metadata of the result set shows that the column does not have any name or label (empty string). The value can only be retrieved by using the index number of 1.

- could the error messages produced by the JDBC library be documented please?

Kudos to the JDBC team for supporting getGeneratedKeys()!|||I'm working on a similar issue and this code seems to help a bit, but i have a question : does getGeneratedKeys not work with a PreparedStatement for which i'm using batch update? I have my code on this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1470935&SiteID=1sql