Team LiB
Previous Section Next Section

DBI Attributes

DBI provides attribute information at several levels. Most attributes are associated with database handles or statement handles, but not with both. Some attributes, such as PrintError and RaiseError, may be associated with either database handles or statement handles. In general, each handle has its own attributes, but some attributes that hold error information, such as err and errstr, are dynamic in that they associate with the most recently used handle.

Attributes passed to connect() or connect_cached() become part of the resulting database handle returned by those methods.

Database Handle Attributes

The attributes in this section are associated with database handles.

  • $dbh->{AutoCommit};

    This attribute can be set to true or false to enable or disable transaction autocommit mode. The default is true. Setting AutoCommit to false allows multiple-statement transactions to be performed, each of which is terminated by calling commit() for a successful transaction or rollback() to abort an unsuccessful transaction. See also the description of the begin_work() database handle method.

  • $dbh->{Statement};

    Holds the statement string most recently passed to prepare() tHRough the given database handle.

General Handle Attributes

These attributes may be applied to individual handles or specified in the %attr parameter to methods that take such a parameter to affect the operation of the method.

  • $h->{ChopBlanks};

    This attribute can be set to true or false to determine whether row-fetching methods will chop trailing spaces from character column values. ChopBlanks is false by default for most database drivers. This attribute has no effect for CHAR columns (and VARCHAR columns before MySQL 5.0.3), because the MySQL server does not return trailing spaces for those types anyway. However, for BLOB and TEXT columns (and VARCHAR columns as of MySQL 5.0.3), setting ChopBlanks to true causes removal of trailing spaces that would otherwise be returned.

  • $h->{FetchHashKeyName};

    Controls the lettercase used for hash keys in result set rows that are returned by fetchrow_hashref() or other methods that invoke fetchrow_hashref(). The default value is "NAME" (use column names as specified in the SELECT statement). Other allowable values are "NAME_lc" or "NAME_uc", which cause column name hash keys to be forced to lowercase or uppercase. This attribute applies only to database and driver handles.

  • $h->{HandleError};

    This attribute is used for error processing. It can be set to a reference to a subroutine to be invoked when an error occurs, prior to the usual RaiseError and PrintError processing. If the subroutine returns true, RaiseError and PrintError processing is skipped, otherwise it is performed as usual. (The error routine can of course terminate the script rather than returning.)

    DBI passes three arguments to the error routine: The text of the error message, the DBI handle being used at the point of occurrence of the error, and the first value returned by the method that failed.

  • $h->{PrintError};

    If set true, the occurrence of a DBI-related error causes a warning message to be printed. PrintError is false by default. This attribute does not affect the value returned by DBI methods when they fail. It determines only whether they print a message before returning.

  • $h->{RaiseError};

    If set to true, the occurrence of a DBI-related error causes an exception to be raised. Normally this causes the script to terminate unless it arranges to catch the exception. RaiseError is false by default.

  • $h->{ShowErrorStatement};

    When set to true, messages produced as a result of errors have the relevant statement text appended to them. ShowErrorStatement is false by default. The effect of this attribute is limited to statement handles and to the prepare() and do() methods.

  • $h->{traceLevel};

    Sets or gets the trace level for the given handle. This attribute provides an alternative to the trace() method.

MySQL-Specific Database Handle Attributes

