CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
DBI Module Insert Dates MS Access

 



sam001
Novice

Apr 18, 2011, 9:27 AM

Post #1 of 1 (3784 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

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives