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.This raises 2 questions:
a) Is it possible to solve this problem by passing the bind params to execute ? (AFAICS, it's not possible).
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 ?
TIA.
Steve Collyer
