DBI Statement Handle Objects (Programming the Perl DBI)

This section lists the methods and attributes associated with DBI statement handles.

A.7.1. Statement Handle Methods

The DBI defines the following methods for use on DBI statement handles:


$rc = $sth->bind_param($p_num, $bind_value) || die
$sth->errstr;
$rv = $sth->bind_param($p_num, $bind_value, \%attr)     || ...
$rv = $sth->bind_param($p_num, $bind_value, $bind_type) || ...

The bind_ param method can be used to bind a value with a placeholder embedded in the prepared statement. Placeholders are indicated with the question mark character (?). For example:

$dbh->{RaiseError} = 1;        # save having to check each method call
$sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE ?");
$sth->bind_param(1, "John%");  # placeholders are numbered from 1
$sth->execute;
DBI::dump_results($sth);

Note that the ? is not enclosed in quotation marks, even when the placeholder represents a string. Some drivers also allow placeholders like :name and :n (e.g., :1, :2, and so on) in addition to ?, but their use is not portable. Undefined bind values or undef can be used to indicate null values.

Some drivers do not support placeholders.

With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it. For example:

"SELECT name, age FROM ?"         # wrong (will probably fail)
"SELECT name, ?   FROM people"    # wrong (but may not 'fail')

Also, placeholders can only represent single scalar values. For example, the following statement won't work as expected for more than one value:

"SELECT name, age FROM people WHERE name IN (?)"    # wrong

A.7.1.1. Datatypes for placeholders

The \%attr parameter can be used to hint at which datatype the placeholder should have. Typically, the driver is interested in knowing only if the placeholder should be bound as a number or a string. For example:

$sth->bind_param(1, $value, { TYPE => SQL_INTEGER });

As a shortcut for this common case, the datatype can be passed directly, in place of the \%attr hash reference. This example is equivalent to the one above:

$sth->bind_param(1, $value, SQL_INTEGER);

The TYPE value indicates the standard (non-driver-specific) type for this parameter. To specify the driver-specific type, the driver may support a driver-specific attribute, such as { ora_type => 97 }. The datatype for a placeholder cannot be changed after the first bind_ param call. However, it can be left unspecified, in which case it defaults to the previous value.

Perl only has string and number scalar datatypes. All database types that aren't numbers are bound as strings and must be in a format the database will understand.

As an alternative to specifying the datatype in the bind_ param call, you can let the driver pass the value as the default type (VARCHAR). You can then use an SQL function to convert the type within the statement. For example:

INSERT INTO price(code, price) VALUES (?, CONVERT(MONEY,?))

The CONVERT function used here is just an example. The actual function and syntax will vary between different databases and is non-portable.

bind_param_inout

$rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len) || die $sth->errstr;
$rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, \%attr) || ...
$rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, $bind_type) || ...

This method acts like bind_ param , but also enables values to be updated by the statement. The statement is typically a call to a stored procedure. The $bind_value must be passed as a reference to the actual value to be used.

Note that unlike bind_ param, the $bind_value variable is not read when bind_ param_inout is called. Instead, the value in the variable is read at the time execute is called.

The additional $max_len parameter specifies the minimum amount of memory to allocate to $bind_value for the new value. If the value returned from the database is too big to fit, then the execution should fail. If unsure what value to use, pick a generous length, i.e., a length larger than the longest value that would ever be returned. The only cost of using a larger value than needed is wasted memory.

It is expected that few drivers will support this method. The only driver currently known to do so is DBD::Oracle (DBD::ODBC may support it in a future release). Therefore, it should not be used for database-independent applications.

Undefined values or undef are used to indicate null values. See also "Placeholders and Bind Values" for more information.

execute

$rv = $sth->execute                || die $sth->errstr;
$rv = $sth->execute(@bind_values)  || die $sth->errstr;

