DBD::Informix and BLOBs

DBD::Informix and BLOBs

Post by Joel Michae » Tue, 02 Jul 2002 15:59:59



Hi All,

If anyone knows anything about using BLOB fields using DBD::Informix,
specifically reading the data out of them, I'd be very grateful.  I'm
currently trying to extract data out of BLOB fields (they happen to be
images), but it seems as though that the value returned is some kind of
identifier, not the actual data.  I'm trying to use something along the
lines of the following script:

-----
#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("DBI:Informix:dbname", "username", "password");
my $sth = $dbh->prepare("select image, type from images;");
$sth->execute();

        print "image='$row[0]'\ntype=$row[1]\n";

Quote:}

-----

image is a blob field, type is a varchar.  I've tried perl 5.6.1+DBI
1.28+DBD::Informix 1.00.PC2, along with various combinations of older
versions.

If this isn't the right place to be posting this, kindly direct me to the
right place :-)

Regards,
--
Joel Michael

 
 
 

DBD::Informix and BLOBs

Post by Jonathan Stow » Tue, 02 Jul 2002 23:10:53



> Hi All,

> If anyone knows anything about using BLOB fields using DBD::Informix,
> specifically reading the data out of them, I'd be very grateful.  I'm
> currently trying to extract data out of BLOB fields (they happen to be
> images), but it seems as though that the value returned is some kind of
> identifier, not the actual data.  I'm trying to use something along the
> lines of the following script:

> -----
> #!/usr/bin/perl -w

> use strict;
> use DBI;

> my $dbh = DBI->connect("DBI:Informix:dbname", "username", "password");
> my $sth = $dbh->prepare("select image, type from images;");
> $sth->execute();

>    print "image='$row[0]'\ntype=$row[1]\n";
> }

It would be useful to see exactly what output it is that you are
getting.

/J\
--
Jonathan Stowe
Architect, Business Operations and Systems Europe
Netscalibur.   <http://www.netscalibur.com>
Phone: 0870 887 8841  Fax: 0870 887 8867
7

 
 
 

DBD::Informix and BLOBs

Post by Joel Michae » Wed, 03 Jul 2002 07:44:31



> It would be useful to see exactly what output it is that you are
> getting.

This is what I'm getting (I hope it doesn't break anyone's news clients
:)

image='01000000d9c8b7a60900000009000000601e0000f7e83a3b000000000100340800000000000000000000000000000000000000000000000000000000000000000000000000000000                                                                                                                '
type=image/gif
image='01000000d9c8b7a6090000000900000056200000f8e83a3b000000000100340800000000000000000000000000000000000000000000000000000000000000000000000000000000                                                                                                                '
type=image/gif
image='01000000d9c8b7a609000000090000005a200000f9e83a3b000000000100340800000000000000000000000000000000000000000000000000000000000000000000000000000000                                                                                                                '
type=image/gif

If anyone knows what's going on, it'd be great to figure this out.  For
the moment, I think I'll just code in a work-around for the generated
email to simply grab the image off a web server (via the web datablade).

Regards,
--
Joel Michael

 
 
 

DBD::Informix and BLOBs

Post by Andrew Ham » Wed, 03 Jul 2002 10:15:42



>This is what I'm getting

>image='01000000d9c8b7a60900000009000000601e0000f7e83a3b00000000010034080000

0000000000000000000000000000000000000000000000000000000000000000000000000000
'
Quote:>type=image/gif
>image='01000000d9c8b7a6090000000900000056200000f8e83a3b00000000010034080000

0000000000000000000000000000000000000000000000000000000000000000000000000000
'
Quote:>type=image/gif
>image='01000000d9c8b7a609000000090000005a200000f9e83a3b00000000010034080000

0000000000000000000000000000000000000000000000000000000000000000000000000000
'
Quote:>type=image/gif

>If anyone knows what's going on, it'd be great to figure this out.  For
>the moment, I think I'll just code in a work-around for the generated
>email to simply grab the image off a web server (via the web datablade).

That looks a hell of a lot like the LOCATOR that is used by Informix API to
reference blobs. I don't use blobs but I do know that they are referenced by
these locators, and the locators are not the data.

