As AUTO_INCREMENT is also proprietary, there's not an especially convincing
reason for not using LAST_INSERT_ID() too. :-) The best way to get the last
insert id in a guaranteed fashion is to use transactions (available with the
appropriate table types since MySQL 3.3x, I think). Something along the
lines of the following should work:
=======
boolean commStatus = conn.getAutoCommit();
conn.setAutoCommit(false);
sth = conn.createStatement();
sql = "INSERT INTO mytable VALUES ('somevalue')";
sth.executeUpdate(sql);
sql = "SELECT LAST_INSERT_ID() AS id FROM mytable";
rs = sth.executeQuery(sql);
long id = rs.getLong("id");
conn.commit();
conn.setAutoCommit(commStatus);
========
I have a feeling there's another approach I've used to this before (perhaps
packaging both SQL statements into one query), but a transactional approach
is really the safest as you are guaranteed ACID properties, which will make
sure you get the last insert id for your particular insert.
--Chris
-----Original Message-----
From: A mailing list about Java Server Pages specification and reference
[mailto:[EMAIL PROTECTED]]On Behalf Of Joe Cheng
Sent: Wednesday, November 28, 2001 10:53 AM
To: [EMAIL PROTECTED]
Subject: Re: jsp-mysql auto_increment
Without using the LAST_INSERT_ID() (which is proprietary, and I don't know
if there is a way to get to it through JDBC...??), there are a couple of
ugly ways to do it that I know of.
One way is to do the SELECT MAX, but also include a WHERE clause that
includes all of the info you've just inserted. For example:
INSERT INTO users (username, password, email) values ('john', 'doe',
'[EMAIL PROTECTED]');
SELECT MAX(userid) AS newid FROM users WHERE username = 'john' AND password
= 'doe' AND email = '[EMAIL PROTECTED]';
This will work especially well if one or more of the columns being inserted
have a unique index. Then there's a pretty small chance of it returning the
wrong value--only thing I can think of is if, between those two commands,
another user manages to remove your row and insert another one that is
identical except for the userid. Pretty darn unlikely.
Another way is to include a unique id (GUID) column in your table, besides
your primary key. This is a really ugly solution, but it can work well if
you have a good GUID algorithm.
for example, here's a really bad GUID algorithm:
public String getGUID()
{
return (System.currentTimeMillis() *
java.util.Random.nextGaussian()) + "";
}
The java code would look like this:
String guid = GUID.getGUID();
String insertQuery = "INSERT INTO users (..., guid) values (..., '" + guid +
"';";
String idQuery = "SELECT MAX(userid) AS newid FROM users WHERE guid = '" +
guid + "';";
Hope that helps.
-jmc
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST
DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://archives.java.sun.com/jsp-interest.html
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.jsp
http://www.jguru.com/faq/index.jsp
http://www.jspinsider.com
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://archives.java.sun.com/jsp-interest.html
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.jsp
http://www.jguru.com/faq/index.jsp
http://www.jspinsider.com
