NAME

DBD::Wire10 - Pure Perl MySQL, Sphinx, and Drizzle driver for DBI.

DESCRIPTION

DBD::Wire10 is a Pure Perl interface able to connect to MySQL, Sphinx and Drizzle servers, utilizing Net::Wire10 for the actual driver core.

SYNOPSIS

use DBI;

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

$dsn = "DBI:Wire10:database=$database;host=$host";

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

$sth = $dbh->prepare("SELECT * FROM foo WHERE bar=1");
$sth->execute;
$numCols = $sth->{NUM_OF_FIELDS};
$numRows = $sth->{wire10_selectedrows};
$sth->finish;

$sth = $dbh->prepare("UPDATE foo SET bar=0 WHERE bar=1");
$sth->execute;
$numRows = $sth->rows;
$sth->finish;

INSTALLATION

DBD::Wire10 is installed like any other CPAN module:

$ perl -MCPAN -e shell
cpan> install DBD::Wire10

For Perl installations where the CPAN module (used above) is missing, you can also just download the .tar.gz from this site and drop the DBD folder in the same folder as the Perl file you want to use the connector from.

Some (particularly commercial) Perl distributions may have their own package management systems. Refer to the documentation that comes with your particular Perl distribution for details.

USAGE

From Perl you just need to make use of DBI to get started:

use DBI;

After that you can connect to servers and send queries via a simple object oriented interface. Two types of objects are mainly used: database handles and statement handles. DBI returns a database handle via the connect() method.

Example: connect

use DBI;

my $host = 'localhost';
my $user = 'test';
my $password = 'test';

# Connect to the database server on 'localhost'.
my $dbh = DBI->connect(
  "DBI:Wire10:host=$host",
  $user, $password,
  {RaiseError' => 1, 'AutoCommit' => 1}
);

Example: create table

# 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'. If this fails, we don't want to
# continue, thus we don't catch errors.
$dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))");

Example: insert data

# 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");

Example: retrieve data

# 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;

Example: disconnect

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

FEATURES

The following DBI features are supported.

Features in `DBD::Wire10`

Driver factory: methods

Methods available from the DBD::Wire10 driver factory.

driver (internal)

Creates a new driver.

CLONE (internal)

Helper that decouples non-shareable driver internals when the application needs to create a new process using fork().

Features in `DBD::Wire10::dr`

Driver: methods

connect

Creates a new driver core and dbh object, returns the dbh object.

A DSN is specified in the usual format and connect() is called via DBI:

my $dsn = "DBI:Wire10:database=$database;host=$host;port=$port";
my $options = {'RaiseError'=>1, 'Warn'=>1};
my $dbh = DBI->connect($dsn, $user, $password, $options);

The default port numbers are 3306 for MySQL Server, 3307 for Sphinx and 4427 for Drizzle. Some server types support multiple protocols, in which case they may also listen on other, unrelated ports.

wire10_debug can be specified in the attribute hash for very noise debug output. It is a bitmask, where 1 shows normal debug messages, 2 shows messages flowing back and forth between client and server, and 4 shows raw TCP traffic.

wire10_timeout can be specified in the attribute hash to set a connect and query timeout. Otherwise, the driver's default values are used.

Warn can be specified to 1 in the attribute hash to output warnings when some silly things are attempted.

ShowErrorStatement can be specified to 1 in the attribute hash to include the prepared statement in output when an error occurs.

RaiseError can be specified to 1 in the attribute hash to enable error handling. Use eval { ... }; guard blocks to catch errors. After the guard block, the special variable $@ is either undefined or contains an error message.

PrintError can be specified to 1 in the attribute hash to disable error handling, and instead print a line on the console and continue execution whenever an error happens.

data_sources

Implemented, but does not return a list of databases, just a blank entry with the name of the driver.

Features in `DBD::Wire10::db`

Database server connection: methods

Some methods have default implementations in DBI, those are not listed here. Refer also to the DBI::DBI documentation.

quote

Quotes a string literal.

MySQL Server chokes if you give it LIMIT parameters that are properly quoted. To work around this, the quote() method specifically does not quote purely numerical values. To quote all values, including numerical values, use Net::Wire10::Util::quote().

quote_identifier

Quotes a schema identifier such as database or table names.

prepare

Given an SQL string, prepares a statement for executing.

Question marks (?) can be used in place of parameters. Actual parameters can then be added later either with a call to bind_param(), or when calling execute().

get_info

Returns various information about the database server when given a code for the particular information to retrieve.

commit

Commits the active transaction.

