RE: datatype error
- From: Philip.Garrett@xxxxxxxxxxx (Philip Garrett)
- Date: Fri, 27 Jul 2007 09:46:54 -0400
Craig Metzer wrote:
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 );
Why the '@' here? $_->[0] would be less confusing (to me, anyway).
$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();
You really don't need to specify the bind_type parameters.
Using Oracle, you will either need to use the TO_DATE() function:
insert into perfdata (time)
values (to_date(?,'YYYY-MM-DD HH24:MI:SS'))
Or, set the NLS_DATE_FORMAT session variable, which Oracle uses
implicitly to convert strings into dates:
/* just once, after you connect */
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
insert into perfdata (time) values (?)
Then, just bind as strings (which is the default):
$sth->execute(@$_);
Regards,
Philip
.
- 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
|
