This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: curdate() problem
From: "Mark Cain" <mark@markcain.com>
Date: Sat, 5 Aug 2000 21:19:16 -0400
At the mysql monitor this works:
SELECT * from orderdata where orderDate = CURDATE();
in a perl script using DBI this works:
#appropriate connections before this line
my $sth = $dbh->prepare( q{SELECT * from orderdata where orderDate =
"2000-08-05" });
$sth->execute();
in a perl script using DBI this does NOT work:
#appropriate connections before this line
my $sth = $dbh->prepare( q{SELECT * from orderdata where orderDate =
CURDATE() });
$sth->execute();
The error message says:
Can't call method "execute" on an undefined value
What am I missing?
Details:
Server version 3.22.26a
OS freeBSD 4.0
mark@markcain.com
===
Subject: Re: curdate() problem
From: "Mark Cain" <mark@markcain.com>
Date: Sun, 6 Aug 2000 07:11:15 -0400
"Alessio Bragadini" <alessio@sevenseas.org> wrote:
> > #appropriate connections before this line
> > my $sth = $dbh->prepare( q{SELECT * from orderdata where orderDate =
> > CURDATE() });
> > $sth->execute();
> >
> > The error message says:
> > Can't call method "execute" on an undefined value
> >
> > What am I missing?
>
> At least,
> my $sth = $dbh->prepare( q{SELECT * from orderdata where orderDate =
> CURDATE() }) or die $DBI::errstr;
>
> Then check the error message.
Where it says:
#appropriate connections before this line
also includes this (notice the RasieError):
my $dbh = DBI->connect($database,$db_user,$db_password,{'RaiseError' => 1})
or die "Couldn't connect to database: " . DBI->errstr;
Doesn't the RaiseError set here at the connection do the samething and hence
yeild me the error message included in the original post? or will your
suggestion give me a new "level" of error?
===
Subject: Re: curdate() problem
From: Paul Lukins <plukins@flashcom.net>
Date: Sun, 06 Aug 2000 08:26:58 -0700
Your SQL syntax looks ok. Try using:
DBI->trace( 2, "trace.txt" );
before connecting to the db to get a better picture of what's going on.
===
Subject: Re: curdate() problem
From: "Michael A. Chase" <mchase@ix.netcom.com>
Date: Sun, 6 Aug 2000 09:57:27 -0700
"Mark Cain" <mark@markcain.com> wrote:
> "Alessio Bragadini" <alessio@sevenseas.org> wrote:
> > > #appropriate connections before this line
> > > my $sth = $dbh->prepare( q{SELECT * from orderdata where orderDate =
> > > CURDATE() });
> > > $sth->execute();
> > >
> > > The error message says:
> > > Can't call method "execute" on an undefined value
The error message means exactly what it says. This error is
raised by Perl, not DBI and is not affected by RaiseError.
Somehow, $sth is undefined at the time you try to call
$sth->execute(). That implies that, for some reason, you
are not getting the error message you should from
$dbh->prepare(). If RaiseError is working, adding 'or die
$DBI::errstr' will do no harm. If it isn't, then you will
see the error message. Calling DBI->trace() as Paul
suggested would also help you see where the problem starts.
The error message you see is just a reflection of an earlier
problem.
===
Subject: Re: curdate() problem
From: "Mark Cain" <mark@markcain.com>
Date: Sun, 6 Aug 2000 14:46:20 -0400
look below:
Mark
----- Original Message -----
From: "Michael A. Chase" <mchase@ix.netcom.com>
To: "Mark Cain" <mark@markcain.com>
Cc: <dbi-users@isc.org>; "Alessio Bragadini" <alessio@sevenseas.org>; "Paul
Lukins" <plukins@flashcom.net>
Sent: Sunday, August 06, 2000 12:57 PM
Subject: Re: curdate() problem
A couple of things: 1 new and the clarification of what was
already posted.
As for what was already posted.
The code works when "2000-08-06" is used in place of
CURDATE(). So, wouldn't that rule out the possibility that
the problem is somewhere else (such as the connection)? but
rather point to the fact that the problem lies in the use of
CURDATE()? Hence, my original post says to the effect: 1)
CURDATE() works on the monitor, 2) using the date as a
string works in the script, 3) CURDATE() does not work in
the script but produces a specific error.
Thanks for the suggestion of using trace. I expected that
and had in fact already run the script with trace(2,
"out.txt") and tried to answer the question for myself
before I created traffic on the list. The report from trace
says little to me other than there is something wrong with
the use of CURDATE(). Here is the report from trace showing
a good connection to the database and a failure at the use
of CURDATE(). It is curious to me that the result is 0
while the use of "2000-08-06" returns records found. :
<-- begin report snip-->
imp_dbh->MyConnect: client_flags = 0
<- connect= DBI::db=HASH(0x8129f0c) at DBI.pm line 382.
-> STORE for DBD::mysql::db (DBI::db=HASH(0x8122760)~INNER 'RaiseError'
1)
<- STORE= 1 at DBI.pm line 407.
-> STORE for DBD::mysql::db (DBI::db=HASH(0x8122760)~INNER 'PrintError'
1)
<- STORE= 1 at DBI.pm line 407.
-> STORE for DBD::mysql::db (DBI::db=HASH(0x8122760)~INNER 'AutoCommit'
1)
<- STORE= 1 at DBI.pm line 407.
<- connect= DBI::db=HASH(0x8129f0c)
-> prepare for DBD::mysql::db (DBI::db=HASH(0x8129f0c)~0x8122760 '
SELECT * from orderdata where orderDate = CURDATE() ')
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0x8129f3c) at testscript.pl line 415.
-> DESTROY for DBD::mysql::st (DBI::st=HASH(0x8122754)~INNER)
<- DESTROY= undef at unknown location!
-> DESTROY for DBD::mysql::db (DBI::db=HASH(0x8122760)~INNER)
IMPLICIT ROLLBACK ON DESTROY of database handle that is still connected!
Rollback ineffective while AutoCommit is on error 15 recorded: Rollback
ineffective while AutoCommit is on
imp_dbh->svsock: 8109a04
<- DESTROY= undef at unknown location!
-> DBI::END
<-- end report snip-->
Thanks again for your help.
===
Subject: Re: curdate() problem
From: "Michael A. Chase" <mchase@ix.netcom.com>
Date: Sun, 6 Aug 2000 12:02:57 -0700
> <-- begin report snip-->
> imp_dbh->MyConnect: client_flags = 0
> <- connect= DBI::db=HASH(0x8129f0c) at DBI.pm line 382.
> -> STORE for DBD::mysql::db (DBI::db=HASH(0x8122760)~INNER
'RaiseError'
> 1)
> <- STORE= 1 at DBI.pm line 407.
> -> STORE for DBD::mysql::db (DBI::db=HASH(0x8122760)~INNER
'PrintError'
> 1)
> <- STORE= 1 at DBI.pm line 407.
> -> STORE for DBD::mysql::db (DBI::db=HASH(0x8122760)~INNER
'AutoCommit'
> 1)
> <- STORE= 1 at DBI.pm line 407.
> <- connect= DBI::db=HASH(0x8129f0c)
> -> prepare for DBD::mysql::db (DBI::db=HASH(0x8129f0c)~0x8122760 '
> SELECT * from orderdata where orderDate = CURDATE() ')
> Setting mysql_use_result to 0
> <- prepare= DBI::st=HASH(0x8129f3c) at testscript.pl line 415.
This is a bit puzzling. It appears that the statement handle is being
destroyed as if it were going out of scope. Also, considering the line
numbers, you have a much more complex script than necessary for
demonstrating the problem.
Try creating a script that just connects, prepares this statement, executes
it, and fetches the results. That would eliminate the other possible
complications. If the SQL with CURDATE() fails and the SQL the date string
succeeds, then you'll just have to avoid CURDATE(). Perhaps you could get
the date in your Perl code, put it in the correct string format, and use a
placeholder.
If the exact same script works if you substitute a date string for
CURDATE(), perhaps CURDATE() is handled by the mysql monitor. I don't use
mysql, so I couldn't say for sure.
===
Subject: Re: curdate() problem
From: Paul Lukins <plukins@flashcom.net>
Date: Sun, 06 Aug 2000 12:30:04 -0700
I tried preparing a statement using the CURDATE() function and it workes fine:
-----------Test script:-----------
#!/usr/bin/perl -w
use DBI;
DBI->trace( 2, "trace.txt" );
my $dbh = DBI->connect( "DBI:mysql:test", "test", "test", {
PrintError => 0,
RaiseError => 1
}) || die $DBI::errstr;
my $sth = $dbh->prepare( q{
SELECT * from test
WHERE date = CURDATE()
});
$sth->execute();
while ( @row = $sth->fetchrow_array ) {
print "Row data: ", @row, "\n";
}
-----------Output:-----------
Row data: 12000-08-06 #('1' is the id)
-----------Trace:-----------
<snip>
<- connect= DBI::db=HASH(0x8181fdc)
-> prepare for DBD::mysql::db (DBI::db=HASH(0x8181fdc)~0x8182c1c '
SELECT * from test
WHERE date = CURDATE()
')
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0x8182de4) at test.pl line 12.
-> execute for DBD::mysql::st (DBI::st=HASH(0x8182de4)~0x8139970)
-> dbd_st_execute for 08139994
<- dbd_st_execute 1 rows
<- execute= 1 at test.pl line 17.
-> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x8182de4)~0x8139970)
-> dbd_st_fetch for 08139994, chopblanks 0
Storing row 0 (1) in 081399d0
Storing row 1 (2000-08-06) in 081399dc
<- dbd_st_fetch, 2 cols
<- fetchrow_array= ( '1' '2000-08-06' ) [2 items] at test.pl line 19.
-> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x8182de4)~0x8139970)
-> dbd_st_fetch for 08139994, chopblanks 0
<- fetchrow_array= ( ) [0 items] at test.pl line 19.
-> finish for DBD::mysql::st (DBI::st=HASH(0x8182de4)~0x8139970)
<- finish= 1 at test.pl line 23.
-> disconnect for DBD::mysql::db (DBI::db=HASH(0x8181fdc)~0x8182c1c)
<snip>
Perhaps there is something else in your code (not shown) that is causing your
$sth variable to go out of scope before the execute?
===