Working with data in Visual FoxPro
- 1 Overview
- 2 Data access with Visual FoxPro
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.
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:
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.
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:
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.
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.
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"
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.