Re: MySQL, DATETIME, bind, insert problem

[Stephen Collyer]

Michael A Chase wrote:

On Sat, 02 Aug 2003 12:32:42 +0000 Stephen Collyer <[EMAIL PROTECTED]> wrote:

 

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.
 

Right. This is a distinction I've not seen made in any of the DBI docs, 
but it's a
pretty important one.

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.
 

OK. That doesn't surprise me.

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.
 

That's more of a pain. I guess that implies that any SQL of the form:

INSERT INTO TestTable
(dob, profession, age, name)
VALUES
(<function>,<value1>,<value2>,<value2>)
where <function> is an arbitrary combination of functions and values
must be prepared like:
INSERT INTO TestTable
(dob, profession, age, name)
VALUES
(<function>,?,?,?)
and executed like:

$sth->execute(<value1>, <value2>, <value3>);

Steve Collyer