SQL Data Sources¶

Supported Features for SQL Data Sources¶

The following lists detail the features of each data source in this chapter. Since some features are only available in certain data sources it is important to check these tables when reading the documentation in order to verify that each data source supports your solution’s required features.

SQL Data Source Tips¶

• Always specify a primary key field using the -keyField parameter for -search, -add, and -findAll actions. This will ensure that the keyField_value method always returns a value.
• Use -keyField and -keyValue parameters to reference a particular record for updates or deletes.
• Data sources can be case-sensitive. For best results, reference database and table names in the same letter case as they appear on disk in your Lasso code. Field names may also be case-sensitive (such as in Oracle and PostgreSQL).
• Some data sources will truncate any data beyond the length they are set up to store. Verify that all fields have sufficient capacity for the values that need to be stored in them.
• Use -returnField parameters to reduce the number of fields that are returned from a -search action. Returning only the fields that need to be used for further processing or shown to the site visitor reduces the amount of data that needs to travel between Lasso and the data source.
• When an -add or -update action is performed on a database, the data from the added or updated record is available inside the capture block of the inline method. If the -returnField parameter is used, only those fields specified should be returned from an -add or -update action. Setting -maxRecords=0 specifies that no record should be returned.

Security Tips¶

• SQL statements that are generated using visitor-defined data should be screened carefully for unwanted commands such as “DROP” or “GRANT”.

• Always sanitize any inputs from site visitors that are incorporated into SQL statements. Any SQL strings that have visitor-defined data should be sanitized using the string->encodeSql method for MySQL data sources and the string->encodeSql92 method for SQL92-compliant data sources such as SQLite, PostgreSQL, or ODBC data sources. Encoding the values in this manner ensures that quotes and other reserved characters are properly escaped within the SQL statement, thereby helping to prevent SQL injection attacks.

  For example, the following SQL “SELECT” statement contains a SQL string in the LIKE clause and uses string->encodeSql to encode the value of the “company” web_request->param. This encoding causes all single quotes within the passed company parameter to be encoded with a backslash.

  local(sql_statement) = "SELECT * FROM contacts.people WHERE company LIKE '" +
        string(web_request->param('company'))->encodeSql + "%';"
  

  If web_request->param('company') returns “McDonald’s” then the SQL statement generated by this code would appear as follows:

  SELECT * FROM Contacts.People WHERE Company LIKE "McDonald's%";
  

SQL Data Source Methods¶

Lasso includes methods to identify which type of SQL data source is being used. These methods are summarized below.

lasso_datasourceIsMySQL(name)¶

Returns “true” if the specified database is hosted by MySQL. Requires one string parameter for the name of a database.

lasso_datasourceIsSybase(name)¶

Returns “true” if the specified database is hosted by Sybase. Requires one string parameter for the name of a database.

lasso_datasourceIsOracle(name)¶

Returns “true” if the specified database is hosted by Oracle. Requires one string parameter for the name of a database.

lasso_datasourceIsPostgreSQL(name)¶

Returns “true” if the specified database is hosted by PostgreSQL. Requires one string parameter for the name of a database.

lasso_datasourceIsSQLServer(name)¶

Returns “true” if the specified database is hosted by Microsoft SQL Server. Requires one string parameter for the name of a database.

lasso_datasourceIsSQLite(name)¶

Returns “true” if the specified database is hosted by SQLite. Requires one string parameter for the name of a database.

if(lasso_datasourceIsMySQL('example'))
   stdoutnl("Example is hosted by MySQL!")
else
   stdoutnl("Example is not hosted by MySQL.")
/if

// => Example is hosted by MySQL!

database_names
   if(lasso_datasourceIsMySQL(database_nameItem))
         '<br />' + database_nameItem + '\n'
   /if
/database_names

// =>
// <br />example
// <br />site

Searching Records with SQL Data Sources¶

In Lasso, there are unique search operations that can be performed using SQL data sources. These search operations take advantage of special functions such as full-text indexing, regular expressions, record limits, and distinct values to allow optimal performance and power when searching. All these search operations can be used on MySQL data sources in addition to all search operations described in the Searching and Displaying Data chapter.

inline(
   -search,
   -database='contacts',
   -table='people',
   -op='ft', 'jobs'='Manager'
) => {^
   records => {^
      '<br />' + field('first_name') + '\n'
   ^}
^}

