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
(DBD ERROR: error possibly near <*> indicator at char 42 in ' INSERT INTO
perfdata
VALUES ( :p1, :p2, :<*>p3, :p4, :p5, :p6 ) ') [for Statement " INSERT INTO
perfdata
VALUES ( ?, ?, ?, ?, ?, ? ) " with ParamValues: :p5=1, :p3='2007-06-26 09:59:14
PM',
:p6='', :p1='bimatm1rsf4', :p4='110', :p2='beratm1rsf1'] at ./atmping.pl line
128.
Database error: ORA-01861: literal does not match format string (DBD ERROR:
error possibly
near <*> indicator at char 42 in ' INSERT INTO perfdata VALUES ( :p1, :p2,
:<*>p3, :p4,
:p5, :p6 ) ')
I also tried 24 hr time format resulting in the same error:
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
(DBD ERROR: error possibly near <*> indicator at char 42 in ' INSERT INTO
perfdata
VALUES ( :p1, :p2, :<*>p3, :p4, :p5, :p6 ) ') [for Statement " INSERT INTO
perfdata
VALUES ( ?, ?, ?, ?, ?, ? ) " with ParamValues: :p5=1, :p3='2007-06-26
22:10:24',
:p6='', :p1='dtsatm1rsf1', :p4='10', :p2='braatm2rsf2'] at ./atmping.pl line
128.
Database error: ORA-01861: literal does not match format string (DBD ERROR:
error
possibly near <*> indicator at char 42 in ' INSERT INTO perfdata VALUES ( :p1,
:p2,
:<*>p3, :p4, :p5, :p6 ) ')
I also tried using the SQL_VARCHAR rather than SQL_DATETIME, but the same
results
Also, here's my table definition:
$sql = qq{ CREATE TABLE perfdata ( source_swt VARCHAR2(64) NOT NULL,
dest_swt VARCHAR2(64) NOT NULL,
time DATE NOT NULL,
latency NUMBER(6,2),
reachable CHAR(1),
path VARCHAR2(128)
) };
$dbh->do( $sql );
BTW - I am using Perl 5.8.5 for x86_64, DBI 1.58, DBD::Oracle 1.19, DBD::ODBC
1.13
Craig
_________________________________________________________________
See what you’re getting into…before you go there.
http://newlivehotmail.com
