RE: datatype error
- From: ucantspamthis@xxxxxxxxxxx (Craig Metzer)
- Date: Fri, 27 Jul 2007 12:34:41 -0400
Not sure why you are doing "eval"... plus I've rarely had to bind with
the "type" for Oracle...
I just pulled this method from a web site. As I understand it, this is done to facilitate the error checking. I asumed this was a best method for this operation.
http://informatics.umdnj.edu/bioinformatics/courses/5004/Notes/DBI%20Examples.htm
Craig
----------------------------------------
Subject: RE: datatype error
Date: Fri, 27 Jul 2007 08:41:07 -0400
From: John.Moon@xxxxxxxxxxxxxxxxx
To: ucantspamthis@xxxxxxxxxxx; dbi-users@xxxxxxxx
-----Original Message-----
From: Craig Metzer [mailto:ucantspamthis@xxxxxxxxxxx]
Sent: Thursday, July 26, 2007 6:36 PM
To: dbi-users@xxxxxxxx
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
_________________________________________________________________
Missed the show? Watch videos of the Live Earth Concert on MSN.
http://liveearth.msn.com.
- Prev by Date: RE: datatype error
- Next by Date: Double Quoting in Table names causing dynamic method access failures
- Previous by thread: RE: datatype error
- Next by thread: RE: datatype error
- Index(es):
Relevant Pages
|
|