For a relatively "pure" understanding of blobs and their locators, read the
4GL manual on the subject (had to do that last week by coincidence) so that
you understand the subject. I suppose you are already familiar with the 4GL
way of blobbing?

With DBD::Informix, there is some detail written either into the release
notes, or the README files or something. I'm not sure if they work or they
fall into "some more work to be done". Go to www.google.com and do a group
search of this newsgroup for any messages written by Jonathan Leffler wot
mention DBD. I know he's commented about it several times, and posted some
sample code somewhere. No doubt he'll comment when the sun rises on his side
of the globe.

 
 
 

DBD::Informix and BLOBs

Post by Brad Patterso » Wed, 03 Jul 2002 11:55:25


According to the README distributed with DBD::Informix...

    DBD::Informix, Version 1.00, provides limited support for user-defined
    data types (UDTs), treating them as CHAR(255).  To handle BLOBs and
    CLOBs, use LOTOFILE() when you fetch the data and FILETOBLOB() or
    FILETOCLOB() when you insert data.  To handle nonblob UDTs that exceed
    255 characters in length, use server-side cast to lvarchar, as in

        select mycol::lvarchar from mytab;
    ...

Basically, unload the blob/clob to the filesystem with LOTOFILE and
manipulate that file in your program.  The Large Object Locater Datablade
User's Guide and the Informix Guide to SQL: Tutorial (version 9.3) have
information on the LOTOFILE and FILETOBLOB/CLOB functions, both available at
http://www.informix.com/answers/

I use DBD::Informix to go the other direction... Loading large text objects
into clob columns with the FILETOCLOB function.  Works quite well.

Hope this helps,
Brad Patterson



>> This is what I'm getting

>> image='01000000d9c8b7a60900000009000000601e0000f7e83a3b00000000010034080000
> 0000000000000000000000000000000000000000000000000000000000000000000000000000
> '
>> type=image/gif
>> image='01000000d9c8b7a6090000000900000056200000f8e83a3b00000000010034080000
> 0000000000000000000000000000000000000000000000000000000000000000000000000000
> '
>> type=image/gif
>> image='01000000d9c8b7a609000000090000005a200000f9e83a3b00000000010034080000
> 0000000000000000000000000000000000000000000000000000000000000000000000000000
> '
>> type=image/gif

>> If anyone knows what's going on, it'd be great to figure this out.  For
>> the moment, I think I'll just code in a work-around for the generated
>> email to simply grab the image off a web server (via the web datablade).

> That looks a hell of a lot like the LOCATOR that is used by Informix API to
> reference blobs. I don't use blobs but I do know that they are referenced by
> these locators, and the locators are not the data.

> For a relatively "pure" understanding of blobs and their locators, read the
> 4GL manual on the subject (had to do that last week by coincidence) so that
> you understand the subject. I suppose you are already familiar with the 4GL
> way of blobbing?

> With DBD::Informix, there is some detail written either into the release
> notes, or the README files or something. I'm not sure if they work or they
> fall into "some more work to be done". Go to www.google.com and do a group
> search of this newsgroup for any messages written by Jonathan Leffler wot
> mention DBD. I know he's commented about it several times, and posted some
> sample code somewhere. No doubt he'll comment when the sun rises on his side
> of the globe.

 
 
 

DBD::Informix and BLOBs

Post by Joel Michae » Wed, 03 Jul 2002 14:29:42



> According to the README distributed with DBD::Informix...

>     DBD::Informix, Version 1.00, provides limited support for
>     user-defined data types (UDTs), treating them as CHAR(255).  To
>     handle BLOBs and CLOBs, use LOTOFILE() when you fetch the data and
>     FILETOBLOB() or FILETOCLOB() when you insert data.  To handle
>     nonblob UDTs that exceed 255 characters in length, use server-side
>     cast to lvarchar, as in

>         select mycol::lvarchar from mytab;
>     ...

> Basically, unload the blob/clob to the filesystem with LOTOFILE and
> manipulate that file in your program.  The Large Object Locater
> Datablade User's Guide and the Informix Guide to SQL: Tutorial (version
> 9.3) have information on the LOTOFILE and FILETOBLOB/CLOB functions,
> both available at http://www.informix.com/answers/

