home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam    

Book HomeMySQL and mSQLSearch this book

21.2. DBI.pm API

The DBI API is the standard database API in Perl. So while MsqPerl and MysqlPerl may be more common in legacy code, all new code should be written with DBI.

use

use DBI;
    This must be declared in every Perl program that uses the DBI module.
DBI::available_drivers

@available_drivers = DBI->available_drivers;
@available_drivers = DBI->available_drivers($quiet);

DBI::available_drivers returns a list of the available DBD drivers. The function does this by searching the Perl distribution for DBD modules. Unless a true value is passed as the argument, the function will print a warning if two DBD modules of the same name are found in the distribution. In the current Msql-Mysql modules distribution, the driver for mSQL is named `mSQL' and the driver for MySQL is named `mysql'.

Example

use DBI;

my @drivers = DBI->available_drivers;
print "All of these drivers are available:\n" . join("\n",@drivers) .
				"\nBut we're only interested in mSQL and mysql. :)\n";
DBI::bind_col

$result = $statement_handle->bind_col($col_num, \$col_variable, \%unused);

DBI::bind_col binds a column of a SELECT statement with a Perl variable. Every time that column is accessed or modified, the value of the corresponding variable changes to match. The first argument is the number of the column in the statement, where the first column is number 1. The second argument is a reference to the Perl variable to bind to the column. The optional third argument is a reference to a hash of attributes. This is unused in DBD::mysql and DBD::mSQL. The function returns an undefined value undef if the binding fails for some reason.

Example

use DBI;
my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $query = "SELECT name, date FROM myothertable";
my $myothertable_output = $db->prepare($query);

my ($name, $date);
$myothertable_output->bind_col(1,\$name,undef);
$myothertable_output->bind_col(2,\$date,undef);
# $name and $date are now bound to their corresponding fields in the outout.

$myothertable_output->execute;
while ($myothertable_output->fetch) {
       # $name and $date are automatically changed each time.
				print "Name: $name Date: $date\n";
}
DBI::bind_columns

$result = $statement_handle->bind_columns(\%unused, @list_of_refs_to_vars);

DBI::bind_columns binds an entire list of scalar references to the corresponding field values in the output. The first argument to the function is a reference to a hash of attributes, as in DBI::bind_col. DBD::mSQL and DBD::mysql do not use this argument. Each following argument must be a reference to a scalar. Optionally, the scalars can be grouped into a \($var1, $var2) structure which has the same effect. There must be exactly as many scalar references as there are fields in the output or the program will die.

Example

use DBI;
my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $query = "SELECT name, date FROM myothertable";
my $myothertable_output = $db->prepare($query);

my ($name, $date);
$myothertable_output->bind_columns(undef, \($name, $date));
# $name and $date are now bound to their corresponding fields in the outout.

$myothertable_output->execute;
while ($myothertable_output->fetch) {
       # $name and $date are automatically changed each time.
				print "Name: $name Date: $date\n";
}
DBI::bind_param

$result = $statement_handle->bind_param($param_number, $bind_value);
$result = $statement_handle->bind_param($param_number, $bind_value, $bind_type);
$result = $statement_handle->bind_param($param_number, $bind_value, \%bind_type);

DBI::bind_param substitutes real values for the `?' placeholders in statements (see Reference 21.22). The first argument is the number of the placeholder in the statement. The first placeholder (from left to right) is 1. The second argument is the value with which to replace the placeholder. An optional third parameter can be supplied which determines the type of the value to be substituted. This can be supplied as a scalar or as a reference to a hash of the form { TYPE => &DBI::SQL_TYPE } where `SQL_TYPE' is the type of the parameter. As of the time of this writing the (undocumented) SQL types supported by DBI are SQL_CHAR, SQL_NUMERIC, SQL_DECIMAL, SQL_INTEGER, SQL_SMALLINT, SQL_FLOAT, SQL_REAL, SQL_DOUBLE, and SQL_VARCHAR. It is not documented how these correspond to the actual types used by DBD::mSQL and DBD::Mysql. However, Table 21-1 contains a list of the corresponding types as of the time of this writing. The function returns undef if the substitution is unsuccessful.

Table 21-1. Example

DBI

MSQL

MySQL

SQL_CHAR

CHAR_TYPE

