Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Tuesday November 18, 2003
01:53 PM

DBI

[ #15846 ]

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • and look for ShowErrorStatement
  • Just put the TO_DATE stuff directly into your SQL statement and put the ? where the literal date is.
    • 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.

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers