Lianja SQL Server

Q:
is possible to connect to the lianja database that is used in development (not deployed)? I was hoping to generate a database programmatically, then write an app against that database and then deploy.
A:
Databases and tables opened in the App Builder are opened exclusively. Those in the deployed path are opened shared by default.

The
Data directory used by the Lianja Server is set in the Lianja Server Manager ‘Settings’ tab
You could set this to your developer data directory (drive:\lianja\data\) temporarily to run your utility, then set it back to the deployed directory once complete.
Remember to make sure that you do not have the database being altered already open in the Lianja App Builder.


Q:
When I try to launch the Lianja Server Manager from within the App Builder I get a message that I cannot launch the Lianja Server Manager because I don’t have Administrator Privileges.
I started the App Builder using Run As Administrator. I am Admin in the App Builder
I was launching using the Lianja Server Manager icon at the top of the App Builder screen
I’m on Win 8.1 pro, not on Active Directory although I get the same notification on a server where I am an admin.
No problem launching from the shortcut of course.
A:
It is probably better to launch it from the desktop shortcut or the control panel.
This functionality is being deprecated.
Please use the Lianja Server Manager desktop shortcut.


Q:
How can you get the Lianja Server to not use port 80? That screwed up my IIS installation because it could not start sites.
A:
Uncheck the port 80 checkbox in the server manager. It’s the top-right checkbox on the HTTP Settings page of the server manager.


Q:
I wanted to create a DSN to use with Crystal report XI prof for testing advanced reporting options.
I created teh DSN following the sample DSN provided for the Southwind DB unfortunately it is not connecting to my DB.
If I get you properly, I can’t create the DSN until the database is published?
A:
yes, you have to deploy the database. The Lianja ODBC driver connects to the Lianja SQL Server, which (unless you have changed the location of the databases in the Server Manager Settings page) looks for the database in the normal deployed data directory.
When you “deploy” you are pushing to a test environment. While the APaaS Cloud Server can’t be hit from outside the network you are on, other than that the environment is identical to production deployment. I wrote a script that allows me to deploy while still in the app, because as you note it’s possible that some things can only be tested in deployment.

I make a distinction between test (which I do as above), QA (separate server, wide variety of data to try to force edge conditions), staging (the customer’s data, final QA run-through) and production. The staging is sometimes important for verifying that a gnarly database structure update will run flawlessly.


Q:
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
A:
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.

A2:
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

Code:
%@ language=VFP %>

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

private aresult
set strcompare off

gnConnHandle = SQLSTRINGCONNECT("driver={SQL server};server=;uid=;pwd=")

strw1 = ""
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)
%>

A3:
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
.



 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s