1DBD::mysql(3) User Contributed Perl Documentation DBD::mysql(3)
2
6 DBD::mysql - MySQL driver for the Perl5 Database Interface (DBI)
7
9 use DBI;
10 $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
12 $dbh = DBI->connect($dsn, $user, $password);
14 $drh = DBI->install_driver("mysql");
17 @databases = DBI->data_sources("mysql");
18 or
19 @databases = DBI->data_sources("mysql",
20 {"host" => $host, "port" => $port, "user" => $user, password => $pass});
21 $sth = $dbh->prepare("SELECT * FROM foo WHERE bla");
23 or
24 $sth = $dbh->prepare("LISTFIELDS $table");
25 or
26 $sth = $dbh->prepare("LISTINDEX $table $index");
27 $sth->execute;
28 $numRows = $sth->rows;
29 $numFields = $sth->{'NUM_OF_FIELDS'};
30 $sth->finish;
31 $rc = $drh->func('createdb', $database, $host, $user, $password, 'admin');
33 $rc = $drh->func('dropdb', $database, $host, $user, $password, 'admin');
34 $rc = $drh->func('shutdown', $host, $user, $password, 'admin');
35 $rc = $drh->func('reload', $host, $user, $password, 'admin');
36 $rc = $dbh->func('createdb', $database, 'admin');
38 $rc = $dbh->func('dropdb', $database, 'admin');
39 $rc = $dbh->func('shutdown', 'admin');
40 $rc = $dbh->func('reload', 'admin');
41
43 #!/usr/bin/perl
44 use strict;
46 use DBI();
47 # Connect to the database.
49 my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost",
50 "joe", "joe's password",
51 {'RaiseError' => 1});
52 # Drop table 'foo'. This may fail, if 'foo' doesn't exist.
54 # Thus we put an eval around it.
55 eval { $dbh->do("DROP TABLE foo") };
56 print "Dropping foo failed: $@\n" if $@;
57 # Create a new table 'foo'. This must not fail, thus we don't
59 # catch errors.
60 $dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))");
61 # INSERT some data into 'foo'. We are using $dbh->quote() for
63 # quoting the name.
64 $dbh->do("INSERT INTO foo VALUES (1, " . $dbh->quote("Tim") . ")");
65 # Same thing, but using placeholders
67 $dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen");
68 # Now retrieve data from the table.
70 my $sth = $dbh->prepare("SELECT * FROM foo");
71 $sth->execute();
72 while (my $ref = $sth->fetchrow_hashref()) {
73 print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n";
74 }
75 $sth->finish();
76 # Disconnect from the database.
78 $dbh->disconnect();
79
81 DBD::mysql is the Perl5 Database Interface driver for the MySQL
82 database. In other words: DBD::mysql is an interface between the Perl
83 programming language and the MySQL programming API that comes with the
84 MySQL relational database management system. Most functions provided by
85 this programming API are supported. Some rarely used functions are
86 missing, mainly because noone ever requested them. :-)
87 In what follows we first discuss the use of DBD::mysql, because this is
89 what you will need the most. For installation, see the sections on
90 INSTALLATION, and "WIN32 INSTALLATION" below. See EXAMPLE for a simple
91 example above.
92 From perl you activate the interface with the statement
94 use DBI;
96 After that you can connect to multiple MySQL database servers and send
98 multiple queries to any of them via a simple object oriented interface.
99 Two types of objects are available: database handles and statement
100 handles. Perl returns a database handle to the connect method like so:
101 $dbh = DBI->connect("DBI:mysql:database=$db;host=$host",
103 $user, $password, {RaiseError => 1});
104 Once you have connected to a database, you can can execute SQL
106 statements with:
107 my $query = sprintf("INSERT INTO foo VALUES (%d, %s)",
109 $number, $dbh->quote("name"));
110 $dbh->do($query);
111 See DBI(3) for details on the quote and do methods. An alternative
113 approach is
114 $dbh->do("INSERT INTO foo VALUES (?, ?)", undef,
116 $number, $name);
117 in which case the quote method is executed automatically. See also the
119 bind_param method in DBI(3). See "DATABASE HANDLES" below for more
120 details on database handles.
121 If you want to retrieve results, you need to create a so-called
123 statement handle with:
124 $sth = $dbh->prepare("SELECT * FROM $table");
126 $sth->execute();
127 This statement handle can be used for multiple things. First of all you
129 can retreive a row of data:
130 my $row = $sth->fetchrow_hashref();
132 If your table has columns ID and NAME, then $row will be hash ref with
134 keys ID and NAME. See "STATEMENT HANDLES" below for more details on
135 statement handles.
136 But now for a more formal approach:
138 Class Methods
140 connect
141 use DBI;
142 $dsn = "DBI:mysql:$database";
144 $dsn = "DBI:mysql:database=$database;host=$hostname";
145 $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
146 $dbh = DBI->connect($dsn, $user, $password);
148 A "database" must always be specified.
150 host
152 port
153 The hostname, if not specified or specified as '' or
154 'localhost', will default to a MySQL server running on the
155 local machine using the default for the UNIX socket. To connect
156 to a MySQL server on the local machine via TCP, you must
157 specify the loopback IP address (127.0.0.1) as the host.
158 Should the MySQL server be running on a non-standard port
160 number, you may explicitly state the port number to connect to
161 in the "hostname" argument, by concatenating the hostname and
162 port number together separated by a colon ( ":" ) character or
163 by using the "port" argument.
164 To connect to a MySQL server on localhost using TCP/IP, you
166 must specify the hostname as 127.0.0.1 (with the optional
167 port).
168 mysql_client_found_rows
170 Enables (TRUE value) or disables (FALSE value) the flag
171 CLIENT_FOUND_ROWS while connecting to the MySQL server. This
172 has a somewhat funny effect: Without mysql_client_found_rows,
173 if you perform a query like
174 UPDATE $table SET id = 1 WHERE id = 1
176 then the MySQL engine will always return 0, because no rows
178 have changed. With mysql_client_found_rows however, it will
179 return the number of rows that have an id 1, as some people are
180 expecting. (At least for compatibility to other engines.)
181 mysql_compression
183 As of MySQL 3.22.3, a new feature is supported: If your DSN
184 contains the option "mysql_compression=1", then the
185 communication between client and server will be compressed.
186 mysql_connect_timeout
188 If your DSN contains the option "mysql_connect_timeout=##", the
189 connect request to the server will timeout if it has not been
190 successful after the given number of seconds.
191 mysql_init_command
193 If your DSN contains the option
194 "mysql_init_command_timeout=##", then this SQL statement is
195 executed when conencting to the MySQL server. It is
196 automatically re-executed if reconnection occurs.
197 mysql_read_default_file
199 mysql_read_default_group
200 These options can be used to read a config file like
201 /etc/my.cnf or ~/.my.cnf. By default MySQL's C client library
202 doesn't use any config files unlike the client programs (mysql,
203 mysqladmin, ...) that do, but outside of the C client library.
204 Thus you need to explicitly request reading a config file, as
205 in
206 $dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf";
208 $dbh = DBI->connect($dsn, $user, $password)
209 The option mysql_read_default_group can be used to specify the
211 default group in the config file: Usually this is the client
212 group, but see the following example:
213 [client]
215 host=localhost
216 [perl]
218 host=perlhost
219 (Note the order of the entries! The example won't work, if you
221 reverse the [client] and [perl] sections!)
222 If you read this config file, then you'll be typically
224 connected to localhost. However, by using
225 $dsn = "DBI:mysql:test;mysql_read_default_group=perl;"
227 . "mysql_read_default_file=/home/joe/my.cnf";
228 $dbh = DBI->connect($dsn, $user, $password);
229 you'll be connected to perlhost. Note that if you specify a
231 default group and do not specify a file, then the default
232 config files will all be read. See the documentation of the C
233 function mysql_options() for details.
234 mysql_socket
236 As of MySQL 3.21.15, it is possible to choose the Unix socket
237 that is used for connecting to the server. This is done, for
238 example, with
239 mysql_socket=/dev/mysql
241 Usually there's no need for this option, unless you are using
243 another location for the socket than that built into the
244 client.
245 mysql_ssl
247 A true value turns on the CLIENT_SSL flag when connecting to
248 the MySQL database:
249 mysql_ssl=1
251 This means that your communication with the server will be
253 encrypted.
254 If you turn mysql_ssl on, you might also wish to use the
256 following flags:
257 mysql_ssl_client_key
259 mysql_ssl_client_cert
260 mysql_ssl_ca_file
261 mysql_ssl_ca_path
262 mysql_ssl_cipher
263 These are used to specify the respective parameters of a call
264 to mysql_ssl_set, if mysql_ssl is turned on.
265 mysql_local_infile
267 As of MySQL 3.23.49, the LOCAL capability for LOAD DATA may be
268 disabled in the MySQL client library by default. If your DSN
269 contains the option "mysql_local_infile=1", LOAD DATA LOCAL
270 will be enabled. (However, this option is *ineffective* if the
271 server has also been configured to disallow LOCAL.)
272 mysql_multi_statements
274 As of MySQL 4.1, support for multiple statements seperated by a
275 semicolon (;) may be enabled by using this option. Enabling
276 this option may cause problems if server-side prepared
277 statements are also enabled.
278 Prepared statement support (server side prepare)
280 As of 3.0002_1, server side prepare statements were on by
281 default (if your server was >= 4.1.3). As of 3.0009, they were
282 off by default again due to issues with the prepared statement
283 API (all other mysql connectors are set this way until C API
284 issues are resolved). The requirement to use prepared
285 statements still remains that you have a server >= 4.1.3
286 To use server side prepared statements, all you need to do is
288 set the variable mysql_server_prepare in the connect:
289 $dbh = DBI->connect(
291 "DBI:mysql:database=test;host=localhost;mysql_server_prepare=1",
292 "",
293 "",
294 { RaiseError => 1, AutoCommit => 1 }
295 );
296 * Note: delimiter for this param is ';'
298 There are many benefits to using server side prepare
300 statements, mostly if you are performing many inserts because
301 of that fact that a single statement is prepared to accept
302 multiple insert values.
303 To make sure that the 'make test' step tests whether server
305 prepare works, you just need to export the env variable
306 MYSQL_SERVER_PREPARE:
307 export MYSQL_SERVER_PREPARE=1
309 mysql_embedded_options
311 The option <mysql_embedded_options> can be used to pass
312 'command-line' options to embedded server.
313 Example:
315 use DBI;
317 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_options=--help,--verbose";
318 $dbh = DBI->connect($testdsn,"a","b");
319 This would cause the command line help to the embedded MySQL
321 server library to be printed.
322 mysql_embedded_groups
324 The option <mysql_embedded_groups> can be used to specify the
325 groups in the config file(my.cnf) which will be used to get
326 options for embedded server. If not specified [server] and
327 [embedded] groups will be used.
328 Example:
330 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_groups=embedded_server,common";
332 Private MetaData Methods
334 ListDBs
335 my $drh = DBI->install_driver("mysql");
336 @dbs = $drh->func("$hostname:$port", '_ListDBs');
337 @dbs = $drh->func($hostname, $port, '_ListDBs');
338 @dbs = $dbh->func('_ListDBs');
339 Returns a list of all databases managed by the MySQL server running
341 on $hostname, port $port. This is a legacy method. Instead, you
342 should use the portable method
343 @dbs = DBI->data_sources("mysql");
345 Server Administration
347 admin
348 $rc = $drh->func("createdb", $dbname, [host, user, password,], 'admin');
349 $rc = $drh->func("dropdb", $dbname, [host, user, password,], 'admin');
350 $rc = $drh->func("shutdown", [host, user, password,], 'admin');
351 $rc = $drh->func("reload", [host, user, password,], 'admin');
352 or
354 $rc = $dbh->func("createdb", $dbname, 'admin');
356 $rc = $dbh->func("dropdb", $dbname, 'admin');
357 $rc = $dbh->func("shutdown", 'admin');
358 $rc = $dbh->func("reload", 'admin');
359 For server administration you need a server connection. For
361 obtaining this connection you have two options: Either use a driver
362 handle (drh) and supply the appropriate arguments (host, defaults
363 localhost, user, defaults to '' and password, defaults to ''). A
364 driver handle can be obtained with
365 $drh = DBI->install_driver('mysql');
367 Otherwise reuse the existing connection of a database handle (dbh).
369 There's only one function available for administrative purposes,
371 comparable to the m(y)sqladmin programs. The command being execute
372 depends on the first argument:
373 createdb
375 Creates the database $dbname. Equivalent to "m(y)sqladmin
376 create $dbname".
377 dropdb
379 Drops the database $dbname. Equivalent to "m(y)sqladmin drop
380 $dbname".
381 It should be noted that database deletion is not prompted for
383 in any way. Nor is it undo-able from DBI.
384 Once you issue the dropDB() method, the database will be gone!
386 These method should be used at your own risk.
388 shutdown
390 Silently shuts down the database engine. (Without prompting!)
391 Equivalent to "m(y)sqladmin shutdown".
392 reload
394 Reloads the servers configuration files and/or tables. This can
395 be particularly important if you modify access privileges or
396 create new users.
397
399 The DBD::mysql driver supports the following attributes of database
400 handles (read only):
401 $errno = $dbh->{'mysql_errno'};
403 $error = $dbh->{'mysql_error'};
404 $info = $dbh->{'mysql_hostinfo'};
405 $info = $dbh->{'mysql_info'};
406 $insertid = $dbh->{'mysql_insertid'};
407 $info = $dbh->{'mysql_protoinfo'};
408 $info = $dbh->{'mysql_serverinfo'};
409 $info = $dbh->{'mysql_stat'};
410 $threadId = $dbh->{'mysql_thread_id'};
411 These correspond to mysql_errno(), mysql_error(),
413 mysql_get_host_info(), mysql_info(), mysql_insert_id(),
414 mysql_get_proto_info(), mysql_get_server_info(), mysql_stat() and
415 mysql_thread_id(), respectively.
416 $info_hashref = $dhb->{mysql_dbd_stats}
418 DBD::mysql keeps track of some statistics in the mysql_dbd_stats
420 attribute. The following stats are being maintained:
421 auto_reconnects_ok
423 The number of times that DBD::mysql successfully reconnected to the
424 mysql server.
425 auto_reconnects_failed
427 The number of times that DBD::mysql tried to reconnect to mysql but
428 failed.
429 The DBD::mysql driver also supports the following attribute(s) of
431 database handles (read/write):
432 $bool_value = $dbh->{mysql_auto_reconnect};
434 $dbh->{mysql_auto_reconnect} = $AutoReconnect ? 1 : 0;
435 mysql_auto_reconnect
437 This attribute determines whether DBD::mysql will automatically
438 reconnect to mysql if the connection be lost. This feature defaults
439 to off; however, if either the GATEWAY_INTERFACE or MOD_PERL
440 envionment variable is set, DBD::mysql will turn
441 mysql_auto_reconnect on. Setting mysql_auto_reconnect to on is not
442 advised if 'lock tables' is used because if DBD::mysql reconnect to
443 mysql all table locks will be lost. This attribute is ignored when
444 AutoCommit is turned off, and when AutoCommit is turned off,
445 DBD::mysql will not automatically reconnect to the server.
446 mysql_use_result
448 This attribute forces the driver to use mysql_use_result rather
449 than mysql_store_result. The former is faster and less memory
450 consuming, but tends to block other processes. (That's why
451 mysql_store_result is the default.)
452 It is possible to set default value of the "mysql_use_result"
454 attribute for $dbh using several ways:
455 - through DSN
457 $dbh= DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
459 - after creation of database handle
461 $dbh->{'mysql_use_result'}=0; #disable
463 $dbh->{'mysql_use_result'}=1; #enable
464 It is possible to set/unset the "mysql_use_result" attribute after
466 creation of statement handle. See below.
467 mysql_enable_utf8
469 This attribute determines whether DBD::mysql should assume strings
470 stored in the database are utf8. This feature defaults to off.
471 When set, a data retrieved from a textual column type (char,
473 varchar, etc) will have the UTF-8 flag turned on if necessary.
474 This enables character semantics on that string. You will also
475 need to ensure that your database / table / column is configured to
476 use UTF8. See Chapter 10 of the mysql manual for details.
477 Additionally, turning on this flag tells MySQL that incoming data
479 should be treated as UTF-8. This will only take effect if used as
480 part of the call to connect(). If you turn the flag on after
481 connecting, you will need to issue the command "SET NAMES utf8" to
482 get the same effect.
483 This option is experimental and may change in future versions.
485 mysql_bind_type_guessing
487 This attribute causes the driver (emulated prepare statements) to
488 attempt to guess if a value being bound is a numeric value, and if
489 so, doesn't quote the value. This was created by Dragonchild and
490 is one way to deal with the performance issue of using quotes in a
491 statement that is inserting or updating a large numeric value. This
492 was previously called "unsafe_bind_type_guessing" because it is
493 experimental. I have successfully run the full test suite with this
494 option turned on, the name can now be simply
495 "mysql_bind_type_guessing".
496 See bug: https://rt.cpan.org/Ticket/Display.html?id=43822
498 "mysql_bind_type_guessing" can be turned on via
500 - through DSN
502 my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
504 { mysql_bind_type_guessing => 1})
505 - OR after handle creation
507 $dbh->{mysql_bind_type_guessing} = 1;
509 mysql_no_autocommit_cmd
511 This attribute causes the driver to not issue 'set autocommit'
512 either through explicit or using mysql_autocommit(). This is
513 particularly useful in the case of using MySQL Proxy.
514 See the bug report:
516 https://rt.cpan.org/Public/Bug/Display.html?id=46308
518 As well as:
520 http://bugs.mysql.com/bug.php?id=32464
522 "mysql_no_autocommit_cmd" can be turned on via
524 - through DSN
526 my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
528 { mysql_no_autocommit_cmd => 1})
529 - OR after handle creation
531 $dbh->{mysql_no_autocommit_cmd} = 1;
533
535 The statement handles of DBD::mysql support a number of attributes. You
536 access these by using, for example,
537 my $numFields = $sth->{'NUM_OF_FIELDS'};
539 Note, that most attributes are valid only after a successfull execute.
541 An "undef" value will returned in that case. The most important
542 exception is the "mysql_use_result" attribute: This forces the driver
543 to use mysql_use_result rather than mysql_store_result. The former is
544 faster and less memory consuming, but tends to block other processes.
545 (That's why mysql_store_result is the default.)
546 To set the "mysql_use_result" attribute, use either of the following:
548 my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1});
550 or
552 my $sth = $dbh->prepare("QUERY");
554 $sth->{"mysql_use_result"} = 1;
555 Column dependent attributes, for example NAME, the column names, are
557 returned as a reference to an array. The array indices are
558 corresponding to the indices of the arrays returned by fetchrow and
559 similar methods. For example the following code will print a header of
560 table names together with all rows:
561 my $sth = $dbh->prepare("SELECT * FROM $table");
563 if (!$sth) {
564 die "Error:" . $dbh->errstr . "\n";
565 }
566 if (!$sth->execute) {
567 die "Error:" . $sth->errstr . "\n";
568 }
569 my $names = $sth->{'NAME'};
570 my $numFields = $sth->{'NUM_OF_FIELDS'};
571 for (my $i = 0; $i < $numFields; $i++) {
572 printf("%s%s", $i ? "," : "", $$names[$i]);
573 }
574 print "\n";
575 while (my $ref = $sth->fetchrow_arrayref) {
576 for (my $i = 0; $i < $numFields; $i++) {
577 printf("%s%s", $i ? "," : "", $$ref[$i]);
578 }
579 print "\n";
580 }
581 For portable applications you should restrict yourself to attributes
583 with capitalized or mixed case names. Lower case attribute names are
584 private to DBD::mysql. The attribute list includes:
585 ChopBlanks
587 this attribute determines whether a fetchrow will chop preceding
588 and trailing blanks off the column values. Chopping blanks does not
589 have impact on the max_length attribute.
590 mysql_insertid
592 MySQL has the ability to choose unique key values automatically. If
593 this happened, the new ID will be stored in this attribute. An
594 alternative way for accessing this attribute is via
595 $dbh->{'mysql_insertid'}. (Note we are using the $dbh in this
596 case!)
597 mysql_is_blob
599 Reference to an array of boolean values; TRUE indicates, that the
600 respective column is a blob. This attribute is valid for MySQL
601 only.
602 mysql_is_key
604 Reference to an array of boolean values; TRUE indicates, that the
605 respective column is a key. This is valid for MySQL only.
606 mysql_is_num
608 Reference to an array of boolean values; TRUE indicates, that the
609 respective column contains numeric values.
610 mysql_is_pri_key
612 Reference to an array of boolean values; TRUE indicates, that the
613 respective column is a primary key.
614 mysql_is_auto_increment
616 Reference to an array of boolean values; TRUE indicates that the
617 respective column is an AUTO_INCREMENT column. This is only valid
618 for MySQL.
619 mysql_length
621 mysql_max_length
622 A reference to an array of maximum column sizes. The max_length is
623 the maximum physically present in the result table, length gives
624 the theoretically possible maximum. max_length is valid for MySQL
625 only.
626 NAME
628 A reference to an array of column names.
629 NULLABLE
631 A reference to an array of boolean values; TRUE indicates that this
632 column may contain NULL's.
633 NUM_OF_FIELDS
635 Number of fields returned by a SELECT or LISTFIELDS statement. You
636 may use this for checking whether a statement returned a result: A
637 zero value indicates a non-SELECT statement like INSERT, DELETE or
638 UPDATE.
639 mysql_table
641 A reference to an array of table names, useful in a JOIN result.
642 TYPE
644 A reference to an array of column types. The engine's native column
645 types are mapped to portable types like DBI::SQL_INTEGER() or
646 DBI::SQL_VARCHAR(), as good as possible. Not all native types have
647 a meaningfull equivalent, for example
648 DBD::mysql::FIELD_TYPE_INTERVAL is mapped to DBI::SQL_VARCHAR().
649 If you need the native column types, use mysql_type. See below.
650 mysql_type
652 A reference to an array of MySQL's native column types, for example
653 DBD::mysql::FIELD_TYPE_SHORT() or DBD::mysql::FIELD_TYPE_STRING().
654 Use the TYPE attribute, if you want portable types like
655 DBI::SQL_SMALLINT() or DBI::SQL_VARCHAR().
656 mysql_type_name
658 Similar to mysql, but type names and not numbers are returned.
659 Whenever possible, the ANSI SQL name is preferred.
660 mysql_warning_count
662 The number of warnings generated during execution of the SQL
663 statement.
664
666 Beginning with DBD::mysql 2.0416, transactions are supported. The
667 transaction support works as follows:
668 · By default AutoCommit mode is on, following the DBI specifications.
670 · If you execute
672 $dbh->{'AutoCommit'} = 0;
674 or
676 $dbh->{'AutoCommit'} = 1;
678 then the driver will set the MySQL server variable autocommit to 0
680 or 1, respectively. Switching from 0 to 1 will also issue a COMMIT,
681 following the DBI specifications.
682 · The methods
684 $dbh->rollback();
686 $dbh->commit();
687 will issue the commands COMMIT and ROLLBACK, respectively. A
689 ROLLBACK will also be issued if AutoCommit mode is off and the
690 database handles DESTROY method is called. Again, this is following
691 the DBI specifications.
692 Given the above, you should note the following:
694 · You should never change the server variable autocommit manually,
696 unless you are ignoring DBI's transaction support.
697 · Switching AutoCommit mode from on to off or vice versa may fail.
699 You should always check for errors, when changing AutoCommit mode.
700 The suggested way of doing so is using the DBI flag RaiseError. If
701 you don't like RaiseError, you have to use code like the following:
702 $dbh->{'AutoCommit'} = 0;
704 if ($dbh->{'AutoCommit'}) {
705 # An error occurred!
706 }
707 · If you detect an error while changing the AutoCommit mode, you
709 should no longer use the database handle. In other words, you
710 should disconnect and reconnect again, because the transaction mode
711 is unpredictable. Alternatively you may verify the transaction mode
712 by checking the value of the server variable autocommit. However,
713 such behaviour isn't portable.
714 · DBD::mysql has a "reconnect" feature that handles the so-called
716 MySQL "morning bug": If the server has disconnected, most probably
717 due to a timeout, then by default the driver will reconnect and
718 attempt to execute the same SQL statement again. However, this
719 behaviour is disabled when AutoCommit is off: Otherwise the
720 transaction state would be completely unpredictable after a
721 reconnect.
722 · The "reconnect" feature of DBD::mysql can be toggled by using the
724 mysql_auto_reconnect attribute. This behaviour should be turned off
725 in code that uses LOCK TABLE because if the database server time
726 out and DBD::mysql reconnect, table locks will be lost without any
727 indication of such loss.
728
730 As of version 3.0002_5, DBD::mysql supports multiple result sets
731 (Thanks to Guy Harrison!). This is the first release of this
732 functionality, so there may be issues. Please report bugs if you run
733 into them!
734 The basic usage of multiple result sets is
736 do
738 {
739 while (@row= $sth->fetchrow_array())
740 {
741 do stuff;
742 }
743 } while ($sth->more_results)
744 An example would be:
746 $dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
748 $dbh->do("create procedure somproc() deterministic
750 begin
751 declare a,b,c,d int;
752 set a=1;
753 set b=2;
754 set c=3;
755 set d=4;
756 select a, b, c, d;
757 select d, c, b, a;
758 select b, a, c, d;
759 select c, b, d, a;
760 end") or print $DBI::errstr;
761 $sth=$dbh->prepare('call someproc()') ||
763 die $DBI::err.": ".$DBI::errstr;
764 $sth->execute || die DBI::err.": ".$DBI::errstr; $rowset=0;
766 do {
767 print "\nRowset ".++$i."\n---------------------------------------\n\n";
768 foreach $colno (0..$sth->{NUM_OF_FIELDS}) {
769 print $sth->{NAME}->[$colno]."\t";
770 }
771 print "\n";
772 while (@row= $sth->fetchrow_array()) {
773 foreach $field (0..$#row) {
774 print $row[$field]."\t";
775 }
776 print "\n";
777 }
778 } until (!$sth->more_results)
779 For more examples, please see the eg/ directory. This is where helpful
781 DBD::mysql code snippits will be added in the future.
782 Issues with Multiple result sets
784 So far, the main issue is if your result sets are "jagged", meaning,
785 the number of columns of your results vary. Varying numbers of columns
786 could result in your script crashing. This is something that will be
787 fixed soon.
788
790 The multithreading capabilities of DBD::mysql depend completely on the
791 underlying C libraries: The modules are working with handle data only,
792 no global variables are accessed or (to the best of my knowledge)
793 thread unsafe functions are called. Thus DBD::mysql is believed to be
794 completely thread safe, if the C libraries are thread safe and you
795 don't share handles among threads.
796 The obvious question is: Are the C libraries thread safe? In the case
798 of MySQL the answer is "mostly" and, in theory, you should be able to
799 get a "yes", if the C library is compiled for being thread safe (By
800 default it isn't.) by passing the option -with-thread-safe-client to
801 configure. See the section on How to make a threadsafe client in the
802 manual.
803
805 Windows users may skip this section and pass over to WIN32 INSTALLATION
806 below. Others, go on reading.
807 First of all, you do not need an installed MySQL server for installing
809 DBD::mysql. However, you need at least the client libraries and
810 possibly the header files, if you are compiling DBD::mysql from source.
811 In the case of MySQL you can create a client-only version by using the
812 configure option --without-server. If you are using precompiled
813 binaries, then it may be possible to use just selected RPM's like
814 MySQL-client and MySQL-devel or something similar, depending on the
815 distribution.
816 First you need to install the DBI module. For using dbimon, a simple
818 DBI shell it is recommended to install Data::ShowTable another Perl
819 module.
820 I recommend trying automatic installation via the CPAN module. Try
822 perl -MCPAN -e shell
824 If you are using the CPAN module for the first time, it will prompt you
826 a lot of questions. If you finally receive the CPAN prompt, enter
827 install Bundle::DBD::mysql
829 If this fails (which may be the case for a number of reasons, for
831 example because you are behind a firewall or don't have network
832 access), you need to do a manual installation. First of all you need to
833 fetch the modules from CPAN search
834 http://search.cpan.org/
836 The following modules are required
838 DBI
840 Data::ShowTable
841 DBD::mysql
842 Then enter the following commands (note - versions are just examples):
844 gzip -cd DBI-(version).tar.gz | tar xf -
846 cd DBI-(version)
847 perl Makefile.PL
848 make
849 make test
850 make install
851 cd ..
853 gzip -cd Data-ShowTable-(version).tar.gz | tar xf -
854 cd Data-ShowTable-3.3
855 perl Makefile.PL
856 make
857 make install
858 cd ..
860 gzip -cd DBD-mysql-(version)-tar.gz | tar xf -
861 cd DBD-mysql-(version)
862 perl Makefile.PL
863 make
864 make test
865 make install
866 During "perl Makefile.PL" you will be prompted some questions. Other
868 questions are the directories with header files and libraries. For
869 example, of your file mysql.h is in /usr/include/mysql/mysql.h, then
870 enter the header directory /usr, likewise for
871 /usr/lib/mysql/libmysqlclient.a or /usr/lib/libmysqlclient.so.
872
874 If you are using ActivePerl, you may use ppm to install DBD-mysql. For
875 Perl 5.6, upgrade to Build 623 or later, then it is sufficient to run
876 ppm install DBI
878 ppm install DBD::mysql
879 If you need an HTTP proxy, you might need to set the environment
881 variable http_proxy, for example like this:
882 set http_proxy=http://myproxy.com:8080/
884 As of this writing, DBD::mysql is missing in the ActivePerl 5.8.0
886 repository. However, Randy Kobes has kindly donated an own distribution
887 and the following might succeed:
888 ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd
890 Otherwise you definitely *need* a C compiler. And it *must* be the same
892 compiler that was being used for compiling Perl itself. If you don't
893 have a C compiler, the file README.win32 from the Perl source
894 distribution tells you where to obtain freely distributable C compilers
895 like egcs or gcc. The Perl sources are available via CPAN search
896 http://search.cpan.org
898 I recommend using the win32clients package for installing DBD::mysql
900 under Win32, available for download on www.tcx.se. The following steps
901 have been required for me:
902 - The current Perl versions (5.6, as of this writing) do have a
904 problem with detecting the C libraries. I recommend to apply the
905 following patch:
906 *** c:\Perl\lib\ExtUtils\Liblist.pm.orig Sat Apr 15 20:03:40 2000
908 --- c:\Perl\lib\ExtUtils\Liblist.pm Sat Apr 15 20:03:45 2000
909 ***************
910 *** 230,235 ****
911 --- 230,239 ----
912 # add "$Config{installarchlib}/CORE" to default search path
913 push @libpath, "$Config{installarchlib}/CORE";
914 + if ($VC and exists($ENV{LIB}) and defined($ENV{LIB})) {
916 + push(@libpath, split(/;/, $ENV{LIB}));
917 + }
918 +
919 foreach (Text::ParseWords::quotewords('\s+', 0, $potential_libs)){
920 $thislib = $_;
922 - Extract sources into C:\. This will create a directory C:\mysql
924 with subdirectories include and lib.
925 IMPORTANT: Make sure this subdirectory is not shared by other TCX
927 files! In particular do *not* store the MySQL server in the same
928 directory. If the server is already installed in C:\mysql, choose a
929 location like C:\tmp, extract the win32clients there. Note that
930 you can remove this directory entirely once you have installed
931 DBD::mysql.
932 - Extract the DBD::mysql sources into another directory, for example
934 C:\src\siteperl
935 - Open a DOS shell and change directory to C:\src\siteperl.
937 - The next step is only required if you repeat building the modules:
939 Make sure that you have a clean build tree by running
940 nmake realclean
942 If you don't have VC++, replace nmake with your flavour of make. If
944 error messages are reported in this step, you may safely ignore
945 them.
946 - Run
948 perl Makefile.PL
950 which will prompt you for some settings. The really important ones
952 are:
953 Which DBMS do you want to use?
955 enter a 1 here (MySQL only), and
957 Where is your mysql installed? Please tell me the directory that
959 contains the subdir include.
960 where you have to enter the win32clients directory, for example
962 C:\mysql or C:\tmp\mysql.
963 - Continued in the usual way:
965 nmake
967 nmake install
968 If you want to create a PPM package for the ActiveState Perl version,
970 then modify the above steps as follows: Run
971 perl Makefile.PL NAME=DBD-mysql BINARY_LOCATION=DBD-mysql.tar.gz
973 nmake ppd
974 nmake
975 Once that is done, use tar and gzip (for example those from the
977 CygWin32 distribution) to create an archive:
978 mkdir x86
980 tar cf x86/DBD-mysql.tar blib
981 gzip x86/DBD-mysql.tar
982 Put the files x86/DBD-mysql.tar.gz and DBD-mysql.ppd onto some WWW
984 server and install them by typing
985 install http://your.server.name/your/directory/DBD-mysql.ppd
987 in the PPM program.
989
991 The current version of DBD::mysql is almost completely written by
992 Jochen Wiedmann, and is now being maintained by Patrick Galbraith
993 (patg@mysql.com). The first version's author was Alligator Descartes,
994 who was aided and abetted by Gary Shea, Andreas KA~Xnig and Tim Bunce
995 amongst others.
996 The Mysql module was originally written by Andreas KA~Xnig
998 <koenig@kulturbox.de>. The current version, mainly an emulation layer,
999 is from Jochen Wiedmann.
1000
1002 This module is Large Portions Copyright (c) 2004-2006 MySQL Patrick
1003 Galbraith, Alexey Stroganov, Large Portions Copyright (c) 2003-2005
1004 Rudolf Lippan; Large Portions Copyright (c) 1997-2003 Jochen Wiedmann,
1005 with code portions Copyright (c)1994-1997 their original authors This
1006 module is released under the same license as Perl itself. See the Perl
1007 README for details.
1008
1010 This module is maintained and supported on a mailing list,
1011 perl@lists.mysql.com
1013 To subscribe to this list, go to
1015 http://lists.mysql.com/perl?sub=1
1017 Mailing list archives are available at
1019 http://lists.mysql.com/perl
1021 Additionally you might try the dbi-user mailing list for questions
1023 about DBI and its modules in general. Subscribe via
1024 dbi-users-subscribe@perl.org
1026 Mailing list archives are at
1028 http://groups.google.com/group/perl.dbi.users?hl=en&lr=
1030 Also, the main DBI site is at
1032 http://dbi.perl.org/
1034
1036 Additional information on the DBI project can be found on the World
1037 Wide Web at the following URL:
1038 http://dbi.perl.org
1040 where documentation, pointers to the mailing lists and mailing list
1042 archives and pointers to the most current versions of the modules can
1043 be used.
1044 Information on the DBI interface itself can be gained by typing:
1046 perldoc DBI
1048 right now!
1050
1052 Please report bugs, including all the information needed such as
1053 DBD::mysql version, MySQL version, OS type/version, etc to this link:
1054 http://bugs.mysql.com/
1056
1058 Hey! The above document had some coding errors, which are explained
1059 below:
1060 Around line 1234:
1062 '=item' outside of any '=over'
1063 Around line 1337:
1065 You forgot a '=back' before '=head1'
1066 Around line 1579:
1068 You forgot a '=back' before '=head1'
1069perl v5.10.1 2010-08-20 DBD::mysql(3)