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

lib::DBD::mSQL

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

NAME

DBD::mSQL / DBD::mysql - mSQL and mysql drivers for the Perl5 DatabaseInterface (DBI)

SYNOPSIS

    use DBI;

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

or

    $dbh = DBI->connect("DBI:mysql:$database:$hostname:$port",                        $user, $password);

    @databases = DBD::mysql::dr->func( $hostname, '_ListDBs' );    @tables = $dbh->func( '_ListTables' );

    $sth = $dbh->prepare("LISTFIELDS $table");    $sth->execute;    $sth->finish;

    $sth = $dbh->prepare("SELECT * FROM foo WHERE bla");    $sth->execute;    $numRows = $sth->rows;    $numFields = $sth->{'NUM_OF_FIELDS'};    $sth->finish;

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

DESCRIPTION

<DBD::mysql> and <DBD::mSQL> are the Perl5 Database Interface drivers forthe mysql, mSQL 1.x and mSQL 2.x databases. The drivers are partof the mysql-modules and Msql-modules packages, respectively.

Class Methods

connect

    use DBI;

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

or

    use DBI;

    $dbh = DBI->connect("DBI:mysql:$database", $user, $password);    $dbh = DBI->connect("DBI:mysql:$database:$hostname",                        $user, $password);    $dbh = DBI->connect("DBI:mysql:$database:$hostname:$port",                        $user, $password);

A database must always be specified.

