12.2. Perl DBI Method and Function Reference


The following is a list of DBI methods and functions in alphabetical order. The syntax and an explanation of each as well as examples of the use of most is provided. However, to save space, the examples are only excerpts and are missing some components, such as the calling of the DBI module and the creation of a database handle. See the previous tutorial for an example of a complete Perl DBI script. In addition to passing parameters, you can affect the behavior of several methods by setting global values called attributes. See the end of this chapter for a list of attributes.

available_drivers( )

DBI->available_drivers([nowarn])

This returns a list of available DBD drivers. Any warning messages may be suppressed by providing the text nowarn as an argument.

... my @drivers = DBI->available_drivers( )               || die "No drivers found."; foreach my $driver(@drivers) {    print "$driver\n"; } exit;

begin_work( )

$database_handle->begin_work( )

This temporarily turns AutoCommit off until commit( ) or rollback( ) is run. There are no arguments to this database handle method.

bind_col( )

$statement_handle->bind_col(index, \$variable[, \%attributes])

This associates or binds a column from a statement handle to a given variable. The values are updated when the related row is retrieved using a fetch method, without extra copying of data.

... my $sql_stmnt = "SELECT title, author FROM books"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); $sth->bind_col(1, \$title); $sth->bind_col(2, \$author); while($sth->fetch( )) {    print "$title by $author \n"; }

A separate statement has to be issued for each bind. To bind multiple columns in one statement, use bind_columns( ).

bind_columns( )

$statement_handle->bind_columns([\%attributes,] @references)

This associates or binds columns from a statement handle to a given list (@references) of variables. The values are updated when the related row is retrieved using a fetch method without extra copying of data. The number of variables given must match the number of columns selected and the columns are assigned to variables in the order the columns are returned. Attributes common to all DBI handles may be stated as the first argument.

... my $sql_stmnt = "SELECT title, author FROM books"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); $sth->bind_columns(\$title, \$author); while($sth->fetch( )) {    print "$title by $author \n"; }

bind_param( )

$statement_param(index, values[, type])

This associates or binds a value in an SQL statement to a placeholder. Placeholders are indicated by ? in SQL statements and are numbered in the order they appear in the statement, starting with 1. The first argument indicates which placeholder to replace with a given value, the second argument. The datatype may be specified as a third argument.

... my $sql_stmnt = "SELECT title, publisher                  FROM books WHERE author = ?"; my $sth = $dbh->prepare($sql_stmnt); $sth->bind_param(1, $author); $sth->execute( ); while($sth->fetch( )) {    print "$title ($publisher) \n"; }

In this example, a placeholder (a question mark) is given in the SQL statement and is replaced with the actual value of $author using bind_param( ). This must be done before the execute( ) is issued.

bind_param_array( )

$statement_handle->bind_param_array(index, values[,                                     \%attributes|type])

This associates or binds an array of values in an SQL statement within a prepare( ) using placeholders. The first argument indicates which placeholder to replace with the array of given values, the second argument. The values are updated when the related row is retrieved using a fetch method. Attributes may be added or the datatype given as a third argument.

bind_param_inout( )

$statement_handle->bind_param_inout(index, values[, length,                                     \%attributes|type])

This associates or binds a value in an SQL statement using a placeholder. The first argument indicates which placeholder to replace with a given value, the second argument. The values are updated when the related row is retrieved using a fetch method. The maximum length of a value may be given in the third argument. Attributes may be added or the datatype may be given as a fourth argument.

can( )

$handle->can($method_name)

This returns true if the method named is implemented by the driver.

clone( )

$database_handle->clone([\%attributes])

Use this to create a new database handle by copying the parameters of the database handle calling the method. Additional attributes may be given with the method.

my $dbh1 = $dbh->clone({AutoCommit=>1});

column_info( )

$database_handle->column_info($catalog, $schema, $table, $column)

This returns a statement handle for fetching information about columns in a table.

commit( )

$database_handle->commit( )

This commits or makes permanent changes to a database. It's disregarded if AutoCommit is already enabled.

connect( )

DBI->connect(DBI:server:database[:host:port],              username, password[, \%attributes])