rollback

Rolls back the active transaction.

disconnect

Disconnects from the database server.

ping

Sends a ping over the network protocol. An error is reported via the standard DBI error mechanism if this fails.

reconnect

Makes sure that there is a connection to the database server. If there is no connection, and the attempt to reconnect fails, an error is reported via the standard DBI error reporting mechanism.

Notice that the timeout when calling this method is in a sense doubled. reconnect() first performs a ping() if the connection seems to be alive. If the ping fails after timeout seconds, then a new underlying connection is established, and establishing this connection could last an additional timeout seconds.

err

Contains an error code when an error has happened. Always use RaiseError and eval {} to catch errors in production code.

state

Contains an SQLSTATE code when an error has happened.

errstr

Contains an error message when an error has happened. Always use RaiseError and eval {} to catch errors in production code.

take_imp_data (internal)

Retrieves a reference to the core driver object and nukes the DBI handle that previously owned it.

STORE (internal)

Used internally to store attributes.

FETCH (internal)

Used internally to fetch attributes.

DESTROY (internal)

Used internally to destroy the object.

Database server connection: attributes

Some attributes have default implementations in DBI, those are not listed here. Refer also to the DBI::DBI documentation.

AutoCommit

Enables or disables automatic commit after each query, in effect wrapping each query in an implicit transaction.

Warn

If enabled, warnings are emitted when unexpected things might occur.

ShowErrorStatement

If enabled, the prepared statement stored by the driver upon a call to prepare() is included in the output when an error occurs.

Using absolute notation such as SELECT * FROM db.table rather than USE db combined with SELECT * FROM table will give more precise debug output (and perform better).

wire10_timeout

The timeout, in seconds, before the driver stops waiting for data from the network when executing a command or connecting to a server.

wire10_thread_id

Returns the connection id of the current connection on the server.

wire10_server_version

Returns the server version of the currently connected-to server.

wire10_debug

A debug bitmask, which when enabled will spew a lots of messages to the console. 1 shows normal debug messages, 2 shows messages flowing back and forth between client and server, and 4 shows raw TCP traffic.

wire10_insertid (imposed by sth->execute)

Contains the auto_increment value for the last row inserted. Always use $sth->{wire10_insertid} instead, if the $sth object is available, since this value can be overwritten by irrelevant transactions.

Potentially useful for single-threaded applications that make use of DBI commands which does not return $sth objects, such as do().

wire10_selectedrows (imposed by sth->execute)

Contains the number of rows returned in the last result set. Always use $sth->{wire10_selectedrows} instead, if the $sth object is available, since this value can be overwritten by irrelevant transactions.

wire10_warning_count (imposed by sth->execute)

Contains the number of warnings produced by the last query. Always use $sth->{wire10_warning_count} instead, if the $sth object is available, since this value can be overwritten by irrelevant transactions.

Active (internal)

Used internally to notify DBI that connect() has been called and disconnect() has not.

wire10_driver_dbh (internal)

This is the internal handle for the core driver object associated with the DBI connection.

wire10_autocommit (internal)

Used internally to store the current AutoCommit setting.

Features in `DBD::Wire10::st`

Statement: methods

bind_param

Given an index and a value, binds that value to the parameter at the given index in the prepared statement. Use after prepare() and before execute().

To bind binary data to a parameter, specify a type such as SQL_BLOB. This prevents the data from being considered Latin-1 or Unicode text. Example:

$sth->bind_param(1, $mydata, SQL_BLOB);

Parameters are numbered beginning from 1. SQL types are defined as optional exports in DBI:

use DBI qw(:sql_types);

execute

Runs a prepared statement, optionally using parameters. Parameters are supplied either via bind_param(), or directly in the call to execute(). When parameters are given in the call to execute(), they override earlier bound parameters for the duration of the call.

cancel

Cancels the currently executing command (query or ping). Safe to call from another thread, but note that DBI currently prevents this.

Non-threaded approaches to invoking cancel() such as SIGALRM is generally unsupported by Windows distributions of Perl, so the above affects that query cancelling is only supported on Unix. A workaround (for this DBD driver) is to grab a reference to the internal driver core and call cancel() on that instead.

Use cancel for interactive code only, where a user may cancel an operation at any time. Do not use cancel for setting query timeouts. For that, use the timeout property instead. The timeout property has slightly better performance, because it does not precipitate creation of an extra thread.

Always returns 1 (success). The actual status of the query (finished or cancelled, depending on timing) appears in the thread which is running the actual query.

