----- Original Message ----- From: "Ronald J Kimball" <[EMAIL PROTECTED]> To: "'Jonathan Mangin'" <[EMAIL PROTECTED]>; <[email protected]> Sent: Friday, January 06, 2006 12:35 PM Subject: RE: Can't use mysql's curdate() as bind variable?
> Jonathan Mangin [mailto:[EMAIL PROTECTED] 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.
