Can"t use mysql"s curdate() as bind variable?

curdate() works if $edate is embedded directly in my
sql statement, but not as a bind variable. $bdate works
fine.

my $bdate = $q->param('bdate') || '%';
my $edate = $q->param('edate') || 'curdate()';

my $sql = "create table $temp_tbl
(date date,
uid varchar(14))
engine = memory
select date,
? as uid
from calendar
where date between ? and $edate";
my $sth = $dbh->prepare($sql);
$sth->execute($uid, $bdate) || die $sth->errstr();

It's mostly just irritating.
Am I doing something wrong?

Thanks,
Jon

Re: Can"t use mysql"s curdate() as bind variable?

curdate() is a function, not a data value.


On 1/6/06 10:18, "Jonathan Mangin" wrote:

> curdate() works if $edate is embedded directly in my
> sql statement, but not as a bind variable. $bdate works
> fine.
>
> my $bdate = $q->param('bdate') || '%';
> my $edate = $q->param('edate') || 'curdate()';
>
> my $sql = "create table $temp_tbl
> (date date,
> uid varchar(14))
> engine = memory
> select date,
> ? as uid
> from calendar
> where date between ? and $edate";
> my $sth = $dbh->prepare($sql);
> $sth->execute($uid, $bdate) || die $sth->errstr();
>
> It's mostly just irritating.
> Am I doing something wrong?
>
> Thanks,
> Jon
>

RE: Can"t use mysql"s curdate() as bind variable?

Jonathan Mangin [mailto:jon.mangin@comcast.net] wrote:
>
> curdate() works if $edate is embedded directly in my
> sql statement, but not as a bind variable. $bdate works
> fine.
>
> my $bdate = $q->param('bdate') || '%';
> my $edate = $q->param('edate') || 'curdate()';
>
> my $sql = "create table $temp_tbl
> (date date,
> uid varchar(14))
> engine = memory
> select date,
> ? as uid
> from calendar
> where date between ? and $edate";
> my $sth = $dbh->prepare($sql);
> $sth->execute($uid, $bdate) || die $sth->errstr();
>
> It's mostly just irritating.
> Am I doing something wrong?

Yes. You can only bind values; you can't bind mysql functions. Try this
instead:

my $bdate = $q->param('bdate') || '%';
my $edate = $q->param('edate');

my $sql = <<"EndOfSQL";
CREATE TABLE $temp_tbl
("date" DATE,
uid VARCHAR(14))
engine = memory
SELECT "date",
? as uid
FROM calendar
WHERE "date" BETWEEN ? AND IFNULL(?, CURDATE())
EndOfSQL
my $sth = $dbh->prepare($sql);
$sth->execute($uid, $bdate, $edate);

So if you bind NULL, curdate() will be used instead.

By the way, you really shouldn't use reserved words (e.g. date) as column
names. It will just come back to bite you later.

HTH,
Ronald

Re: Can"t use mysql"s curdate() as bind variable?

----- Original Message -----
From: "Ronald J Kimball"
To: "'Jonathan Mangin'" ;
Sent: Friday, January 06, 2006 12:35 PM
Subject: RE: Can't use mysql's curdate() as bind variable?


> Jonathan Mangin [mailto:jon.mangin@comcast.net] wrote:
> >
> > curdate() works if $edate is embedded directly in my
> > sql statement, but not as a bind variable. $bdate works
> > fine.
> >
> > my $bdate = $q->param('bdate') || '%';
> > my $edate = $q->param('edate') || 'curdate()';
> >
> > my $sql = "create table $temp_tbl
> > (date date,
> > uid varchar(14))
> > engine = memory
> > select date,
> > ? as uid
> > from calendar
> > where date between ? and $edate";
> > my $sth = $dbh->prepare($sql);
> > $sth->execute($uid, $bdate) || die $sth->errstr();
> >
> > It's mostly just irritating.
> > Am I doing something wrong?
>
> Yes. You can only bind values; you can't bind mysql functions. Try this
> instead:
>
> my $bdate = $q->param('bdate') || '%';
> my $edate = $q->param('edate');
>
> my $sql = <<"EndOfSQL";
> CREATE TABLE $temp_tbl
> ("date" DATE,
> uid VARCHAR(14))
> engine = memory
> SELECT "date",
> ? as uid
> FROM calendar
> WHERE "date" BETWEEN ? AND IFNULL(?, CURDATE())
> EndOfSQL
> my $sth = $dbh->prepare($sql);
> $sth->execute($uid, $bdate, $edate);
>
> So if you bind NULL, curdate() will be used instead.
>
> By the way, you really shouldn't use reserved words (e.g. date) as column
> names. It will just come back to bite you later.
>
> HTH,
> Ronald
>
Must be a great deal more to binding than I'm
aware of. Not surprising. I assumed a simple
string substitution. I will adopt ifnull().
Thanks.