Database Interaction Fundamentals

A database is the cornerstone of any significant web application. One of the primary applications of Lasso is to perform database actions and format the results of those actions. This chapter introduces the fundamentals of specifying database actions in Lasso.

Using Inlines

The inline method is used to specify a database action and to present the results of that action within a Lasso page. The database action is specified using keyword parameters passed to 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 methods.

inline(...)

Performs the database action specified by the parameters. The results of the database action are available inside the required capture block or, if an -inlineName is specified, later on the page within resultSet, records, or rows methods.

Parameters:
  • -database – Specifies the name of the database that will perform the database action. If no -host is specified then the database is used to look up the data source specified in Lasso Admin for that database. Optional.
  • -host – Specifies the connection parameters for a host within the inline. This provides an alternative to setting up data source hosts within Lasso Admin. Optional. See the table Host Array Parameters for the options available.
  • -inlineName – Specifies a name for the inline. The same name can be used with resultSet, records, or rows methods to return the records from the inline later on in the page. 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 receive the database action. Most database actions require that a table be specified. The -table is used to determine what encoding will be used when interpreting database results, so a -table may be necessary even for an inline with an -sql action. Optional.

The results of the database action can be displayed within the contents of the inline’s capture block using the records or rows methods along with field or column methods. Alternately, the inline can be named using -inlineName and the results can be displayed later using resultSet, records, or rows methods.

The entire database action can be specified directly in the opening inline method, or visitor-defined aspects of the action can be retrieved from query or post parameters. Nested inline methods can create complex database actions.

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 for seeing the statement Lasso will generate for an action.

Database Actions

A database action is performed to retrieve data from a database or to manipulate data stored in a database. Database actions in Lasso can query records in a database that match specific criteria, return a particular record from a database, add a record to a database, delete a record from a database, fetch information about a database, or navigate through the found set from a database search. Additionally, database actions can execute SQL statements in databases that understand SQL.

The database actions in Lasso are defined according to which action parameter is used to trigger the action. The following table lists the parameters that perform database actions that are available in Lasso.

Database Action Parameters
Parameter 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 in a database table.
-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 SQLite, MySQL, and other SQL databases.
-prepare=? Creates a prepared SQL statement in a compatible data source. Nested inlines will execute the prepared statement with different values.
-nothing The default action which performs no database actions, but simply passes the parameters of the action.

Note

The table Database Action Parameters lists all of the database actions that Lasso supports. Individual data source connectors may only support a subset of these parameters. For example, the Lasso Connector for FileMaker Server does not support the -sql action. See the documentation for third-party data source connectors for information about which actions they support.

Each database action parameter requires additional parameters in order to execute the action properly. These parameters are specified using additional keyword parameters. 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. Keyword parameters 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.

Full documentation on which inline parameters are required for each action are detailed in the section specific to that action in this chapter or in subsequent chapters.

Specifying a -FindAll Action Within an Inline

The following example shows an inline method that has a -findAll database action specified. 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 to specify the key field for the table. The entire database action is hard-coded within the inline method.

The method found_count returns how many records are in the database. The records method executes the code in the capture block 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 “people” table.

