[Answers] Lianja SQL Server

Q:
Differences between the embedded database and Lianja SQL Server?
A:
There is no difference, the Lianja SQL Server is a means to allow you access to Lianja data from 3rd party programs or databases, primarily via ODBC.

---

Q:
partial uninstall
A:
work around is to double click the uninstall.exe in the C:\Lianja\sqlserver\ directory directly.

---

Q:
I am using Lianja SQL server from the same LAN. How can I tell my network admin to setup IP/hostnames to reduce delay.
A:
Try putting the IP/hostname mapping in the C:\Windows\System32\drivers\etc\hosts file on the client.

---

Background Scripts you can create and run via the Lianja Server on Windows, so for anyone who is interested in using these, I hope this will guide you in their use and design.
Background scripts can be used to perform data transformation, data synchronization or other custom operation on your data.
Background scripts run with no UI.
Each runs as a separate LianjaRunScript process.

They are primarily used to perform data collection tasks or batch job tasks such as checking for new or updated data and emailing out notifications to users.

Typically a background script will sleep for a period of time and then repeat its work. Background scripts have the standard program extensions of prg (Lianja/VFP script source) / dbo (compiled Lianja/VFP script) and reside in the Services folder (Lianja Server Manager Settings tab, by default 'C:\Lianja\server\services\').
The Background Scripts tab in the Lianja Server Manager allows for the creation, modification and configuration of background scripts.
http://www.lianja.com/community/show...=8626#post8626

---

The Lianja SQL Server uses the operating system user authentication/permissions. Specify a valid username and password for the server's host OS. You can also deny/allow connections based on ranges of or individual IP addresses in the Security settings of the Lianja Server Manager.

---

The lianja database engine is full 64 bit and has no practical file size limits as is detailed on the product pages.
It has self healing indexes and is widely deployed around the world in defense, banking and finance systems.
It is embedded in lianja app builder, lianja sql server and lianja cloud server.
It is fast and lightweight.
It is easy to install.
It comes with both an odbc driver and a jdbc driver.
It handles transparent connectivity to third party databases using virtual tables.
You can write stored procedures and triggers in VFP.

---

Lianja SQL server is a SQL server as stated in the name. Saying that however, it does allow you to write stored procedures in VFP using the NoSQL commands. Lianja SQL Server as with native Lianja uses indexes automatically when performing queries.
Lianja does not support table buffering.

---

Yes any type of binary file of any size can be stored as the database engine uses 64 bit file access. Space is automatically reclaimed on updates.
yes, you are correct, with this functionality Lianja can easily be used as a NoSQL key/value database just create a table with two fields, key and value.

Code:

---

create table NoSQL (key char(20) foreign key, value varchar)

---

you can then perform very fast key/value lookups of JSON or XML encoded data stored in the value column.
And of course the inherent problem of distributed updates in NoSQL databases does not exist in Lianja. Everyone sees the updates immediately.

---

Q:
I've used the Lianja ODBC Manager to setup a connection to my VFP data and it tests out fine. I've setup a new database in the App Builder and used the option Create Virtual Tables from ODBC. It takes a a few minutes to complete and then I see all of my tables prefaced with "vt_". The problem is though that many of the tables, when clicked on, display a message say "File [vt_tablename] does not exist". A few tables are accessible - seems to be those with a very small number of fields. Is there something I missed?
A:
The Lianja ODBC Manager is for connecting to Lianja SQL Server data.
For other data sources you need to use the ODBC driver specific to it. in your case you need to use the VFP ODBC driver.

---

The Lianja SQL Server is a complete SQL database server used for building client / server Apps. It is not just for providing third party access to Lianja data.
You can install and run it on a separate server machine (Windows or Linux).
It is the equivalent of MySQL or MSSQL.
One big advantage is that you can write stored procedures in Lianja/VFP scripting.

---

---

All topics in [Answers] alphabetically: http://www.lianja.com/community/show...ll=1#post12352

Q:

Code:

---

lcDSNLess="DRIVER=Lianja ODBC Driver;SERVERNAME=?;USERNAME=?;PASSWORD=?;DATABASE =C:\LIANJA\DATA\BDFICC"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)
if lnConnHandle > 0
sqlexec(lnConnHandle,"select * from historia")
list
sqldisconnect(lnConnHandle)
else
messagebox("Sin Conexion","Error")
endif

---

I cant connect.

A:
https://lh5.googleusercontent.com/ad...Da5rQNIC1030EU

Development databases used in the app builder are opened non shared.
When connecting using ODBC from VFP the ODBC driver expects the database to be deployed. It does not access the development database.
Databases that have been deployed (runtime databases) are opened shared.
Which only works if the southwind database is not open by the app builder.

---

Q:
The embedded high-performance cross-platform 64-bit database is a kind of SQL or a file database, like the old one from VFP?
A:
Lianja database tables support SQL and NoSQL access. Tables, multiple indexes, table dictionaries etc. are stored as individual files.

---

---

All topics in [Answers] alphabetically: http://www.lianja.com/community/show...ll=1#post12352

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

---

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.

---

---

​All topics in [Answers] alphabetically: http://www.lianja.com/community/show...p?2717-Answers

Lianja SQL now supports aggregate functions in GROUP BY ... HAVING and also ORDER BY.

---

---

All topics in [Answers] alphabetically: http://www.lianja.com/community/show...ll=1#post12352

These answers are also systematized on the site "Lianja developer": https://lianjadeveloper.wordpress.co...ja-sql-server/