// =>
// <br />Mike
// <br />Jane

inline(
   -search,
   -database='contacts',
   -table='people',
   -op='rx', 'last_name'='.{8}',
   -maxRecords='all'
) => {^
   records => {^
      '<br />' + field('last_name') + ', ' + field('first_name')
   ^}
^}

// =>
// <br />Lastname, Mike
// <br />Lastname, Mary Beth

inline(
   -search,
   -database='contacts',
   -table='people',
   -op='nrx', 'last_name'='.{8}',
   -maxRecords='all'
) => {^
   records => {^
      '<br />' + field('last_name') + ', ' + field('first_name') + '\n'
   ^}
^}

// =>
// <br />Doe, John
// <br />Doe, Jane
// <br />Surname, Bob
// <br />Surname, Jane
// <br />Surname, Margaret
// <br />Unknown, Thomas

inline(
   -findAll,
   -database='contacts',
   -table='people',
   -returnField='last_name',
   -distinct
) => {^
   records => {^
      '<br />' + field('last_name') + '\n'
   ^}
^}

// =>
// <br />Doe
// <br />Surname
// <br />Lastname
// <br />Unknown

inline(
   -findAll,
   -database='contacts',
   -table='people',
   -returnField='last_name',
   -distinct
) => {^
   '<select name="last_name">\n'
   records => {^
      '   <option value="' + field('last_name') + '">' + field('last_name') + '</option>\n'
   ^}
   '</select>\n'
^}

// =>
// <select name="last_name">
//    <option value="Doe">Doe</option>
//    <option value="Surname">Surname</option>
//    <option value="Lastname">Lastname</option>
//    <option value="Unknown">Unknown</option>
// </select>

inline(
   -search,
   -database='contacts',
   -table='people',
   -groupBy='last_name'
) => {^
   '<select name="last_name">\n'
   records => {^
      '   <option value="' + field('last_name') + '">' + field('last_name') + '</option>\n'
   ^}
   '</select>\n'
^}

inline(
   -findAll,
   -database='contacts',
   -table='people',
   -keyField='id',
   -sortRandom
) => {^
   records => {^
      field('id')
   ^}
^}

// => 5 2 8 1 3 6 4 7

inline(
   -findAll,
   -database='contacts',
   -table='people',
   -maxRecords='5',
   -useLimit
) => {^
   found_count
^}

// => 5

inline(
   -search,
   -database='contacts',
   -table='people',
   -op='eq', 'title'=null,
   -maxRecords='all'
) => {^
   records => {^
      '<br />Record ' + field('id') + ' does not have a title.\n'
   ^}
^}

// =>
// <br />Record 7 does not have a title.
// <br />Record 8 does not have a title.

Adding and Updating Records¶

In Lasso, there are special add and update operations that can be performed using SQL data sources in addition to all the add and update operations described in the Adding and Updating Records chapter.

inline(
   -add,
   -database='contacts',
   -table='people',
   -keyField='id',
   'jobs'='Customer Service',
   'jobs'='Sales'
) => {^
   field('jobs')
^}

// => Customer Service,Sales

inline(
   -update,
   -database='contacts',
   -table='people',
   -keyField='id',
   -keyValue=5,
   'jobs'='Customer Service',
   'jobs'='Sales',
   'jobs'='Support'
) => {^
   field('jobs')
^}

// => Customer Service,Sales,Support

inline(
   -add,
   -database='contacts',
   -table='people',
   -keyField='id',
   'last_name'=null
) => {}

inline(
   -update,
   -database='contacts',
   -table='people',
   -keyField='id',
   -keyValue=5,
   'last_name'=null
) => {}

Value Lists for ENUM or SET Fields¶

A value list in Lasso is a set of possible values that can be used for a field. Value lists in MySQL are lists of predefined and stored values for a “SET” or “ENUM” field type. A value list from a “SET” or “ENUM” field can be displayed using the methods defined below. None of these methods will work in -sql inlines or if -noValueLists is specified.

value_list(name::string)¶

Executes a capture block once for each value allowed for an “ENUM” or “SET” field. Requires a single parameter specifying the name of an “ENUM” or “SET” field from the current table. It will not work in -sql inlines or if the -noValueLists parameter is specified.

value_listItem()¶

While in a value_list capture block, it returns the value for the current item.

selected()¶

Displays the word “selected” if the current value list item is contained in the data of the “ENUM” or “SET” field.