Use this to establish a connection to MySQL and to select a database. The first argument includes a list of settings separated by colons. The name of the module (DBI), the type of server (mysql), and the name of the database to use are required. The hostname or IP address and port number are optional. The second argument is the username and the third is the user's password. You can substitute any of these settings or values with variables just be sure to enclose each argument containing variables with double quotes so that the values will be interpolated. Finally, attributes may be given in the fourth argument.

my $dbh = DBI->connect('DBI:mysql:bookstore:localhost',                        'jacinta','richardson',                       {AutoCommit=>0});

In this excerpt, Perl is connecting to the MySQL server with the username of jacinta and the password of richardson, with the database bookstore. The attribute of AutoCommit is set to off so that changes to the data may be undone using rollback( ). See the end of this chapter for a list of attributes.

connect_cached( )

DBI->connect_cached(DBI:server:database[:host:port],                     user, password[, \%attributes])

This is similar to connect( ), except that the database handle is stored in a hash with the given parameters. This allows the database handle to be reused if connect_cached( ) is called again. You can access and eliminate with the CachedKids attribute.

data_sources( )

DBI->data_sources([driver, \%attributes])

This returns a list of databases associated with a given driver. If none is specified, the driver named in the environment variable DBI_DRIVER is used. Attributes may be given as a second argument.

... my @drivers = DBI->available_drivers( );       || die "No drivers found."; foreach my $driver(@drivers) {    my @sources = DBI->data_sources($driver);    foreach my $source(@sources) {       print "$driver:  $source\n";    } }

disconnect( )

$database_handle->disconnect( )

This disconnects a Perl script from a database; it ends a MySQL session. There are no arguments for this function.

$sth->finish( ); $dbh->disconnect( );

It's appropriate to end all statement handles before disconnecting from MySQL using the finish( ) function as shown here.

do( )

$database_handle->do(statement[, \%attributes, @values])

This executes an SQL statement without having to use the prepare( ) method. It returns the number of rows changed. The first argument contains an SQL statement. If placeholders are used in the SQL statement, their values are provided in a comma-separated list or an array in the third argument. Statement handle attributes may be given for the second argument.

... my $sql_stmnt = "UPDATE books SET publisher = ?                  WHERE publisher = ?"; $dbh->do($sql_stmnt, '', 'Oxford Univ. Press', 'OUP'); $dbh->disconnect( );

In this example, the initials of a particular publisher are changed to the publisher's name. The SQL statement is executed without a prepare( ) or an execute(). Therefore, a finish( ) isn't required, just a disconnect() is.

dump_results( )

$statement_handle->dump_results(length, row_delimiter,                                 column_delimiter, filehandle})

This displays the results of a statement using the neat_list( ) function on each row for the statement handle given. The first argument is the maximum length of each column's display. For columns containing more characters than the maximum length, the excess will be omitted and ellipses will be presented in its place. The default length is 35 characters. For the second argument, the delimiter for each row may be given the default is \n. The delimiter for columns may also be changed from the default of a comma and a space in the third argument. In the last argument of the function, a file handle of where to direct the results of the function may be given. If one is not specified, stdout is used.

... my $sql_stmnt = "SELECT title, authors                  FROM books                  WHERE author= 'Henry James' LIMIT 3"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); $results = $sth->dump_results(10, "\n", '|'); ...

The results of the preceding script would look like this:

'The Boston...'|'Henry James' 'The Muse'|'Henry James' 'Washington...'|'Henry James' 3 rows

err( )

$handle->err( )

This returns any error codes from the last driver method call.

 . . . my $dbh = DBI->connect('DBI:mysql:bookstore:localhost',                        'username','password')           || die 'Could not connect; Error Code: '           . DBI->err;

errstr( )

$handle->errstr( )

This returns any error messages from the last driver method called.

 . . . my $dbh = DBI->connect('DBI:mysql:bookstore:localhost',                        'username","password')           || die 'Could not connect to database: '           . DBI->errstr;

The error message given in the die statement is joined to the DBI error message.

execute( )

$statement_handle->execute( )

This executes a statement handle that has been processed with the prepare( ) method. There are no arguments to this function.

 . . . my $dbh = DBI->connect ("$data_source","$user","$pwd") my $sql_stmnt = "SELECT * FROM books"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( );

