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
---------------------------------------------------------------------
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
