MSSQL and MySQL

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.

Name:  2017-02-16 08_24_05-Google Traduttore.png Views: 76 Size:  47.8 KB

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

Name:  2017-02-16 08_30_15-Lianja App Builder v3.3.0 [martinelli_articoli] - UTF-8 - cogema - Licensed .jpg Views: 76 Size:  39.2 KB

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.

Name:  2017-02-16 08_28_02-Lianja App Builder v3.3.0 [martinelli_articoli] - UTF-8 - cogema - Licensed .jpg Views: 75 Size:  87.5 KB


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.

Name:  Screen Shot 2017-02-03 at 11.04.04 AM.jpg Views: 62 Size:  91.5 KB

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.

Name:  Screen Shot 2017-02-03 at 11.14.02 AM.jpg Views: 64 Size:  67.2 KB

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.

Name:  Screen Shot 2017-02-03 at 12.46.06 PM.jpg Views: 63 Size:  77.2 KB

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.


 

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s