Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Bind param error

by doug145 (Initiate)
on Jun 29, 2010 at 20:16 UTC ( [id://847197]=perlquestion: print w/replies, xml ) Need Help??

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

I keep getting this error on a run: Can't call method "bind_param" on an undefined value at /home/mundyda/extract.pl line 197. Here is line 197: $sth1->bind_param( 1, $extract_date); Here is the code up to that point:
"extract.pl" 841 lines, 28112 characters #!/usr/local/pa20_64/bin/perl -w #TURNED ON WARNINGS # # ######################################################### # # $Log: extract.pl,v $ # # Revision 1.6 2010/06/04 mundyda (Doug Mundy) # ECP F20-RM-224 Title: Deductions for Treasury Offset Program (TOP) # # # Revision 1.5 2008/02/28 11:07:56 11:07:56 carusojc (John Caruso) # ECP: Remedy Ticket #181282 Title: Use env variables as in Euro mo +dule # # # Revision 1.4 2005/03/07 11:42:48 11:42:48 lawrenma (Michael A La +wrence) # ECP: F20-IT-009 Title: 8400 Migration # # Environment variable changes required for 8400 # # Revision 1.3 2005/06/27 08:39:44 08:39:44 lawrenma (Michael A La +wrence) # ECP: F20-RN-223 Title: VPIS Extract # # Corrected several bugs identified by Diane in testing # # # Revision 1.2 2005/06/27 08:37:13 08:37:13 lawrenma (Michael A La +wrence) # ECP: F20-RM-223 Title: VPIS Extract # # This change reformatted some of the output records that weren't lini +ng up # correctly and also changed the looping construct # # # Revision 1.1 2004/09/27 10:49:57 10:49:57 coxrir (Richard R Cox) # Initial revision # # ######################################################### "extract.pl" 841 lines, 28112 characters # ######################################################### # use DBI; # this wonderful little perl creates the extract..... ###################################################################### +#### ## a world of perl and oracle wonderment to behold lays before you $ENV{PATH} = "/opt/ap/crp/saves/conus/output/vpis"; ##################### database connection #my $Dbh= DBI->connect("dbi:Oracle:pcrpi","$ENV{SQL_USER}/$ENV{PW}") # or die "Can't connect to Oracle database: $DBI::errstr\n"; my $Dbh= DBI->connect("dbi:Oracle:tcrpi.deca.mil","mike/zygote") or die "Can't connect to Oracle database: $DBI::errstr\n"; print "connected... processing input.\n"; ######################################################## # # Extract the valid payment dates # ######################################################## $completed_flg = undef; $extract_date = undef; $e_date_quoted = undef; $mystat = undef; #Added for Revision 1.6 $sth0=$Dbh->prepare("select completed_flg, extract_date from vpis_extract where completed_flg <> 'Y'"); $sth0->execute(); if ( ! $sth0->execute() ) { my $err = $DBI::errstr; $sth0->finish()}; $sth0->bind_columns(\$completed_flg, \$extract_date); print "extract date: $extract_date\n"; ######################################################## # # Begin loop of all payment dates # ######################################################## while ( $sth0->fetch){ print "extract date was: $extract_date completed_flg was: $ +completed_flg \n"; #my $e_date_quoted = $dbh->quote($extract_date); #my $filename = ">/opt/ap/crp/saves/conus/output/vpis/ +outfile.$extract_date"; my $filename = ">/home/mundyda/outfile.$extract_date"; + #TEST ONLY # my $filename = ">outfile.$extract_date"; open (outfile, $filename ) or die "File failed to initialize. +- 1 Extract failed 1\n"; ######################################################## # # Extract the history payment data # ######################################################## $contract_no = undef; $call_no = undef; $cage_cd = undef; $dunns_plus4 = undef; $check_no = undef; $voucher_no = undef; $invoice_no = undef; $eft_no = undef; $invoice_issue_dt = undef; $payment_dt = undef; $invoice_amt = undef; $check_eft_amt = undef; $discount_amt = undef; $interest_amt = undef; $pay_status = undef; $invoice_recv_dt = undef; $merch_accept_dt = undef; $paying_dssn = undef; $gross_inv_amt = undef; $due_dt = undef; $receiving_bank_id = undef; $adjustment_amount_1 = undef; $adjustment_reason_1 = undef; $adjustment_amount_2 = undef; $adjustment_reason_2 = undef; $account_number = undef; $currency_code = undef; $address_1 = undef; $address_2 = undef; $address_3 = undef; $payee_name = undef; $tin = undef; $vendor_code = undef; $net_due_date = undef; #added for Revision 1.6 $status = undef; #added for Revision 1.6 $space = " "; $sth1=$Dbh->prepare(" select a.contracting_office_text||h.piin as CONTRACT_NO, substr(h.dodaac, 4, 3)||h.piin||h.call_no as CAL +L_NO, b.cage_code as CAGE_CD, b.cec_code as DUNNS_PLUS4, vo.check_no, h.dov_no as VOUCHER_NO, h.invoice_no, vo.eft_trace as EFT_NO, to_char(h.dovi, 'YYYYMMDD') as INVOICE_ISSUE_DT, to_char(h.dov_date, 'YYYYMMDD') as PAYMENT_DT, h.invoice_amt, h.dov_amt as CHECK_EFT_AMT, decode(greatest(discount_due_date, h.dov_date), +discount_due_date, h.discount_amt,0) as DISCOUNT_AMT, h.penalty_amt as INTEREST_AMT, decode(h.status, 90, 1, 60, 6, 2) as PAY_STATUs, to_char(h.doiar, 'YYYYMMDD') as INVOICE_RECV_DT, to_char(h.doras, 'YYYYMMDD') as MERCH_ACCEPT_DT, s.fao_dssn as PAYING_DSSN, (h.invoice_amt+h.adj_invoice_amt+h.adj_invoice_a +mt_2+h.adj_invoice_amt_3+h.adj_invoice_amt_4+h.adj_invoice_amt_5+h.ad +j_i nvoice_amt_6) as GROSS_INV_AMT, to_char(h.dov_date, 'YYYYMMDD') as DUE_DT, f.routing_no as RECEIVING_BANK_ID, h.adj_invoice_amt as ADJUSTMENT_AMOUNT_1, h.reason_code_i as ADJUSTMENT_REASON_1, decode(h.edi_disc_amt, 0,h.adj_invoice_amt_2 + h.adj_invoice_amt +_3 + h.adj_invoice_amt_4 + h.adj_invoice_amt_5 + h.adj_invoice_amt_6, h.edi_disc_amt) as ADJUSTMENT_AMOUNT_2, decode(h.edi_disc_amt, 0,h.reason_code_i_2, 'SWELL') as ADJUSTMENT_REASON_2, f.account_no as ACCOUNT_NUMBER, h.currency_code, h.address_line_1 as ADDRESS_1, h.address_line_2 as ADDRESS_2, h.city|| ',' ||h.state_cd||' '||h.zip_code as AD +DRESS_3, h.company_name as PAYEE_NAME, e.tin, h.vendor_code, h.net_due_date as NET_DUE_DATE, #Added for Revis +ion 1.6 h.status as STATUS #Added for Revision 1.6 from historydb h, voucher vo, cdb c, cont_office a, vdb b, + eft f, vcodedb e, svcuniqdb s where h.dov_no = vo.dov_no and h.dov_date = vo.dov_date and h.piin = c.piin and c.status = 20 and c.contracting_office_id = a.contracting_office_id and c.mail_to_code = b.address_code and c.vendor_code = b.vendor_code and c.vendor_code = f.vendor_code (+) and c.eft_addr_code = f.eft_addr_code (+) and f.status (+) = 40 and h.vendor_code = e.vendor_code and h.dov_date = ? "); $sth1->bind_param( 1, $extract_date);
I am a long-time SQL coder, but a newbie to PERL. Doug

Replies are listed 'Best First'.
Re: Bind param error
by kennethk (Abbot) on Jun 29, 2010 at 20:24 UTC
    If $sth1 is undefined, that means that your prepare statement failed, which is usually attributable to an SQL bug. Try modifying your prepare statement to look like

    $sth1=$Dbh->prepare(...) or die "Prepare failed: $DBI::errstr\n";

    I also usually try to define my SQL strings independently of the prepare statement to make the code more obvious. An example from some of my code, using a heredoc:

    my $sql = <<EOSQL; INSERT INTO files (filename, content) VALUES (?,?) EOSQL my $query = $oracle->prepare($sql) or die "Insert prepare failed: $DBI::errstr"; $query->bind_param(1,$filename) or die "Insert bind_param failed: $DBI::errstr"; $query->bind_param(2,$content,{ora_type => ORA_BLOB}) or die "Insert bind_param failed: $DBI::errstr"; $query->execute or die "Insert execute failed: $DBI::errstr";

    You can also accomplish something similar using the PrintError or RaiseError flags at connect; see DBI for details.

    my $oracle = DBI->connect( @dbi_path, { PrintError => 0, RaiseError => 0, AutoCommit => 0 } ) or die "Database connect to $db_name failed:" . $DBI::errstr;
      Kenneth, The prepare statenment in this script has been running fine for over a year now. I just added an IF....ELSE to it. I'm still not clear what I need to do here.
        Assuming you added the or die clause I suggested, the script will die on the prepare. I know this because the next statement says the result was undefined. It will output $DBI::errstr - this should either provide you with the diagnostics to debug your SQL or will not make any sense, in which case the bug is in the Perl code. So, run the script again with the followed subbed for your prepare statement, and post the error message that results if it does not give you the necessary guidance:

        $sth1=$Dbh->prepare(" select a.contracting_office_text||h.piin as CONTRACT_NO, substr(h.dodaac, 4, 3)||h.piin||h.call_no as CAL +L_NO, b.cage_code as CAGE_CD, b.cec_code as DUNNS_PLUS4, vo.check_no, h.dov_no as VOUCHER_NO, h.invoice_no, vo.eft_trace as EFT_NO, to_char(h.dovi, 'YYYYMMDD') as INVOICE_ISSUE_DT, to_char(h.dov_date, 'YYYYMMDD') as PAYMENT_DT, h.invoice_amt, h.dov_amt as CHECK_EFT_AMT, decode(greatest(discount_due_date, h.dov_date), +discount_due_date, h.discount_amt,0) as DISCOUNT_AMT, h.penalty_amt as INTEREST_AMT, decode(h.status, 90, 1, 60, 6, 2) as PAY_STATUs, to_char(h.doiar, 'YYYYMMDD') as INVOICE_RECV_DT, to_char(h.doras, 'YYYYMMDD') as MERCH_ACCEPT_DT, s.fao_dssn as PAYING_DSSN, (h.invoice_amt+h.adj_invoice_amt+h.adj_invoice_a +mt_2+h.adj_invoice_amt_3+h.adj_invoice_amt_4+h.adj_invoice_amt_5+h.ad +j_i nvoice_amt_6) as GROSS_INV_AMT, to_char(h.dov_date, 'YYYYMMDD') as DUE_DT, f.routing_no as RECEIVING_BANK_ID, h.adj_invoice_amt as ADJUSTMENT_AMOUNT_1, h.reason_code_i as ADJUSTMENT_REASON_1, decode(h.edi_disc_amt, 0,h.adj_invoice_amt_2 + h.adj_invoice_amt +_3 + h.adj_invoice_amt_4 + h.adj_invoice_amt_5 + h.adj_invoice_amt_6, h.edi_disc_amt) as ADJUSTMENT_AMOUNT_2, decode(h.edi_disc_amt, 0,h.reason_code_i_2, 'SWELL') as ADJUSTMENT_REASON_2, f.account_no as ACCOUNT_NUMBER, h.currency_code, h.address_line_1 as ADDRESS_1, h.address_line_2 as ADDRESS_2, h.city|| ',' ||h.state_cd||' '||h.zip_code as AD +DRESS_3, h.company_name as PAYEE_NAME, e.tin, h.vendor_code, h.net_due_date as NET_DUE_DATE, #Added for Revis +ion 1.6 h.status as STATUS #Added for Revision 1.6 from historydb h, voucher vo, cdb c, cont_office a, vdb b, + eft f, vcodedb e, svcuniqdb s where h.dov_no = vo.dov_no and h.dov_date = vo.dov_date and h.piin = c.piin and c.status = 20 and c.contracting_office_id = a.contracting_office_id and c.mail_to_code = b.address_code and c.vendor_code = b.vendor_code and c.vendor_code = f.vendor_code (+) and c.eft_addr_code = f.eft_addr_code (+) and f.status (+) = 40 and h.vendor_code = e.vendor_code and h.dov_date = ? ") or die "Prepare failed: $DBI::errstr";
Re: Bind param error
by rowdog (Curate) on Jun 30, 2010 at 04:06 UTC

    In your prepare statement for $sth1 you have

    h.net_due_date as NET_DUE_DATE, #Added for Revision 1.6 h.status as STATUS #Added for Revision 1.6

    I know a lot more Perl than SQL but I doubt those are legal SQL comments.

      fwiw, those are legit SQL commands (at least in MySQL):

      mysql> select username as client_name from clients limit 1; +-------------+ | client_name | +-------------+ | stevieb | +-------------+

      Steve

        Really? SQLite disagrees...

        sqlite> select count(*) from show; # illegal comment; 27 Error: unrecognized token: "#"

        I guess I wasn't clear enough: Perl comments aren't valid SQL.

      Rowdog, I thought this was PERL, not SQL. Anyway, using 'as ' with aliases has worked fine in the current version of the code.

        I wasn't questioning the commands, I was questioning the use of Perl comments in SQL.

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others browsing the Monastery: (6)
As of 2024-03-19 02:35 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found