RE: datatype error
- From: PCAPACIO@xxxxxxxxx (Paula J Capacio)
- Date: Fri, 27 Jul 2007 08:06:10 -0500
-----Original Message-----wondering
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
if anyone could point me in the right direction -- ThanksHere's the code:
I'm trying to run an insert ... the date value is giving me errors.
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);
}
.
- References:
- datatype error
- From: Craig Metzer
- datatype error
- Prev by Date: RE: datatype error
- Next by Date: RE: datatype error
- Previous by thread: RE: datatype error
- Next by thread: RE: datatype error
- Index(es):
Relevant Pages
|
|
