MAN page from RedHat Other mysql-perl-bin-3.21.23-2libc.i386.rpm

lib::Mysql

Section: User Contributed Perl Documentation (3)
Updated: perl 5.004, patch 04
Index

NAME

Msql / Mysql - Perl interfaces to the mSQL and mysql databases

SYNOPSIS

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

or

  use Mysql;

  $dbh = Mysql->connect(undef, $database, $user, $password);  $dbh = Mysql->connect($host, $database, $user, $password);          $dbh->selectdb($database);          @arr = $dbh->listdbs;  @arr = $dbh->listtables;          $quoted_string = $dbh->quote($unquoted_string);  $error_message = $dbh->errmsg;  $error_number = $dbh->errno;   # MySQL only

  $sth = $dbh->listfields($table);  $sth = $dbh->query($sql_statement);          @arr = $sth->fetchrow;  @arr = $sth->fetchcol($col_number);  %hash = $sth->fetchhash;          $sth->dataseek($row_number);

  $sth->as_string;

  @indices = $sth->listindices                   # only in mSQL 2.0  @arr = $dbh->listindex($table,$index)          # only in mSQL 2.0  ($step,$value) = $dbh->getsequenceinfo($table) # only in mSQL 2.0

  $rc = $dbh->shutdown();  $rc = $dbh->createdb($database);  $rc = $dbh->dropdb($database);

DESCRIPTION

This package is designed as close as possible to its C APIcounterpart. The manual that comes with mSQL or MySQL describes most thingsyou need. Due to popular demand it was decided though, that this interfacedoes not use StudlyCaps (see below).

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 Msql andMsql::Statement or Mysql and Mysql::Statement, respectively.You will never see the latter, because you reachit through a statement handle returned by a query or a listfieldsstatement. The only class you name explicitly is Msql or Mysql. Theyoffer you the connect command:

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

or

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

This connects you with the desired host/database. With no argument orwith an empty string as the first argument it connects to the UNIXsocket, which has a much better performance thanthe TCP counterpart. A database name as the second argument selectsthe chosen database within the connection. The return value is adatabase handle if the connect succeeds, otherwise the return value isundef.

You will need this handle to gain further access to the database.

   $dbh->selectdb($database);

If you have not chosen a database with the connect command, or ifyou want to change the connection to a different database using adatabase handle you have got from a previous connect, then useselectdb.

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

These two work rather similar as descibed in the mSQL or MySQL manual. Theyreturn a statement handle which lets you further explore what theserver has to tell you. On error the return value is undef. The objectreturned by listfields will not know about the size of the table, so anumrows() on it will return the string ``N/A'';

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

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

  @arr = $sth->fetchrow;

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

  %hash = $sth->fetchhash;

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

  @arr = $sth->fetchcol($colnum);

returns an array of the values of each row for column $colnum. Note thatthis reads the entire table and leaves the row offset at the end of thetable; be sure to use $sth->dataseek() to reset it if you want tore-examine the table.

  $sth->dataseek($row_number);

lets you specify a certain offset of the data associated with thestatement handle. The next fetchrow will then return the appropriaterow (first row being 0).

No close statement

Whenever the scalar that holds a database or statement handle losesits value, Msql chooses the appropriate action (frees the result orcloses the database connection). So if you want to free the result orclose the connection, choose to do one of the following:

undef the handle
use the handle for another purpose
let the handle run out of scope
exit the program.

Error messages

Both drivers, Msql and Mysql implement a method ->errmsg(), whichreturns a textual error message. Mysql additionally supports a method->errno returning the corresponding error number. Note that Msql'serrmsg is a static method, thus it is legal to fetch

    Msql->errmsg();

Mysql doesn't support this, fetching the error message is only validvia

    $dbh->errmsg();

I recommend, that even Msql users restrict themselves to the latterfor portability reasons. There are also global variables $Msql::db_errstrand $Mysql::db_errstr, which always hold the last error message. The formeris reset with the next executed command, the latter not. Usuallythere's no need for accessing the global variables, with one exception:If the connect method fails, you need them.