checked()¶

Displays the word “checked” if the current value list item is contained in the data of the “ENUM” or “SET” field.

inline(
   -show,
   -database='contacts',
   -table='people'
) => {^
   value_list('title') => {^
      '<br />' + value_listItem + '\n'
   ^}
^}

// =>
// <br />Mr.
// <br />Mrs.
// <br />Ms.
// <br />Dr.

inline(
   -inlineName='Values',
   -show,
   -database='contacts',
   -table='people'
) => {}

// ...

resultSet(1, -inlineName='Values') => {^
   value_list('title') => {^
      '<br />' + value_listItem + '\n'
   ^}
^}

// =>
// <br />Mr.
// <br />Mrs.
// <br />Ms.
// <br />Dr.

'<form action="response.lasso" method="POST">\n'
inline(
   -show,
   -database='contacts',
   -table='people'
) => {^
   '<select name="title">\n'
   value_list('title') => {^
      '   <option value="' + value_listItem + '">' + value_listItem + '</option>\n'
   ^}
   '</select>\n'
^}
'<p><input type="submit" name="submit" value="Add Record"></p>\n</form>\n'

// =>
// <form action="response.lasso" method="POST">
// <select name="title">
//    <option value="Mr.">Mr.</option>
//    <option value="Mrs.">Mrs.</option>
//    <option value="Ms.">Ms.</option>
//    <option value="Dr.">Dr.</option>
// </select>
// <p><input type="submit" name="submit" value="Add Record"></p>
// </form>

'<form action="response.lasso" method="POST">\n'
inline(
   -show,
   -database='contacts',
   -table='people'
) => {^
   value_list('title') => {^
      '   <input type="radio" name="title" value="' + value_listItem + '" /> ' + value_listItem + '\n'
   ^}
^}
'<p><input type="submit" name="submit" value="Add Record"></p>\n</form>\n'

// =>
// <form action="response.lasso" method="POST">
//    <input type="radio" name="title" value="Mr." /> Mr.
//    <input type="radio" name="title" value="Mrs." /> Mrs.
//    <input type="radio" name="title" value="Ms." /> Ms.
//    <input type="radio" name="title" value="Dr." /> Dr.
// <p><input type="submit" name="submit" value="Add Record"></p>
// </form>

inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   -keyValue=126
) => {^
   value_list('title') => {^
      if(selected != '') => {^
         '<br />' + value_listItem
      ^}
   ^}
^}

// => <br />Mr.

'<br />' + field('title')

// => <br />Mr.

'<form action="response.lasso" method="POST">\n'
inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   -keyValue=126
) => {^
   '<select name="title" multiple size="4">\n'
   value_list('title') => {^
      '   <option value="' + value_listItem + '" ' + selected + '>' + value_listItem + '</option>\n'
   ^}
   '</select>\n'
^}
'<input type="submit" name="submit" value="Update Record">\n</form>\n'

// =>
// <form action="response.lasso" method="POST">
// <select name="title" multiple size="4">
//    <option value="Mr." selected>Mr.</option>
//    <option value="Mrs." >Mrs.</option>
//    <option value="Ms." >Ms.</option>
//    <option value="Dr." >Dr.</option>
// </select>
// <input type="submit" name="submit" value="Update Record">
// </form>

'<form action="response.lasso" method="POST">\n'
inline(
   -search,
   -database='contacts',
   -table='people',
   -keyField='id',
   -keyValue=126
) => {^
   value_list('title') => {^
      '   <input type="checkbox" name="title" value="' + value_listItem + '" ' + checked + '>'
            + value_listItem + '\n'
   ^}
^}
'<input type="submit" name="submit" value="Update Record">\n</form>\n'

// =>
// <form action="response.lasso" method="POST">
//    <input type="checkbox" name="title" value="Mr." checked> Mr.
//    <input type="checkbox" name="title" value="Mrs." > Mrs.
//    <input type="checkbox" name="title" value="Ms." > Ms.
//    <input type="checkbox" name="title" value="Dr." > Dr.
// <input type="submit" name="submit" value="Update Record">
// </form>

SQL Statements¶

