NAME

PAB3::DB - Common interface for database communication

SYNOPSIS

use PAB3::DB;

$db = PAB3::DB->connect( %arg );

$res = $db->query( $sql );

$stmt = $db->prepare( $sql );
$stmt->bind_param( $p_num, $value );

$res = $stmt->execute();
$res = $stmt->execute( @bind_values );

@row = $res->fetch_row();
@row = $stmt->fetch_row();

@row = $res->fetch_array();
@row = $stmt->fetch_array();

%row = $res->fetch_hash();
%row = $stmt->fetch_hash();

@col = $res->fetch_col();
@col = $stmt->fetch_col();

@name = $res->fetch_names();
@name = $stmt->fetch_names();

@len = $res->fetch_lengths();
@len = $stmt->fetch_lengths();

$num_rows = $res->num_rows();
$num_rows = $stmt->num_rows();

$row_index = $res->row_tell();
$row_index = $stmt->row_tell();

$res->row_seek( $row_index );
$stmt->row_seek( $row_index );

$num_fields = $res->num_fields();
$num_fields = $stmt->num_fields();

$res->field_seek( $row_index );
$stmt->field_seek( $row_index );

@field = $res->fetch_field();
@field = $stmt->fetch_field();

$hr = $db->begin_work();
$hr = $db->commit();
$hr = $db->rollback();

$str   = $db->error();
$errno = $db->errno();

$hr = $db->set_charset( $charset );
$charset = $db->get_charset();

$quoted = $db->quote( $arg );
$quoted = $db->quote_id( ... );

# for query method
$uv = $db->insert_id();
$uv = $db->insert_id( $field );
$uv = $db->insert_id( $field, $table );
$uv = $db->insert_id( $field, $table, $schema );

# for execute method
$uv = $stmt->insert_id();
$uv = $stmt->insert_id( $field );
$uv = $stmt->insert_id( $field, $table );
$uv = $stmt->insert_id( $field, $table, $schema );

@drivers = PAB3::DB->show_drivers();

@catalogs = $db->show_catalogs();
@catalogs = $db->show_catalogs( $wild );

@tables = $db->show_tables();
@tables = $db->show_tables( $schema );
@tables = $db->show_tables( $schema, $catalog );
@tables = $db->show_tables( $schema, $catalog, $wild );

@fields = $db->show_fields( $table );
@fields = $db->show_fields( $table, $schema );
@fields = $db->show_fields( $table, $schema, $catalog );
@fields = $db->show_fields( $table, $schema, $catalog, $wild );

@index = $db->show_index( $table );
@index = $db->show_index( $table, $schema );
@index = $db->show_index( $table, $schema, $catalog );

$rv = $db->reconnect();

$rv = $db->close();

DESCRIPTION

PAB3::DB provides an interface for database communication.

SQL statements can be submitted in two different ways, with 'query' method or with 'prepare' and 'execute' methods. The 'query' method is more simple and should run faster for a single call. The 'prepare' and 'execute' methods are more secure and can speed the execution time if 'execute' is called more times on the same statement.

Most functions may be used in different ways. The documentation uses the following variables to define different classes. $db defines a database class, $res defines a result class and $stmt defines a statement class.

EXAMPLES

Using "query" method

use PAB3::DB;

$db = PAB3::DB->connect(
    'driver' => 'Mysql',
    'host' => 'localhost',
    'user' => 'root',
    'auth' => '',
    'db' => 'test',
);

$db->set_charset( 'utf8' );

$res = $db->query( 'select * from table' );

@names = $res->fetch_names();
print join( '|', @names ), "\n";

while( @row = $res->fetch_row() ) {
    print join( '|', @row ), "\n";
}

Using "prepare" and "execute" methods

use PAB3::DB;

$db = PAB3::DB->connect(
    'driver' => 'Mysql',
    'host' => 'localhost',
    'user' => 'root',
    'auth' => '',
    'db' => 'test',
);

$db->set_charset( 'utf8' );

$stmt = $db->prepare( 'select * from table where field = ?' );
# bind "foo" to parameter 1 and execute
$res = $stmt->execute( 'foo' );

@names = $res->fetch_names();
print join( '|', @names ), "\n";

while( @row = $res->fetch_row() ) {
    print join( '|', @row ), "\n";
}

METHODS

Connection Control Methods

$db = connect ( %arg )

$db = new ( %arg )

Opens a connection to a database server and returns a new class.

Parameters

%arg

