HOW CAN I MAKE A REMOTE VIEW IN APPLICATION IN VFP TO CLIENT SERVER FAST METHOD ?

[farzad321]

Dear Sir.

I have some free table with about 200,000 record in all of that. at the moment i create a database for creating local view and use it, and all application saved in my 2012 R2 server. users have a map drive and then run the program in any client.
This is my question. when i want to extract for example 30 record from this free table , system must read and transfer all 200,000 record to client system and local view find 30 record on it. this is make a long time and more traffic on LAN . Is there any way that server find this 30 record and send it to client?
I know we have a remote view to connect remotely but I can not set it for connection problem. I think i need to ODBC driver for vfp to make data source. I can not find this driver anywhere. or any other way to make a remote view.

therefore if you know any document or driver place that can help me , I will be very happy.

with best regards

farzad

 

[Chris Miller]

A local view does open the DBF it queries, but that doesn't mean it loads the full table.
The workarea used for the view only contains the result rows.

What is worse than the usual client/servrer pproach is that it takes a few steps for rushmore optimization to determine rows to be fetched and unoptimized queries could fetch more rows than finally necessary, but you gain a lot of performance even withou full rushmore optimization if the indexes you have are selective enough to get say 2% of the table of which 50% are in the actual result.

Remote views don't really make this better, as they are running with the local ODBC driver which has a limited VFP runtime in it and acts from the client, not on a server. You only gain the possibility to access dbf data for other programming languages.

What can turn access to DBFs to a real remote client/server access is using an MSSQL server and its linked server feature to link to a DBC or a directory of free tables.


Chriss

 

[Chris Miller]

By the way, you can get the ODBC driver by using the runtime installer from the former ProLib company.

https://github.com/VFPX/VFPRuntimeInstallers

The original MS ODBC driver included in the installer is version 6, the latest from MS, so you don't have support for all modern field types free tables also support. But you may have old version DBFs for which it is fine.

Chriss

 

[farzad321]

Thank you Chris
Is there any way that I run Vfp application with Free table via MSSQL or I must install SQL SERVER and create table on it ?
I think remote view work on server and send the result to client.
Can you refer me documents about making connection in database ? then i can make remote view. until now i can not make a connection that works!!!
thanks again for you time.
farzad

 

[Chris Miller]

MSSQL is MS SQL Server

A linked server works as if it was an SQL Server database, but the DBFs are used by SQL Server. Of course you therefore need to install SQL Server, the Express version is sufficient. Better find the 32bit version to make use of the 32bit VFP OLEDB Provider.

And as said remote views don't really make it better than local views. You get a separate dataasession you can't access from your main VFP process when going through ODBC, but the ODBC driver is just also not adressing a server, there are only the DBFs, no server.

The linked server changes that, as you now actually connect to the MSSQL Server and make queries through it.

Chriss

 

[Chris Miller]

I think remote view work on server and send the result to client.

No, that's only half the truth. Like local views remote view definition (and the query itself) are stored in a VFP DBC. Remote views differ from local views by making an ODBC connection to a remote database.

Now, in case you make a folder of DBFs the "remote" database - which is possible by using the VFP ODBC driver - you don't connect to a remote server, the ODBC driver itself is "the server" and queries DBFs with the VFP SQL engine embedded in the ODBC driver. This surfaces in the fact on how you define the ODBC connection with a path to a DBC or a directory of free DBFs with a path either using a mapped drive or a UNC path. LAN is the limit, you don't connect to a server but to a share.

You could talk of remote execution if you'd manage to install the VFP ODBC driver on the file server with the network share, but then please tell me: How do you make an ODBC connection to a remotely installed ODBC driver? The ODBC driver used to connect to a database has to be locally installed at the client, that's also the case with MSSQL or MySQL servers, etc. There are always client side drivers the local software interacts with.

The difference of such real database servers is, that the ODBC driver to server databases doesn't act as the sql engine itself as the VFP driver, it is just an agent talking to the remote server and receiving results from it.

And there you also have the way an MSSQL server helps you to really make the query execution remote, by using MSSQL as the server component.

And no, you don't need to create MSSQL tables. Surely that would open up many advantages and features MSSQL can do with its own storage engine, but to explain the term "linked server" a bit more: It has the historic background that at first you could use an SQL server instance on one database server to access another SQL instance instead of migrating the database. The "links" now are not only limited to real servers, but any data source/sink which can be defined by ODBC or OLEDB.

So in this way you actually get what I said is not possible directly. The VFP ODBC driver or the OleDB Provider is installed on the server side, the MSSQL server on that server (computer) uses it, and your VFP frontend addresses this construct by an MSSQL ODBC driver which menas you go through the three agents (or brokers) - 1. the MSSQL ODBC driver (clientside), 2. the MSSQL server (server side), 3. the VFP ODBC driver or OLEDB provider (server side).

So only that can give you real remote execution of queries to DBFs.

What is true is that remote views execute queries remote in the usual case the remote backend is not VFP but a server, a database server/service, not just a file server.

When you fully move to MSSQL databases you spare one level of agent on the path from frontend to data, but that'll take time to rewrite. The linked server means you keep the folder of DBFs (free or part of a DBC doesn't matter) and so you can use that in parallel to native USE of the DBFs, you stay with DBFs and can continue to run legacy code needing them but also have a remote server that can act for you via MSSQL ODBC.

In summary the needed ingredients are:

1. Your DBFs where they currently are - provided it's a Windows Server allowing to install MSSQL in parallel.
In theory this can be on another computer, but that makes DBF access networed additional to the networked access of MSSQL itself. For best performance the DBFs should be local to the MSSQL server, just like its native databases are local to it (disregarding more complex scenarios with SAN, for example).

2. An SQL Server Express insance installed on that same server. (32bit version)
3. A VFP ODBC driver or the OleDB Provider (32bit)
If you find third party 64bit drivers/providers - fine, then you can also let a 64bit MSSQL Server act on your DBFs
4. A 32bit ODBC driver for MSSQL access on the client running your VFP EXE.

If you have your DBFs in a NAS (usually Linux based) there's a low chance you can use Microsofts Linux version of MSSQL Server, but AFAIK only a full blown Linux server will give you enough freedom to install that.

I haven't looked but it's still no problem to get 32bit MSSQL server and drivers, just note a 64bit version of MSSQL usually is bundled with 64bit client tools (the MSSQL ODBC drivers, for example) and the VFP exe needs a 32bit ODBC driver. A client side 32bit ODBC driver can also address a 64bit MSSQL server instance through network.

But you face a problem with a 64 bit MSSQL instance - not having a 64bit ODBC or OleDB Provider to the DBFs to be used by the MSSQL Server instance. So the simplest scenario is to have all 32bit software and you can use an older SQL edition for that matter, as you make no major use of the MSSQL database and storage engines. You merely use it as a server side gateway to the DBFs.

Chriss

 

[farzad321]

Very nice my friend. this is very good description for me. So I need Further research on it and more work.

I am sorry for delay to answer.

Apparently i must do try and error to know about this connection between data and user.

I read your text several times and I try to work via your help

thank you for your time CHRIS

farzad

 

[Chris Miller]

If this all is in a LAN you still can try to get better performance from a local view by indexing your data for rushmore optimization.

Chriss