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, can 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 auto-commit 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.

  If MySQL does not support transactions, attempting to disable AutoCommit results in a fatal error.

  Transaction support requires DBD::mysql 1.2216 or later and MySQL 3.23.17 or later.

• $dbh->{Statement};

  Holds the query string most recently passed to prepare() through the given database handle.

General Handle Attributes

The following attributes can 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 or not 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 or VARCHAR columns, because the MySQL server does not return trailing spaces for those types anyway. However, for BLOB and TEXT columns, setting ChopBlanks to true will cause removal of trailing spaces.

• $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.

  FetchHashKeyName was introduced in DBI 1.19.

• $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. (Of course, the error routine can 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.

  HandleError was introduced in DBI 1.21.

• $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 true, the occurrence of a DBI-related error causes automatic script termination. RaiseError is false by default.

• $h->{ShowErrorStatement};

  When set true, messages produced as a result of errors have the relevant query 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.

  ShowErrorStatement was introduced in DBI 1.19.

• $h->{TraceLevel};

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

  TraceLevel was introduced in DBI 1.21.

MySQL-Specific Database Handle Attributes

The attributes in this section are specific to the DBI MySQL driver, DBD::mysql. As indicated in the attribute descriptions, each of them corresponds to a function in the MySQL C API. For more information, see Appendix F.

• $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 queries 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 can also 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().

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

  Returns a string describing the server version, for example, "4.0.2-alpha-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 G.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);
  

Statement Handle Attributes

Statement handle attributes generally apply to SELECT (or SELECT-like) queries and are not valid until the query 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 values that are a reference to an array of values, one value per column of the query. 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 as follows:

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 as follows:

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).

  NAME_hash was introduced in DBI 1.20.

• $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.

  NAME_lc_hash was introduced in DBI 1.20.

• $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.

  NAME_uc_hash was introduced in DBI 1.20.

• $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 as defined in the CREATE TABLE statement.

• $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, the scale is 0.

• $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. Prior to DBD::mysql 1.1919, this attribute is like mysql_type. As of 1.1919, it contains portable type numbers, and mysql_type can 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 F 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 the following after prepare() but before execute():

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

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

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

  This attribute can also be used with database handles.

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

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

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

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

  A reference to an array of values indicating whether or not 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 column values in the result set.

• $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 F 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 F 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 are now deprecated and have been replaced by newer preferred forms, as indicated in Table G.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);
  

  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

The following 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().