inline(
   -findAll,
   -database='contacts',
   -table='people',
   -keyField='id'
) => {^
   'There are ' + found_count + ' record(s) in the People table.\n'
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

// =>
// There are 2 record(s) in the People table.
// <br />John Doe
// <br />Jane Doe

Specifying a -Search Action Within an Inline

The following example shows an inline method that has a -search database action . 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 to specify the key field for the table. The subsequent keyword parameters, 'first_name'='John' and 'last_name'='Doe', specify the query that 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 method found_count returns how many records for “John Doe” are in the database. The records method executes the code in the capture block 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 “people” table:

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'first_name'='John',
   'last_name'='Doe'
) => {^
   'There were ' + found_count + ' record(s) found in the People table.\n'
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

// =>
// There were 1 record(s) found in the People table.
// <br />John Doe

Displaying the Generated Action Statement

Use the action_statement method within the inline method. This returns 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(-search, -database='example', -table='example', /* etc. */) => {^
   action_statement
   // ...
^}

To see the action statement that would be generated by the data source without actually performing the database action, the -statementOnly parameter can be specified in the inline method. The action_statement method will return the same value it would for a normal inline database action, but the database action will not actually be performed.

inline(-search, -database='example', -table='example', -statementOnly, /* etc. */) => {^
   action_statement
   // ...
^}

Inlines and HTML Forms

The previous two examples show how to specify a hard-coded database action completely within an inline method. This is an excellent way to embed a database action that will be the same every time a page is loaded, but does not provide any room for visitor interaction.

A more powerful technique is to use values from an HTML form or URL to allow a site visitor to modify the database action that is performed within the inline. The following two examples demonstrate two different techniques for doing this using the singular web_request->param method and the tie-based web_request->params method.

Using HTML Form Values Within an Inline

An inline-based database action can make use of visitor-specified parameters by reading values from an HTML form that the visitor customizes and submits to trigger the page containing the inline method.

The following HTML form provides two inputs into which the visitor can type information. An input is provided for “first_name” and one for “last_name”. These correspond to the names of fields in the “people” table. The action of the form is set to “response.lasso” which will contain the inline that performs the actual database action:

<form action="response.lasso" method="POST">
   <br />First Name: <input type="text" name="first_name" value="" />
   <br />Last Name: <input type="text" name="last_name" value="" />
   <br /><input type="submit" name="submit" value="Search" />
</form>

The inline in “response.lasso” contains the pair parameter 'first_name'=web_request->param('first_name'). The web_request->param method instructs Lasso to fetch the input named “first_name” from the form post parameters submitted to the current page being served, namely the form shown above. The inline contains a similar pair parameter for “last_name”.

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'first_name'=web_request->param('first_name'),
   'last_name'=web_request->param('last_name')
) => {^
   'There were ' + found_count + ' record(s) found in the People table.\n'
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

If the visitor entered “Jane” for the first name and “Doe” for the last name then the following results would be returned:

// =>
// There were 1 record(s) found in the People table.
// <br />Jane Doe

As many parameters as needed can be named in the HTML form and then retrieved in the response page via the inline.

Tip

The web_request->param member method is a replacement for the action_param or form_param methods used in prior versions of Lasso for fetching GET or POST data.

Using an Array of Form Values Within an Inline

Rather than specifying each web_request->param individually, an entire set of HTML form parameters can be entered into an inline method using the web_request->params method. Inserting the web_request->params method into an inline functions as if all the parameters and name/value pairs in the HTML form were placed into the inline at the location of the web_request->params parameter.

The inline method in our updated “response.lasso” contains the parameter web_request->params. This instructs Lasso to take all the parameters from the HTML form or URL which results in the current page being loaded and insert them in the inline as if they had been typed at the location of web_request->params. This will cause the name/value pairs for “first_name” and “last_name” entered in the form above to be inserted into the inline.

inline(
   web_request->params,
   -search,
   -database='contacts',
   -table='people',
   -keyField='id'
) => {^
   'There were ' + found_count + ' record(s) found in the People table.\n'
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

If the visitor entered “Jane” for the first name and “Doe” for the last name then the following results would be returned:

// =>
// There were 1 record(s) found in the People table.
// <br />Jane Doe

As many parameters as needed can be named in the HTML form. They will all be passed into the inline at the location of the web_request->params method.

Tip

The web_request->params member method is a replacement for the action_params method used in prior versions of Lasso for fetching GET or POST data.

Setting HTML Form Values

If the Lasso page containing an HTML form is the action to an HTML form or the URL has query parameters, the values of the HTML form inputs can be set to values passed from the previous Lasso page using web_request->param.

For example, if a form is on “default.lasso” and the action of the form is also “default.lasso” then the same page will be reloaded with the visitor-specified form values each time the form is submitted. The following HTML form uses web_request->param calls to automatically restore the values the user specified in the form previously each time the page is reloaded:

<form action="default.lasso" method="POST">
   First Name: <br />
   <input type="text" name="first_name" value="[web_request->param('first_name')]" />
   Last Name: <br />
   <input type="text" name="last_name" value="[web_request->param('last_name')]" />
   <br />
   <input type="submit" name="submit" value="Submit" />
</form>

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 methods. For example, if inlines are placed inside a records method within another inline then the inner inline methods will execute once for each record found in the outer inline method.

All database result methods function for only the innermost inline method. Variables can pass through into nested inlines.

Tip

SQL nested inlines can also perform reversible SQL transactions in transaction-compliant data sources. See the section SQL Transactions in the SQL Data Sources chapter for more information.

Updating Specific Records with Nested Inlines

This example uses nested inline methods 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(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'last_name'='Doe',
   -maxRecords='all'
) => {^
   records => {^
      inline(
         -update,
         -database='contacts',
         -table='people',
         -keyField='id',
         -keyValue=keyField_value,
         'last_name'='Person'
      ) => {^
         '<br />Name is now ' + field('first_name') + ' ' + field('last_name') + '\n'
      ^}
   ^}
^}

// =>
// <br />Name is now John Person
// <br />Name is now Jane Person

Array-based Inline Parameters

Most parameters used within an inline method specify an action. Additionally, keyword parameters and name/value pair parameters can be stored in an array and then passed into an inline as a group. Any single value in an inline that is an array object will be interpreted as a series of parameters inserted at the location of the array. This technique is useful for programmatically assembling database actions.

Many parameters can only take a single value within an inline method. For example, only a single action can be specified and only a single database can be specified. The last parameter defines the value that will be used for the action. For example, the last -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.

Using an Array to Pass Values Into an Inline

The following Lasso code performs a -findAll database action with the parameters first specified in an array and stored in the variable “params”, then passed into an inline method all at once. The value for -maxRecords in the inline 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) = (:
   -findAll,
   -database='contacts',
   -table='people',
   -maxRecords=50
)
inline(#params, -maxRecords=100) => {^
   'There are ' + found_count + ' record(s) in the People table.'
^}

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

Inline Introspection Methods

Lasso has a set of methods that return information about the current inline’s action. The parameters of the action itself can be returned or information about the action’s results can be returned.

The following methods can be used within an inline method’s capture block to return information about the action specified by the inline.

action_param(name::string, join::string='rn')
action_param(name::string, -count)
action_param(name::string, position::integer)

Requires a parameter specifying the name of a keyword or pair parameter passed to the inline method. If no other parameter is specified, it returns all values it finds for the specified name joined together with a line break. An optional second parameter can specify the string to use as a separator when it finds more than one parameter with the specified name.

To find the number of parameters passed to an inline method that share a specified name, specify -count as the second parameter. This will return the number of parameters sharing the same name. To get the value of a specific one of these parameters, instead pass an integer specifying which parameter you want. For example, if an inline is passed four parameters that share the same name, the one that comes third can be retrieved by passing a “3” as the second value to action_param.

action_params()

Returns an array containing all of the keyword parameters and pair parameters that define the current action.

action_statement()

Returns the statement that was generated for the data source to implement the requested action. For SQL databases, this will return a SQL statement. Other data sources may return different values.

database_name()

Returns the name of the current database.

keyField_name()
keyColumn_name()

Returns the name of the current key field.

keyField_value()
keyColumn_value()

Returns the name of the current key value if defined. Can also be used for actions that add a new record to get the newly generated ID.

lasso_currentAction()

Returns the name of the current action.

maxRecords_value()

Returns the number of records from the found set that are currently being displayed.

skipRecords_value()

Returns the current offset into a found set.

table_name()
layout_name()

Returns the name of the current table.

search_arguments()

Executes a capture block once for each pair parameter in the current action.

search_fieldItem()

Used in the capture block of a search_arguments method. Returns the “name” portion of the current pair parameter.

search_valueItem()

Used in the capture block of a search_arguments method. Returns the “value” portion of the current pair parameter.

search_operatorItem()

Used in the capture block of a search_arguments method. Returns the operator associated with the current pair parameter.

sort_arguments()

Executes a capture block once for each sort parameter in the current action.

sort_fieldItem()

Used in the capture block of a sort_arguments method. Returns the field that will be sorted.

sort_orderItem()

Used in the capture block of a sort_arguments method. Returns the direction in which the field will be sorted.

Display Parameters of the Current Database Action

The value of the action_params method in the following example is formatted to clearly show the elements of the returned array:

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id'
) => {^
   action_params
^}

