LianjaDev

Many developers have requested the ability to be able to build modern Web Apps in Lianja while running legacy VFP applications side by side accessing the same data.

Although this is possible using Lianja virtual tables with the VFP ODBC driver, OLEDB provides better performance and flexibility.

In Lianja 6.3 we have provided the ability to use OLEDB providers as an alternative means of transparent data access.

Let’s look at how we can integrate Lianja and VFP concurrent data access in code in the console.

Getting started

1. Create an empty Lianja database container.

Code:

drop database oledbtest if exists
create database oledbtest

2. Now let’s create a few Lianja virtual tables that access the VFP northwind database.

We specify an OLEDB provider rather than an ODBC driver by prefixing the connection string with "oledb:".

Code:

open database oledbtest
// Notice that I have copied the northwind.dbc into c:\users\barrymavin\northwind.dbc
// you will need to edit this to point at your own VFP dbc database container
create virtualtable vt_customers connstr “oledb:Provider=VFPOLEDB;Mode=Share Deny None;Data Source=c:\users\barrymavin\northwind.dbc;Exclusive=No” as select * from customers
create virtualtable vt_orders connstr “oledb:Provider=VFPOLEDB;Mode=Share Deny None;Data Source=c:\users\barrymavin\northwind.dbc;Exclusive=No” as select * from orders

3. Now startup VFP and open the northwind database then open a few tables.

Code:

open database c:\users\barrymavin\northwind.dbc shared
use orders in 0 shared 
use customers in 0 shared
select orders
set lock on

It is important that in VFP you SET LOCK ON to cause VFP to force read records bypassing it’s cache. This is needed in shared data applications.

4. Now in Lianja let’s open the same tables that VFP currently has open.

Code:

open database oledbtest
use vt_orders
set lock on
list first 10 // verify the table opened ok

In Lianja we should also SET LOCK ON so that it will force read records that are being shared with VFP.

5. Let’s change a record in Lianja.

Code:

goto 90
replace shipregion with “USA”
skip 0 // forces update of active record buffer

6. Now switch back to VFP and read that record. You will see the changes that were made in Lianja.

7. Now change a record in VFP then switch back to Lianja and read that record. You will see the changes that were made in VFP.

Code:

goto 90
display

8. Now add a blank record in Lianja then switch back to VFP and verify the the record count has changed.

Code:

append blank

9. Now do the same thing in VFP then switch back to Lianja and verify the record count has changed in Lianja.

Executing VFP commands in the VFP OLEDB provider

Code:

ca = cursorAdaptor()
ca.exec(“set exact off”)
ca.exec(“set near on;set deleted on”) // separate multiple commands with semi colons

Evaluating VFP expressions in the VFP OLEDB provider

Code:

ca = cursorAdaptor()
result = ca.eval(“dbf(1)”)

Executing program files in the VFP OLEDB provider

Code:

ca = cursorAdaptor()
ca.execScript(“yourfilename.prg”)

Searching for records in NoSQL

Code:

use orders ;
    connstr "oledb:Provider=VFPOLEDB;Mode=Share Deny None;Data Source=c:\users\barrymavin\northwind.dbc;Exclusive=No"
set order to customerid
seek "RATTC"
display

Note that the following commands are passed through and executed by VFP.

set order to
seek
find
set filter to

This is done so that VFP rushmore is enabled against VFP indexes.

Filtering the records in NoSQL

Code:

set filter to customerid="RATTC"
list first 10

Searching and filtering records in SQL

Code:

open database oledbtest
use vt_customers
list first 10
ca = cursorAdaptor()
ca.requery("customerid='RATTC'")
ca.requery("customerid='RATTC' order by customerid”) // order by can also be specified
list first 10
ca.requery("")
list first 10

Browsing VFP data in Lianja

Let’s look at how we can transparently integrate Lianja and VFP concurrent data access in the Lianja UI

Firstly let’s browse a VFP table in the data workspace



After opening the table in the data workspace switch to the console workspace and LIST STATUS. You can see that there is now a direct connection with the VFP database.



You can also BROWSE the VFP data and query it just as if it were a native Lianja table



Filter the data using the Query builder.



Use "Search" to filter the data based on words or phrases contained in the records.



Now let’s create a simple app with one Form section and navigate between records and make an update then switch to VFP to verify that VFP can see the change that has been made.

Navigate between the customer records and watch the orders being related and displayed.



Can I use OLEDB in Web and Mobile Apps?

Yes, the Lianja Cloud Server on Windows operates transparently on Virtual Tables.

Can I use OLEDB with MSSQL?

Yes, you can use any OLEDB driver just specify its connection string in the virtual table definition.

Why use OLEDB rather than ODBC?

Using OLEDB connections with Virtual Tables is faster as Lianja only reads records as they are needed rather than filling the cursor when the virtual table is opened which is what happens with ODBC. Additionally records are force read dynamically.

When to use ODBC rather than OLEDB

If you want your apps to run on non windows platforms i.e Linux and MacOS then you should not use OLEDB as it is a windows specific technology.

Lianja uses UTF-8 and VFP uses 8-bit characters how is that handled?

Data read from VFP is converted to UTF-8 and reconverted to 8-bit characters from UTF-8 when writing. This enables the VFP data to be used in Web Apps in a standard way.

How is record locking handled?

Lianja uses optimistic locking using before and after buffering.

Prerequisites

Lianja 6.3

VFP OLEDB provider (included in the lianja\lib directory)
File c:\lianja\lib\VFPOLEDBSetup.msi

MySQL and MariaDB OLEDB providers are available from
https://cherrycitysoftware.com/

MSSQL OLEDB provider is available from
https://docs.microsoft.com/en-us/sql...l-server-ver15