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 =item mysql_init_command
193 If your DSN contains the option "mysql_init_command_timeout=##", then
195 this SQL statement is executed when connecting to the MySQL server.
196 It is 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 CAVEAT: Even though you can insert an integer value into a
498 character column, if this column is indexed, if you query that
499 column with the integer value not being quoted, it will not use the
500 index:
501 MariaDB [test]> explain select * from test where value0 = '3' \G
503 *************************** 1. row ***************************
504 id: 1
505 select_type: SIMPLE
506 table: test
507 type: ref possible_keys: value0
508 key: value0
509 key_len: 13
510 ref: const
511 rows: 1
512 Extra: Using index condition 1 row in set (0.00 sec)
513 MariaDB [test]> explain select * from test where value0 = 3
515 -> \G *************************** 1. row
516 ***************************
517 id: 1
518 select_type: SIMPLE
519 table: test
520 type: ALL possible_keys: value0
521 key: NULL
522 key_len: NULL
523 ref: NULL
524 rows: 6
525 Extra: Using where 1 row in set (0.00 sec)
526 See bug: https://rt.cpan.org/Ticket/Display.html?id=43822
528 mysql_bind_comment_placeholders
530 This attribute causes the driver (emulated prepare statements) will
531 cause any placeholders in comments to be bound. This is not correct
532 prepared statement behavior, but some developers have come to
533 depend on this behavior, so I have made it available in 4.015
534 See bug: https://rt.cpan.org/Ticket/Display.html?id=
536 "mysql_bind_type_guessing" can be turned on via
538 - through DSN
540 my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
542 { mysql_bind_type_guessing => 1})
543 - OR after handle creation
545 $dbh->{mysql_bind_type_guessing} = 1;
547 mysql_no_autocommit_cmd
549 This attribute causes the driver to not issue 'set autocommit'
550 either through explicit or using mysql_autocommit(). This is
551 particularly useful in the case of using MySQL Proxy.
552 See the bug report:
554 https://rt.cpan.org/Public/Bug/Display.html?id=46308
556 As well as:
558 http://bugs.mysql.com/bug.php?id=32464
560 "mysql_no_autocommit_cmd" can be turned on via
562 - through DSN
564 my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
566 { mysql_no_autocommit_cmd => 1})
567 - OR after handle creation
569 $dbh->{mysql_no_autocommit_cmd} = 1;
571
573 The statement handles of DBD::mysql support a number of attributes. You
574 access these by using, for example,
575 my $numFields = $sth->{'NUM_OF_FIELDS'};
577 Note, that most attributes are valid only after a successfull execute.
579 An "undef" value will returned in that case. The most important
580 exception is the "mysql_use_result" attribute: This forces the driver
581 to use mysql_use_result rather than mysql_store_result. The former is
582 faster and less memory consuming, but tends to block other processes.
583 (That's why mysql_store_result is the default.)
584 To set the "mysql_use_result" attribute, use either of the following:
586 my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1});
588 or
590 my $sth = $dbh->prepare("QUERY");
592 $sth->{"mysql_use_result"} = 1;
593 Column dependent attributes, for example NAME, the column names, are
595 returned as a reference to an array. The array indices are
596 corresponding to the indices of the arrays returned by fetchrow and
597 similar methods. For example the following code will print a header of
598 table names together with all rows:
599 my $sth = $dbh->prepare("SELECT * FROM $table");
601 if (!$sth) {
602 die "Error:" . $dbh->errstr . "\n";
603 }
604 if (!$sth->execute) {
605 die "Error:" . $sth->errstr . "\n";
606 }
607 my $names = $sth->{'NAME'};
608 my $numFields = $sth->{'NUM_OF_FIELDS'};
609 for (my $i = 0; $i < $numFields; $i++) {
610 printf("%s%s", $i ? "," : "", $$names[$i]);
611 }
612 print "\n";
613 while (my $ref = $sth->fetchrow_arrayref) {
614 for (my $i = 0; $i < $numFields; $i++) {
615 printf("%s%s", $i ? "," : "", $$ref[$i]);
616 }
617 print "\n";
618 }
619 For portable applications you should restrict yourself to attributes
621 with capitalized or mixed case names. Lower case attribute names are
622 private to DBD::mysql. The attribute list includes:
623 ChopBlanks
625 this attribute determines whether a fetchrow will chop preceding
626 and trailing blanks off the column values. Chopping blanks does not
627 have impact on the max_length attribute.
628 mysql_insertid
630 MySQL has the ability to choose unique key values automatically. If
631 this happened, the new ID will be stored in this attribute. An
632 alternative way for accessing this attribute is via
633 $dbh->{'mysql_insertid'}. (Note we are using the $dbh in this
634 case!)
635 mysql_is_blob
637 Reference to an array of boolean values; TRUE indicates, that the
638 respective column is a blob. This attribute is valid for MySQL
639 only.
640 mysql_is_key
642 Reference to an array of boolean values; TRUE indicates, that the
643 respective column is a key. This is valid for MySQL only.
644 mysql_is_num
646 Reference to an array of boolean values; TRUE indicates, that the
647 respective column contains numeric values.
648 mysql_is_pri_key
650 Reference to an array of boolean values; TRUE indicates, that the
651 respective column is a primary key.
652 mysql_is_auto_increment
654 Reference to an array of boolean values; TRUE indicates that the
655 respective column is an AUTO_INCREMENT column. This is only valid
656 for MySQL.
657 mysql_length
659 mysql_max_length
660 A reference to an array of maximum column sizes. The max_length is
661 the maximum physically present in the result table, length gives
662 the theoretically possible maximum. max_length is valid for MySQL
663 only.
664 NAME
666 A reference to an array of column names.
667 NULLABLE
669 A reference to an array of boolean values; TRUE indicates that this
670 column may contain NULL's.
671 NUM_OF_FIELDS
673 Number of fields returned by a SELECT or LISTFIELDS statement. You
674 may use this for checking whether a statement returned a result: A
675 zero value indicates a non-SELECT statement like INSERT, DELETE or
676 UPDATE.
677 mysql_table
679 A reference to an array of table names, useful in a JOIN result.
680 TYPE
682 A reference to an array of column types. The engine's native column
683 types are mapped to portable types like DBI::SQL_INTEGER() or
684 DBI::SQL_VARCHAR(), as good as possible. Not all native types have
685 a meaningfull equivalent, for example
686 DBD::mysql::FIELD_TYPE_INTERVAL is mapped to DBI::SQL_VARCHAR().
687 If you need the native column types, use mysql_type. See below.
688 mysql_type
690 A reference to an array of MySQL's native column types, for example
691 DBD::mysql::FIELD_TYPE_SHORT() or DBD::mysql::FIELD_TYPE_STRING().
692 Use the TYPE attribute, if you want portable types like
693 DBI::SQL_SMALLINT() or DBI::SQL_VARCHAR().
694 mysql_type_name
696 Similar to mysql, but type names and not numbers are returned.
697 Whenever possible, the ANSI SQL name is preferred.
698 mysql_warning_count
700 The number of warnings generated during execution of the SQL
701 statement.
702
704 Beginning with DBD::mysql 2.0416, transactions are supported. The
705 transaction support works as follows:
706 · By default AutoCommit mode is on, following the DBI specifications.
708 · If you execute
710 $dbh->{'AutoCommit'} = 0;
712 or
714 $dbh->{'AutoCommit'} = 1;
716 then the driver will set the MySQL server variable autocommit to 0
718 or 1, respectively. Switching from 0 to 1 will also issue a COMMIT,
719 following the DBI specifications.
720 · The methods
722 $dbh->rollback();
724 $dbh->commit();
725 will issue the commands COMMIT and ROLLBACK, respectively. A
727 ROLLBACK will also be issued if AutoCommit mode is off and the
728 database handles DESTROY method is called. Again, this is following
729 the DBI specifications.
730 Given the above, you should note the following:
732 · You should never change the server variable autocommit manually,
734 unless you are ignoring DBI's transaction support.
735 · Switching AutoCommit mode from on to off or vice versa may fail.
737 You should always check for errors, when changing AutoCommit mode.
738 The suggested way of doing so is using the DBI flag RaiseError. If
739 you don't like RaiseError, you have to use code like the following:
740 $dbh->{'AutoCommit'} = 0;
742 if ($dbh->{'AutoCommit'}) {
743 # An error occurred!
744 }
745 · If you detect an error while changing the AutoCommit mode, you
747 should no longer use the database handle. In other words, you
748 should disconnect and reconnect again, because the transaction mode
749 is unpredictable. Alternatively you may verify the transaction mode
750 by checking the value of the server variable autocommit. However,
751 such behaviour isn't portable.
752 · DBD::mysql has a "reconnect" feature that handles the so-called
754 MySQL "morning bug": If the server has disconnected, most probably
755 due to a timeout, then by default the driver will reconnect and
756 attempt to execute the same SQL statement again. However, this
757 behaviour is disabled when AutoCommit is off: Otherwise the
758 transaction state would be completely unpredictable after a
759 reconnect.
760 · The "reconnect" feature of DBD::mysql can be toggled by using the
762 mysql_auto_reconnect attribute. This behaviour should be turned off
763 in code that uses LOCK TABLE because if the database server time
764 out and DBD::mysql reconnect, table locks will be lost without any
765 indication of such loss.
766
768 As of version 3.0002_5, DBD::mysql supports multiple result sets
769 (Thanks to Guy Harrison!). This is the first release of this
770 functionality, so there may be issues. Please report bugs if you run
771 into them!
772 The basic usage of multiple result sets is
774 do
776 {
777 while (@row= $sth->fetchrow_array())
778 {
779 do stuff;
780 }
781 } while ($sth->more_results)
782 An example would be:
784 $dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
786 $dbh->do("create procedure somproc() deterministic
788 begin
789 declare a,b,c,d int;
790 set a=1;
791 set b=2;
792 set c=3;
793 set d=4;
794 select a, b, c, d;
795 select d, c, b, a;
796 select b, a, c, d;
797 select c, b, d, a;
798 end") or print $DBI::errstr;
799 $sth=$dbh->prepare('call someproc()') ||
801 die $DBI::err.": ".$DBI::errstr;
802 $sth->execute || die DBI::err.": ".$DBI::errstr; $rowset=0;
804 do {
805 print "\nRowset ".++$i."\n---------------------------------------\n\n";
806 foreach $colno (0..$sth->{NUM_OF_FIELDS}) {
807 print $sth->{NAME}->[$colno]."\t";
808 }
809 print "\n";
810 while (@row= $sth->fetchrow_array()) {
811 foreach $field (0..$#row) {
812 print $row[$field]."\t";
813 }
814 print "\n";
815 }
816 } until (!$sth->more_results)
817 For more examples, please see the eg/ directory. This is where helpful
819 DBD::mysql code snippits will be added in the future.
820 Issues with Multiple result sets
822 So far, the main issue is if your result sets are "jagged", meaning,
823 the number of columns of your results vary. Varying numbers of columns
824 could result in your script crashing. This is something that will be
825 fixed soon.
826
828 The multithreading capabilities of DBD::mysql depend completely on the
829 underlying C libraries: The modules are working with handle data only,
830 no global variables are accessed or (to the best of my knowledge)
831 thread unsafe functions are called. Thus DBD::mysql is believed to be
832 completely thread safe, if the C libraries are thread safe and you
833 don't share handles among threads.
834 The obvious question is: Are the C libraries thread safe? In the case
836 of MySQL the answer is "mostly" and, in theory, you should be able to
837 get a "yes", if the C library is compiled for being thread safe (By
838 default it isn't.) by passing the option -with-thread-safe-client to
839 configure. See the section on How to make a threadsafe client in the
840 manual.
841
843 Windows users may skip this section and pass over to WIN32 INSTALLATION
844 below. Others, go on reading.
845 First of all, you do not need an installed MySQL server for installing
847 DBD::mysql. However, you need at least the client libraries and
848 possibly the header files, if you are compiling DBD::mysql from source.
849 In the case of MySQL you can create a client-only version by using the
850 configure option --without-server. If you are using precompiled
851 binaries, then it may be possible to use just selected RPM's like
852 MySQL-client and MySQL-devel or something similar, depending on the
853 distribution.
854 First you need to install the DBI module. For using dbimon, a simple
856 DBI shell it is recommended to install Data::ShowTable another Perl
857 module.
858 I recommend trying automatic installation via the CPAN module. Try
860 perl -MCPAN -e shell
862 If you are using the CPAN module for the first time, it will prompt you
864 a lot of questions. If you finally receive the CPAN prompt, enter
865 install Bundle::DBD::mysql
867 If this fails (which may be the case for a number of reasons, for
869 example because you are behind a firewall or don't have network
870 access), you need to do a manual installation. First of all you need to
871 fetch the modules from CPAN search
872 http://search.cpan.org/
874 The following modules are required
876 DBI
878 Data::ShowTable
879 DBD::mysql
880 Then enter the following commands (note - versions are just examples):
882 gzip -cd DBI-(version).tar.gz | tar xf -
884 cd DBI-(version)
885 perl Makefile.PL
886 make
887 make test
888 make install
889 cd ..
891 gzip -cd Data-ShowTable-(version).tar.gz | tar xf -
892 cd Data-ShowTable-3.3
893 perl Makefile.PL
894 make
895 make install
896 cd ..
898 gzip -cd DBD-mysql-(version)-tar.gz | tar xf -
899 cd DBD-mysql-(version)
900 perl Makefile.PL
901 make
902 make test
903 make install
904 During "perl Makefile.PL" you will be prompted some questions. Other
906 questions are the directories with header files and libraries. For
907 example, of your file mysql.h is in /usr/include/mysql/mysql.h, then
908 enter the header directory /usr, likewise for
909 /usr/lib/mysql/libmysqlclient.a or /usr/lib/libmysqlclient.so.
910
912 If you are using ActivePerl, you may use ppm to install DBD-mysql. For
913 Perl 5.6, upgrade to Build 623 or later, then it is sufficient to run
914 ppm install DBI
916 ppm install DBD::mysql
917 If you need an HTTP proxy, you might need to set the environment
919 variable http_proxy, for example like this:
920 set http_proxy=http://myproxy.com:8080/
922 As of this writing, DBD::mysql is missing in the ActivePerl 5.8.0
924 repository. However, Randy Kobes has kindly donated an own distribution
925 and the following might succeed:
926 ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd
928 Otherwise you definitely *need* a C compiler. And it *must* be the same
930 compiler that was being used for compiling Perl itself. If you don't
931 have a C compiler, the file README.win32 from the Perl source
932 distribution tells you where to obtain freely distributable C compilers
933 like egcs or gcc. The Perl sources are available via CPAN search
934 http://search.cpan.org
936 I recommend using the win32clients package for installing DBD::mysql
938 under Win32, available for download on www.tcx.se. The following steps
939 have been required for me:
940 - The current Perl versions (5.6, as of this writing) do have a
942 problem with detecting the C libraries. I recommend to apply the
943 following patch:
944 *** c:\Perl\lib\ExtUtils\Liblist.pm.orig Sat Apr 15 20:03:40 2000
946 --- c:\Perl\lib\ExtUtils\Liblist.pm Sat Apr 15 20:03:45 2000
947 ***************
948 *** 230,235 ****
949 --- 230,239 ----
950 # add "$Config{installarchlib}/CORE" to default search path
951 push @libpath, "$Config{installarchlib}/CORE";
952 + if ($VC and exists($ENV{LIB}) and defined($ENV{LIB})) {
954 + push(@libpath, split(/;/, $ENV{LIB}));
955 + }
956 +
957 foreach (Text::ParseWords::quotewords('\s+', 0, $potential_libs)){
958 $thislib = $_;
960 - Extract sources into C:\. This will create a directory C:\mysql
962 with subdirectories include and lib.
963 IMPORTANT: Make sure this subdirectory is not shared by other TCX
965 files! In particular do *not* store the MySQL server in the same
966 directory. If the server is already installed in C:\mysql, choose a
967 location like C:\tmp, extract the win32clients there. Note that
968 you can remove this directory entirely once you have installed
969 DBD::mysql.
970 - Extract the DBD::mysql sources into another directory, for example
972 C:\src\siteperl
973 - Open a DOS shell and change directory to C:\src\siteperl.
975 - The next step is only required if you repeat building the modules:
977 Make sure that you have a clean build tree by running
978 nmake realclean
980 If you don't have VC++, replace nmake with your flavour of make. If
982 error messages are reported in this step, you may safely ignore
983 them.
984 - Run
986 perl Makefile.PL
988 which will prompt you for some settings. The really important ones
990 are:
991 Which DBMS do you want to use?
993 enter a 1 here (MySQL only), and
995 Where is your mysql installed? Please tell me the directory that
997 contains the subdir include.
998 where you have to enter the win32clients directory, for example
1000 C:\mysql or C:\tmp\mysql.
1001 - Continued in the usual way:
1003 nmake
1005 nmake install
1006 If you want to create a PPM package for the ActiveState Perl version,
1008 then modify the above steps as follows: Run
1009 perl Makefile.PL NAME=DBD-mysql BINARY_LOCATION=DBD-mysql.tar.gz
1011 nmake ppd
1012 nmake
1013 Once that is done, use tar and gzip (for example those from the
1015 CygWin32 distribution) to create an archive:
1016 mkdir x86
1018 tar cf x86/DBD-mysql.tar blib
1019 gzip x86/DBD-mysql.tar
1020 Put the files x86/DBD-mysql.tar.gz and DBD-mysql.ppd onto some WWW
1022 server and install them by typing
1023 install http://your.server.name/your/directory/DBD-mysql.ppd
1025 in the PPM program.
1027
1029 Originally, there was a non-DBI driver, Mysql, which was much like PHP
1030 drivers such as mysql and mysqli. The Mysql module was originally
1031 written by Andreas KA~Xnig <koenig@kulturbox.de> who still, to this
1032 day, contributes patches to DBD::mysql. An emulated version of Mysql
1033 was provided to DBD::mysql from Jochen Wiedmann, but eventually
1034 deprecated as it was another bundle of code to maintain.
1035 The first incarnation of DBD::mysql was developed by Alligator
1037 Descartes, who was also aided and abetted by Gary Shea, Andreas KA~Xnig
1038 and Tim Bunce.
1039 The current incarnation of DBD::mysql was written by Jochen Wiedmann,
1041 then numerous changes and bug-fixes were added by Rudy Lippan. Next,
1042 prepared statement support was added by Patrick Galbraith and Alexy
1043 Stroganov (who also soley added embedded server support).
1044 For the past seven years DBD::mysql has been maintained by Patrick
1046 Galbraith (patg@patg.net) along with the entire community of Perl
1047 developers who keep sending patches and making Patrick's job easier.
1048
1050 Anyone who desires to contribute to this project is encouraged to do
1051 so. Currently, the sourcecode for this project can be found at Github:
1052 git://github.com/CaptTofu/DBD-mysql.git
1054 Either fork this repository and produce a branch with your changeset
1056 that the maintainer can merge to his tree, or create a diff with git.
1057 The maintainer is more than glad to take contributions from the
1058 community as many features and fixes from DBD::mysql have come from the
1059 community.
1060
1062 This module is Large Portions Copyright (c) 2004-2010 Patrick Galbraith
1063 Large Portions Copyright (c) 2004-2006 Alexey Stroganov Large Portions
1064 Copyright (c) 2003-2005 Rudolf Lippan Large Portions Copyright (c)
1065 1997-2003 Jochen Wiedmann, with code portions Copyright (c)1994-1997
1066 their original authors This module is released under the same license
1067 as Perl itself. See the Perl README for details.
1068
1070 This module is maintained and supported on a mailing list,
1071 perl@lists.mysql.com
1073 To subscribe to this list, go to
1075 http://lists.mysql.com/perl?sub=1
1077 Mailing list archives are available at
1079 http://lists.mysql.com/perl
1081 Additionally you might try the dbi-user mailing list for questions
1083 about DBI and its modules in general. Subscribe via
1084 dbi-users-subscribe@perl.org
1086 Mailing list archives are at
1088 http://groups.google.com/group/perl.dbi.users?hl=en&lr=
1090 Also, the main DBI site is at
1092 http://dbi.perl.org/
1094 And source:
1096 git://github.com/CaptTofu/DBD-mysql.git
1098
1100 Additional information on the DBI project can be found on the World
1101 Wide Web at the following URL:
1102 http://dbi.perl.org
1104 where documentation, pointers to the mailing lists and mailing list
1106 archives and pointers to the most current versions of the modules can
1107 be used.
1108 Information on the DBI interface itself can be gained by typing:
1110 perldoc DBI
1112 right now!
1114
1116 Please report bugs, including all the information needed such as
1117 DBD::mysql version, MySQL version, OS type/version, etc to this link:
1118 http://bugs.mysql.com/
1120
1122 Hey! The above document had some coding errors, which are explained
1123 below:
1124 Around line 1234:
1126 '=item' outside of any '=over'
1127 Around line 1382:
1129 You forgot a '=back' before '=head1'
1130 Around line 1624:
1132 You forgot a '=back' before '=head1'
1133perl v5.12.1 2010-08-11 DBD::mysql(3)