// =>
// staticarray(
//     (-search = true),
//     (-database = contacts),
//     (-table = people),
//     (-keyField = id)
// )

Display Parameter Pairs of the Current Database Action

Loop through the action_params method and display only name/value pairs for which the name does not start with a hyphen, i.e., any pair parameters and not keyword parameters. The following example shows a search of the “people” table of the “contacts” database for a person named “John Doe”:

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'first_name'='John',
   'last_name'='Doe'
) => {^
   with param in action_params
   where not #param->first->beginsWith('-')
   sum '<br />' + #param->asString->encodeHtml + '\n'
^}

// =>
// <br />(first_name = John)
// <br />(last_name = Doe)

Display Action Parameters to a Site Visitor

The search_arguments method can be used in conjunction with the search_fieldItem, search_valueItem and search_operatorItem methods to return a list of all pair parameters and associated operators specified in a database action.

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   'first_name'='John',
   'last_name'='Doe'
) => {^
   search_arguments => {^
      '<br />' + search_fieldItem + ' ' + search_operatorItem + ' ' + search_valueItem + '\n'
   ^}
^}

// =>
// <br />first_name BW John
// <br />last_name BW Doe

The sort_arguments method can be used in conjunction with the sort_fieldItem and sort_orderItem methods to return a list of all sort parameters specified in a database action.

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   -sortField='first_name', -sortOrder='descending',
   -sortField='last_name'
) => {^
   sort_arguments => {^
      '<br />' + sort_fieldItem + ' ' + sort_orderItem + '\n'
   ^}
^}