IDENT_TYPE

NULL_TYPE

DATE_TYPE

MONEY_TYPE

TIME_TYPE

IDX_TYPE

SYSVAR_TYPE

ANY_TYPE

FIELD_TYPE_CHAR

FIELD_TYPE_DATE

FIELD_TYPE_DATETIME

FIELD_TYPE_NULL

FIELD_TYPE_TIMESTAMP

FIELD_TYPE_TIME

SQL_NUMERIC

FIELD_TYPE_LONG

FIELD_TYPE_LONGLONG

FIELD_TYPE_SHORT

SQL_DECIMAL

FIELD_TYPE_DECIMAL

SQL_INTEGER

INT_TYPE

FIELD_TYPE_INT24

SQL_SMALLINT

UINT_TYPE

FIELD_TYPE_INT24

SQL_FLOAT

FIELD_TYPE_FLOAT

SQL_REAL

REAL_TYPE

LAST_REAL_TYPE

FIELD_TYPE_DOUBLE

SQL_DOUBLE

FIELD_TYPE_DOUBLE

SQL_VARCHAR

TEXT_TYPE

FIELD_TYPE_TINY_BLOB

FIELD_TYPE_MEDIUM_BLOB

FIELD_TYPE_BLOB

FIELD_TYPE_LONG_BLOB

FIELD_TYPE_VAR_STRING

FIELD_TYPE_STRING

Example

use DBI;
my $db = DBI->connect('DBD:msql:mydata','me','mypass');
my $statement = $db->prepare(
"SELECT name, date FROM myothertable WHERE name like ? OR name like ?");

$statement->bind_param(1,'J%','SQL_CHAR');
$statement->bind_param(2,'%oe%', { TYPE => &DBI::SQL_CHAR });
# The statement will now be:
# SELECT name, date FROM myothertable WHERE name like 'J%' or name like '%oe%'
DBI::connect

$db = DBI->connect($data_source, $username, $password);
$db = DBI->connect($data_source, $username, $password, \%attributes);

DBI::connect requires at least three arguments, with an optional fourth, and returns a handle to the requested database. It is through this handle that you perform all of the transactions with the database server. The first argument is a data source. A list of available data sources can be obtained using DBI::data_sources. For mSQL and MySQL the format of the data sources is 'DBI:mSQL:$database:$hostname:$port' and 'DBI:mysql:$database:$hostname:$port' respectively. You may leave the ':$port' extension off to connect to the standard port. Also, you may leave the ':$hostname:$port' extension off to connect to a server on the local host using a Unix-style socket. A database name must be supplied.

The second and third arguments are the username and password of the user connecting to the database. For mSQL, these should both be `undef'. If they are `undef' for MySQL, the user running the program must have permission to access the requested databases.

The final argument is optional and is a reference to an associative array. Using this hash you may preset certain attributes for the connection. Currently, the only supported attributes are PrintError, RaiseError, and AutoCommit. These can be set to for off and some true value for on. The defaults for PrintError and AutoCommit are on and the default for RaiseError is off. Because mSQL and MySQL both do not currently support transactions, the AutoCommit attribute must be set to on (see Reference 21.28 for more details).

If the connection fails, an undefined value undef is returned and the error is placed in $DBI::errstr.

Example

use DBI;

my $db1 = DBI->connect('DBI:mSQL:mydata',undef,undef);
# $db1 is now connected to the local mSQL server using the database 'mydata'.

my $db2 = DBI->connect('DBI:mysql:mydata:myserver.com','me','mypassword');
# $db2 is now connected to the MySQL server on the default port of 
# 'myserver.com' using the database 'mydata'. The connection was made with
# the username 'me' and the password 'mypassword'.

My $db3 = DBI->connect('DBI:mSQL:mydata',undef,undef, {
				RaiseError => 1
});
# $db3 is now connected the same way as $db1 except the 'RaiseError'
# attribute has been set to true.
DBI::data_sources

@data_sources = DBI->data_sources($dbd_driver);

DBI::data_sources takes the name of a DBD module as its argument and returns all of the available databases for that driver in a format suitable for use as a data source in the DBI::connect function. The program will die with an error message if an invalid DBD driver name is supplied. In the current Msql-Mysql modules distribution, the driver for mSQL is named `mSQL' and the driver for MySQL is named `mysql'.

