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
