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:

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.

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.

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.

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.

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-SELECTexecute (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
"SELECTCOUNT(*)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 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.

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 "wherecurrent 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.