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;
# Connect
my ($host, $user, $password, $db) = ('localhost', 'test', 'test', 'test');
my $dsn = "DBI:Wire10:host=$host;database=$db";
my $dbh = DBI->connect($dsn, $user, $password);
# CREATE TABLE example
my $sth = $dbh->prepare("CREATE TABLE foo (id INT, message TEXT)");
$sth->execute;
# INSERT example
$sth = $dbh->prepare("INSERT INTO foo (id, message) VALUES (?, ?)");
$sth->execute(1, 'Hello World!');
$sth->execute(2, 'Goodbye, Cruel World!');
printf "Affected rows: %d\n", $sth->rows;
# SELECT example
$sth = $dbh->prepare("SELECT * FROM foo");
$sth->execute;
printf "Selected rows: %d / columns: %d\n",
$sth->{rows},
$sth->{NUM_OF_FIELDS}
;
while (my $row = $sth->fetchrow_arrayref()) {
printf
"Id: %s, Message: %s\n",
$row->[0],
$row->[1]
;
}
$dbh->disconnect;INSTALLATION
DBD::Wire10 is installed like any other CPAN module:
$ perl -MCPANPLUS -eshell
CPAN Terminal> install DBD::Wire10For 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 directly by this driver. Any DBI feature that internally make use of any of the following features are also supported.
Refer to the DBI::DBI documentation for complete information on all available methods and attributes.
It is unlikely that you will want to make use of any of the methods and attributes documented here as "(internal)", you can probably skip reading about those.
Features in DBD::Wire10
Driver factory: methods
Methods available from the DBD::Wire10 driver factory.
driver (internal)
Creates a new driver.
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, 9306 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_connect_timeout and wire10_query_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.
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.
last_insert_id
Returns the auto-increment value generated by the last INSERT statement executed.
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 wire10_connect_timeout seconds, then a new underlying connection is established, and establishing this connection could last an additional wire10_connect_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.
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.
wire10_connect_timeout
The timeout, in seconds, before the driver stops waiting for data from the network when connecting to a server.
wire10_query_timeout
The timeout, in seconds, before the driver stops waiting for data from the network when executing a query.
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.
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 statement (or other blocking protocol command, such as ping()). Safe to call from another thread, but note that DBI currently prevents this. Safe to call from a signal handler.
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, just set the wire10_query_timeout attribute to an appropriate number of seconds.
Always returns 1 (success). The actual status of the query (finished or cancelled, depending on timing) appears in the thread which is running the query.
Use cancel() to abort a query when the user presses CTRL-C:
$SIG{INT} = sub { $sth->cancel; };Notice that the driver core will terminate the connection when a cancel() is performed. A call to reconnect() is thus required after a statement has been cancelled in order to reestablish the connection:
{
local $SIG{INT} = sub { $sth->cancel; };
$sth->execute;
}
$dbh->reconnect;If a cancel happens to be performed after the current command has finished executing (in a so-called race condition), it will instead take effect during the next command. To avoid that the next user query is unduly aborted, a cancel can be flushed out of the system with a ping() (or a reconnect() which implicitly does a ping()). In the example above, reconnect() is always performed, thus resolving the race condition.
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.
There is a multitude of other fetch methods available, such as fetchrow_hashref. These methods are implemented in DBI, they internally make use of fetchrow_arrayref to retrieve result data. Refer to the DBI documentation for more information on the various fetch methods.
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.
Statement: attributes
wire10_insertid
Contains the auto_increment value for the last row inserted.
my $id = $sth->{wire10_insertid};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.
NULLABLE
Returns an array indicating for each column whether it has a NOT NULL constraint.
TROUBLESHOOTING
Supported operating systems and Perl versions
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
Differences from DBD-MySQL
Unicode always enabled
This driver always runs in a mode where international characters outside of the currently active ANSI code page are supported.
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.
Automatic reconnect
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.
A good time and place to add a call to reconnect() could be when a connection is first used after a long period of inactivity, plus at any point in your code where it is safe and appropriate to restart processing when an error occurs.
Automatic numerical trim
Numerical string values bound via bind_param() and provided via execute() parameters are not automatically trimmed of whitespace, even if they look like numbers.
Various missing protocol features
Various connection methods and other protocol features are not supported by the underlying driver. See the "Unsupported features" chapter in the Net::Wire10 documentation for more information.
Supported DBI methods and attributes
Some methods are not yet supported in this driver, in particular type_info_all, table_info, column_info, primary_key_info and foreign_key_info. Some attributes are not yet supported, in particular TYPE.
Supported mysql_ attributes
All of the mysql_ attributes are unavailable. DBI requires that each driver uses a unique prefix, therefore this driver supports only attributes named wire10_.
Not all mysql_ attributes have equivalently named wire10_ attributes. For example, there is no mysql_use_result attribute, but one called wire10_streaming does exactly the same.
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.
SEE ALSO
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
Copyright (C) 2002 and (C) 2009 as described in AUTHORS.
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.