NAME

PAB3::DB::Driver::Mysql - Perl5 wrapper to the mysql5+ client libary and driver for the PAB3::DB class

SYNOPSIS

use PAB3::DB::Driver::Mysql;
# functions and constants are exported by default

$linkid = mysql_connect();
$linkid = mysql_connect( $server );
$linkid = mysql_connect( $server, $user );
$linkid = mysql_connect( $server, $user, $pass );
$linkid = mysql_connect( $server, $user, $pass, $db );
$linkid = mysql_connect( $server, $user, $pass, $db, $client_flag );

$resid = mysql_query( $statement );
$resid = mysql_query( $linkid, $statement );

$stmtid = mysql_prepare( $statement );
$stmtid = mysql_prepare( $linkid, $statement );

$rv = mysql_bind_param( $stmtid, $p_num );
$rv = mysql_bind_param( $stmtid, $p_num, $value );
$rv = mysql_bind_param( $stmtid, $p_num, $value, $type );

$stmtid = $resid = mysql_execute( $stmtid );
$stmtid = $resid = mysql_execute( $stmtid, @bind_values );

@row = mysql_fetch_row( $resid );
@row = mysql_fetch_row( $stmtid );
@row = mysql_fetch_array( $resid );
@row = mysql_fetch_array( $stmtid );

@col = mysql_fetch_col( $resid );
@col = mysql_fetch_col( $stmtid );

%row = mysql_fetch_hash( $resid );
%row = mysql_fetch_hash( $stmtid );

@names = mysql_fetch_names( $resid );
@names = mysql_fetch_names( $stmtid );

@lengths = mysql_fetch_lengths( $resid );
@lengths = mysql_fetch_lengths( $stmtid );

$num_rows = mysql_num_rows( $resid );
$num_rows = mysql_num_rows( $stmtid );

$row_index = mysql_row_tell( $resid );
$row_index = mysql_row_tell( $stmtid );

mysql_row_seek( $resid, $row_index );
mysql_row_seek( $stmtid, $row_index );

$num_fields = mysql_num_fields( $resid );
$num_fields = mysql_num_fields( $stmtid );

%field = mysql_fetch_field( $resid );
%field = mysql_fetch_field( $resid, $offset );
%field = mysql_fetch_field( $stmtid );
%field = mysql_fetch_field( $stmtid, $offset );

$field_index = mysql_field_tell( $resid );
$field_index = mysql_field_tell( $stmtid );

$hr = mysql_field_seek( $resid );
$hr = mysql_field_seek( $resid, $offset );
$hr = mysql_field_seek( $stmtid );
$hr = mysql_field_seek( $stmtid, $offset );

mysql_free_result( $resid );
mysql_free_result( $stmtid );

$affected_rows = mysql_affected_rows();
$affected_rows = mysql_affected_rows( $linkid );
$affected_rows = mysql_affected_rows( $stmtid );

$id = mysql_insert_id();
$id = mysql_insert_id( $linkid );
$id = mysql_insert_id( $stmtid );

$hr = mysql_set_charset( $charset );
$hr = mysql_set_charset( $linkid, $charset );

$charset = mysql_get_charset();
$charset = mysql_get_charset( $linkid );

$quoted = mysql_quote( $str );
$quoted = mysql_quote_id( ... );
$str = mysql_escape( $str );
$str = mysql_escape( $linkid, $str );

mysql_auto_commit( $mode );
mysql_auto_commit( $linkid, $mode );
mysql_begin_work();
mysql_begin_work( $linkid );
mysql_commit();
mysql_commit( $linkid );
mysql_rollback();
mysql_rollback( $linkid );

$str   = mysql_error();
$str   = mysql_error( $linkid );
$errno = mysql_errno();
$errno = mysql_errno( $linkid );

mysql_close();
mysql_close( $linkid );
mysql_close( $stmtid );
mysql_close( $resid );

DESCRIPTION

PAB3::DB::Driver::Mysql provides an interface to the mysql client library.

This module should be threadsafe, BUT:

If you plan using threads, you should use own connections in each thread. It is never safe to use the same connection in two or more threads.

Under ModPerl or PerlEx environment several scripts may take access to the same instance of the perl interpreter. All functions are thread local but global to the interpreter! If you plan using different connections in your scripts which may access to the same interpreter you should explicitly set $linkid in all expected functions. You can alternatively use the PAB3::DB class. It takes care of it by itself. See more at the PAB3::DB manpage.