The hostname, if not specified or specified as `', will default to anmysql or mSQL daemon running on the local machine on the default portfor the UNIX socket.

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

Private MetaData Methods

ListDBs

    @dbs = $dbh->func("$hostname:$port", '_ListDBs');

Returns a list of all databases managed by the mysql daemon ormSQL daemon running on $hostname, port $port. This methodis rarely needed for databases running on localhost: You shoulduse the portable method

    @dbs = DBI->data_sources("mysql");

or

    @dbs = DBI->data_sources("mSQL");

whenever possible. It is a design problem of this method, that there'sno way of supplying a host name or port number to data_sources, that'sthe only reason why we still support ListDBs. :-(

ListTables

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

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

ListTables returns an array containing the names of all the tablespresent 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

Deprecated, see the section on /COMPATIBILITY ALERT below.

ListSelectedFields

Deprecated, see the section on /COMPATIBILITY ALERT below.

Database Manipulation

CreateDB

DropDB

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

or

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

These two methods allow programmers to create and drop databases fromDBI scripts. Since mSQL disallows the creation and deletion ofdatabases over the network, these methods explicitly connect to themSQL daemon running on the machine localhost and execute theseoperations there.

It should be noted that database deletion is not prompted for inany 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.

STATEMENT HANDLES

The statement handles of DBD::mysql and DBD::mSQL support a numberof attributes. You access these by using, for example,

  my $numFields = $sth->{'NUM_OF_FIELDS'};

Note, that most attributes are valid only after a successfull execute.An undef value will returned in that case. The most important exceptionis the mysql_use_result attribute: This forces the driver to usemysql_use_result rather than mysql_store_result. The former is fasterand less memory consuming, but tends to block other processes. (That's whymysql_store_result is the default.)

To set the mysql_use_result attribute, use either of the following:

  my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1});

  my $sth = $dbh->prepare("QUERY");  $sth->{"mysql_use_result"} = 1;

Of course it doesn't make sense to set this attribute before calling theexecute method.

Column dependent attributes, for example NAME, the column names,are returned as a reference to an array. The array indices arecorresponding to the indices of the arrays returned by fetchrowand similar methods. For example the following code will print aheader of table names together with all rows:

  my $sth = $dbh->prepare("SELECT * FROM $table");  if (!$sth) {      die "Error:" . $dbh->errstr . "\n";  }  if (!$sth->execute) {      die "Error:" . $sth->errstr . "\n";  }  my $names = $sth->{'NAME'};  my $numFields = $sth->{'NUM_OF_FIELDS'};  for (my $i = 0;  $i < $numFields;  $i++) {      printf("%s%s", $$names[$i], $i ? "," : "");  }  print "\n";  while (my $ref = $sth->fetchrow_arrayref) {      for (my $i = 0;  $i < $numFields;  $i++) {          printf("%s%s", $$ref[$i], $i ? "," : "");      }      print "\n";  }xFor portable applications you should restrict yourself to attributes withcapitalized or mixed case names. Lower case attribute names are privateto DBD::mSQL and DBD::mysql. The attribute list includes:

ChopBlanks: this attribute determines whether a fetchrow will chop precedingand trailing blanks off the column values. Chopping blanks does nothave impact on the max_length attribute.
insertid: MySQL has the ability to choose unique key values automatically. If thishappened, the new ID will be stored in this attribute. This attributeis not valid for DBD::mSQL.
is_blob: Reference to an array of boolean values; TRUE indicates, that therespective column is a blob. This attribute is valid for MySQL only.
is_key: Reference to an array of boolean values; TRUE indicates, that therespective column is a key. This is valid for MySQL only.
is_num: Reference to an array of boolean values; TRUE indicates, that therespective column contains numeric values.
is_pri_key: Reference to an array of boolean values; TRUE indicates, that therespective column is a primary key. This is only valid for MySQLand mSQL 1.0.x: mSQL 2.x uses indices.
is_not_null: A reference to an array of boolean values; FALSE indicates that thiscolumn may contain NULL's. You should better use the NULLABLEattribute above which is a DBI standard.
length
max_length: A reference to an array of maximum column sizes. The max_length isthe maximum physically present in the result table, length givesthe theoretically possible maximum. max_length is valid for MySQLonly.
NAME: A reference to an array of column names.
NULLABLE: A reference to an array of boolean values; TRUE indicates that this columnmay contain NULL's.
NUM_OF_FIELDS: Number of fields returned by a SELECT or LISTFIELDS statement.You may use this for checking whether a statement returned a result:A zero value indicates a non-SELECT statement like INSERT,DELETE or UPDATE.
table: A reference to an array of table names, useful in a JOIN result.
type: A reference to an array of column types. It depends on the DBMS,which values are returned, even for identical types. mSQL willreturn types like &DBD::mSQL::INT_TYPE, &DBD::msql::TEXT_TYPE etc.,MySQL uses &DBD::mysql::FIELD_TYPE_SHORT, &DBD::mysql::FIELD_TYPE_STRING etc.

COMPATIBILITY ALERT

As of version 0.70 DBD::mSQL has a new maintainer. Even more, the sourceshave been completely rewritten in August 1997, so it seemed apropriateto bump the version number: Incompatibilities are more than likely.

Recent changes:

New connect method

DBD::mSQL and DBD::mysql now use the new connect method as introducedwith DBI 0.83 or so. For compatibility reasons the old method stillworks, but the driver issues a warning when he detects use of theold version. There's no workaround, you must update your sources.(Sorry, but the change was in DBI, not in DBD::mysql and DBD::mSQL.)

_ListFields returning statement handle

As of Msql-modules 1.1805, the private functions

    $dbh->func($table, "_ListFields");

and

    $sth->func("_ListSelectedFields");

no longer return a simple hash, but a statement handle.(_ListSelectedFields is a stub now which just returns $self.)This should usually not be visible, when your statement handle getsout of scope. However, if your database handle ($dbh in theabove example) disconnects, either because you explicitly disconnector because he gets out of scope, and the statement handle is stillactive, DBI will issue a warning for active cursors being destroyed.

The simple workaround is to execute $sth->finish or to ensurethat $sth gets out of scope before $dbh. Sorry, but it wasobvious nonsense to support two different things for accessing thebasically same thing: A M(y)SQL result.

The drivers do not conform to the current DBI specification in some minorpoints. For example, the private attributes is_num or is_blob havebeen written IS_NUM and IS_BLOB. For historical reasons we continuesupporting the capitalized names, although the DBI specification nowreserves capitalized names for standard names, mixed case for DBI and lowercase for private attributes and methods.

We currently consider anything not conforming to the DBI as deprecated.It is quite possible that we remove support of these deprecated namesand methods in the future. In particular these includes:

$sth->func($table, '_ListSelectedFields')

highly deprecated, all attributes are directly accessible via thestatement handle. For example instead of

  $ref = $sth->func($table, '_ListSelectedFields')  my @names = $ref->{'NAME'}

you just do a

  my @names = @{$sth->{'NAME'}};

Capitalized attribute names

Deprecated, should be replaced by the respective lower case names.

BUGS

The port part of the first argument to the connect call isimplemented in an unsafe way. In fact it never did more than setthe environment variable MSQL_TCP_PORT during the connect call. Ifanother connect call uses another port and the handles are usedsimultaneously, they will interfere. In a future version thisbehaviour will hoefully change, depending on David and Monty. :-)

The func method call on a driver handle seems to be undocumented inthe DBI manpage. DBD::mSQL has func methods on driverhandles, databasehandles, and statement handles. What gives?

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

When running ``make test'', you will notice that some test scripts fail.This is due to bugs in the respective databases, not in the DBI drivers:

Nulls

mSQL seems to have problems with NULL's: The following fails withmSQL 2.0.1 running on a Linux 2.0.30 machine:

    [joeAATTlaptop Msql-modules-1.18]$ msql test    Welcome to the miniSQL monitor.  Type \h for help.    mSQL > CREATE TABLE foo (id INTEGER, name CHAR(6))\g    Query OK.  1 row(s) modified or retrieved.    mSQL > INSERT INTO foo VALUES (NULL, 'joe')\g    Query OK.  1 row(s) modified or retrieved.    mSQL > SELECT * FROM foo WHERE id = NULL\g    Query OK.  0 row(s) modified or retrieved.    +----------+------+    | id       | name |    +----------+------+    +----------+------+    mSQL >

Blanks

mysql has problems with Blanks on the right side of string fields: Theyget chopped of. (Tested with mysql 3.20.25 on a Linux 2.0.30 machine.)

    [joeAATTlaptop Msql-modules-1.18]$ mysql test    Welcome to the mysql monitor.  Commands ends with ; or \g.    Type 'help' for help.    mysql> CREATE TABLE foo (id INTEGER, bar CHAR(8));    Query OK, 0 rows affected (0.10 sec)    mysql> INSERT INTO foo VALUES (1, ' a b c ');    Query OK, 1 rows affected (0.00 sec)    mysql> SELECT * FROM foo;    1 rows in set (0.19 sec)    +------+--------+    | id   | bar    |    +------+--------+    |    1 |  a b c |    +------+--------+    mysql> quit;    [joeAATTlaptop Msql-modules-1.18]$ mysqldump test foo

    [deleted]

    INSERT INTO foo VALUES (1,' a b c');

AUTHOR

DBD::mSQL has been primarily written by Alligator Descartes(descarteAATThermetica.com), who has been aided and abetted by GaryShea, Andreas Koenig and Tim Bunce amongst others. Apologies if yourname 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 portionsCopyright (c)1994-1997 their original authors. This module isreleased under the `Artistic' license which you can find in the perldistribution.

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

ADDITIONAL DBI INFORMATION

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

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

where documentation, pointers to the mailing lists and mailing listarchives and pointers to the most current versions of the modules canbe used.

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

    perldoc DBI

right now!

Interface (DBI)"

This document was created byman2html,using the manual pages.