Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Help with DBD::Oracle Blobs

by merlyn (Sage)
on Mar 04, 2005 at 21:00 UTC ( #436756=perlquestion: print w/ replies, xml ) Need Help??
merlyn has asked for the wisdom of the Perl Monks concerning the following question:

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!

-- Randal L. Schwartz, Perl hacker
Be sure to read my standard disclaimer if this is a reply.

Comment on Help with DBD::Oracle Blobs
Select or Download Code
Re: Help with DBD::Oracle Blobs
by RazorbladeBidet (Friar) on Mar 04, 2005 at 21:18 UTC
    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();
    --------------
    It's sad that a family can be torn apart by such a such a simple thing as a pack of wild dogs
      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.

      -- Randal L. Schwartz, Perl hacker
      Be sure to read my standard disclaimer if this is a reply.

        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?
Re: Help with DBD::Oracle Blobs
by jbrugger (Parson) on Mar 04, 2005 at 21:18 UTC
    I assume you looked here ?
    $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });
Re: Help with DBD::Oracle Blobs
by Thilosophy (Curate) on Mar 05, 2005 at 13:31 UTC
    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 .
Re: Help with DBD::Oracle Blobs
by fizbin (Chaplain) on Mar 05, 2005 at 16:33 UTC
    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: 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 }] );
    -- @/=map{[/./g]}qw/.h_nJ Xapou cets krht ele_ r_ra/; map{y/X_/\n /;print}map{pop@$_}@/for@/

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://436756]
Approved by Limbic~Region
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (7)
As of 2014-10-25 10:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (142 votes), past polls