DBD::mysqlPP - Pure Perl MySQL driver for the DBI

- NAME
- SYNOPSIS
- EXAMPLE
- DESCRIPTION

- Class Methods
- MetaData Method
- Private MetaData Methods

- DATABASE HANDLES
- STATEMENT HANDLES
- INSTALLATION
- SUPPORT OPERATING SYSTEM
- DEPENDENCIES
- DIFFERENCE FROM ``DBD::mysql''

- Parameter of Cnstructor
- Private MetaData Methods
- Server Administration
- Database Handles
- Statement Handles
- SQL Extensions

- TODO
- SEE ALSO
- AUTHORS
- COPYRIGHT AND LICENCE

# NAME

DBD::mysqlPP - Pure Perl MySQL driver for the DBI

# SYNOPSIS

        use DBI;

        $dsn = "dbi:mysqlPP:database=$database;host=$hostname";

        $dbh = DBI->connect($dsn, $user, $password);

        $drh = DBI->install_driver("mysqlPP");

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

# EXAMPLE

      #!/usr/bin/perl

      use strict;
      use DBI;

      # Connect to the database.
      my $dbh = DBI->connect("dbi:mysqlPP:database=test;host=localhost",
                             "joe", "joe's password",
                             {'RaiseError' => 1});

      # Drop table 'foo'. This may fail, if 'foo' doesn't exist.
      # Thus we put an eval around it.
      eval { $dbh->do("DROP TABLE foo") };
      print "Dropping foo failed: $@\n" if $@;

      # Create a new table 'foo'. This must not fail, thus we don't
      # catch errors.
      $dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))");

      # INSERT some data into 'foo'. We are using $dbh->quote() for
      # quoting the name.
      $dbh->do("INSERT INTO foo VALUES (1, " . $dbh->quote("Tim") . ")");

      # Same thing, but using placeholders
      $dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen");

      # Now retrieve data from the table.
      my $sth = $dbh->prepare("SELECT id, name FROM foo");
      $sth->execute();
      while (my $ref = $sth->fetchrow_arrayref()) {
        print "Found a row: id = $ref->[0], name = $ref->[1]\n";
      }
      $sth->finish();

      # Disconnect from the database.
      $dbh->disconnect();

# DESCRIPTION

DBD::mysqlPP is a Pure Perl client interface for the MySQL database. This module implements network protool between server and client of MySQL, thus you don't need external MySQL client library like libmysqlclient for this module to work. It means this module enables you to connect to MySQL server from some operation systems which MySQL is not ported. How nifty!

From perl you activate the interface with the statement

        use DBI;

After that you can connect to multiple MySQL database servers and send multiple queries to any of them via a simple object oriented interface. Two types of objects are available: database handles and statement handles. Perl returns a database handle to the connect method like so:

      $dbh = DBI->connect("dbi:mysqlPP:database=$db;host=$host",
                          $user, $password, {RaiseError => 1});

Once you have connected to a database, you can can execute SQL statements with:

      my $query = sprintf("INSERT INTO foo VALUES (%d, %s)",
                          $number, $dbh->quote("name"));
      $dbh->do($query);

See DBI(3) for details on the quote and do methods. An alternative approach is

      $dbh->do("INSERT INTO foo VALUES (?, ?)", undef,
               $number, $name);

in which case the quote method is executed automatically. See also the bind_param method in DBI(3). See DATABASE HANDLES below for more details on database handles.

If you want to retrieve results, you need to create a so-called statement handle with:

      $sth = $dbh->prepare("SELECT id, name FROM $table");
      $sth->execute();

This statement handle can be used for multiple things. First of all you can retreive a row of data:

      my $row = $sth->fetchow_arrayref();

If your table has columns ID and NAME, then $row will be array ref with index 0 and 1. See STATEMENT HANDLES below for more details on statement handles.

I's more formal approach:

## Class Methods

connect

          use DBI;

          $dsn = "dbi:mysqlPP:$database";
          $dsn = "dbi:mysqlPP:database=$database;host=$hostname";
          $dsn = "dbi:mysqlPP:database=$database;host=$hostname;port=$port";

          $dbh = DBI->connect($dsn, $user, $password);

  A `database` must always be specified.

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

