in reply to Re^4: I got confuse with INSERT
in thread I got confuse with INSERT
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.
|
|---|