DBI Question

boodong has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to insert a massive CLOB into an oracle DB and get a "Can't locate object method "prepare" via package DBI::st" errror when executing the command. Now the row does get inserted but the script dies after this error.

Here is a summarized version of the code

use strict;
use Time::Local;
use warnings;
use Storable;
use Data::Dumper;
use DateTime;
use DBI;
use Digest::MD5 qw(md5 md5_hex md5_base64);
use DBD::Oracle ':ora_types';

# The Connection
#
## Connect to VistaMart DB
my $dbh = DBI->connect("dbi:Oracle:host=$scriptProps{'IVDBHOST'};sid=$
+scriptProps{'IVDBSID'};port=$scriptProps{'IVDBPORT'}", $scriptProps{'
+IVDBUSER'}, $scriptProps{'IVDBPASSWORD'},{ AutoCommit => 1 }) or grac
+efulExit($DBI::errstr);
        $dbh->{LongReadLen} = 41943040;
        $dbh->{LongTruncOk} = 1;




#The insert
        $dbh = $dbh->prepare("INSERT INTO vindbase_operator.mobily_vb_
+script_worklistv2 (worklistid, emstype, submitserver, submitdate, sub
+mitowner, state, contents, contentscksum) VALUES (vindbase_operator.m
+obily_vb_script_worklist_inc.nextval,?,?,to_date(?,'YYYY-MM-DD H
H24:MI:SS'),?,?,?,?)");
                $dbh->bind_param( 1, 'samsungWSM' );
                $dbh->bind_param( 2, $hostname );
                $dbh->bind_param( 3, getDateFromEPOC($fileList{$filena
+me}{timestamp}) );
                $dbh->bind_param( 4, $scriptName );
                $dbh->bind_param( 5, 1 );
                $dbh->bind_param( 6, $outFile, {ora_type => ORA_CLOB})
+;
                $dbh->bind_param( 7, md5_hex($outFile) );
                $dbh->execute;
                #$dbh->commit;
                print ">>>>>>>>>>>>>>>>>FINNN\n"; #DEBUG


#disconnect
$dbh->disconnect;
[download]

The result is that the row gets inserted but the script barfs with the following error.

Can't locate object method "prepare" via package "DBI::st" at ./samsun
+gWSMv2.pl line 512.
[download]

Comment on DBI Question Select or Download Code

Replies are listed 'Best First'.
Re: DBI Question by moritz (Cardinal) on Aug 25, 2011 at 14:55 UTC
You're mixing up database handle (usually `$dbh`) and statement handle (usally `$sth`) in this line: `$dbh = $dbh->prepare(...)` [download] That will place a statement handle (return value from `$dbh->prepare`) in a variable that you think contains a database handle. Another `$dbh->prepare` call goes BOOM. So don't do that, use separate variables for separate things. There are working examples in the DBI docs. Perl 6 - second systems done right	[reply] [d/l] [select]
Re: DBI Question by blue_cowdawg (Monsignor) on Aug 25, 2011 at 14:58 UTC
`$dbh = $dbh->prepare("INSERT INTO vindbase_operator.mobily_vb_ +script_worklistv2 (worklistid, emstype, submitserver, submitdate, sub +mitowner, state, contents, contentscksum) VALUES (vindbase_operator.m +obily_vb_script_worklist_inc.nextval,?,?,to_date(?,'YYYY-MM-DD H H24:MI:SS'),?,?,?,?)");` [download] The aove cited code looks suspicious to me. You are reassigning your database handle as a statement handle and then trying to treat it as a database handle again later on in your code. When using DBI I normally use another variable as my statement handle thusly: `my $sth = $dbh->prepare("INSERT INTO vindbase_operator.mobily_vb_ +script_worklistv2 (worklistid, emstype, submitserver, submitdate, sub +mitowner, state, contents, contentscksum) VALUES (vindbase_operator.m +obily_vb_script_worklist_inc.nextval,?,?,to_date(?,'YYYY-MM-DD H H24:MI:SS'),?,?,?,?)") or die $dbh->errstr;` [download] and then hopefully life goes on as normal. Use the $sth to execute your insert. That's just the first thing that leaps to my attention... Peter L. Berghold -- Unix Professional Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg	[reply] [d/l] [select]
Re^2: DBI Question by boodong (Novice) on Aug 25, 2011 at 19:29 UTC
DOH! , I guess one can stare at code for hours and miss the glaringly obvious. Thanks for the set of eyes on this one ;)	[reply]
Re^3: DBI Question by locked_user sundialsvc4 (Abbot) on Aug 26, 2011 at 14:12 UTC
Been there... done that. (Show of hands, please?) See what I mean? Yup, lots of bruised foreheads in the Monastery right now. Such are the unfortunate drawbacks of a weakly-typed language.