Apache-LoggedAuthDBI
view release on metacpan or search on metacpan
It is also possible for C<HandleError> to hide an error, to a limited
degree, by using L</set_err> to reset $DBI::err and $DBI::errstr,
and altering the return value of the failed method. For example:
$h->{HandleError} = sub {
return 0 unless $_[0] =~ /^\S+ fetchrow_arrayref failed:/;
return 0 unless $_[1]->err == 1234; # the error to 'hide'
$h->set_err(undef,undef); # turn off the error
$_[2] = [ ... ]; # supply alternative return value
return 1;
};
This only works for methods which return a single value and is hard
to make reliable (avoiding infinite loops, for example) and so isn't
recommended for general use! If you find a I<good> use for it then
please let me know.
=item C<HandleSetErr> (code ref, inherited)
The C<HandleSetErr> attribute can be used to intercept
the setting of handle C<err>, C<errstr>, and C<state> values.
If set to a reference to a subroutine then that subroutine is called
whenever set_err() is called, typically by the driver or a subclass.
The subroutine is called with five arguments, the first five that
were passed to set_err(): the handle, the C<err>, C<errstr>, and
C<state> values being set, and the method name. These can be altered
by changing the values in the @_ array. The return value affects
set_err() behaviour, see L</set_err> for details.
It is possible to 'stack' multiple HandleSetErr handlers by using
closures. See L</HandleError> for an example.
The C<HandleSetErr> and C<HandleError> subroutines differ in subtle
but significant ways. HandleError is only invoked at the point where
the DBI is about to return to the application with C<err> set true.
It's not invoked by the failure of a method that's been called by
another DBI method. HandleSetErr, on the other hand, is called
whenever set_err() is called with a defined C<err> value, even if false.
So it's not just for errors, despite the name, but also warn and info states.
The set_err() method, and thus HandleSetErr, may be called multiple
times within a method and is usually invoked from deep within driver code.
In theory a driver can use the return value from HandleSetErr via
set_err() to decide whether to continue or not. If set_err() returns
an empty list, indicating that the HandleSetErr code has 'handled'
the 'error', the driver could then continue instead of failing (if
that's a reasonable thing to do). This isn't excepted to be
common and any such cases should be clearly marked in the driver
documentation and discussed on the dbi-dev mailing list.
The C<HandleSetErr> attribute was added in DBI 1.41.
=item C<ErrCount> (unsigned integer)
The C<ErrCount> attribute is incremented whenever the set_err()
method records an error. It isn't incremented by warnings or
information states. It is not reset by the DBI at any time.
The C<ErrCount> attribute was added in DBI 1.41. Older drivers may
not have been updated to use set_err() to record errors and so this
attribute may not be incremented when using them.
=item C<ShowErrorStatement> (boolean, inherited)
The C<ShowErrorStatement> attribute can be used to cause the relevant
Statement text to be appended to the error messages generated by
the C<RaiseError>, C<PrintError>, and C<PrintWarn> attributes.
Only applies to errors on statement handles
plus the prepare(), do(), and the various C<select*()> database handle methods.
(The exact format of the appended text is subject to change.)
If C<$h-E<gt>{ParamValues}> returns a hash reference of parameter
(placeholder) values then those are formatted and appended to the
end of the Statement text in the error message.
=item C<TraceLevel> (integer, inherited)
The C<TraceLevel> attribute can be used as an alternative to the
L</trace> method to set the DBI trace level and trace flags for a
specific handle. See L</TRACING> for more details.
The C<TraceLevel> attribute is especially useful combined with
C<local> to alter the trace settings for just a single block of code.
=item C<FetchHashKeyName> (string, inherited)
The C<FetchHashKeyName> attribute is used to specify whether the fetchrow_hashref()
method should perform case conversion on the field names used for
the hash keys. For historical reasons it defaults to 'C<NAME>' but
it is recommended to set it to 'C<NAME_lc>' (convert to lower case)
or 'C<NAME_uc>' (convert to upper case) according to your preference.
It can only be set for driver and database handles. For statement
handles the value is frozen when prepare() is called.
=item C<ChopBlanks> (boolean, inherited)
The C<ChopBlanks> attribute can be used to control the trimming of trailing space
characters from fixed width character (CHAR) fields. No other field
types are affected, even where field values have trailing spaces.
The default is false (although it is possible that the default may change).
Applications that need specific behaviour should set the attribute as
needed.
Drivers are not required to support this attribute, but any driver which
does not support it must arrange to return C<undef> as the attribute value.
=item C<LongReadLen> (unsigned integer, inherited)
The C<LongReadLen> attribute may be used to control the maximum
length of 'long' type fields (LONG, BLOB, CLOB, MEMO, etc.) which the driver will
read from the database automatically when it fetches each row of data.
The C<LongReadLen> attribute only relates to fetching and reading
long values; it is not involved in inserting or updating them.
A value of 0 means not to automatically fetch any long data.
B<Data Types for Placeholders>
The C<\%attr> parameter can be used to hint at the data type the
placeholder should have. Typically, the driver is only interested in
knowing if the placeholder should be bound as a number or a string.
$sth->bind_param(1, $value, { TYPE => SQL_INTEGER });
As a short-cut for the common case, the data type can be passed
directly, in place of the C<\%attr> hash reference. This example is
equivalent to the one above:
$sth->bind_param(1, $value, SQL_INTEGER);
The C<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 C<{ ora_type =E<gt> 97 }>.
The SQL_INTEGER and other related constants can be imported using
use DBI qw(:sql_types);
See L</"DBI Constants"> for more information.
The data type for a placeholder cannot be changed after the first
C<bind_param> call. In fact the whole \%attr parameter is 'sticky'
in the sense that a driver only needs to consider the \%attr parameter
for the first call, for a given $sth and parameter. After that the driver
may ignore the \%attr parameter for that placeholder.
Perl only has string and number scalar data types. All database types
that aren't numbers are bound as strings and must be in a format the
database will understand except where the bind_param() TYPE attribute
specifies a type that implies a particular format. For example, given:
$sth->bind_param(1, $value, SQL_DATETIME);
the driver should expect $value to be in the ODBC standard SQL_DATETIME
format, which is 'YYYY-MM-DD HH:MM:SS'. Similarly for SQL_DATE, SQL_TIME etc.
As an alternative to specifying the data type in the C<bind_param> call,
you can let the driver pass the value as the default type (C<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 C<CONVERT> function used here is just an example. The actual function
and syntax will vary between different databases and is non-portable.
See also L</"Placeholders and Bind Values"> for more information.
=item C<bind_param_inout>
$rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len) or die $sth->errstr;
$rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, \%attr) or ...
$rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, $bind_type) or ...
This method acts like L</bind_param>, but also enables values to be
updated by the statement. The statement is typically
a call to a stored procedure. The C<$bind_value> must be passed as a
reference to the actual value to be used.
Note that unlike L</bind_param>, the C<$bind_value> variable is not
copied when C<bind_param_inout> is called. Instead, the value in the
variable is read at the time L</execute> is called.
The additional C<$max_len> parameter specifies the minimum amount of
memory to allocate to C<$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.
Undefined values or C<undef> are used to indicate null values.
See also L</"Placeholders and Bind Values"> for more information.
=item C<bind_param_array>
$rc = $sth->bind_param_array($p_num, $array_ref_or_value)
$rc = $sth->bind_param_array($p_num, $array_ref_or_value, \%attr)
$rc = $sth->bind_param_array($p_num, $array_ref_or_value, $bind_type)
The C<bind_param_array> method is used to bind an array of values
to a placeholder embedded in the prepared statement which is to be executed
with L</execute_array>. For example:
$dbh->{RaiseError} = 1; # save having to check each method call
$sth = $dbh->prepare("INSERT INTO staff (first_name, last_name, dept) VALUES(?, ?, ?)");
$sth->bind_param_array(1, [ 'John', 'Mary', 'Tim' ]);
$sth->bind_param_array(2, [ 'Booth', 'Todd', 'Robinson' ]);
$sth->bind_param_array(3, "SALES"); # scalar will be reused for each row
$sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );
The C<%attr> ($bind_type) argument is the same as defined for L</bind_param>.
Refer to L</bind_param> for general details on using placeholders.
(Note that bind_param_array() can I<not> be used to expand a
placeholder into a list of values for a statement like "SELECT foo
WHERE bar IN (?)". A placeholder can only ever represent one value
per execution.)
Scalar values, including C<undef>, may also be bound by
C<bind_param_array>. In which case the same value will be used for each
L</execute> call. Driver-specific implementations may behave
differently, e.g., when binding to a stored procedure call, some
databases may permit mixing scalars and arrays as arguments.
The default implementation provided by DBI (for drivers that have
not implemented array binding) is to iteratively call L</execute> for
each parameter tuple provided in the bound arrays. Drivers may
provide more optimized implementations using whatever bulk operation
support the database API provides. The default driver behaviour should
match the default DBI behaviour, but always consult your driver
documentation as there may be driver specific issues to consider.
Note that the default implementation currently only supports non-data
returning statements (INSERT, UPDATE, but not SELECT). Also,
C<bind_param_array> and L</bind_param> cannot be mixed in the same
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 C<finish> method can be used to tell
the server that the buffer space can be freed.
Calling C<finish> resets the L</Active> attribute for the statement. It
may also make some statement handle attributes (such as C<NAME> and C<TYPE>)
unavailable if they have not already been accessed (and thus cached).
The C<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.
See also L</disconnect> and the L</Active> attribute.
The C<finish> method should have been called C<discard_pending_rows>.
=item C<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 I<non>-C<SELECT>
C<execute> (for some specific operations like C<UPDATE> and C<DELETE>), or
after fetching all the rows of a C<SELECT> statement.
For C<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
C<rows> method or C<$DBI::rows> with C<SELECT> statements is not
recommended.
One alternative method to get a row count for a C<SELECT> is to execute a
"SELECT COUNT(*) FROM ..." SQL statement with the same "..." as your
query and then fetch the row count from that.
=item C<bind_col>
$rc = $sth->bind_col($column_number, \$var_to_bind);
$rc = $sth->bind_col($column_number, \$var_to_bind, \%attr );
$rc = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
Binds a Perl variable and/or some attributes to an output column
(field) of a C<SELECT> statement. Column numbers count up from 1.
You do not need to bind output columns in order to fetch data.
For maximum portability between drivers, bind_col() should be called
after execute() and not before.
See also C<bind_columns> for an example.
The binding is performed at a low level using Perl aliasing.
Whenever a row is fetched from the database $var_to_bind appears
to be automatically updated simply because it refers to the same
memory location as the corresponding column value. This makes using
bound variables very efficient. Multiple variables can be bound
to a single column, but there's rarely any point. Binding a tied
variable doesn't work, currently.
The L</bind_param> method
performs a similar, but opposite, function for input variables.
B<Data Types for Column Binding>
The C<\%attr> parameter can be used to hint at the data type
formatting the column should have. For example, you can use:
$sth->bind_col(1, undef, { TYPE => SQL_DATETIME });
to specify that you'd like the column (which presumably is some
kind of datetime type) to be returned in the standard format for
SQL_DATETIME, which is 'YYYY-MM-DD HH:MM:SS', rather than the
native formatting the database would normally use.
There's no $var_to_bind in that example to emphasize the point
that bind_col() works on the underlying column value and not just
a particular bound variable.
As a short-cut for the common case, the data type can be passed
directly, in place of the C<\%attr> hash reference. This example is
equivalent to the one above:
$sth->bind_col(1, undef, SQL_DATETIME);
The C<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 C<{ ora_type =E<gt> 97 }>.
The SQL_DATETIME and other related constants can be imported using
use DBI qw(:sql_types);
See L</"DBI Constants"> for more information.
The data type for a bind variable cannot be changed after the first
C<bind_col> call. In fact the whole \%attr parameter is 'sticky'
in the sense that a driver only needs to consider the \%attr parameter
for the first call for a given $sth and column.
The TYPE attribute for bind_col() was first specified in DBI 1.41.
=item C<bind_columns>
$rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
Calls L</bind_col> for each column of the C<SELECT> statement.
The C<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() and not before.
For example:
( run in 0.665 second using v1.01-cache-2.11-cpan-fe3c2283af0 )