byterock

In part 13 we had a look at a look at bind_col now we will take a quick look at blob, lob and clobs and even memo data.

Some Good! Some Bad!

The one thing about memo, blob, lob, clobs and other large DB fields is that not all DBDs use em, or even care about them, but when you need them you have to use them.

So for fun we will just use an example from DBD::Oracle but in general most of the others that do (DBD::DB2, DBD::Sybase) follow the spec fairly closely, One first has to tell the DB what type of data to bind, set up a maximum length to read and or a length to stop reading.

Give it a Try

So the first thing we have to do it tell the bind_param() method what sort of LOB to expect. So your bind call would look like this:

$sth->bind_param(:p_big_desc, $big_desc, {ora_type=>ORA_CLOB});

Or if you are dealing with a BLOB:

$sth->bind_param(:p_pic, $a_pic, {ora_type=>ORA_BLOB});

Note there how I am using ORA_BLOB and ORA_CLOB there where imported values I got like this

use DBD::DBD qw(:ora_types);

One odd non spec quirk when using CLOBS and BLOBS with DBD::Oracle is encountered when doing an insert or update and you have more than one CLOB or BLOB field in the query. In this case you will have to tell DBD::Oracle which field corresponds to which datatype. To do this, you simply add the ora_field=> attribute to the bind call, with the corresponding table field name for the bind.

$sth->bind_param(:p_caption, $caption,
{ora_type=>ORA_CLOB, ora_field=>'caption’}
);
$sth->bind_param(:p_picture, $a_pic,
{ora_type=>ORA_BLOB,
ora_field=>'picture’}
);
$sth->bind_param(:p_comment, $coms,
{ora_type=>ORA_CLOB, ora_field=>'comment’}
);

When working with LOBs, is it also very important to tell DBD::Oracle what is the maximum size of the LOB that is to be returned. If you do not, DBD::Oracle will truncate the LOB and abort the fetch. You have to set a few attributes on the database handle, the first being LongTruncOk. You will want to set this to false, so that any fetch of a LOB variable that is too big will cause the fetch to fail.

$dbh->{LongTruncOk} = 1;

You also have to tell DBD::Oracle what the maximum size is for the LOB with the LongReadLen attribute. Set this value to the maximum size in bytes you want your LOBs to be. If you want to set a two megabyte limit for returned LOBs, you would set the attribute like this:

$dbh->{LongReadLen} = 2*1024*1024;

That is about it really, just make sure you check with the DBD you are using as they are implemented in different flavors.