[Answers] MSSQL and MySQL

**josipradnik** · 2015-05-27, 07:24

Virtual Tables are mainly used for accessing third party SQL databases such as MSSQL and MySQL.
However, If you want to run the Lianja Cloud Server on one machine and the Lianja SQL Server on another (think load balanced cloud connections) then VTs are one of the solutions.
You can also configure the Cloud Server to access shared data on another machine.

So there are a variety of solutions for scaling out Apps which have a lot of concurrent users.
If you are building Web Apps that use the native Lianja database, this is embedded in the Cloud Server. This makes it very fast as there is no communication to the external SQL server. The functionality is better and the performance is very good.
With the ability to also use VTs in .rsp dynamic pages in v1.3 the sky is the limit for integrating external data sources.

We now have OData working well with MSSQL and MySQL.
In v1.3 ODBC connections are kept alive across queries which is showing very good performance. This screenshot below will give you an idea.
These OData functions are used internally with Web Apps so in a short while Virtual Tables will be fully functional in the Web Client.

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

**josipradnik** · 2016-12-20, 04:05

Q:
Is there an article walking through use of MS Sql Server?
Not to Import data but to read and write an MS Sql Server database directly?
A:
Lianja does this via Virtual Tables.

Lianja Cloud Server 1.3 Release for Windows will include data source independent data access to Lianja SQL Server, MSSQL, PostgreSQL, Oracle and MySQL using Lianja Virtual Tables.
The Lianja APaaS Developer and App Builder distributions will include the sample App 'Lianja Web Virtual Tables Demo' (example_webvirtualtables) using virtual tables in the included cloudlibtest database to connect to MySQL and MS SQL Server.
To run the App, you will need to have ODBC access set up to a MySQL Server with the sakila sample database and to the AdventureWorks sample database on MS SQL Server.
link: http://www.lianja.com/doc/index.php/...a_Cloud_Server

Q:
I can create a system DSN to the SQL server using the SQL Server Native Client 11, the SQL server or ODBC driver 11 for SQL Server. However if I use Lianja ODBC driver, it can't connect (no response is the answer) In the app Builder, it can't use any of the connection I create with the other drivers. (Failed to connect to DB source.)
Windows 7, tried 32 and 64 bit options. Tried using the ODBC manager.
How are connections to MSSQL v 11 (2014) handled?
A:
Lianja currently only supports 32 bit ODBC drivers so you will want to verify that you are using the 32 bit version of the SQL Server ODBC driver as well as the 32 bit ODBC Administrator (usually found here: C:\Windows\SysWOW64\odbcad32.exe).
You can test the connection in the ODBC tab of the Console WorkSpace by entering your DSN name as the Connstring and supplying any necessary user credentials.
The Lianja ODBC driver is used to connect to the Lianja SQL Server.

Here what I was able to connect.
Driver={SQL Server Native Client 11.0};Server=myServerAddress;
Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Instead of a local or system DSN, it is the actual string that is saved by the control panel that worked for me.

You can always specify the full connection string if you do not want to setup an odbc dsn on each machine.
As dave has pointed out you need to create a 32 bit odbc dsn and also have the 32 bit odbc driver installed.
Many people are using virtual tables with odbc.
Later as detailed in the roadmap we will be providing a 64-bit version on windows which will use the normal 64 bit odbc drivers but in the meanwhile you need to use the 32 bit ones.

(?A):
One tip on installing the 32-bit drivers on 64-bit machines: regardless of your SQL Server version (back to 2005), get the 64-bit "ODBC Driver 11 For SQL Server" 64-bit install https://www.microsoft.com/en-us/down....aspx?id=36434 and it will install both the 64-bit driver and the 32-bit driver. If you try to install the 32-bit install on an x64 machine, it will fail.

When building an installer, run both installs for silent install, using the directions here: https://msdn.microsoft.com/en-us/lib...=sql.110).aspx (This does not appear to be searchable by Google, so store it away somewhere for when you need it). The installation command will look like this, but you will want to rename the installers to identify them. This command line is different than all prior versions in a couple of ways:

msiexec /quiet /passive /qn /i msodbcsql.msi IACCEPTMSODBCSQLLICENSETERMS=YES

The ODBC Driver 11 will work against all SQL Servers back to 2005, so this simplifies your installer. Further simplifying matters, as mentioned above, you can have the installer run both the 64-bit (which includes 32-bit) and the 32-bit installs. If on x64, the 33-bit install will fail to run (but do so silently and quickly), but the 64-bit install will install the 32-bit driver. The reverse is true if you are installing on a 32-bit server.

1. SET DEBUG ON in the console.
2. Select the ODBC tab
3. Open a MySQL ODBC DSN
4. Create a table called ftest with a float(10,2) column then insert one row into it
5. select * from test
6. quit Lianja
Now look in c:\lianja\debug\debug_client.txt

Lianja Cloud Server 1.3 Release for Windows will include data source independent data access to Lianja SQL Server, MSSQL, PostgreSQL, Oracle and MySQL using Lianja Virtual Tables.
The Lianja APaaS Developer and App Builder distributions will include the sample App 'Lianja Web Virtual Tables Demo' (example_webvirtualtables) using virtual tables in the included cloudlibtest database to connect to MySQL and MS SQL Server.
To run the App, you will need to have ODBC access set up to a MySQL Server with the sakila sample database and to the AdventureWorks sample database on MS SQL Server.
For more information on this, please see Virtual Tables - Lianja Cloud Server.
We have just added a 'Troubleshooter' wiki page for this for anyone who is having difficulty accessing the AdventureWorks sample database on MS SQL Server. Follow the link on the original Virtual Tables - Lianja Cloud Server page, or go directly to the Troubleshooter here.

ODBC Steps:

(1) Sample SQL Server database
I needed a sample MS SQL Server database mentioned in text above. So I downloaded AdventureWorks from http://msftdbprodsamples.codeplex.co...ses/view/93587
2008 version: http://msftdbprodsamples.codeplex.co...ads/get/478218
I unzipped it into folder with my other databases.
In MS SQL Server Management Studio I attached this database

(2) ODBC
Now setting ODBC according https://www.youtube.com/watch?v=tUiaK5fRH7k

There are several better ways, but I'd suggest the following 2 which allow you to connect to SQLServer without setting up an ODBCconnection .

1. Virtual Tables

Here is an example for connecting to SQLServer using a trusted connection.

create virtualtable <tablename> connstr 'driver={SQL server};server=<servername>;Trusted_Connection=Yes ;database=<dbName>' as select * from <tablename> where 1 =2

You should look up cursoradaptors to understand how to handle large data sets.

2. The other suggestion is SQLStringConnect.

It is a DSNless connection, so you done need to use a named connection . There are examples on the forum.
http://www.lianja.com/community/show...lstringconnect

- The windows ODBC utility will be located in different locations depending if you are using a 32 bit or 64 bit system.
32 bit: C:\Windows\System32\odbcad32.exe
64 bit: C:\Windows\SysWOW64\odbcad32.exe

Note: 64 bit systems will have the utility in the same path as the 32 bit system although it is only configured for 64 bit programs.
To ensure compatibility with other systems and programs, it is recommended to use the 64 bit utility from the path mentioned above.

- SQLServer is typically used for older connections (older programs). New connection might be able to use any of the various versions of Native Client.

- When selecting the server from the drop down box, if the server is not listed, you can manually enter the IP address for the server name.

- SQLServer authentication is used more if the SQLServer is not located on the main machine.

If a specific user is not setup in the SQLServer , many people will use the system administration account (which should have been configured when SQLServer was installed).
In this case the login ID (user name) would be 'sa'.

- In Lianja, you can test the connection by selecting the ODBC tab.

Q:
differencies and/or benefits of Lianja Server in comparison to PostGres.
A:
PostgreSQL is a good database and can be used with Lianja.
Lianja SQL offers better JSON support and stored procedures can be written in Lianja/VFP.

Q:
ODBC test from console fails while "Datasource" test from "Administrative Tools" panel succeeds. This does'nt happen on Vista 32bits.
Driver is "MySQL ODBC 5.2 ANSI DRIVER" for Windows 64bits and Windows Lianja RC7.
A:
Lianja on Windows is a 32 bit application, so you will need the 32 bit MySQL ODBC Driver and the Datasource set up in the 32 bit ODBC Administrator (%windir%\SysWOW64\odbcad32.exe).
Did you use the correct version of odbcad32.exe, referenced in Yvonne's post, above?

Using the ODBC manager from the control panel will bring up the 64-bit ODBC manager, which is the wrong one. You can create a shortcut to the correct one to make access to it easier.

Q:
are you suggesting using MSSQL views rather than creating a Virtual Table?

