NAME

DBD::mysql - mSQL-1.x / 2.x driver for the Perl5 Database Interface (DBI)

SYNOPSIS

$dbh = DBI->connect( "$database:$hostname:$port" );

@databases = $drh->func( $hostname, '_ListDBs' );
@tables = $dbh->func( '_ListTables' );
$ref = $dbh->func( $table, '_ListFields' );
$ref = $sth->func( '_ListSelectedFields' );

$numRows = $sth->func( '_NumRows' );

$rc = $drh->func( $database, '_CreateDB' );
$rc = $drh->func( $database, '_DropDB' );

DESCRIPTION

<DBD::mysql> is the Perl5 Database Interface driver for mSQL 1.x and 2.x databases.

Compatibility Alert

As of version 0.70 DBD::mysql has a new maintainer

DBD::mysql Class Methods

connect

$dbh = DBI->connect( "$database" );
$dbh = DBI->connect( "$database:$hostname" );
$dbh = DBI->connect( "$database:$hostname:$port" );

A database must always be specified.

The hostname, if not specified or specified as '', will default to an mSQL daemon running on the local machine on the default port for the UNIX socket.

Should the mSQL daemon be running on a non-standard port number, you may explicitly state the port number to connect to in the hostname argument, by concatenating the hostname and port number together separated by a colon ( : ) character.

DBD::mysql Private MetaData Methods

ListTables

@tables = $dbh->func( '_ListTables' );

Once connected to the desired database on the desired mSQL daemon with the DBI-connect()> method, we may extract a list of the tables that have been created within that database.

ListTables returns an array containing the names of all the tables present within the selected database. If no tables have been created, an empty list is returned.

@tables = $dbh->func( '_ListTables' );
foreach $table ( @tables ) {
    print "Table: $table\n";
  }

ListFields

$ref = $dbh->func( $table, '_ListFields' );

ListFields returns a reference to a hashtable containing metadata information on the fields within the given table. If the table specified in table does not exist, undef will be returned and an error flagged.

The valid keys within the hashtable that may be referenced are:

NAME           The name of the field
TYPE           The datatype of the field: CHAR, REAL, INTEGER, NULL
IS_NOT_NULL    Indicates whether the field is NULLable or not
IS_PRI_KEY     Indicates whether the field is a Primary Key ( this is
                 only valid in mSQL 1.x databases. mSQL 2.x uses indices )
LENGTH         The size of the field
NUMFIELDS      The number of fields within the table

Since a reference is returned, it requires slightly more work to extract the pertinent information from it. Here's an example of how to do it:

$ref = $dbh->func( 'someTable', '_ListFields' );
@fieldNames = @{ $ref->{NAME} };
@fieldTypes = @{ $ref->{TYPE} };
@fieldNulls = @{ $ref->{IS_NOT_NULL} };
@fieldKeys  = @{ $ref->{IS_PRI_KEY} };
@fieldLength = @{ $ref->{LENGTH} };
for ( $i = 0 ; $i < $ref->{NUMFIELDS} ; $i++ ) {
    print "Field: $fieldNames[$i]\n";
    print "\tType: $fieldTypes[$i]\n";
    print "\tNullable: $fieldNulls[$i]\n";
    print "\tKey?: $fieldKeys[$i]\n";
    print "\tLength: $fieldLength[$i]\n";
  }

ListSelectedFields

$ref = $sth->func( '_ListSelectedFields' );

ListSelectedFields is a similar function to ListFields, except, where ListFields lists the fields for a given table within the current database, ListSelectedFields lists the field information for the fields present in a SELECT statement handle. This is primarily used for extracting meta-data about the current sth.

The usage of ListSelectedFields is identical to ListFields.

DBD::mysql Database Manipulation

CreateDB

$rc = $drh->func( $database, '_CreateDB' );
$rc = $drh->func( $database, '_DropDB' );

These two methods allow programmers to create and drop databases from DBI scripts. Since mSQL disallows the creation and deletion of databases over the network, these methods explicitly connect to the mSQL daemon running on the machine localhost and execute these operations there.

It should be noted that database deletion is not prompted for in any way. Nor is it undo-able from DBI.

Once you issue the dropDB() method, the database will be gone!

These methods should be used at your own risk.

BUGS

The port part of the first argument to the connect call is implemented in an unsafe way. In fact it never did more than set the environment variable MSQL_TCP_PORT during the connect call. If another connect call uses another port and the handles are used simultaneously, they will interfere. In a future version this behaviour will change.

The host part of the first argument to the connect call is currently documented as defaulting to 'localhost'. If I read this right, it implicates that there are no provisions to connect to the UNIX socket. This is a major speed disadvantage for application that run on the server host. This will have to be revisited in the next release.

The func method call on a driver handle seems to be undocumented in the DBI manpage. DBD::mysql has func methods on driverhandles, database handles, and statement handles. What gives?

Despite all these func methods, AFAIK it is currently not possible to connect to a different host and query the available databases. If true, this is a minor nit, but needs to be resolved somehow.

I haven't yet found out how the constants CHAR_TYPE, INT_TYPE, etc. are accessed in DBD::mysql. Can anybody help me on the tracks here?

Please speak up now (June 1997) if you encounter additional bugs. I'm still learning about the DBI API and can neither judge the quality of the code presented here nor the DBI compliancy. But I'm intending to resolve things quickly as I'd really like to get rid of the multitude of implementations ASAP.

AUTHOR

DBD::mysql has been primarily written by Alligator Descartes <descarte@hermetica.com>, who has been aided and abetted by Gary Shea, Andreas Koenig and Tim Bunce amongst others. Apologies if your name isn't listed, it probably is in the file called 'Acknowledgments'. As of version 0.80 the maintainer is Andreas König. Version 2.00 is an almost complete rewrite by Jochen Wiedmann.

COPYRIGHT

This module is Copyright (c)1997 Jochen Wiedmann, with code portions Copyright (c)1994-1997 their original authors. This module is released under the 'Artistic' license which you can find in the perl distribution.

This document is Copyright (c)1997 Alligator Descartes. All rights reserved. Permission to distribute this document, in full or in part, via email, Usenet, ftp archives or http is granted providing that no charges are involved, reasonable attempt is made to use the most current version and all credits and copyright notices are retained ( the AUTHOR and COPYRIGHT sections ). Requests for other distribution rights, including incorporation into commercial products, such as books, magazine articles or CD-ROMs should be made to Alligator Descartes <descarte@hermetica.com>.

Additional DBI Information

Additional information on the DBI project can be found on the World Wide Web at the following URL:

http://www.hermetica.com/technologia/perl/DBI

where documentation, pointers to the mailing lists and mailing list archives and pointers to the most current versions of the modules can be used.

Information on the DBI interface itself can be gained by typing:

perldoc DBI

right now!

cpanm Mysql

perl -MCPAN -e shell
install Mysql