Lasso provides the ability to issue SQL statements directly to SQL-compliant data sources, including the MySQL data source. SQL statements are specified within the inline method using the -sql parameter. Many third-party databases that support SQL statements also support the use of the -sql parameter. SQL inlines can be used as the primary method of database interaction in Lasso, or they can be used alongside standard inline actions (e.g. -search, -add, -update, -delete) where a specific SQL function is desired that cannot be replicated using standard database commands.

For most data sources multiple SQL statements can be specified within the -sql parameter separated by a semicolon. Lasso will issue all of the statements to the data source at once and will collect all of the results into result sets. The resultSet_count method returns the number of result sets that Lasso found. The resultSet method can then be used with an integer parameter to return the results from one of the result sets.

The -database parameter can be any database within the data source in which the SQL statement should be executed. The -database parameter determines the data source, and table references within the statement can include both a database name and a table name (e.g. “contacts.people”) in order to fetch results from multiple tables. For example, to create a new database in MySQL, a CREATE DATABASE statement can be executed with -database set to a name of a database in the host you want the new database to reside in.

When referencing the name of a database and table in a SQL statement (e.g. “contacts.people”), only the true names of a database can be used as MySQL does not recognize Lasso database aliases in a SQL command.

Results from a SQL statement are returned in a record set within the inline method. The results can be read and displayed using the records or rows methods and the field or column method. However, many SQL statements return a synthetic record set that does not correspond to the names of the fields of the table being operated upon. This is demonstrated in the examples that follow.

inline(
   -database='example',
   -sql="SELECT 1+2 AS result;"
) => {^
   'The result is: ' + field('result')
^}

// => The result is 3

inline(
   -database='example',
   -sql="SELECT 1+2 AS one, sin(.5) AS two, 5%2 AS three;"
) => {^
   'The results are: ' + field('one') + ', ' + field('two') + ', and ' + field('three')
^}

// => The results are 3, 0.579426, and 1

inline(
   -database='example',
   -sql="SELECT " + (1+2) + " AS one, " + math_sin(0.5) + " AS two, " + (5%2) + " AS three;"
) => {^
   'The results are: ' + field('one') + ', ' + field('two') + ', and ' + field('three')
^}

// => The results are 3, 0.579426, and 1

inline(
   -database='contacts',
   -sql="SELECT * FROM phone_book WHERE first_name = 'John';"
) => {^
   records => {^
      '<br />' + field('first_name') + ' ' + field('last_name') + '\n'
   ^}
^}

// =>
// <br />John Doe
// <br />John Person

inline(
   -database='contacts',
   -sql="INSERT INTO people (first_name, last_name) VALUES ('John',  'Jakob');
         INSERT INTO people (first_name, last_name) VALUES ('Tom',   'Smith');
         INSERT INTO people (first_name, last_name) VALUES ('Sally', 'Brown');"
) => {}

local(real_db) = database_realName('Contacts_alias')
inline(
   -database='contacts_alias',
   -sql="SELECT * FROM `" + #real_db + "`.people;"
) => {}

inline(
   -database='contacts',
   -sql="SELECT * FROM phone_book WHERE first_name = '" +
      string(web_request->param('first_name'))->encodeSql + "';"
) => {}

inline(
   -database='contacts',
   -sql="SELECT * FROM phone_book WHERE first_name = '" +
      string(web_request->param('first_name'))->encodeSql92 + "';"
) => {}

inline(
   -database='contacts',
   -sql="REPAIR TABLE contacts.people;",
   -maxRecords='all'
) => {^
   '<table border="1">\n'
   '<tr>\n'
   loop(field_name(-count)) => {^
      '   <td><b>' + field_name(loop_count) + '</b></td>\n'
   ^}
   '</tr>\n'
   records => {^
      '<tr>\n'
      loop(field_name(-count)) => {^
         '   <td>' + field(field_name(loop_count)) + '</td>\n'
      ^}
      '</tr>\n'
   ^}
   '</table>\n'
^}

// =>
// <table border="1">
// <tr>
//    <td><b>Table</b></td>
//    <td><b>Op</b></td>
//    <td><b>Msg_type</b></td>
//    <td><b>Msg_text</b></td>
// </tr>
// <tr>
//    <td>people</td>
//    <td>Check</td>
//    <td>Status</td>
//    <td>OK</td>
// </tr>
// </table>

