This section lists the methods and attributes associated with DBI statement handles.
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);
Some drivers do not support placeholders.
"SELECT name, age FROM ?" # wrong (will probably fail) "SELECT name, ? FROM people" # wrong (but may not 'fail')
"SELECT name, age FROM people WHERE name IN (?)" # wrong
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.
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.
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.
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.
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.
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.
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.
With no parameters, fetchall_arrayref acts as if passed an empty array ref.
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 });
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).
SELECT foo FROM table WHERE bar=? ORDER BY foo
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.
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.
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.
$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"; }
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 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";
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.
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.
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.
Copyright © 2001 O'Reilly & Associates. All rights reserved.