Although this excerpt will execute the given statement, it will not display the results. To do this, a method like fetchrow_array( ) is needed.

execute_array( )

$statement_handle->execute_array([\%attributes, @values)

Use this to execute a prepared statement multiple times, once for each set of values given either as the second argument of the method or from previous uses of the bind_param_array( ) method.

execute_for_fetch( )

execute_for_fetch($fetch[, \@status)

Use this to execute multiple statements given as the argument of the method.

fetch( )

$statement_handle->fetch( )

This returns a reference to an array of one row from the results of a statement handle. It's synonymous with fetchrow_arrayref(). There are no arguments for this function.

fetchall_arrayref( )

$statement_handle->fetchall_arrayref( )

This captures the results of a statement and returns a reference to the data. The results is a complex data structure; an array of references to an array for each row of data retrieved.

... my $sql_stmnt = "SELECT title, author FROM books"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); my $books = $sth->fetchall_arrayref( ); $sth->finish( ); foreach my $book (@$books) {    my ($title, $author) = @$book;    print "$title by $author \n"; } $sth->finish( );

fetchall_hashref( )

$statement_handle->fetchall_hashref(key_column)

This captures the result of a statement and returns a reference to the data. The result is a complex data structure: a hash of references to a hash of each row of data retrieved. By specifying the column to use as a key for the primary hash, a particular row may be accessed and thereby other columns.

... my $sql_stmnt = "SELECT book_id, title, authors FROM books"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); $books = $sth->fetchall_hashref('book_id'); foreach my $book_id (%$books) {    print "$books->{$book_id}->{title} by              $books->{$book_id}->{author} \n"; } $sth->finish( );

fetchrow_array( )

$statement_handle->fetchrow_array( )

This returns one row from the results of a statement handle in the form of an array, each of whose element is a column.

... my $sql_stmnt = "SELECT title, author FROM books"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); while (my ($title, $author) = $sth->fetchrow_array( ){    print "$title by $author \n"; } $sth->finish( );

fetchrow_arrayref( )

$statement_handle->fetchrow_arrayref( )

This returns a reference to a place in memory containing an array of one row from the results of a statement handle. There are no arguments for this function. It's synonymous with fetch( ).

... my $sql_stmnt = "SELECT title, author FROM books"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); while (my $book = $sth->fetchrow_arrayref( )) {    my ($title, $author) = @$book;    print "$title by $author \n"; } $sth->finish( );

Notice that fetchrow_arrayref( ) is reused at the beginning of each pass through the while statement. This is because a reference to one row is retrieved at a time.

fetchrow_hashref( )

$statement_handle->fetchrow_hashref( )

This returns a reference to a place in memory containing a hash of keys and values for one row from the results of a statement handle. There are no arguments for this function.

... my $sql_stmnt = "SELECT title, author FROM books"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); while (my $book_ref = $sth->fetchrow_hashref( )) {    print "$book_ref->{'title'} by $book_ref->{'author'} \n"; } $sth->finish( );

finish( )

$statement_handle->finish( )

This ends a statement handle given that was established by the prepare( ) method. There are no arguments to the method.

$sth->finish( );

Although a statement handle may have been closed with finish( ), more statement handles may be created and executed as long as the database handle has not been closed using disconnect( ).

foreign_key_info( )

$database_handle->foreign_key_info(                          $pk_catalog, $pk_schema, $pk_table,                           $pk_catalog, $pk_schema, $pk_table,                           $fk_catalog, $fk_schema, $fk_table                          $pk_catalog, $pk_schema, $pk_table,                           $fk_catalog, $fk_schema, $fk_table                          [, \%attributes])

This returns a handle for fetching information about foreign keys in a given table.

func( )

$handle->func(@arguments, function_name)

This calls private nonportable and nonstandard methods for handles. The name of the function is given as the second argument. Any arguments for the function specified are given in the first argument of this method.

get_info( )

$database_handle->get_info(type)

This returns information about the database handle for the numeric code type (based on SQL standards) given as an argument to the method. Information can include the driver and the capabilities of the data source. The function returns undef for an unknown type.