// =>
// <br />first_name descending
// <br />last_name ascending

Inline Action Result Methods

The following documentation details the methods that return information about the results of the current action. These methods provide information about the current found set rather than providing data about the database or providing information about what database action was performed. Examples of using most of these methods are provided in the Searching and Displaying Data and SQL Data Sources chapters.

field(name::string, ...)
column(name::string, ...)

Returns the value for a specified field from the result set. Can optionally take one of the following encoding keyword parameters: -encodeNone, -encodeHtml, -encodeBreak, -encodeSmart, -encodeUrl, -encodeStrictUrl, -encodeXml.

found_count() → integer

Returns the number of records found by the database action.

records(inlineName::string)
records(-inlineName::string=?)
rows(inlineName::string)
rows(-inlineName::string=?)

Loops once for each record in the found set. Any field methods within the records or rows methods return the value for the specified field in each row in turn. Can be used outside of an inline capture block by specifying the name of a previously declared inline method with an -inlineName keyword parameter or just by passing in an inline name.

records_array()
rows_array()

Returns the complete found set in a staticarray of staticarrays. The outer staticarray contains one staticarray for every row in the found set. The inner staticarrays contain one item for each field in the result set.

records_map(...)

Returns the complete found set in a map of maps. See the table below for details about the parameters and output of records_map.