Example

use DBI;

my @msql_data_sources = DBI->data_sources('mSQL');
my @mysql_data_sources = DBI->data_sources('mysql');
# Both DBD::mSQL and DBD::mysql had better be installed or
# the program will die.

print "mSQL databases:\n" . join("\n",@msql_data_sources) . "\n\n";
print "MySQL databases:\n" . join("\n",@mysql_data_sources) . "\n\n";
DBI::do

$rows_affected  = $db->do($statement);
$rows_affected  = $db->do($statement, \%unused);
$rows_affected  = $db->do($statement, \%unused, @bind_values);

DBI::do directly performs a non-SELECT SQL statement and returns the number of rows affected by the statement. This is faster than a DBI::prepare/DBI::execute pair which requires two function calls. The first argument is the SQL statement itself. The second argument is unused in DBD::mSQL and DBD::mysql, but can hold a reference to a hash of attributes for other DBD modules. The final argument is an array of values used to replace `placeholders,' which are indicated with a `?' in the statement. The values of the array are substituted for the placeholders from left to right. As an additional bonus, DBI::do will automatically quote string values before substitution.

Example

use DBI;
my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);

my $rows_affected = $db->do("UPDATE mytable SET name='Joe' WHERE name='Bob'");
print "$rows_affected Joe's were changed to Bob's\n";

my $rows_affected2 = $db->do("INSERT INTO mytable (name) VALUES (?)",
				{}, ("Sheldon's Cycle"));
# After quoting and substitution, the statement:
# INSERT INTO mytable (name) VALUES ('Sheldon's Cycle')
# was sent to the database server.
DBI::disconnect

$result  = $db->disconnect;

DBI::disconnect disconnects the database handle from the database server. With mSQL and MySQL, this is largely unnecessary because the databases do not support transactions and an unexpected disconnect will do no harm. However, databases that do support transactions need to be explicitly disconnected. Therefore, for portable code you should always call disconnect before exiting the program. If there is an error while attempting to disconnect, a nonzero value will be returned and the error will be set in $DBI::errstr.

Example

use DBI;
my $db1 = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $db2 = DBI->connect('DBI:mSQL:mydata2',undef,undef);
...
$db1->disconnect;
# The connection to 'mydata' is now severed. The connection to 'mydata2'
# is still alive.
DBI::dump_results

$neat_rows = DBI::dump_results($statement_handle);
$neat_rows = DBI::dump_results($statement_handle, $maxlen);
$neat_rows = DBI::dump_results($statement_handle, $maxlen, $line_sep);
$neat_rows = DBI::dump_results($statement_handle, $maxlen, $line_sep, 
             $field_sep);
$neat_rows = DBI::dump_results($statement_handle, $maxlen, $line_sep, 
             $field_sep, $file_handle);

DBI::dump_results prints the contents of a statement handle in a neat and orderly fashion by calling DBI::neat_string on each row of data. This is useful for quickly checking the results of queries while you write your code. The only required argument is the statement handle to print out. If a second argument is present, it is used as the maximum length of each field in the table. The default is 35. A third argument is the string used to separate each line of data. The default is \n. The fourth argument is the string used to join the fields in a row. The default is a comma. The final argument is a reference to a filehandle glob. The results are printed to this filehandle. The default is STDOUT. If the statement handle cannot be read, an undefined value undef is returned.

Example

use DBI;
my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $query = "SELECT name, date FROM myothertable";
my $myothertable_output = $db->prepare($query);
$myothertable_output->execute;

print DBI::dump_results($myothertable_output);
# Print the output in a neat table.

open(MYOTHERTABLE,">>myothertable");
print DBI::dump_results($myothertable_output,undef,undef,undef,\*MYOTHERTABLE);
# Print the output again into the file 'myothertable'.
$DBI::err

$error_code  = $handle->err;

$DBI::err returns the error code for the last DBI error encountered. This error number corresponds to the error message returned from $DBI::errstr. The variable $DBI::err performs the same function. This function is available from both database and statement handles.

Example

use DBI;
my $db = DBI->connect('DBI:mysql:mydata','webuser','super_secret_squirrel');

# There is a parse error in this query...
my $output = $db->prepare('SLECT * from mydata');
$output->execute;