port
  Port where MySQL daemon listens to. default is 3306.

## MetaData Method

tables

          @names = $dbh->tables;

  Returns a list of table and view names, possibly including a schema prefix. This list should include all tables that can be used in a ``SELECT'' statement without further qualification.

## Private MetaData Methods

ListDBs

          @dbs = $dbh->func('_ListDBs');

  Returns a list of all databases managed by the MySQL daemon.

ListTables
  WARNING: This method is obsolete due to DBI's $dbh->tables().

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

  Once connected to the desired database on the desired mysql 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";
          }

# DATABASE HANDLES

The DBD::mysqlPP driver supports the following attributes of database handles (read only):

      $insertid = $dbh->{'mysqlpp_insertid'};
      $insertid = $dbh->{'mysql_insertid'};

# STATEMENT HANDLES

The statement handles of DBD::mysqlPP support a number of attributes. You access these by using, for example,

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

mysqlpp_insertid/mysql_insertid
  MySQL has the ability to choose unique key values automatically. If this happened, the new ID will be stored in this attribute. An alternative way for accessing this attribute is via $dbh->{'mysqlpp_insertid'}. (Note we are using the $dbh in this case!)

NUM_OF_FIELDS
  Number of fields returned by a SELECT 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.

# INSTALLATION

To install this module type the following:

       perl Makefile.PL
       make
       make test
       make install

# SUPPORT OPERATING SYSTEM

This module has been tested on these OSes.

- MacOS 9.x
with MacPerl5.6.1r.

- MacOS X
with perl5.6.0 build for darwin.

- Windows2000
with ActivePerl5.6.1 build631.

- FreeBSD 3.4 and 4.x
with perl5.6.1 build for i386-freebsd.

with perl5.005_03 build for i386-freebsd.

# DEPENDENCIES

This module requires these other modules and libraries:

      DBI
      Net::MySQL

Net::MySQL is a Pure Perl client interface for the MySQL database.

Net::MySQL implements network protool between server and client of MySQL, thus you don't need external MySQL client library like libmysqlclient for this module to work. It means this module enables you to connect to MySQL server from some operation systems which MySQL is not ported. How nifty!

# DIFFERENCE FROM ``DBD::mysql''

The function of DBD::mysql which cannot be used by DBD::mysqlPP is described.

## Parameter of Cnstructor

Cannot be used.

- msql_configfile
- mysql_compression
- mysql_read_default_file/mysql_read_default_group
- mysql_socket

## Private MetaData Methods

These methods cannot be used for $drh.

- ListDBs
- ListTables

## Server Administration

All `func()` method cannot be used.

- `func('createdb')`
- `func('dropdb')`

    =item * func('shutdown')

- `func('reload')`

## Database Handles

Cannot be used

- $dbh->{info}

## Statement Handles

A different part.

- The return value of execute('SELECT * from table')
Although DBD::mysql makes a return value the number of searched records SQL of SELECT is performed, DBD::mysqlPP surely returns 0E0.

Cannot be used.

- 'mysql_use_result' attribute
- 'ChopBlanks' attribute
- 'is_blob' attribute
- 'is_key' attribute
- 'is_num' attribute
- 'is_pri_key' attribute
- 'is_not_null' attribute
- 'length'/'max_length' attribute
- 'NUUABLE' attribute
- 'table' attribute
- 'TYPE' attribute
- 'mysql_type' attribute
- 'mysql_type_name' attributei

## SQL Extensions

Cannot be used.

- LISTFIELDS
- LISTINDEX

# TODO

Encryption of the password independent of Math::BigInt.

Enables access to much metadata.

# SEE ALSO

the Net::MySQL manpage, the DBD::mysql manpage

# AUTHORS

Hiroyuki OYAMA <oyama@module.jp>

# COPYRIGHT AND LICENCE

Copyright (C) 2002 Hiroyuki OYAMA. Japan. All rights reserved.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

DBD::mysqlPP - Pure Perl MySQL driver for the DBI