Database Interaction

One of the primary purposes of Lasso is to perform database actions which are a combination of predefinedand visitor-defined parameters and to format the results of those actions.

This section introduces the fundamentals of specifying database actions in Lasso.

• Inline Database Actions includes full details for how to use the [Inline] tag to specify database actions.

• Action Parameters describes how to get information about an action.

• Results includes information about how to return details of a Lasso database action.

• Showing Database Schema describes the tags that can be used to examine the schema of a database.

• SQL Statements describes the -SQL command tag and how to issue raw SQL statements to SQL-compliant data sources.

• SQL Transactions describes how to perform reversible SQL transactions using Lasso.

Inlines

The [Inline] blocks are used to specify a database action and to present the results of that action within a Lasso page. The database action is specified using parameters as keyword/value parameters within the [Inline]. Additional name/value parameters specify the user-defined parameters of the database action. Each [Inline] normally represents a single database action, but when using SQL statements a single inline can be use to perform batch operations as well. Additional actions can be performed in subsequent or nested [Inline]s.

The results of the database action can be displayed within the contents of the [Inline] capture block using the [Records] capture block and the [Field] method. Alternately, the [Inline] can be named using -InlineName and the results can be displayed later using [ResultSet] capture block.

The entire database action can be specified directly in the opening [Inline] tag or visitor-defined aspects of the action can be retrieved from an HTML form submission. [Link_…] methods can be used to navigate a found set in concert with the use of [Inline]s. Nested [Inline] blocks can be used to create complex database actions.

Inlines can log the statement (SQL or otherwise) that they generate. The optional -Log parameter controls at what level the statement is logged. Setting -Log to None will suppress logging from the inline. If no -Log is specified then the default log-level set for the data source in Lasso Instance Administration will be used.

The -StatementOnly option instructs the data source to generate the implementation-specific statement required to perform the desired database action, but not to actually perform it. The generated statement can be returned with [Action_Statement]. This is useful in order to see the statement Lasso will generate for an action, perform some modifications to that statement, then re-issue the statement using -SQL in another inline.

To see the action statement generated by an inline database action:
Use [Action_Statement] within [Inline] capture blocks. [Action_Statement] will return the action statement that was generated by the data source connector to fulfill the specified database action. For SQL data sources like MySQL and SQLite a SQL statement will be returned. Other data sources may return a different style of action statement.

inline( -database='Database', -table='table', ..., -search )=> {^
	action_statement;
}

To see the action statement that would be generated by the data source without actually performing the database action the -StatementOnly keyword can be specified in the opening [Inline]. The [Action_Statement] will return the same value it would for a normal inline database action, but the database action will not actually be performed.

inline( -database='Database', -table='table', ..., -statementOnly, -search )=> {^
	action_statement;
}

Database Actions

A database action is performed to retrieve data from a database or to manipulate data which is stored in a database. Database actions can be used in Lasso to query records in a database that match specific criteria, to return a particular record from a database, to add a record to a database, to delete a record from a database, to fetch information about a database, or to navigate through the found set from a database search. In addition, database actions can be used to execute SQL statements in compliant databases. 
The database actions in Lasso are defined according to what action parameter is used to trigger the action. The following table lists the parameters which perform database actions that are available in Lasso.

Note: Table 2: Database Action Parameters lists all of the database actions that Lasso supports. Individual data source connectors may only support a subset of these parameters. The Lasso Connector for MySQL and the Lasso Connector for SQLite do not support the -Duplicate action. The Lasso Connector for FileMaker Pro does not support the -SQL action. See the documentation for third party data source connectors for information about what parameters they support.

Each database action parameter requires additional parameters in order to execute the proper database action. These parameters are specified using additional parameters and name/value pairs. For example, a -Database parameter specifies the database in which the action should take place and a -Table parameter specifies the specific table from that database in which the action should take place. Name/value pairs specify the query for a -Search action, the initial values for the new record created by an -Add action, or the  updated values for an -Update action.

Example of specifying a -FindAll action within an [Inline]:
The following example shows an [Inline] that has a -FindAll database action specified in the opening tag. The [Inline] includes a -FindAll parameter to specify the action, -Database and -Table parameters to specify the database and table from which records should be returned, and a -KeyField parameter which specifies the key field for the table. The entire database action is hard-coded within the [Inline]. 

The [Found_Count] returns how many records are in the database. The [Records] capture block repeat their contents for each record in the found set. The [Field] methods are repeated for each found record creating a listing of the names of all the people stored in the Contacts database.

inline( -database='Contacts', -table='People', -keyField='ID', findall )=> {^
	'There are ' + found_count + ' record(s) in the People table.';
	
	records => {^
		'<br />'; field( 'First_Name' ); ' '; field( 'Last_Name' );		
	^} // Close records
^} // Close inline

There are 2 record(s) in the People table.
John Doe
Jane Doe

Example of specifying a -Search action within an [Inline]:
The following example shows an [Inline] tag that has a -Search database action specified in the opening. The [Inline] includes a -Search parameter to specify the action, -Database and -Table parameters to specify the database and table records from which records should be returned, and a -KeyField parameter which specifies the key field for the table. The subsequent name/value parameters, 'First_Name'='John' and 'Last_Name'='Doe', specify the query which will be performed in the database. Only records for John Doe will be returned. The entire database action is hard-coded within the [Inline].