Parameters:
  • -keyField – The name of the field to use as the key for the outer map. Defaults to the current keyField_name, “ID”, or the first field of the database results.
  • -returnField – Specifies a field name that should be included in the inner map. Should be called multiple times to include multiple fields. If no -returnField is specified then all fields will be returned.
  • -excludeField – The name of a field to exclude from the inner map. If no -excludeField is specified then all fields will be returned.
  • -fields – An array of field names to use for the inner map. By default the value for field_names will be used.
  • -type – By default the method returns a map of maps. By specifying -type='array' the method will instead return an array of maps. This can be useful when the order of records is important.
resultSet_count(-inlineName=?)

Returns the number of result sets that were generated by the inline. This will generally only be applicable to inlines that include a -sql parameter with multiple statements. An optional -inlineName parameter specifying the name of another inline will return the number of result sets that it has, outside of that inline’s capture block.

resultSet(-inlineName=?)
resultSet(num::integer, -inlineName=?)
resultSet(num::integer, inlineName::string)

Returns a single result set from an inline. The method can take an integer specifying which result set to return, defaulting to the first set if it is not specified. An optional -inlineName keyword parameter or just an inline name will return that inline’s result set.

shown_count()

Returns the number of records shown in the current found set. Less than or equal to maxRecords_value.

shown_first()

Returns the number of the first record shown from the found set. Usually skipRecords_value plus one.

shown_last()

Returns the number of the last record shown from the found set.

The action result methods display information about the current found set. For example, the following code generates a status message that can be displayed under a database listing:

'Found ' + found_count + ' records.\n'
'<br />Displaying ' + shown_count + ' records from ' + shown_first + ' to ' + shown_last + '.'

// =>
// Found 100 records.
// Displaying 10 records from 61 to 70.

These methods can also create links that allow a visitor to navigate through a found set.

Using a Records Array

The records_array method gets access to all of the data from an inline operation. The method returns a staticarray with one element for each record/row in the found set. Each element is itself a staticarray that contains one element for each field/column in the found set.

The method can either quickly output all of the data from the inline operation or can be used with the iterate methods or query expressions to access the data programmatically. (Of course, at that point, you probably just want to use the records or rows methods with the field or column methods.)

inline(-search, -database='contacts', -table='people') => {^
   records_array
^}

// => staticarray(staticarray(1, John, Doe), staticarray(1, Jane, Doe), ...)

The output can be made easier to read on a web page using the iterate method and the array->join method:

inline(-search, -database='contacts', -table='people') => {^
   iterate(records_array, local(record)) => {^
      '<br />' + ('"' + #record->join('", "') + '"')->encodeHtml + '\n'
   ^}
^}

// =>
// <br />&quot;1&quot;, &quot;John&quot;, &quot;Doe&quot;
// <br />&quot;2&quot;, &quot;Jane&quot;, &quot;Doe&quot;
// ...

// Web output
// =>
// "1", "John", "Doe"
// "2", "Jane", "Doe"
// ...

The output can be listed with the appropriate field names by using the field_names method, which returns an array containing each field name from the current found set. It will always contain the same number of elements as the elements of the records_array method.

inline(-search, -database='contacts', -table='people') => {^
   '<table>\n'
   '<tr><td>' + field_names->join('</td><td>')->encodeHtml(false, true) + '</td></tr>\n'
   iterate(records_array, local(record)) => {^
      '<tr>\n'
      '   <td>' + #record->join('</td><td>')->encodeHtml(false, true) + '</td>\n'
      '</tr>\n'
   ^}
   '</table>\n'
^}

// =>
// <table>
// <tr><td>id</td><td>first_name</td><td>last_name</td></tr>
// <tr>
//    <td>1</td><td>John</td><td>Doe</td>
// </tr>
// <tr>
//    <td>2</td><td>Jane</td><td>Doe</td>
// </tr>
// ...
// </table>

Together the field_names and records_array methods provide a programmatic process of accessing all the data returned by an inline action. There may be some cases when these methods yield better performance than using records, field, and field_name methods.

Using a Records Map

The records_map method functions similarly to the records_array method, but returns all of the data from an inline operation as a map of maps. The keys for the outer map are the key field values for each record from the table. The keys for the inner map are the field names for each record in the found set.

inline(-search, -database='contacts', -table='people', -keyField='id') => {^
   records_map
^}

// => map(1 = map(first = John, last = Doe), 2 = map(first = Jane, last = Doe), ...)

Database Schema Inspection Methods

The schema of a database can be inspected using the database_… methods or the inline -show action parameter which allows information about a database to be returned using the field_name method. Value lists within FileMaker Server databases can also be accessed using the -show parameter. This is documented in the FileMaker Data Sources chapter.

The -show action parameter functions like the -search parameter except that no name/value pair parameters, sort parameters, result parameters, or operator parameters are required. The only other parameters required for a -show action are the -database and -table parameters. It is also recommended that you specify the -keyField parameter.

The methods detailed below are for inspecting the schema of a database. The field_name method must be used in concert with a -show action or any database action that returns results including -search, -add, -update, -random, or -findAll. The database_names and database_tableNames methods can be used on their own.

database_names()

Executes the capture block for every database specified in Lasso Admin. Requires using database_nameItem to show results.

database_nameItem()

Used inside the capture block of a database_names method to return the name of the current database.

database_realName(alias::string)

Returns the real name of a database given the alias that Lasso uses for the name.

database_tableNames(dbname::string)

Executes the capture block for every table in the specified database. Requires using database_tableNameItem to show results.

database_tableNameItem()

Used inside the capture block of a database_tableNames method to return the name of the current table.

field_name(-count)
field_name(position::integer, -type=?)
column_name(-count)
column_name(position::integer, -type=?)

If passed the parameter -count then it returns the number of fields in the current table. If passed an integer, it returns the name of a field at that position in the current database and table. If passed an integer and then the -type parameter, it returns the type of field rather than the name. Types include “Text”, “Number”, “Date/Time”, “Boolean”, and “Unknown”.

field_names()
column_names()

Returns an array containing all the field names in the current result set. This is the same data as returned by field_name, but in a format more suitable for iterating or other data processing.

List All Databases Entered in Lasso Admin

The following example shows how to list the names of all databases set in Lasso Admin using the database_names and database_nameItem methods:

database_names => {^
   '<br />' + loop_count + ': ' + database_nameItem + '\n'
^}

// =>
// <br />1: Contacts
// <br />2: Examples
// <br />3: Site

List All Tables Within a Database

The following example shows how to list the names of all the tables within a database using the database_tableNames and database_tableNameItem methods. The tables within the “Site” database are listed:

database_tableNames('contacts') => {^
   '<br />' + loop_count + ': ' + database_tableNameItem + '\n'
^}

// =>
// <br />1: companies
// <br />2: people

List All Fields Within a Table

The following example demonstrates how to return information about the fields in a table using the inline method to perform a -show action. A loop method loops through the number of fields in the table and the name and type of each field is returned. The fields within the “contacts” table are shown:

inline(
   -show,
   -database='contacts',
   -table='people',
   -keyField='id'
) => {^
   loop(field_name(-count)) => {^
      '<br />' + loop_count + ': ' + field_name(loop_count) +
            ' (' + field_name(loop_count, -type) + ')\n'
   ^}
^}

// =>
// <br />1: creation_date (Date)
// <br />2: id (Number)
// <br />3: first_name (Text)
// <br />4: last_name (Text)

Inline Connection Options

Lasso provides two different ways to specify the data source that should execute an inline database action. The connection characteristics for the data source host can be specified entirely within the inline or the connection characteristics can be specified within Lasso Admin and then looked up based on which -database is specified within the inline.

Each of these options is described in more detail below including when one may be preferable to the other and the drawbacks of each. The database method is used throughout most of the examples in this documentation.

Database Name Method

An inline containing only a -database parameter will look up which host and data source should service the inline. If there is a -table parameter, Lasso uses this to look up which encoding should be used for the results of the database action. If an inline does not have a specified -database then it inherits the -database (and -table and -keyField) from the surrounding inline.

Advantages:When using the database method, all of the connection characteristics for the data source host are defined in Lasso Admin. This makes it easy to change the characteristics of a host, and even move databases from one host to another, without modifying any Lasso code.
Disadvantages:Setting up a new data source when using the database method requires visiting Lasso Admin. This helps promote good security practices, but can be an impediment when working on simple web sites or when quickly mocking up solutions. Additionally, having part of the set up for a website in Lasso Admin means that Lasso must be configured properly in order to deploy a solution. It is sometimes desirable to have all of the configuration of a solution contained within the code files of the solution itself.

Host Array Method

With the host array method, all of the characteristics of the data source host that will process the inline database action are specified directly within the inline.

Advantages:Data source hosts can be quickly specified directly within an inline. No need to visit Lasso Admin to set up a new data source host. Additionally, there is reduced overhead since the connection information doesn’t need to be retrieved from the SQLite database.
Disadvantages:The username and password for the host must be embedded within the Lasso code. (Although this can be in code that is not in the web root, thereby mitigating this disadvantage.) Also, switching data source hosts can be more difficult if inline hosts have been hard-coded.

Inline hosts are specified using a -host parameter within the inline. The value for this parameter is an array specifying the connection characteristics for the database host. The following example shows an inline host for the MySQL data source that connects to “localhost” using a username of “lasso”:

inline(
   -host=(:
      -datasource='mysqlds',
      -name='localhost',
      -username='lasso',
      -password='secret'
   ),
   -sql="SHOW DATABASES;"
) => {^
   records_array
^}

// => staticarray(staticarray(contacts), staticarray(examples), staticarray(site))

The following table lists all of the parameters that can be specified within the -host array. Some data sources may require that just the -datasource be specified, but most data sources will require -datasource, -name, -username, and -password.

The -host parameter can also take a value of “inherit” which specifies that the -host from the surrounding inline should be used. This is necessary when specifying a -database within nested inlines to prevent Lasso from looking up the database as it would using the database method.

Host Array Parameters
Parameter Description
-dataSource=? Required data source name. The name for each data source can be found in the “Datasources” section of Lasso Server Admin.
-name=? The IP address, DNS host name, or connection string for the data source. Required for most data sources.
-port=? The port for the data source. Optional.
-username=? The username for the data source connection. Required for most data sources.
-password=? The password for the username. Required for most data sources.
-schema=? The schema for the data source connection. Required for some data sources.
-tableEncoding=? The table encoding for the data source connection. Defaults to “UTF-8”. Optional.
-extra=? Configuration information that may be used by some data sources. Optional.

Note

Consult the documentation for each data source for details about which parameters are required, their format, and whether the -extra parameter is used.

Once a -host array has been specified the rest of the parameters of the inline will work much the same as they do in inlines that use a configured data source host. The primary differences are explained here:

  • Nested inlines will inherit the -host from the surrounding inline if they are specified with -host='inherit' or if they do not contain a -database parameter.
  • Nested inlines that have a -database parameter and no -host parameter will use the -database parameter to look up the data source host.
  • Nested inlines can specify a different -host parameter than the surrounding inline. Lasso can handle arbitrarily nested inlines, each of which can use a different host.
  • The parameters -database, -table, -keyField (or -key), and -schema may be required depending on the database action. Inline actions such as -search, -findAll, -add, -update, -delete, etc. require that the database, table, and key field be specified just as they would need to be in any inline.
  • Some SQL statements may also require that a -database be specified. For example, in MySQL, a host-level SQL statement like SHOW DATABASES doesn’t require that a -database be specified. A table-level SQL statement like SELECT * FROM 'people' won’t work unless the -database is specified in the inline. (A fully qualified SQL statement like SELECT * FROM 'contacts'.'people' will also work without a -database.)