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


SYNOPSIS

         use Msql;

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

             or

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

         $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;        # Array context
         $firstcol = $sth->fetchrow;   # Scalar context
         @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);


OBSOLETE SOFTWARE

       As of Msql-Mysql-modules 1.19_10 M(y)sqlPerl is no longer

       drivers. You are strongly encouraged to implement new code
       with DBI directly. See the COMPATIBILITY NOTES entry else­
       where in this document below.


DESCRIPTION

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

       As of March 1998, the Msql and Mysql modules are obsoleted
       by the DBI drivers DBD::mSQL and DBD::mysql, respectively.
       You are strongly encouraged to implement new code with the
       DBI drivers. In fact, Msql and Mysql are currently imple­
       mented as emulations on top of the DBI drivers.

       Internally you are dealing with the two classes `Msql' and
       `Msql::Statement' or `Mysql' and `Mysql::Statement',
       respectively.  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 Msql or Mysql. They offer you the connect command:

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

           or

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

           or

         $dbh = Msql1->connect($host);
         $dbh = Msql1->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, which has a much better per­
       formance than the TCP counterpart. 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.

          $dbh->selectdb($database);

       If you have not chosen a database with the `connect' com­
       mand, or if you want to change the connection to a differ­
       ent database using a database handle you have got from a


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

       These two work rather similar as descibed in the mSQL or
       MySQL 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. Be carefull with context here! In scalar
       context the method behaves different than expected and
       returns the first column:

         $firstcol = $sth->fetchrow; # Scalar context!

       Similar does

         %hash = $sth->fetchhash;

       return a complete hash. The keys in this hash are the col­
       umn 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.

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

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

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


       Whenever the scalar that holds a database or statement
       handle loses its value, Msql chooses the appropriate
       action (frees the result or closes the database connec­
       tion). So if you want to free the result or close the con­
       nection, 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(), which returns a textual error message. Mysql
       additionally supports a method ->errno returning the cor­
       responding error number.

       Usually you do fetch error messages with

           $errmsg = $dbh->errmsg();

       In situations where a $dbh is not available (for example
       when connect() failed) you may instead do a

           $errmsg = Msql->errmsg();
               or
           $errmsg = Mysql->errmsg();
               or
           $errmsg = Msql1->errmsg();

       The `-w' switch

       With Msql and Mysql the `-w' switch is your friend! If you
       call your perl program with the `-w' switch you get the
       warnings from ->errmsg on STDERR. This is a handy method
       to get the error messages from the msql 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 noth­
       ing to be added.

       By default errors are printed as warnings. You can sup­
       press this behaviour by using the PrintError attribute of
       the respective handles:


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

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

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

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

       If you pass undefined values to the quote method, it
       returns the string `NULL'.

       If a second parameter is passed to `quote', the result is
       truncated to 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 meta­
       data.

       Database Handle

       As said above you get a database handle with the connect()
       method.  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;

       Mysql additionally supports

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

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


       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 and an array reference (see the
       perlref manpage and the perlldsc manpage for details) when
       called in a scalar context. The scalar context is useful,
       if you need only 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];


       The query() function in the API returns the number of rows
       affected by a 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 check the number of rows returned. For non-selects
       we behave just the same as mSQL-2.

       To find all indices associated with a table you can call
       the `listindices()' method on a statement handle. To find
       out the columns included 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
       their numeric 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 same time. 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 out what the `-I' switch in perl
       is for.

       Everything else seems to remain backwards compatible.

       @EXPORT

       For historical reasons the constants CHAR_TYPE, INT_TYPE,
       and REAL_TYPE are in @EXPORT instead of @EXPORT_OK. This
       means, that you always have them imported into your names­
       pace. I consider it a bug, but not such a serious one,
       that I intend to break old programs by moving them into
       EXPORT_OK.

       Displaying whole tables in one go

       A handy method to show the complete contents of a state­
       ment handle is the as_string method. This works similar to
       the msql monitor with a few exceptions:

       the width of a column
         is calculated by examining the width of all entries in
         that column

         are mapped into their backslashed octal representation

       backslashes
         are doubled (`\\ instead of \')

       numeric values
         are adjusted right (both integer and floating point val­
         ues)

       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.

       configuration parameters: gethostinfo, getserverinfo, and
       getprotoinfo.  All three are available both as class meth­
       ods or via a database handle. But under no circumstances
       they are associated with a database handle. All three
       return global variables that reflect the last connect()
       command within the current program. This means, that all
       three return empty strings or zero before the first call
       to connect().

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

       Administration

       shutdown, createdb, dropdb, reloadacls are all accessible
       via a database handle and implement the corresponding
       methods to what msqladmin does.

       The mSQL and MySQL engines do not permit that these com­
       mands are invoked by users without sufficient privileges.
       So please make sure to 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 in any 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 List­
       Tables but prefer list_tables or listtables. The mSQL API
       uses StudlyCaps everywhere and so did early versions of
       MsqlPerl. Beginning with $VERSION 1.06 all methods are
       internally in lowercase, but may be written however you
       please. Case is ignored and you may use the underline to
       improve readability.

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


PREREQUISITES

       mSQL is a database server and an API library written by

       install these first.

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


COMPATIBILITY NOTES

       M(y)sql used to be a separate module written in C. This is
       no longer the case, instead the old modules are emulated
       by their corresponding DBI drivers. I did my best to
       remove any incompatibilities, but the following problems
       are known to remain:

       Static methods
           For whatever reason, mSQL implements some functions
           independent from the respective database connection
           that really depend on it. This made it possible to
           implement

               Msql->errmsg

           or

               Msql->getserverinfo

           as static methods. This is no longer the case, it
           never was for MysqlPerl. Instead you have to use

               $dbh->errmsg

           or

               $dbh->getserverinfo

       $M(Y)SQL::QUIET
           This variable used to turn off the printing of error
           messages. Unfortunately DBI uses a completely differ­
           ent mechanism for that: The `PrintError' attribute of
           the database and/or statement handles. We try to emu­
           late the old behaviour by setting the `PrintError'
           attribute to the current value of $M(Y)SQL::QUIET when
           a handle is created, that is when M(y)sql->connect or
           $dbh->query() are called.

           You can overwrite this by using something like

               $dbh->{'dbh'}->{'PrintError'} = 1;

           or

               $sth->{'PrintError'} = 0;

       Andreas Koenig `koenig@franz.ww.TU-Berlin.DE' wrote the
       original MsqlPerl. Jochen Wiedmann `joe@ispsoft.de' wrote
       the M(y)sqlPerl emulation using DBI.


SEE ALSO

       Alligator Descartes wrote a database driver for Tim
       Bunce's DBI. I recommend anybody to carefully watch the
       development of this module (`DBD::mSQL'). Msql is a sim­
       ple, stable, and fast module, and it will be supported for
       a long time. But it's a dead end. I expect in the medium
       term, that the DBI efforts result in a richer module fam­
       ily with better support and more functionality. Alligator
       maintains an interesting page on the DBI development:

           http://www.symbolstone.org/technology/perl/DBI


Man(1) output converted with man2html