SQL statement in Perl doesn't work

Discussion in 'Perl Misc' started by Huub, Apr 26, 2007.

  1. Huub

    Huub Guest

    Hi,

    I have written a script that reads from a MySQL database. It works fine,
    except that the addition of one AND does not work while it does in Query
    Browser:

    select betaald2006 from hvw where lidnr = $record and naam != " " and
    kenmerk2006 is null

    works. The same statement via Perl:

    $betaald2006 = "select betaald2006 from hvw where lidnr = $record and
    naam != ' ' and kenmerk2006 is null";

    does not work. When I remove "and kenmerk2006 is null", it works ok.
    With it, I don't get a real error, just this message about $betaald2006:

    "Use of uninitialized value in string eq"

    in

    "if ($betaald2006 eq $vergelijk)"

    Any idea what I'm doing wrong?

    Thanks

    Huub
     
    Huub, Apr 26, 2007
    #1
    1. Advertising

  2. Huub

    Huub Guest

    >
    > Are you sure that warning is about $betaald2006? What's the value of
    > $vergelijk?
    >


    Yes, I'm sure that warning is about $betaald2006, because that's on the
    line indicated with the warning. $vergelijk = "N". And $betaald2006 can
    only be "Y" and "N".
     
    Huub, Apr 26, 2007
    #2
    1. Advertising

  3. On Apr 26, 2:46 pm, Huub <"v.niekerk at hccnet.nl"> wrote:
    > Hi,
    >
    > I have written a script that reads from a MySQL database. It works fine,
    > except that the addition of one AND does not work while it does in Query
    > Browser:
    >
    > select betaald2006 from hvw where lidnr = $record and naam != " " and
    > kenmerk2006 is null
    >
    > works. The same statement via Perl:
    >
    > $betaald2006 = "select betaald2006 from hvw where lidnr = $record and
    > naam != ' ' and kenmerk2006 is null";


    That's just a string. It may _look_ to you like an SQL query but
    there's nothing in your code to cause that to be sent to a database
    engine. It's just a string.

    > does not work.


    Never say "it does not work".

    > When I remove "and kenmerk2006 is null", it works ok.


    Never say "it works ok".

    > With it, I don't get a real error, just this message about $betaald2006:
    >
    > "Use of uninitialized value in string eq"
    > in
    >
    > "if ($betaald2006 eq $vergelijk)"


    I do not believe you.

    If you've just put a string in $betaald2006 then it would not be
    undefined. I suspect there are further statements between the two you
    showed us. Either that or $vergelijk is undefined.

    > Any idea what I'm doing wrong?


    You are posting to comp.lang.perl.misc without reading the posting
    guidelines.
     
    Brian McCauley, Apr 26, 2007
    #3
  4. Huub

    Huub Guest

    > I do not believe you.

    Really. Why?

    >
    > If you've just put a string in $betaald2006 then it would not be
    > undefined. I suspect there are further statements between the two you
    > showed us. Either that or $vergelijk is undefined.


    Alright. There are indeed some more lines in between:

    $betaald2006 = "select betaald2006 from hvw where lidnr = $record and
    naam != ' ' and kenmerk2006 is null";
    $sth = $dbh->prepare($betaald2006);
    $sth->execute or die "SQL Error: $DBI::errstr\n";
    @betaald2006 = $sth->fetchrow_array;
    $betaald2006 = @betaald2006;
    $betaald2006 = $betaald2006[0];
    $vergelijk = "N";
    if ($betaald2006 eq $vergelijk)
    {

    And the warning I posted does point to the "if" line. And yes: without
    the last AND in $betaald2006 the result is fine. With it, I do get that
    warning about 20 times.
     
    Huub, Apr 26, 2007
    #4
  5. Huub

    J. Gleixner Guest

    Huub wrote:
    [...]
    > $betaald2006 = "select betaald2006 from hvw where lidnr = $record and
    > naam != ' ' and kenmerk2006 is null";


    You don't show the value of $record.

    print "SQL=$betaald2006\n";

    Actually I'd suggest using a more descriptive variable name..
    $betaald2006_sql, or something like that.

    > $sth = $dbh->prepare($betaald2006);


    my $sth = $dbh->prepare($betaald2006);

    > $sth->execute or die "SQL Error: $DBI::errstr\n";
    > @betaald2006 = $sth->fetchrow_array;


    my @betaald2006 = $sth->fetchrow_array;

    Verify the results are what you expect:

    use Data::Dumper;
    print Dumper( @betaald2006 );

    or maybe simply:

    print join( "\n", @betaald2006, '');

    Using Dumper might make possible data issue more visible.

    FYI: That's only the first row, you'll need a loop to iterate
    through the other rows, if there are more than one.

    Using a more descriptive variable name might be useful too. e.g.
    @results, or @betaald2006_results.

    > $betaald2006 = @betaald2006;
    > $betaald2006 = $betaald2006[0];


    That's pointless, just use $betaald2006[0].

    > $vergelijk = "N";
    > if ($betaald2006 eq $vergelijk)
    > {


    my $vergelijk = 'N';
    print "Does $betaald2006[0] eq $vergelijk?\n";
    if ( $betaald2006[0] eq $vergelijk) {
    print "Yes\n";
    }

    Also, if you're only after the first row, use 'limit 1' in your SQL.

    >
    > And the warning I posted does point to the "if" line. And yes: without
    > the last AND in $betaald2006 the result is fine. With it, I do get that
    > warning about 20 times.


    Using a few print statements should show you/us what's going on.
     
    J. Gleixner, Apr 26, 2007
    #5
  6. On Apr 26, 9:05 pm, Glenn Jackman <> wrote:
    > At 2007-04-26 03:13PM, "Huub" wrote:
    >
    > > Alright. There are indeed some more lines in between:

    >
    > > $betaald2006 = "select betaald2006 from hvw where lidnr = $record and
    > > naam != ' ' and kenmerk2006 is null";
    > > $sth = $dbh->prepare($betaald2006);
    > > $sth->execute or die "SQL Error: $DBI::errstr\n";
    > > @betaald2006 = $sth->fetchrow_array;
    > > $betaald2006 = @betaald2006;
    > > $betaald2006 = $betaald2006[0];
    > > $vergelijk = "N";
    > > if ($betaald2006 eq $vergelijk)
    > > {

    >
    > So, clearly, the first column of the first row returned by your query is
    > null (defined($betaald2006[0]) is false).


    Or the query returned no rows.
     
    Brian McCauley, Apr 27, 2007
    #6
  7. On Apr 26, 8:44 pm, "J. Gleixner" <>
    wrote:

    > use Data::Dumper;
    > print Dumper( @betaald2006 );


    It's better to dump @betaald2006 as a single variable

    print Dumper( \ @betaald2006 );

    > Also, if you're only after the first row, use 'limit 1' in your SQL.


    I'm not sure that the lack of portability of using a non-standard SQL
    extension is justified.
     
    Brian McCauley, Apr 27, 2007
    #7
  8. Huub

    ikeon Guest

    On Apr 26, 9:44 pm, "J. Gleixner" <>
    wrote:
    > Huub wrote:
    >
    > [...]
    >
    > > $betaald2006 = "select betaald2006 from hvw where lidnr = $record and
    > > naam != ' ' and kenmerk2006 is null";

    >
    > You don't show the value of $record.
    >
    > print "SQL=$betaald2006\n";
    >
    > Actually I'd suggest using a more descriptive variable name..
    > $betaald2006_sql, or something like that.
    >
    > > $sth = $dbh->prepare($betaald2006);

    >
    > my $sth = $dbh->prepare($betaald2006);
    >
    > > $sth->execute or die "SQL Error: $DBI::errstr\n";
    > > @betaald2006 = $sth->fetchrow_array;

    >
    > my @betaald2006 = $sth->fetchrow_array;
    >
    > Verify the results are what you expect:
    >
    > use Data::Dumper;
    > print Dumper( @betaald2006 );
    >
    > or maybe simply:
    >
    > print join( "\n", @betaald2006, '');
    >
    > Using Dumper might make possible data issue more visible.
    >
    > FYI: That's only the first row, you'll need a loop to iterate
    > through the other rows, if there are more than one.
    >
    > Using a more descriptive variable name might be useful too. e.g.
    > @results, or @betaald2006_results.
    >
    > > $betaald2006 = @betaald2006;
    > > $betaald2006 = $betaald2006[0];

    >
    > That's pointless, just use $betaald2006[0].
    >
    > > $vergelijk = "N";
    > > if ($betaald2006 eq $vergelijk)
    > > {

    >
    > my $vergelijk = 'N';
    > print "Does $betaald2006[0] eq $vergelijk?\n";
    > if ( $betaald2006[0] eq $vergelijk) {
    > print "Yes\n";
    >
    > }
    >
    > Also, if you're only after the first row, use 'limit 1' in your SQL.
    >
    >
    >
    > > And the warning I posted does point to the "if" line. And yes: without
    > > the last AND in $betaald2006 the result is fine. With it, I do get that
    > > warning about 20 times.

    >
    > Using a few print statements should show you/us what's going on.


    Maybe the problem is in the quote inside. maybe try:

    $betaald2006 = "select betaald2006 from hvw where lidnr = $record and
    naam != \' \' and kenmerk2006 is null";
     
    ikeon, Apr 27, 2007
    #8
  9. Huub

    Huub Guest

    Thank you for your comments. The last answer by Brian McCauley made me
    look again at the query result from the database. It turns out no rows
    were fetched indeed, so the script does work. By changing the range of
    records I got the results I expected in the first place, so I apparently
    misinterpreted the warning.

    Huub
     
    Huub, Apr 27, 2007
    #9
  10. Huub

    Bart Lateur Guest

    Huub wrote:

    >select betaald2006 from hvw where lidnr = $record and naam != " " and
    >kenmerk2006 is null
    >
    >works. The same statement via Perl:
    >
    >$betaald2006 = "select betaald2006 from hvw where lidnr = $record and
    >naam != ' ' and kenmerk2006 is null";
    >
    >does not work.


    It's not the same statement. You've changed the quotes.

    --
    Bart.
     
    Bart Lateur, Apr 27, 2007
    #10
  11. Huub

    Joe Smith Guest

    Huub wrote:

    > if ($betaald2006 eq $vergelijk)
    > {
    >
    > And the warning I posted does point to the "if" line.


    I see that you've found the answer to your problem, but I want to point out
    something. You did not indicate whether or not there is an "elsif" clause
    on your "if" statement. That can make a difference when debugging.

    In an "if" statement with an "elsif" part, uninitialized variables detected in
    the "elsif" part will be reported with the line number of the "if" part.



    linux% cat -n test.pl
    1 use strict; use warnings;
    2 my($foo,$bar);
    3 if ($foo == 1) {
    4 print "foo is one\n";
    5 } elsif ($bar eq 'two') {
    6 print "bar is two\n";
    7 } else {
    8 print "none of the above\n";
    9 }
    linux% perl test.pl
    Use of uninitialized value in numeric eq (==) at test.pl line 3.
    Use of uninitialized value in string eq at test.pl line 3.
    none of the above

    The string eq line 5 is considered to be a part of the if statement
    that started at line 3.

    -Joe
     
    Joe Smith, Apr 29, 2007
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andy B

    Simple if statement doesn't work

    Andy B, Apr 25, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    361
    cfps.Christian
    Apr 25, 2008
  2. Replies:
    2
    Views:
    116
  3. Replies:
    2
    Views:
    158
  4. ranrodrig
    Replies:
    2
    Views:
    99
  5. stroncococcus

    type casting for DBI SQL statement doesn't work

    stroncococcus, Dec 6, 2006, in forum: Perl Misc
    Replies:
    4
    Views:
    185
    stroncococcus
    Dec 6, 2006
Loading...

Share This Page