Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Problem with Code

by SamueD2 (Novice)
on Mar 20, 2003 at 20:17 UTC ( [id://244697] : perlquestion . print w/replies, xml ) Need Help??

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

I wrote the script below but when I try to run it I get the following error. Can't call method "Sql" on an undefined value at line 51. Can somebody help me.
use strict; use warnings; use DBI; #use Win32::ODBC; use DBD::ODBC; # Global Variables my @statement; # Array of sql statements that will be used for Sql my @data; # Array of table rows read from Sybase my @message; # Array of error messages used for debugging Sql # SQL Connection my $dbh = DBI->connect( "dbi:ODBC:RETDEV_SQL", "sa", "henry", {RaiseError => 1, PrintError => 1, AutoCommit => 1} ) or die "Unable to connect: ";#. $DBI::errstr . "\n"; # Sybase Connection my $dbh2 = DBI->connect( "dbi:ODBC:BPSA", "vornoi", "vornoi14", {RaiseError => 1, PrintError => 1, AutoCommit => 1} ) or die "Unable to connect: ";#. $DBI::errstr . "\n"; #Create the sql statement for Sybase and execute it $sth = $dbh2->prepare("SELECT (dbo.tbookkeeping_trans.branch_cd + dbo. +tbookkeeping_trans.account_cd)As AccountNo, 'TRI'as TransferFlag,'NCT' as TransferType, 'C' as RejectType, dbo.tbo +okkeeping_trans.security_adp_nbr as AdpNumber, dbo.tbookkeeping_trans.share_trans_qty as Shares,dbo.tbookkeeping_tra +ns.processing_dt FROM dbo.tbookkeeping_trans WHERE (dbo.tbookkeeping_trans.client_nbr='0030' AND dbo.tbookkeeping_t +rans.entry_cd IN ('JNL', 'JRL', 'REC') AND dbo.tbookkeeping_trans.branch_cd >'248') ORDER BY 1"); $sth->execute; while ((@data) = $sth->fetchrow_array) { # This is were we grab all the table rows from Sybase and store th +e # an array to be inserted into Sql. push @statement,"INSERT INTO TestClearTransferData(AccountNo,Trans +ferFlag,TransferType,RejectType,AdpNumber,Shares,ProcessingDate) VALU +ES ('$data[0]', '$data[1]', '$data[2]','$data[3]','$data[4]', '$data[ +5]', '$data[6]')"; } #Execute sql statments and if there is an error report it foreach (@statement){ $connection->Sql($_); if ($connection->Error()){ print "\nERROR: \nThe SQL statement:</b>\n $_ \n\n<b>caused th +e error:</b>\n\n ",$connection->Error(); die(); } push @message,$connection->Error(); } #Close the sql DB Connection $connection->Close(); #Close the Sybase DB connection $sth->finish; $dbh2->disconnect;

Edit: Added <code> tags. larsen

Replies are listed 'Best First'.
Re: Problem with Code
by Coruscate (Sexton) on Mar 20, 2003 at 20:36 UTC

    It appears as though you are trying to call Sql() on $connection. $connection is not defined anywhere in your code. Perhaps you meant $dbh or $dbh2? Furthermore, is Sql() a valid call? Perhaps you meant do() instead of Sql(). Perhaps I am mistaken though. Sql() is supported if you are using EZDBI. BTW, you may want to take a look at that module. It makes things quite simple :)

    Update: As a side note, I do believe you left your mysql password in the script. You may want to dub that out :)

    If the above content is missing any vital points or you feel that any of the information is misleading, incorrect or irrelevant, please feel free to downvote the post. At the same time, please reply to this node or /msg me to inform me as to what is wrong with the post, so that I may update the node to the best of my ability.

Re: Problem with Code
by dga (Hermit) on Mar 20, 2003 at 20:41 UTC

    I don't know DBD::ODBC directly but one thought that occured to me was "Do you need to pass a statement handle to Sql instead of a scalar containing some sql to execute?"

    Alternately this could be done with 2 statement handles along these lines:

    #the stuff at the top up to $sth->execute; my $insh=$dbh->prepare("INSERT INTO TestClearTransferData(AccountNo, T +ransferFlag, TransferType, RejectType, AdpNumber, Shares, ProcessingD +ate) VALUES ( ?, ?, ?, ?, ?, ?, ? )"); eval { while((@data) = $sth->fetchrow_array) { $insh->execute(@data); } }; if($@) { print STDERR "The insert died: $@"; } ...

    Since RaiseError is set the eval will die if theres a problem. Also the placeholders will speed up the inserts. And if you could set AutoCommit to 0 on the database getting the inserts you could do all of them in a transaction so that all are successful or none are successful, thus not leaving a messy table half way through the inserts if there is a problem.

      Thanks for your help. Can you explain to me how placeholders work. Also if I set the AutoCommit to 0 where do I place the Commit Thanks

        Placeholders tell DBI where in a stamement to place values passed in via the execute method. Here is an example which hopefully will demonstrate both things.

        ... # code to get things set up and vars defined etc. my $dbh=DBI->connect($datasource_with_RaiseError_1_and_AutoCommit_0); eval { $dbh->rollback; #this sets the time on the transaction to now my $sth=$dbh->prepare('INSERT INTO table ( name, email ) VALUES ( ?, + ? )'); while(my($name,$email)=each %emaillist) { $sth->execute($name, $email); } $dbh->commit; ]; if($@) { $dbh->rollback; print STDERR "Something went wrong in insert: $@"; }

        With later DBI's you can get the actual statement tried after placeholder substitution for your error messages also. $@ has the message from DBI when it called die. You would have to declare the sth outside the eval so it would be in scope for the error handler block. Also with current DBI there is a begin method which turns off autocommit for one transaction so you can normally have it on for selects and just use multi statement transaction mode when desired.

        With this code, if anything goes wrong inserting values the while will be cancelled and the eval is exited with $@ set so the if will catch it. If everything goes ok the while will end and the commit will be called making the changes to the database durable and the if block gets skipped. The rollback at the top gets any timestamps etc set to the start of the eval. Of course if you call this from somewhere where there is a transaction underway it will be cancelled before this one starts so be careful to coordinate your rollbacks so as not to undo things you wanted saved.

        The placeholders ?, ? will be replaced by DBI on each call to the execute by the variables passed in order. It will also take an array and use them in order. The advantages are that the Database backend makes up a generic statement for use and sets up the query plan then the variables are just dropped into the existing statement and the database has only to run the previously prepared statement without generating a new plan. Also by doing things in a transaction the overhead of adding rows is smaller since with autocommit on you effectively do a commit, which is an expensive operation, on every row instead of once for a group of rows. In summary, in your loop you don't make query plans or commit changes, just add rows quickly, and getting expensive operations out of loops is a Good Thing™.