Performs whatever processing is necessary to execute the prepared statement. An undef is returned if an error occurs. A successful execute always returns true regardless of the number of rows affected, even if it's zero (see below). It is always important to check the return status of execute (and most other DBI methods) for errors.

For a non-SELECT statement, execute returns the number of rows affected, if known. If no rows were affected, then execute returns 0E0, which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known, then execute returns -1.

For SELECT statements, execute simply "starts" the query within the database engine. Use one of the fetch methods to retrieve the data after calling execute. The execute method does not return the number of rows that will be returned by the query (because most databases can't tell in advance), it simply returns a true value.

If any arguments are given, then execute will effectively call bind_ param for each value before executing the statement. Values bound in this way are usually treated as SQL_VARCHAR types unless the driver can determine the correct type (which is rare), or unless bind_ param (or bind_ param_inout) has already been used to specify the type.

fetchrow_arrayref

$ary_ref = $sth->fetchrow_arrayref;
$ary_ref = $sth->fetch;    # alias

Fetches the next row of data and returns a reference to an array holding the field values. Null fields are returned as undef values in the array. This is the fastest way to fetch data, particularly if used with $sth->bind_columns.

If there are no more rows, or if an error occurs, then fetchrow_arrayref returns an undef. You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the undef returned was due to an error.

Note that the same array reference will currently be returned for each fetch, so don't store the reference and then use it after a later fetch.

fetchrow_array

 @ary = $sth->fetchrow_array;

An alternative to fetchrow_arrayref . Fetches the next row of data and returns it as a list containing the field values. Null fields are returned as undef values in the list.

If there are no more rows, or if an error occurs, then fetchrow_array returns an empty list. You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the empty list returned was due to an error.

In a scalar context, fetchrow_array returns the value of the first field. An undef is returned if there are no more rows or if an error occurred. Since that undef can't be distinguished from an undef returned because the first field value was NULL, you should exercise some caution if you use fetchrow_array in a scalar context.

fetchrow_hashref

 $hash_ref = $sth->fetchrow_hashref;
 $hash_ref = $sth->fetchrow_hashref($name);

An alternative to fetchrow_arrayref . Fetches the next row of data and returns it as a reference to a hash containing field name and field value pairs. Null fields are returned as undef values in the hash.

If there are no more rows, or if an error occurs, then fetchrow_hashref returns an undef. You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the undef returned was due to an error.

The optional $name parameter specifies the name of the statement handle attribute. For historical reasons it defaults to NAME; however, using either NAME_lc or NAME_uc is recommended for portability.

The keys of the hash are the same names returned by $sth->{$name}. If more than one field has the same name, there will only be one entry in the returned hash for those fields.

Because of the extra work fetchrow_hashref and Perl have to perform, this attribute is not as efficient as fetchrow_arrayref or fetchrow_array.

Currently, a new hash reference is returned for each row. This will change in the future to return the same hash ref each time, so don't rely on the current behavior.

fetchall_arrayref

$tbl_ary_ref = $sth->fetchall_arrayref;
$tbl_ary_ref = $sth->fetchall_arrayref( $slice_array_ref );
$tbl_ary_ref = $sth->fetchall_arrayref( $slice_hash_ref  );

The fetchall_arrayref method can be used to fetch all the data to be returned from a prepared and executed statement handle. It returns a reference to an array that contains one reference per row.

If there are no rows to return, fetchall_arrayref returns a reference to an empty array. If an error occurs, fetchall_arrayref returns the data fetched thus far, which may be none. You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the data is complete or was truncated due to an error.

When passed an array reference, fetchall_arrayref uses fetchrow_arrayref to fetch each row as an array ref. If the parameter array is not empty, then it is used as a slice to select individual columns by index number.

With no parameters, fetchall_arrayref acts as if passed an empty array ref.

When passed a hash reference, fetchall_arrayref uses fetchrow_hashref to fetch each row as a hash reference. If the parameter hash is not empty, then it is used as a slice to select individual columns by name. The names should be lower case regardless of the letter case in $sth->{NAME}. The values of the hash should be set to 1.

For example, to fetch just the first column of every row:

$tbl_ary_ref = $sth->fetchall_arrayref([0]);

To fetch the second to last and last column of every row:

$tbl_ary_ref = $sth->fetchall_arrayref([-2,-1]);

To fetch only the fields called "foo" and "bar" of every row:

$tbl_ary_ref = $sth->fetchall_arrayref({ foo=>1, bar=>1 });

The first two examples return a reference to an array of array refs. The last returns a reference to an array of hash refs.

finish

$rc  = $sth->finish;

Indicates that no more data will be fetched from this statement handle before it is either executed again or destroyed. The finish method is rarely needed, but can sometimes be helpful in very specific situations to allow the server to free up resources (such as sort buffers).

When all the data has been fetched from a SELECT statement, the driver should automatically call finish for you. So you should not normally need to call it explicitly.

Consider a query like:

SELECT foo FROM table WHERE bar=? ORDER BY foo

where you want to select just the first (smallest) "foo" value from a very large table. When executed, the database server will have to use temporary buffer space to store the sorted rows. If, after executing the handle and selecting one row, the handle won't be re-executed for some time and won't be destroyed, the finish method can be used to tell the server that the buffer space can be freed.

Calling finish resets the Active attribute for the statement. It may also make some statement handle attributes (such as NAME and TYPE) unavailable if they have not already been accessed (and thus cached).

The finish method does not affect the transaction status of the database connection. It has nothing to do with transactions. It's mostly an internal "housekeeping" method that is rarely needed. There's no need to call finish if you're about to destroy or re-execute the statement handle. See also disconnect and the Active attribute.

rows

$rv = $sth->rows;

Returns the number of rows affected by the last row-affecting command, or -1 if the number of rows is not known or not available.

Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.

For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the rows method or $DBI::rows with SELECT statements is not recommended.

One alternative method to get a row count for a SELECT is to execute a "SELECT COUNT(*) FROM ..." SQL statement with the same "..." as your query, and then fetch the row count from that.

bind_col

$rc = $sth->bind_col($column_number, \$var_to_bind);

Binds an output column (field) of a SELECT statement to a Perl variable. See bind_columns for an example. Note that column numbers count up from 1.

Whenever a row is fetched from the database, the corresponding Perl variable is automatically updated. There is no need to fetch and assign the values manually. The binding is performed at a very low level using Perl aliasing, so there is no extra copying taking place. This makes using bound variables very efficient.

For maximum portability between drivers, bind_col should be called after execute. This restriction may be removed in a later version of the DBI.

You do not need to bind output columns in order to fetch data, but it can be useful for some applications that need either maximum performance or greater clarity of code. The bind_ param method performs a similar but opposite function for input variables.

bind_columns

$rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);

