You are viewing a plain text version of this content. The canonical link for it is here.
Posted to taglibs-user@tomcat.apache.org by Rick Ross <rr...@stupendousman.com> on 2003/07/22 23:54:07 UTC
MySQL LAST_INSERT_ID( )
I have a bad feeling about this, but I haven't been able to access the
LAST_INSERT_ID( ) from mySQL.
In mySQL you can have one column that is automatically incremented (usually
the primary key field). You don't specify a value for this field in your
INSERT statement or you specify NULL. Either way, an value is generated for
you and placed into the field. In mySQL, you can retrieve that value from
the LAST_INSERT_ID( ) function. It would look something like this (from the
command line):
mysql> INSERT mytable VALUES (null, 'This', 'That');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT LAST_INSERT_ID() as lastID;
+--------+
| lastID |
+--------+
| 57 |
+--------+
1 row in set (0.00 sec)
So that is clearly quite handy. Now trying to do that in JSTL should be
fairly simple:
<sql:update var="updateResult" >
INSERT myTable VALUES (null, ?,?)
<sql:param value="${param.name}" />
<sql:param value="${param.description}" />
</sql:update>
<sql:query var="idResult">
SELECT LAST_INSERT_ID() as lastID
</sql:query>
<c:set var="id" value="${idResult.rows[0].lastID}" />
And now, 'id' should contain the results of the LAST_INSERT_ID( ) function.
It doesn't. I always get a zero back. Despite the fact that the insert and
select's are both successful.
Lets get a good look at the results of our query:
<c:out value="ID = ${idResult}" /><br/>
<c:out value="ID.rows = ${idResult.rows}" /><br/>
<c:out value="ID.rows[0] = ${idResult.rows[0]}" /><br/>
<c:forEach var="row" items="${idresult.rows}" varStatus="status" >
<c:out value="row=${row} id=${row.lastID}" /><br/>
</c:forEach>
returns this:
ID = org.apache.taglibs.standard.tag.common.sql.ResultImpl@834cfb
ID.rows = [Ljava.util.SortedMap;@330fb9
ID.rows[0] = {lastID=0}
Notice that no rows were processed by the forEach despite the fact that row
0 clearly exists and has a single column result in it.
Does anybody know what's going wrong?
Rick
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: MySQL LAST_INSERT_ID( )
Posted by Rick Ross <rr...@stupendousman.com>.
Correct! and nicely done, I might add.
Thank you.
R
----- Original Message -----
From: "Michael Nascimento Santos" <mi...@hotmail.com>
To: "Tag Libraries Users List" <ta...@jakarta.apache.org>
Sent: Tuesday, July 22, 2003 2:54 PM
Subject: Re: MySQL LAST_INSERT_ID( )
> I guess you must be inside the same transaction to achieve what you want.
> Try to use <sql:transaction> to make both statements run inside the same
> transaction.
>
> []s
> Michael
>
> ----- Original Message -----
> From: "Rick Ross" <rr...@stupendousman.com>
> To: <ta...@jakarta.apache.org>
> Sent: Tuesday, July 22, 2003 6:54 PM
> Subject: MySQL LAST_INSERT_ID( )
>
>
> > I have a bad feeling about this, but I haven't been able to access the
> > LAST_INSERT_ID( ) from mySQL.
> >
> > In mySQL you can have one column that is automatically incremented
> (usually
> > the primary key field). You don't specify a value for this field in
your
> > INSERT statement or you specify NULL. Either way, an value is generated
> for
> > you and placed into the field. In mySQL, you can retrieve that value
from
> > the LAST_INSERT_ID( ) function. It would look something like this (from
> the
> > command line):
> >
> > mysql> INSERT mytable VALUES (null, 'This', 'That');
> > Query OK, 1 row affected (0.00 sec)
> > mysql> SELECT LAST_INSERT_ID() as lastID;
> > +--------+
> > | lastID |
> > +--------+
> > | 57 |
> > +--------+
> > 1 row in set (0.00 sec)
> >
> > So that is clearly quite handy. Now trying to do that in JSTL should be
> > fairly simple:
> >
> > <sql:update var="updateResult" >
> > INSERT myTable VALUES (null, ?,?)
> > <sql:param value="${param.name}" />
> > <sql:param value="${param.description}" />
> > </sql:update>
> >
> > <sql:query var="idResult">
> > SELECT LAST_INSERT_ID() as lastID
> > </sql:query>
> >
> > <c:set var="id" value="${idResult.rows[0].lastID}" />
> >
> > And now, 'id' should contain the results of the LAST_INSERT_ID( )
> function.
> > It doesn't. I always get a zero back. Despite the fact that the insert
> and
> > select's are both successful.
> >
> > Lets get a good look at the results of our query:
> >
> > <c:out value="ID = ${idResult}" /><br/>
> > <c:out value="ID.rows = ${idResult.rows}" /><br/>
> > <c:out value="ID.rows[0] = ${idResult.rows[0]}" /><br/>
> > <c:forEach var="row" items="${idresult.rows}" varStatus="status" >
> > <c:out value="row=${row} id=${row.lastID}" /><br/>
> > </c:forEach>
> >
> > returns this:
> >
> > ID = org.apache.taglibs.standard.tag.common.sql.ResultImpl@834cfb
> > ID.rows = [Ljava.util.SortedMap;@330fb9
> > ID.rows[0] = {lastID=0}
> >
> > Notice that no rows were processed by the forEach despite the fact that
> row
> > 0 clearly exists and has a single column result in it.
> >
> > Does anybody know what's going wrong?
> >
> > Rick
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
Re: MySQL LAST_INSERT_ID( )
Posted by Michael Nascimento Santos <mi...@hotmail.com>.
I guess you must be inside the same transaction to achieve what you want.
Try to use <sql:transaction> to make both statements run inside the same
transaction.
[]s
Michael
----- Original Message -----
From: "Rick Ross" <rr...@stupendousman.com>
To: <ta...@jakarta.apache.org>
Sent: Tuesday, July 22, 2003 6:54 PM
Subject: MySQL LAST_INSERT_ID( )
> I have a bad feeling about this, but I haven't been able to access the
> LAST_INSERT_ID( ) from mySQL.
>
> In mySQL you can have one column that is automatically incremented
(usually
> the primary key field). You don't specify a value for this field in your
> INSERT statement or you specify NULL. Either way, an value is generated
for
> you and placed into the field. In mySQL, you can retrieve that value from
> the LAST_INSERT_ID( ) function. It would look something like this (from
the
> command line):
>
> mysql> INSERT mytable VALUES (null, 'This', 'That');
> Query OK, 1 row affected (0.00 sec)
> mysql> SELECT LAST_INSERT_ID() as lastID;
> +--------+
> | lastID |
> +--------+
> | 57 |
> +--------+
> 1 row in set (0.00 sec)
>
> So that is clearly quite handy. Now trying to do that in JSTL should be
> fairly simple:
>
> <sql:update var="updateResult" >
> INSERT myTable VALUES (null, ?,?)
> <sql:param value="${param.name}" />
> <sql:param value="${param.description}" />
> </sql:update>
>
> <sql:query var="idResult">
> SELECT LAST_INSERT_ID() as lastID
> </sql:query>
>
> <c:set var="id" value="${idResult.rows[0].lastID}" />
>
> And now, 'id' should contain the results of the LAST_INSERT_ID( )
function.
> It doesn't. I always get a zero back. Despite the fact that the insert
and
> select's are both successful.
>
> Lets get a good look at the results of our query:
>
> <c:out value="ID = ${idResult}" /><br/>
> <c:out value="ID.rows = ${idResult.rows}" /><br/>
> <c:out value="ID.rows[0] = ${idResult.rows[0]}" /><br/>
> <c:forEach var="row" items="${idresult.rows}" varStatus="status" >
> <c:out value="row=${row} id=${row.lastID}" /><br/>
> </c:forEach>
>
> returns this:
>
> ID = org.apache.taglibs.standard.tag.common.sql.ResultImpl@834cfb
> ID.rows = [Ljava.util.SortedMap;@330fb9
> ID.rows[0] = {lastID=0}
>
> Notice that no rows were processed by the forEach despite the fact that
row
> 0 clearly exists and has a single column result in it.
>
> Does anybody know what's going wrong?
>
> Rick
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: taglibs-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org