Book HomeProgramming the Perl DBISearch this book

A.7. DBI Statement Handle Objects

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:

bind_ param


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

See also Section A.2.7, "Placeholders and Bind Values " for more information.

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.

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.

See also the RowCacheSize database handle attribute.



Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.

This HTML Help has been published using the chm2web software.