The driver core fetches all results in execute(), so cancelling later on during a fetch does nothing to the current statement.

If a cancel happens to be performed after the current command has finished executing, it will instead take effect during the next command. In that case, the cancel can be forced out of the system with a ping(), or a reconnect() which implicitly does a ping().

finish

Clears out the resources used by a statement. This is called automatically at the start of a new query, among other places, and is therefore normally not necessary to call explicitly.

fetchrow_arrayref

Fetch one row as an array.

fetch

Deprecated alias for fetchrow_arrayref.

rows

The number of affected rows after an UPDATE or similar query, or the number of rows so far read by the client during a SELECT or similar query.

STORE (internal)

Used internally to store attributes.

FETCH (internal)

Used internally to fetch attributes.

DESTROY (internal)

Used internally to destroy the object.

Statement: attributes

wire10_insertid

Contains the auto_increment value for the last row inserted.

my $id = $sth->{wire10_insertid};

wire10_selectedrows

Contains the number of rows returned in the last result set.

my $numRows = $sth->{wire10_selectedrows};

wire10_streaming

If this is set to 1 (or any value that evaluates to true), results will be streamed from the server rather than downloaded all at once, when the statement is executed.

$sth->{wire10_streaming} = 1;

Notice that the underlying protocol has a limitation: when a streaming statement is active, no other statements can execute on the same connection.

wire10_warning_count

Contains the number of warnings produced by the last query.

my $warnings = $sth->{wire10_warning_count};

ChopBlanks

If enabled, runs every field value in result sets through a regular expression that trims for whitespace.

NUM_OF_PARAMS

Returns the number of parameter tokens found in the prepared statement after a prepare().

NUM_OF_FIELDS

Returns the number of columns in the result set after a query has been executed.

my $numCols = $sth->{NUM_OF_FIELDS};

NAME

Returns the names of all the columns in the result set after a query has been executed.

Active (internal)

Used internally to notify DBI that the statement has an active result iterator.

wire10_driver_sth (internal)

Used internally to store a reference to the core driver object.

wire10_iterator (internal)

Used internally to store a reference to the active result iterator, if any.

wire10_prepared (internal)

Used internally to store a reference to the prepared statement created by the core driver.

wire10_rows (internal)

Used internally to store the number of affected rows.

TROUBLESHOOTING

Supported operating systems and Perl versions

This module has been tested on these OSes.

Windows Server 2008

with ActivePerl 5.10.0 build 1004, 32 and 64-bit

The build script dependencies do not contain any version numbers, because nobody has any clue what the minimum requirements are for using this package. It is very recommendable, however, not to mix and match versions of Net::Wire10 and DBD::Wire10. For best results use the newest version of both packages.

Over at CPAN Testers, there's a vast number of testers that do a very good job of figuring out which versions work together: http://static.cpantesters.org/distro/N/DBD-Wire10.html

Feel free to send in reports of success or failure using different platforms.

Unsupported features

DBI has a rich set of reference features, some of which are not implemented by each individual driver.

In general, it should be possible to check for particular features before using them with the can() method, available on all types of DBI handles, and raise an error if a required feature is not supported.

Here is a list of some notable features that this driver does not yet have.

See also the documentation for "Unsupported features" in Net::Wire10 for limitations in the driver core.

Unsupported database server connection features in `DBD::Wire10::db`

The following methods are supposed to be implemented by the DBD connection, but is not (yet) supported in this driver. For an up-to-date list, see the DBI documentation.

type_info_all
type_info
table_info (and tables)
```
@names = $dbh->tables;
```
Should return 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.
column_info
primary_key_info (and primary_key)
foreign_key_info
statistics_info
list_tables
last_insert_id

The following attributes are supposed to be implemented by each DBD driver, but is not (yet) supported in this driver. For an up-to-date list, see the DBI documentation.

Name
RowCacheSize
LongReadLen
LongTruncOk

Unsupported statement features in `DBD::Wire10::st`

The following methods are supposed to be implemented by each DBD driver, but is not (yet) supported in this driver. For an up-to-date list, see the DBI documentation.

bind_col
bind_columns
bind_param_inout

The following attributes are supposed to be implemented by each DBD driver, but is not (yet) supported in this driver. For an up-to-date list, see the DBI documentation.

TYPE
PRECISION
SCALE
NULLABLE
CursorName
RowsInCache
ParamValues
ParamArrays
ParamTypes

Supported workarounds

Using tokens for LIMITs in prepared statements

