Re^4: I got confuse with INSERT

[padawan_linuxero]

yes is like this:
We have a very obsolete program that runs on FoxPro for DOS
but for a big reason ($$$$) we cant change it, our customers
want to see the data send to us in a more easy way like a web page
so I need to take the data from several DBF and turn them into MySQL tables to work with them the thing is that in the convertion is taking a lot of time, and the clients wants to see them in real time
working with mysql is quite fast but when I need to do the part of bringing new data from dbf to mysql it take sometime like 7 minutes for one table (a small one) so imagine a big table with almost 3 times that size it takes forever.
So I try to do it directly with DBF but it takes more time.

[twotone]

I have been working with foxpro data lately and may be able to help you access and manipulate the data directly from the .dbf files. If you are on a windows machine you can connect using DBI with DBD::ADO module installed.

FYI, my data is in individual dbf tables, not a unified foxpro database. Here is what I did:

#!/usr/bin/perl -w
use strict;
use DBI;

# requires installing perl module DBD::ADO
# and microsoft ole db driver for foxpro - avialable from microsoft de
+veloper website

my $connect_string = 'Provider=VFPOLEDB.1;Data Source=C:\foxpro\folder
+;Mode=ReadWrite|Share Deny None;Password="";Collating Sequence=MACHIN
+E'; # ADO Connection String
my $dbh = DBI->connect("dbi:ADO:$$connect_string") or die("Can't conne
+ct: $DBI::errstr");

# note: haven't been able to figure out using placeholders yet, or if 
+it is even supported - i.e. "select * from table where id = ?"

# table in this statement is your dbf file, i.e. table.dbf

my $sth = $dbh->prepare("select * from table where date between {d '20
+08-08-01'} and {d '2008-08-16'} order by date") or die( "Database Err
+or: $DBI::errstr" );
$sth->execute() or die( qq(Database Error: $DBI::errstr) );

while (my $r = $sth->fetchrow_hashref) {
    # do stuff with $r->{column_name}, etc.
    # helpful note: @{$r->{NAME}} works with foxpro/ado connection - g
+ives you an array of the table column names
}

$sth = $dbh->prepare("insert into table (column1,column2,date) values 
+('Hi','There',{^2008-08-16})") or die( "Database Error: $DBI::errstr"
+ );
$sth->execute() or die( qq(Database Error: $DBI::errstr) );

# notice the difference in date formats for select and insert - took m
+e a while to figure that out
# if you have foxpro tables that rely on indexes, you can select just 
+fine
# and you can insert, but I don't know how to update the index file
# anyone else know?

$sth->finish;
$dbh->disconnect;

exit;

If anyone knows how to work with foxpro indexes, especially to update the index on an sql insert, that would be very helpful information for me.