Tip of the Week - Lasso 8.5 MySQL Prepared Statements

This article was originally published Sep 8, 2006

Tip of the week for September 8, 2006 describes the new prepared statements support in Lasso 8.5 for MySQL data sources. Prepared statements allow repeated actions such as INSERTs or UPDATEs to be performed quickly.

Introduction

Prepared statements provide an efficient method of executing a SQL action more than once. First, an action is "prepared" by passing a template for the action to MySQL with placeholders representing the values that will be filled in later. Then, the action is "executed" multiple times with provided values for the placeholders.

For example, the following statement contains placeholders for three values: first name, last name, and page. Each question mark ? represents a location where a value will be plugged in when the statement is executed.

INSERT INTO contacts.people (first, last, age) VALUES (?, ?, ?)

There are several advantages of using a prepared statement if similar actions are going to be performed with different values:

• MySQL only needs to parse the prepared statement once.
• The SQL statement itself only needs to be transmitted to the server once.
• The data for executing the statement can be sent in a more compact format.
• MySQL may be able to perform additional internal optimizations.

Whether prepared statements can speed up your Web site will depend on what kinds of statements you need to issue and how many times you would be able to execute a single prepared statement. You should always test your particular solution to make sure that using prepared statements is actually producing a speed increase. Prepared statements are most often used with INSERTs or UPDATES.

The following caveats apply to prepared statements:

• MySQL supports the following actions for prepared statements INSERT, UPDATE, REPLACE, SELECT, DELETE, SHOW, SET, DO, and CREATE TABLE.
• Only a single statement can be prepared (multiple statements separated by ; are not allowed).
• Note that SELECTs will not use the query cache if they are issues as prepared statements, which might erase some of the performance benefits.
• Prepared statements are supported in MySQL 4.1 or higher.

You can find some background about prepared statements at this URL: <http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html>.

The -Prepare Action

A statement is prepared with the new -Prepare action. This action is specified in lieu of a -SQL action. The value for the -Prepare action should be a SQL statement with question mark ? in place of where values will eventually be inserted. The inline will not return any results.

The following code prepares a SQL INSERT statement with three value placeholders. Each placeholder represents the corresponding field from the field list: first name, last name, and age.

[Inline: -Database='contacts', 
    -Table='people',
    -Keyfield='id',
    -Prepare='INSERT INTO mytable (first, last, age) VALUES (?, ?, ?)']
  ...
[/Inline]

One or more nested inlines will execute the prepared statement. The prepared statement is automatically destroyed at the end of the inline with the -Prepare action.

The -Exec Action

A statement is executed with the -Exec action. This value is specified in lieu of a -SQL action and must be contained within an inline with a -Prepare action. The value for the -Exec action should be an array with one value for each question mark ? in the prepared statement.

The following code adds two records to a database using a prepared SQL INSERT statement. Note that the internal inlines do not need to specify the -Database, -Table, or -KeyField. They will inherit these values from the outer inline

[Inline: -Database='contacts', 
    -Table='people',
    -Keyfield='id',
    -Prepare='INSERT INTO mytable (first, last, age) VALUES (?, ?, ?)']
  [Inline: -Exec=(Array: 'John', 'Doe', 25)][/Inline]
  [Inline: -Exec=(Array: 'Mary', 'Surname', 34)][/Inline]
[/Inline]

Executing the prepared statement with these values is the equivalent of executing the following two SQL statements.

INSERT INTO mytable (first, last, age) VALUES ('John', 'Doe', 25);
INSERT INTO mytable (first, last, age) VALUES ('Mary', 'Surname', 34);

If the executed statement is a SELECT then the results of the statement can be found within the inline just as they could be if a normal -SQL or -Search action had been performed.

More Information

More information about all of the tags used in this example can be found in the Lasso 8.5 Language Guide or in the online Lasso Reference.

Background information about prepared statement support in MySQL can be found in the MySQL documentation <http://dev.mysql.com/doc/refman/5.0/en/> and <http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html>.