Prepare & Execute

Prepare & Execute – Prepare and execute SQL statements

Description

Purpose

prepare() and
execute()
give you more power and flexibilty for query execution.
Prepare/execute mode is helpful when you have to run the
same query several times but with different values in it,
such as adding a list of addresses into a database.

Another place prepare/execute is useful is supporting
databases which have different SQL syntaxes.
Imagine you want to support two databases with different
INSERT syntax:

NB: we don't recommend using non-standard SQL syntax.
The example above is just meant to show what you can do with prepared statements,
but if you use MDB2 because you heart portability,
then be sure you're using a standard SQL syntax (hint: the db1 INSERT is correct).

Prepare

To use the features above, you have to do two steps. Step one is to
prepare the statement which returns an instance
of the MDB2_Statement_Common class.
The second step is to execute it.

To start out, you need to
prepare() a generic
SQL statement. Create a generic statement by writing
the SQL query as usual:

SELECT surname, name, age
FROM person
WHERE name = 'name_to_find' AND age < age_limit

Then substitute "placeholders" for the
literal values which will be provided at run time:

SELECT surname, name, age
FROM person
WHERE name = ? AND age < ?

Then pass this SQL statement to
prepare(), which returns
a statement class instance to be used when calling
execute().

prepare() can handle different types of
placeholders (a.k.a. wildcards). By default all placeholders are handled
as strings. However passing an array of
data types
as the second parameter makes it possible to set a specific type for each placeholder.

Since DML (data manipulation language - INSERT, UPDATE, DELETE) statements
have different return values than data fetches the
prepare() accepts a third parameter. This
parameter should be set to MDB2_PREPARE_MANIP for DML
statements (this way the number of affected rows will be returned).
For data reads it should either be set to MDB2_PREPARE_RESULT,
an array of data types
for each of the columns in the result set or NULL in order to
automatically detect the data types in the result set.

Execute

After preparing the statement, you can execute the query. This
means to assign the variables to the prepared statement. To do
this, execute() requires one argument a scalar or
array with the values to assign.

Passing scalars to execute()

<?php// Once you have a valid MDB2 object named $mdb2...$sth = $mdb2->prepare('INSERT INTO numbers (number) VALUES (?)', array('integer'), MDB2_PREPARE_MANIP);$sth->execute(1);$sth->execute(8);?>

When a prepared statement has multiple placeholders, you must use
an array to pass the values to execute().
The first entry of the array represents the first
placeholder, the second the second placeholder, etc.
The order is independent of the type of placeholder used.

When using named placeholders the data array needs to be an associative
array with the keys matching the placeholder names.

Passing an array to execute()

<?php// Once you have a valid MDB2 object named $mdb2...$sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE id = ?', array('integer'), array('text', 'text'));

$result = $sth->execute(1);?>

The values passed in $data must be literals.
Do not submit SQL functions (for example CURDATE()).
SQL functions that should be performed at execution time need
to be put in the prepared statement. Similarly, identifiers
(i.e. table names and column names) can not be used because
the names get validated during the prepare phase.

ExecuteMultiple

MDB2 contains a process for executing several queries at once.
So, rather than having to execute them manually, like this:

The result is the same. If one of the records failed, the
unfinished records will not be executed.

execute() has three
possible returns:
a new MDB2_Result_Common object
for queries that return results (such as
SELECT queries),
integer for queries that manipulate data (such as
INSERT queries) or a MDB2_Error object on failure

Freeing Prepared Statements

Once you finish using prepared statements, if your script
continues for a while, it's a good idea to save
memory by freeing the prepared statement set via
Use free().

Freeing

<?php// Once you have a valid MDB2 object named $mdb2...$sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE id = ?', array('integer'), array('text', 'text'));

$result = $sth->execute(1);

$sth->free();?>

Limiting rows and reading from an offset

In order to read/write to only a limited number of rows from a result set
and/or to start reading from a specific offset, the
setLimit() can be called prior to calling
prepare().
The limit and offset will only affect the next method call that
will issue a query or prepare a statement and will automatically be reset
after issuing the query. This also applies to any internal queries issued
inside MDB2. Note that limit may not work with DML statements on RDBMS that
emulate limit support and no error will be raised.

Using setLimit with prepare

<?php// Once you have a valid MDB2 object named $mdb2...// read 20 rows with an offset of 10$mdb2->setLimit(20, 10);$sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE group_id = ?', array('integer'), array('text', 'text'));