/ 
MysqlPerl-1.16a
/ Mysql

NAME

Mysql - Perl interface to the mySQL database

SYNOPSIS

use Msql;

$dbh = Msql->connect;
$dbh = Msql->connect($host);
$dbh = Msql->connect($host, $database);

$dbh->selectdb($database);

@arr = $dbh->listdbs;
@arr = $dbh->listtables;

$quoted_string = $dbh->quote($unquoted_string);
$error_message = $dbh->errmsg;

$sth = $dbh->listfields($table);
$sth = $dbh->query($sql_statement);

@arr = $sth->fetchrow;
%hash = $sth->fetchhash;

$sth->dataseek($row_number);

$sth->as_string;

DESCRIPTION

This package is designed as close as possible to its C API counterpart. The manual that comes with mySQL describes most things you need.

The version you have selected is an adaption still under development, please consult the file "Changes" in your distribution.

Internally you are dealing with the two classes Mysql and Mysql::Statement. You will never see the latter, because 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:

$dbh = Mysql->connect;
$dbh = Mysql->connect($host);
$dbh = Mysql->connect($host, $database);

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 as 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 will need this handle to gain further access to the database. Issue multiple Connect statements -- no problem.

$dbh->selectdb($database);

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

$sth = $dbh->listfields($table);
$sth = $dbh->query($sql_statement);

These two work rather similar as descibed in the mSQL manual. They return a statement handle which lets you further explore what the server has to tell you. On error the return value is undef. The object returned by listfields will not know about the size of the table, so a numrows() on it will return the string "N/A";

@arr = $dbh->listdbs();
@arr = $dbh->listtables;

An array is returned that contains the requested names without any further information.

@arr = $sth->fetchrow;

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

%hash = $sth->fetchhash;

return a complete hash. The keys in this hash are the column names of the table, the values are the table values. Be aware, that when you have a table with two identical column names, you will not be able to use this method without trashing one column. In such a case, you should use the fetchrow method.

$sth->dataseek($row_number);

lets you specify a certain offset of the data associated with the statement handle. The next FetchRow will then return the appropriate row (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, choose to 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 said above you get a database handle with

$dbh = Mysql->connect($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 without or with only one argument.

Statement Handle

Two constructor methods return a statement handle:

$sth = $dbh->listfields($table);
$sth = $dbh->query($sql_statement);

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

  $scalar = $sth->numrows;    
  $scalar = $sth->numfields;  
  $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->isnotnull; array of boolean
  $arrref  = $sth->isprikey;  array of boolean
  $arrref  = $sth->isnum;	array of boolean
  $arrref  = $sth->isblob;	array of boolean
  $arrref  = $sth->length;      array of the length of each field in bytes

The -w switch

Also with Mysql 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 method 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'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 true value, and they 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 definitely have to install this library first.

AUTHOR

andreas koenig koenig@franz.ww.TU-Berlin.DE

BUGS

mysql does not support Tim Bunce's Database Interface DBI (yet :')

This specific version of the Mysql adaptor is a kind of a hack to be able to use old Msql scripts. See the file "Changes" in your distribution.