These attributes are specific to the DBI MySQL driver, DBD::mysql. Most of them correspond to a function in the MySQL C API, as indicated in the attribute descriptions. See Appendix G, "C API Reference," for more information about the C functions.

  • $rv = $dbh->{mysql_auto_reconnect};

    Whether the driver automatically reconnects to the server after the connection goes down. Normally, auto-reconnect is disabled by default, but will be enabled if the GATEWAY_INTERFACE or MOD_PERL environment variables are set. The mysql_auto_reconnect setting is ignored and no reconnects are attempted if AutoCommit is disabled.

    mysql_auto_reconnect was introduced in DBD::mysql 2.9002.

  • $hash_ref = $dbh->{mysql_dbd_stats};

    Returns a hash reference containing driver statistics. Currently this hash has two keys, auto_reconnects_ok and auto_reconnects_failed, indicating the number of times the driver tried successfully and unsuccessfully to reconnect to the server.

    mysql_dbd_stats was introduced in DBD::mysql 2.9002.

  • $rv = $dbh->{mysql_errno};

    Returns the most recent error number, like the C API function mysql_errno().

  • $str = $dbh->{mysql_error};

    Returns the most recent error string, like the C API function mysql_error().

  • $str = $dbh->{mysql_hostinfo};

    Returns a string describing the given connection, like the C API function mysql_get_host_info().

  • $str = $dbh->{mysql_info};

    Returns information about statements that affect multiple rows, like the C API function mysql_info().

  • $rv = $dbh->{mysql_insertid};

    Returns the AUTO_INCREMENT value that was most recently generated on the connection associated with $dbh, like the C API function mysql_insert_id().

    This attribute also may be used with statement handles.

  • $rv = $dbh->{mysql_protoinfo};

    Returns a number indicating the client/server protocol version used for the given connection, like the C API function mysql_get_proto_info().

  • $rv = $dbh->{mysql_server_prepare};

    Returns true if server-side statement preparation is enabled, false if statement preparation is emulated on the client side.

    You can assign to this attribute to enable or disable server-side prepared statement execution for statement handles created from $dbh:

    $dbh->{mysql_server_prepare} = 1; # enable server-side preparation
$dbh->{mysql_server_prepare} = 0; # disable server-side preparation

    For information about the versions of MySQL and DBD::mysql needed to use the mysql_server_prepare attribute, see the introduction to this appendix.

  • $str = $dbh->{mysql_serverinfo};

    Returns a string describing the server version, for example, "4.1.4-gamma-log". The value consists of a version number, possibly followed by one or more suffixes. This attribute returns the same information as the C API function mysql_get_server_info(). The suffix values are listed in the description of the VERSION() function in Appendix C, "Operator and Function Reference."

  • $str = $dbh->{mysql_stat};

    Returns a string containing server status information, like the C API function mysql_stat().

  • $rv = $dbh->{mysql_thread_id};

    Returns the thread number of the connection associated with $dbh, like the C API function mysql_thread_id().

As of DBD::mysql 2.0900, several MySQL-specific database handle attributes have been designated obsolete and should no longer be used. Table H.2 lists these together with the corresponding preferred attributes. If your version of DBD::mysql does not support the preferred attributes, try the deprecated forms instead, or upgrade to a newer version. It's also possible to write code that uses the newer version of an attribute if it's available and falls back to the older version otherwise. For example:

my $info = $dbh->{mysql_info};
# use old form if new form unavailable
$info = $dbh->{info} unless defined ($info);

Table H.2. Deprecated MySQL-Specific Database Handle Attributes

Deprecated Attribute

Preferred Attribute

errno

mysql_errno

error

mysql_error

hostinfo

mysql_hostinfo

info

mysql_info

protoinfo

mysql_protoinfo

serverinfo

mysql_serverinfo

stats

mysql_stat

thread_id

mysql_thread_id


Statement Handle Attributes

Statement handle attributes generally apply to SELECT (or SELECT-like) statements and are not valid until the statement has been passed to prepare() to obtain a statement handle and execute() has been called for that handle. In addition, finish() may invalidate statement attributes; in general, it is not safe to access them after finish() has been invoked (or after reaching the end of a result set, which causes finish() to be invoked implicitly).

Many statement handle attributes have a value that is a reference to an array of values, one value per column selected by the statement. The number of elements in the array is given by the $sth->{NUM_OF_FIELDS} attribute. For a statement attribute stmt_attr that is a reference to an array, you can refer to the entire array as @{$sth->{stmt_attr}}, or loop through the elements in the array like this:

for (my $i = 0; $i < $sth->{NUM_OF_FIELDS}; $i++)
{
    my $value = $sth->{stmt_attr}->[$i];
}

The NAME_hash, NAME_lc_hash, and NAME_uc_hash attributes return a reference to a hash. You can loop through the hash elements like this:

foreach my $key (keys (%{$sth->{stmt_attr}}))
{
    my $value = $sth->{stmt_attr}->{$key};
}

  • $ary_ref = $sth->{NAME};

    A reference to an array of strings indicating the name of each column. The lettercase of the names is as specified in the SELECT statement.

  • $ary_ref = $sth->{NAME_hash};

    A reference to a hash of strings indicating the name of each column. The lettercase of the names is as specified in the SELECT statement. The value of each hash element indicates the position of the corresponding column within result set rows (beginning with 0).

  • $ary_ref = $sth->{NAME_lc};

    Like NAME, but the names are returned as lowercase strings.

  • $ary_ref = $sth->{NAME_lc_hash};

    Like NAME_hash, but the names are returned as lowercase strings.

  • $ary_ref = $sth->{NAME_uc};

    Like NAME, but the names are returned as uppercase strings.

  • $ary_ref = $sth->{NAME_uc_hash};

    Like NAME_hash, but the names are returned as uppercase strings.

  • $ary_ref = $sth->{NULLABLE};

    A reference to an array of values indicating whether each column can be NULL. Values for each element can be 0 or an empty string (no), 1 (yes), or 2 (unknown).

  • $rv = $sth->{NUM_OF_FIELDS};

    The number of columns in a result set, or zero for a non-SELECT statement.

  • $rv = $sth->{NUM_OF_PARAMS};

    The number of placeholders in a prepared statement.

  • $ary_ref = $sth->{PRECISION};

    A reference to an array of values indicating the precision of each column. DBI uses "precision" in the ODBC sense, which for MySQL means the maximum width of the column. For numeric columns, this is the display width. For string columns, it's the maximum length of the column, in octets; that is, PRECISION is a length in bytes, not characters.

  • $ary_ref = $sth->{SCALE};

    A reference to an array of values indicating the scale of each column. DBI uses "scale" in the ODBC sense, which for MySQL means the number of decimal places for floating-point columns. For other columns where scale is not applicable, the scale is undef.

  • $str = $sth->{Statement};

    The text of the statement associated with $sth, as seen by prepare() before any placeholder substitution takes place.

  • $ary_ref = $sth->{TYPE};

    A reference to an array of values indicating the numeric type of each column. This attribute contains portable type numbers. The mysql_type attribute may be accessed to obtain MySQL-specific type numbers.

MySQL-Specific Statement Handle Attributes

These attributes are specific to the DBI MySQL driver, DBD::mysql. Most of them should be considered read-only and should be accessed after invoking execute(). Exceptions are the mysql_store_result and mysql_use_result attributes. DBD::mysql provides the capability for controlling the result set processing style used by your script. The statement handle attributes mysql_store_result and mysql_use_result select the result set processing behavior of the C API functions mysql_store_result() and mysql_use_result(). See Appendix G, "C API Reference," for a discussion of these two functions and how they differ. By default, DBI uses mysql_store_result(), but you can enable the mysql_use_result attribute, which tells DBI to use mysql_use_result() instead. Do this after prepare() but before execute():

$sth = $dbh->prepare (...);
$sth->{mysql_use_result} = 1;
$sth->execute();

Alternatively, do this:

