SQL statement in Perl doesn't work

[Huub]

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]

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".

 

[Brian McCauley]

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.

 

[Huub]

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.

 

[J. Gleixner]

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:umper;
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.

 

[Brian McCauley]

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]

use Data:umper;
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.

 

[ikeon]

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:umper;
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";

 

[Huub]

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

 

[Bart Lateur]

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.

 

[Joe Smith]

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