Calls bind_col for each column of the SELECT statement. The bind_columns method will die if the number of references does not match the number of fields.

For maximum portability between drivers, bind_columns should be called after execute.

For example:

$dbh->{RaiseError} = 1; # Do this, or check every call for errors
$sth = $dbh->prepare(q{ SELECT region, sales FROM sales_by_region });
$sth->execute;
my ($region, $sales);

# Bind Perl variables to columns:
$rv = $sth->bind_columns(\$region, \$sales);

# You can also use Perl's \(...) syntax (see perlref docs):
#     $sth->bind_columns(\($region, $sales));

# Column binding is the most efficient way to fetch data
while ($sth->fetch) {
    print "$region: $sales\n";
}

For compatibility with old scripts, the first parameter will be ignored if it is undef or a hash reference.

dump_results

$rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);

Fetches all the rows from $sth, calls DBI::neat_list for each row, and prints the results to $fh (defaults to STDOUT) separated by $lsep (default "\n"). $fsep defaults to ", " and $maxlen defaults to 35.

This method is designed as a handy utility for prototyping and testing queries. Since it uses neat_list to format and edit the string for reading by humans, it is not recomended for data transfer applications.

A.7.2. Statement Handle Attributes

This section describes attributes specific to statement handles. Most of these attributes are read-only.