The -w switch

With Msql and Mysql the -w switch is your friend! If you call your perlprogram with the -w switch you get the warnings from ->errmsg onSTDERR. This is a handy method to get the error messages from the msqlserver without coding it into your program.

If you want to know in greater detail what's going on, set theenvironment variables that are described in David's manual. David'sdebugging aid is excellent, there's nothing to be added.

If you want to use the -w switch but do not want to see the errormessages from the msql daemon, you can set the variables $Msql::QUIETor $Mysql::QUIET to some true value, and they will be supressed.

->quote($str [, $length])

returns the argument enclosed in single ticks ('') with any specialcharacter escaped according to the needs of the API.

For mSQL this means, any single tick within the string is escaped witha backslash and backslashes are doubled. Currently (as of msql-1.0.16)the API does not allow to insert NUL's (ASCII 0) into tables. The quotemethod does not fix this deficiency.

MySQL allows NUL's or any other kind of binary data in strings. Thusthe quote method will additionally escape NUL's as \0.

If you pass undefined values to the quote method, it returns thestring NULL.

If a second parameter is passed to quote, the result is truncatedto that many characters.

NULL fields

NULL fields in tables are returned to perl as undefined values.

Metadata

Now lets reconsider the above methods with regard to metadata.

Database Handle

As said above you get a database handle with

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

or

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

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

You get at the three values with the methods

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

Mysql additionally supports

  $scalar = $dbh->user;  $scalar = $dbh->sockfd;

where the latter is the file descriptor of the socket used by thedatabase connection. This is the same as $dbh->sock for mSQL.

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;

  @arr  = $sth->table;       the names of the tables of each column  @arr  = $sth->name;        the names of the columns  @arr  = $sth->type;        the type of each column, defined in msql.h                             and accessible via Msql::CHAR_TYPE,                             &Msql::INT_TYPE, &Msql::REAL_TYPE or                             &Mysql::FIELD_TYPE_STRING,                             &Mysql::FIELD_TYPE_LONG, ...  @arr  = $sth->isnotnull;   array of boolean  @arr  = $sth->isprikey;    array of boolean  @arr  = $sth->isnum;       array of boolean  @arr  = $sth->length;      array of the possibble maximum length of each                             field in bytes  @arr  = $sth->maxlength;   array of the actual maximum length of each field                             in bytes. Be careful when using this attribute                             under MsqlPerl: The server doesn't offer this                             attribute, thus it is calculated by fetching                             all rows. This might take a long time and you                             might need to call $sth->dataseek.

Mysql additionally supports

  $scalar  = $sth->affectedrows  number of rows in database affected by query  $scalar  = $sth->insertid      the unique id given to a auto_increment field.  $string  = $sth->info()        more info from some queries (ALTER TABLE...)  $arrref  = $sth->isblob;       array of boolean

The array methods (table, name, type, is_not_null, is_pri_key, length,affected_rows, is_num and blob) return an array in array context andan array reference (see the perlref manpage and the perlldsc manpage for details) whencalled in a scalar context. The scalar context is useful, if you needonly the name of one column, e.g.

    $name_of_third_column = $sth->name->[2]

which is equivalent to

    @all_column_names = $sth->name;    $name_of_third_column = $all_column_names[2];

New in mSQL 2.0

The query() function in the API returns the number of rows affected bya query. To cite the mSQL API manual, this means...

  If the return code is greater than 0, not only does it imply  success, it also indicates the number of rows "touched" by the query  (i.e. the number of rows returned by a SELECT, the number of rows  modified by an update, or the number of rows removed by a delete).

As we are returning a statement handle on selects, we can easily checkthe number of rows returned. For non-selects we behave just the sameas mSQL-2.

To find all indices associated with a table you can call thelistindices() method on a statement handle. To find out the columnsincluded in an index, you can call the listindex($table,$index)method on a database handle.

