APEX

I'm using version 2.0.13 of mmmysql.The change log says Rudimentary version of Statement.getGeneratedKeys() from JDBC-3.0 now implemented (you need to be using JDK-1.4 for this to work, I believe)

I've previously found it works with java.sql.Statement but not with java.sql.PreparedStatement.

From the change log it looks like he hasn't yet implemented getGeneratedKeys() for PerparedStatements.

Col

What does that mean? Should I use something like that:

Statement ps = (Statement)connection.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);

Isn't PreparedStatement an extended Statement? OK, it' an interface, but Java should use Statement.getGeneratedKeys, if PreparedStatement.getGeneratedKeys doesn't exists, or what?

???? So, how do you test, that the method getGeneratedKeys works ??? Could I use the code to get the ID or is it only a dummy?

Don't get confused between interface inheritance and implementation inheritance. There is no method Statement.getGeneratedKeys(), only a declaration of a method. The actual method is defined in some class (lets call it MyStatement) in the MMMySQL JDBC driver. There is also a MMMySQL class (lets call it MyPreparedStatement) that implements PreparedStatement. Just because PreparedStatement extends Statement it doesn't follow that MyPreparedStatement extends MyStatement. Thats an implementation issue.

> You test it by calling it. You tried this and it doesn't work for PreparedStatements and no amount of casting is going to make it work. You'll have to wait for the driver to implement it or switch to another driver.

I'd suggest putting up a feature request at the MMMySql site so that the developer knows that somebody needs this functionality. The driver is under active development at the moment.

Col

Ha, thats the code I should use:

Statement ps = connection.createStatement();
try {
result = ps.execute(query);
ResultSet rs = ps.getGeneratedKeys();

int columns = rs.getMetaData().getColumnCount();

while (rs.next()) {
for (int i=1;i<=columns;i++) {
System.out.println(rs.getMetaData().getColumnName(i)+":"+rs.getString(i));
}
}
rs.close();
rs = null;
} catch (NullPointerException npe) {
error("NullPointerException durch die Query '"+query+"'",npe);
}
result = true;

ps.close();
ps = null;

It's specific to mySql but there's a function for the last insert ID, execute

SELECT LAST_INSERT_ID()

as a query.

Unlike trying to look for the max value this is reasonably thread-safe as the value is recorded per connection (so if some other task inserts another record between the insert and the select you should still get the your own value).

The value of getGeneratedKeys() over using malcolm's suggestion... which is still a very valid one... is that any changes of database do not require any changes to your code. As was mentioned, getting the last ID is usually DB dependant... SQLServer uses SELECT @@IDENTITY, etc. As long as you continue to use drivers that support getGeneratedKeys(), then switching databases is less of an issue.

Say we have an online Form with

first name
last name
address line1
address line2
state
zip

And there are two tables in the DB

PERSON (ID,FNAME,LNAME,ADDRESSID)
ADDRESS(ADDRESSID,ADDRLINE1,ADDRLINE2,STATE,ZIP)

The data from needs to be entered into the above 2
tables and RI needs to maintained with the correct
key.

Here are the logical steps:
1. Get the address data from the form
2. Insert the address data into the ADDRESS table
3. Get the ID from the ADDRESS table, and store it in
memory
4. Get the info of the person from the HTML form
5. Insert the person info into the PERSON table, along
with the ADDRESS ID

Is this is the best approach ?

In JDBC 3.0 (beta) we can call getGeneratedKeys method
on the ResultSet returned from the executeUpdate()
method on the Statement object. And use it for the
next entry.

With JDBC 2.0, has anyone implemented something like this.

-Rohit

But this is another transaction - and a simple getGeneratedKeys() should be much faster...

Statement stmt = conn.createStatement();
PreparedStatement ps = conn.prepareStatement("INSERT into SCHEDULE( SHIFT_DATE,SHIFT_NUM,USERS,CREATE_TIME) VALUES (?,?,?,getDate())", PreparedStatement.RETURN_GENERATED_KEYS );

ps.setDate(1,shiftDate);
ps.setString(2, shift);
ps.setString(3, userName);
ps.executeUpdate();
ResultSet resultset = ps.getGeneratedKeys();

if (resultset.next())
{
// retrieve the new ID
sch.setScheduleID(resultset.getInt(1));
}
ps.close();
stmt.close();

no matter how I tried to use java.sql.Statement.getGeneratedKeys() I could not get the information I needed (last inserted id).
Maybe it is a problem of versions (mysql-4.1 / mysql connector 3.1.10) but getGeneratedKeys() always returns an empty ResultSet.

So if I have to go to a platform dependant solution, here's a way to do it:

			Statement s = connection.createStatement();
			s.execute(sql);
			if (s instanceof com.mysql.jdbc.Statement) {
				com.mysql.jdbc.Statement mysqls = (com.mysql.jdbc.Statement)s;
				return mysqls.getLastInsertID();
			} else {
				return -1l;
			}

Hoping it helps some lost souls.

Hi I was getting this same error and searched on net but was not able to get satisfactory solution. Therefore I am giving u the solution which worked fine for me :)
If u are using MS SQL Server 2005 just make sure u register u r driver as
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
where as if u r using MS SQL Server 2000 use
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
and use the latest jdbc driver and now u will be able to use
Statement.RETURN_GENERATED_KEYS and getGeneratedKeys().

Hope this will be usefull for all :)
Cheers