Examples

Using "query" method

use PAB3::DB::Driver::Mysql;

# make a connection
$linkid = mysql_connect( 'host', 'user', 'passwd', 'db' )
    or die mysql_error();

# send a query and store the result
$resid = mysql_query( 'SELECT * FROM my_table' )
    or die mysql_error();

# fetch rows from the result
while( @row = mysql_fetch_row( $resid ) ) {
    print join( ', ', @row ), "\n";
}

# free the result
mysql_free_result( $result );

# close the connection
mysql_close( $linkid );

Using "prepare" and "execute" methods

use PAB3::DB::Driver::Mysql;

# make a connection
$linkid = mysql_connect( 'host', 'user', 'passwd', 'db' )
    or die mysql_error();

# prepare statement
$stmtid = mysql_prepare( 'SELECT * FROM my_table WHERE my_field = ?' )
    or die mysql_error();

# bind "foo" to parameter 1 as string
mysql_bind_param( $stmtid, 1, 'foo', 's' );
    or die mysql_error();

# execute statement and store the result
$resid = mysql_execute( $stmtid )
    or die mysql_error();

# fetch rows from the result
while( @row = mysql_fetch_row( $resid ) ) {
    print join( ', ', @row ), "\n";
}

Exports

By default all functions and constants are exported. Exported functions get the prefix "mysql_". Exported constants get the prefix "MYSQL_".

METHODS

Connection Control Methods

connect ()

connect ( $server )

connect ( $server, $user )

connect ( $server, $user, $auth )

connect ( $server, $user, $auth, $db )

connect ( $server, $user, $auth, $db, $client_flag )

Opens a connection to a MySQL server.

Parameters

$server

The MySQL server. It can also include a port number. e.g. "hostname:port" or a path to a local socket e.g. ":/path/to/socket" for the localhost.

$username

The username.

$auth

The authorization password.

$db

The database name.

$client_flag

The $client_flag parameter can be a combination of the following constants:

CLIENT_COMPRESS ...... Use compression protocol 
CLIENT_IGNORE_SPACE .. Allow space after function names 
CLIENT_INTERACTIVE ... Allow interactive_timeout seconds
                       (instead of wait_timeout)
                       of inactivity before closing the connection. 
CLIENT_RECONNECT ..... Enable automatic reconnection to the server if the
                       connection is found to have been lost.

Return Values

Returns a connection link identifier ($linkid) on success, or NULL on failure.

Example

use PAB3::DB::Driver::Mysql;
# functions and constants are exported by default

# make connection to localhost as user "user" to database "testdb"
$linkid = mysql_connect( '', 'user', '', 'testdb', MYSQL_CLIENT_RECONNECT );
if( ! $linkid ) {
    die 'Connection failed: ' . mysql_error();
}

db_connect ( %arg )

Wrapper to connect() used by PAB3::DB::connect().

Following arguments are supported:

host       => hostname
user       => authorized username
auth       => authorization password
db         => database name
port       => port for tcp/ip connection
socket     => unix socket for local connection
options    => hashref with parameters mapped to $client_flag
              these parameters are:
              'reconnect', 'compress', 'interactive', 'ignore_space'
              a description can be found at connect() above

Return Values

Returns a connection link identifier ($linkid) on success, or NULL on failure.

close ()

close ( $linkid )

Closes a previously opened database connection

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

Return Values

Returns TRUE on success or FALSE on failure.

reconnect ()

reconnect ( $linkid )

Reconnects a previously opened database connection

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

Return Values

Returns TRUE on success or FALSE on failure.

set_charset ( $charset )

set_charset ( $linkid, $charset )

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

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

$charset

The charset to be set as default.

Return Values

Returns TRUE on success or FALSE on failure.

get_charset ()

get_charset ( $linkid )

Gets the default character.

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

Return Values

Returns the default charset or NULL on error.

errno ()

errno ( $linkid )

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

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

Return Values

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

error ()

error ( $linkid )

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

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

Return Values

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

Command Execution Methods

query ( $query )

query ( $linkid, $query )

Sends a query to the currently active database on the server that is associated with the specified link identifier.

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

$query

The query, as a string.

Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, query returns a result set identifier ($resid) 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.

prepare ( $query )

prepare ( $linkid, $query )

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

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

$query

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 identifier ($stmtid) or FALSE if an error occured.

See Also

execute, bind_param

bind_param ( $stmtid, $p_num )

