christopherbarsuk has asked for the wisdom of the Perl Monks concerning the following question:

In attempting to write a generic "insert_into" function for my DBI subclass (for a MySQL database) I came across this annoyance:

Let's say I have an array, @values = ( 'test', 'Now()' ), and I build an SQL statement like this: INSERT INTO test ( Name, EntryDate ) VALUES ( ?, ? ).

If I call prepare and execute (passing the SQL statement to prepare, and @values to execute), the date that actually gets inserted into EntryDate is null. This, I've determined, is because execute is passing the value as a string ( "Now()" ) rather than a bare word ( Now() ).

If I call prepare, then bind_param( 2, $values[1], SQL_DATE ), I get the same result. This, I presume, is because the database driver only distinguishes character and integers, and treats the SQL_DATE type like a character.

If I call bind_param( 2, $values[1], SQL_INTEGER ), then everything works fine.

So here's my question: Is this what I've got to do, or is there something I'm overlooking, some easier/better way of doing it?

Replies are listed 'Best First'.
Re: DBI placeholders, bind_values, and SQL_DATE
by runrig (Abbot) on Aug 16, 2001 at 00:33 UTC
    Assuming 'Now()' is a MySQL function, this is one place NOT to use placeholders, replace the corresponding '?' with 'Now()' and remove it from your @values list. Unless you want to get localtime() and reformat it for MySQL, you'll need to create another statement handle if you want to insert something besides Now().

    If on the other hand, Now() is a function in your perl program, remove the quotes around it in your @values assignment.

[reply]
Re: DBI placeholders, bind_values, and SQL_DATE
by dga (Hermit) on Aug 16, 2001 at 00:45 UTC

    SQL_DATE probably still needs to be quoted. I do not know in MySQL specifically but in PostgreSQL when you send a date in a query it has to be quoted. SQL_INTEGER will tell DBI not to quote it (in PgSQL) and I would think everywhere else so it will work. It does seem kind of 'workaroundish' (makes up a word) though. Maybe a comment to that effect?

    The only other way that comes to mind quickly (which isn't as efficient, unless you do lots of inserts and want the same date) would be to select Now() and save the properly formatted 'now' and use that string for inputs which would be quoted.

[reply]
Re: DBI placeholders, bind_values, and SQL_DATE
by tune (Curate) on Aug 16, 2001 at 20:11 UTC
    If you want to insert the current date every time, why don't you do this: 
    $query="INSERT INTO test ( Name, EntryDate ) VALUES ( ?, now() )";

    [download]
    or 
    $query="INSERT INTO test ( Name, EntryDate ) VALUES ( ?, CURRENT_TIMES
+TAMP )";

    [download]

    --
    tune

[reply]
[d/l]
[select]
      Thanks, but I'm writing a generic function -- one to which I can pass any number of fields/values of various types -- so I needed a way to generalize the SQL.

      (btw, I've used the first suggestion above -- rather than pass the value 'Now()' to my generic insert_into function, I convert 'Now()' to the properly formatted date value for the database (MySQL))

[reply]
        Another option... I don't know if MySQL has the NVL function, which returns the first argument if it's not null, and the second argument otherwise. With Oracle, you could do something like this: 
        $insert_sth = $dbh->prepare(<<"EndOfSQL");
  INSERT INTO members
    (member_id, name, date_joined)
  VALUES
    (?, ?, NVL(?, SysDate))
EndOfSQL

$insert_sth->execute(1, 'Bob', '2001-08-15');  # Bob actually joined y
+esterday
$insert_sth->execute(2, 'Tom', undef);         # Tom is joining now

        [download]
        So, you can bind a specific date in the execute, or you can bind undef and let the database specify the current date.
[reply]
[d/l]

Back to Seekers of Perl Wisdom