Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > SQL statement in Perl doesn't work

Reply
Thread Tools

SQL statement in Perl doesn't work

 
 
Huub
Guest
Posts: n/a
 
      04-26-2007
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
 
Reply With Quote
 
 
 
 
Huub
Guest
Posts: n/a
 
      04-26-2007
>
> 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".
 
Reply With Quote
 
 
 
 
Brian McCauley
Guest
Posts: n/a
 
      04-26-2007
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.

 
Reply With Quote
 
Huub
Guest
Posts: n/a
 
      04-26-2007
> 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.
 
Reply With Quote
 
J. Gleixner
Guest
Posts: n/a
 
      04-26-2007
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.
 
Reply With Quote
 
Brian McCauley
Guest
Posts: n/a
 
      04-27-2007
On Apr 26, 9:05 pm, Glenn Jackman <(E-Mail Removed)> 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.

 
Reply With Quote
 
Brian McCauley
Guest
Posts: n/a
 
      04-27-2007
On Apr 26, 8:44 pm, "J. Gleixner" <(E-Mail Removed)>
wrote:

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


 
Reply With Quote
 
ikeon
Guest
Posts: n/a
 
      04-27-2007
On Apr 26, 9:44 pm, "J. Gleixner" <(E-Mail Removed)>
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: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";

 
Reply With Quote
 
Huub
Guest
Posts: n/a
 
      04-27-2007
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
 
Reply With Quote
 
Bart Lateur
Guest
Posts: n/a
 
      04-27-2007
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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
executing an sql statement in perl MMWJones@googlemail.com Perl Misc 11 05-22-2007 08:00 PM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM
DBI SQL column datatype not jiving with SQL statement requirement dna Perl 1 01-18-2004 04:15 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page David Browne ASP .Net 0 08-21-2003 10:43 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page William \(Bill\) Vaughn ASP .Net 0 08-21-2003 10:41 PM



Advertisments