sam001
Novice
Apr 18, 2011, 9:27 AM
Post #1 of 1
(3980 views)
|
|
DBI Module Insert Dates MS Access
|
Can't Post
|
|
Hi,
I'm trying to use the DBI module to insert values into MS Access. I found that binding variables is the best way as it inserts memo fields properly.
However, I'm having trouble insert the date.
In the code below, I have tried using functions like to_date or just date before the ?. I also tried &{"DBI::SQL_DATE"} but it gives me error DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast specification (SQL-22018).
The date values are in 5,8 and 9 of the array i.e bind values 6, 10, 11.
Does anyone know the best way to format dates for MS Access using the Perl DBI Module?
my $insertsql = qq/
INSERT INTO school VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
/;
#For each node, read the cell values
for ($i=0; $i<$countrows; $i++)
{
foreach $e(@{$data->{DATA_PROVIDER}->{ROW}->[$i]->{CELL}})
{
$columnid = $e->{INDEX};
$columnvalue = $e->{content};
#clean column value
#$columnvalue =~ s/[\x80-\xFF]//;
$rowdata{$columnid} = $columnvalue;
}
$sth->bind_param(1,$rowdata{0},&{"DBI::SQL_VARCHAR"});
$sth->bind_param( 2, $rowdata{1}, &{"DBI::SQL_VARCHAR"});
$sth->bind_param( 3, $rowdata{2}, &{"DBI::SQL_VARCHAR"});
$sth->bind_param( 4, $rowdata{3}, &{"DBI::SQL_VARCHAR"});
$sth->bind_param( 5, $rowdata{4}, &{"DBI::SQL_VARCHAR"});
$sth->bind_param( 6, $rowdata{5}, &{"DBI::SQL_DATE"});
$sth->bind_param( 7, $rowdata{6}, &{"DBI::SQL_VARCHAR"});
$sth->bind_param( 8, $rowdata{7}, &{"DBI::SQL_VARCHAR"});
$sth->bind_param( 9, $rowdata{8}, &{"DBI::SQL_VARCHAR"});
$sth->bind_param( 10, $rowdata{9},&{"DBI::SQL_DATE"});
$sth->bind_param( 11, $rowdata{10},&{"DBI::SQL_DATE"});
$sth->bind_param( 12, $rowdata{11}, &{"DBI::SQL_LONGVARCHAR"});
$sth->execute;
Many Thanks
Sam
|
MAIN
SEARCH
WHO'S 
LOG 