Got a situation where I prepare a DBI SQL statement handle, but when I execute it it fails saying I have a non-numeric value where it doesn't belong. However, when I substitute the variables directly into the statement and punch it into SQL plus, it works. Is there a way to tell the statement handle to print out what SQL text it thinks it's executing, complete with bound parameters?
Update: I figured out what I was doing wrong. I have a Time::Piece and I want to insert it into a date column in a table.
my $date = Time::Piece->new;
my $sql = qq{
INSERT INTO TABLE
(datecol)
VALUES
(?)
};
my $sth = $dbh->prepare($sql);
$sth->execute($date);
However, I knew that wouldn't work directly (and I figured if it did I might get bit by conversions), so I wrote a little datequote routine that returns a string TO_DATE('2003-11-18 13:15:23', 'YYYY-MM-DD HH24:MI:SS'). Then I just
$sth->execute(datequote($date));
The datequote routine was supposed to be like DBI's quote routine, but turns out it doesn't work because I have to bind a value in to that question mark, not an Oracle expression. DBI is creating a statement like
INSERT INTO table
(datecol)
VALUES
('TO_DATE(''2003-11-18 13:17:00'', ''YYYY-MM-DD HH24:MI:SS'')')
... which is absolute rubbish.
Perhaps I should browse the DBI and DBD::Oracle docs to see if there's a way to handle this.
I wish all the DBDs would integrate with Time::Piece or whatever datetime module finally emerges.
Re:it will still work
jdavidb on 2003-11-18T21:27:44
Yeah, that's what I finally did. Ideally I'd like to just hand DBI a Time::Piece and have it know what to do with it.
:D And receive the same datatype back when I do a select. And I want a pony.