This chapter is still in a fairly early state of development.

The Mysql Perl Adaptor: Simple Perl interface to the mySQL database

SUMMERRY:

        
  use Mysql;
        
  $dbh = Mysql->Connect;
  $dbh = Mysql->Connect($host);
  $dbh = Mysql->Connect($host,$database);
  $dbh = Mysql->Connect($host,$database,$password);
  $dbh = Mysql->Connect($host,$database,$password,$user);
        
  $dbh->SelectDB($database);
        
  $sth = $dbh->ListFields($table);
     @arr = @{$sth->name};       
     @arr = @{$sth->length};     
     $value = $sth->numfields;  
     @arr = @{$sth->type};     
     @arr = @{$sth->is_num};  
     @arr = @{$sth->is_blob};
     @arr = @{$sth->is_not_null};
                                

  $sth = $dbh->Query($sql_statement);
        
  @arr = $dbh->ListDBs;
  @arr = $dbh->ListTables;
        
  @arr = $sth->FetchRow;
        
  $sth->DataSeek($row_number);

This package is designed to be as close as possible to its C API counterpart.

Internally you are dealing with the two classes Mysql and Mysql::Statement. You will never see the latter, as you reach it through a statement handle returned by a Query or a ListFields statement. The only class you name explicitly is Mysql. It offers you the Connect command.

Connect

$dbh = Mysql->Connect;
$dbh = Mysql->Connect($host);
$dbh = Mysql->Connect($host,$database);
$dbh = Mysql->Connect($host,$database,$password);
$dbh = Mysql->Connect($host,$database,$password,$user);

This connects you with the desired host/database. With no argument or with an empty string as the first argument it connects to the UNIX socket /dev/mysql, which is a big performance gain. A database name in the second argument selects the chosen database within the connection. The return value is a database handle if the Connect succeeds, otherwise the return value is undef.

You may also optionally provide a username and password. If no user name is provided then the current login will be used. If no password is provided the connection will fail if the user has a password.

You will need this handle to gain further access to the database. You may issue multiple Connect statements, but be sure to use different variable names ($dbh1,$dbh2, $dbh3, etc.)

SYNOPSIS:

$dbh->SelectDB($database);

If you have not chosen a database with the Connect command, or if you want to change the connection to a different database using a database handle you acquired from a previous Connect, then use SelectDB.

SYNOPSIS:

  $sth = ListFields  $dbh $table;

The mysql Listfields does not work quite the same as it's mSQL counterpart. In mysql you use the following commands to get information.

You must have made a sucessful call to ListFields before using the following functions.

@arr = @{$sth->name};	Returns a array of the column names
@arr = @{$sth->length};	Returns a array of column lengths
$value = $sth->numfields;	Returns number of columns in table
@arr = @{$sth->type};	Array of mysql types
@arr = @{$sth->is_num};	Array of 0 and 1 where 1 indicates that the column is numerical
@arr = @{$sth->is_blob};	Array of 0 and 1 where 1 indicates that the column is a blob
@arr = @{$sth->is_not_null};	Array of 0 and 1 where 1 indicates that the column is NOT NULL

SYNOPSIS:

  $sth = Query       $dbh $sql_statement;

Example:

$sth = $dbh->Query("SELECT * FROM Widget_Table WHERE widget_id = 1") or die $Mysql::db_errstr;

while(@record = $sth->FetchRow) {

   $foo = $record[0]; $bar = $record[9];

  @arr = $dbh->ListDBs;

ListDBs will return an array that contains one element for the name of each database that the mysql database engine manages.

Example:

@dbs = $dbh->ListDBs; # Returns an array of DB names

$count = $#dbs; # Figure out how many elements.

for($i=0;$i<=$count;$i++) {

      print("   ".$dbs[$i]."\n");

  @arr = $dbh->ListTables;

Returns an array with one element for each table name in the database pointed to be $dbh. You must have specified a database either when calling Connect, or with SelectDB.

Example:

@tables = $dbh->ListTables; # Assumes that $dbh points 
                              to a valid database

$count = $#dbs; # Figure out how many elements.

for($i=0;$i<=$count;$i++) { # Print out the table names.
      print("   ".$tables[$i]."\n");
}

returns an array of the values of the next row fetched from the server.

lets you specify a certain offset into the data associated with the statement handle. The next FetchRow will then return the appropriate row (The first row being 0).

No close statement

undef the handle

use the handle for another purpose

use the handle inside a block and declare it with my()

exit the program.

Metadata

Database Handle

The database handle knows about the socket, the host, and the database it is connected to.

You get at the three values with the methods

database returns undef, if you have connected with no arguments, or with only one argument.

Statement Handle

$sth knows about all metadata that are provided by the API:

The -w switch

If you want to use the -w switch but do not want to see the error messages from the mysql daemon, you can set the variable $Mysql::QUIET to some non zero value, and the error messages will be suppressed.

PREREQUISITES

To use the adaptor you have to install this library first.

AUTHOR