Re: execute SQL statement that are not in a map
At 08:47 AM 3/27/2005 -0700, Brandon Goodin wrote: I don't think you need to worry about LAST_INSERT_ID so long as you have the same connection that inserted it. With IBatis this is not a problem. The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. The paragraph above assumes that the client always holds onto the same database connection. In the case where a framework (i.e. Spring) is managing the database connections, perhaps using commons-dbcp, wouldn't it be possible that it would grab a connection from the pool, execute the insert, then release it back to the pool? John Fereira [EMAIL PROTECTED] Ithaca, NY
Re: execute SQL statement that are not in a map
Brandon Goodin: thanks for the explanation I was worried about my practice to do same. Example using hsqldb I have: insert id=insertPerson parameterClass=Person INSERT INTO PERSONS (FIRSTNAME, LASTNAME) VALUES (#firstName#, #lastName#) selectKey resultClass=int keyProperty=id CALL IDENTITY() /selectKey /insert wich return the inserted auto-incremented (identity) key for column 'id' key = (Integer) sqlMap.insert(insertPerson, newPerson); replacing 'CALL IDENTITY' with select 'LAST_INSERT_ID()' should work for MySql Thanks for the clarification jfz. On Sun, 27 Mar 2005 08:47:43 -0700, Brandon Goodin [EMAIL PROTECTED] wrote: I don't think you need to worry about LAST_INSERT_ID so long as you have the same connection that inserted it. With IBatis this is not a problem. The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. Full text from manual is below. Brandon From the MySQL manual: LAST_INSERT_ID() , LAST_INSERT_ID(expr) Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql SELECT LAST_INSERT_ID(); - 195 The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. The value of LAST_INSERT_ID() is not changed if you update the AUTO_INCREMENT column of a row with a non-magic value (that is, a value that is not NULL and not 0). If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row. The reason for this is to make it possible to easily reproduce the same INSERT statement against some other server. On Sun, 27 Mar 2005 08:55:15 -0500, John Fereira [EMAIL PROTECTED] wrote: At 01:58 PM 3/23/2005 +, James, Steven wrote: hi steven it was actually $$ not ##. this works for me it will also get the last insert key change to suit db ie @@identity sqlserver insert id=test parameterClass=string $value$ selectKey resultClass=int select LAST_INSERT_ID() /selectKey /insert You might want to be careful about using the LAST_INSERT_ID(). That function returns the last auto_incremented value maintained by the server. In a multiuser environment their is the possibility of a race condition. The function is also not portable. As an alternative I create a Sequence table containing name, id fields. When an insert to a table is performed, first query the sequence table to get the last id, use it for the value for the primary key (don't use an auto-increment value), then increment the value after the insert succeeds. John Fereira [EMAIL PROTECTED] Ithaca, NY
Re: execute SQL statement that are not in a map
The SQLMap either retrieves or receives a connection from the datasource. That connection remains on the thread for the life of the SQLMap execution. The connection is only released when everything is complete. So, if you used the selectKey there should be no risk of failure for LAST_INSERT_ID. Also, when a user calls startTransaction in IBatis it will retrieve a connection at that time that will only be returned when endTransaction is called. If you were to end a transaction and then start another one... you would have problems. This problem would apply also in the case where you did not use ibatis' internal transaction demarkation and tried to retrieve the last inserted id in another sql map call. I'm not sure how Spring handles it connections inside transactions. You would have to discuss that on their forums. I'd love to hear the result of that question. However, everything else should function in identical manner. Bottom line is... selectKey wist LAST_INSERT_ID is safe especially if you only using IBatis SQLMap. Brandon On Sun, 27 Mar 2005 11:08:11 -0500, John Fereira [EMAIL PROTECTED] wrote: At 08:47 AM 3/27/2005 -0700, Brandon Goodin wrote: I don't think you need to worry about LAST_INSERT_ID so long as you have the same connection that inserted it. With IBatis this is not a problem. The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. The paragraph above assumes that the client always holds onto the same database connection. In the case where a framework (i.e. Spring) is managing the database connections, perhaps using commons-dbcp, wouldn't it be possible that it would grab a connection from the pool, execute the insert, then release it back to the pool? John Fereira [EMAIL PROTECTED] Ithaca, NY
RE: execute SQL statement that are not in a map
hi steven
it was actually $$ not ##.
this works for me it will also get the last insert key change to suit db ie
@@identity sqlserver
insert id=test parameterClass=string
$value$
selectKey resultClass=int
select LAST_INSERT_ID()
/selectKey
/insert
java code
public void test(String x) throws SQLException {
// TODO Auto-generated method stub
Integer z = (Integer)sqlMap.insert(test,x);
//z is now the key for the row you inserted
}
steve
-Original Message-
From: Steven Pannell [mailto:[EMAIL PROTECTED]
Sent: Wed 3/23/2005 12:36 PM
To: 'ibatis-user-java@incubator.apache.org'
Subject: RE: execute SQL statement that are not in a map
Hi,
I tried this to execute my dynamic SQL statement but does not work. anyone
an idea how to resolve this???
The SQL statements have to be built in the java code, so I cannot use a
standard map.
statement id=execute-rule parameterClass=string
resultClass=long
![CDATA[
#value#
]]
/statement
//Java code
client.insert(execute-rule,insert into myTable(id) values (1));
THX,
steve.
-Original Message-
From: James, Steven [mailto:[EMAIL PROTECTED]
Sent: 22 March 2005 17:00
To: ibatis-user-java@incubator.apache.org
Subject: RE: execute SQL statement that are not in a map
create a generic statement in a sqlmap
statement id=Execute parameterClass=string
#string
/statement
-Original Message-
From: Steven Pannell [mailto:[EMAIL PROTECTED]
Sent: Tue 3/22/2005 3:16 PM
To: 'ibatis-user-java@incubator.apache.org'
Subject: execute SQL statement that are not in a map
Hi,
I build some SQL statements in java and want to execute them on the DB. As
I don't have a iBatis-map for these (they are build dynamically) what is the
best way to execute the statements on the db. the SqlMapClient does not have
any kind of execute method. The only thing I came up with at the moment was:
client.getDataSource().getConnection().prepareStatement(mysql).executeQuery(
)
Bit nasty...
Any ideas?
Thanks,
Steve.
This e-mail and any attachment is for authorised use by the intended
recipient(s) only. It may contain proprietary material, confidential
information and/or be subject to legal privilege. It should not be copied,
disclosed to, retained or used by, any other party. If you are not an
intended recipient then please promptly delete this e-mail and any
attachment and all copies and inform the sender. Thank you.
This e-mail and any attachment is for authorised use by the intended
recipient(s) only. It may contain proprietary material, confidential
information and/or be subject to legal privilege. It should not be copied,
disclosed to, retained or used by, any other party. If you are not an intended
recipient then please promptly delete this e-mail and any attachment and all
copies and inform the sender. Thank you.winmail.dat
execute SQL statement that are not in a map
Hi, I build some SQL statements in java and want to execute them on the DB. As I don't have a iBatis-map for these (they are build dynamically) what is the best way to execute the statements on the db. the SqlMapClient does not have any kind of execute method. The only thing I came up with at the moment was: client.getDataSource().getConnection().prepareStatement(mysql).executeQuery( ) Bit nasty... Any ideas? Thanks, Steve.
RE: execute SQL statement that are not in a map
create a generic statement in a sqlmap statement id=Execute parameterClass=string #string /statement -Original Message- From: Steven Pannell [mailto:[EMAIL PROTECTED] Sent: Tue 3/22/2005 3:16 PM To: 'ibatis-user-java@incubator.apache.org' Subject: execute SQL statement that are not in a map Hi, I build some SQL statements in java and want to execute them on the DB. As I don't have a iBatis-map for these (they are build dynamically) what is the best way to execute the statements on the db. the SqlMapClient does not have any kind of execute method. The only thing I came up with at the moment was: client.getDataSource().getConnection().prepareStatement(mysql).executeQuery( ) Bit nasty... Any ideas? Thanks, Steve. This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.winmail.dat
Re: execute SQL statement that are not in a map
Oh, and you'll probably need to specify a resultClass (or resultMap, if you have one setup). Specifying remapResults=true won't hurt, either (especially with a resultMap specification). On Tue, 22 Mar 2005 11:29:39 -0600, Brice Ruth [EMAIL PROTECTED] wrote: you'll probably need to reference the string as $value$, unless you're using a syntax I'm not familiar with. On Tue, 22 Mar 2005 16:00:05 -, James, Steven [EMAIL PROTECTED] wrote: create a generic statement in a sqlmap statement id=Execute parameterClass=string #string /statement -Original Message- From: Steven Pannell [mailto:[EMAIL PROTECTED] Sent: Tue 3/22/2005 3:16 PM To: 'ibatis-user-java@incubator.apache.org' Subject: execute SQL statement that are not in a map Hi, I build some SQL statements in java and want to execute them on the DB. As I don't have a iBatis-map for these (they are build dynamically) what is the best way to execute the statements on the db. the SqlMapClient does not have any kind of execute method. The only thing I came up with at the moment was: client.getDataSource().getConnection().prepareStatement(mysql).executeQuery( ) Bit nasty... Any ideas? Thanks, Steve. This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
