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
