RE: datatype error
- From: ucantspamthis@xxxxxxxxxxx (Craig Metzer)
- Date: Mon, 30 Jul 2007 12:31:55 -0400
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: PCAPACIO@xxxxxxxxx> To: ucantspamthis@xxxxxxxxxxx> CC: dbi-users@xxxxxxxx> > >-----Original Message-----> >From: Craig Metzer [mailto:ucantspamthis@xxxxxxxxxxx] > >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
- Prev by Date: Re: Recovering records from corrupted MSSQL tables
- Next by Date: FW: Double Quoting in Table names causing dynamic method access failures
- Previous by thread: RE: datatype error
- Next by thread: Double Quoting in Table names causing dynamic method access failures
- Index(es):
Relevant Pages
|
