Announcement Announcement Module
No announcement yet.
iBATIS/MySQL: error in syntax? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • iBATIS/MySQL: error in syntax?

    I'm trying to make BLOB insertion using Spring / iBATIS / MySQL work. Here's what I got so far

    the sqlmap:

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE sqlMap PUBLIC "-// SQL Map 2.0//EN" "" >
    	<typeAlias alias="image" type="pay.dao.ibatis.test.ImageDO" />
    		<resultMap class="image" id="image">
    			<result property="id" column="id" />
    			<result property="desc" column="desc" />
    			<result property="image" column="image" jdbcType="BLOB" javaType="[B" />
    	<insert id="insertRecord" parameterClass="image">
    		insert into pay_testblob (id,desc,image) values (#id#,#desc#,#image#)
    	<update id="updateImage" parameterClass="image">
    		update pay_testblob set image =#image# where id = #id#
    	<select id="getImageById" parameterClass="int" resultClass="java.lang.String">
    		select image from pay_testblob where id = #id#
    The weird part is that I get this error when I invoke the insert :

    2007-07-13 02:19:23,390 main
     [DEBUG] - Database product name cached for DataSource [[email protected]]: name is 'MySQL'
    2007-07-13 02:19:23,390 main
     [DEBUG] - SQL error codes for 'MySQL' found
    2007-07-13 02:19:23,390 main
     [DEBUG] - Translating SQLException with SQL state '42000', error code '1064', message [  
    --- The error occurred in ibatis/image.xml.  
    --- The error occurred while applying a parameter map.  
    --- Check the insertRecord-InlineParameterMap.  
    --- Check the statement (update failed).  
    --- Cause: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc,image) values (1,'image1',_binary'\0JFIF\0\0\0\0\0\0C\0' at line 1]; SQL was [] for task [SqlMapClient operation]
    2007-07-13 02:19:23,390 main
     [DEBUG] org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
    I've successfully mapped other objects using iBATIS and the insert statements are pretty much straight forward. The only thing that is different in this case is that I wasn't using Spring transactions (in fact the tests were not using transactions) in all the other tests I've done and those tests didn't involve BLOBs. Here however, aside from BLOBs, I'm forced to use transactions.