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 Brice Ruth <bd...@gmail.com> on 2005/01/09 20:27:20 UTC
BLOB/LONGBLOB w/ MySQL & Connector/J
I'm using the latest Connector/J driver (3.1.6) for MySQL 3.23.58
(don't ask - inherited database). Anyway, I've never much used BLOB
fields, but I need to pull some data out of this database.
I went and grabbed 2.0.9 and saw that it had built-in support for
BLOB/CLOB as long as your driver (and database?) were well-behaved.
Of course, the docs haven't quite caught up with the release, so I'm
not sure what qualifies as a well-behaved driver.
I did try a simple test to see if I could just get lucky ... ;)
My JavaBean simply defines three fields as byte[]. The select in my
SqlMap looks like this:
<select id="getImages" resultClass="Image">
SELECT
ean,
picorg AS original,
picthumb AS thumb,
picscale AS scale
FROM
bb_data a
LEFT OUTER JOIN
bb_pic b
ON
a.id=b.id
ORDER BY
ean
LIMIT 1
</select>
Where "Image" is an alias defined in my config. No errors are thrown
by iBATIS - but when I try to do something like getThumb().length - I
get an NPE. The fields picthumb and picscale are BLOBs, picorg is a
LONGBLOB. The other field, ean, is just a string - which is pulled out
correctly.
Any guidance? Been a long time iBATIS user, just not with BLOB fields :)
Thanks!
Brice
Re: BLOB/LONGBLOB w/ MySQL & Connector/J
Posted by Brice Ruth <bd...@gmail.com>.
I got it all working. Ya know, the crazy thing is - I ended up
googling and writing a TypeHandlerCallback and kept getting frustrated
that wasNull kept returning false and getBlob() kept returning null.
Until I started checking my SQL statement against the database ... and
found that the record it was finding (I was using LIMIT 1 to test, so
it just grabbed the first one) - was actually not returning any
data!!!
Ugh! So, when I actually found a record that had valid BLOBs, all of a
sudden, getBlob() worked just fine and when I reverted back to my
original Bean and SqlMap + Config, everything worked magically.
I don't even need to set the javaType or jdbcType ... it figures it
all out, implicitly - without a result map even. Way to go, guys!
Thanks for the responses!
Brice
On Sun, 9 Jan 2005 21:42:46 -0700, Clinton Begin
<cl...@gmail.com> wrote:
> Yes, definitely specify the types as javaType="byte" and
> jdbcType="BLOB". Also be sure to use iBATIS 2.0.9, as it is the first
> to have included BLOB/CLOB type handlers. If that doesn't work (i.e.
> if MySQL doesn't support standard JDBC BLOB API), then you may have to
> write a custom type handler.
>
> Cheers,
> Clinton
>
>
> On Sun, 09 Jan 2005 14:29:20 -0700, Nathan Maves <Na...@sun.com> wrote:
> > Are you using a result map? If so send it out. If you are not then
> > you need to specify the type inline.
> >
> > Nathan
> >
> >
> > On Jan 9, 2005, at 12:27 PM, Brice Ruth wrote:
> >
> > > I'm using the latest Connector/J driver (3.1.6) for MySQL 3.23.58
> > > (don't ask - inherited database). Anyway, I've never much used BLOB
> > > fields, but I need to pull some data out of this database.
> > >
> > > I went and grabbed 2.0.9 and saw that it had built-in support for
> > > BLOB/CLOB as long as your driver (and database?) were well-behaved.
> > >
> > > Of course, the docs haven't quite caught up with the release, so I'm
> > > not sure what qualifies as a well-behaved driver.
> > >
> > > I did try a simple test to see if I could just get lucky ... ;)
> > >
> > > My JavaBean simply defines three fields as byte[]. The select in my
> > > SqlMap looks like this:
> > >
> > > <select id="getImages" resultClass="Image">
> > > SELECT
> > > ean,
> > > picorg AS original,
> > > picthumb AS thumb,
> > > picscale AS scale
> > > FROM
> > > bb_data a
> > > LEFT OUTER JOIN
> > > bb_pic b
> > > ON
> > > a.id=b.id
> > > ORDER BY
> > > ean
> > > LIMIT 1
> > > </select>
> > >
> > > Where "Image" is an alias defined in my config. No errors are thrown
> > > by iBATIS - but when I try to do something like getThumb().length - I
> > > get an NPE. The fields picthumb and picscale are BLOBs, picorg is a
> > > LONGBLOB. The other field, ean, is just a string - which is pulled out
> > > correctly.
> > >
> > > Any guidance? Been a long time iBATIS user, just not with BLOB fields
> > > :)
> > >
> > > Thanks!
> > >
> > > Brice
> >
> >
>
Re: BLOB/LONGBLOB w/ MySQL & Connector/J
Posted by Clinton Begin <cl...@gmail.com>.
Yes, definitely specify the types as javaType="byte" and
jdbcType="BLOB". Also be sure to use iBATIS 2.0.9, as it is the first
to have included BLOB/CLOB type handlers. If that doesn't work (i.e.
if MySQL doesn't support standard JDBC BLOB API), then you may have to
write a custom type handler.
Cheers,
Clinton
On Sun, 09 Jan 2005 14:29:20 -0700, Nathan Maves <Na...@sun.com> wrote:
> Are you using a result map? If so send it out. If you are not then
> you need to specify the type inline.
>
> Nathan
>
>
> On Jan 9, 2005, at 12:27 PM, Brice Ruth wrote:
>
> > I'm using the latest Connector/J driver (3.1.6) for MySQL 3.23.58
> > (don't ask - inherited database). Anyway, I've never much used BLOB
> > fields, but I need to pull some data out of this database.
> >
> > I went and grabbed 2.0.9 and saw that it had built-in support for
> > BLOB/CLOB as long as your driver (and database?) were well-behaved.
> >
> > Of course, the docs haven't quite caught up with the release, so I'm
> > not sure what qualifies as a well-behaved driver.
> >
> > I did try a simple test to see if I could just get lucky ... ;)
> >
> > My JavaBean simply defines three fields as byte[]. The select in my
> > SqlMap looks like this:
> >
> > <select id="getImages" resultClass="Image">
> > SELECT
> > ean,
> > picorg AS original,
> > picthumb AS thumb,
> > picscale AS scale
> > FROM
> > bb_data a
> > LEFT OUTER JOIN
> > bb_pic b
> > ON
> > a.id=b.id
> > ORDER BY
> > ean
> > LIMIT 1
> > </select>
> >
> > Where "Image" is an alias defined in my config. No errors are thrown
> > by iBATIS - but when I try to do something like getThumb().length - I
> > get an NPE. The fields picthumb and picscale are BLOBs, picorg is a
> > LONGBLOB. The other field, ean, is just a string - which is pulled out
> > correctly.
> >
> > Any guidance? Been a long time iBATIS user, just not with BLOB fields
> > :)
> >
> > Thanks!
> >
> > Brice
>
>
Re: BLOB/LONGBLOB w/ MySQL & Connector/J
Posted by Nathan Maves <Na...@Sun.COM>.
Are you using a result map? If so send it out. If you are not then
you need to specify the type inline.
Nathan
On Jan 9, 2005, at 12:27 PM, Brice Ruth wrote:
> I'm using the latest Connector/J driver (3.1.6) for MySQL 3.23.58
> (don't ask - inherited database). Anyway, I've never much used BLOB
> fields, but I need to pull some data out of this database.
>
> I went and grabbed 2.0.9 and saw that it had built-in support for
> BLOB/CLOB as long as your driver (and database?) were well-behaved.
>
> Of course, the docs haven't quite caught up with the release, so I'm
> not sure what qualifies as a well-behaved driver.
>
> I did try a simple test to see if I could just get lucky ... ;)
>
> My JavaBean simply defines three fields as byte[]. The select in my
> SqlMap looks like this:
>
> <select id="getImages" resultClass="Image">
> SELECT
> ean,
> picorg AS original,
> picthumb AS thumb,
> picscale AS scale
> FROM
> bb_data a
> LEFT OUTER JOIN
> bb_pic b
> ON
> a.id=b.id
> ORDER BY
> ean
> LIMIT 1
> </select>
>
> Where "Image" is an alias defined in my config. No errors are thrown
> by iBATIS - but when I try to do something like getThumb().length - I
> get an NPE. The fields picthumb and picscale are BLOBs, picorg is a
> LONGBLOB. The other field, ean, is just a string - which is pulled out
> correctly.
>
> Any guidance? Been a long time iBATIS user, just not with BLOB fields
> :)
>
> Thanks!
>
> Brice