FoxPro and Sql Server

[topgunf25]

I am trying to convert a large application from Foxpro tables to SQL Server Databases, using ODBC.

I have been fairly successful, but having problem using indexes as I would in Foxpro. Select statements are fine, but how do you get "Seeks" to work against SQL database?

The only thing I have gotten to work is creating a remote view of the data and building an index on the view. But, this only works if you keep the file open, and you must rebuild the index each time you connect to the database. It looks like the remote views are working like cursor tables in foxpro.

Would appreciate any thoughts?

 

[weedz]

In our company there used to be a project where this was supposed to be done. Unfortunately the project was cancelled due to other reasons (not related to the VFP => SQL conversion), but I remember having a list with VFP command that would not work with SQL Server.

Iso SEEK we were forced to use LOCATE (at least that is the one I remember). Sonce LOCATE is a SQL function itself.

HTH,

Weedz (Wietze Veld)
My private project:

http://www.crowncap.demon.nl\info\crwnbase

Download the CrownBase source code !!

 

[fluteplr]

You need to change the way you are working. In a client server application you only retrieve the records you need from the server. (Look at parameterized views). If you are retrieving more than a few hundred records from the server at anyone time you probably need to rethink your code. If you are only retrieving a few hundred records a locate is pretty much as fast as a seek. About the only time I index a remote view is if I need to change the order in which the data is being displayed.

 

[DSummZZZ]

Like fluteplr says, you need to change your thinking. You aren't really working with tables the same way as you would if you opend up a Fox table when using SQL via ODBC. More like what you said, a cursor. You can grab one record, or a set of records from the SQL database and throw it in a view, but you can't actually move around the SQL table(s) using SEEK or SKIP etc., the way you would a Fox table. After retrieving the record(s) from the SQL table into your view which match the criteria contained in your SELECT statement, you can then treat it like a Fox cursor.

Dave S.

 

[topgunf25]

Thanks for the input, every thing works great as long as I create the same Fox indexes on the remote view. The only problem is, that once you close the remote view, you lose the indexes. Otherwise you can't tell the difference between reading a Fox table/database, i.e. indexes, filters, setting table orders. It looks like you could create a remote view to anything and it work the same in the Fox application.

I guess you get the best of both database engines.

 

[fluteplr]

Except for the incredably bad response time the first time you try to open a nonparameterized remote view on a table with a couple of million records in it.