On Wed, 2002-05-29 at 16:13, Lance Prais wrote:
> I am m using the following code to read from a .txt file. I am running into
> a problem that I am not sure why it is happening.
> The problem is when I run this script the SQL is not reading the variable.
> I am testing to make sure the .txt file contains data and it does.
>
> I get the following error:
> DBD::Oracle::st execute failed: called with 1 bind variables when 0 are
> needed at /data/verity/custom-kb/scripts/test.pl line 36, <BULK> line 1.
> DBD::Oracle::st execute failed: called with 1 bind variables when 0 are
> needed at /data/verity/custom-kb/scripts/test.pl line 36, <BULK> line 1.
> Issuing rollback() for database handle being DESTROY'd without explicit
> disconnect(), <BULK> line 1.
>
> LINE 36 where the error is occuring: sr_num = $ln");
>
> In the past when reading form an array fbuilt by a SQL statement I do it
> different. This is my first tiem trying to read form Files
>
> Any Ideas what I am doing wrong?
>
> Thank you in advance
> Lance
>
Besides failing to use whitespace? You used string interpolation when
you built the query instead of using variable binding. Both are valid
methods -- when used separately. I prefer binding since there aren't any
quote problems that way. I can understand why you couldn't see it; your
code was horrifyingly unindented. Below is your script with decent
indenting and a few minor idiomatic changes. N.B. if you version perl
is old you may have to change the "our"s to "my"s.
#!/usr/local/bin/perl
#
# Purpose: To extract Daily Siebel (SecureTrak) cases
#
# Written by Lance
use strict; #ALWAYS USE STRICT
use DBI;
#ENVIRONMENT VARIABLES
our $CUSTOM = "/data/verity/custom-kb";
our $SERVICE = "XXXXX.world";
our $oracle_user = "XXXXXX";
our $oracle_password = "XXXXX";
our $html_file_path = "$CUSTOM/content/daily_securetrak_new";
our $bif_file_path = "$CUSTOM/content/daily_securetrak";
our $bulk_load_filename = "$CUSTOM/bif/daily_securetrak.bif";
our $template_filename =
"$CUSTOM/templates/daily_securetrak_main_build_template.html";
our $template_comm_filename =
"$CUSTOM/templates/daily_securetrak_comm_build_template.html";
our $daily_sr_file = "$CUSTOM/scripts/unique_sr_list.txt";
print "Extract started at: " . `date`;
#open connection to the DB
#why use FALSE/TRUE sometimes and 0/1 others?
#be consistent
my $dbh = DBI->connect(
"DBI:Oracle:$SERVICE",
$oracle_user,
$oracle_password,
{
AutoCommit => 0,
LongTruncOk => TRUE,
PrintError => TRUE,
ChopBlanks => TRUE,
LongTruncOk => TRUE,
LongReadLen => 50000,
RaiseError => TRUE
}
) or die "connecting: $DBI::errstr";
#open file to read from
#use $! here to tell you why
#also use of "or" allows you to ditch the parens
open BULK, $daily_sr_file or die "Could not open file:$!";
my $line = 1;
while( my $ln = <BULK> ) {
chomp($ln);
#what in the world is this comment?
#use the items of the
#NOTE: all columns are named
my $get_case_text = $dbh->prepare("
SELECT
a.sr_num sr_num,
b.name account_name,
b.loc loc,
a.sr_title sr_title,
f.mid_name uc_id,
a.sr_stat_id sr_stst_id,
e.name group_name,
f.fst_name fst_name,
f.last_name last_name,
f.email_addr email_addr,
g.country country,
g.Province Province,
g.zipcode zipcode,
to_char(
a.x_cp_created,'DD-MON-YYYY'
) x_cp_created,
to_char(
a.x_cp_closed,'DD-MON-YYYY'
) x_cp_closed,
c.login login,
a.desc_text desc_text
FROM
s_srv_req a,
s_org_ext b,
s_employee c,
s_postn d,
s_org_int e,
s_contact f,
s_addr_org g
WHERE a.cst_ou_id = b.row_id
AND a.cst_con_id = f.row_id
AND a.owner_emp_id = c.row_id
AND c.pr_postn_id = d.row_id
AND d.ou_id = e.row_id
AND b.pr_addr_id = g.row_id
AND sr_num = ?"
);
#just above is the problem should be a ? instead of $ln
#$sr_num should be $ln
$get_case_text->execute($ln) or $dbh->errstr;
#use hashrefs instead of 17 variables
my $case = $get_case_text->fetchrow_hashref();
$get_case_text->finish;
print "--------------> SR(bulk): $case->{sr_num}\n";
print "--------------> User Id(name): $case->{uc_id}\n";
$line++;
}
close(DAT);
$dbh->disconnect;
--
Today is Prickle-Prickle the 3rd day of Confusion in the YOLD 3168
Wibble.
Missile Address: 33:48:3.521N 84:23:34.786W
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