if (not $output) {
				print "Error $DBI:err: $DBI:errstr\n";
}
$DBI::errstr

$error = $handle->errstr;

$DBI::errstr returns the error message for the last DBI error encountered. The value remains until the next error occurs, at which time it is replaced. If no error has occurred during your session, the function returns undef. The variable $DBI::errstr performs the same function. This function is available from both database and statement handles.

Example

Use DBI;
my $db = DBI->connect('DBI:mysql:mydata','webuser','super_secret_squirrel');
...
my $error = $db->errstr;
warn("This is your most recent DBI error: $error");
DBI::execute

$rows_affected = $statement_handle->execute;
$rows_affected = $statement_handle->execute(@bind_values);

DBI::execute executes the SQL statement held in the statement handle. For a non-SELECT query, the function returns the number of rows affected. The function returns `-1' if the number of rows is not known. For a SELECT query, some true value is returned upon success. If arguments are provided, they are used to fill in any placeholders in the statement (see Reference 21.22).

Example

use DBI;
my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $statement_handle = $db->prepare("SELECT * FROM mytable");
my $statement_handle2 = $db->prepare("SELECT name, date FROM myothertable
   WHERE name like ?");

$statement_handle->execute;
# The first statement has now been performed. The values can now be accessed
# through the statement handle.

$statement_handle->execute("J%");
# The second statement has now been executed as the following:
# SELECT name, date FROM myothertable WHERE name like 'J%'
DBI::fetchall_arrayref

$ref_of_array_of_arrays = $statement_handle->fetchall_arrayref;

DBI::fetchall_arrayref returns all of the remaining data in the statement handle as a reference to an array. Each row of the array is a reference to another array that contains the data in that row. The function returns an undefined value undef if there is no data in the statement handle. If any previous DBI::fetchrow_* functions were called on this statement handle, DBI::fetchall_arrayref returns all of the data after the last DBI::fetchrow_* call.

Example

use DBI;
my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $query = "SELECT name, date FROM myothertable";
my $output = $db->prepare($query);
$output->execute;

my $data = $output->fetchall_arrayref;
# $data is not a reference to an array of arrays. The each element of the
# `master' array is itself an array that contains a row of data.

print "The fourth date in the table is: " . $data->[3][1] . "\n";
# data.
# Element 1 of that array is the date.
DBI::fetchrow_array

@row_of_data = $statement_handle->fetchrow;

DBI::fetchrow returns the next row of data from a statement handle generated by DBI::execute. Each successive call to DBI::fetchrow returns the next row of data. When there is no more data, the function returns an undefined value undef. The elements in the resultant array are in the order specified in the original query. If the query was of the form SELECT * FROM . . ., the elements are ordered in the same sequence as the fields were defined in the table.

Example

use DBI;
my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $query = "SELECT name, date FROM myothertable WHERE name LIKE 'Bob%'";
my $myothertable_output = $db->prepare($query);
$myothertable_output->execute;

my ($name, $date);

# This is the first row of data from $myothertable_output.
($name, $date) = $myothertable_output->fetchrow_array;
# This is the next row...
($name, $date) = $myothertable_output->fetchrow_array;
# And the next...
my @name_and_date = $myothertable_output->fetchrow_array;
# etc...
DBI::fetchrow_arrayref, DBI::fetch

$array_reference = $statement_handle->fetchrow_arrayref;
$array_reference = $statement_handle->fetch;

DBI:: fetchrow_arrayref and its alias, DBI::fetch, work exactly like DBI::fetchrow_array except that they return a reference to an array instead of an actual array.

Example

use DBI;
my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $query = "SELECT name, date FROM myothertable WHERE name LIKE 'Bob%'";
my $myothertable_output = $db->prepare($query);
$myothertable_output->execute;

my $name1 = $myothertable_output->fetch->[0]
# This is the 'name' field from the first row of data.
my $date2 = $myothertable_output->fetch->[1]
# This is the 'date' from from the *second* row of data.
my ($name3, $date3) = @{$myothertable_output->fetch};
# This is the entire third row of data. $myothertable_output->fetch returns a
# reference to an array. We can 'cast' this into a real array with the @{}
# construct.
DBI::fetchrow_hashref

