----- Original Message -----
From: "Chris Tucker" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 29, 2001 3:16 AM
Subject: Re: jsp-mysql auto_increment
> 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.
Do you really need to wrap it in a transaction ? Having to change
the table types that mySQL uses is a pain if your not DBA inclined.
My understanding of LAST_INSERT_ID() is that you
do not need to use transactions for the following reasons:
1) The LAST_INSERT_ID() is tied to the connection that performed
the insert i.e. you need to use the same connection that you did
the insert with to get the last insert id made using that connection.
2) mySQL doesn't support transactions by default but does
support LAST_INSERT_ID() therefore it does not require transactions.
I know that Oracle, Sybase and mySQL use the same approach
where the last auto increment id is tied to the connection so what
I have done in the passed is to have my code detect which DB I am
using and use the approriate query to select the last insert id i.e.
switch(dbType) {
case ORACLE:
sql = "SELECT seq.currval FROM DUAL"
break;
case SYBASE:
sql = "SELECT id FROM my_table WHERE id=@@identity"
break;
case MYSQL:
sql = "SELECT id FROM my_table WHERE id=LAST_INSERT_ID()"
break;
}
I actually prefer the idea of having an SQL Manager and SQLGenerator
Interface with different implemenations for differenet DBs. This mean
you can support new DBs by just coding the new implementation of the
SQLGenerator put it in you class path.
>
> --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
>
===========================================================================
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