A combination of the following parameters:

driver     => drivername, default is Mysql
host       => database server
user       => authorized username
auth       => authorization password
db         => database
port       => port for tcp/ip connection
options    => hashref with driver specific options, like {reconnect => 1}
warn       => warns on error, default is ON
die        => dies on error, default is ON

A concrete definition of these and additional parameters could by found in the drivers documentation.

Return Values

Returns a PAB3::DB class ($db) on success or FALSE on failure.

Examples

# loading the driver on startup will speed up the connection process
# use PAB3::DB::Driver::Postgres ();

$db = PAB3::DB->connect(
    'driver' => 'Postgres',
    'host' => 'localhost',
    'user' => 'postgres',
    'auth' => 'postgres',
    'db' => 'mydb',
);

$db -> reconnect ()

Reconnect to the database server.

$db -> close ()

Closes the currently active connection

$db -> set_charset ( $charset )

Sets the default character set to be used when sending data from and to the database server.

Parameters

$charset

The charset to be set as default.

Return Values

Returns TRUE on success or FALSE on failure.

$db -> get_charset ()

Gets the default character.

Return Values

Returns the default charset or NULL on error.

$db -> errno ()

Returns the last error code for the most recent function call that can succeed or fail.

Return Values

An error code value for the last call, if it failed. zero means no error occurred.

$db -> error ()

Returns the last error message for the most recent function call that can succeed or fail.

Return Values

A string that describes the error. An empty string if no error occurred.

Command Execution Methods

$res = $db -> query ( $statement )

Sends a SQL statement to the currently active database on the server.

Parameters

$statement

The query, as a string.

Return Values

For selectives queries query() returns a result class ($res) on success, or FALSE on error.

For other type of SQL statements, UPDATE, DELETE, DROP, etc, query() returns TRUE on success or FALSE on error.

$stmt = $db -> prepare ( $statement )

Prepares the SQL query pointed to by the null-terminated string query, and returns a statement handle to be used for further operations on the statement. The query must consist of a single SQL statement.

Parameters

$statement

The query, as a string.

This parameter can include one or more parameter markers in the SQL statement by embedding question mark (?) characters at the appropriate positions.

Return Values

Returns a statement class ($stmt) or FALSE if an error occured.

See Also

execute(), bind_param()

$stmt -> bind_param ( $p_num )

$stmt -> bind_param ( $p_num, $value )

$stmt -> bind_param ( $p_num, $value, $type )

Binds a value to a prepared statement as parameter

Parameters

$p_num

The number of parameter starting at 1.

$value

Any value.

$type

A string that contains one character which specify the type for the corresponding bind value:

Character Description
---------------------
i   corresponding value has type integer 
d   corresponding value has type double 
s   corresponding value has type string 
b   corresponding value has type binary

Return Values

Returns TRUE on success or FALSE on failure.

$res = $stmt -> execute ()

$res = $stmt -> execute ( @bind_values )

Executes a query that has been previously prepared using the prepare() function. When executed any parameter markers which exist will automatically be replaced with the appropiate data.

Parameters

@bind_values

An array of values to bind. Values bound in this way are usually treated as "string" types unless the driver can determine the correct type, or unless bind_param() has already been used to specify the type.

Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, query returns a result class ($res) on success, or FALSE on error. The result class is bound to the statement. If the result class is not used, it will be freed when the statement is freed. Some drivers do not support different results in a statement. In this case the return value could be the statement class.

For other type of SQL statements, UPDATE, DELETE, DROP, etc, query returns TRUE on success or FALSE on error.

Retrieving Query Result Information

$db -> affected_rows ()

$stmt -> affected_rows ()

Gets the number of affected rows in a previous SQL operation After executing a statement with query() or execute(), returns the number of rows changed (for UPDATE), deleted (for DELETE), or inserted (for INSERT). For SELECT statements, affected_rows() works like num_rows().

Return Values

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed.

$db -> insert_id ()

$db -> insert_id ( $field )

$db -> insert_id ( $field, $table )

$db -> insert_id ( $field, $table, $schema )

$db -> insert_id ( $field, $table, $schema, $catalog )

$stmt -> insert_id ()

$stmt -> insert_id ( $field )

$stmt -> insert_id ( $field, $table )

$stmt -> insert_id ( $field, $table, $schema )

$stmt -> insert_id ( $field, $table, $schema, $catalog )

Returns the auto generated id used in the last query or statement.

Parameters

$field

The field to retrieve the generated id from.

$table

The table where the field is located.

$schema

The schema where the table is located.

$catalog

The catalog where the table is located.

Return Values

The value of an AUTO_INCREMENT (IDENDITY,SERIAL) field that was updated by the previous query. Returns NULL if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.

Accessing Rows in a Result Set

$res -> fetch_row ()

$res -> fetch_array ()

$stmt -> fetch_row ()

$stmt -> fetch_array ()

Get a result row as an enumerated array. fetch_array() is a synonym for fetch_row().

Paramters

None

Return Values

Returns an array of values that corresponds to the fetched row or NULL if there are no more rows in result set.

$res -> fetch_hash ()

$stmt -> fetch_hash ()

Fetch a result row as an associative array (hash).

Paramters

None

Return Values

Returns an associative array (hash) of values representing the fetched row in the result set, where each key in the hash represents the name of one of the result set's columns or NULL if there are no more rows in resultset.

If two or more columns of the result have the same field names, the last column will take precedence. To access the other columns of the same name, you either need to access the result with numeric indices by using fetch_row() or add alias names.

$res -> fetch_col ()

$stmt -> fetch_col ()

Fetch the first column of each row in the result set a an array.

Paramters

None

Return Values

Returns an array of values that corresponds to the first column of each row in the result set or FALSE if no data is available.

$res -> fetch_lengths ()

Returns the lengths of the columns of the current row in the result set.

Paramters

None

Return Values

An array of integers representing the size of each column (not including terminating null characters). FALSE if an error occurred.

$res -> num_rows ()

$stmt -> num_rows ()

Gets the number of rows in a result.

Paramters

None

Return Values

Returns number of rows in the result set.

$res -> row_tell ()

Gets the actual position of row cursor in a result (Starting at 0).

Paramters

None

Return Values

Returns the actual position of row cursor in a result.

$res -> row_seek ( $offset )

$stmt -> row_seek ( $offset )

Sets the actual position of row cursor in a result (Starting at 0).

Paramters

$offset

Absolute row position. Valid between 0 and num_rows() - 1.

Return Values

Returns the previous position of row cursor in a result.

Accessing Fields (Columns) in a Result Set

$res -> fetch_names ()

$stmt -> fetch_names ()

Returns an array of field names representing in a result set.

Paramters

None

Return Values

Returns an array of field names or FALSE if no field information is available.

$res -> num_fields ()

$stmt -> num_fields ()

Gets the number of fields (columns) in a result.

Paramters

None

Return Values

Returns number of fields in the result set.

$res -> fetch_field ()

$res -> fetch_field ( $offset )

$stmt -> fetch_field ()

$stmt -> fetch_field ( $offset )

Returns the next field in the result.

Paramters

$offset

If set, moves the field cursor to this position.

Return Values

Returns a hash which contains field definition information or FALSE if no field information is available.

$res -> field_tell ()

$stmt -> field_tell ()

Gets the actual position of field cursor in a result (Starting at 0).

Paramters

None

Return Values

Returns the actual position of field cursor in the result.

$res -> field_seek ( $offset )

$stmt -> field_seek ( $offset )

Sets the actual position of field cursor in the result (Starting at 0).

Paramters

$offset

Absolute field position. Valid between 0 and num_fields() - 1.

Return Values

Returns the previous position of field cursor in the result.

Freeing Results or Statements

Results and Statements will freed when its classes are destroying. To free $res or $stmt, just undefine the variables.

Example

$res = $db->query( 'select 1' );
# free the result
undef $res;

Transaction Methods

$db -> auto_commit ( $mode )

Turns on or off auto-commit mode on queries for the database connection.

Parameters

$mode

Whether to turn on auto-commit or not.

Return Values

Returns TRUE on success or FALSE on failure.

$db -> begin_work ()

Turns off auto-commit mode for the database connection until transaction is finished.

Parameters

None

Return Values

Returns TRUE on success or FALSE on failure.

$db -> commit ()

Commits the current transaction for the database connection.

Parameters

None

Return Values

Returns TRUE on success or FALSE on failure.

$db -> rollback ()

Rollbacks the current transaction for the database.

Parameters

None

Return Values

Returns TRUE on success or FALSE on failure.

Information Retrieval Functions

show_drivers ()

Returns an array with names of drivers found.

Example

@drivers = PAB3::DB->show_drivers();

$db -> show_catalogs ()

$db -> show_catalogs ( $wild )

