NOTE: this custom type has been superceded by fwp_recordData for all SQL databases. It is included in v5 of the framework to provide FileMaker support until an updated interface for FileMaker is provided.

The fwp_rcrdData custom type provides a general purpose interface for several data management tasks common to database driven web pages. It provides member tags for adding, deleting, updating, and selecting records, as well as some additional utility functions.

Currently the fwp_rcrdData data type supports MySQL and FileMaker Pro, but has been roadmapped to evolve into a fully n-tiered interface for any SQL database with an SDK API for developers to extend the data type's operability with other databases.

The primary purpose for this custom type is to wrap supporting routines, known as ActionShells, around the core database action to integrate comprehensive error management, data input validation, and record locking functionalities into a single, easy-to-use object. The fwp_rcrdData object also helps to automate several user interface responses to these actions.

The fwp_rcrdData data type is not intended to be the only method a developer uses with FWPro to interface to a database, but rather it is a tool available to simplify the implementation of many commonly integrated functions. While the fwp_rcrdData API can handle complex SQL queries, there are going to be occassions where using Lasso's ability to communicate directly with the database will be advantageous. For example, transactions should be written out manually with Lasso's inline tags. The fwp_rcrdData member tags are not yet written to support transactions.

The secondary purpose is to automate the writing of INSERT/ADD, DELETE, UPDATE, and SELECT/SEARCH statements in response to form data and a few simple tag parameters. The SELECT actions can also be specified in detail to allow for complex relational SQL queries, or complex tag pairings for FileMaker, with specialized sort orders.

By consolidating these tasks and supporting routines into a single general purpose custom type, the programmer has a stable and easy-to-use interface for many data tasks, and can focus development efforts on the more complex data and object management issues that are application specific. Additionally, the membertags and parameters for the SQL and FileMaker databases are almost identical which facilitates quick conversion of app code from one database to the other.

Additionally, by writing to the custom type API, the underlying custom tags which perform the details of the actions can be replaced with database-specific versions without requiring any change in the developer's application code.

Data Storage and SELECT Options

This type's primary task is not to store data, but rather to simplify the interface for managing data actions, though there are a few data storage features for convenience. Data retrieved for the object are stored in Lasso's named inline containers. While there would be advantages to using a map or a series of arrays for some objects, it is generally too inefficient at this time to duplicate the data into a custom type for general access purposes. For objects that would benefit from such data structures, it is likely worth the effort to build dedicated custom types. As of this release, the data type does not take advantage of Lasso 7 specific capabilities in this area as the library is stil LP6/LP7 cross compatible.

Each dataset created by an fwp_rcrdData SELECT does, however, contain ->'foundCount', ->'showFirst', and ->'showLast' properties as equivalents to the built-in lasso tags (which are not usable with a named inline records container). Also, each SELECT action returns a string for the named inline name which the programmer stores to his own variable for later display of those records. Other such status data is also available depending on the member tag action performed.

Having said all this, named inlines are often an inconvenient means for using values from a SELECT which will always return a single record. For those cases, the fwp_rcrdData type can create page variables of one or more fields returned in the named inline. A named inline is still created, but some or all of the fields in that named inline can also be specified for use as page variables for convenience or to support n-tier MVC separation of display and logic where all data is converted to vars (abstracting the data source which could be database, text file, XML, etc) for use in the display code.

Syntax

We begin by creating a table connector map in _siteConfigDB.lgc for each SQL database table and FileMaker layout used in the application.

var:'varName' = (map:
   'conn' = 'mysql|fmp',
   'db' = 'databaseName',
   'tbl' = 'tableName');

With those definitions in place, there are two steps to using the fwp_rcrdData custom type. First initializing a new object, and then using the member tags for that object to engage specific actions. To initialize a new object requires the following:

var:'objectVarName'=(fwp_rcrdData:
   -table = connectorMapVarName,
   -keyfld = keyfieldName);

Each member tag of the type has unique parameter requirements, but the typical structure is seen in the ->update and ->select member tags:

Updates a specified record:

$objectVarName->(update:
   -keyfld = keyField,
   -keyVal = keyValue,
   -confirm = fieldList);

Selects a specific record:

$objectVarName->(select:
   -select = fieldList,
   -keyfld = keyField,
   -keyVal = keyValue);

Selects a record set based on full SQL query

$objectVarName->(select:
   -select = fieldList,
   -from = full sql from clause,
   -where = full sql where clause,
   -limit = maxRcrds,
   -orderby = sortDetails);

Selects a record set based on form inputs:

$objectVarName->(select:
   -select = fieldList,
   -where = 'form');

Detailed requirements for each member tag is available with the links below.

Parameters & Member Tags

Initialization Parameters

Either -table or all of -db, -tbl, -conn are required.

-table = required : the table connector map variable

-db = required : the name of the database, in lieu of using -table

-tbl = required : the name of the table (or primary table)

-conn = required : the connector code for the database (mysql|fmp|mssql|pgres|orcl). Only mysql and fmp are currently supported.

-keyfld = the name of the primary key field

Member Tags

->getRcrd = retrieves one record by its keyValue (see details)

->select = retrieves a set of records based on various programmer-defined options (see details)

->addPrep = prepares for an add record form (creates a new keyValue) (see details)

->add = performs field input validation and INSERTs a record (see details)

->deletePrep = prepares for a delete record form by locking the record, and retrieves record content for display (see details)

->delete = DELETEs a record (see details)

->updatePrep = prepares for an update form by locking the record and retrieving field data for display in the form (see details)

->update = performs field input validation and UPDATEs a record (see details)

Instance Variables

The object also has the following properties (some of which apply only when certain member tags are used):

->'foundCount' = number of records found in a SELECT

->'showFirst' = equivalent to Lasso's [shown_first] value resulting form a SELECT

->'showLast' = equivalent to Lasso's [shown_last] value resulting form a SELECT

->'inlineNm' = the name of the Lasso named inline object that records are stored in

->'lock' = the lock ID created by updatePrep and deletePrep

->'error' = error results from the action

->'query' = the literal query string executed for SQL queries, or the series of pairs submitted to FileMaker. Use this for debugging.

Examples

To initialize a new object:

var:'newsRcrds'=(fwp_rcrdData:
  -table = $fw_tblNews,
  -pKey = 'rcrdNo);

To select a single record:

$newsRcrds->(getRcrd:
  -keyVal = $fw_r,
  -select = 'newsTitle, newsDate, newsStory, newsContact');

To display fields from the above selected record:

[records: -inlinename=$newsRcrds->'inlineNm']
[field:'fieldName']
[/records]

Alternatively, where single records are selected for display, use the -mkvars parameter to create variables.

$newsRcrds->(getRcrd:
  -keyVal = $fw_r,
  -select = 'newsTitle, newsDate, newsStory, newsContact',
  -mkvars = true);

The fields will be translated into variables with the same name as the input to field mapping specified in the table's {link=dbTble_ config file:::/refc/dbTbl_{dbtblname}.lasso}.

So, if that file, had the following mapping:

nws_title=newsTitle
nws_date=newsDate
nws_story=newsStory
nws_cntc=newsContact

Then, instead of using the [records] loop and [field:] tags, you would use $nws_title, $nws_date, $nws_story, $nws_cntc to display the field values.

See each of the above links in the Parameters section for detailed examples of each member tag.

API Reference

fwp_rcrdData (Type)

Description

Syntax

Parameters & Member Tags

Examples

New version available

Version 5.5 (zip)

Requirements

Contacts