On Sat, 02 Aug 2003 12:32:42 +0000 Stephen Collyer <[EMAIL PROTECTED]> wrote:
> I have a problem binding arbitrary data to a MySQL DATETIME
> column during an insert.
>
> I generate a SQL string like so:
>
>
> INSERT INTO TestTable
> (dob, profession, age, name)
> VALUES
> (?,?,?,?)
>
> where dob is of type DATETIME.
>
> This code is prepared and executed like so:
>
> $sth = $dbh->prepare($SQL);
> $result = $sth->execute(@bind_params);
>
> where $SQL contains the insert sql as above and @bind_params
> contains the bind data.
>
> 1. When @bind_params contains, say, ('19970523', 'Doctor', 30, 'Bill'),
> all is well and MySQL interprets '19970523' correctly as a valid
> format for a DATETIME field, and generates the correct entry in the dob
> field.
>
> 2. When @bind_params contains, say, ('NOW()', 'Doctor', 30, 'Bill'),
> it all goes horribly wrong, and MySQL fails to interpret the 'NOW()'
> string as an attempt to call a MySQL specific function.
DBD::mysql is doing what is it supposed to do. 'NOW()' is a 5
character string, not a function. Placeholders pass values, not
syntactic elements.
> a) Is it possible to solve this problem by passing the bind params
> to execute ? (AFAICS, it's not possible).
No. You are already doing that.
> b) Can this problem be solved by calling bind_param explicitly for
> each argument, and passing an explicit bind type, say SQL_TIMESTAMP
> for the dob column ? If so, how would any given MySQL field type
> (DATETIME,DATE, TIMESTAMP, etc) map onto the SQL_* constants available
> in DBI ?
No.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
