Database Interaction Fundamentals¶

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.

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

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

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

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

<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>

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'
   ^}
^}

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

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'
   ^}
^}

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

<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>

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

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.

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

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

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)

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

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.

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

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

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"
// ...

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>

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.

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

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

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

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

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.

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

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