$hash_reference = $statement_handle->fetchrow_hashref;

DBI::fetchrow_hashref works exactly like DBI::fetchrow_arrayref except that it returns a reference to an associative array instead of a regular array. The keys of the hash are the names of the fields and the values are the values of that row of data.

Example

use DBI;
my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $query = "SELECT * FROM mytable";
my $mytable_output = $db->prepare($query);
$mytable_output->execute;

my %row1 = $mytable_ouput->fetchrow_hashref;
my @field_names = keys %row1;
# @field_names now contains the names of all of the fields in the query.
# This needs to be set only once. All future rows will have the same fields.
my @row1 = values %row1;
DBI::finish

$result = $statement_handle->finish;

DBI::finish releases all data in the statement handle so that the handle may be destroyed or prepared again. Some database servers require this in order to free the appropriate resources. DBD::mSQL and DBD::mysql do not need this function, but for portable code, you should use it after you are done with a statement handle. The function returns an undefined value undef if the handle cannot be freed.

Example

use DBI;
my $db = DBI->connect('DBI:mysql:mydata','me','mypassword');
my $query = "SELECT * FROM mytable";
my $mytable_output = $db->prepare($query);
$mytable_output->execute;
...
$mytable_output->finish;
# You can now reassign $mytable_output or prepare another statement for it.
DBI::func

$handle->func(@func_arguments, $func_name);
@dbs = $db->func("$hostname", '_ListDBs');
@dbs = $db->func("$hostname:$port", '_ListDBs');
@tables = $db->func('_ListTables');
$result = $drh->func( $database, '_CreateDB' );
$result = $drh->func( $database, '_DropDB' );

DBI::func calls specialized nonportable functions included with the various DBD drivers. It can be used with either a database or a statement handle depending on the purpose of the specialized function. If possible, you should use a portable DBI equivalent function. When using a specialized function, the function arguments are passed as a scalar first followed by the function name. DBD::mSQL and DBD::mysql implement the following functions:

_ListDBs

The _ListDBs function takes a hostname and optional port number and returns a list of the databases available on that server. It is better to use the portable function DBI::data_sources.

_ListTables

The _ListTables function returns a list of the tables present in the current database.

_CreateDB

The _CreateDB function takes the name of a database as its argument and attempts to create that database on the server. You must have permission to create databases for this function to work. The function returns -1 on failure and on success.

_DropDB

The _DropDB function takes the name of a database as its argument and attempts to delete that database from the server. This function does not prompt the user in any way, and if successful, the database will be irrevocably gone forever. You must have permission to drop databases for this function to work. The function returns -1 on failure and on success.

Example

use DBI;
my $db = DBI->connect('DBI:mysql:mydata','me','mypassword');

my @tables = $db->func('_ListTables');
# @tables now has a list of the tables in 'mydata'.
DBI::neat

$neat_string = DBI::neat($string);
$neat_string = DBI::neat($string, $maxlen);

DBI::neat takes as its arguments a string and an optional length. The string is then formatted to print out neatly. The entire string is enclosed in single quotes. All unprintable characters are replaced with periods. If the length argument is present, are characters after the maximum length are removed and the string is terminated with three periods (...). If no length is supplied, 400 is used as the default length.

Example

use DBI;

my $string = "This is a very, very, very long string with lots of stuff in it.";
my $neat_string = DBI::neat($string,14);
# $neat_string is now: 'This is a very...
DBI::neat_list

$neat_string = DBI::neat_list(\@listref, $maxlen);
$neat_string = DBI::neat_list(\@listref, $maxlen, $field_seperator);

DBI::neat_list takes three arguments and returns a neatly formatted string suitable for printing. The first argument is a reference to a list of values to print. The second argument is the maximum length of each field. The final argument is a string used to join the fields. DBI::neat is called for each member of the list using the maximum length given. The resulting strings are then joined using the last argument. If the final argument is not present, a comma is used as the separator.

Example

use DBI;

my @list = (`Bob', `Joe', `Frank');
my $neat_string = DBI::neat_list(\@list, 3);
# $neat_string is now: 'Bob', 'Joe', 'Fra...
DBI::prepare

$statement_handle = $db->prepare($statement);
$statement_handle = $db->prepare($statement, \%unused);

