After an insert the just do a "select last_insert_id()"
This will return the value or 0 if no insert has been done with the current session.
It works ok for me with "mysql-connector-java-3.0.1-beta-bin.jar" on tomcat with DBCP.
from sql prompt.
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 0 |
+------------------+
1 row in set (0.01 sec)
Simon
Rodney Waldhoff wrote:
You can get to the underlying (driver specific) Statement or
PreparedStatement objects by invoking the getDelegate or
getInnermostDelegate methods of Delegating[Prepared]Statement. For
example:
java.sql.Statement jdbcstmt = ....
org.apache.commons.dbcp.DelegatingStatement delstmt =
(org.apache.commons.dbcp.DelegatingStatement)jdbcstmt;
com.mysql.jdbc.Statement mysqlstmt =
(com.mysql.jdbc.Statement)delstmt.getInnermostDelegate();
(you could make that more concise of course)
I'd recommend making the code work in both the pooled and unpooled case,
for example, but adding a method like:
public com.mysql.jdbc.Statement getMySqlStatement(java.sql.Statement stmt) {
if(stmt instanceof com.mysql.jdbc.Statement) {
return (com.mysql.jdbc.Statement)stmt;
} else if(stmt instanceof DelegatingStatement) {
return getMySqlStatement(
((DelegatingStatement)stmt).getInnermostDelegate());
} else if(null == stmt) {
return null;
} else {
throw new SomeException("Not a MySqlStatement.");
}
}
Be careful never to close the underlying statement out from under the
delegating one.
Alternatively, you could stick to pure JDBC/SQL, but I don't know if MySql
supports another mechanism for getting to the auto generated ids.
On Thu, 7 Nov 2002, Michael Bowman wrote:
Hello to all,
Has anyone had any success retrieving the insert key on a MySQL
auto_increment field? The approach recommended in the MySQL javadocs
says:
Any IDs generated for AUTO_INCREMENT fields can be retrieved
* by casting this Statement to org.gjt.mm.mysql.Statement and
* calling the getLastInsertID() method.
Actually, the package has changed from org.gjt.mm.mysql to
com.mysql.jdbc. Even with this change, however, the cast throws
exceptions when using a DBCP statement. Anyone know of workarounds?
Thanks,
Michael
--
To unsubscribe, e-mail: <mailto:commons-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail: <mailto:commons-user-help@;jakarta.apache.org>
-- To unsubscribe, e-mail: <mailto:commons-user-unsubscribe@;jakarta.apache.org> For additional commands, e-mail: <mailto:commons-user-help@;jakarta.apache.org>
