MySQL Chapter Six (The MySQL perl API)

Documentation Version: 0.95
MySQL Version: 3.20.29

The MySQL Perl Adaptor

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;
  %hash = FetchHash  $sth;
        
  $sth->DataSeek($row_number);

  $scalar = $dbh->sock;
  $scalar = $dbh->host;
  $scalar = $dbh->database;
  $scalar = $dbh->quote($binary_string);

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.

Because of limitations in how Perl deals with numeric values you will have problems using numbers larger than the size of a signed LONG. (2147483647). This can occur when using MySQL's unsigned LONG (DOUBLE) or LONGLONG (BIGINT) data types. Perl stores returned values as strings, but will automatically convert them to numbers when you use the values in a numeric context. This will cause them to be "truncated" to 2147483647 since Perl uses a signed LONG value to store such numbers.

You can use the following work arounds. First of all, always treat values that may be large as strings. As long as you do this they can be displayed and re-inserted into a database without incident. The same goes for inserting new values into tables. If you set a variable called $tmpvar to be equal to "4147483647" and then INSERT it into your database you should be fine. Note that the ""'s are important here, since they force Perl to treat the value as a string.

If you need to do calculations involving large numbers you should do them via the initial SELECT.

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

You can find out what socket, host and database a given handle is associated with by using doing the following.

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

The database will be undef if you specified only a host during the Connect.

$ENV{"MYSQL_TCP_PORT"}=3334;
$ENV{"MYSQL_UNIX_PORT"}="/tmp/mysql_new.sock";
use Mysql;
$dbh = Mysql->Connect($host);

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.

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.

The MySQL ListFields does not work quite the same as its 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

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.

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

}

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

---

FetchCol

Return an array containing one column's worth of values.

SYNOPSIS:

@arr = $sth->FetchCol($col);

DESCRIPTION:
Returns an array of the values for column number $col. FetchRow returns the next row of the query result; FetchCol returns an entire single column of the query result.

Note that this function will reset the current row pointer to the end of the table; you'll need to use DataSeek(0) to reset it.

---

FetchHash

Fetch a result hash.

SYNOPSIS:

%hash = $sth->FetchHash;

DESCRIPTION:
Returns an associative array containing the next row fetched from the server.

---

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:
Enables you to 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).

---

Escaping Binary Strings

Certain characters must be escaped before binary strings can be inserted into a MySQL database. The MySQL Perl interface provides the following function to do this automatically for you.

$scaler = $dbh->quote($binary_string)

This will escape all ' and \ characters and convert \0 and \n's as well.

---

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

---

Metadata

Now 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 Handle Two 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->affected_rows;	Number of inserted/changed rows.
$scalar = $sth->info;	Report stats information on previous ALTER TABLE or LOAD DATA FROM INFILE query.
$arrref = $sth->is_blob;	An array of boolean specifying whether a given field is a BLOB.
$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.
$scalar = $sth->insert_id;	Value assigned to column with AUTO_INCREMENT on last INSERT.
$arrref = $sth->length;	An array of the length of each field in bytes.
$arrref = $sth->name;	The names of the columns.
$scalar = $sth->numrows;	Number of rows returned.
$scalar = $sth->numfields;	Number of fields returned.
$arrref = $sth->table;	The names of the tables of each column.
$arrref = $sth->type;	The type of each column, defined in mysql.h. and accessible via &Mysql::CHAR_TYPE, &Mysql::INT_TYPE, &Mysql::REAL_TYPE

---

The -w switch

The -w switch can be very useful when debugging problems. If you call your perl program with the -w switch the warnings that normally are stored in $Mysql::db_errstr are printed to 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 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.17 version of Andreas Koenig's koenig@franz.ww.TU-Berlin.DE mSQL perl API.