>-----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);
}
