PDA

View Full Version : Recommended VFP ODBC Driver?



cuckoonuck
2016-09-16, 01:33
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
2016-09-16, 01:48
Hi Alan,

you can use ADS Drivers

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

Thanks,
Dilip

barrymavin
2016-09-16, 01:57
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
2016-09-17, 19:46
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
2016-09-17, 22:43
Thanks, Dilip. Have you used these drivers successfully in your applications?

Alan

barrymavin
2016-09-17, 23:36
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
2016-09-18, 14:31
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
2016-09-19, 11:49
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
2016-09-19, 13:09
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
2016-09-19, 16:35
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

HankFay
2016-09-19, 19:15
Hi Alan,

yes to all questions. I am assuming that you are on the same network.

If you are really running Lianja on a remote server, you have a couple of ways of doing this: using oData or by creating a web service using an .rsp page.

Either way you will need an http client on the VFP end. We use the Chilkat controls for this purpose. The error messaging with Chilkat is great and saves a lot of time when things don't work as you might expect. They also can handle SSL connections: if you are in the cloud, you will want to put the LCS behind IIS, in order to use https -- unless you block every address except those permitted to connect. You will also need a JSON library for VFP: the nfJSON project in VFPX works very nicely on the VFP side. You will want to base64 encode/decode the JSON messaging.

If the Lianja server is not in the cloud, but on the same network, it all gets easier. You could put the query instructions in a Lianja table, use the Lianja ODBC driver to pick up the query from VFP, and the same driver to insert the records.

hth,

Hank

barrymavin
2016-09-19, 20:03
As it turns out, the Lianja ODBC driver communicates with the server using TCP/IP so it can in fact be used across the Internet.

When the cloud server us running Lianja SQL is running too.

cuckoonuck
2016-09-19, 22:26
Thanks for your responses, Hank & Barry. Ideally, I would like the VFP9 app to be able to communicate with the Lianja database either remotely or on the same network, depending on the settings of the implementation. I have a few different clients that use my VFP6 app, and some may prefer to host the Lianja database on their own servers, while others may prefer to use a third party hosting service in the cloud. From Barry's response, I gather that as long as VFP9 accesses the Lianja SQL Server via the Lianja ODBC driver, either configuration would work. Would this work using an Amazon AWS-hosted instance of Lianja Cloud Server?

Alan

barrymavin
2016-09-19, 22:43
Hi Alan,

Yes. Lianja SQL Server is part of the Lianja Cloud Server install. When you create an AWS EC2 instance it has a FQDN which you can point to from DNS e.g. You purchase a meaningful domain name from network solutions (or other) and set it up to point at your AWS FQDN. So yes is the answer.

So you can have web apps sharing the data with Lianja SQL server and VFP accessing the data using an ODBC connection to Lianja SQL server.

It is quite a flexible solution.

hmischel@diligentsystems.com
2016-09-20, 09:19
Hi Alan,

Here is a skeleton of a generic rsp page that I use to read from a SQL Server.
Obviously, you would change the read to a write, but I wanted to illustrate the json object for others.

Lets say I call it SQLcode.rsp and save it to the cloud server as
c:\lianja\cloudserver\tenants\public\apps\myrspapp \SQLcode.rsp

(The preferred way would be to create an app called myrspapp, add an webview rsp page called sqlcode.rsp then deploy it to the cloud server)

I would then call it as"http://myserver/apps/myrspapp/sqlcode.rsp?param1=x&param2=y"
Or if you are using the Lianja default port, then you would call it as
"http://myserver:8001/apps/myrspapp/sqlcode.rsp?param1=x&param2=y"





<%@ language=VFP %>

<%
private m_param1 = getParameter("param1")
private m_param2 = getParameter("param2")

private aresult
set strcompare off

gnConnHandle = SQLSTRINGCONNECT("driver={SQL server};server=<servername>;uid=<userid>;pwd=<password>")

strw1 = "<sqlcommand .....>"
SQLPREPARE(gnConnHandle,strw1,'v_SQLresult')
= SQLEXEC(gnConnHandle)

select * from v_SQLresult into arrayofobjects aresult


response.addheader("Content-type", "application/json")

print_json(aresult)

=sqldisconnect(gnConnHandle)
%>



Herb

HankFay
2016-09-20, 11:02
Just a note for those wanting to connect remotely to any SQL Database, not just the LCS.

First, you will need to protect your open IP address. Not doing so leaves you open to brute-force password attacks. You can set up a VPN; or you can limit the addresses that are able to access the port.

Second, you will need to have protection from Denial of Service attacks.

Third, as Herb shows in his example, you should SqlPrepare your queries in order to thwart SQL Injection attacks.

Fourth, to prevent data sniffing if not using a VPN, I would put the LCS behind IIS in order to get an SSL connection. The LCS will get its own SSL ability in a future version, according to the roadmap.

All of these points apply to any "in the cloud" service. They simply apply "more" in this instance, where your data doesn't have the protective layer of a web service. Building dynamic AES-256 encrypted keys into a web service is simple (using Chilkat's encryption module). That extra level of security comforts me. Keeping unallowed IP addresses outside the firewall comforts me more -- put up an endpoint in the cloud and log all failed logins and you'll have a sleepless night. Especially when you see the IP address ranges coming from places where you have no users. They will start showing up in hours, literally.


Hank

pauln
2016-09-21, 09:36
Nice Hank! - Very Nice summary...
As the entire modern world moves and depends on the Internet - security will be a very important issue to manage.