MySQL Server chokes if you give it LIMIT parameters that are properly quoted. As a workaround, this DBD driver scans all field values given to it, and when a purely numerical value is found, it is not quoted.

Differences from DBD-MySQL

Binary data must be bound

Binary/BLOB data must be given as a bound parameter (see bind_param) using fx. the SQL_BLOB flag. When using any other method, strings will as a default be interpreted as text.

Finding the number of selected rows

The rows attribute on a statement always returns -1, as specified in the DBI documentation. A custom attribute, wire10_selectedrows, is available to retrieve the number of selected rows.

The same applies to the return value of execute('SELECT * FROM ...'). DBD::Wire10 returns true on success, and false on failure, as specified in DBI documentation. This differs from DBD::mysql, which returns the number of selected rows.

To get the number of rows from a SELECT statement, use this:

my $numRows = $sth->{wire10_selectedrows};

If streaming is turned on for the statement, the number of rows selected is unknown and the above always returns -1.

The wire protocol does not have any means by which the server can tell the client how many rows are in the result set, even if the server should discover this at some point. Therefore the row count is only available after the entire result set has been pulled down to the client.

Enabling streaming

The mysql_use_result attribute is unavailable. Another attribute, wire10_streaming, does exactly the same.

Flipping result set with more_results()

The more_results() method currently does nothing, because the underlying driver does not yet support queries that return multiple result sets.

As an alternative, grab two (or more) connections from the connection pool and execute one query on each connection simultaneously. This also has the advantage that the queries can be executed in parallel.

No implicit reconnects

Automatic reconnection is not performed when a connection fails mid-execution. The corresponding DBD-MySQL options auto_reconnect and mysql_init_command are therefore unavailable.

The driver expects you to call reconnect() at any time you wish to check the connection status and (if need be) reestablish a connection with the server.

No type guessing

No automatic conversion between types are done. Weird string values are not interpreted as numbers when bound and specified as SQL_INTEGER, and vice-versa.

Dependencies

This module requires these other modules and libraries:

L<DBI::DBI>
L<Net::Wire10>

Net::Wire10 is a Pure Perl connector for MySQL, Sphinx and Drizzle servers.

Net::Wire10 implements the network protool used to communicate between server and client.

AUTHORS

DSN parsing and various code by Hiroyuki OYAMA E, Japan. DBD boilerplate by DBD authors. Various code by the open source team at Dubex A/S.

COPYRIGHT AND LICENCE

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

WARRANTY

Because this software is licensed free of charge, there is absolutely no warranty of any kind, expressed or implied.

To install DBD::Wire10, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBD::Wire10

CPAN shell

perl -MCPAN -e shell
install DBD::Wire10

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

DESCRIPTION

SYNOPSIS

INSTALLATION

USAGE

Example: connect

Example: create table

Example: insert data

Example: retrieve data

Example: disconnect

FEATURES

Features in DBD::Wire10

Driver factory: methods

driver (internal)

CLONE (internal)

Features in DBD::Wire10::dr

Driver: methods

connect

data_sources

Features in DBD::Wire10::db

Database server connection: methods

quote

quote_identifier

prepare

get_info

commit

rollback

disconnect

ping

reconnect

err

state

errstr

take_imp_data (internal)

STORE (internal)

FETCH (internal)

DESTROY (internal)

Database server connection: attributes

AutoCommit

Warn

ShowErrorStatement

wire10_timeout

wire10_thread_id

wire10_server_version

wire10_debug

wire10_insertid (imposed by sth->execute)

wire10_selectedrows (imposed by sth->execute)

wire10_warning_count (imposed by sth->execute)

Active (internal)

wire10_driver_dbh (internal)

wire10_autocommit (internal)

Features in DBD::Wire10::st

Statement: methods

bind_param

execute

cancel

finish

fetchrow_arrayref

fetch

rows

STORE (internal)

FETCH (internal)

DESTROY (internal)

Statement: attributes

wire10_insertid

wire10_selectedrows

wire10_streaming

wire10_warning_count

ChopBlanks

NUM_OF_PARAMS

NUM_OF_FIELDS

NAME

Active (internal)

wire10_driver_sth (internal)

wire10_iterator (internal)

wire10_prepared (internal)

wire10_rows (internal)

TROUBLESHOOTING

Supported operating systems and Perl versions

Unsupported features

Features in `DBD::Wire10`

Features in `DBD::Wire10::dr`

Features in `DBD::Wire10::db`

Features in `DBD::Wire10::st`

Unsupported database server connection features in `DBD::Wire10::db`

Unsupported statement features in `DBD::Wire10::st`