RE: Problem reading a file and passing a variable

[Lance Prais]

This is the most bizarre thing I have ever seen.  I am using the following
code:
There is absolutely no reason why this should not return anything when I
pass in the variable.

The text file that it is reading form looks like this:
1-10903223
1-281734170
1-294613112
1-313763161
1-314022306
1-330892166
1-358488075

The result of the print is as follows:
--------------> User Id(name):
--------------> SR(bulk):
--------------> User Id(name):
--------------> SR(bulk):
--------------> User Id(name):
--------------> SR(bulk):
--------------> User Id(name):

You can see the there is data but is not being read.

print "Extract started at: " . `date`;

use Strict;
use DBI;
$connect_string="DBI:Oracle:$SERVICE";

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 BULK, $daily_sr_file or die "Could not open file:$!";

my $line = 1;

while(my $ln = <BULK> ) {
        $ln =~ s/ //g;
        #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,
                        a.sr_title         sr_title,
                        a.sr_stat_id       sr_stst_id
                FROM
                        s_srv_req  a
                WHERE   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->{sr_title}\n";
        $line++;
}

close(BULK);
$dbh->disconnect;

-----Original Message-----
From: Chas Owens [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 29, 2002 5:39 PM
To: Lance Prais
Cc: Perl
Subject: RE: Problem reading a file and passing a variable

On Wed, 2002-05-29 at 18:35, Lance Prais wrote:
> Thank you.    I scaled down the SQL and tested it in SQL (records
returned)
> and then in the script(no records returned).  This is bizarre to me.
> Actually it it is a string with the values being for example "1-99999"

Do yo mean $ln contains the value "1-99999"?  The following will strip
spaces from a string:

$ln =~ s/ //g;

If it worked before you may want to eschew using the binding feature and
just plop $ln directly into the query (just make certain to call execute
with no parameters).

>
>
> Does perl default to a string?  If I put "" or '' around the? Like so '?'
> Or "?", I get an error message therefore I know I am not going down the
> write path.  Could it have something to do with permissions?  I have them
> set for execute.
>
> Thanks
> Lance
>
> -----Original Message-----
> From: Chas Owens [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 29, 2002 4:11 PM
> To: Lance Prais
> Cc: Perl
> Subject: RE: Problem reading a file and passing a variable
>
> On Wed, 2002-05-29 at 17:05, Lance Prais wrote:
> > [Chas],
> >   Thank you, you made me realize the value of indention like never
before.
> > In the past I used the "=?" to return results form the query but in this
> > case when I used in and run this script it does not error out but
instead
> > the query returns no values?  Could that be because there are leading or
> > trailing spaces. My understanding is that the Chomp would take care of
> that.
> > Am I wrong in my assumption?
> >
> > Thanks again
> > Lance
> >
>
> The chomp function removes the trailing newline character (actually it
> removes whatever is in $/ variable, but that is usually \n so it doesn't
> really matter).  In this case $ln looks like a number so you could say
> "$ln += 0;" which would force $ln to contain a number instead of a
> string (thus getting rid of spaces).  If spaces are not the problem then
> try running the query by hand in sql*plus (or toad if you have it) to
> make sure it is not a problem with the query.
>
> --
> Today is Prickle-Prickle the 3rd day of Confusion in the YOLD 3168
> This statement is false.
>
> Missile Address: 33:48:3.521N  84:23:34.786W
>
>
> --
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
--
Today is Prickle-Prickle the 3rd day of Confusion in the YOLD 3168
You are what you see.

Missile Address: 33:48:3.521N  84:23:34.786W


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]