Working with data in JavaScript

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.

Python, PHP and JavaScript are seamlessly integrated on top of this 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.

Data access with JavaScript

Accessing data in Lianja databases from JavaScript is simple, but nevertheless powerful, exposing much of the power of the Lianja database to JavaScript developers.

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 JavaScript code to accomplish this.

Working with local data cursors in the Lianja Web/Mobile Client

The Lianja HTML5 Client maintains local data cursors for tables that are actively being used in sections within pages. Each local data cursor has the concept of an "Active Record". As you navigate data and the sections are refreshed the "Active Record" for these local data cursors is kept in sync with what is displayed in the UI. In desktop apps, the Lianja/VFP database engine provides direct access to these using the familiar alias.fieldname notation.

In your Web and Mobile database Apps you can also get access to this data using Lianja.getCursor("tablename") and then use the getData() and setData() methods on the cursor object that is returned from Lianja.getCursor().

Additionally, seeing as the cursor state is known at any time you can use {alias.fieldname} macros in your validation expressions and any URLs that you have associated with WebView sections. Note that if you create a dynamic local cursor using Lianja.createCursor() then macros will not be evaluated against this cursor, you need to do that manually in your custom JavaScript code.

Local cursors provide the ability to dynamically query and refresh section contents based on the values of fields in other sections.

Referencing an existing cursor

You can get a reference to an existing cursor in desktop, web or mobile apps. This is typically how you would inspect the Active record and its fields.

var orders = Lianja.getCursor("orders");
var name = orders.getData("name");
var unitcost = orders.getData("unitcost");
var quantity = orders.getData("quantity");
orders.setData("total", unitcode * quantity);
orders.update( 
    function() {    
        // onsuccess
        Lianja.showSuccessMessage("Record was updated");
        Lianja.showDocument("page:orders?action=refresh");
    },
    function() {
       // onerror
       Lianja.showErrorMessage("Record could not be updated", "Update failed");
    }
);

See the Cursor documentation for details of the properties and methods available.

Opening a database

To open a Lianja database from JavaScript you use the global Lianja function openDatabase().

db = Lianja.openDatabase("southwind");

Create a Recordset

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:

rs = db.openRecordSet("select * from customers");

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

rs = db.openRecordSet("customers");

Recordset data navigation

After we have opened a recordset we can navigate through the data using any of the recordset data navigation methods of the Recordset class.

• moveFirst()
• moveNext()
• movePrevious()
• moveLast()

For example to position on the first record in a recordset use the moveFirst() method.

rs.moveFirst();

Extract data from the Recordset

When you are positioned on a particular record in a recordset you can extract data using the fields() method.

for (i=0; i‹rs.fields.count; ++i)
{
    name = rs.fields(i).name;
    value = rs.fields(i).value;
}

The fields() method can also take the field name as an argument as well as the column ordinal position.

value = rs.fields("amount").value;

Filtering selected data

When you open a recordset 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.

rs = db.openRecordSet("customers");

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

• findFirst(condition)
• findNext(condition)
• findPrevious(condition)
• findLast(condition)

NoSQL keyed data access

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

rs = db.openRecordSet("customers"); 
rs.index = "id"; 
rs.seek("12345"); 
if (!rs.found()) 
{  
    // key was not found. 
}

Adding new records

You can add new blank records to a recordset using the addNew() method.

rs.addNew();

Note that after executing addNew() the record is not written until the update() method is called. 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 recordset using the update() method.

rs = db.openRecordSet("customers"); 
rs.index = "id"; 
rs.seek("12345"); 
if (rs.found()) 
{  
    rs.fields("amount").value = rs.fields("amount").value + 1000;  
    rs.update(); 
}

Deleting records

You can delete records in a recordset using the delete() method.

rs = db.openRecordSet("customers"); 
rs.index = "id"; 
rs.seek("12345"); 
if (! rs.noMatch()) 
{
    rs.delete(); 
}

Closing a Recordset

You close a recordset using the close() method.

rs.close();