PAB3::DB::Driver::Mysql - Perl5 wrapper to the mysql5+ client libary and driver for the PAB3::DB class
See more at the PAB3::DB manpage
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 );
PAB3::DB::Driver::Mysql provides an interface to the mysql client library.
PAB3::DB::Driver::Mysql
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.
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.
PAB3::DB
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 );
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"; }
By default all functions and constants are exported. Exported functions get the prefix "mysql_". Exported constants get the prefix "MYSQL_".
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(); }
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
Closes a previously opened database connection
$linkid
A link identifier returned by connect(). If the link identifier is not specified, the last link is assumed.
Returns TRUE on success or FALSE on failure.
Reconnects a previously opened database 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.
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 query to the currently active database on the server that is associated with the specified link identifier.
$query
The query, as a string.
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.
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 identifier ($stmtid) or FALSE if an error occured.
See Also
execute, bind_param
Binds a value to a prepared statement as parameter
$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
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.
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().
A statement identifier returned by prepare() which has been executed.
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.
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.
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().
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 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 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.
Frees the memory associated with a result or statement.
Resturn TRUE on success or FALSE on error.
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.
$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.
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".
Value to be quoted.
The quoted value with adding the required type of outer quotation marks.
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.
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`.*
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.
The string to be escaped.
Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z.
Returns an escaped string.
Christian Mueller <christian_at_hbr1.com>
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.