$sth = $dbh->prepare ($stmt_str, { mysql_use_result => 1 });

  • $rv = $sth->{mysql_insertid};

    The AUTO_INCREMENT value that was most recently generated on the connection associated with $sth.

    This attribute also may be used with database handles.

  • $ary_ref = $sth->{mysql_is_auto_increment};

    A reference to an array of values indicating whether each column is an AUTO_INCREMENT column.

    This attribute may be used as of DBD::mysql 2.1014.

  • $ary_ref = $sth->{mysql_is_blob};

    A reference to an array of values indicating whether each column is a BLOB or TEXT type.

  • $ary_ref = $sth->{mysql_is_key};

    A reference to an array of values indicating whether each column is part of a key.

  • $ary_ref = $sth->{mysql_is_num};

    A reference to an array of values indicating whether each column is a numeric type.

  • $ary_ref = $sth->{mysql_is_pri_key};

    A reference to an array of values indicating whether each column is part of a PRIMARY KEY.

  • $ary_ref = $sth->{mysql_length};

    This is like the PRECISION attribute.

  • $ary_ref = $sth->{mysql_max_length};

    A reference to an array of values indicating the actual maximum length of the values in each column of the result set.

  • $sth->{mysql_server_prepare};

    Returns true if server-side statement preparation is enabled, false if statement preparation is emulated on the client side.

    For information about the versions of MySQL and DBD::mysql needed to use the mysql_server_prepare attribute, see the introduction to this appendix.

  • $sth->{mysql_store_result};

    If mysql_store_result is enabled (set to 1), result sets are retrieved from the MySQL server using the mysql_store_result() C API function rather than by using mysql_use_result(). See Appendix G for a discussion of these two functions and how they differ.

    If you set the mysql_store_result attribute, do so after invoking prepare() and before invoking execute().

  • $ary_ref = $sth->{mysql_table};

    A reference to an array of values indicating the name of the table from which each column comes. The table name for a calculated column is the empty string.

  • $ary_ref = $sth->{mysql_type};

    A reference to an array of values indicating the MySQL-specific type number for each column in the result set.

  • $ary_ref = $sth->{mysql_type_name};

    A reference to an array of values indicating the MySQL-specific type name for each column in the result set.

  • $sth->{mysql_use_result};

    If mysql_use_result is enabled (set to 1), result sets are retrieved from the MySQL server using the mysql_use_result() C API function rather than by using mysql_store_result(). See Appendix G for a discussion of these two functions and how they differ.

    Note that use of this attribute causes some attributes such as mysql_max_length to become invalid. It also invalidates the use of the rows() method, although it's better to count rows when you fetch them anyway.

    If you set the mysql_use_result attribute, do so after invoking prepare() and before invoking execute().

Several MySQL-specific attributes that were available in older versions of DBD::mysql now are deprecated and have been replaced by newer preferred forms, as indicated in Table H.3. If your version of DBD::mysql does not support the preferred attributes, try the deprecated forms instead, or upgrade to a newer version. It's also possible to write code that uses the newer version of an attribute if it's available and falls back to the older version otherwise. For example:

my $lengths = $sth->{PRECISION};
# use old form if new form unavailable
$lengths = $sth->{length} unless defined ($lengths);

Table H.3. Deprecated MySQL-Specific Statement Handle Attributes

Deprecated Attribute

Preferred Attribute

insertid

mysql_insertid

is_blob

mysql_is_blob

is_key

mysql_is_key

is_not_null

NULLABLE

is_num

mysql_is_num

is_pri_key

mysql_is_pri_key

length

PRECISION

max_length

mysql_max_length

table

mysql_table


Note that insertid is a statement handle attribute, whereas its preferred form, mysql_insertid, can be used as either a database or statement handle attribute.

Dynamic Attributes

These attributes are associated with the most recently used handle, represented by $h in the following descriptions. They should be used immediately after invoking whatever handle method sets them, and before invoking another method that resets them.

  • $rv = $DBI::err;

    This is the same as calling $h->err().

  • $str = $DBI::errstr;

    This is the same as calling $h->errstr().

  • $rows = $DBI::rows;

    This is the same as calling $h->rows().

    Team LiB
    Previous Section Next Section