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
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;
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
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.
| [reply] [Watch: Dir/Any] |
|
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";
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
|
|
|
Re: Bind param error
by rowdog (Curate) on Jun 30, 2010 at 04:06 UTC
|
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.
| [reply] [Watch: Dir/Any] [d/l] |
|
mysql> select username as client_name from clients limit 1;
+-------------+
| client_name |
+-------------+
| stevieb |
+-------------+
Steve | [reply] [Watch: Dir/Any] [d/l] |
|
sqlite> select count(*) from show; # illegal comment;
27
Error: unrecognized token: "#"
I guess I wasn't clear enough: Perl comments aren't valid SQL.
| [reply] [Watch: Dir/Any] [d/l] |
|
Rowdog,
I thought this was PERL, not SQL. Anyway, using 'as ' with aliases has worked fine in the current version of the code.
| [reply] [Watch: Dir/Any] |
|
| [reply] [Watch: Dir/Any] |
|
|