Hi, I'm new to SQL Maps and I have been successful at getting it to do what I wanted until I hit a rather ugly hitch. One of my insert statements appeared to be working fine, but nothing was getting inserted into the database. At first I thought there was something wrong with the way I was supplying the parameters, but I trimmed the statement down until it was nothing more than the following:
<insert id="submitAttachment" parameterClass="java.util.Map"> insert into RESPONSE_ATTACHMENTS values(NULL,'2','2','30') </insert>
The console log showed this, as if everything was working normally:
<DEBUG 21:48:11,444 SimpleDataSource:563> Checked out connection 8441896 from pool.
<DEBUG 21:48:11,447 Connection:42> {conn-100087} Connection
<DEBUG 21:48:11,449 PreparedStatement:48> {pstm-100088} PreparedStatement: insert into RESPONSE_ATTACHMENTS values(NULL,'2','2','30')
<DEBUG 21:48:11,546 PreparedStatement:49> {pstm-100088} Parameters: []
<DEBUG 21:48:11,548 PreparedStatement:50> {pstm-100088} Types: []
<DEBUG 21:48:11,709 SimpleDataSource:527> Returned connection 8441896 to pool.
...but still, no row was being inserted into RESPONSE_ATTACHMENTS. After much hair pulling, I discovered what the problem was. I'm using MySQL 4.0.21, sometimes using MyISAM tables (where full text search is needed) and the rest of the time using InnoDB tables. All my insert statements that inserted data into MyISAM tables worked, but RESPONSE_ATTACHMENTS was an InnoDB table. I converted it to a MyISAM table and submitAttachment immediately started working. Another piece of my application which uses JDBC code to insert rows (via SqlMapSession.getCurrentConnection().prepareStatement()) into a different InnoDB table also works.
Am I missing something here? I searched the mailing list archives for other people with the same problem, but found nothing. It seems like a pretty glaring problem for no one else to have noticed, so I'm guessing it has something to do with my configuration.
I'm using iBATIS 2.0.7, a JDBC transaction manager and a simple datasource with only the JDBC.Driver, JDBC.ConnectionURL, JDBC.Username, and JDBC.Password parameters set.
Any insights would be greatly appreciated.
Thanks, Robin
Hey Robin,
Your problem is to do with transactions, I believe. MyISAM tables don't support transactions (a pox on them and all their kind), but InnoDB tables do. When you insert into a MyISAM table, the data is just written. With an InnoDB table you have to either commit or rollback the change. I think that the MySQL driver will rollback by default, hence your problem. The easiest solution is to try this as a SimpleDataSource property:
<property name="JDBC.DefaultAutoCommit" value="true" />
But if the distinction between transactional and non-transactional table types doesn't mean much to you, take a look at this:
http://dev.mysql.com/books/mysqlpress/mysql-tutorial/ch10.html
HTH, Kris