bind_param ( $stmtid, $p_num, $value )

bind_param ( $stmtid, $p_num, $value, $type )

Binds a value to a prepared statement as parameter

Parameters

$stmtid

A statement identifier returned by prepare().

$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.

execute ( $stmtid )

execute ( $stmtid, @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

$stmtid

A statement identifier returned by prepare().

@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 set identifier ($resid) 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.

Retrieving Query Result Information

affected_rows ()

affected_rows ( $linkid )

affected_rows ( $stmtid )

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().

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

$stmtid

A statement identifier returned by prepare() which has been executed.

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.

insert_id ()

insert_id ( $linkid )

insert_id ( $stmtid )

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

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

$stmtid

A statement identifier returned by prepare() which has been executed.

Return Values

The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero 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

fetch_row ( $resid )

fetch_row ( $stmtid )

fetch_array ( $resid )

fetch_array ( $stmtid )

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

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

Return Values

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

fetch_hash ( $resid )

fetch_hash ( $stmtid )

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

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

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 column(s) of the same name, you either need to access the result with numeric indices by using fetch_row() or add alias names.

fetch_col ( $resid )

fetch_col ( $stmtid )

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

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

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.

fetch_lengths ( $resid )

fetch_lengths ( $stmtid )

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

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

Return Values

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

num_rows ( $resid )

num_rows ( $stmtid )

Gets the number of rows in a result.

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

Return Values

Returns number of rows in the result set.

row_tell ( $resid )

row_tell ( $stmtid )

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

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

Return Values

Returns the actual position of row cursor in a result.

row_seek ( $resid, $offset )

row_seek ( $stmtid, $offset )

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

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

$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

fetch_names ( $resid )

fetch_names ( $stmtid )

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

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

Return Values

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

num_fields ( $resid )

num_fields ( $stmtid )

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

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

Return Values

Returns number of fields in the result set.

fetch_field ( $resid )

fetch_field ( $resid, $offset )

fetch_field ( $stmtid )

fetch_field ( $stmtid, $offset )

Returns the next field in the result.

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

$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.

field_tell ( $resid )

field_tell ( $stmtid )

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

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

Return Values

Returns the actual position of field cursor in the result.

field_seek ( $resid, $offset )

field_seek ( $stmtid, $offset )

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

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

$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

free_result ( $resid )

free_result ( $stmtid )

Frees the memory associated with a result or statement.

Paramters

$resid

A result set identifier returned by query() or execute().

$stmtid

A statement identifier returned by prepare() which has been executed.

Return Values

Resturn TRUE on success or FALSE on error.

Transaction Methods

auto_commit ( $bool )

auto_commit ( $linkid, $mode )

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

To determine the current state of autocommit use the SQL command SELECT @@autocommit.

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

$mode

Whether to turn on auto-commit or not.

Return Values

Returns TRUE on success or FALSE on failure.

begin_work ()

begin_work ( $linkid )

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

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

Return Values

Returns TRUE on success or FALSE on failure.

commit ()

commit ( $linkid )

Commits the current transaction for the database connection.

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

Return Values

Returns TRUE on success or FALSE on failure.

rollback ()

rollback ( $linkid )

Rollbacks the current transaction for the database.

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

Return Values

Returns TRUE on success or FALSE on failure.

Other Functions

quote ( $value )

Quote a string literal 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.

quote_id ( $field )

quote_id ( $table, $field )

quote_id ( $schema, $table, $field )

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

$s = mysql_quote_id( 'table' );
# $s should be `table`

$s = mysql_quote_id( 'table', 'field' );
# $s should be `table`.`field`

$s = mysql_quote_id( 'table', '*' );
# $s should be `table`.*

escape ( $value )

escape ( $linkid, $value )

This function is used to create a legal SQL string that you can use in an SQL statement. The given string is encoded to an escaped SQL string, taking into account the current character set of the connection.

Parameters

$linkid

A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.

$value

The string to be escaped.

Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z.

Return Values

Returns an escaped string.

AUTHORS

Christian Mueller <christian_at_hbr1.com>

COPYRIGHT

The PAB3::DB::Driver::Mysql 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::DB::Driver::Mysql, copy and paste the appropriate command in to your terminal.

cpanm

cpanm PAB3::DB::Driver::Mysql

CPAN shell

perl -MCPAN -e shell
install PAB3::DB::Driver::Mysql

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

Exports

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

Other Functions

AUTHORS

COPYRIGHT

Module Install Instructions