Thanks for the info, however I'm struggling to understand how it applies to
> my case. Following is the piece of code in question, and the error is due
to
> the SELECT following the INSERT (insert works fine; LIGHTBOX_ID is on
> AUTO_INCREMENT) :
>
> // add a LIGHTBOX row ...
> rs = stmt.executeQuery("INSERT INTO LIGHTBOX (CLIENT_ID,
> LIGHTBOX_ID, NAME, TS, IMAGE_COUNT) VALUES ('" + (String)
> session.getValue("clientID") + "', 0, '" +
> req.getParameter("lb_name").trim() + "', NULL, '" +
> sessionLightbox.getNumOfImages() + "')");
>
> // get the LIGHTBOX_ID value just assigned ...
> rs = stmt.executeQuery("SELECT LAST_INSERT_ID() AS ID");
> int iNextId = rs.getInt("ID");
>
> Basically I'm not traversing any returned sets here, just inserted a row,
> determining an allocated ID and would be (!) carrying on inserting into
> another table.
>
> Is there a 'de-facto' reference JDBC driver I should try / use ?
I am very much an SQL newbie, so I wouldn't claim any authority. I'll just
remark
on what occurs to me on your code.
Firstly, I would do the insert with executeUpdate not executeQuery. I am
not
sure what state the ResultSet is left in if you execute an Update as a
Query - though it should be fixed by the second query.
Secondly, if you are using the MM.MySQL JDBC driver, there is a special
facility
for retrieving the Last Insert Id more efficiently. Cast stmt to
org.gjt.mm.mysql.Statement and then call getLastInsertID(). This should
save an
inter-processor call, at the cost of being MySQL dependant.Thus
int iNextID = ((org.gjt.mm.mysql.Statement) stmt).getLastInsertID () ;
That is the way I have solved this problem.
Thirdly - and this is more a query than a criticism - wouldn't it be more
efficient
to do
> rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
> int iNextId = rs.getInt(1);
which saves the text search on the column numbers? It seems to me
reasonable to
"assume" the number 1 between two adjacent lines of code.
Alec
---------------------------------------------------------------------
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
