Lasso Soft Inc. > Home

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.

Method/Keyword Description
[Inline]
Performs the database action specified in the [Inline]. The results of the database action are available inside the capture block or later on the page within [ResultSet].
-Database Specifies the name of the database which will be used to perform the database action. If no -Host is specified then the database is used to determine what data source should process the inline action. (Optional)
-Host Specifies the connection parameters for a host within the inline. This provides an alternative to setting up data source hosts within Lasso Instance Administration. (Optional)
-InlineName Specifies a name for the inline. The same name can be used with the [ResultSet] capture block to return the records from the inline later on the page. (Optional)
-Log Specifies at what log level the statement from the inline should be logged. Values include None, Detail, Warning, and Critical. If not specified then the default log level for action statements will be used. (Optional)
-StatementOnly Specifies that the inline should generate the internal statement required to perform the action, but not actually perform the action. The statement can be fetched with [Action_Statement]. (Optional)
-Table Specifies the table that should be used to perform the database action. Most database actions require that a table be specified.

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.

Keyword Description
-Search Finds records in a database that match specific criteria, returns detail for a particular record in a database, or navigates through a found set of records.
-FindAll Returns all records in a specific database table.
-Random Returns a single, random record from a database table.
-Add Adds a record to a database table.
-Update Updates a specific record from a database table.
-Duplicate Duplicates a specific record in a database table. Only works with FileMaker databases.
-Delete Removes a specified record from a database table.
-Show Returns information about the tables and fields within a database.
-SQL Executes a SQL statement in a compatible data source. Only works with SQL databases.
-Prepare Creates a prepared SQL statement in a compatible data source. Nested inlines with an -Exec action will execute the prepared statement with different values.
-Exec Executes a prepared statement. Must be called from an inline nested within an inline with a -Prepare action.
-Nothing The default action which performs no database interaction, but simply passes the 
parameters of the action.

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
Name is now Jane Person
Name is now John Person

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.

Method Description
[Action_Param]
Returns the value for a specified name/value parameter.
[Action_Params]
Returns an array containing all of the parameters and name/value parameters that define the current action.
[Action_Statement]
Returns the statement that was generated by the datasource to implement the requested action. For SQL datasources this will return a SQL statement. Other datasources may return different values.
[Database_Name] Returns the name of the current database.
[KeyField_Name] Returns the name of the current key field.
[KeyField_Value] Returns the name of the current key value if defined.
[Lasso_CurrentAction] Returns the name of the current Lasso action.
[MaxRecords_Value]
Returns the number of records from the found set that are currently being displayed.
[Response_FilePath] Returns the path to the current Lasso page.
[Table_Name] Returns the name of the current table.
[Search_Arguments] Container tag repeats once for each name/value parameter of the current action.
[Search_FieldItem] Returns the name portion of a name/value parameter of the current action.
[Search_OperatorItem]
Returns the operator associated with a name/value parameter of the current
action.
[Search_ValueItem] Returns the value portion of a name/value parameter of the current action.
[Sort_Arguments] Container tag repeats once for each sort parameter.
[Sort_FieldItem] Returns the field which will be sorted.
[Sort_OrderItem] Returns the order by which the field will be sorted.

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 />';
Action: Find All
Database: Contacts
Table: People
Key Field: ID
KeyValue: 100001
MaxRecords: 50
SkipRecords: 0
Logical Operator: AND
Statement: SELECT * FROM Contacts.People LIMIT 50

 

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(

(-database = LP9), 
(-table = addupdatedelete_tbl), 
(-keyfield = id), 
(-search = true)
)
 
Name Description
Action
The action parameter is always returned first. The name of the first item is set to the action parameter and the value is left empty.
-Database If defined, the name of the current database.
-Table If defined, the name of the current table.
-KeyField
If defined, the name of the field which holds the primary key for the specified table.
-KeyValue If defined, the particular value for the primary key.
-MaxRecords Always included. Defaults to 50.
-SkipRecords Always included. Defaults to 0.
-ReturnField If defined, can have multiple values.
-SortOrder, -SortField
If defined, can have multiple values. -SortOrder is always defined for each -SortField. Defaults to ascending.
-Token
If defined, can have multiple values each specified as -Token.TokenName with the appropriate value.
Name/Value Parameter If defined, each name/value parameter is included.
-Operator
If defined, can have multiple values. Included in order within name/value parameters.
-OperatorBegin
If defined, can have multiple values. Included in order within name/value parameters.
-OperatorEnd
If defined, can have multiple values. Included in order within name/value parameters.

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

 
To output the name/value parameters of the current database action:
Loop through the [Action_Params] and display only name/value pairs for which the name does not start with a hyphen, i.e. any name/value pairs which do not start with a keyword. The following example shows a search of the People table of the Contacts database for a person named John Doe.
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
Insert content here

See more about

Filemaker data-source connectivity

MySQL connectivity

 

LassoSoft Inc. > Home

 

 

©LassoSoft Inc 2014 | Web Development by Treefrog Inc | PrivacyLegal terms and Shipping | Contact LassoSoft