View RSS Feed

LianjaDev

HOWTO integrate Lianja and VFP with concurrent data access

Rate this Entry
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

Click image for larger version. 

Name:	Screen Shot 2021-11-17 at 8.16.55 AM.jpg 
Views:	721 
Size:	126.7 KB 
ID:	2647

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.

Click image for larger version. 

Name:	Screen Shot 2021-11-17 at 8.18.49 AM.jpg 
Views:	704 
Size:	83.3 KB 
ID:	2648

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

Click image for larger version. 

Name:	Screen Shot 2021-11-17 at 8.28.20 AM.jpg 
Views:	691 
Size:	95.4 KB 
ID:	2649

Filter the data using the Query builder.

Click image for larger version. 

Name:	Screen Shot 2021-11-17 at 8.28.36 AM.jpg 
Views:	664 
Size:	71.4 KB 
ID:	2650

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

Click image for larger version. 

Name:	Screen Shot 2021-11-17 at 8.29.02 AM.jpg 
Views:	676 
Size:	89.6 KB 
ID:	2651

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.

Click image for larger version. 

Name:	Screen Shot 2021-11-17 at 8.46.39 AM.jpg 
Views:	682 
Size:	117.6 KB 
ID:	2652

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

Submit "HOWTO integrate Lianja and VFP with concurrent data access" to Google Submit "HOWTO integrate Lianja and VFP with concurrent data access" to Facebook Submit "HOWTO integrate Lianja and VFP with concurrent data access" to Twitter

Updated 2022-01-18 at 00:22 by barrymavin

Tags: None Add / Edit Tags
Categories
LianjaDev , NoCode , LowCode , ProCode

Comments

  1. josipradnik's Avatar
    I will add some more examples to view data:

    Free VFP dbf:

    Code:
    create virtualtable vt_cus connstr "oledb:Provider=VFPOLEDB;Mode=Share Deny None;Data Source=C:\myfreedbf\customers.dbf;Exclusive=No" as select * from customers
    use vt_cus
    browse
    MSSQL:

    Code:
    create virtualtable vt_por connstr "oledb:Provider=MSOLEDBSQL; Server=MYPC\SQLEXPRESS; Database=EVIO;Trusted_Connection=yes;" as select * from poruke
    use vt_por
    browse
  2. Schlingmeier's Avatar
    If that works, that would be a great thing. There are still good VFP programs that cannot easily be switched. This would make the transition much easier. Thank you! Is it rude to ask at this point whether there are already plans to improve the calendar function? That would be a great thing from my point of view. I'd rather have everything from one source than involve other third parties. From my point of view, the current calendar function in Lianja has come a long way, only a few small adjustments are missing.
  3. barrymavin's Avatar
    Quote Originally Posted by Schlingmeier
    If that works, that would be a great thing. There are still good VFP programs that cannot easily be switched. This would make the transition much easier. Thank you! Is it rude to ask at this point whether there are already plans to improve the calendar function? That would be a great thing from my point of view. I'd rather have everything from one source than involve other third parties. From my point of view, the current calendar function in Lianja has come a long way, only a few small adjustments are missing.
    Hi Olaf,

    If you submit an enhancement request ticket on Lianja.com I will look into it for you. It will not be in Lianja 7.0 as we expect to release that soon.
  4. Schlingmeier's Avatar
    Thanks Barry, but I added the improvement a few months ago. You also asked me for a sample link, which I submitted as well. I hope I posted it correctly.
  5. leon@doene.net's Avatar
    Is it possible to do this with foxpro2.6 free tables?

    I've tried but can't get the foxpro2.6 table to update with that changed in the virtual table.
    Updated 2022-06-30 at 08:59 by leon@doene.net
  6. barrymavin's Avatar
    Quote Originally Posted by leon@doene.net
    Is it possible to do this with foxpro2.6 free tables?

    I've tried but can't get the foxpro2.6 table to update with that changed in the virtual table.
    Hi Leon,

    What version of Lianja are you using?

    If you submit a ticket and attach the VFP 2.6 table I will test with the latest version.
Journey into the Cloud
Join us