Recommended VFP ODBC Driver?

[cuckoonuck]

I would like to use ODBC to pull data from an existing foxpro table into a Lianja virtual table. As Microsoft no longer supports its VFP ODBC driver (and does not appear to offer a download link anymore), is there a different ODBC driver that I should use? As the foxpro tables are part of an active application that will continue to be used alongside the Lianja app, importing them into Lianja is not an option. I need to be able to retrieve data from the foxpro dbf frequently using an SQL SELECT command. Updating the foxpro table will not be required.

Thanks,

Alan

[dilipkumar]

Hi Alan,

you can use ADS Drivers

http://scn.sap.com/docs/DOC-39207

Thanks,
Dilip

[barrymavin]

Hi Alan,

In fact if you USE a foxpro table NOUPDATE then you can access the data and COPY it to a Lianja table.

[cuckoonuck]

Hi Barry,

By using the COPY command, you would not use virtual tables in that case, correct?
Is the COPY command optimized to work with the existing CDX index files? Does it require exclusive access to the source dbf?
The dbfs to be accessed can be quite large and I would want the COPY to execute reasonable fast.

My goal is to be able to query the Foxpro data files in real time, rather than resorting to populating the Lianja database overnight.

Thanks,
Alan

[cuckoonuck]

Thanks, Dilip. Have you used these drivers successfully in your applications?

Alan

[barrymavin]

Hi Alan,

You would not need to use VTs at all in this use case. You would just use sqlconnect(), sqlexec() etc with an ODBC driver.

I don't know what the selection criteria is going to be. If it is from a known last record number you can GOTO that record and then use COPY REST.

Another solution is to perform your queries in VFP itself as a stored procedure and return the resultset. For example you could create a known file and have a VFP job looking for its existence. When it finds it, it performs the query (contained within the file) then leaves the resultset in a known table and deletes the text file. The Lianja side then just copies the data from that table.

The tables can be open by VFP and Lianja if you open non exclusive.

[HankFay]

Hi Alan,

I've used the ADS driver. Be aware that it does not using indexes to optimize queries.

If you don't use the field types added after VFP6, you can use those ODBC drivers (from Windows of course), and they do optimized queries with available indexes.

The VFP9 OleDB drivers work, but of course the usage is different. Using wwSQL from West Wind (a component of the wwClient tools, which have to be purchased), you can get ODBC-like commands using the VFP9 OleDB drivers.

It all depends on your Use Case as to which method you use, of all those suggested in this thread.

Hank

[cuckoonuck]

Barry, if I have VFP9 execute the queries as you suggested, could I configure VFP to INSERT (SQL) directly into the Lianja database, rather than doing it in 2 steps (creating a FoxPro table and having Lianja COPY it)?

[HankFay]

Hi Alan,

If you are using the Lianja SQL Server you can insert rows using the Lianja ODBC Driver.

The header of the Lianja DBFs is different than the VFP header. VFP can't INSERT into a DBF it can't USE.

Hank

[cuckoonuck]

Thanks, Hank. That's what I thought. If the Lianja app is using a local database (i.e. not SQL server), VFP cannot INSERT into the dbfs. But if a SQL Server is implemented (Lianja or other) it would be possible using ODBC. Is that correct?

Now if the Lianja app is running under Lianja Cloud Server, would it still be possible for VFP (running locally/not in the cloud) to connect to the Lianja SQL Server?

So, in summary, this is my scenario: I have a VFP6 app running on a local machine. I have a Lianja app running in the cloud using the Lianja SQL Server. Then I have a VFP9 program that retrieves query instructions from the Lianja app, and processes the query by scanning the VFP6 tables and inserting the matching records into the Lianja SQL database in the cloud. Is it feasible to do this?

Also, if the Lianja front end is developed on a local machine, I am assuming that it would be fairly straight-forward to move it to the cloud by changing the database connections (excuse me if my terminology is poor as I'm new to SQL client/server development).

Thanks,

Alan