Help with DBD::Oracle Blobs

[merlyn]

I have a prepare on a DBD::Oracle database:

my $sth = $dbh->prepare(<<'');
INSERT INTO sometable (x, y, z, blobby) VALUES (?, ?, ?, ?)

$sth->execute('x', 'y', 'z', $blobdata);

But now I understand I have to tell Oracle that the fourth column is a blob. I see that I have to use

$sth->bind_param($field_num, $lob_value, { ora_type => ORA_BLOB });

but the examples show only a single value, and not multiple values, so I'm not sure how to sequence both the execute and the (hopefully single) bind_param.

Help!

[RazorbladeBidet]

are you asking which comes first? The bind_param or the execute?

if you want to bind multiple parameters, just execute multiple bind_param statements with the first param the incremental parameter number.



$sth->bind_param(1, $value, ... );
$sth->bind_param( 2, $value, ... );



If that's the case, the bind_param will come after the prepare and before the execute. If it's not, please elucidate :-D

Update:
I see what you're trying...



my $sql = "INSERT INTO sometable (x, y, z, blobby) VALUES (?, ?, ?, ?)
+"

my $sth = $dbh->prepare( $sql );

$sth->bind_param( 1, $x );
$sth->bind_param( 2, $y );
$sth->bind_param( 3, $z );
$sth->bind_param( 4, $lob_value, { ora_type => ORA_BLOB });

$sth->execute();

[merlyn]

I'm trying to see if I can avoid doing all those bind_params. The actual list is 10 elements. Seems crazy to have to spell all those out just to have one odd bind_param type.

[jbrugger]

It seems to work that way... ever tried a for-next loop to bind them ;-)
I know... this was for the fun...
But serious, in ALL the examples i've seen, you'd have to do it like this.
Perhaps a time to creat your own module / update the current?

[jbrugger]

I assume you looked here ?
$sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });

[fizbin]

By default, you need to go through bind_param for each parameter, and then call execute with no args. However, the DBI is explicitly built for subclassing, (see http://search.cpan.org/~timb/DBI/DBI.pm#Subclassing_the_DBI) so writing your own little wrapper is pretty easy:
Read more... (1036 Bytes)
Now in your main code, just use MyDBI->connect instead of DBI->connect (or pass the RootClass attribute as shown in the documentation), and then you can call execute as:

my $sth = $dbh->prepare(<<'');
INSERT INTO sometable (x, y, z, blobby) VALUES (?, ?, ?, ?)

$sth->execute('x', 'y', 'z', $blobdata, { ora_type => ORA_BLOB });

Or, if pulling off neighboring parameters as attributes offends your sensibilities, do:

$sth->execute('x', 'y', 'z', [$blobdata, { ora_type => ORA_BLOB }] );

[Thilosophy]

How big is the data you are inserting into the blob column, and what is the Oracle version? 9i lets you insert into blobs using the default (varchar) binding, at least up to a certain size. So if we are talking about 5KB or so, you probably do not have to declare anything to the DBI .