Working with data in Visual FoxPro

Overview

As the Lianja App Builder has an embedded database built into it, you do not need to install any other database software to be able to build multi-user high performance database apps for the desktop, web and mobile devices.

The Lianja embedded database engine is highly compatible with Visual FoxPro 9.0. It includes a feature-complete cross platform implementation of the Visual FoxPro scripting language and database engine.

If you know Visual FoxPro you can develop in Lianja leveraging all of your existing knowledge.

Additionally, Python, PHP and JavaScript are seamlessly integrated on top of the Lianja database engine, allowing you to build custom UI sections and custom UI gadgets in Visual FoxPro, JavaScript, PHP or Python and make full use of the power of this high performance database engine with local cursors and complete SQL and noSQL database support.

To facilitate the development of custom UI sections and gadgets, Lianja comes with a cross-platform, UI independent application framework called the Lianja UI Framework.

Note that the Lianja database has a high degree of Visual FoxPro compatibility but has many extensions above and beyond Visual FoxPro to facilitate the deployment of high-availability systems with a high degree of fault tolerance.

If you are building a custom section in Visual FoxPro, whenever any of the methods of your section class are executed from your Lianja App, then the cursor for the table that is bound to the section will be active.

Data access with Visual FoxPro

Accessing data in Lianja databases from Visual FoxPro is simple as this data-centric scripting language sits right on top of the Lianja database engine and everything works in a way that Visual FoxPro developers are already familiar with.

Note that if you are building a UI with the Lianja UI Framework, you can bind your UI controls to data sources in the Lianja database just by setting the controlsource property of the UI control to tablename.columnname, you do not need to write any special Visual FoxPro code to accomplish this.

Opening a database

To open a Lianja database from Visual FoxPro you use the OPEN DATABASE command.

open database southwind

Create a Cursor

You can then access a table in the database using the openRecordSet() method of the Database class using SQL or noSQL. For example we can access the customers table using SQL like this:

select * from customers into cursor cust

Or alternatively just open the customers table with noSQL like this:

use customers

Cursor data navigation

After we have opened a cursor we can navigate through the data using any of the cursor data navigation commands.

For example: to position on the first record in a cursor use the goto top command.

goto top

Extract data from the Cursor

When you are positioned on a particular record in a cursor you can extract data just by referencing the field name like this:

? customers.amount

Filtering selected data

When you open a cursor with an SQL select statement, the data selected is only that which matches the where condition of the select statement. If you open a table with noSQL e.g.

use customers

You can filter the records that are returned using the data restriction commands.

For example to lookup a customer by their id and scan through the data selecting only those records that satisfy a certain condition you could write:

use customers order tag id
seek "12345"
scan rest while amount > 0
   ? "Amount is ", customers.amount
endscan

noSQL keyed data access

When opening a table in noSQL mode, you can lookup records by keys.

use customers order tag id
seek "12345"
if not found()
   // key was not found.
endif

Adding new records

You can add new blank records to a recordset using the APPEND BLANK command.

use customers
append blank
replace id with "34567", amount with 0

Or alternatively the SQL INSERT command.

insert into customers (id, amount) values("34567", 0)

Note that after executing append blank the record is not written until the you move the record pointer as Lianja supports record buffering by default. This allows you to update the fields of the blank record prior to it being committed into the table.

Updating records

You can update records in a cursor using the REPLACE command.

use customers
set order to tag id
seek "12345"
if found() 
   replace amount with amount+1000
endif

Or alternatively using the SQL UPDATE command.

update customers set amount = amount+1000  where id = "12345"

Deleting records

You can delete records in a cursor using the DELETE command.

use customers
set order to tag id
seek "12345"
if found() 
   delete
endif

Or alternatively using the SQL DELETE command.

delete from customers  where id = "12345"

Closing a Cursor

You close a cursor using the USE command.

select customers
use

Closing a Database

You close a database using the CLOSE DATABASE command.

close database