installed_versions( )

DBI->installed_versions( )

This returns a list of installed drivers. There are no arguments to this method. Enter the following from the command line to see results:

perl -MDBI -e 'DBI->installed_versions'

last_insert_id( )

$database_handle->last_insert_id( )

This returns the value stored in the row identification column of the most recent row inserted for the current MySQL session, provided the identification number was incremented using AUTO_INCREMENT in MySQL. It works like the LAST_INSERT_ID( ) function in MySQL. No arguments for this function are necessary with MySQL. This function doesn't work with MySQL before Version 1.45 of DBI.

looks_like_number( )

DBI::looks_like_number(@array)

This returns 1 for each element in an array that appears to be a number.

neat( )

DBI::neat(string[, length])

This returns a string given as the first argument of the function, placed in quotes, for an optional maximum length given as the second argument.

#!/usr/bin/perl -w use DBI; my $test = "This is a test."; print "Test: " . DBI::neat($test, 8) . "\n\n"; exit;

Here are the results of running this script:

Test: 'This is...'

Notice that the results are in single quotes, that the text was truncated based on the maximum length given, and that ellipses were automatically placed at the end of the string. To neaten a list of strings, use the neat_list() function.

neat_list( )

DBI::neat_list(\@strings[, length, delimiter])

This returns a list of strings given as the first argument of the function, placed in quotes, each truncated to an optional maximum length given as the second argument. An optional third argument can specify a delimiter to place between the elements of the list or array given in the first argument. A comma and a space will be used by default if no delimiter is specified.

#!/usr/bin/perl -w use DBI; my @test = ("This is a test.", "Another test"); print "Test: " . DBI::neat_list(\@test, 8); exit;

Here are the results of this script:

Test: 'This is...', 'Another...'

parse_dsn( )

DBI->parse_dsn($data_source_name)

This returns the components of the DBI Data Source Name (DSN) values: the scheme (i.e., dbi); the driver (i.e, $ENV{DBI_DRIVER}); an optional attribute string; a reference to a hash with the attribute names and values; and the DSN DBI string.

... my ($scheme, $driver, $attributes_string,        $attributes_hash, $driver_dsn) = DBI->parse_dsn($dsn);

parse_trace_flag( )

$handle->parse_trace_flag($settings)

This returns a bit flag for a trace flag name given as an argument to this method. To parse a list of trace flags, see parse_trace_flags( ).

parse_trace_flags( )

$handle->parse_trace_flags($settings)

Use this to parse a string given as an argument that contains a list of trace settings. These settings are either trace flag names or integers representing trace levels.

ping( )

$database_handle->ping( )

Use this to determine if a MySQL server is still running and the database connection is still available. There are no arguments for this method.

... $results = $dbh->ping( ); print "Results:  $results"; ...

prepare( )

$statement_handle = $database_handle->prepare(statement[, \%attribute])

This creates a statement handle by preparing an SQL statement given as the first argument for subsequent execution with execute(). It returns a reference to the statement handle. The second argument of this function is a hash of attributes.

my $dbh = DBI->connect ("$data_source","$user","$pwd") my $sql_stmnt = "SELECT * FROM books"; my $sth = $dbh->prepare($sql_stmnt,                         {RaiseError => 1, ChopBlanks => 1});

In this example, warning messages are enabled and trailing spaces of fixed-width character columns are trimmed. See the end of this chapter for a list of attributes.

prepare_cached( )

$database_handle->prepare_cached($sql_standard[, \%attribute, $active])

This creates a statement handle like prepare( ), but stores the resulting statement handle in a hash. Attributes for a statement handle may be given in the second argument in the form of a hash. The third argument of the method changes the behavior of the handle if an active statement handle is already in the cache. Table 12-1 lists the four choices for this argument.

Table 12-1. Active argument for prepare_cached( )

Active value

Result

0

Warning messages will be issued, and finish( ) for the statement handle will be employed.

1

No warning will be displayed, but finish( ) will be executed.

2

Disables checking for an active handle.

3

Causes the new statement handle to replace the active one.


primary_key( )

$database_handle->primary_key($catalog, $schema, $table)

