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 Jason Sheedy <mo...@gmail.com> on 2008/10/31 01:01:01 UTC
blob vs clob in mysql
Hi,
I'm new to this list, so please excuse be kind. I'm trying to get my head
around using blobs with ibatis/mysql and have run into an issue when trying
to read the blobs back into my result map.
For some unknown reason using BLOB or LONGVARBINARY for the jdbcType on my
result map throws a strange exception. The binary data is written
correctly, but when i try and read it in i get the following exception.
com.ibatis.sqlmap.client.SqlMapException: Error getting nested result map
values
for 'attachments'. Cause: java.sql.SQLException: Not a valid escape
sequence:
<resultMap id="compositeAttachment" class="........EQAttach" >
<result column="eq_attach_id" property="eqAttachId" jdbcType="INTEGER"
/>
<result column="eq_id" property="eqId" jdbcType="INTEGER" />
<result column="file_name" property="fileName" jdbcType="VARCHAR" />
<result column="file_size" property="size" jdbcType="SMALLINT" />
<result column="file_data" property="fileContent" jdbcType="BLOB" />
</resultMap>
The really strange thing is that everything works ok if I user CLOB as the
jdbcType. My data type on the mysql field is blob.
Any tips would be appreciated.
--
Jason Sheedy
www.bytespring.com
www.jmpj.net
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Re: blob vs clob in mysql
Posted by Jeff Butler <je...@gmail.com>.
With the combination of byte[] and CLOB, iBATIS should use the
ByteArrayTypeHandler. This type hander executes the JDBC call
ResultSet.getBytes().
With the combination of byte[] and BLOB or LONGVARBINARY, iBATIS
should use BlobTypeHandlerCallback. This type handler basically
executes the JDBC call ResultSet.getBlob().getBytes().
It might be enlightening to know where the exception is being thrown.
That might help track down the error.
Jeff Butler
On Fri, Oct 31, 2008 at 10:20 AM, Jason Sheedy <mo...@gmail.com> wrote:
> Hi Jeff,
> the result type is indeed byte[]. The strange thing is that it works fine
> when using CLOB as the jdbcType, but not BLOG or LONGVARBINARY. I used
> abator to create the initial mapping, but I'm actually a bit unclear about
> the purpose of LONGVARBINARY. Any idea what the difference is between that
> and BLOG? I know CLOB is supposed to be used for character data...
>
> Cheers,
>
> Jason
>
> 2008/11/1 Jeff Butler <je...@gmail.com>
>>
>> What is the Java type of the file_data propoerty in your result
>> class? Should be byte[] for a BLOB.
>>
>> Jeff Butler
>>
>> On Thu, Oct 30, 2008 at 7:01 PM, Jason Sheedy <mo...@gmail.com>
>> wrote:
>> > Hi,
>> > I'm new to this list, so please excuse be kind. I'm trying to get my
>> > head
>> > around using blobs with ibatis/mysql and have run into an issue when
>> > trying
>> > to read the blobs back into my result map.
>> >
>> > For some unknown reason using BLOB or LONGVARBINARY for the jdbcType on
>> > my
>> > result map throws a strange exception. The binary data is written
>> > correctly, but when i try and read it in i get the following exception.
>> >
>> > com.ibatis.sqlmap.client.SqlMapException: Error getting nested result
>> > map
>> > values
>> > for 'attachments'. Cause: java.sql.SQLException: Not a valid escape
>> > sequence:
>> >
>> > <resultMap id="compositeAttachment" class="........EQAttach" >
>> > <result column="eq_attach_id" property="eqAttachId"
>> > jdbcType="INTEGER"
>> > />
>> > <result column="eq_id" property="eqId" jdbcType="INTEGER" />
>> > <result column="file_name" property="fileName" jdbcType="VARCHAR" />
>> > <result column="file_size" property="size" jdbcType="SMALLINT" />
>> > <result column="file_data" property="fileContent" jdbcType="BLOB" />
>> > </resultMap>
>> >
>> > The really strange thing is that everything works ok if I user CLOB as
>> > the
>> > jdbcType. My data type on the mysql field is blob.
>> >
>> > Any tips would be appreciated.
>> >
>> >
>> > --
>> > Jason Sheedy
>> > www.bytespring.com
>> > www.jmpj.net
>> >
>> > ---------------------------------------------------------------------------------------------
>> >
>> > ---------------------------------------------------------------------------------------------
>> >
>
>
>
> --
> Jason Sheedy
> www.bytespring.com
> www.jmpj.net
> ---------------------------------------------------------------------------------------------
> ---------------------------------------------------------------------------------------------
>
Re: blob vs clob in mysql
Posted by Jason Sheedy <mo...@gmail.com>.
Hi Jeff,
the result type is indeed byte[]. The strange thing is that it works fine
when using CLOB as the jdbcType, but not BLOG or LONGVARBINARY. I used
abator to create the initial mapping, but I'm actually a bit unclear about
the purpose of LONGVARBINARY. Any idea what the difference is between that
and BLOG? I know CLOB is supposed to be used for character data...
Cheers,
Jason
2008/11/1 Jeff Butler <je...@gmail.com>
> What is the Java type of the file_data propoerty in your result
> class? Should be byte[] for a BLOB.
>
> Jeff Butler
>
> On Thu, Oct 30, 2008 at 7:01 PM, Jason Sheedy <mo...@gmail.com>
> wrote:
> > Hi,
> > I'm new to this list, so please excuse be kind. I'm trying to get my head
> > around using blobs with ibatis/mysql and have run into an issue when
> trying
> > to read the blobs back into my result map.
> >
> > For some unknown reason using BLOB or LONGVARBINARY for the jdbcType on
> my
> > result map throws a strange exception. The binary data is written
> > correctly, but when i try and read it in i get the following exception.
> >
> > com.ibatis.sqlmap.client.SqlMapException: Error getting nested result map
> > values
> > for 'attachments'. Cause: java.sql.SQLException: Not a valid escape
> > sequence:
> >
> > <resultMap id="compositeAttachment" class="........EQAttach" >
> > <result column="eq_attach_id" property="eqAttachId"
> jdbcType="INTEGER"
> > />
> > <result column="eq_id" property="eqId" jdbcType="INTEGER" />
> > <result column="file_name" property="fileName" jdbcType="VARCHAR" />
> > <result column="file_size" property="size" jdbcType="SMALLINT" />
> > <result column="file_data" property="fileContent" jdbcType="BLOB" />
> > </resultMap>
> >
> > The really strange thing is that everything works ok if I user CLOB as
> the
> > jdbcType. My data type on the mysql field is blob.
> >
> > Any tips would be appreciated.
> >
> >
> > --
> > Jason Sheedy
> > www.bytespring.com
> > www.jmpj.net
> >
> ---------------------------------------------------------------------------------------------
> >
> ---------------------------------------------------------------------------------------------
> >
>
--
Jason Sheedy
www.bytespring.com
www.jmpj.net
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Re: blob vs clob in mysql
Posted by Jeff Butler <je...@gmail.com>.
What is the Java type of the file_data propoerty in your result
class? Should be byte[] for a BLOB.
Jeff Butler
On Thu, Oct 30, 2008 at 7:01 PM, Jason Sheedy <mo...@gmail.com> wrote:
> Hi,
> I'm new to this list, so please excuse be kind. I'm trying to get my head
> around using blobs with ibatis/mysql and have run into an issue when trying
> to read the blobs back into my result map.
>
> For some unknown reason using BLOB or LONGVARBINARY for the jdbcType on my
> result map throws a strange exception. The binary data is written
> correctly, but when i try and read it in i get the following exception.
>
> com.ibatis.sqlmap.client.SqlMapException: Error getting nested result map
> values
> for 'attachments'. Cause: java.sql.SQLException: Not a valid escape
> sequence:
>
> <resultMap id="compositeAttachment" class="........EQAttach" >
> <result column="eq_attach_id" property="eqAttachId" jdbcType="INTEGER"
> />
> <result column="eq_id" property="eqId" jdbcType="INTEGER" />
> <result column="file_name" property="fileName" jdbcType="VARCHAR" />
> <result column="file_size" property="size" jdbcType="SMALLINT" />
> <result column="file_data" property="fileContent" jdbcType="BLOB" />
> </resultMap>
>
> The really strange thing is that everything works ok if I user CLOB as the
> jdbcType. My data type on the mysql field is blob.
>
> Any tips would be appreciated.
>
>
> --
> Jason Sheedy
> www.bytespring.com
> www.jmpj.net
> ---------------------------------------------------------------------------------------------
> ---------------------------------------------------------------------------------------------
>