There are a few new column types in mSQL 2. You can access theirnumeric value with these functions defined in the Msql package:IDENT_TYPE, NULL_TYPE, TEXT_TYPE, DATE_TYPE, UINT_TYPE, MONEY_TYPE,TIME_TYPE, IDX_TYPE, SYSVAR_TYPE.

You cannot talk to a 1.0 server with a 2.0 client.

You cannot link to a 1.0 library and to a 2.0 library at the sametime. So you may want to build two different Msql modules at a time,one for 1.0, another for 2.0, and load whichever you need. Check outwhat the -I switch in perl is for.

Everything else seems to remain backwards compatible.

@EXPORT

For historical reasons the constants CHAR_TYPE, INT_TYPE, andREAL_TYPE are in @EXPORT instead of @EXPORT_OK. This means, that youalways have them imported into your namespace. I consider it a bug,but not such a serious one, that I intend to break old programs bymoving them into EXPORT_OK.

Displaying whole tables in one go

A handy method to show the complete contents of a statement handle isthe as_string method. This works similar to the msql monitor with afew exceptions:

the width of a column: is calculated by examining the width of all entries in that column
control characters: are mapped into their backslashed octal representation
backslashes: are doubled (\\ instead of \)
numeric values: are adjusted right (both integer and floating point values)

The differences are illustrated by the following table:

Input to msql (a real carriage return here replaced with ^M):

    CREATE TABLE demo (      first_field CHAR(10),      second_field INT    ) \g

    INSERT INTO demo VALUES ('new    line',2)\g    INSERT INTO demo VALUES ('back\\slash',1)\g    INSERT INTO demo VALUES ('cr^Mcrnl    nl',3)\g

Output of msql:

     +-------------+--------------+     | first_field | second_field |     +-------------+--------------+     | new    line    | 2            |     | back\slash  | 1            |    crnlr    nl  | 3            |     +-------------+--------------+

Output of pmsql:

    +----------------+------------+    |first_field     |second_field|    +----------------+------------+    |new\012line     |           2|    |back\\slash     |           1|    |cr\015crnl\012nl|           3|    +----------------+------------+

Version information

The version of Msql and Mysql is always stored in $Msql::VERSION or$Mysql::VERSION as it is perl standard.

The mSQL API implements methods to access some internal configurationparameters: gethostinfo, getserverinfo, and getprotoinfo. All threeare available both as class methods or via a database handle. Butunder no circumstances they are associated with a database handle. Allthree return global variables that reflect the last connect()command within the current program. This means, that all three returnempty strings or zero before the first call to connect().

This situation is better with MySQL: The methods are valid onlyin connection with a database handle.

Administration

shutdown, createdb, dropdb, reloadacls are all accessible via adatabase handle and implement the corresponding methods to whatmsqladmin does.

The mSQL and MySQL engines do not permit that these commands are invoked byusers without sufficient privileges. So please make sureto check the return and error code when you issue one of them.

    $rc = $dbh->shutdown();    $rc = $dbh->createdb($database);    $rc = $dbh->dropdb($database);

It should be noted that database deletion is not prompted for inany way. Nor is it undo-able from within Perl.

    B<Once you issue the dropdb() method, the database will be gone!>

These methods should be used at your own risk.

StudlyCaps

Real Perl Programmers (C) usually don't like to type ListTables butprefer list_tables or listtables. The mSQL API uses StudlyCapseverywhere and so did early versions of MsqlPerl. Beginning with$VERSION 1.06 all methods are internally in lowercase, but may bewritten however you please. Case is ignored and you may use theunderline to improve readability.

The price for using different method names is neglectible. Any methodname you use that can be transformed into a known one, will only bedefined once within a program and will remain an alias until theprogram terminates. So feel free to run fetch_row or connecT orListDBs as in your old programs. These, of course, will continue towork.

PREREQUISITES

mSQL is a database server and an API library written by DavidHughes. To use the adaptor you definitely have to install these first.

MySQL is a libmysqlclient.a library written by Michael WideniusThis was originally inspired by MySQL.

AUTHOR

andreas koenig koenigAATTfranz.ww.TU-Berlin.DE

This document was created byman2html,using the manual pages.