-----Original Message-----
From: Craig Metzer [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 26, 2007 6:36 PM
To: [email protected]
Subject: datatype error
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();
where record is:
push @record, [$src_swt, $dst_swt, otime(), $latency, $reachability,
$path];
sub otime {
my $m = (split /\s/, gmtime)[1];
my ($seconds, $minutes, $hours, $day_of_month, $month,
$year, $wday, $yday, $n) = gmtime;
if ($hours > 12) {
$hours -= 12;
$n = 'PM';
} else {
$n = 'AM';
}
return sprintf("%04d-%02d-%02d %02d:%02d:%02d",
$year+1900, $month, $day_of_month, $hours, $minutes, $seconds) . "
$n";
}
results in:
SQL type 9 for ':p3' is not fully supported, bound as SQL_VARCHAR
instead at ./atmping.pl line 124.
DBD::Oracle::st execute failed: ORA-01861: literal does not match format
string
...
I also tried 24 hr time format resulting in the same error:
...
Not sure why you are doing "eval"... plus I've rarely had to bind with
the "type" for Oracle...
But to the date issue... it has been my experience that you either set
the date format for the session or add a "to_date" function to the sql
then bind the "string" data (date) and let Oracle do the work... at
least that's easier for me...
Example:
...
$sth->prepare(q{select to_date(?,'Mon-yyyy') as First_of_Month from
dual}) or die"...";
$sth->execute('Jul-2007') or die "...";
($bom) = $sth->fetch;
...
Or...
$sth->prepare(q{select add_months(to_date(?,'Mon-yyyy'),1) - 1 as
lastDay from dual}) or die"...";
$sth->execute() or die "...";
Foreach my $date ('Jan-2007', 'Feb-2007', ...) {
$sth->bind_param($date);
my ($eom) = $sth->fetch;
}
Not tested...
Hope this helps...
jwm
