-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Tea Yu wrote:
Hi there!
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Tea Yu wrote:
Hi! I just tried Windows ME + MySQL Server - 4.0.9/4.0.11 + ConnectorJ - 3.0.4/3.0.6/3.1.0
with an InnoDB table test (id bigint not null auto_increment, name varchar(255)) also tried test (id int not null auto_increment, name varchar(255))
and did an SQLQuery thru JDBC insert into TABLE (name) values ('test'); select last_insert_id() from TABLE;
but got the Exception java.sql.SQLException: Invalid value for getInt() - 'q'
while successive running of program returns 'r', 's', 't'...
Query from console returns the correct result, but it returns n rows in
set
if last_insert_id() is n e.g. if select last_insert_id() = 7, 7 rows are returned in set
Is there a bug in ConnectorJ regarding this... seems nobody else is
having
such a problem...?
I can't reproduce this error with the following test:
public void testLastInsertId() throws Exception { try { stmt.executeUpdate("DROP TABLE IF EXISTS lastInsertTest"); stmt.executeUpdate("CREATE TABLE lastInsertTest (id bigint not null auto_increment primary key, name varchar(255))"); stmt.executeUpdate("INSERT INTO lastInsertTest (name) values ('test')"); rs = stmt.executeQuery("SELECT LAST_INSERT_ID() FROM lastInsertTest"); rs.next(); rs.getInt(1);
} finally { stmt.executeUpdate("DROP TABLE IF EXISTS lastInsertTest"); } }
However, your SQL is a little funny, for example you can't create a table using the SQL you give, as AUTO_INCREMENT columns also have to be the primary key. Also, you call the table 'test', but then refer to it as 'TABLE' when doing your SELECT_LAST_INSERT_ID()...so hopefully these are cut-and-paste errors, otherwise it seems you are not creating the queries you think you are.
Sorry, cause I didn't copy-n-paste those so there were typos, actually I created the table in console:
1) create table test (id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(255), primary key (id));
2) here is the code segment
String insertStmt = "insert into test (name) values ('hi')"; String queryStmt = "select last_insert_id()";
try { stmt.executeUpdate(insertStmt); ResultSet rs = stmt.executeQuery(queryStmt); if(rs.next()) System.out.println("last insert id: " + rs.getInt(1)); } catch (SQLException e) { System.out.println(e); }
if a) I comment out the rs.getInt(1) line, no exception throws... else it throws something like java.sql.SQLException: Invalid value for getInt() - 't' b) if I use getString(1) instead, no exception throws... but it returns "t", "u" or something
3) I tried to insert records thru MySQLCC without errors.
More thoughts?
Regards Tea
The only thing I can guess (because my testsuite is really the same code, I just don't store the queries in variables), is that 'queryStmt' does not hold the query you think it does. Try inspecting it in a debugger or printing it out before you execute the query, to see what query you are really executing.
-mark
- -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mmma
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.orgiD8DBQE+X4FptvXNTca6JD8RAmbvAKCSP6jENF9/DlJQrdTulDmC6A5BsACeOVGS dobDs9BgnVxc2zTxSV18B9k= =x4ct -----END PGP SIGNATURE-----
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
