PAB3::DB - Common interface for database communication
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();
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 up the execution time if 'execute' is called more times on the same statement. It also makes available sending binary data to along with the 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.
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"; }
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"; }
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', );
Reconnect to the database server.
Closes the currently active connection
Sets the default character set to be used when sending data from and to the database server.
$charset
The charset to be set as default.
Returns TRUE on success or FALSE on failure.
Gets the default character.
Returns the default charset or NULL on error.
Returns the last error code for the most recent function call that can succeed or fail.
An error code value for the last call, if it failed. zero means no error occurred.
Returns the last error message for the most recent function call that can succeed or fail.
A string that describes the error. An empty string if no error occurred.
Sends a SQL statement to the currently active database on the server.
$statement
The query, as a string.
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.
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.
This parameter can include one or more parameter markers in the SQL statement by embedding question mark (?) characters at the appropriate positions.
Returns a statement class ($stmt) or FALSE if an error occured.
See Also
execute(), bind_param()
Binds a value to a prepared statement as parameter
$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
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.
@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.
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.
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().
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.
Returns the auto generated id used in the last query or statement.
$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.
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.
Get a result row as an enumerated array. fetch_array() is a synonym for fetch_row().
Paramters
None
Returns an array of values that corresponds to the fetched row or NULL if there are no more rows in result set.
Fetch a result row as an associative array (hash).
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.
Fetch the first column of each row in the result set a an array.
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.
Returns the lengths of the columns of the current row in the result set.
An array of integers representing the size of each column (not including terminating null characters). FALSE if an error occurred.
Gets the number of rows in a result.
Returns number of rows in the result set.
Gets the actual position of row cursor in a result (Starting at 0).
Returns the actual position of row cursor in a result.
Sets the actual position of row cursor in a result (Starting at 0).
$offset
Absolute row position. Valid between 0 and num_rows() - 1.
Returns the previous position of row cursor in a result.
Returns an array of field names representing in a result set.
Returns an array of field names or FALSE if no field information is available.
Gets the number of fields (columns) in a result.
Returns number of fields in the result set.
Returns the next field in the result.
If set, moves the field cursor to this position.
Returns a hash which contains field definition information or FALSE if no field information is available.
Gets the actual position of field cursor in a result (Starting at 0).
Returns the actual position of field cursor in the result.
Sets the actual position of field cursor in the result (Starting at 0).
Absolute field position. Valid between 0 and num_fields() - 1.
Returns the previous position of field cursor in the result.
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;
Turns on or off auto-commit mode on queries for the database connection.
$mode
Whether to turn on auto-commit or not.
Turns off auto-commit mode for the database connection until transaction is finished.
Commits the current transaction for the database connection.
Rollbacks the current transaction for the database.
Returns an array with names of drivers found.
@drivers = PAB3::DB->show_drivers();
Gets an array with names of catalogs found.
$wild
The argument may accept search patterns according to the database/driver, for example: $wild = '%FOO%'.
An array with names of catalogs.
Gets an array with information about tables and views that exist in the database.
The schema to search in.
The catalog to search in.
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';
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.
Gets an array with information about fields (columns) in a specified table.
The table to search in.
The value of $field may accept search patterns according to the database/driver, for example: $field = '%FOO%';
An array with information about fields in the specified table.
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.
Gets an array with information about indexes (keys) in a specified table.
An array with information about indexes in the specified table.
NAME | COLUMN | TYPE
NAME
The name of the index.
The type of the key. Possible values are:
1 - Primary key 2 - Unique key 3 - Other Index
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".
Value to be quoted.
The quoted value with adding the required type of outer quotation marks.
$s = $db->quote( "It's our time" ); # $s should be something like this: 'It''s our time'
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.
One or more values to be quoted.
The quoted string with adding the required type of outer quotation marks.
# 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".*
For additional database functions look at PAB3::DB::Max.
Christian Mueller <christian_at_hbr1.com>
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.