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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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 = [EMAIL PROTECTED]
> 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: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
