Many thanks to all who provided assistance.
The problem was the date format I was using. The system default was
"YYYY-Mon-DD", which didn't provide the time info I needed. My options were:
1. To specify a format for each transaction using the to_date function.
2. To set the default format for my session using the "alter session set
NLS_TIME_FORMAT" statement.
I chose the latter, added one line of code after I made the DB connection
(applied to my DBI object)
$dbh = DBI->connect( .....);
$dbh->do('ALTER SESSION SET NLS_TIME_FORMAT = "YYYYDDMM HH24:MI:SS"');
....
Then instead of using the SQL_DATETIME type, I used the SQL_VARCHAR:
from:
$sth->bind_param( 3, @$_->[2], SQL_DATETIME );
to:
$sth->bind_param( 3, @$_->[2], SQL_VARCHAR );
Thanks again,
Craig
> Subject: RE: datatype error> Date: Fri, 27 Jul 2007 08:06:10 -0500> From:
> [EMAIL PROTECTED]> To: [EMAIL PROTECTED]> CC: [email protected]> >
> >-----Original Message-----> >From: Craig Metzer [mailto:[EMAIL PROTECTED] >
> >Sent: Thursday, July 26, 2007 5:36 PM> >I'm getting an error trying to run
> an insert action due to the DATE > >or DATETIME datatype. I couldn't find
> anything on it, and was> wondering> >if anyone could point me in the right
> direction -- Thanks> >I'm trying to run an insert ... the date value is
> giving me errors.> Here's the code:> > my $sql = qq{ INSERT INTO perfdata
> VALUES ( ?, ?, ?, ?, ?, ? ) };> > my $sth = $dbh->prepare( $sql );> > for (
> @record ) {> > eval {> > $sth->bind_param( 1, @$_->[0], SQL_VARCHAR );> >
> $sth->bind_param( 2, @$_->[1], SQL_VARCHAR );> > $sth->bind_param( 3,
> @$_->[2], SQL_DATETIME );> > $sth->bind_param( 4, @$_->[3], SQL_INTEGER );> >
> $sth->bind_param( 5, @$_->[4], SQL_CHAR );> > $sth->bind_param( 6, @$_->[5],
> SQL_VARCHAR );> > $sth->execute();> > $dbh->commit();> > };> > if ( $@ ) {> >
> warn "Database error: $DBI::errstr\n";> > $dbh->rollback(); #just die if
> rollback is failing> > }> > }> > $sth->finish();> > Perhaps...I'm no
> expert...you need to use the TO_Date function> and specify the format your
> string is in.> I have code from an insert process that used the following
> SQL> statement, > unfortunately the input data file no longer exists so I
> can't look at> what the format was. > HTH, Paula> (watch out for text
> wrapping of code by mail client)> > sub prepareInsStmt { #set up and prepare
> the SQL statement> my $seConn = $_[0];> my $stmt1 = 'INSERT INTO
> SEWEB.logfile ';> my $stmt2 = '(userid, update_ts, category) ';> my $stmt3 =
> "values (?,TO_Date(?,'YYYY-MM-DD HH24:MI:SS'),?)";> my $stmt =
> $stmt1.$stmt2.$stmt3;> my $sth = $seConn->prepare( $stmt ) or>
> processError("PrepareFailed","$stmt",$seConn); > printIt("Insert statement
> prepared:\n\t$stmt");> return $sth; #return the prepared statement handle> }>
> This subroutine was called via:> my $isth = prepareInsStmt($seConn); #prepare
> INSERT w/ placeholder> > And the inserts processed via:> while (<HIST>) {>
> chomp;> my ($uid,$datets,$category,$desc) = split /,/,$_;>
> $isth->execute($uid,$datets,$category) > or
> processError("ExecuteFailed",$isth,$seConn);> }
_________________________________________________________________
Local listings, incredible imagery, and driving directions - all in one place!
Find it!
http://maps.live.com/?wip=69&FORM=MGAC01