DBI::prepare takes as its argument an SQL statement, which some database modules put into an internal compiled form so that it runs faster when DBI::execute is called. These DBD modules (not DBD::mSQL or DBD::mysql) also accept a reference to a hash of optional attributes. The mSQL and MySQL server do not currently implement the concept of "preparing," so DBI::prepare merely stores the statement. You may optionally insert any number of `?' symbols into your statement in place of data values. These symbols are known as "placeholders." The DBI::bind_param function is used to substitute the actual values for the placeholders. The function returns undef if the statement cannot be prepared for some reason.

Example

use DBI;
my $db = DBI->connect('DBI:mysql:mydata','me','mypassword');

my $statement_handle = $db->prepare('SELECT * FROM mytable');
# This statement is now ready for execution.

My $statement_handle = $db->prepare(
'SELECT name, date FROM myothertable WHERE name like ?');
# This statement will be ready for exececuting once the placeholder is filled
# in using the DBI::bind_param function.
DBI::quote

$quoted_string = $db->quote($string);

DBI::quote takes a string intended for use in an SQL query and returns a copy that is properly quoted for insertion in the query. This includes placing the proper outer quotes around the string.

Example

use DBI;
my $db1 = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $db2 = DBI->connect('DBI:mysql:myotherdata','me','mypassword');

my $string = "Sheldon's Cycle";

my $qs1 = $db1->quote($string);
# $qs1 is: 'Sheldon\'s Cycle' (including the outer quotes)
my $qs2 = $db2->quote($string);
# $qs2 is: 'Sheldon's Cycle' (including the outer quotes)
# Both strings are now suitable for use in a statement for their respective
# database servers.
DBI::rows

$number_of_rows = $statement_handle->rows;

DBI::rows returns the number of rows of data contained in the statement handle. With DBD::mSQL and DBD::mysql, this function is accurate for all statements, including SELECT statements. For many other drivers which do not hold of the results in memory at once, this function is only reliable for non-SELECT statements. This should be taken into account when writing portable code. The function returns `-1' if the number of rows is unknown for some reason. The variable $DBI::rows provides the same functionality.

Example

use DBI;
my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
my $query = "SELECT name, date FROM myothertable WHERE name='Bob'";
my $myothertable_output = $db->prepare($query);
$myothertable_output->execute;

my $rows = $myothertable_output->rows;
print "There are $rows 'Bob's in 'myothertable'.\n";
DBI::state

$sql_error  = $handle->state;

