Data validation and integrity [examples]

Column constraint to specify minimum and maximum values for a date or numerical column

CREATE TABLE orderhead;
  (ord_week INT RANGE 1,52,;
  ord_date DATE RANGE date(), gomonth(date(),12))

https://www.lianja.com/doc/index.php/RANGE_Column_Constraint


Column constraint to set a default value for the specified column

ALTER TABLE customer;
  ADD COLUMN dateref DATE DEFAULT date()

https://www.lianja.com/doc/index.php/DEFAULT_Column_Constraint


Column constraint to validate a change to the value of a column

ALTER TABLE customer;
  ADD COLUMN timeref CHAR(8);
  CHECK validtime(timeref);
  ERROR "Not a valid time string"

https://www.lianja.com/doc/index.php/CHECK_Column_Constraint


Table constraint activated when an operation to insert, update or delete records in the table is called

CREATE TABLE purchase_order;
  (POid i PRIMARY KEY, SuppId i, POtotal n(10,2),;
  CHECK callauth(); 
  ERROR "Not authorized")

https://www.lianja.com/doc/index.php/CHECK_Table_Constraint


Column constraint to validate a change to the value of a column

ALTER TABLE customer;
  ALTER COLUMN timeref;
  SET CHECK validtime(timeref)

https://www.lianja.com/doc/index.php/SET_CHECK_Column_Constraint


Column constraint to specify input mask

ALTER TABLE "customers" MODIFY CONSTRAINT CUSTOMERID SET PICTURE "@^"

https://www.lianja.com/doc/index.php/PICTURE_Column_Constraint


Column constraint to specify a choice list for a character column

open database southwind
// Static choices
ALTER TABLE "example" MODIFY CONSTRAINT TITLE SET CHOICES "Mr,Mrs,Ms"
// Dynamic choices @tablename,expression
ALTER TABLE "orders" MODIFY CONSTRAINT CUSTOMERID SET CHOICES "@customers,customerid"
// Dynamic choices SQL Select
ALTER TABLE "orders" MODIFY CONSTRAINT CUSTOMERID SET CHOICES "select customerid from customers"

https://www.lianja.com/doc/index.php/CHOICES_Column_Constraint


Column constraint to allow NULL values

ALTER TABLE customer;
  ADD COLUMN custref CHAR(5) NULL
ALTER TABLE customer;
  ALTER COLUMN custref NOT NULL

https://www.lianja.com/doc/index.php/NULL_Column_Constraint


Column constraint to disallow NULL values

ALTER TABLE customer;
  ADD COLUMN custref CHAR(5) NULL
ALTER TABLE customer;
  ALTER COLUMN custref NOT NULL

https://www.lianja.com/doc/index.php/NOT_NULL_Column_Constraint


Cross Table Lookups

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

Use the sqlLookup() or keyLookup() function in your validation attribute.

sqllookup("southwind!employees","lastname","'{}'","employeeid","not found")!="not found"
keylookup("southwind!employees","employeeid","{}", lastname, "not found")!="not found"

You specify the Validation attribute declaratively in the App Inspector or programatically by assigning a value to the appropriate object property.

Range Checks

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

Specify these in your validation attribute.

between({}, 0, 9999)

You specify the Validation attribute declaratively in the App Inspector or programatically by assigning a value to the appropriate object property.

Contained within a static List

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

Specify these in your validation attribute.

inlist("{}", "apples", "pears", "oranges")

 

Referencing data in 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");
    }
);

 

Providing visual feedback to the user

In Desktop Apps it is typical for dialogs to be popped up to inform the user when invalid data has been entered or when validation fails generally.

In Lianja Apps It is better to use the built-in notification methods on the Lianja system object as these are much more modern and they animate in and out on the top right of the UI.

Lianja.showWarningMessage("You are about to be logged out");
Lianja.showSuccessMessage("Record was updated");
Lianja.showErrorMessage("Record could not be updated");

Server-side Validation in Web/Mobile Apps

The following methods can be used to call server side procedures (JavaScript or Lianja/VFP).

Lianja.evaluate()

The Lianja.evaluate() method can be used to call a Lianja/VFP function/procedure.

var result = Lianja.evaluate("myproc()");

To call a procedure in a library, prefix the procedure name with the library name and ‘::’.

var result = Lianja.evaluate("mylib::mylibproc()");

Lianja.evaluateJavascript()

The Lianja.evaluateJavascript() method can be used to call a JavaScript function.

var result = Lianja.evaluateJavascript("myfunc()");

The file ‘myfunc.js’ should be in the app or library directory and contain a function with the same name as the file. If the ‘myfunc.js’ file does not exist in the app or library directory and the file ‘server_functions.js’ exists then the function ‘myfunc’ is assumed to be defined in the ‘server_functions.js’ file.

To call a function in a library, prefix the function name with the library name and ‘::’.

var result = Lianja.evaluateJavascript("myjslib::mylibfunc()");

An alternative syntax using the Lianja.evaluate() method is also available:

var result = Lianja.evaluate("javascript:myfunc()");
var result = Lianja.evaluate("javascript:myjslib::mylibfunc()");

exports.conf

A function can be called directly if it is declared in exports.conf.

var result = myfunc();

The exports.conf file enables a simplified approach for calling server-side business procedures directly from the JavaScript client.

This is accomplished by defining the libraries, functions and scripting language for the functions in the exports.conf file which can reside in the app and/or the library directory.

Example exports.conf file:

# This is an example exports.conf file
#
# Each line describes a function that will be available directly from 
# JavaScript code in the client
#
# library, function, type
# (source library, name of the function, type: vfp or javascript)
# (php and python will be added later)
#
# or
#
# library, function 
# (implied type of vfp)
#
# or
#
# function 
# (file expected to exist with a .dbo extension)
#
mylib,myfunc1,vfp
myjslib,myfunc2,javascript
myfunc3

In the Lianja JavaScript Web/Mobile App the server-side functions can be called directly:

// Note that arguments are automatically converted into the correct format for the 
// remote function call and the function call is proxied
// using a synchronous  Lianja.evaluate() call
var result = myfunc1("hello world", 2015, true);
var result2 = myfunc2();
 
// When calling remote functions with objects as parameters the object is automatically 
// converted to JSON and sent base64 encoded
// This allows you to send and receive complete JSON encoded objects between the 
// client and the server
var result3 = myfunc3( {"name":"barry", "company":"Lianja" });

https://www.lianja.com/doc/index.php/Data_Validation


 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.