Ah, I noticed this a couple of minutes after I posted.  Reading up a bit
more on LOTOFILE (using the SQL Syntax guide), I have got this working
perfectly, and am now sending emails with images attached from the
database.

Many thanks to those that replied.  If any of you are in Brisbane, get in
contact with me and I'll buy you a beer or three :-)

--
Joel Michael                                    |  Phone:       +61 7 3367 3555
Systems Administrator                   |  Fax:         +61 7 3367 3544
WorldHosting.org Pty. Ltd.              |  Mobile:      +61 408 336 728

 
 
 

DBD::Informix and BLOBs

Post by Mark Cor » Wed, 03 Jul 2002 14:31:21


Just an addition to Brad's correct answer.  When using LOTOFILE, be sure to
cast the results to CHAR.  We have automated Perl processes that utilize
FILETOBLOB and LOTOFILE to load and unload thousands of images daily, so I
can guarantee it is quite useful.

HTH!

Mark Cory


> According to the README distributed with DBD::Informix...

>     DBD::Informix, Version 1.00, provides limited support for user-defined
>     data types (UDTs), treating them as CHAR(255).  To handle BLOBs and
>     CLOBs, use LOTOFILE() when you fetch the data and FILETOBLOB() or
>     FILETOCLOB() when you insert data.  To handle nonblob UDTs that exceed
>     255 characters in length, use server-side cast to lvarchar, as in

>         select mycol::lvarchar from mytab;
>     ...

> Basically, unload the blob/clob to the filesystem with LOTOFILE and
> manipulate that file in your program.  The Large Object Locater Datablade
> User's Guide and the Informix Guide to SQL: Tutorial (version 9.3) have
> information on the LOTOFILE and FILETOBLOB/CLOB functions, both available
at
> http://www.informix.com/answers/

> I use DBD::Informix to go the other direction... Loading large text
objects
> into clob columns with the FILETOCLOB function.  Works quite well.

> Hope this helps,
> Brad Patterson



> >> This is what I'm getting

image='01000000d9c8b7a60900000009000000601e0000f7e83a3b00000000010034080000
0000000000000000000000000000000000000000000000000000000000000000000000000000
Quote:> > '
> >> type=image/gif

image='01000000d9c8b7a6090000000900000056200000f8e83a3b00000000010034080000
0000000000000000000000000000000000000000000000000000000000000000000000000000
Quote:> > '
> >> type=image/gif

image='01000000d9c8b7a609000000090000005a200000f9e83a3b00000000010034080000
0000000000000000000000000000000000000000000000000000000000000000000000000000

- Show quoted text -

Quote:> > '
> >> type=image/gif

> >> If anyone knows what's going on, it'd be great to figure this out.  For
> >> the moment, I think I'll just code in a work-around for the generated
> >> email to simply grab the image off a web server (via the web
datablade).

> > That looks a hell of a lot like the LOCATOR that is used by Informix API
to
> > reference blobs. I don't use blobs but I do know that they are
referenced by
> > these locators, and the locators are not the data.

> > For a relatively "pure" understanding of blobs and their locators, read
the
> > 4GL manual on the subject (had to do that last week by coincidence) so
that
> > you understand the subject. I suppose you are already familiar with the
4GL
> > way of blobbing?

> > With DBD::Informix, there is some detail written either into the release
> > notes, or the README files or something. I'm not sure if they work or
they
> > fall into "some more work to be done". Go to www.google.com and do a
group
> > search of this newsgroup for any messages written by Jonathan Leffler
wot
> > mention DBD. I know he's commented about it several times, and posted
some
> > sample code somewhere. No doubt he'll comment when the sun rises on his
side
> > of the globe.

 
 
 

DBD::Informix and BLOBs

Post by Andrew Ham » Wed, 03 Jul 2002 16:50:00



> Ah, I noticed this a couple of minutes after I posted.  Reading up a
> bit more on LOTOFILE (using the SQL Syntax guide), I have got this
> working perfectly, and am now sending emails with images attached
> from the database.

> Many thanks to those that replied.  If any of you are in Brisbane,
> get in contact with me and I'll buy you a beer or three :-)

Not, but I could be....

I've got an account at the local?