Database Handle Methods | Mastering MySQL 4

These methods are available to the database handle, so you need to open a connection before you can use any of them. Some of these methods return a statement handle, which you can then further process with the statement handle methods listed later.

begin_work

$rc  = $dbh->begin_work   or die $dbh->errstr;

Begins a transaction and turns AutoCommit off until the transaction ends with commit() or rollback().

column_info

  $sth = $dbh->column_info($catalog,$schema,$table,$column);

An experimental method that returns an active statement handle for getting information about columns.

commit

$rc  = $dbh->commit;

Commits the current transaction. The AutoCommit parameter needs to be off for this to have any effect.

disconnect

$rc = $dbh->disconnect;

Uses the specified database handle to disconnect from the database. Returns true if successful or false if not.

The method does not define whether to roll back or commit currently open transactions, so make sure you specifically commit or roll them back in your applications before calling disconnect.

do

$rv = $dbh->do($statement [,\%attributes [,@bind_values]]);

Prepares and executes an SQL statement, returning the number of rows affected. Returns 0E0 (treated as true) if no rows are affected or undef if an error occurs.

Usually used for queries that do not return results (such as INSERT and UPDATE) and that do not make use of placeholders. This method is faster than the equivalent prepare() and execute() methods.

It returns an integer.

For example:

my $hostname = 'localhost'; my $database = 'firstdb'; my $username = 'guru2b'; my $password = 'g00r002b'; #Connect to the database  my $dbh = DBI->connect("dbi:mysql:$database:$hostname", $username,–  $password) or die $DBI::errstr; $sql = "INSERT INTO customer(id,surname) VALUES(11,'Sandman')"; $dbh->do($sql);

foreign_key_info

$sth = $dbh->foreign_key_info($pk_catalog, $pk_schema, $pk_table–  [, $fk_catalog, $fk_schema, $fk_table]);

An experimental method that returns a statement handle for getting foreign key information. The arguments $pk_catalog, $pk_schema, and $pk_table specify the primary key table. The arguments $fk_catalog, $fk_schema, and $fk_table specify the foreign key table.

The returned result depends on which tables are supplied. If only the foreign key table is supplied (by passing undef as the primary key argument), the results contain all foreign keys in that table and the associated primary keys. If only the primary key table is supplied, the results contain the primary key from that table and all associated foreign keys. If both tables are supplied, the results contain the foreign key from the foreign key table that refers to the primary key of the primary key table.

get_info

$value = $dbh->get_info( $info_type );

An experimental method returning implementation information.

ping

$rc = $dbh->ping;

Checks if the database is still running and the connection is active.

prepare

$sth = $dbh->prepare($statement [, \%attributes])

Returns a reference to a statement handle and readies an SQL statement for execution (with the execute method). You'd usually prepare statements that are to return results, such as SELECT and DESCRIBE.

prepare_cached

$sth = $dbh->prepare_cached($statement [, \%attributes,[ $allow_active]]);

The same as prepare, except that the statement handle is stored in a hash so that future calls to identical arguments will return the same handle. The $allow_active argument has three settings. The default, 0, generates a warning and calls finish() on the statement handle before it is returned, and 1 calls finish() but suppresses the warning. If set to 2, the DBI will not call finish() before returning the statement.

primary_key

@key_column_names = $dbh->primary_key($catalog, $schema, $table);

An experimental interface to the primary_key_info method that returns an array of fieldnames that make up the primary key, in sequence, for the specified table.

primary_key_info

$sth = $dbh->primary_key_info($catalog, $schema, $table);

An experimental method for getting information about primary key columns.

quote

$quoted_string = $dbh->quote($string [,$data_type])

Returns a string with any special characters escaped (such as single or double quotes) and adds outer quotation marks. If the data type is specified, Perl will use it to determine the required quoting behavior.

quote_identifier

$sql = $dbh->quote_identifier( $name1[ , $name2, $name3, \%attributes ]);

Escapes any special characters in an identifier (such as a fieldname) for use in a query.

rollback

$rc  = $dbh->rollback;

Rolls back the current transaction. The AutoCommit parameter needs to be off for this to have any effect.

selectall_arrayref

$ary_ref  = $dbh->selectall_arrayref($statement [, \%attributes [,@bind_values]]);

A method that combines the prepare(), execute(), and fetchall_arrayref() methods into one for ease of use. It returns a reference to an array containing a reference to an array for each row of data returned from the query. The statement can also be a statement handle that has already been prepared, in which case the method does not do a prepare().

You can set other arguments to pass to the selectall_arrayref() method in %attributes.

selectall_hashref

$hash_ref = $dbh->selectall_hashref($statement, $key_field–  [, \%attributes [,@bind_values]]);

A method that combines the prepare(), execute(), and fetchall_hashref() methods into one for ease of use. It returns a reference to a hash containing an entry for each row returned from the query. The key for each field is specified by $key_field, and the value is a reference to a hash. The statement can also be a statement handle that has already been prepared, in which case the method skips the prepare().

selectcol_arrayref

$ary_ref = $dbh->selectcol_arrayref($statement [, \%attributes [,@bind_values]]);

A method that combines the prepare() and execute() methods with fetching one or more columns from all rows returned from the query. It returns a reference to an array containing the values of the columns from each row. The statement can also be a statement handle that has already been prepared, in which case the method skips the prepare().

By default it returns the first columns from each row, but it can return more with a Columns attribute, which is a reference to an array containing the column number to use.

For example:

# perform a query and return the two columns my $array_ref = $dbh->selectcol_arrayref("SELECT first_name, surname–  FROM customer", { Columns=>[1,2] }); # create the hash from key-value pairs so $hash{$first_name} => surname my %hash = @$array_ref;

selectrow_array

@row_ary  = $dbh->selectrow_array($statement [, \%attributes [,@bind_values]]);

A method that combines the prepare(), execute(), and fetchrow_array() methods into one for ease of use. It returns the first row of data returned from the query. The statement can also be a statement handle that has already been prepared, in which case the method does not do a prepare().

selectrow_arrayref

$ary_ref  = $dbh->selectrow_arrayref($statement [, \%attributes [,@bind_values]]);

A method that combines the prepare(), execute(), and fetchrow_arrayref() methods into one for ease of use. The statement can also be a statement handle that has already been prepared, in which case the method does not do a prepare().

selectrow_hashref

$hash_ref = $dbh->selectrow_hashref($statement [, \%attributes [,@bind_values]]);

A method that combines the prepare(), execute(), and fetchrow_hashref() methods into one for ease of use. It returns the first row of data returned from the query. The statement can also be a statement handle that has already been prepared, in which case the method does not do a prepare().

table_info

$sth = $dbh->table_info($catalog, $schema, $table, $type [, \%attributes]);

An experimental method that returns an active statement handle for getting information about tables and views from the database.

tables

@names = $dbh->tables($catalog, $schema, $table, $type);

An experimental interface to the table_info() method that returns an array of table names.

type_info

@type_info = $dbh->type_info($data_type);

An experimental method that returns an array of hash references containing information about data type variants.

type_info_all

$type_info_all = $dbh->type_info_all;

An experimental method that returns a reference to an array containing information about data types supported by the database and driver.