inline(
   -database='contacts',
   -sql="SELECT CONCAT(first_name, ' ', last_name) AS name FROM people; SELECT name FROM companies;"
) => {^
   resultSet_count + ' Result Sets\n'
   '<hr />\n'
   loop(resultSet_count) => {^
      resultSet(loop_count) => {^
         records => {^
            '<br />' + field('name') + '\n'
         ^}
         '<hr />\n'
      ^}
   ^}
^}

// =>
// 2 Result Sets
// <hr />
// <br />John Doe
// <br />Jane Doe
// <hr />
// <br />LassoSoft
// <hr />

inline(
   -inlineName='MyResults',
   -database='contacts',
   -sql="SELECT CONCAT(first_name, ' ', last_name) AS name FROM people; SELECT name FROM companies;"
) => {}

// ...

resultSet_count(-inlineName='MyResults') + ' Result Sets\n<hr />'
loop(resultSet_count(-inlineName='MyResults')) => {^
   resultSet(loop_count, -inlineName='MyResults') => {^
      records => {^
         '<br />' + field('name')
      ^}
      '<hr />'
   ^}
^}

SQL Transactions¶

Lasso supports the ability to perform SQL transactions, which are reversible groups of statements, provided that the data source used supports this functionality, such as MySQL 4 and later with certain storage engines. See your data source documentation to see if transactions are supported.

SQL transactions can be achieved within nested inline methods. A single connection to MySQL or ODBC data sources will be held open around the outer inline. Any nested inlines that use the same data source will make use of the same connection.

inline(
   -database='contacts',
   -sql="START TRANSACTION;
         INSERT INTO contacts.people (title, company) VALUES ('Mr.', 'LassoSoft');"
) => {
   if(error_currentError != error_msg_noerror) => {
      inline(-database='contacts', -sql="ROLLBACK;") => {}
   else
      inline(-database='contacts', -sql="COMMIT;") => {}
   }
}

inline(
   -database='contacts',
   -sql="INSERT INTO people (title, company) VALUES ('Mr.', 'LassoSoft');"
) => {^
   inline(-sql="SELECT last_insert_id();") => {^
      field('last_insert_id()')
   ^}
^}

// => 23

Prepared Statements¶

Lasso supports the ability to use prepared statements to speed up database operations provided that the data source used supports this functionality, such as MySQL 4 and later. See your data source documentation to see if prepared statements are supported.

A prepared statement is a cached database query that can speed up database operations by cutting down on the amount of overhead that the data source needs to perform for each statement. For example, processing the following “INSERT” statement requires the data source to load the people table, determine its primary key, load information about its indexes, and determine default values for fields not listed. After the new record is inserted the indexes must be updated. If another “INSERT” is performed then all of these steps are repeated from the beginning:

INSERT INTO people (`first_name`, `last_name`) VALUES ("John", "Doe");

When this statement is changed into a prepared statement then the data source knows to expect multiple executions of the statement. The data source can cache information about the table in memory and reuse that information for each execution. The data source might also be able to defer some operations such as finalizing index updates until after several statements have been executed.

The specific details of how prepared statements are treated are dependent on the data source. The savings in overhead and increase in speed may vary depending on what type of SQL statement is being issued, the size of the table and indexes that are being used, and other factors.

The statement above can be rewritten as a prepared statement by replacing the values with question marks. The name of the table and field list are defined just as they were in the original SQL statement. This statement is a template into which particular values will be placed before the data source executes it:

INSERT INTO people (`first_name`, `last_name`) VALUES (?, ?)

The particular values are specified as an array. Each element of the array corresponds with one question mark from the prepared statement. To insert “John Doe” into the “people” table the following array would be used:

array('John', 'Doe')

One new database action is used to prepare statement and execute them: -prepare is similar to -sql, but informs Lasso that you want to create a prepared statement. Nested inlines are then issued with an array and the -sql parameter. The array should contain values that should be plugged into the prepared statement.

The prepared statement and values shown above would be issued by the following inlines. The outer inline prepares the statement and the inner inline executes it with specific values. Note that the inner inline does not contain any -database or -table parameters. These are inherited from the outer inline so they don’t need to be specified again.

inline(
   -database='contacts',
   -prepare="INSERT INTO people (`first_name`, `last_name`) VALUES (?, ?);"
) => {
   inline(array('John', 'Doe'), -sql) => {}
}

If the executed statement returns any values then those results can be inspected within the inner inline. The inline with the -prepare action will not return any results itself, but each inner inline with a -sql parameter may return a result as if the full equivalent SQL statement were issued in that inline.