If so, this will limit your ability to move between backend databases. It will also make source control more difficult: with a VT created through a prg (or deconstructed into a prg, which is easily doable in Lianja). Done in Lianja, versioning, merging, etc. all remain within your control. In most cases, creating a VT using a PRG is the quickest way to create, and debug/fine-tune, a Virtual Table. The exception would be the simplest VT's.
A:
There is no performance advantage (beyond the first run) between using an MSSQL view and a Virtual table. The MSSQL Plan Cache will be the same for each after the first run of the VT.

Q:
I am replacing a system that I built prior to Lianja that has hundreds of stored procedures already in MS SQL.
A:
There's no reason to rewrite that code.

Q2:
Additionaly, I am building systems that interact with other vendor products that are already on our SQL Server.
A2:
Vt allow you to really take advantage of all the built in processes like relating sections, auto insert /update/ delete etc.

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

**josipradnik** · 2017-12-18, 00:38

Q:
... connect to MYSQL.
I would like to try that with Lianja. I use Navicat to see that the DB is working. Lianja says it
will connect thru OBCD. Could you point me in direction to get that happening.

A:
if you use MySql, you need to install the ODBC driver for MySql, then create the ODBC connection.

Then, from lianja, go to the "Data" tab, crete a DB, create a Virtual Table

Now you can use your virtual table as a Lianja table.
For my client, I've a database "mixed" with some Virtual Table linked to MySql and real Lianja table.

Q:
the problem is with migration.
The applications which are using more than 256 columns, those will have problem.
Not only with VFP migration, But also with .NET apps who what to migrate to Lianja after including C# and VB.net scripting languages in lianja 5.0
extending more than 256 columns is required. Ms SqlServer is supporting 1024 columns per table. Oracle table is supporting 1000 columns

A:
My new apps are running in parallel with my older apps. The data is mostly on SQL Server as I have many hundreds of stored procedures.
I still use those stored procedures on SQL Server and return the data to Lianja. The same way I would in C# or C++.
I call the stored procedure using SQLConnect and return the data into a temporary table.
If you have an app that that needs a 500 columns, just keep the data in SQL Server. I cant imagine that you need to view that many columns at once.
Just grab what you need.
Or if you really want the data in Lianja, logically partition multiple tables. It's not a difficult work around.

SQL Server and Oracle are Relational Database Management systems.
Lianja is an entire framework. From local or remote databases and tables, to JavaScript web pages, to mobile app development to robust desktop apps that easily combine VFP, Python, PHP, Javascript, TypeScript, C and C++.
How many applications need 1000 columns in a single table? I can see some data analytics possibly designed that way.

Q:
I want to insert a new record. I have a virtual table from MSSQL and my original table has a Primary Key with autoincrement (Identity).
When I want to insert a new record I have the next error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'Catalogo_Correos' when IDENTITY_INSERT is set to OFF.

A:
You need to exclude autoinc columns from the update.
https://www.lianja.com/doc/index.php...ble_Properties
Specify this in the VT properties:
updatefldlst=*,-yourautoincfield
Notice the use of - to prefix a column you want to exclude.

Q:
I am testing with virtual tables
"[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'Cuerpo', table 'APIntranet.dbo.Catalogo_Correos'; column does not allow nulls. INSERT fails. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. "

A:
From the console SET DEBUG ON then perform your update.
Exit Lianja App Builder then look in \lianja\debug folder for a debug_xxx.txt file. Read it and you will see the SQL that was generated.
If younare adding a new record then setup a default value or validation on the columns that should not be null.
Just as in MSSQL, if a field is NOT NULL (the default in MSSQL), you have to supply a value for that field when adding a record. Or you have to set a default for the field in MSSQL. When I generate an MSSQL database (we work almost exclusively form metadata) I supply empty defaults for every field, as the validation is going to occur on the VFP (and now LIanja) side.

It is simple to work with third party databases in Lianja.
Apart from Virtual Tables you can perform queries very quickly with minimum coding.
Take a look at this screenshot which demonstrates how to perform SQL queries quickly against MSSQL.

Notice how you can specify the CONNSTR (connection string) which will connect to MSSQL and perform a SELECT * FROM the specified table name selecting the rows that match the WHERE condition.
We can also specify the SQL SELECT statement we want to execute. See below.

Now that we have the MSSQL data in a local cursor we can slice it up and output JSON to send back to a web/mobile client.

Clearly we can query MySQL data and PostgreSQL data also and fetch the data into a local cursor.
At this point we can then perform a join of MSSQL and MySQL and PostgreSQL if that is required.

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.com/category/mssql-and-mysql/