Changes to these statement handle attributes do not affect any other existing or future statement handles.

Attempting to set or get the value of an unknown attribute is fatal, except for private driver-specific attributes (which all have names starting with a lowercase letter).

For example:

... = $h->{NUM_OF_FIELDS};    # get/read

Note that some drivers cannot provide valid values for some or all of these attributes until after $sth->execute has been called.

See also finish to learn more about the effect it may have on some attributes.

NUM_OF_FIELDS (integer, read-only)

Number of fields (columns) the prepared statement will return. Non-SELECT statements will have NUM_OF_FIELDS == 0.

NUM_OF_PARAMS (integer, read-only)

The number of parameters (placeholders) in the prepared statement. See "Substitution Variables" later in this appendix for more details.

NAME (array-ref, read-only)

Returns a reference to an array of field names for each column. The names may contain spaces but should not be truncated or have any trailing space. Note that the names have the letter case (upper, lower, or mixed) as returned by the driver being used. Portable applications should use NAME_lc or NAME_uc. For example:

print "First column name: $sth->{NAME}->[0]\n";

NAME_lc (array-ref, read-only)

Like NAME but always returns lowercase names.

NAME_uc (array-ref, read-only)

Like NAME but always returns uppercase names.

TYPE (array-ref, read-only) (NEW )

Returns a reference to an array of integer values for each column. The value indicates the datatype of the corresponding column.

The values correspond to the international standards (ANSI X3.135 and ISO/IEC 9075), which, in general terms, means ODBC. Driver-specific types that don't exactly match standard types should generally return the same values as an ODBC driver supplied by the makers of the database. That might include private type numbers in ranges the vendor has officially registered.

For more information, see:

ftp://jerry.ece.umassd.edu/isowg3/dbl/SQL_Registry

Where there's no vendor-supplied ODBC driver to be compatible with, the DBI driver can use type numbers in the range that is now officially reserved for use by the DBI: -9999 to -9000.

All possible values for TYPE should have at least one entry in the output of the type_info_all method (see type_info_all).

PRECISION (array-ref, read-only) (NEW )

Returns a reference to an array of integer values for each column. For non-numeric columns, the value generally refers to either the maximum length or the defined length of the column. For numeric columns, the value refers to the maximum number of significant digits used by the datatype (without considering a sign character or decimal point). Note that for floating-point types (REAL, FLOAT, DOUBLE), the "display size" can be up to seven characters greater than the precision. (for the sign + decimal point + the letter E + a sign + two or three digits).

SCALE (array-ref, read-only) (NEW )

Returns a reference to an array of integer values for each column. NULL (undef) values indicate columns where scale is not applicable.

NULLABLE (array-ref, read-only)

Returns a reference to an array indicating the possibility of each column returning a NULL. Possible values are 0 = no, 1 = yes, 2 = unknown. For example:

print "First column may return NULL\n" if $sth->{NULLABLE}->[0];

CursorName (string, read-only)

Returns the name of the cursor associated with the statement handle, if available. If not available, or if the database driver does not support the "where current of ..." SQL syntax, then it returns undef.

Statement (string, read-only) (NEW )

Returns the statement string passed to the prepare method.

RowsInCache (integer, read-only)

If the driver supports a local row cache for SELECT statements, then this attribute holds the number of unfetched rows in the cache. If the driver doesn't, then it returns undef. Note that some drivers pre-fetch rows on execute, whereas others wait till the first fetch.

A.7. DBI Statement Handle Objects

A.7.1. Statement Handle Methods

A.7.1.1. Datatypes for placeholders

A.7.2. Statement Handle Attributes