DBI::state returns the SQLSTATE SQL error code for the last error DBI encountered. Currently both DBD::mSQL and DBD::mysql report `S1000' for all errors. This function is available from both database and statement handles. The variable $DBI::state performs the same function.

Example

Use DBI;
my $db = DBI->connect('DBI:mysql:mydata','webuser','super_secret_squirrel');
...
my $sql_error = $db->state;
warn("This is your most recent DBI SQL error: $sql_error");
DBI::trace

DBI->trace($trace_level)
DBI->trace($trace_level, $trace_file)
$handle->trace($trace_level);
$handle->trace($trace_level, $trace_file);

DBI::trace is useful mostly for debugging purposes. If the trace level is set to 2, full debugging information will be displayed. Setting the trace level to disables the trace. If DBI->trace is used, tracing is enabled for all handles. If $handle->trace is used, tracing is enabled for that handle only. This works for both database and statement handles. If a second argument is present for either DBI->trace or $handle->trace, the debugging information for all handles is appended to that file. You can turn on tracing also by setting the environment variable DBI_TRACE. If the environment variable is defined as a number (0 or 2, currently) tracing for all handles is enabled at that level. With any other definition, the trace level is set to 2 and the value of the environment variable is used as the filename for outputting the trace information.

Example

use DBI;
my $db1 = DBI->connect('DBI:mysql:mydata','webuser','super_secret_squirrel');
my $db2 = DBI->connect('DBI:mSQL:myotherdata',undef,undef);

DBI->trace(2);
# Tracing is now enabled for all handles at level 2.
$db2->trace(0);
# Tracing is now disabled for $db2, but it is still enabled for $db1
$db1->trace(2,'DBI.trace');
# Tracing is now enabled for all handles at level 2, with the output being
# sent to the file 'DBI.trace'.
DBI::commit, DBI::rollback, DBI::ping

$result = $db->commit;
$result = $db->rollback;
$result = $db->ping;

DBI::commit and DBI::rollback are useful only with database servers that support transactions. They have no effect when used with DBD::mSQL and DBD::mysql. DBD::ping attempts to verify if the database server is running. It is not implemented and has no effect with DBD::mSQL and DBD::mysql.

Attributes

$db->{AutoCommit}
$handle->{ChopBlanks}
$handle->{CompatMode}
$handle->{InactiveDestroy}
$handle->{LongReadLen}
$handle->{LongTruncOk}
$handle->{PrintError}
$handle->{RaiseError}
$handle->{Warn}
$statement_handle->{CursorName}
$statement_handle->{insertid} (MySQL only)
$statement_handle->{is_blob} (MySQL only)
$statement_handle->{is_key} (MySQL only)
$statement_handle->{is_not_null}
$statement_handle->{is_num}
$statement_handle->{is_pri_key} (MySQL and mSQL 1.x only)
$statement_handle->{length}
$statement_handle->{max_length} (MySQL only)
$statement_handle->{NAME}
$statement_handle->{NULLABLE}
$statement_handle->{NUM_OF_FIELDS}
$statement_handle->{NUM_OF_PARAMS}
$statement_handle->{table}
$statement_handle->{type}

The DBI.pm API defines several attributes that may be set or read at any time. Assigning a value to an attribute that can be set changes the behavior of the current connection in some way. Assigning any true value to an attribute will set that attribute on. Assigning to an attribute sets it off. Some values are defined only for particular databases and are not portable. The following are attributes that are present for both database and statement handles.

$db->{AutoCommit}

This attribute affects the behavior of database servers that support transactions. For mSQL and MySQL, they must always be set to `on' (the default). Attempting to change this will kill the program.

$handle->{ChopBlanks}

If this attribute is on, any data returned from a query (such as DBI::fetchrow call) will have any leading or trailing spaces chopped off. Any handles deriving from the current handle inherit this attribute. The default for this attribute is `off.'

$handle->{InactiveDestroy}

This attribute is designed to enable handles to survive a `fork' so that a child can make use of a parent's handle. You should enable this attribute in either the parent or the child but not both. The default for this attribute is `off.'

$handle->{PrintError}

If this attribute is on, all warning messages will be displayed to the user. If this attribute is off, the errors are available only through $DBI::errstr. Any handles deriving from the current handle inherit this attribute. The default for this attribute is `on.'

$handle->{RaiseError}

If this attribute is on, any errors will raise an exception in the program, killing the program if no `__DIE__' handler is defined. Any handles deriving from the current handle inherit this attribute. The default for this attribute is `off.'

$handle->{Warn}

If this attribute is on, warning messages for certain bad programming practices (most notably holdovers from Perl 4) will be displayed. Turning this attribute off disables DBI warnings and should be used only if you are really confident in your programming skills. Any handles deriving from the current handle (such as a statement handle resulting from a database handle query) inherit this attribute. The default for this attribute is `on.'

$statement_handle->{insertid}

This is a nonportable attribute that is defined only for DBD::mysql. The attribute returns the current value of the auto_increment field (if there is one) in the table. If no auto_increment field exists, the attribute returns undef.

$statement_handle->{is_blob}

This is a nonportable attribute which is defined only for DBD::mysql. The attribute returns a reference to an array of boolean values indicating if each of the fields contained in the statement handle is of a BLOB type. For a statement handle that was not returned by a SELECT statement, $statement_handle->{is_blob} returns undef.

$statement_handle->{is_key}

This is a nonportable attribute which is defined only for DBD::mysql. The attribute returns a reference to an array of boolean values indicating if each of the fields contained in the statement handle were defined as a KEY. For a statement handle that was not returned by a SELECT statement, $statement_handle->{is_key} returns undef.

$statement_handle->{is_not_null}

This is a nonportable attribute which is defined only for DBD::mSQL and DBD::mysql. The attribute returns a reference to a list of boolean values indicating if each of the fields contained in the statement handle are defined `NOT NULL'. For a statement handle that was not returned by a SELECT statement, $statement_handle->{is_not_null} returns undef. The same effect of this attribute can be accomplished in a portable manner by using $statement_handle->{NULLABLE}.