Gets an array with names of catalogs found.

Parameters

$wild

The argument may accept search patterns according to the database/driver, for example: $wild = '%FOO%'.

Return Values

An array with names of catalogs.

$db -> show_tables ()

$db -> show_tables ( $schema )

$db -> show_tables ( $schema, $catalog )

$db -> show_tables ( $schema, $catalog, $type )

Gets an array with information about tables and views that exist in the database.

Parameters

$schema

The schema to search in.

$catalog

The catalog to search in.

$type

The value of $type is a comma-separated list of one or more types of tables to be returned in the result set.

$type = 'table';
$type = 'table,view';

Return Values

An array with information about tables and views.

The array should contain the following fields in the order shown below.

TABLE | SCHEMA | CATALOG | TYPE

TABLE

Name of the table (or view, synonym, etc).

SCHEMA

The name of the schema containing the TABLE value. This field can be NULL if not applicable.

CATALOG

Table catalog identifier. This field can be NULL if not applicable.

TYPE

One of the following: "table", "view" or a type identifier that is specific to the data source.

$db -> show_fields ( $table )

$db -> show_fields ( $table, $schema )

$db -> show_fields ( $table, $schema, $catalog )

$db -> show_fields ( $table, $schema, $catalog, $field )

Gets an array with information about fields (columns) in a specified table.

Parameters

$table

The table to search in.

$schema

The schema to search in.

$catalog

The catalog to search in.

$field

The value of $field may accept search patterns according to the database/driver, for example: $field = '%FOO%';

Return Values

An array with information about fields in the specified table.

The array should contain the following fields in the order shown below.

COLUMN | NULLABLE | DEFAULT | IS_PRIMARY | IS_UNIQUE | TYPENAME | AUTOINC

COLUMN

The field identifier.

NULLABLE

Indicates that the field does accept 'NULL'.

DEFAULT

The default value of the column.

IS_PRIMARY

Indicates that the field is part of the primary key.

IS_UNIQUE

Indicates that the field is part of a unique key.

TYPENAME

A data source dependent data type name.

AUTOINC

Indicates that the field will automatically be incremented.

$db -> show_index ( $table )

$db -> show_index ( $table, $schema )

$db -> show_index ( $table, $schema, $catalog )

Gets an array with information about indexes (keys) in a specified table.

Parameters

$table

The table to search in.

$schema

The schema to search in.

$catalog

The catalog to search in.

Return Values

An array with information about indexes in the specified table.

The array should contain the following fields in the order shown below.

NAME | COLUMN | TYPE

NAME

The name of the index.

COLUMN

The field identifier.

TYPE

The type of the key. Possible values are:

1 - Primary key
2 - Unique key
3 - Other Index

Other Functions

$db -> quote ( $value )

Quote a value for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks.

The quote() method should not be used with "Placeholders and Bind Values".

Parameters

$value

Value to be quoted.

Return Values

The quoted value with adding the required type of outer quotation marks.

Examples

$s = $db->quote( "It's our time" );
# $s should be something like this: 'It''s our time'

$db -> quote_id ( $field )

$db -> quote_id ( $table, $field )

$db -> quote_id ( $schema, $table, $field )

$db -> quote_id ( $catalog, $schema, $table, $field )

Quote an identifier (table name etc.) for use in an SQL statement, by escaping any special characters it contains and adding the required type of outer quotation marks.

Parameters

One or more values to be quoted.

Return Values

The quoted string with adding the required type of outer quotation marks.

Examples

# using driver 'Postgres'

$s = $db->quote_id( 'table' );
# $s should be "table"

$s = $db->quote_id( 'table', 'field' );
# $s should be "table"."field"

$s = $db->quote_id( 'table', '*' );
# $s should be "table".*

AUTHORS

Christian Mueller <christian_at_hbr1.com>

COPYRIGHT

The PAB3::DB module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

To install PAB3, copy and paste the appropriate command in to your terminal.

cpanm

cpanm PAB3

CPAN shell

perl -MCPAN -e shell
install PAB3

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

EXAMPLES

Using "query" method

Using "prepare" and "execute" methods

METHODS

Connection Control Methods

Command Execution Methods

Retrieving Query Result Information

Accessing Rows in a Result Set

Accessing Fields (Columns) in a Result Set

Freeing Results or Statements

Transaction Methods

Information Retrieval Functions

Other Functions

SEE ALSO

AUTHORS

COPYRIGHT

Module Install Instructions