Insert with SELECT@@IDENTITY (sqljdbc 1.2)

Hi!

We are porting a database from DB2 to SQL-Server 2005. We use WAS 5.1. We have problems with insert staments followed by an SELECT @.@.IDENTITY. The sql error is that no result set is returned. The syntax is "<insert statment>;\nSELECT @.@.IDENTITY.
We have no problems with this in the 1.1 version (there we have the known "read date" error).

Joachim

Joachim,

Thank-you for notifying us of this issue. We are investigating this problem.

Jimmy

|||

Hello Joachim,

Can you provide us with a standalone java repro. I'm interested in learning which of the Statement's execute method you are using and how you are using it. I tried to author a quick repro in house but was unsuccessful.

boolean returnValue = stmt.execute("insert into GenKeys values('text')", Statement.RETURN_GENERATED_KEYS);

System.out.println("Returned gen keys: " + returnValue);

rs = stmt.getGeneratedKeys();

while(rs.next())

{

System.out.println("Got back Generated Key = " + rs.getInt(1));

}

BTW: I am also curious to learn the intent of using select @.@.identity. JDBC provides functionality for accessing generated keys via the Statement.getGeneratedKeys() method.

Thanks,

Jaaved

|||

Hi!

We used a PreparedStatment with executeQuery(). It worked well with 1.21. The getGeneratedKeys() method is not supported by websphere 5.1 (wich is an IBM problem...). In 6.0 i think this is fixed. Until then we did find an other solution to our problem:

Code Snippet

public long getGeneratedKey(PreparedStatement ps) throws SQLException {
ResultSet rs = null;
try {
if (ps.getMoreResults()) {
rs = ps.getResultSet();
}
if (rs != null && rs.next()) {
return rs.getLong(1);
}
return -1;
} catch (SQLException e) {
try {
rs.close();
} catch (Exception ignore){}
throw e;
}
}

It′s not perfect but it seems to work (in cases with only one insert followed by an SELECT @.@. IDENTITY). Is the trouble with executeQuery() a bug or is that how it should be?

Joachim