HOWTO integrate Lianja and VFP with concurrent data access
by
, 2021-11-16 at 21:02 (47089 Views)
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.
2. Now let’s create a few Lianja virtual tables that access the VFP northwind database.Code:drop database oledbtest if exists create database oledbtest
We specify an OLEDB provider rather than an ODBC driver by prefixing the connection string with "oledb:".
3. Now startup VFP and open the northwind database then open a few tables.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
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.Code:open database c:\users\barrymavin\northwind.dbc shared use orders in 0 shared use customers in 0 shared select orders set lock on
4. Now in Lianja let’s open the same tables that VFP currently has open.
In Lianja we should also SET LOCK ON so that it will force read records that are being shared with VFP.Code:open database oledbtest use vt_orders set lock on list first 10 // verify the table opened ok
5. Let’s change a record in Lianja.
6. Now switch back to VFP and read that record. You will see the changes that were made in Lianja.Code:goto 90 replace shipregion with “USA” skip 0 // forces update of active record buffer
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.
8. Now add a blank record in Lianja then switch back to VFP and verify the the record count has changed.Code:goto 90 display
9. Now do the same thing in VFP then switch back to Lianja and verify the record count has changed in Lianja.Code:append blank
Executing VFP commands in the VFP OLEDB provider
Evaluating VFP expressions in the VFP OLEDB providerCode:ca = cursorAdaptor() ca.exec(“set exact off”) ca.exec(“set near on;set deleted on”) // separate multiple commands with semi colons
Executing program files in the VFP OLEDB providerCode:ca = cursorAdaptor() result = ca.eval(“dbf(1)”)
Searching for records in NoSQLCode:ca = cursorAdaptor() ca.execScript(“yourfilename.prg”)
Note that the following commands are passed through and executed by VFP.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
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
Searching and filtering records in SQLCode:set filter to customerid="RATTC" list first 10
Browsing VFP data in LianjaCode: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
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