Recommended VFP ODBC Driver?

[HankFay]

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]

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]

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]

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]

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=<pass word>")

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]

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]

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.