Working with OleDB

Overview

From Lianja 6.3 OLEDB providers can be used as an alternative means of transparent data access.

This provides the ability 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.

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.

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

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.

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 its cache. This is needed in shared data applications.

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

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.

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.

goto 90
display

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

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

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

ca = cursorAdaptor()
result = ca.eval("dbf(1)")

Executing program files in the VFP OLEDB provider

ca = cursorAdaptor()
ca.execScript("yourfilename.prg")

Searching for records in NoSQL

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 optimization is enabled against VFP indexes.

Filtering the records in NoSQL

set filter to customerid="RATTC"
list first 10

Searching and filtering records in SQL

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:

FAQ

• 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 do I 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 and OLEDB Prorviders

• Lianja 6.3 or above.

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

• MySQL and MariaDB OLEDB providers

• MSSQL OLEDB provider