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_write_timeout
193 If your DSN contains the option "mysql_write_timeout=##", the
194 write operation to the server will timeout if it has not been
195 successful after the given number of seconds.
196 mysql_read_timeout
198 If your DSN contains the option "mysql_read_timeout=##", the
199 read operation to the server will timeout if it has not been
200 successful after the given number of seconds.
201 mysql_init_command
203 If your DSN contains the option "mysql_init_command_timeout=##", then
204 this SQL statement is executed when connecting to the MySQL server.
205 It is automatically re-executed if reconnection occurs.
206 mysql_read_default_file
208 mysql_read_default_group
209 These options can be used to read a config file like
210 /etc/my.cnf or ~/.my.cnf. By default MySQL's C client library
211 doesn't use any config files unlike the client programs (mysql,
212 mysqladmin, ...) that do, but outside of the C client library.
213 Thus you need to explicitly request reading a config file, as
214 in
215 $dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf";
217 $dbh = DBI->connect($dsn, $user, $password)
218 The option mysql_read_default_group can be used to specify the
220 default group in the config file: Usually this is the client
221 group, but see the following example:
222 [client]
224 host=localhost
225 [perl]
227 host=perlhost
228 (Note the order of the entries! The example won't work, if you
230 reverse the [client] and [perl] sections!)
231 If you read this config file, then you'll be typically
233 connected to localhost. However, by using
234 $dsn = "DBI:mysql:test;mysql_read_default_group=perl;"
236 . "mysql_read_default_file=/home/joe/my.cnf";
237 $dbh = DBI->connect($dsn, $user, $password);
238 you'll be connected to perlhost. Note that if you specify a
240 default group and do not specify a file, then the default
241 config files will all be read. See the documentation of the C
242 function mysql_options() for details.
243 mysql_socket
245 As of MySQL 3.21.15, it is possible to choose the Unix socket
246 that is used for connecting to the server. This is done, for
247 example, with
248 mysql_socket=/dev/mysql
250 Usually there's no need for this option, unless you are using
252 another location for the socket than that built into the
253 client.
254 mysql_ssl
256 A true value turns on the CLIENT_SSL flag when connecting to
257 the MySQL database:
258 mysql_ssl=1
260 This means that your communication with the server will be
262 encrypted.
263 If you turn mysql_ssl on, you might also wish to use the
265 following flags:
266 mysql_ssl_client_key
268 mysql_ssl_client_cert
269 mysql_ssl_ca_file
270 mysql_ssl_ca_path
271 mysql_ssl_cipher
272 These are used to specify the respective parameters of a call
273 to mysql_ssl_set, if mysql_ssl is turned on.
274 mysql_local_infile
276 As of MySQL 3.23.49, the LOCAL capability for LOAD DATA may be
277 disabled in the MySQL client library by default. If your DSN
278 contains the option "mysql_local_infile=1", LOAD DATA LOCAL
279 will be enabled. (However, this option is *ineffective* if the
280 server has also been configured to disallow LOCAL.)
281 mysql_multi_statements
283 As of MySQL 4.1, support for multiple statements seperated by a
284 semicolon (;) may be enabled by using this option. Enabling
285 this option may cause problems if server-side prepared
286 statements are also enabled.
287 Prepared statement support (server side prepare)
289 As of 3.0002_1, server side prepare statements were on by
290 default (if your server was >= 4.1.3). As of 3.0009, they were
291 off by default again due to issues with the prepared statement
292 API (all other mysql connectors are set this way until C API
293 issues are resolved). The requirement to use prepared
294 statements still remains that you have a server >= 4.1.3
295 To use server side prepared statements, all you need to do is
297 set the variable mysql_server_prepare in the connect:
298 $dbh = DBI->connect(
300 "DBI:mysql:database=test;host=localhost;mysql_server_prepare=1",
301 "",
302 "",
303 { RaiseError => 1, AutoCommit => 1 }
304 );
305 * Note: delimiter for this param is ';'
307 There are many benefits to using server side prepare
309 statements, mostly if you are performing many inserts because
310 of that fact that a single statement is prepared to accept
311 multiple insert values.
312 To make sure that the 'make test' step tests whether server
314 prepare works, you just need to export the env variable
315 MYSQL_SERVER_PREPARE:
316 export MYSQL_SERVER_PREPARE=1
318 mysql_embedded_options
320 The option <mysql_embedded_options> can be used to pass
321 'command-line' options to embedded server.
322 Example:
324 use DBI;
326 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_options=--help,--verbose";
327 $dbh = DBI->connect($testdsn,"a","b");
328 This would cause the command line help to the embedded MySQL
330 server library to be printed.
331 mysql_embedded_groups
333 The option <mysql_embedded_groups> can be used to specify the
334 groups in the config file(my.cnf) which will be used to get
335 options for embedded server. If not specified [server] and
336 [embedded] groups will be used.
337 Example:
339 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_groups=embedded_server,common";
341 Private MetaData Methods
343 ListDBs
344 my $drh = DBI->install_driver("mysql");
345 @dbs = $drh->func("$hostname:$port", '_ListDBs');
346 @dbs = $drh->func($hostname, $port, '_ListDBs');
347 @dbs = $dbh->func('_ListDBs');
348 Returns a list of all databases managed by the MySQL server running
350 on $hostname, port $port. This is a legacy method. Instead, you
351 should use the portable method
352 @dbs = DBI->data_sources("mysql");
354 Server Administration
356 admin
357 $rc = $drh->func("createdb", $dbname, [host, user, password,], 'admin');
358 $rc = $drh->func("dropdb", $dbname, [host, user, password,], 'admin');
359 $rc = $drh->func("shutdown", [host, user, password,], 'admin');
360 $rc = $drh->func("reload", [host, user, password,], 'admin');
361 or
363 $rc = $dbh->func("createdb", $dbname, 'admin');
365 $rc = $dbh->func("dropdb", $dbname, 'admin');
366 $rc = $dbh->func("shutdown", 'admin');
367 $rc = $dbh->func("reload", 'admin');
368 For server administration you need a server connection. For
370 obtaining this connection you have two options: Either use a driver
371 handle (drh) and supply the appropriate arguments (host, defaults
372 localhost, user, defaults to '' and password, defaults to ''). A
373 driver handle can be obtained with
374 $drh = DBI->install_driver('mysql');
376 Otherwise reuse the existing connection of a database handle (dbh).
378 There's only one function available for administrative purposes,
380 comparable to the m(y)sqladmin programs. The command being execute
381 depends on the first argument:
382 createdb
384 Creates the database $dbname. Equivalent to "m(y)sqladmin
385 create $dbname".
386 dropdb
388 Drops the database $dbname. Equivalent to "m(y)sqladmin drop
389 $dbname".
390 It should be noted that database deletion is not prompted for
392 in any way. Nor is it undo-able from DBI.
393 Once you issue the dropDB() method, the database will be gone!
395 These method should be used at your own risk.
397 shutdown
399 Silently shuts down the database engine. (Without prompting!)
400 Equivalent to "m(y)sqladmin shutdown".
401 reload
403 Reloads the servers configuration files and/or tables. This can
404 be particularly important if you modify access privileges or
405 create new users.
406
408 The DBD::mysql driver supports the following attributes of database
409 handles (read only):
410 $errno = $dbh->{'mysql_errno'};
412 $error = $dbh->{'mysql_error'};
413 $info = $dbh->{'mysql_hostinfo'};
414 $info = $dbh->{'mysql_info'};
415 $insertid = $dbh->{'mysql_insertid'};
416 $info = $dbh->{'mysql_protoinfo'};
417 $info = $dbh->{'mysql_serverinfo'};
418 $info = $dbh->{'mysql_stat'};
419 $threadId = $dbh->{'mysql_thread_id'};
420 These correspond to mysql_errno(), mysql_error(),
422 mysql_get_host_info(), mysql_info(), mysql_insert_id(),
423 mysql_get_proto_info(), mysql_get_server_info(), mysql_stat() and
424 mysql_thread_id(), respectively.
425 $info_hashref = $dhb->{mysql_dbd_stats}
427 DBD::mysql keeps track of some statistics in the mysql_dbd_stats
429 attribute. The following stats are being maintained:
430 auto_reconnects_ok
432 The number of times that DBD::mysql successfully reconnected to the
433 mysql server.
434 auto_reconnects_failed
436 The number of times that DBD::mysql tried to reconnect to mysql but
437 failed.
438 The DBD::mysql driver also supports the following attribute(s) of
440 database handles (read/write):
441 $bool_value = $dbh->{mysql_auto_reconnect};
443 $dbh->{mysql_auto_reconnect} = $AutoReconnect ? 1 : 0;
444 mysql_auto_reconnect
446 This attribute determines whether DBD::mysql will automatically
447 reconnect to mysql if the connection be lost. This feature defaults
448 to off; however, if either the GATEWAY_INTERFACE or MOD_PERL
449 envionment variable is set, DBD::mysql will turn
450 mysql_auto_reconnect on. Setting mysql_auto_reconnect to on is not
451 advised if 'lock tables' is used because if DBD::mysql reconnect to
452 mysql all table locks will be lost. This attribute is ignored when
453 AutoCommit is turned off, and when AutoCommit is turned off,
454 DBD::mysql will not automatically reconnect to the server.
455 It is also possible to set the default value of the
457 "mysql_auto_reconnect" attribute for the $dbh by passing it in the
458 "\%attr" hash for "DBI-"connect>.
459 Note that if you are using a module or framework that performs
461 reconnections for you (for example DBIx::Connector in fixup mode),
462 this value must be set to 0.
463 mysql_use_result
465 This attribute forces the driver to use mysql_use_result rather
466 than mysql_store_result. The former is faster and less memory
467 consuming, but tends to block other processes. (That's why
468 mysql_store_result is the default.)
469 It is possible to set the default value of the "mysql_use_result"
471 attribute for the $dbh using several ways:
472 - through DSN
474 $dbh= DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
476 - after creation of database handle
478 $dbh->{'mysql_use_result'}=0; #disable
480 $dbh->{'mysql_use_result'}=1; #enable
481 It is possible to set/unset the "mysql_use_result" attribute after
483 creation of the statement handle. See below.
484 mysql_enable_utf8
486 This attribute determines whether DBD::mysql should assume strings
487 stored in the database are utf8. This feature defaults to off.
488 When set, a data retrieved from a textual column type (char,
490 varchar, etc) will have the UTF-8 flag turned on if necessary.
491 This enables character semantics on that string. You will also
492 need to ensure that your database / table / column is configured to
493 use UTF8. See Chapter 10 of the mysql manual for details.
494 Additionally, turning on this flag tells MySQL that incoming data
496 should be treated as UTF-8. This will only take effect if used as
497 part of the call to connect(). If you turn the flag on after
498 connecting, you will need to issue the command "SET NAMES utf8" to
499 get the same effect.
500 This option is experimental and may change in future versions.
502 mysql_bind_type_guessing
504 This attribute causes the driver (emulated prepare statements) to
505 attempt to guess if a value being bound is a numeric value, and if
506 so, doesn't quote the value. This was created by Dragonchild and
507 is one way to deal with the performance issue of using quotes in a
508 statement that is inserting or updating a large numeric value. This
509 was previously called "unsafe_bind_type_guessing" because it is
510 experimental. I have successfully run the full test suite with this
511 option turned on, the name can now be simply
512 "mysql_bind_type_guessing".
513 CAVEAT: Even though you can insert an integer value into a
515 character column, if this column is indexed, if you query that
516 column with the integer value not being quoted, it will not use the
517 index:
518 MariaDB [test]> explain select * from test where value0 = '3' \G
520 *************************** 1. row ***************************
521 id: 1
522 select_type: SIMPLE
523 table: test
524 type: ref possible_keys: value0
525 key: value0
526 key_len: 13
527 ref: const
528 rows: 1
529 Extra: Using index condition 1 row in set (0.00 sec)
530 MariaDB [test]> explain select * from test where value0 = 3
532 -> \G *************************** 1. row
533 ***************************
534 id: 1
535 select_type: SIMPLE
536 table: test
537 type: ALL possible_keys: value0
538 key: NULL
539 key_len: NULL
540 ref: NULL
541 rows: 6
542 Extra: Using where 1 row in set (0.00 sec)
543 See bug: https://rt.cpan.org/Ticket/Display.html?id=43822
545 "mysql_bind_type_guessing" can be turned on via
547 - through DSN
549 my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
551 { mysql_bind_type_guessing => 1})
552 - OR after handle creation
554 $dbh->{mysql_bind_type_guessing} = 1;
556 mysql_bind_comment_placeholders
558 This attribute causes the driver (emulated prepare statements) will
559 cause any placeholders in comments to be bound. This is not correct
560 prepared statement behavior, but some developers have come to
561 depend on this behavior, so I have made it available in 4.015
562 mysql_no_autocommit_cmd
564 This attribute causes the driver to not issue 'set autocommit'
565 either through explicit or using mysql_autocommit(). This is
566 particularly useful in the case of using MySQL Proxy.
567 See the bug report:
569 https://rt.cpan.org/Public/Bug/Display.html?id=46308
571 "mysql_no_autocommit_cmd" can be turned on via
573 - through DSN
575 my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
577 { mysql_no_autocommit_cmd => 1})
578 - OR after handle creation
580 $dbh->{mysql_no_autocommit_cmd} = 1;
582
584 The statement handles of DBD::mysql support a number of attributes. You
585 access these by using, for example,
586 my $numFields = $sth->{'NUM_OF_FIELDS'};
588 Note, that most attributes are valid only after a successfull execute.
590 An "undef" value will returned in that case. The most important
591 exception is the "mysql_use_result" attribute: This forces the driver
592 to use mysql_use_result rather than mysql_store_result. The former is
593 faster and less memory consuming, but tends to block other processes.
594 (That's why mysql_store_result is the default.)
595 To set the "mysql_use_result" attribute, use either of the following:
597 my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1});
599 or
601 my $sth = $dbh->prepare("QUERY");
603 $sth->{"mysql_use_result"} = 1;
604 Column dependent attributes, for example NAME, the column names, are
606 returned as a reference to an array. The array indices are
607 corresponding to the indices of the arrays returned by fetchrow and
608 similar methods. For example the following code will print a header of
609 table names together with all rows:
610 my $sth = $dbh->prepare("SELECT * FROM $table");
612 if (!$sth) {
613 die "Error:" . $dbh->errstr . "\n";
614 }
615 if (!$sth->execute) {
616 die "Error:" . $sth->errstr . "\n";
617 }
618 my $names = $sth->{'NAME'};
619 my $numFields = $sth->{'NUM_OF_FIELDS'};
620 for (my $i = 0; $i < $numFields; $i++) {
621 printf("%s%s", $i ? "," : "", $$names[$i]);
622 }
623 print "\n";
624 while (my $ref = $sth->fetchrow_arrayref) {
625 for (my $i = 0; $i < $numFields; $i++) {
626 printf("%s%s", $i ? "," : "", $$ref[$i]);
627 }
628 print "\n";
629 }
630 For portable applications you should restrict yourself to attributes
632 with capitalized or mixed case names. Lower case attribute names are
633 private to DBD::mysql. The attribute list includes:
634 ChopBlanks
636 this attribute determines whether a fetchrow will chop preceding
637 and trailing blanks off the column values. Chopping blanks does not
638 have impact on the max_length attribute.
639 mysql_insertid
641 MySQL has the ability to choose unique key values automatically. If
642 this happened, the new ID will be stored in this attribute. An
643 alternative way for accessing this attribute is via
644 $dbh->{'mysql_insertid'}. (Note we are using the $dbh in this
645 case!)
646 mysql_is_blob
648 Reference to an array of boolean values; TRUE indicates, that the
649 respective column is a blob. This attribute is valid for MySQL
650 only.
651 mysql_is_key
653 Reference to an array of boolean values; TRUE indicates, that the
654 respective column is a key. This is valid for MySQL only.
655 mysql_is_num
657 Reference to an array of boolean values; TRUE indicates, that the
658 respective column contains numeric values.
659 mysql_is_pri_key
661 Reference to an array of boolean values; TRUE indicates, that the
662 respective column is a primary key.
663 mysql_is_auto_increment
665 Reference to an array of boolean values; TRUE indicates that the
666 respective column is an AUTO_INCREMENT column. This is only valid
667 for MySQL.
668 mysql_length
670 mysql_max_length
671 A reference to an array of maximum column sizes. The max_length is
672 the maximum physically present in the result table, length gives
673 the theoretically possible maximum. max_length is valid for MySQL
674 only.
675 mysql_clientinfo
677 List information of the MySQL client library that DBD::mysql was
678 built against:
679 print "$dbh->{mysql_clientinfo}\n";
681 5.2.0-MariaDB
683 mysql_clientversion
685 print "$dbh->{mysql_clientversion}\n";
686 50200
688 mysql_serverversion
690 print "$dbh->{mysql_serverversion}\n";
691 50200
693 NAME
695 A reference to an array of column names.
696 NULLABLE
698 A reference to an array of boolean values; TRUE indicates that this
699 column may contain NULL's.
700 NUM_OF_FIELDS
702 Number of fields returned by a SELECT or LISTFIELDS statement. You
703 may use this for checking whether a statement returned a result: A
704 zero value indicates a non-SELECT statement like INSERT, DELETE or
705 UPDATE.
706 mysql_table
708 A reference to an array of table names, useful in a JOIN result.
709 TYPE
711 A reference to an array of column types. The engine's native column
712 types are mapped to portable types like DBI::SQL_INTEGER() or
713 DBI::SQL_VARCHAR(), as good as possible. Not all native types have
714 a meaningfull equivalent, for example
715 DBD::mysql::FIELD_TYPE_INTERVAL is mapped to DBI::SQL_VARCHAR().
716 If you need the native column types, use mysql_type. See below.
717 mysql_type
719 A reference to an array of MySQL's native column types, for example
720 DBD::mysql::FIELD_TYPE_SHORT() or DBD::mysql::FIELD_TYPE_STRING().
721 Use the TYPE attribute, if you want portable types like
722 DBI::SQL_SMALLINT() or DBI::SQL_VARCHAR().
723 mysql_type_name
725 Similar to mysql, but type names and not numbers are returned.
726 Whenever possible, the ANSI SQL name is preferred.
727 mysql_warning_count
729 The number of warnings generated during execution of the SQL
730 statement.
731
733 Beginning with DBD::mysql 2.0416, transactions are supported. The
734 transaction support works as follows:
735 · By default AutoCommit mode is on, following the DBI specifications.
737 · If you execute
739 $dbh->{'AutoCommit'} = 0;
741 or
743 $dbh->{'AutoCommit'} = 1;
745 then the driver will set the MySQL server variable autocommit to 0
747 or 1, respectively. Switching from 0 to 1 will also issue a COMMIT,
748 following the DBI specifications.
749 · The methods
751 $dbh->rollback();
753 $dbh->commit();
754 will issue the commands COMMIT and ROLLBACK, respectively. A
756 ROLLBACK will also be issued if AutoCommit mode is off and the
757 database handles DESTROY method is called. Again, this is following
758 the DBI specifications.
759 Given the above, you should note the following:
761 · You should never change the server variable autocommit manually,
763 unless you are ignoring DBI's transaction support.
764 · Switching AutoCommit mode from on to off or vice versa may fail.
766 You should always check for errors, when changing AutoCommit mode.
767 The suggested way of doing so is using the DBI flag RaiseError. If
768 you don't like RaiseError, you have to use code like the following:
769 $dbh->{'AutoCommit'} = 0;
771 if ($dbh->{'AutoCommit'}) {
772 # An error occurred!
773 }
774 · If you detect an error while changing the AutoCommit mode, you
776 should no longer use the database handle. In other words, you
777 should disconnect and reconnect again, because the transaction mode
778 is unpredictable. Alternatively you may verify the transaction mode
779 by checking the value of the server variable autocommit. However,
780 such behaviour isn't portable.
781 · DBD::mysql has a "reconnect" feature that handles the so-called
783 MySQL "morning bug": If the server has disconnected, most probably
784 due to a timeout, then by default the driver will reconnect and
785 attempt to execute the same SQL statement again. However, this
786 behaviour is disabled when AutoCommit is off: Otherwise the
787 transaction state would be completely unpredictable after a
788 reconnect.
789 · The "reconnect" feature of DBD::mysql can be toggled by using the
791 mysql_auto_reconnect attribute. This behaviour should be turned off
792 in code that uses LOCK TABLE because if the database server time
793 out and DBD::mysql reconnect, table locks will be lost without any
794 indication of such loss.
795
797 As of version 3.0002_5, DBD::mysql supports multiple result sets
798 (Thanks to Guy Harrison!). This is the first release of this
799 functionality, so there may be issues. Please report bugs if you run
800 into them!
801 The basic usage of multiple result sets is
803 do
805 {
806 while (@row= $sth->fetchrow_array())
807 {
808 do stuff;
809 }
810 } while ($sth->more_results)
811 An example would be:
813 $dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
815 $dbh->do("create procedure somproc() deterministic
817 begin
818 declare a,b,c,d int;
819 set a=1;
820 set b=2;
821 set c=3;
822 set d=4;
823 select a, b, c, d;
824 select d, c, b, a;
825 select b, a, c, d;
826 select c, b, d, a;
827 end") or print $DBI::errstr;
828 $sth=$dbh->prepare('call someproc()') ||
830 die $DBI::err.": ".$DBI::errstr;
831 $sth->execute || die DBI::err.": ".$DBI::errstr; $rowset=0;
833 do {
834 print "\nRowset ".++$i."\n---------------------------------------\n\n";
835 foreach $colno (0..$sth->{NUM_OF_FIELDS}) {
836 print $sth->{NAME}->[$colno]."\t";
837 }
838 print "\n";
839 while (@row= $sth->fetchrow_array()) {
840 foreach $field (0..$#row) {
841 print $row[$field]."\t";
842 }
843 print "\n";
844 }
845 } until (!$sth->more_results)
846 For more examples, please see the eg/ directory. This is where helpful
848 DBD::mysql code snippits will be added in the future.
849 Issues with Multiple result sets
851 So far, the main issue is if your result sets are "jagged", meaning,
852 the number of columns of your results vary. Varying numbers of columns
853 could result in your script crashing. This is something that will be
854 fixed soon.
855
857 The multithreading capabilities of DBD::mysql depend completely on the
858 underlying C libraries: The modules are working with handle data only,
859 no global variables are accessed or (to the best of my knowledge)
860 thread unsafe functions are called. Thus DBD::mysql is believed to be
861 completely thread safe, if the C libraries are thread safe and you
862 don't share handles among threads.
863 The obvious question is: Are the C libraries thread safe? In the case
865 of MySQL the answer is "mostly" and, in theory, you should be able to
866 get a "yes", if the C library is compiled for being thread safe (By
867 default it isn't.) by passing the option -with-thread-safe-client to
868 configure. See the section on How to make a threadsafe client in the
869 manual.
870
872 You can make a single asynchronous query per MySQL connection; this
873 allows you to submit a long-running query to the server and have an
874 event loop inform you when it's ready. An asynchronous query is
875 started by either setting the 'async' attribute to a truthy value in
876 the "do" in DBI method, or in the "prepare" in DBI method. Statements
877 created with 'async' set to true in prepare always run their queries
878 asynchronously when "execute" in DBI is called. The driver also offers
879 three additional methods: "mysql_async_result", "mysql_async_ready",
880 and "mysql_fd". "mysql_async_result" returns what do or execute would
881 have; that is, the number of rows affected. "mysql_async_ready"
882 returns true if "mysql_async_result" will not block, and zero
883 otherwise. They both return "undef" if that handle is not currently
884 running an asynchronous query. "mysql_fd" returns the file descriptor
885 number for the MySQL connection; you can use this in an event loop.
886 Here's an example of how to use the asynchronous query interface:
888 use feature 'say';
890 $dbh->do('SELECT SLEEP(10)', { async => 1 });
891 until($dbh->mysql_async_ready) {
892 say 'not ready yet!';
893 sleep 1;
894 }
895 my $rows = $dbh->mysql_async_result;
896
898 Windows users may skip this section and pass over to WIN32 INSTALLATION
899 below. Others, go on reading.
900 Environment Variables
902 For ease of use, you can now set environment variables for DBD::mysql
903 installation. You can set any or all of the options, and export them by
904 putting them in your .bashrc or the like:
905 export DBD_MYSQL_CFLAGS=-I/usr/local/mysql/include/mysql
907 export DBD_MYSQL_LIBS="-L/usr/local/mysql/lib/mysql -lmysqlclient"
908 export DBD_MYSQL_EMBEDDED=
909 export DBD_MYSQL_CONFIG=mysql_config
910 export DBD_MYSQL_NOCATCHSTDERR=0
911 export DBD_MYSQL_NOFOUNDROWS=0
912 export DBD_MYSQL_SSL=
913 export DBD_MYSQL_TESTDB=test
914 export DBD_MYSQL_TESTHOST=localhost
915 export DBD_MYSQL_TESTPASSWORD=s3kr1+
916 export DBD_MYSQL_TESTPORT=3306
917 export DBD_MYSQL_TESTUSER=me
918 The most useful may be the host, database, port, socket, user, and
920 password.
921 Installation will first look to your mysql_config, and then your
923 environment variables, and then it will guess with intelligent
924 defaults.
925 Installing with CPAN
927 First of all, you do not need an installed MySQL server for installing
928 DBD::mysql. However, you need at least the client libraries and
929 possibly the header files, if you are compiling DBD::mysql from source.
930 In the case of MySQL you can create a client-only version by using the
931 configure option --without-server. If you are using precompiled
932 binaries, then it may be possible to use just selected RPM's like
933 MySQL-client and MySQL-devel or something similar, depending on the
934 distribution.
935 First you need to install the DBI module. For using dbimon, a simple
937 DBI shell it is recommended to install Data::ShowTable another Perl
938 module.
939 I recommend trying automatic installation via the CPAN module. Try
941 perl -MCPAN -e shell
943 If you are using the CPAN module for the first time, it will prompt you
945 a lot of questions. If you finally receive the CPAN prompt, enter
946 install Bundle::DBD::mysql
948 Manual Installation
950 If this fails (which may be the case for a number of reasons, for
951 example because you are behind a firewall or don't have network
952 access), you need to do a manual installation. First of all you need to
953 fetch the modules from CPAN search
954 http://search.cpan.org/
956 The following modules are required
958 DBI
960 Data::ShowTable
961 DBD::mysql
962 Then enter the following commands (note - versions are just examples):
964 gzip -cd DBI-(version).tar.gz | tar xf -
966 cd DBI-(version)
967 perl Makefile.PL
968 make
969 make test
970 make install
971 cd ..
973 gzip -cd Data-ShowTable-(version).tar.gz | tar xf -
974 cd Data-ShowTable-3.3
975 perl Makefile.PL
976 make
977 make install
978 cd ..
980 gzip -cd DBD-mysql-(version)-tar.gz | tar xf -
981 cd DBD-mysql-(version)
982 perl Makefile.PL
983 make
984 make test
985 make install
986 During "perl Makefile.PL" you will be prompted some questions. Other
988 questions are the directories with header files and libraries. For
989 example, of your file mysql.h is in /usr/include/mysql/mysql.h, then
990 enter the header directory /usr, likewise for
991 /usr/lib/mysql/libmysqlclient.a or /usr/lib/libmysqlclient.so.
992
994 If you are using ActivePerl, you may use ppm to install DBD-mysql. For
995 Perl 5.6, upgrade to Build 623 or later, then it is sufficient to run
996 ppm install DBI
998 ppm install DBD::mysql
999 If you need an HTTP proxy, you might need to set the environment
1001 variable http_proxy, for example like this:
1002 set http_proxy=http://myproxy.com:8080/
1004 As of this writing, DBD::mysql is missing in the ActivePerl 5.8.0
1006 repository. However, Randy Kobes has kindly donated an own distribution
1007 and the following might succeed:
1008 ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd
1010 Otherwise you definitely *need* a C compiler. And it *must* be the same
1012 compiler that was being used for compiling Perl itself. If you don't
1013 have a C compiler, the file README.win32 from the Perl source
1014 distribution tells you where to obtain freely distributable C compilers
1015 like egcs or gcc. The Perl sources are available via CPAN search
1016 http://search.cpan.org
1018 I recommend using the win32clients package for installing DBD::mysql
1020 under Win32, available for download on www.tcx.se. The following steps
1021 have been required for me:
1022 - The current Perl versions (5.6, as of this writing) do have a
1024 problem with detecting the C libraries. I recommend to apply the
1025 following patch:
1026 *** c:\Perl\lib\ExtUtils\Liblist.pm.orig Sat Apr 15 20:03:40 2000
1028 --- c:\Perl\lib\ExtUtils\Liblist.pm Sat Apr 15 20:03:45 2000
1029 ***************
1030 *** 230,235 ****
1031 --- 230,239 ----
1032 # add "$Config{installarchlib}/CORE" to default search path
1033 push @libpath, "$Config{installarchlib}/CORE";
1034 + if ($VC and exists($ENV{LIB}) and defined($ENV{LIB})) {
1036 + push(@libpath, split(/;/, $ENV{LIB}));
1037 + }
1038 +
1039 foreach (Text::ParseWords::quotewords('\s+', 0, $potential_libs)){
1040 $thislib = $_;
1042 - Extract sources into C:\. This will create a directory C:\mysql
1044 with subdirectories include and lib.
1045 IMPORTANT: Make sure this subdirectory is not shared by other TCX
1047 files! In particular do *not* store the MySQL server in the same
1048 directory. If the server is already installed in C:\mysql, choose a
1049 location like C:\tmp, extract the win32clients there. Note that
1050 you can remove this directory entirely once you have installed
1051 DBD::mysql.
1052 - Extract the DBD::mysql sources into another directory, for example
1054 C:\src\siteperl
1055 - Open a DOS shell and change directory to C:\src\siteperl.
1057 - The next step is only required if you repeat building the modules:
1059 Make sure that you have a clean build tree by running
1060 nmake realclean
1062 If you don't have VC++, replace nmake with your flavour of make. If
1064 error messages are reported in this step, you may safely ignore
1065 them.
1066 - Run
1068 perl Makefile.PL
1070 which will prompt you for some settings. The really important ones
1072 are:
1073 Which DBMS do you want to use?
1075 enter a 1 here (MySQL only), and
1077 Where is your mysql installed? Please tell me the directory that
1079 contains the subdir include.
1080 where you have to enter the win32clients directory, for example
1082 C:\mysql or C:\tmp\mysql.
1083 - Continued in the usual way:
1085 nmake
1087 nmake install
1088 If you want to create a PPM package for the ActiveState Perl version,
1090 then modify the above steps as follows: Run
1091 perl Makefile.PL NAME=DBD-mysql BINARY_LOCATION=DBD-mysql.tar.gz
1093 nmake ppd
1094 nmake
1095 Once that is done, use tar and gzip (for example those from the
1097 CygWin32 distribution) to create an archive:
1098 mkdir x86
1100 tar cf x86/DBD-mysql.tar blib
1101 gzip x86/DBD-mysql.tar
1102 Put the files x86/DBD-mysql.tar.gz and DBD-mysql.ppd onto some WWW
1104 server and install them by typing
1105 install http://your.server.name/your/directory/DBD-mysql.ppd
1107 in the PPM program.
1109
1111 Originally, there was a non-DBI driver, Mysql, which was much like PHP
1112 drivers such as mysql and mysqli. The Mysql module was originally
1113 written by Andreas KieXXnig <koenig@kulturbox.de> who still, to this
1114 day, contributes patches to DBD::mysql. An emulated version of Mysql
1115 was provided to DBD::mysql from Jochen Wiedmann, but eventually
1116 deprecated as it was another bundle of code to maintain.
1117 The first incarnation of DBD::mysql was developed by Alligator
1119 Descartes, who was also aided and abetted by Gary Shea, Andreas
1120 KieXXnig and Tim Bunce.
1121 The current incarnation of DBD::mysql was written by Jochen Wiedmann,
1123 then numerous changes and bug-fixes were added by Rudy Lippan. Next,
1124 prepared statement support was added by Patrick Galbraith and Alexy
1125 Stroganov (who also soley added embedded server support).
1126 For the past seven years DBD::mysql has been maintained by Patrick
1128 Galbraith (patg@patg.net) along with the entire community of Perl
1129 developers who keep sending patches and making Patrick's job easier.
1130
1132 Anyone who desires to contribute to this project is encouraged to do
1133 so. Currently, the sourcecode for this project can be found at Github:
1134 git://github.com/CaptTofu/DBD-mysql.git
1136 Either fork this repository and produce a branch with your changeset
1138 that the maintainer can merge to his tree, or create a diff with git.
1139 The maintainer is more than glad to take contributions from the
1140 community as many features and fixes from DBD::mysql have come from the
1141 community.
1142
1144 This module is Large Portions Copyright (c) 2004-2010 Patrick Galbraith
1145 Large Portions Copyright (c) 2004-2006 Alexey Stroganov Large Portions
1146 Copyright (c) 2003-2005 Rudolf Lippan Large Portions Copyright (c)
1147 1997-2003 Jochen Wiedmann, with code portions Copyright (c)1994-1997
1148 their original authors This module is released under the same license
1149 as Perl itself. See the Perl README for details.
1150
1152 This module is maintained and supported on a mailing list, dbi-users.
1153 To subscribe to this list, send and email to
1155 dbi-users-subscribe@perl.org
1157 Mailing list archives are at
1159 http://groups.google.com/group/perl.dbi.users?hl=en&lr=
1161 Also, the main DBI site is at
1163 http://dbi.perl.org/
1165 And source:
1167 git://github.com/CaptTofu/DBD-mysql.git
1169
1171 Additional information on the DBI project can be found on the World
1172 Wide Web at the following URL:
1173 http://dbi.perl.org
1175 where documentation, pointers to the mailing lists and mailing list
1177 archives and pointers to the most current versions of the modules can
1178 be used.
1179 Information on the DBI interface itself can be gained by typing:
1181 perldoc DBI
1183 Information on the DBD::mysql specifically can be gained by typing:
1185 perldoc DBD::mysql
1187
1189 Please report bugs, including all the information needed such as
1190 DBD::mysql version, MySQL version, OS type/version, etc to this link:
1191 http://rt.cpan.org
1193 Note: until recently, MySQL/Sun/Oracle responded to bugs and assisted
1195 in fixing bugs which many thanks should be given for their help! This
1196 driver is outside the realm of the numerous components they support,
1197 and the maintainer and community solely support DBD::mysql
1198
1200 Hey! The above document had some coding errors, which are explained
1201 below:
1202 Around line 1315:
1204 '=item' outside of any '=over'
1205 Around line 1464:
1207 You forgot a '=back' before '=head1'
1208 Around line 1727:
1210 You forgot a '=back' before '=head1'
1211 Around line 2070:
1213 Non-ASCII character seen before =encoding in 'KieXXnig'. Assuming
1214 UTF-8
1215perl v5.16.3 2013-04-12 DBD::mysql(3)