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:

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.



Library Navigation Links

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