This returns a list of primary key column names for a given table.

primary_key_info( )

$database_handle->primary_key_info($catalog, $schema, $table)

This returns a statement handle for fetching information about primary key columns for a table.

quote( )

$database_handle->quote(string)

Use this to escape special characters contained in a given string. It's useful in SQL statements particularly for unknown user input that might contain metacharacters that would cause undesirable behavior by MySQL.

... my $comment = "Henry James' book \"The Muse\" is wonderful!"; my $quoted_comment = $dbh->quote($comment); my $sql_stmnt = "UPDATE books SET comment = ?"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute($quoted_comment);

quote_identifier( )

$database_handle->quote_identifier($name) $database_handle->quote_identifier($catalog, $schema,  $database_handle->quote_identifier($catalog, $schema,                                     $table, \%attributes)

Use this to escape special characters of an identifier for use in an SQL statement. In the first syntax, the one parameter could be an identifier such as a table name. For the second syntax, the catalog name or link is given for the first parameter, a schema for the second, a table name for the third, and database attributes for the fourth. Here is an example of the first syntax:

my $sql_stmnt = "SHOW indexes FROM "                 . $dbh->quote_identifier($table);

Below is an example of the second syntax:

my $table_identifier = $dbh->quote_identifier('link', 'schema', 'table');

The value of $table_identifier would be "schema"."table"@"link".

rollback( )

$database_handle->rollback( )

Use this to undo the last change to an InnoDB or BDB table. It requires that the database handle was created with the attribute of AutoCommit set to false or 0, and that the change was not committed using the commit( ) function.

rows( )

$statement_handle->rows( )

This returns the number of rows affected by the last statement handle executed. It works with UPDATE, INSERT, and DELETE dependably. It doesn't work effectively with SELECT statements unless they select all rows in a table. If the number of rows is unknown, -1 is returned. There are no arguments to this method.

... my $sql_stmnt = "UPDATE books SET author = 'Robert B. Parker'                  WHERE author = 'Robert Parker'"; my $sth = $dbh->prepare($sql_stmnt); $sth->execute( ); my $change_count = $sth->rows( ); print "$change_count rows were changed.";

This script displays the following when run:

2 rows were changed

selectall_arrayref( )

$database_handle->selectall_arrayref($statement, \%attributes, @bind_values)

This returns a reference to an array of references to arrays containing data for each row retrieved from the results of an SQL statement given. This method combines prepare( ), execute(), and fetchall_arrayref( ). An optional second argument can specify any of the attributes allowed for a statement handle. If placeholders are used in the SQL statement, their values must be given as an array for the third argument.

my $sql_stmnt = "SELECT title, author                  FROM books WHERE book_id = ?"; my $books = $dbh->selectall_arrayref($sql_stmnt,                                       undef, '1234'); foreach my $book (@$books) {    my ($title, $author) = @$book;    print "$title by $author \n"; }

selectall_hashref( )

$database_handle->selectall_hashref($statement, $key_field,                                      \%attributes, @bind_values)

This returns a reference to a hash of references to hashes, one for each row from the results of an SQL statement given. This method combines prepare(), execute( ), and fetchall_hashref( ). An optional second argument can specify any of the attributes allowed for a statement handle. If placeholders are used in the SQL statement, their values must be given as an array for the third argument.

... my $sql_stmnt = "SELECT rec_id, title, author                  FROM books"; my $books = $dbh->selectall_hashref($sql_stmnt, 'book_id'); foreach my $book_id (keys %$books) {    print "$books->{$book_id}{title}           by $books->{$book_id}{author} \n"; }

selectcol_arrayref( )

$database_handle->selectcol_arrayref($sql_statement[,                   \%attributes, @bind_values])

This returns a reference to an array containing a value in the first column of each row selected. The SQL statement is given as the first argument of the function. This can be particularly useful if the first column is a key field. This function performs prepare( ) and execute( ) on the SQL statement.

... my $sql_stmnt = "SELECT * FROM books"; my $book = $dbh->selectcol_arrayref($sql_stmnt); foreach my $author_id (@$book){    print "$author_id \n"; }

selectrow_array( )

$database_handle->selectrow_array($sql_statement[,                                    \%attributes, @values])

This returns one row from the results of an SQL statement in the form of an array, where each column returned is represented by an element of the array, in order. This method combines prepare( ), execute(), and fetchrow_array( ). No statement handle is created, so finish( ) is unnecessary. An optional second argument can specify any of the attributes allowed for a statement handle. If placeholders are used in the SQL statement, their values must be given as an array for the third argument.

... my $sql_stmnt = "SELECT title, author                  FROM books WHERE book_id = ?"; my ($title, $author) = $dbh->selectrow_array(                              $sql_stmnt, undef, '1234'); print "$title by $author \n";

No attributes are given for the SQL statement, so undef is used for the second argument. The third argument provides the book_id number for the placeholder in the SQL statement.

selectrow_arrayref( )

$database_handle->selectrow_arrayref($sql_statement[,                                       \%attributes, @values])

This returns a reference to an array of one row from the results of an SQL statement given. This method combines prepare( ), execute(), and fetchrow_arrayref( ). An optional second argument can specify any of the attributes allowed for a statement handle. If placeholders are used in the SQL statement, their values must be given as an array for the third argument.

... my $sql_stmnt = "SELECT title, author                  FROM books WHERE book_id = ?"; my $book = $dbh->selectrow_arrayref($sql_stmnt,                                     undef, '1234'); my ($title, $author) = @$book; print "$title by $author \n";

selectrow_hashref( )

$database_handle->selectrow_hashref($sql_statement[,                                      \%attributes, @values])

This returns a reference to a hash of one row from the results of an SQL statement given. This method combines prepare( ), execute(), and fetchrow_hashref( ). Attributes that may be given for a statement handle may be provided in a hash for the second argument of this method. If placeholders are used in the SQL statement, their values may be given as an array for the third argument.

... my $sql_stmnt = "SELECT title, author                  FROM books WHERE book_id = ?"; my $book_ref = $dbh->selectrow_hashref($sql_stmnt,                                        undef, '1234'); print "$book_ref->{title} by $book_ref->{author} \n";

Notice that the method captures the names of the columns as the keys to the values in the hash generated.

set_err( )

$handle->set_err($err, $errstr[,                   $state, $method, $return_value])

This sets the values for err, errstr, and state for the handle.

state( )

$handle->state( )

This returns the error code of an error in a five-character format.

table_info( )

$database_handle->table_info($catalog, $schema, $table,                               $type[, \%attributes])

This returns a statement handle for fetching information about a table in a database.

... my $dbinfo = $dbh->table_info( ); while( my($qualifier,$owner,$name,$type,$remarks) =        $dbinfo->fetchrow_array( )) {    foreach ($qualifier,$owner,$name,$type,$remarks) {       $_ = '' unless defined $_;    }    print "Info:  $qualifier $owner $name $type $remarks \n"; }

table_info_all( )

$database_handle->table_info_all($catalog, $schema, $table,                                   $type[, \%attributes])

This returns a statement handle for fetching information about a table in a database.

tables( )

$database_handle->tables($catalog, $schema, $table, $type)

This returns a list of tables for a database handle.

trace( )

$handle->trace(level[, log])

This sets the trace level for a handle. A level of 0 disables tracing; level 1 traces the execution of the database handle; level 2 provides more details including parameter values. If a filename is given as the second argument, trace information will be appended to that logfile instead of stderr.

trace_msg( )

$handle->TRace_msg(message[, level])

This adds text given in first argument to trace data. A minimum trace level (see trace() method) required for the message to be used may be specified as a second argument.

type_info( )

$database_handle->type_info([$data_type])

This returns a hash containing information on a given datatype.

... my $dbinfo = $dbh->type_info( ); while(my($key, $value) = each(%$dbinfo)){    print "$key => $value\n"; }

type_info_all( )

$database_handle->type_info_all( )

This returns a reference to an array of all datatypes supported by the driver.



MySQL in a Nutshell
MYSQL in a Nutshell (In a Nutshell (OReilly))
ISBN: 0596514336
EAN: 2147483647
Year: 2006
Pages: 134
Authors: Russell Dyer

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net