
sam001
Novice
Apr 18, 2011, 9:27 AM
Post #1 of 1
(5500 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
|