The [Found_Count] method returns how many records for John Doe are in the database. The [Records] capture blocks repeat their contents for each record in the found set. The [Field] methods are repeated for each found record creating a listing of all the records for John Doe stored in the Contacts database.

inline( -database='Contacts', -table='People', -keyfield='ID', 'First_Name'='John', 'Last_Name'='Doe', -search )=> {^
	'There were ' + found_count + ' records(s) found in the People table.';

	records => {^
		'<br />' + field( 'First_Name' ) + ' ' + field( 'Last_Name' );
	^} // Close records
^} // Close inline

There were 1 record(s) found in the People table.
John Doe

Nesting Inline Database Actions

Database actions can be combined to perform compound database actions. All the records in a database that meet certain criteria could be updated or deleted. Or, all the records from one database could be added to a different database. Or, the results of searches from several databases could be merged and used to search another database.

Database actions are combined by nesting [Inline] capture blocks. For example, if [Inline] blocks are placed inside the [Records] capture block within another [Inline] then the inner [Inline] will execute once for each record found in the outer [Inline].

All database results tags function for only the innermost [Inline]. Variables can pass through into nested [Inline]s.

SQL Note: Nested inlines can also be used to perform reversible SQL transactions in transaction-compliant SQL data sources. See the SQL Transactions section at the end of this chapter for more information. 

Example of nesting [Inline]s:
This example will use nested [Inline]s to change the last name of all people whose last name is currently Doe in a database to Person. The outer [Inline] performs a hard-coded search for all records with Last_Name equal to Doe. The inner [Inline] updates each record so Last_Name is now equal to Person. The output confirms that the conversion went as expected by outputting the new values. 

inline( -database='Contacts', -table='People', -keyField='ID', 'Last_Name'='Doe', -maxRecords='All', -search )=> {^
	records => {^
		inline( -database='Contacts', -table='People', -keyField='ID', -keyValue=keyField_value, 'Last_Name'='Person', -update )=> {^
			'<br />Name is now '; field( 'First_Name' ); ' '; field( 'Last_Name' );
		^} // Close inline
	^} // Close records
^} // Close inline

Array Inline Parameters

Most parameters can be used within an [Inline] to specify an action. In addition, parameters and name/value parameters can be stored in an array and then passed into an [Inline] as a block. Any single value in an [Inline] which is an array data type will be interpreted as a series of parameters inserted at that location in the array. This technique is useful for programmatically assembling database actions.

Many parameters can only take a single value within an [Inline]. For example, only a single action can be specified and only a single database can be specified. The last action parameter defines the value that will be used for the action. The last, for example, -Database parameter defines the value that will be used for the database of the action. If an array parameter comes first in an [Inline] then all subsequent parameters will override any conflicting values within the array parameter. 

Example of using an array to pass values into an [Inline]:

The following LassoScript performs a -FindAll database action with the parameters first specified in an array and stored in the variable Params, then passed into the opening [Inline] all at once. The value for -MaxRecords in the [Inline] tag overrides the value specified within the array parameter since it is specified later. Only the number of records found in the database are returned.

local( params ) = Array( -database='Contacts', -table='People', -maxRecords=50, -findall );

inline( #params, -maxRecords=100 )=> {^
	'There are ' + found_count + ' record(s) in the People table.';
^} // Close inline

There are 2 record(s) in the People table.

Action Parameters

Lasso has a set of method which allow for information about the current action to be returned. The parameters of the action itself can be returned or information about the action’s results can be returned.

The following table details the method which allow information about the current action to be returned. If these methods are used within an [Inline] capture block they return information about the action specified in the opening [Inline]. Otherwise, these tags return information about the action which resulted in the current Lasso page being served.

The individual methods can be used to return feedback to site visitors about what database action is being performed or to return debugging information. For example, the following code inserted at the top of a response page to an HTML form or URL or in the body of an [Inline] capture block will return details about the database action that was performed.

'Action:   ' + lasso_currentAction; '<br />';
'Database: ' + database_name; '<br />';
'Table:    ' + table_name; '<br />';
'Key field:' + keyfield_Name; '<br />';
'Key value:' + keyfield_value; '<br />';
'Max records: ' + maxRecords_value; '<br />';
'Skip records: ' + skipRecords_value; '<br />';
'Statement: ' + action_statement; '<br />';

The [Action_Params] can be used to return information about the entire Lasso action in a single array. Rather than assembling information using the individual methods it is often easier to extract information from the [Action_Params] array. 

The schema shows the names of the values which are returned in the array. Even if -Layout is used to specify the layout for a database action, the value of that tag is returned after -Table in the [Action_Params] array.

To output the parameters of the current database action:
The value of the [Action_Params] in the following example is formatted to show the elements of the returned array clearly.

inline( -database='LP9', -table='addupdatedelete_tbl', -keyfield='id', -search )=> {^
	action_params;
^}

staticarray(

The [Action_Params] array contains all the parameters and name/value parameters required to defined in the [Inline].

inline( -database='Contacts', -table='People', -keyField='ID', 'First_Name'='John', 'Last_Name'='Doe', -search )=> {^
	WITH k IN action_params
	WHERE !#k->first( )->beginsWith( '-' )
	DO {^
		#k; '<br />';
	^} // Close Do
^} // Close inline

See more about

Filemaker data-source connectivity

MySQL connectivity