You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Vincent <vi...@xaymaca.com> on 2005/05/10 22:19:13 UTC
Controlling connections
Hello All,
I have a mysql database that I am doing an insert on.
I also require the resultant ID to use in a subsequent insert.
doing an INSERT INTO ... followed by a SELECT LAST_INSERT_ID()
within the same connection will give me back the ID I require.
Most of the time this is working fine in iBatis. Other times...
PreparedStatement: INSERT INTO ...
PreparedStatementLogProxy: {pstm-100071}
ConnectionLogProxy: {conn-100072} Connection
PreparedStatementLogProxy: {pstm-100073} PreparedStatement: SELECT
LAST_INSERT_ID()
ConnectionLogProxy: {conn-100075} Connection
PreparedStatementLogProxy: {pstm-100076} PreparedStatement: INSERT
INTO ...
PreparedStatementLogProxy: {pstm-100076}
Parameters: [0 <-- ouch, this is supposed to be the last_inserted_id !
also note the different connection ids.
Short of converting my tables to innodb and using transactions
is there a simple way to fix this?
Using latest SQL Maps.
Thank You.
--
Plato is my friend, Aristotle is my friend, but my greatest friend is truth.
- Isaac Newton
Re: Controlling connections
Posted by Vincent <vi...@xaymaca.com>.
No, I was not. I just learned about another cool
iBatis feature. :)
Thanks,
Vincent
Brandon Goodin wrote:
> you will get a different connection because it is not the same
> transaction. Once you close your transaction your chances of getting
> the id you want are unlikely. Are you using <selectKey> on your
> inserts?
>
> Brandon
>
> On 5/10/05, Vincent <vi...@xaymaca.com> wrote:
>
>>Hello All,
>>I have a mysql database that I am doing an insert on.
>>I also require the resultant ID to use in a subsequent insert.
>>doing an INSERT INTO ... followed by a SELECT LAST_INSERT_ID()
>>within the same connection will give me back the ID I require.
>>Most of the time this is working fine in iBatis. Other times...
>>
>>PreparedStatement: INSERT INTO ...
>>PreparedStatementLogProxy: {pstm-100071}
>>
>> ConnectionLogProxy: {conn-100072} Connection
>>PreparedStatementLogProxy: {pstm-100073} PreparedStatement: SELECT
>>LAST_INSERT_ID()
>>
>> ConnectionLogProxy: {conn-100075} Connection
>>PreparedStatementLogProxy: {pstm-100076} PreparedStatement: INSERT
>>INTO ...
>>PreparedStatementLogProxy: {pstm-100076}
>>Parameters: [0 <-- ouch, this is supposed to be the last_inserted_id !
>>
>>also note the different connection ids.
>>
>>Short of converting my tables to innodb and using transactions
>>is there a simple way to fix this?
>>
>>Using latest SQL Maps.
>>Thank You.
>>
>>--
>>Plato is my friend, Aristotle is my friend, but my greatest friend is truth.
>>- Isaac Newton
>>
--
Plato is my friend, Aristotle is my friend, but my greatest friend is truth.
- Isaac Newton
Re: Controlling connections
Posted by Brandon Goodin <br...@gmail.com>.
you will get a different connection because it is not the same
transaction. Once you close your transaction your chances of getting
the id you want are unlikely. Are you using <selectKey> on your
inserts?
Brandon
On 5/10/05, Vincent <vi...@xaymaca.com> wrote:
> Hello All,
> I have a mysql database that I am doing an insert on.
> I also require the resultant ID to use in a subsequent insert.
> doing an INSERT INTO ... followed by a SELECT LAST_INSERT_ID()
> within the same connection will give me back the ID I require.
> Most of the time this is working fine in iBatis. Other times...
>
> PreparedStatement: INSERT INTO ...
> PreparedStatementLogProxy: {pstm-100071}
>
> ConnectionLogProxy: {conn-100072} Connection
> PreparedStatementLogProxy: {pstm-100073} PreparedStatement: SELECT
> LAST_INSERT_ID()
>
> ConnectionLogProxy: {conn-100075} Connection
> PreparedStatementLogProxy: {pstm-100076} PreparedStatement: INSERT
> INTO ...
> PreparedStatementLogProxy: {pstm-100076}
> Parameters: [0 <-- ouch, this is supposed to be the last_inserted_id !
>
> also note the different connection ids.
>
> Short of converting my tables to innodb and using transactions
> is there a simple way to fix this?
>
> Using latest SQL Maps.
> Thank You.
>
> --
> Plato is my friend, Aristotle is my friend, but my greatest friend is truth.
> - Isaac Newton
>