$statement_handle->{is_num}

This is a nonportable attribute which is defined only for DBD::mSQL and DBD::mysql. The attribute returns a reference to an array of boolean values indicating if each of the fields contained in the statement handle is a number type. For a statement handle that was not returned by a SELECT statement, $statement_handle->{is_num} returns undef.

$statement_handle->{is_pri_key}

This is a nonportable attribute which is defined only for DBD::mSQL and DBD::mysql. When used with DBD::mSQL it has effect only in conjunction with mSQL 1.x servers, because mSQL 2.x does not use primary keys. The attribute returns a reference to a list of boolean values indicating if each of the fields contained in the statement handle is a primary key. For a statement handle that was not returned by a SELECT statement, $statement_handle->{is_pri_key} returns undef.

$statement_handle->{length}

This is a nonportable attribute which is defined only for DBD::mysql and DBD::mSQL. The attribute returns a reference to a list of the maximum possible length of each field contained in the statement handle. For a statement handle that was not returned by a SELECT statement, $statement_handle->{length} returns undef.

$statement_handle->{max_length}

This is a nonportable attribute which is defined only for DBD::mysql. The attribute returns a reference to a list of the actual maximum length of each field contained in the statement handle. For a statement handle that was not returned by a SELECT statement, $statement_handle->{max_length} returns undef.

$statement_handle->{NAME}

This attribute returns a reference to a list of the names of the fields contained in the statement handle. For a statement handle that was not returned by a SELECT statement, $statement_handle->{NAME} returns undef.

$statement_handle->{NULLABLE}

This attribute returns a reference to a list of boolean values indicating if each of the fields contained in the statement handle can have a NULL value. A field defined with `NOT NULL' will have a value of in the list. All other fields will have a value of 1. For a statement handle that was not returned by a SELECT statement, $statement_handle->{NULLABLE} returns undef.

$statement_handle->{NUM_OF_FIELDS}

This attribute returns the number of columns of data contained in the statement handle. For a statement handle that was not returned by a SELECT statement, $statement_handle->{NUM_OF_FIELDS} returns 0.

$statement_handle->{NUM_OF_PARAMS}

This attribute returns the number of "placeholders" in the statement handle. Placeholders are indicated with a `?' in the statement. The DBI::bind_values function is used to replace the placeholders with the proper values.

$statement_handle->{table}

This is a nonportable attribute which is defined only for DBD::mSQL and DBD::mysql. The attribute returns a reference to a list of the names of the tables accessed in the query. This is particularly useful in conjunction with a JOINed SELECT that uses multiple tables.

$statement_handle->{type}

This is a nonportable attribute which is defined only for DBD::mSQL and DBD::mysql. The attribute returns a reference to a list of the types of the fields contained in the statement handle. For a statement handle that was not returned by a SELECT statement, $statement_handle->{max_length} returns undef. The values of this list are integers that correspond to an enumeration in the mysql_com.h C header file found in the MySQL distribution. There is currently no method to access the names of these types from within DBI. But the types are accessible via the &Mysql::FIELD_TYPE_* function in Mysql.pm. There is also an undocumented attribute in DBD::mysql called $statement_handle->{format_type_name} which is identical to $statement_handle->{type} except that it returns the SQL names of the types instead of integers. It should be stressed that this is an undocumented attribute and the author of DBD::mysql has stated his intention to remove it should DBI implement the same functionality.

$statement_handle->{CursorName}
$handle->{LongReadLen}
$handle->{LongTruncOk}
$handle->{CompatMode}

All of these attributes are unsupported in DBD::mSQL and DBD::mysql. Assigning to them will do nothing and reading them will return a or undef. The exception is $statement_handle->{CursorName}. Currently, accessing this attribute in any way will cause the program to die.

Example

use DBI;
my $db = DBI->connect('mysql:mydata','me','mypassword');

$db->{RAISE_ERROR} = 1;
# Now, any DBI/DBD errors will kill the program.

my $statement_handle = $db->prepare('SELECT * FROM mytable');
$statement_handle->execute;

my @fields = @{$statement_handle->{NAME}};
# @fields now contains an array of all of the field names in 'mytable'.


Library Navigation Links

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







??????????????@Mail.ru