DBI Methods
The method descriptions here are written in a somewhat different format than is used for the C functions in Appendix F, "C API Reference," and for the PHP functions in Appendix H, "PHP API Reference." Functions in those appendixes are written in prototype form, with return value types and parameter types listed explicitly. The descriptions here indicate parameter and return value types using variables, where the leading character of each variable indicates its type: '$' for a scalar, '@' for an array, and '%' for a hash (associative array). In addition, any parameter listed with a leading '\' signifies a reference to a variable of the given type, not the variable itself. A variable name suffix of ref indicates that the variable's value is a reference.
Certain variable names recur throughout this appendix and have the conventional meanings shown in Table G.1.
| Name | Meaning |
|---|---|
| $drh | A handle to a driver object |
| $dbh | A handle to a database object |
| $sth | A handle to a statement (query) object |
| $fh | A handle to an open file |
| $h | A "generic" handle; the meaning depends on context |
| $rc | The return code from operations that return true or false |
| $rv | The return value from operations that return an integer |
| $rows | The return value from operations that return a row count |
| $str | The return value from operations that return a string |
| @ary | An array representing a list of values |
| @row_ary | An array representing a row of values returned by a query |
Many methods accept a hash argument %attr containing attributes that affect the way the method works. This hash should be passed by reference, which you can do two ways:
Set up the contents of the hash value %attr before invoking the method and then pass it to the method:
my %attr = (AttrName1 => value1, AttrName2 => value2); $ret_val = $h->method (..., \%attr);
Supply an anonymous hash directly in the method invocation:
$ret_val = $h->method (..., {AttrName1 => value1, AttrName2 => value2});
The way in which a method or function is used is indicated by the calling sequence. DBI-> indicates a DBI class method, DBI:: indicates a DBI function, and $DBI:: indicates a DBI variable. For methods that are called using handles, the handle name indicates the scope of the method. $dbh-> indicates a database handle method, $sth-> indicates a statement handle method, and $h-> indicates a method that can be called with different kinds of handles. Optional information is indicated by square brackets ([]). The following is an example calling sequence:
@row_ary = $dbh->selectrow_array ($statement, [\%attr [, @bind_values]]);
This indicates that the selectrow_array() method is called as a database handle method because it's invoked using $dbh->. The parameters are $statement (a scalar value), %attr (a hash that should be passed as a reference, as indicated by the leading '\'), and @bind_values (an array). The second and third parameters are optional. The return value, @row_ary, is an array representing the row of values returned by the method.
Each method description indicates what the return value is when an error occurs, but that value is returned on error only if the RaiseError attribute is disabled. If RaiseError is enabled, the method raises an exception rather than returning and the script automatically terminates.
In the descriptions that follow, the term "SELECT query" should be taken to mean a SELECT query or any other query that returns rows, such as DESCRIBE, EXPLAIN, or SHOW.
DBI Class Methods
The %attr parameter for methods in this section can be used to specify method-processing attributes. (An attribute parameter that is missing or undef means "no attributes.") For MySQL, the most important attributes are PrintError, RaiseError, and AutoCommit. Attributes passed to connect() or connect_cached() become part of the resulting database handle returned by those methods. For example, to turn on automatic script termination when a DBI error occurs within any method associated with a given database handle, enable RaiseError when you create the handle:
$dbh = DBI->connect ($data_source, $user_name, $password, {RaiseError => 1});
PrintError, RaiseError, and AutoCommit are discussed in the "DBI Attributes" section later in this appendix.
@ary = DBI->available_drivers ([$quiet]);
Returns a list of available DBI drivers. The default value of the optional $quiet parameter is 0, which causes a warning to be issued if multiple drivers with the same name are found. To suppress the warning, pass a $quiet value of 1.
$dbh = DBI->connect ($data_source, $user_name, $password [, \%attr]);connect() establishes a connection to a database server and returns a database handle or undef if the connection attempt fails. To terminate a successfully established connection, invoke disconnect() using the database handle returned by connect().
$dbh = DBI->connect ("DBI:mysql:sampdb:cobra.snake.net", "sampadm", "secret", \%attr) or die "Could not connect\n"; $dbh->disconnect ();The data source can be given in several forms. The first part is always DBI:mysql:, where DBI can be given in any lettercase and the driver name, mysql, must be lowercase. Everything after the second colon (which must be present) is interpreted by the driver, so the syntax described in the following discussion does not necessarily apply to any driver other than DBD::mysql.
Following the second colon, you can also specify a database name and hostname in the initial part of the data source string:
$data_source = "DBI:mysql:db_name"; $data_source = "DBI:mysql:db_name:host_name";
The database can be specified as db_name or as database=db_name., The hostname can be specified as host_name or as host=host_name.
Following the initial part of the data source string, you can specify several options in attribute=value format. Each option setting should be preceded by a semicolon. For example
DBI:mysql:sampdb:localhost;mysql_socket=/tmp/mysql.sock;mysql_compression=1
The MySQL driver understands the following options:
host=host_name
The host to which to connect. For TCP/IP connections, a port number can also be specified by using host_name:port_num format or by using the port attribute.
On UNIX systems, connections to the host localhost use UNIX domain sockets by default. (In this case, you can use mysql_socket to specify the socket name.) Use host=127.0.0.1 if you want to connect to the local host using TCP/IP.
On Windows NT-based systems, connections to the host '.' connect to the local server using a named pipe or TCP/IP if that doesn't work. (In this case, you can use mysql_socket to specify the pipe name.)
port=port_num
The port number to which to connect. This option is ignored for non-TCP/IP connections (for example, connections to localhost under UNIX).
mysql_client_found_rows=val
For UPDATE queries, the MySQL server by default returns the number of rows affected (changed) rather than the number of rows matched. Passing a value of 1 for the mysql_client_found_rows option tells the server to return the number of rows matched instead. A value of 0 leaves the default behavior unchanged.
mysql_client_found_rows was introduced in DBD::mysql 1.2208.
mysql_compression=1
This option enables compressed communication between the client and the MySQL server.
mysql_compression was introduced in DBD::mysql 1.1920 and requires MySQL 3.22.3 or later.
mysql_connect_timeout=seconds
The number of seconds to wait during the connection attempt before timing out and returning failure.
mysql_connect_timeout was introduced in DBD::mysql 1.2207.
mysql_local_infile=val
This option may be used as of MySQL 3.23.49 to control availability of the LOCAL capability for the LOAD DATA statement. Setting the option to 1 enables LOCAL if it is disabled in the MySQL client library by default (as long as the server has not also been configured to disallow it). Setting the option to 0 disables LOCAL if it is enabled in the client library.
mysql_local_infile was introduced in DBD::mysql 2.1020.
mysql_read_default_file=file_name
By default, DBI scripts do not check any MySQL option files for connection parameters. mysql_read_default_file allows you to specify an option file to read. The filename should be a full pathname. (Otherwise, it will be interpreted relative to the current directory, and you will get inconsistent results depending on where the script is run.)
If you expect that a script will be used by multiple users and you want each of them to connect using parameters specified in their own option file (rather than using parameters that you hardwire into the script), specify the filename as $ENV{HOME}/.my.cnf. The script then will use the .my.cnf file in the home directory of whatever user happens to be running the script.
Specifying an option filename that includes a drive letter doesn't work under Windows because the ':' character that separates the drive letter and the following pathname is also used by DBI as a separator within the data source string. An ugly way to work around this is to specify the filename without the drive letter and chdir() to that drive before invoking connect(). To leave the current directory undisturbed by the operation, you should save the current directory before calling connect() and then chdir() back to it after connecting.
mysql_read_default_file was introduced in DBD::mysql 1.2106, and requires MySQL 3.22.10 or later.
mysql_read_default_group=group_name
Specify an option file group in which to look for connection parameters. If mysql_read_default_group is used without mysql_read_default_file, the standard option files are read. If both mysql_read_default_group and mysql_read_default_file are used, only the file named by the latter is read.
The [client] option file group is always read from option files. mysql_read_default_group allows you to specify a group to be read in addition to the [client] group. For example, mysql_read_default_group=dbi specifies that both the [dbi] and [client] groups should be used. If you only want the [client] group to be read, use mysql_read_default_group=client.
The format of option files is described in Appendix E, "MySQL Program Reference."
mysql_read_default_group was introduced in DBD::mysql 1.2106 and requires MySQL 3.22.10 or later.
mysql_socket=socket_name
Under UNIX, this option specifies the pathname of the UNIX domain socket to use for connections to localhost. Under Windows, it indicates a named pipe name. This option is ignored for TCP/IP connections (for example, connections to hosts other than localhost on UNIX).
mysql_socket requires MySQL 3.21.15 or later.
mysql_ssl=1 mysql_ssl_ca_file=file_name mysql_ssl_ca_path=dir_name mysql_ssl_cipher=str mysql_ssl_client_cert=file_name mysql_ssl_client_key=file_name
These options are used to establish a secure connection to the server using SSL. Setting mysql_ssl to 1 enables use of SSL. The other options then may be used to specify the other characteristics of the connection; their meanings are the same as the corresponding arguments of the mysql_ssl_set() function in the C API. For details, see the entry for that function in Appendix F. If you enable mysql_ssl, you should also specify values for at least the mysql_ssl_ca_file, mysql_ssl_client_cert, and mysql_ssl_client_key options.
These options require MySQL 4 or later and may be used as of DBD::mysql 2.1013.
If connection parameters are not specified explicitly in the arguments to connect(), DBI examines several environment variables to determine which parameters to use:
If the data source is undefined or empty, the value of the DBI_DSN variable is used.
If the driver name is missing from the data source, the value of the DBI_DRIVER variable is used.
If the user_name or password parameters of the connect() call are undefined, the values of the DBI_USER and DBI_PASS variables are used. This does not occur if the parameters are empty strings. (Use of DBI_PASS is a security risk, so you shouldn't use it on multiple-user systems where environment variable values may be visible to other users by means of system-monitoring commands.)
DBI uses default values for any connection parameters that remain unknown after all information sources have been consulted. If the hostname is unspecified, it defaults to localhost. If the username is unspecified, it defaults to your login name under UNIX and to ODBC under Windows. If the password is unspecified, there is no default; instead, no password is sent.
$dbh = DBI->connect_cached ($data_source, $user_name, $password [, \%attr]);This method is like connect() except that DBI caches the database handle internally. If a subsequent call is made to connect_cached() with the same connection parameters while the connection is still active, DBI returns the cached handle rather than opening a new connection. If the cached handle is no longer valid, DBI establishes a new connection and then caches and returns the new handle.
@ary = DBI->data_sources ($driver [, \%attr]);
Returns a list of databases available through the named driver. For MySQL, $driver should be mysql (lowercase). If $driver is undef or the empty string, DBI checks the value of the DBI_DRIVER environment variable to get the driver name.
For many DBI drivers, data_sources() returns an empty or incomplete list. For MySQL in particular, this function returns nothing unless the driver can connect to the server on the local host with no name or password. This is unlikely to be true except for insecure server configurations.
$drh = DBI->install_driver ($driver_name);
Activates a DBD-level driver and returns a driver handle for it or dies with an error message if the driver cannot be found. For MySQL, the driver name is mysql (it must be lowercase). Normally, it is not necessary to use this method because DBI activates the proper driver automatically when you invoke the connect() method. However, install_driver() can be helpful if you're using the func() method to perform administrative operations. (See the "MySQL-Specific Administrative Methods" section later in this appendix.)
Database Handle Methods
The methods in this section are invoked through a database handle and can be used after you have obtained such a handle by calling the connect() or connect_cached() method.
The %attr parameter for methods in this section can be used to specify method-processing attributes. (An attribute parameter of undef means "no attributes.") For MySQL, the most important of these are PrintError and RaiseError. For example, if RaiseError currently is disabled, you can enable it while processing a particular statement to cause automatic script termination if a DBI error occurs:
$rows = $dbh->do ($statement, {RaiseError => 1});
PrintError and RaiseError are discussed in the "DBI Attributes" section later in this appendix.
$rc = $dbh->begin_work ();
Turns off auto-commit mode by disabling the AutoCommit database handle attribute. This allows a multiple-statement transaction to be performed. AutoCommit remains disabled until the next call to commit()or rollback(), after which it becomes enabled again. Use of begin_work() differs from disabling the AutoCommit attribute manually; in the latter case, you must also re-enable AutoCommit manually after committing or rolling back.
begin_work() returns true if AutoCommit was disabled successfully, and it returns false if it was already disabled. If MySQL does not support transactions, attempting to disable AutoCommit results in a fatal error.
begin_work() was introduced in DBI 1.20. Transaction support requires DBD::mysql 1.2216 or later and MySQL 3.23.17 or later.
$rc = $dbh->commit ();
Commits the current transaction if MySQL supports transactions and AutoCommit is disabled. If AutoCommit is enabled, invoking commit() has no effect and results in a warning.
Transaction support requires DBD::mysql 1.2216 or later and MySQL 3.23.17 or later.
$rc = $dbh->disconnect ();
Terminates the connection associated with the database handle. If the connection is still active when the script exits, DBI terminates it automatically but issues a warning.
The behavior of disconnect() is undefined with respect to active transactions. If a transaction is active, you should terminate it explicitly by invoking commit() or rollback() before calling disconnect().
$rows = $dbh->do ($statement [, \%attr [, @bind_values]]);Prepares and executes the query indicated by $statement. The return value is the number of rows affected, ?1 if the number of rows is unknown, and undef if an error occurred. If the number of rows affected is zero, the return value is the string "0E0", which evaluates as zero in numeric contexts but is considered true in boolean contexts.
do() is used primarily for statements that do not retrieve rows, such as DELETE, INSERT, REPLACE, or UPDATE. Trying to use do() for a SELECT statement is ineffective; you don't get back a statement handle, so you won't be able to fetch any rows.
Normally, no attributes are passed to do(), so the %attr parameter can be specified as undef. @bind_values represents a list of values to be bound to placeholders, which are indicated by '?' characters within the query string.
If a query includes no placeholders, you can omit both the %attr parameter and the value list:
$rows = $dbh->do ("UPDATE member SET expiration = NOW() WHERE member_id = 39");If the query does contain placeholders, the list must contain as many values as there are placeholders and must be preceded by the %attr argument. In the following example, the attribute argument is undef and is followed by two data values to be bound to the two placeholders in the query string:
$rows = $dbh->do ("UPDATE member SET expiration = ? WHERE member_id = ?", undef, "2005-11-30", 39);$rc = $dbh->ping ();
Re-establishes the connection to the server if the connection has timed out. Returns true if the connection was still active or was re- established successfully and false otherwise.
$sth = $dbh->prepare ($statement [, \%attr]);
Prepares the query indicated by $statement for later execution and returns a statement handle or undef if an error occurs. The statement handle returned from a successful invocation of prepare() can be used with execute() to execute the query.
$sth = $dbh->prepare_cached ($statement [, \%attr [, $allow_active]]);This method is like prepare() except that DBI caches the statement handle internally. If a subsequent call is made to prepare_cached() with the same $statement and %attr arguments, DBI returns the cached handle rather than creating a new one. If the cached handle is active, DBI calls finish() and issues a warning before returning the handle. To suppress the warning, pass a true value for $allow_active.
$str = $dbh->quote ($value [, $data_type]);
Processes a string to perform quoting and escaping of characters that are special in SQL. The resulting string can be used as a data value in a statement without causing a syntax error when you execute the statement. For example, the string I'm happy is returned as 'I\'m happy'. If $value is undef, it is returned as the literal word NULL. Note that the return value includes surrounding quote characters as necessary, so you should not add extra quotes around it when you insert the value into a query string.
Do not use quote() with values that you are going to insert into a query using placeholders. DBI quotes such values automatically.
The $data_type parameter is usually unnecessary because MySQL converts string values in queries to other data types as necessary. $data_type can be specified as a hint that a value is of a particular type?for example, DBI::SQL_INTEGER to indicate the $value represents an integer.
$rc = $dbh->rollback ();
Rolls back the current transaction if MySQL supports transactions and AutoCommit is disabled. If AutoCommit is enabled, invoking rollback() has no effect and results in a warning.
Transaction support requires DBD::mysql 1.2216 or later and MySQL 3.23.17 or later.
$ary_ref = $dbh->selectall_arrayref ($statement [, \%attr [, @bind_values]]);Combines the effect of prepare(), execute(), and fetchall_arrayref() to execute the query specified by $statement. If $statement is a handle to a previously prepared statement, the prepare() step is omitted. The %attr and @bind_values parameters have the same meaning as for the do() method.
The return value is a reference to an array. Each array element is a reference to an array containing the values for one row of the result set. The array will be empty if the result set contains no rows.
If an error occurred, selectall_arrayref() returns undef unless a partial result set already has been fetched. In that case, it returns the rows retrieved to that point. To determine whether a non-undef return value represents success or failure, check $dbh->err() or $DBI::err.
$hash_ref = $dbh->selectall_hashref ($statement, $key_col [, \%attr [, @bind_values]]);Combines the effect of prepare(), execute(), and fetchall_hashref() to execute the query specified by $statement. If $statement is a handle to a previously prepared statement, the prepare() step is omitted. The %attr and @bind_values parameters have the same meaning as for the do() method.
The return value is a reference to a hash that contains one element for each row of the result set. Hash keys are the values of the column indicated by $key_col, which should be either the name of a column selected by the query, or a column number (beginning with 1). Values in the key column should be unique to avoid loss of rows due to key collisions in the hash. The hash will be empty if the result set contains no rows. Otherwise, the value of each hash element is a reference to a hash containing one row of the result set, keyed by the names of the columns selected by the query.
If an error occurred, selectall_hashref() returns undef unless a partial result set already has been fetched. In that case, it returns the rows retrieved to that point. To determine whether a non-undef return value represents success or failure, check $dbh->err() or $DBI::err.
selectall_hashref() was introduced in DBI 1.20. (It actually appeared in 1.15, but with different behavior than it currently has.)
$ary_ref = $dbh->selectcol_arrayref ($statement, [\%attr [, @bind_values]]);Combines the effect of prepare(), execute(), and a row-fetching operation to execute the query specified by $statement. If $statement is a handle to a previously prepared statement, the prepare() step is omitted. The %attr and @bind_values parameters have the same meaning as for the do() method.
The return value is a reference to an array containing the first column from each row.
If an error occurred, selectcol_arrayref() returns undef unless a partial result set already has been fetched. In that case, it returns the rows retrieved to that point. To determine whether a non-undef return value represents success or failure, check $dbh->err() or $DBI::err.
selectcol_arrayref() was introduced in DBI 1.09.
@row_ary = $dbh->selectrow_array ($statement [, \%attr [, @bind_values]]);Combines the effect of prepare(), execute(), and fetchrow_array() to execute the query specified by $statement. If $statement is a handle to a previously prepared statement, the prepare() step is omitted. The %attr and @bind_values parameters have the same meaning as for the do() method.
When called in a list context, selectrow_array() returns an array representing the values in the first row of the result set or an empty array if no row was returned or an error occurred. In a scalar context, selectrow_array() returns one element of the array or undef if no row was returned or if an error occurred. Which element is returned is undefined; see the note on this behavior in the entry for fetchrow_array().
To distinguish between no row and an error in list context, check $sth->err() or $DBI::err. A value of zero indicates that no row was returned. However, in the absence of an error, an undef return value in scalar context may represent either a NULL column value or that no row was returned.
$ary_ref = $dbh->selectrow_arrayref ($statement [, \%attr [, @bind_values]]);Combines the effect of prepare(), execute(), and fetchrow_arrayref() to execute the query specified by $statement. If $statement is a handle to a previously prepared statement, the prepare() step is omitted. The %attr and @bind_values parameters have the same meaning as for the do() method.
The return value is a reference to an array containing the values in the first row of the result set or undef if an error occurred.
selectrow_arrayref() was introduced in DBI 1.15.
$hash_ref = $dbh->selectrow_hashref ($statement [, \%attr [, @bind_values]]);Combines the effect of prepare(), execute(), and fetchrow_hashref() to execute the query specified by $statement. If $statement is a handle to a previously prepared statement, the prepare() step is omitted. The %attr and @bind_values parameters have the same meaning as for the do() method.
The return value is a reference to a hash containing the first row of the result set or undef if an error occurred. The hash elements are keyed by the names of the columns selected by the query.
selectrow_hashref() was introduced in DBI 1.16.
Other Database Handle Methods
A number of additional database handle methods for getting database and table metadata have appeared in recent versions of DBI. However, some of them are still experimental, and most haven't been implemented for MySQL yet. These include column_info(), foreign_key_info(), get_info(), primary_key(), primary_key_info(), table_info(), tables(), type_info(), and type_info_all(). For more information about them, consult the DBI documentation:
% perldoc DBI
Statement Handle Methods
The methods in this section are invoked through a statement handle that you obtain by calling a method such as prepare() or prepare_cached().
$rc = $sth->bind_col ($col_num, \$var);
Binds a given output column from a SELECT query to a Perl variable, which should be passed as a reference. $col_num should be in the range from 1 to the number of columns selected by the query. Each time a row is fetched, the variable is updated automatically with the column value.
bind_col() should be called after execute() and before fetching rows.
bind_col() returns false if the column number is not in the range from 1 to the number of columns selected by the query.
$rc = $sth->bind_columns (\$var1, \$var2, ...);
Binds a list of variables to columns returned by a prepared SELECT statement. See the description of the bind_col() method. Like bind_col(), bind_columns() should be called after execute() and before fetching rows.
bind_columns() returns false if the number of arguments doesn't match the number of columns selected by the query.
$rv = $sth->bind_param ($n, $value [, \%attr]); $rv = $sth->bind_param ($n, $value [, $bind_type]);
Binds a value to a placeholder in a query string so that the value will be included in the statement when it is sent to the server. Placeholders are represented by '?' characters in the query string. This method should be called after prepare() and before execute().
$n specifies the number of the placeholder to which the value $value should be bound and should be in the range from 1 to the number of placeholders. To bind a NULL value, $value should be undef.
The %attr or $bind_type parameter can be supplied as a hint about the type of the value to be bound. For example, to specify that the value represents an integer, you can invoke bind_param() in either of the following ways:
$rv = $sth->bind_param ($n, $value, { TYPE => DBI::SQL_INTEGER }); $rv = $sth->bind_param ($n, $value, DBI::SQL_INTEGER);The default is to treat the variable as a VARCHAR. This is normally sufficient because MySQL converts string values in queries to other data types as necessary.
$rows = $sth->dump_results ([$maxlen [, $line_sep [, $field_sep [, $fh]]]]);Fetches all rows from the statement handle $sth, formats them by calling the utility function DBI::neat_list(), and prints them to the given file handle. Returns the number of rows fetched.
The defaults for the $maxlen, $line_sep, $field_sep, and $fh parameters are 35, "\n", ", ", and STDOUT.
$rv = $sth->execute ([@bind_values]);
Executes a prepared statement. For SELECT statements, execute() returns true if the statement executed successfully or undef if an error occurred. For non-SELECT statements, the return value is the number of rows affected: ?1 if the number of rows is unknown, and undef if an error occurred. If the number of rows affected is zero, the return value is the string "0E0", which evaluates as zero in numeric contexts but is considered true in boolean contexts.
The @bind_values parameter has the same meaning as for the do() method.
$ary_ref = $sth->fetch ();
fetch() is an alias for fetchrow_arrayref().
$ary_ref = $sth->fetchall_arrayref ([$slice_array_ref]); $ary_ref = $sth->fetchall_arrayref ([$slice_hash_ref]);
Fetches all rows from the statement handle $sth and returns a reference to an array that contains one reference for each row fetched. This array will be empty if the result set contains no rows. Otherwise, each element of $ary_ref is a reference to one row of the result set. The meaning of the row references depends on the type of argument you pass. With no argument or an array slice argument, each row reference points to an array of column values. Array slice values begin at 0 because they are Perl array indices. Negative values count back from the end of the row. Thus, to fetch the first and last columns of each row, do the following:
$ary_ref = $sth->fetchall_arrayref ([0, -1]);
With a hash slice argument, each row reference points to a hash of column values, indexed by the names of the columns you want to retrieve. To specify a hash slice, column names should be given as hash keys and each key should have a value of 1:
$ary_ref = $sth->fetchall_arrayref ({id => 1, name => 1});To fetch all columns as a hash, pass an empty hash reference:
$ary_ref = $sth->fetchall_arrayref ({});If an error occurred, fetchall_arrayref() returns the rows fetched up to the point of the error. Check $sth->err() or $DBI::err to determine whether or not an error occurred.
$hash_ref = $sth->fetchall_hashref ($key_col);
Fetches the result set and returns a reference to a hash that contains one element for each row of the result set. Hash keys are the values of the column indicated by $key_col which should be either the name of a column selected by the query, or a column number (beginning with 1). Values in the key column should be unique to avoid loss of rows due to key collisions in the hash. The hash will be empty if the result set contains no rows. Otherwise, the value of each hash element is a reference to a hash containing one row of the result set, keyed by the names of the columns selected by the query.
If an error occurred due to an invalid key column argument, fetchall_hashref() returns undef., Otherwise, it returns the rows fetched up to the point of the error. To determine whether a non-undef return value represents success or failure, check $sth->err() or $DBI::err.
@ary = $sth->fetchrow_array ();
When called in a list context, fetchrow_array() returns an array containing column values for the next row of the result set or an empty array if there are no more rows or an error occurred. In a scalar context, fetchrow_array() returns one element of the array or undef if there are no more rows or an error occurred. Prior to version 1.29, the DBI documentation stated that fetchrow_array() returned the first column value in scalar context. Applications should no longer rely on this behavior because which element is returned now is undefined; you can tell for sure only for queries that select a single column.
To distinguish between normal exhaustion of the result set and an error in list context, check $sth->err() or $DBI::err. A value of zero indicates you've reached the end of the result set without error. However, in the absence of an error, an undef return value in scalar context can represent either a NULL column value or the end of the result set.
$ary_ref = $sth->fetchrow_arrayref ();
Returns a reference to an array containing column values for the next row of the result set or undef if there are no more rows or an error occurred.
To distinguish between normal exhaustion of the result set and an error, check $sth->err() or $DBI::err. A value of zero indicates you've reached the end of the result set without error.
$hash_ref = $sth->fetchrow_hashref ([$name]);
Returns a reference to a hash containing column values for the next row of the result set or undef if there are no more rows or an error occurred. Hash index values are the column names, and elements of the hash are the column values.
The $name argument can be specified to control hash key lettercase. It defaults to "NAME" (use column names as specified in the query). To force hash keys to be lowercase or uppercase, you can specify a $name value of "NAME_lc" or "NAME_uc" instead. (Another way to control hash key letter case is with the FetchHashKeyName attribute, which is discussed in the "DBI Attributes" section later in this appendix.)
To distinguish between normal exhaustion of the result set and an error, check $sth->err() or $DBI::err. A value of zero indicates you've reached the end of the result set without error.
$rc = $sth->finish ();
Frees any resources associated with the statement handle. Normally, you need not invoke this method yourself because row-fetching methods invoke it implicitly when they reach the end of the result set. If you fetch only part of a result set, calling finish() explicitly lets DBI know that you are done fetching data from the handle.
Calling finish() invalidates statement attributes, and because this method can be invoked implicitly by row-fetching methods when they detect the end of the result set, it's best to access any attributes you need immediately after invoking execute(), rather than waiting until later.
$rv = $sth->rows ();
Returns the number of rows affected by the statement associated with $sth, or ?1 if an error occurred. This method is used primarily for statements such as UPDATE or DELETE that do not return rows. For SELECT statements, you should not rely on the rows() method; count the rows as you fetch them instead.
General Handle Methods
The methods in this section are not specific to particular types of handles. They can be invoked using driver, database, or statement handles.
$rv = $h->err ();
Returns the numeric error code for the most recently invoked driver operation. For MySQL, this is the error number returned by the MySQL server. A return value of 0 or undef indicates that no error occurred.
$str = $h->errstr ();
Returns the string error message for the most recently invoked driver operation. For MySQL, this is the error message returned by the MySQL server. A return value of the empty string or undef indicates that no error occurred.
DBI->trace ($trace_level [, $trace_filename]); $h->trace ($trace_level [, $trace_filename]);
Sets a trace level. Tracing provides information about DBI operation. The trace level can be in the range from 0 (off) to 9 (maximum information). Tracing can be enabled for all DBI operations within a script by invoking trace as a DBI class method or for an individual handle:
DBI->trace (2); # Turn on global script tracing $sth->trace (2); # Turn on per-handle tracing
Tracing can also be enabled on a global level for all DBI scripts you run by setting the DBI_TRACE environment variable.
Trace output goes to STDERR by default. The $filename parameter can be supplied to direct output to a different file. Output is appended to any existing contents of the file; the file is not overwritten.
Each trace call causes output from all traced handles to go to the same file. If a file is named, all trace output goes there. If no file is named, all trace output goes to STDERR.
DBI->trace_msg ($str [, $min_level]); $h->trace_msg ($str [, $min_level]);
When called as a class method (DBI->trace_msg()), writes the message in $str to the trace output if tracing has been enabled at the DBI level. When called as a handle method ($h->trace_msg()), writes the message if the handle is being traced or if tracing has been enabled at the DBI level.
The $min_level parameter can be supplied to specify that the message should be written only if the trace level is at least at that level.
MySQL-Specific Administrative Methods
This section describes the func() method that DBI provides as a means of accessing driver-specific operations directly. Note that func() is not related to the use of stored procedures. Stored procedure methods currently are not defined by DBI.
$rc = $drh->func ("createdb", $db_name, $host_name, $user_name, $password, "admin"); $rc = $drh->func ("dropdb", $db_name, $host_name, $user_name, $password, "admin"); $rc = $drh->func ("reload", $host_name, $user_name, $password, "admin"); $rc = $drh->func ("shutdown", $host_name, $user_name, $password, "admin"); $rc = $dbh->func ("createdb", $db_name, "admin"); $rc = $dbh->func ("dropdb", $db_name, "admin"); $rc = $dbh->func ("reload", "admin"); $rc = $dbh->func ("shutdown", "admin");
The func() method is accessed either through a driver handle or through a database handle. A driver handle is not associated with an open connection, so if you access func() that way, you must supply arguments for the hostname, username, and password to allow the method to establish a connection. If you access func() with a database handle, those arguments are unnecessary. A driver handle can be obtained, if necessary, as follows:
$drh = DBI->install_driver ("mysql"); # note: "mysql" must be lowercaseThe actions understood by func() are as follows:
createdb
Creates the database named by $db_name. You must have the CREATE privilege for the database to do this.
dropdb
Drops (removes) the database named by $db_name. You must have the DROP privilege for the database to do this.
Be careful; if you drop a database, it's gone. You can't get it back.
reload
Tells the server to reload the grant tables. This is necessary if you modify the contents of the grant tables directly using DELETEINSERT, or UPDATE rather than using GRANT or REVOKE., You must have the RELOAD privilege to use reload.
shutdown
Shuts down the server. You must have the SHUTDOWN privilege to do this.
Note that the only func() action that cannot be performed through the usual DBI query-processing mechanism is shutdown. For the other actions, it is preferable to issue a CREATE DATABASE, DROP DATABASE, or FLUSH PRIVILEGES statement rather than invoking func().
