# -*- cperl -*- package DBD::mysql; use strict; use vars qw(@ISA $VERSION $err $errstr $drh); use DBI (); use DynaLoader(); use Carp (); @ISA = qw(DynaLoader); $VERSION = '2.9008'; bootstrap DBD::mysql $VERSION; $err = 0; # holds error code for DBI::err $errstr = ""; # holds error string for DBI::errstr $drh = undef; # holds driver handle once initialised sub driver{ return $drh if $drh; my($class, $attr) = @_; $class .= "::dr"; # not a 'my' since we use it above to prevent multiple drivers $drh = DBI::_new_drh($class, { 'Name' => 'mysql', 'Version' => $VERSION, 'Err' => \$DBD::mysql::err, 'Errstr' => \$DBD::mysql::errstr, 'Attribution' => 'DBD::mysql by Rudy Lippan' }); $drh; } sub CLONE { undef $drh; } sub _OdbcParse($$$) { my($class, $dsn, $hash, $args) = @_; my($var, $val); if (!defined($dsn)) { return; } while (length($dsn)) { if ($dsn =~ /([^:;]*)[:;](.*)/) { $val = $1; $dsn = $2; } else { $val = $dsn; $dsn = ''; } if ($val =~ /([^=]*)=(.*)/) { $var = $1; $val = $2; if ($var eq 'hostname' || $var eq 'host') { $hash->{'host'} = $val; } elsif ($var eq 'db' || $var eq 'dbname') { $hash->{'database'} = $val; } else { $hash->{$var} = $val; } } else { foreach $var (@$args) { if (!defined($hash->{$var})) { $hash->{$var} = $val; last; } } } } } sub _OdbcParseHost ($$) { my($class, $dsn) = @_; my($hash) = {}; $class->_OdbcParse($dsn, $hash, ['host', 'port']); ($hash->{'host'}, $hash->{'port'}); } sub AUTOLOAD { my ($meth) = $DBD::mysql::AUTOLOAD; my ($smeth) = $meth; $smeth =~ s/(.*)\:\://; my $val = constant($smeth, @_ ? $_[0] : 0); if ($! == 0) { eval "sub $meth { $val }"; return $val; } Carp::croak "$meth: Not defined"; } 1; package DBD::mysql::dr; # ====== DRIVER ====== use strict; use DBI qw(:sql_types); sub connect { my($drh, $dsn, $username, $password, $attrhash) = @_; my($port); my($cWarn); # Avoid warnings for undefined values $username ||= ''; $password ||= ''; # create a 'blank' dbh my($this, $privateAttrHash) = (undef, $attrhash); $privateAttrHash = { %$privateAttrHash, 'Name' => $dsn, 'user' => $username, 'password' => $password }; DBD::mysql->_OdbcParse($dsn, $privateAttrHash, ['database', 'host', 'port']); if (!defined($this = DBI::_new_dbh($drh, {'Name' => $dsn}, $privateAttrHash))) { return undef; } # Call msqlConnect func in mSQL.xs file # and populate internal handle data. DBD::mysql::db::_login($this, $dsn, $username, $password) or $this = undef; if ($this && ($ENV{MOD_PERL} || $ENV{GATEWAY_INTERFACE})) { $this->{mysql_auto_reconnect} = 1; } $this; } sub data_sources { my($self) = shift; my($attributes) = shift; my($host, $port, $user, $password) = ('', '', '', ''); if ($attributes) { $host = $attributes->{host} || ''; $port = $attributes->{port} || ''; $user = $attributes->{user} || ''; $password = $attributes->{password} || ''; } my(@dsn) = $self->func($host, $port, $user, $password, '_ListDBs'); my($i); for ($i = 0; $i < @dsn; $i++) { $dsn[$i] = "DBI:mysql:$dsn[$i]"; } @dsn; } sub admin { my($drh) = shift; my($command) = shift; my($dbname) = ($command eq 'createdb' || $command eq 'dropdb') ? shift : ''; my($host, $port) = DBD::mysql->_OdbcParseHost(shift(@_) || ''); my($user) = shift || ''; my($password) = shift || ''; $drh->func(undef, $command, $dbname || '', $host || '', $port || '', $user, $password, '_admin_internal'); } package DBD::mysql::db; # ====== DATABASE ====== use strict; use DBI qw(:sql_types); %DBD::mysql::db::db2ANSI = ("INT" => "INTEGER", "CHAR" => "CHAR", "REAL" => "REAL", "IDENT" => "DECIMAL" ); ### ANSI datatype mapping to mSQL datatypes %DBD::mysql::db::ANSI2db = ("CHAR" => "CHAR", "VARCHAR" => "CHAR", "LONGVARCHAR" => "CHAR", "NUMERIC" => "INTEGER", "DECIMAL" => "INTEGER", "BIT" => "INTEGER", "TINYINT" => "INTEGER", "SMALLINT" => "INTEGER", "INTEGER" => "INTEGER", "BIGINT" => "INTEGER", "REAL" => "REAL", "FLOAT" => "REAL", "DOUBLE" => "REAL", "BINARY" => "CHAR", "VARBINARY" => "CHAR", "LONGVARBINARY" => "CHAR", "DATE" => "CHAR", "TIME" => "CHAR", "TIMESTAMP" => "CHAR" ); sub prepare { my($dbh, $statement, $attribs)= @_; # create a 'blank' dbh my $sth = DBI::_new_sth($dbh, {'Statement' => $statement}); # Populate internal handle data. if (!DBD::mysql::st::_prepare($sth, $statement, $attribs)) { $sth = undef; } $sth; } sub db2ANSI { my $self = shift; my $type = shift; return $DBD::mysql::db::db2ANSI{"$type"}; } sub ANSI2db { my $self = shift; my $type = shift; return $DBD::mysql::db::ANSI2db{"$type"}; } sub admin { my($dbh) = shift; my($command) = shift; my($dbname) = ($command eq 'createdb' || $command eq 'dropdb') ? shift : ''; $dbh->{'Driver'}->func($dbh, $command, $dbname, '', '', '', '_admin_internal'); } sub _SelectDB ($$) { die "_SelectDB is removed from this module; use DBI->connect instead."; } { my $names = ['TABLE_CAT', 'TABLE_SCHEM', 'TABLE_NAME', 'TABLE_TYPE', 'REMARKS']; sub table_info ($) { my $dbh = shift; my $sth = $dbh->prepare("SHOW TABLES"); return undef unless $sth; if (!$sth->execute()) { return DBI::set_err($dbh, $sth->err(), $sth->errstr()); } my @tables; while (my $ref = $sth->fetchrow_arrayref()) { push(@tables, [ undef, undef, $ref->[0], 'TABLE', undef ]); } my $dbh2; if (!($dbh2 = $dbh->{'~dbd_driver~_sponge_dbh'})) { $dbh2 = $dbh->{'~dbd_driver~_sponge_dbh'} = DBI->connect("DBI:Sponge:"); if (!$dbh2) { DBI::set_err($dbh, 1, $DBI::errstr); return undef; } } my $sth2 = $dbh2->prepare("SHOW TABLES", { 'rows' => \@tables, 'NAME' => $names, 'NUM_OF_FIELDS' => 5 }); if (!$sth2) { DBI::set_err($sth2, $dbh2->err(), $dbh2->errstr()); } $sth2; } } sub _ListTables { my $dbh = shift; if (!$DBD::mysql::QUIET) { warn "_ListTables is deprecated, use \$dbh->tables()"; } return map { $_ =~ s/.*\.//; $_ } $dbh->tables(); } sub column_info { my ($dbh, $catalog, $schema, $table, $column) = @_; return $dbh->set_err(1, "column_info doesn't support table wildcard") if $table !~ /^\w+$/; return $dbh->set_err(1, "column_info doesn't support column selection") if $column ne "%"; my $table_id = $dbh->quote_identifier($catalog, $schema, $table); my @names = qw( TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE CHAR_SET_CAT CHAR_SET_SCHEM CHAR_SET_NAME COLLATION_CAT COLLATION_SCHEM COLLATION_NAME UDT_CAT UDT_SCHEM UDT_NAME DOMAIN_CAT DOMAIN_SCHEM DOMAIN_NAME SCOPE_CAT SCOPE_SCHEM SCOPE_NAME MAX_CARDINALITY DTD_IDENTIFIER IS_SELF_REF mysql_is_pri_key mysql_type_name mysql_values ); my %col_info; local $dbh->{FetchHashKeyName} = 'NAME_lc'; my $desc_sth = $dbh->prepare("DESCRIBE $table_id"); my $desc = $dbh->selectall_arrayref($desc_sth, { Columns=>{} }); my $ordinal_pos = 0; foreach my $row (@$desc) { my $type = $row->{type}; $type =~ m/^(\w+)(?:\((.*?)\))?\s*(.*)/; my $basetype = lc($1); my $info = $col_info{ $row->{field} } = { TABLE_CAT => $catalog, TABLE_SCHEM => $schema, TABLE_NAME => $table, COLUMN_NAME => $row->{field}, NULLABLE => ($row->{null} eq 'YES') ? 1 : 0, IS_NULLABLE => ($row->{null} eq 'YES') ? "YES" : "NO", TYPE_NAME => uc($basetype), COLUMN_DEF => $row->{default}, ORDINAL_POSITION => ++$ordinal_pos, mysql_is_pri_key => ($row->{key} eq 'PRI'), mysql_type_name => $row->{type}, }; # This code won't deal with a pathalogical case where a value # contains a single quote followed by a comma, and doesn't unescape # any escaped values. But who would use those in an enum or set? my @type_params = ($2 && index($2,"'")>=0) ? ("$2," =~ /'(.*?)',/g) # assume all are quoted : split /,/, $2||''; # no quotes, plain list s/''/'/g for @type_params; # undo doubling of quotes my @type_attr = split / /, $3||''; #warn "$type: $basetype [@type_params] [@type_attr]\n"; $info->{DATA_TYPE} = SQL_VARCHAR(); if ($basetype =~ /^(char|varchar|\w*text|\w*blob)/) { $info->{DATA_TYPE} = SQL_CHAR() if $basetype eq 'char'; if ($type_params[0]) { $info->{COLUMN_SIZE} = $type_params[0]; } else { $info->{COLUMN_SIZE} = 65535; $info->{COLUMN_SIZE} = 255 if $basetype =~ /^tiny/; $info->{COLUMN_SIZE} = 16777215 if $basetype =~ /^medium/; $info->{COLUMN_SIZE} = 4294967295 if $basetype =~ /^long/; } } elsif ($basetype =~ /^(binary|varbinary)/) { $info->{COLUMN_SIZE} = $type_params[0]; # SQL_BINARY & SQL_VARBINARY are tempting here but don't match the # semantics for mysql (not hex). SQL_CHAR & SQL_VARCHAR are correct here. $info->{DATA_TYPE} = ($basetype eq 'binary') ? SQL_CHAR() : SQL_VARCHAR(); } elsif ($basetype =~ /^(enum|set)/) { if ($basetype eq 'set') { $info->{COLUMN_SIZE} = length(join ",", @type_params); } else { my $max_len = 0; length($_) > $max_len and $max_len = length($_) for @type_params; $info->{COLUMN_SIZE} = $max_len; } $info->{"mysql_values"} = \@type_params; } elsif ($basetype =~ /int/) { # big/medium/small/tiny etc + unsigned? $info->{DATA_TYPE} = SQL_INTEGER(); $info->{NUM_PREC_RADIX} = 10; $info->{COLUMN_SIZE} = $type_params[0]; } elsif ($basetype =~ /^decimal/) { $info->{DATA_TYPE} = SQL_DECIMAL(); $info->{NUM_PREC_RADIX} = 10; $info->{COLUMN_SIZE} = $type_params[0]; $info->{DECIMAL_DIGITS} = $type_params[1]; } elsif ($basetype =~ /^(float|double)/) { $info->{DATA_TYPE} = ($basetype eq 'float') ? SQL_FLOAT() : SQL_DOUBLE(); $info->{NUM_PREC_RADIX} = 2; $info->{COLUMN_SIZE} = ($basetype eq 'float') ? 32 : 64; } elsif ($basetype =~ /date|time/) { # date/datetime/time/timestamp if ($basetype eq 'time' or $basetype eq 'date') { $info->{DATA_TYPE} = ($basetype eq 'time') ? SQL_TYPE_TIME() : SQL_TYPE_DATE(); $info->{COLUMN_SIZE} = ($basetype eq 'time') ? 8 : 10; } else { # datetime/timestamp $info->{DATA_TYPE} = SQL_TYPE_TIMESTAMP(); $info->{SQL_DATA_TYPE} = SQL_DATETIME(); $info->{SQL_DATETIME_SUB} = $info->{DATA_TYPE} - ($info->{SQL_DATA_TYPE} * 10); $info->{COLUMN_SIZE} = ($basetype eq 'datetime') ? 19 : $type_params[0] || 14; } $info->{DECIMAL_DIGITS} = 0; # no fractional seconds } elsif ($basetype eq 'year') { # no close standard so treat as int $info->{DATA_TYPE} = SQL_INTEGER(); $info->{NUM_PREC_RADIX} = 10; $info->{COLUMN_SIZE} = 4; } else { Carp::carp("column_info: unrecognized column type '$basetype' of $table_id.$row->{field} treated as varchar"); } $info->{SQL_DATA_TYPE} ||= $info->{DATA_TYPE}; #warn Dumper($info); } my $sponge = DBI->connect("DBI:Sponge:", '','') or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr"); my $sth = $sponge->prepare("column_info $table", { rows => [ map { [ @{$_}{@names} ] } values %col_info ], NUM_OF_FIELDS => scalar @names, NAME => \@names, }) or return $dbh->DBI::set_err($sponge->err(), $sponge->errstr()); return $sth; } #################### # get_info() # Generated by DBI::DBD::Metadata sub get_info { my($dbh, $info_type) = @_; require DBD::mysql::GetInfo; my $v = $DBD::mysql::GetInfo::info{int($info_type)}; $v = $v->($dbh) if ref $v eq 'CODE'; return $v; } package DBD::mysql::st; # ====== STATEMENT ====== use strict; 1; __END__ =pod =head1 NAME DBD::mysql - MySQL driver for the Perl5 Database Interface (DBI) =head1 SYNOPSIS use DBI; $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"; $dbh = DBI->connect($dsn, $user, $password); $drh = DBI->install_driver("mysql"); @databases = DBI->data_sources("mysql"); or @databases = DBI->data_sources("mysql", {"host" => $host, "port" => $port}); $sth = $dbh->prepare("SELECT * FROM foo WHERE bla"); or $sth = $dbh->prepare("LISTFIELDS $table"); or $sth = $dbh->prepare("LISTINDEX $table $index"); $sth->execute; $numRows = $sth->rows; $numFields = $sth->{'NUM_OF_FIELDS'}; $sth->finish; $rc = $drh->func('createdb', $database, $host, $user, $password, 'admin'); $rc = $drh->func('dropdb', $database, $host, $user, $password, 'admin'); $rc = $drh->func('shutdown', $host, $user, $password, 'admin'); $rc = $drh->func('reload', $host, $user, $password, 'admin'); $rc = $dbh->func('createdb', $database, 'admin'); $rc = $dbh->func('dropdb', $database, 'admin'); $rc = $dbh->func('shutdown', 'admin'); $rc = $dbh->func('reload', 'admin'); =head1 EXAMPLE #!/usr/bin/perl use strict; use DBI(); # Connect to the database. my $dbh = DBI->connect("DBI:mysql: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 * FROM foo"); $sth->execute(); while (my $ref = $sth->fetchrow_hashref()) { print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n"; } $sth->finish(); # Disconnect from the database. $dbh->disconnect(); =head1 DESCRIPTION B is the Perl5 Database Interface driver for the MySQL database. In other words: DBD::mysql is an interface between the Perl programming language and the MySQL programming API that comes with the MySQL relational database management system. Most functions provided by this programming API are supported. Some rarely used functions are missing, mainly because noone ever requested them. :-) In what follows we first discuss the use of DBD::mysql, because this is what you will need the most. For installation, see the sections on L, and L below. See L for a simple example above. 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:mysql: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 L 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 L. See L 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 * 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_hashref(); If your table has columns ID and NAME, then $row will be hash ref with keys ID and NAME. See L below for more details on statement handles. But now for a more formal approach: =head2 Class Methods =over =item B use DBI; $dsn = "DBI:mysql:$database"; $dsn = "DBI:mysql:database=$database;host=$hostname"; $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"; $dbh = DBI->connect($dsn, $user, $password); A C must always be specified. =over =item host =item port 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 UNIX socket. Should the MySQL daemon be running on a non-standard port number, you may explicitly state the port number to connect to in the C argument, by concatenating the I and I together separated by a colon ( C<:> ) character or by using the C argument. =item mysql_client_found_rows Enables (TRUE value) or disables (FALSE value) the flag CLIENT_FOUND_ROWS while connecting to the MySQL server. This has a somewhat funny effect: Without mysql_client_found_rows, if you perform a query like UPDATE $table SET id = 1 WHERE id = 1 then the MySQL engine will always return 0, because no rows have changed. With mysql_client_found_rows however, it will return the number of rows that have an id 1, as some people are expecting. (At least for compatibility to other engines.) =item mysql_compression As of MySQL 3.22.3, a new feature is supported: If your DSN contains the option "mysql_compression=1", then the communication between client and server will be compressed. =item mysql_connect_timeout If your DSN contains the option "mysql_connect_timeout=##", the connect request to the server will timeout if it has not been successful after the given number of seconds. =item mysql_read_default_file =item mysql_read_default_group These options can be used to read a config file like /etc/my.cnf or ~/.my.cnf. By default MySQL's C client library doesn't use any config files unlike the client programs (mysql, mysqladmin, ...) that do, but outside of the C client library. Thus you need to explicitly request reading a config file, as in $dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf"; $dbh = DBI->connect($dsn, $user, $password) The option mysql_read_default_group can be used to specify the default group in the config file: Usually this is the I group, but see the following example: [client] host=localhost [perl] host=perlhost (Note the order of the entries! The example won't work, if you reverse the [client] and [perl] sections!) If you read this config file, then you'll be typically connected to I. However, by using $dsn = "DBI:mysql:test;mysql_read_default_group=perl;" . "mysql_read_default_file=/home/joe/my.cnf"; $dbh = DBI->connect($dsn, $user, $password); you'll be connected to I. Note that if you specify a default group and do not specify a file, then the default config files will all be read. See the documentation of the C function mysql_options() for details. =item mysql_socket As of MySQL 3.21.15, it is possible to choose the Unix socket that is used for connecting to the server. This is done, for example, with mysql_socket=/dev/mysql Usually there's no need for this option, unless you are using another location for the socket than that built into the client. =item mysql_ssl A true value turns on the CLIENT_SSL flag when connecting to the MySQL database: mysql_ssl=1 This means that your communication with the server will be encrypted. If you turn mysql_ssl on, you might also wish to use the following flags: =item mysql_ssl_client_key =item mysql_ssl_client_cert =item mysql_ssl_ca_file =item mysql_ssl_ca_path =item mysql_ssl_cipher These are used to specify the respective parameters of a call to mysql_ssl_set, if mysql_ssl is turned on. =item mysql_local_infile As of MySQL 3.23.49, the LOCAL capability for LOAD DATA may be disabled in the MySQL client library by default. If your DSN contains the option "mysql_local_infile=1", LOAD DATA LOCAL will be enabled. (However, this option is *ineffective* if the server has also been configured to disallow LOCAL.) =back =back =head2 Private MetaData Methods =over =item B my $drh = DBI->install_driver("mysql"); @dbs = $drh->func("$hostname:$port", '_ListDBs'); @dbs = $drh->func($hostname, $port, '_ListDBs'); @dbs = $dbh->func('_ListDBs'); Returns a list of all databases managed by the MySQL daemon running on C<$hostname>, port C<$port>. This method is rarely needed for databases running on C: You should use the portable method @dbs = DBI->data_sources("mysql"); whenever possible. It is a design problem of this method, that there's no way of supplying a host name or port number to C, that's the only reason why we still support C. :-( =back =head2 Server Administration =over =item admin $rc = $drh->func("createdb", $dbname, [host, user, password,], 'admin'); $rc = $drh->func("dropdb", $dbname, [host, user, password,], 'admin'); $rc = $drh->func("shutdown", [host, user, password,], 'admin'); $rc = $drh->func("reload", [host, user, password,], 'admin'); or $rc = $dbh->func("createdb", $dbname, 'admin'); $rc = $dbh->func("dropdb", $dbname, 'admin'); $rc = $dbh->func("shutdown", 'admin'); $rc = $dbh->func("reload", 'admin'); For server administration you need a server connection. For obtaining this connection you have two options: Either use a driver handle (drh) and supply the appropriate arguments (host, defaults localhost, user, defaults to '' and password, defaults to ''). A driver handle can be obtained with $drh = DBI->install_driver('mysql'); Otherwise reuse the existing connection of a database handle (dbh). There's only one function available for administrative purposes, comparable to the m(y)sqladmin programs. The command being execute depends on the first argument: =over =item createdb Creates the database $dbname. Equivalent to "m(y)sqladmin create $dbname". =item dropdb Drops the database $dbname. Equivalent to "m(y)sqladmin drop $dbname". It should be noted that database deletion is I in any way. Nor is it undo-able from DBI. Once you issue the dropDB() method, the database will be gone! These method should be used at your own risk. =item shutdown Silently shuts down the database engine. (Without prompting!) Equivalent to "m(y)sqladmin shutdown". =item reload Reloads the servers configuration files and/or tables. This can be particularly important if you modify access privileges or create new users. =back =back =head1 DATABASE HANDLES The DBD::mysql driver supports the following attributes of database handles (read only): $errno = $dbh->{'mysql_errno'}; $error = $dbh->{'mysql_error}; $info = $dbh->{'mysql_hostinfo'}; $info = $dbh->{'mysql_info'}; $insertid = $dbh->{'mysql_insertid'}; $info = $dbh->{'mysql_protoinfo'}; $info = $dbh->{'mysql_serverinfo'}; $info = $dbh->{'mysql_stat'}; $threadId = $dbh->{'mysql_thread_id'}; These correspond to mysql_errno(), mysql_error(), mysql_get_host_info(), mysql_info(), mysql_insert_id(), mysql_get_proto_info(), mysql_get_server_info(), mysql_stat() and mysql_thread_id(), respectively. $info_hashref = $dhb->{mysql_dbd_stats} DBD::mysql keeps track of some statistics in the mysql_dbd_stats attribute. The following stats are being maintained: =over =item auto_reconnects_ok The number of times that DBD::mysql successfully reconnected to the mysql server. =item auto_reconnects_failed The number of times that DBD::mysql tried to reconnect to mysql but failed. =back The DBD::mysql driver also supports the following attribute(s) of database handles (read/write): $bool_value = $dbh->{mysql_auto_reconnect}; $dbh->{mysql_auto_reconnect} = $AutoReconnect ? 1 : 0; =item mysql_auto_reconnect This attribute determines whether DBD::mysql will automatically reconnect to mysql if the connection be lost. This feature defaults to off; however, if either the GATEWAY_INTERFACE or MOD_PERL envionment variable is set, DBD::mysql will turn mysql_auto_reconnect on. Setting mysql_auto_reconnect to on is not advised if 'lock tables' is used because if DBD::mysql reconnect to mysql all table locks will be lost. This attribute is ignored when AutoCommit is turned off, and when AutoCommit is turned off, DBD::mysql will not automatically reconnect to the server. =head1 STATEMENT HANDLES The statement handles of DBD::mysql support a number of attributes. You access these by using, for example, my $numFields = $sth->{'NUM_OF_FIELDS'}; Note, that most attributes are valid only after a successfull I. An C value will returned in that case. The most important exception is the C attribute: This forces the driver to use mysql_use_result rather than mysql_store_result. The former is faster and less memory consuming, but tends to block other processes. (That's why mysql_store_result is the default.) To set the C attribute, use either of the following: my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1}); or my $sth = $dbh->prepare("QUERY"); $sth->{"mysql_use_result"} = 1; Column dependent attributes, for example I, the column names, are returned as a reference to an array. The array indices are corresponding to the indices of the arrays returned by I and similar methods. For example the following code will print a header 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", $i ? "," : "", $$names[$i]); } print "\n"; while (my $ref = $sth->fetchrow_arrayref) { for (my $i = 0; $i < $numFields; $i++) { printf("%s%s", $i ? "," : "", $$ref[$i]); } print "\n"; } For portable applications you should restrict yourself to attributes with capitalized or mixed case names. Lower case attribute names are private to DBD::mysql. The attribute list includes: =over =item ChopBlanks this attribute determines whether a I will chop preceding and trailing blanks off the column values. Chopping blanks does not have impact on the I attribute. =item 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->{'mysql_insertid'}. (Note we are using the $dbh in this case!) =item mysql_is_blob Reference to an array of boolean values; TRUE indicates, that the respective column is a blob. This attribute is valid for MySQL only. =item mysql_is_key Reference to an array of boolean values; TRUE indicates, that the respective column is a key. This is valid for MySQL only. =item mysql_is_num Reference to an array of boolean values; TRUE indicates, that the respective column contains numeric values. =item mysql_is_pri_key Reference to an array of boolean values; TRUE indicates, that the respective column is a primary key. =item mysql_is_auto_increment Reference to an array of boolean values; TRUE indicates that the respective column is an AUTO_INCREMENT column. This is only valid for MySQL. =item mysql_length =item mysql_max_length A reference to an array of maximum column sizes. The I is the maximum physically present in the result table, I gives the theoretically possible maximum. I is valid for MySQL only. =item NAME A reference to an array of column names. =item NULLABLE A reference to an array of boolean values; TRUE indicates that this column may contain NULL's. =item NUM_OF_FIELDS Number of fields returned by a I