mysql Chapter Six (The mysql perl API)

Documentation Version: 0.80
mysql Version: 3.20.16

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

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

SUMMARY:
        
  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. If you are using a mysqld compiled with MIT threads you will be unable to use sockets. In this case specify your systems hostname for the host variable.

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

SelectDB

Choose the database you wish to use.

 SYNOPSIS:

$dbh->SelectDB($database);

 DESCRIPTION:
 
 

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.

ListFields

List the fields in a table.

 SYNOPSIS:

$sth = ListFields $dbh $table;

 DESCRIPTION:

 ListFields returns a statement handle which can be used to find out what the server has to offer you. In the event of an error the return value will be undef.

 In mysql ListFields does not work quite the same as it's mSQL counterpart. In mysql you use the following commands to get information after making a ListFields call.

 You must have made a successful 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

Query

Perform a query.

SYNOPSIS:

$sth = $dbh->Query($sql_statement);

 DESCRIPTION:

 Query is the meat and potatoes call of the perl API. It allows you to send a query to the database. You'll need to use FetchRow to get back results.

 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[1];
}

 It's a good idea to always check for errors. In the example above this is done by the "or die..." portion of the Query line.
 
 

ListDBs

List the available Databases.

SYNOPSIS:

@arr = $dbh->ListDBs;

 DESCRIPTION:

 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");
}
 
 

ListTables

List tables available in a database.

SYNOPSIS:

@arr = $dbh->ListTables;

 DESCRIPTION:

 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 = $#tables; # Figure out how many elements.

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

FetchRow

Fetch a result row.

 SYNOPSIS:

@arr = $sth->FetchRow;

 DESCRIPTION:

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

DataSeek

Seek to an arbitrary position in data.

 SYNOPSIS:

DataSeek $sth $row_number;

 DESCRIPTION:

 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

Whenever the scalar that holds a database or statement handle looses its value, Mysql chooses the appropriate action (frees the result or closes the database connection). So if you want to free the result or close the connection, you may do one of the following:
undef the handle
use the handle for another purpose
use the handle inside a block and declare it with my()
exit the program.

 

 

.

MetadataNow lets reconsider the above methods with regard to metadata.

Database Handle

As mentioned above you acquire a database handle with

$dbh = Connect Mysql $host, $database;

 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

 $scalar = $dbh->sock;
$scalar = $dbh->host;
$scalar = $dbh->database;

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

Statement HandleTwo constructor methods return a statement handle:

$sth = ListFields $dbh $table;
$sth = Query $dbh $sql_statement;

 $sth knows about all metadata that are provided by the API:
$scalar = $sth->numrows; Number of rows returned.
$scalar = $sth->numfields; Number of fields returned.
$scalar = $sth->affected_rows; Number of inserted/changed rows.
$scalar = $sth->insert_id; New id in column with AUTO_INCREMENT specifier.
$arrref = $sth->table; The names of the tables of each column.
$arrref = $sth->name; The names of the columns.
$arrref = $sth->type; The type of each column, defined in mysql.h. and accessible via &Mysql::CHAR_TYPE, &Mysql::INT_TYPE, &Mysql::REAL_TYPE
$arrref = $sth->is_not_null;  An array of boolean values specifying whether a given field is NULL or not.
$arrref = $sth->is_pri_key; An array of boolean specifying whether a given field is a primary key.
$arrref = $sth->is_num; An array of boolean specifying whether a given field is a number.
$arrref = $sth->is_blob; An array of boolean specifying whether a given field is a BLOB.
$arrref = $sth->length; An array of the length of each field in bytes. 

The -w switch

The -w switch is your friend! If you call your perl program with the -w switch you get the warnings that normally are stored in $Mysql::db_errstr on STDERR. This is a handy way to get the error messages from the mysql server without coding it into your program. If you want to know in greater detail what's going on, set the environment variables that are described in David Axmark's manual. David's debugging aid is excellent, there's nothing to be added.

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

mySQL is a libmysql.a library written by Michael Widenius This was originally inspired by mSQL.

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

AUTHOR

The mysql perl API is based on the 1.03 version of Andreas Koenig's koenig@franz.ww.TU-Berlin.DE mSQL perl API.