NYTProf Profile: /usr/lib/perl5/DBD/mysql.pm

File	/usr/lib/perl5/DBD/mysql.pm
Statements Executed	2122
Total Time	0.0154924999999999 seconds

Subroutines — ordered by exclusive time
Calls	P	F	Exclusive Time	Inclusive Time	Subroutine
507	1	2	3.71ms	3.71ms	DBD::mysql::st::::_prepareDBD::mysql::st::_prepare(xsub)
1	1	2	2.98ms	2.98ms	DBD::mysql::db::::_loginDBD::mysql::db::_login(xsub)
1	1	2	156µs	156µs	DBD::mysql::::bootstrap DBD::mysql::bootstrap(xsub)
1	1	1	114µs	256µs	DBD::mysql::::_OdbcParse DBD::mysql::_OdbcParse
1	1	1	18µs	106µs	DBD::mysql::::driver DBD::mysql::driver
0	0	0	0s	0s	DBD::mysql::::AUTOLOAD DBD::mysql::AUTOLOAD
0	0	0	0s	0s	DBD::mysql::::BEGIN DBD::mysql::BEGIN
0	0	0	0s	0s	DBD::mysql::::CLONE DBD::mysql::CLONE
0	0	0	0s	0s	DBD::mysql::::_OdbcParseHost DBD::mysql::_OdbcParseHost
0	0	0	0s	0s	DBD::mysql::db::::ANSI2dbDBD::mysql::db::ANSI2db
0	0	0	0s	0s	DBD::mysql::db::::BEGINDBD::mysql::db::BEGIN
0	0	0	0s	0s	DBD::mysql::db::::_ListTablesDBD::mysql::db::_ListTables
0	0	0	0s	0s	DBD::mysql::db::::_SelectDBDBD::mysql::db::_SelectDB
0	0	0	0s	0s	DBD::mysql::db::::_versionDBD::mysql::db::_version
0	0	0	0s	0s	DBD::mysql::db::::adminDBD::mysql::db::admin
0	0	0	0s	0s	DBD::mysql::db::::column_infoDBD::mysql::db::column_info
0	0	0	0s	0s	DBD::mysql::db::::db2ANSIDBD::mysql::db::db2ANSI
0	0	0	0s	0s	DBD::mysql::db::::foreign_key_infoDBD::mysql::db::foreign_key_info
0	0	0	0s	0s	DBD::mysql::db::::get_infoDBD::mysql::db::get_info
0	0	0	0s	0s	DBD::mysql::db::::prepareDBD::mysql::db::prepare
0	0	0	0s	0s	DBD::mysql::db::::primary_key_infoDBD::mysql::db::primary_key_info
0	0	0	0s	0s	DBD::mysql::db::::table_infoDBD::mysql::db::table_info
0	0	0	0s	0s	DBD::mysql::dr::::BEGINDBD::mysql::dr::BEGIN
0	0	0	0s	0s	DBD::mysql::dr::::adminDBD::mysql::dr::admin
0	0	0	0s	0s	DBD::mysql::dr::::connectDBD::mysql::dr::connect
0	0	0	0s	0s	DBD::mysql::dr::::data_sourcesDBD::mysql::dr::data_sources
0	0	0	0s	0s	DBD::mysql::st::::BEGINDBD::mysql::st::BEGIN

Line	Stmts.	Exclusive Time	Avg.	Code
1				# -- cperl --
2
3				package DBD::mysql;
4	3	44µs	15µs	use strict; # spent 15µs making 1 call to strict::import
5	3	44µs	15µs	use vars qw(@ISA $VERSION $err $errstr $drh); # spent 82µs making 1 call to vars::import
6
7	3	17µs	6µs	use DBI ();
8	3	17µs	6µs	use DynaLoader();
9	3	526µs	175µs	use Carp ();
10	1	14µs	14µs	@ISA = qw(DynaLoader);
11
12	1	700ns	700ns	$VERSION = '4.008';
13
14	1	12µs	12µs	bootstrap DBD::mysql $VERSION; # spent 1.10ms making 1 call to DynaLoader::bootstrap
15
16
17	1	700ns	700ns	$err = 0; # holds error code for DBI::err
18	1	800ns	800ns	$errstr = ""; # holds error string for DBI::errstr
19	1	900ns	900ns	$drh = undef; # holds driver handle once initialised
20
21				# spent 106µs (18+88) within DBD::mysql::driver which was called # once (18µs+88µs) by DBI::install_driver at line 787 of /usr/lib/perl5/DBI.pm sub driver{
22	5	17µs	3µs	return $drh if $drh;
23				my($class, $attr) = @_;
24
25				$class .= "::dr";
26
27				# not a 'my' since we use it above to prevent multiple drivers
28				$drh = DBI::_new_drh($class, { 'Name' => 'mysql', # spent 88µs making 1 call to DBI::_new_drh
29				'Version' => $VERSION,
30				'Err' => \$DBD::mysql::err,
31				'Errstr' => \$DBD::mysql::errstr,
32				'Attribution' => 'DBD::mysql by Patrick Galbraith'
33				});
34
35				$drh;
36				}
37
38				sub CLONE {
39				undef $drh;
40				}
41
42				# spent 256µs (114+142) within DBD::mysql::_OdbcParse which was called # once (114µs+142µs) by DBD::mysql::dr::connect at line 123 sub _OdbcParse($$$) {
43	26	113µs	4µs	my($class, $dsn, $hash, $args) = @_;
44				my($var, $val);
45				if (!defined($dsn)) {
46				return;
47				}
48				while (length($dsn)) {
49	1	22µs	22µs	if ($dsn =~ /([^:;])[:;](.)/) { # spent 108µs making 2 calls to utf8::SWASHNEW, avg 54µs/call
50				$val = $1;
51				$dsn = $2;
52				} else {
53				$val = $dsn;
54				$dsn = '';
55				}
56	1	5µs	5µs	if ($val =~ /([^=])=(.)/) { # spent 34µs making 1 call to utf8::SWASHNEW
57				$var = $1;
58				$val = $2;
59				if ($var eq 'hostname' \|\| $var eq 'host') {
60				$hash->{'host'} = $val;
61				} elsif ($var eq 'db' \|\| $var eq 'dbname') {
62				$hash->{'database'} = $val;
63				} else {
64				$hash->{$var} = $val;
65				}
66				} else {
67				foreach $var (@$args) {
68				if (!defined($hash->{$var})) {
69				$hash->{$var} = $val;
70				last;
71				}
72				}
73				}
74				}
75				}
76
77				sub _OdbcParseHost ($$) {
78				my($class, $dsn) = @_;
79				my($hash) = {};
80				$class->_OdbcParse($dsn, $hash, ['host', 'port']);
81				($hash->{'host'}, $hash->{'port'});
82				}
83
84				sub AUTOLOAD {
85				my ($meth) = $DBD::mysql::AUTOLOAD;
86				my ($smeth) = $meth;
87				$smeth =~ s/(.*)\:\://;
88
89				my $val = constant($smeth, @_ ? $_[0] : 0);
90				if ($! == 0) { eval "sub $meth { $val }"; return $val; }
91
92				Carp::croak "$meth: Not defined";
93				}
94
95	1	300ns	300ns	1;
96
97
98				package DBD::mysql::dr; # ====== DRIVER ======
99	3	31µs	10µs	use strict; # spent 9µs making 1 call to strict::import
100	3	37µs	12µs	use DBI qw(:sql_types); # spent 582µs making 1 call to Exporter::import
101	3	559µs	186µs	use DBI::Const::GetInfoType; # spent 115µs making 1 call to Exporter::import
102
103				sub connect {
104	18	3.02ms	168µs	my($drh, $dsn, $username, $password, $attrhash) = @_;
105				my($port);
106				my($cWarn);
107				my $connect_ref= { 'Name' => $dsn };
108				my $dbi_imp_data;
109
110				# Avoid warnings for undefined values
111				$username \|\|= '';
112				$password \|\|= '';
113				$attrhash \|\|= {};
114
115				# create a 'blank' dbh
116				my($this, $privateAttrHash) = (undef, $attrhash);
117				$privateAttrHash = { %$privateAttrHash,
118				'Name' => $dsn,
119				'user' => $username,
120				'password' => $password
121				};
122
123				DBD::mysql->_OdbcParse($dsn, $privateAttrHash, # spent 256µs making 1 call to DBD::mysql::_OdbcParse
124				['database', 'host', 'port']);
125
126
127				if ($DBI::VERSION >= 1.49)
128				{
129				$dbi_imp_data = delete $attrhash->{dbi_imp_data};
130				$connect_ref->{'dbi_imp_data'} = $dbi_imp_data;
131				}
132
133				if (!defined($this = DBI::_new_dbh($drh, # spent 61µs making 1 call to DBI::_new_dbh
134				$connect_ref,
135				$privateAttrHash)))
136				{
137				return undef;
138				}
139
140				# Call msqlConnect func in mSQL.xs file
141				# and populate internal handle data.
142				DBD::mysql::db::_login($this, $dsn, $username, $password) # spent 2.98ms making 1 call to DBD::mysql::db::_login
143				or $this = undef;
144
145				if ($this && ($ENV{MOD_PERL} \|\| $ENV{GATEWAY_INTERFACE})) {
146				$this->{mysql_auto_reconnect} = 1;
147				}
148				$this;
149				}
150
151				sub data_sources {
152				my($self) = shift;
153				my($attributes) = shift;
154				my($host, $port, $user, $password) = ('', '', '', '');
155				if ($attributes) {
156				$host = $attributes->{host} \|\| '';
157				$port = $attributes->{port} \|\| '';
158				$user = $attributes->{user} \|\| '';
159				$password = $attributes->{password} \|\| '';
160				}
161				my(@dsn) = $self->func($host, $port, $user, $password, '_ListDBs');
162				my($i);
163				for ($i = 0; $i < @dsn; $i++) {
164				$dsn[$i] = "DBI:mysql:$dsn[$i]";
165				}
166				@dsn;
167				}
168
169				sub admin {
170				my($drh) = shift;
171				my($command) = shift;
172				my($dbname) = ($command eq 'createdb' \|\| $command eq 'dropdb') ?
173				shift : '';
174				my($host, $port) = DBD::mysql->_OdbcParseHost(shift(@_) \|\| '');
175				my($user) = shift \|\| '';
176				my($password) = shift \|\| '';
177
178				$drh->func(undef, $command,
179				$dbname \|\| '',
180				$host \|\| '',
181				$port \|\| '',
182				$user, $password, '_admin_internal');
183				}
184
185				package DBD::mysql::db; # ====== DATABASE ======
186	3	33µs	11µs	use strict; # spent 12µs making 1 call to strict::import
187	3	2.66ms	887µs	use DBI qw(:sql_types); # spent 447µs making 1 call to Exporter::import
188
189	1	5µs	5µs	%DBD::mysql::db::db2ANSI = ("INT" => "INTEGER",
190				"CHAR" => "CHAR",
191				"REAL" => "REAL",
192				"IDENT" => "DECIMAL"
193				);
194
195				### ANSI datatype mapping to mSQL datatypes
196	1	16µs	16µs	%DBD::mysql::db::ANSI2db = ("CHAR" => "CHAR",
197				"VARCHAR" => "CHAR",
198				"LONGVARCHAR" => "CHAR",
199				"NUMERIC" => "INTEGER",
200				"DECIMAL" => "INTEGER",
201				"BIT" => "INTEGER",
202				"TINYINT" => "INTEGER",
203				"SMALLINT" => "INTEGER",
204				"INTEGER" => "INTEGER",
205				"BIGINT" => "INTEGER",
206				"REAL" => "REAL",
207				"FLOAT" => "REAL",
208				"DOUBLE" => "REAL",
209				"BINARY" => "CHAR",
210				"VARBINARY" => "CHAR",
211				"LONGVARBINARY" => "CHAR",
212				"DATE" => "CHAR",
213				"TIME" => "CHAR",
214				"TIMESTAMP" => "CHAR"
215				);
216
217				sub prepare {
218	2028	8.23ms	4µs	my($dbh, $statement, $attribs)= @_;
219
220				# create a 'blank' dbh
221				my $sth = DBI::_new_sth($dbh, {'Statement' => $statement}); # spent 14.5ms making 507 calls to DBI::_new_sth, avg 29µs/call
222
223				# Populate internal handle data.
224				if (!DBD::mysql::st::_prepare($sth, $statement, $attribs)) { # spent 3.71ms making 507 calls to DBD::mysql::st::_prepare, avg 7µs/call
225				$sth = undef;
226				}
227
228				$sth;
229				}
230
231				sub db2ANSI {
232				my $self = shift;
233				my $type = shift;
234				return $DBD::mysql::db::db2ANSI{"$type"};
235				}
236
237				sub ANSI2db {
238				my $self = shift;
239				my $type = shift;
240				return $DBD::mysql::db::ANSI2db{"$type"};
241				}
242
243				sub admin {
244				my($dbh) = shift;
245				my($command) = shift;
246				my($dbname) = ($command eq 'createdb' \|\| $command eq 'dropdb') ?
247				shift : '';
248				$dbh->{'Driver'}->func($dbh, $command, $dbname, '', '', '',
249				'_admin_internal');
250				}
251
252				sub _SelectDB ($$) {
253				die "_SelectDB is removed from this module; use DBI->connect instead.";
254				}
255
256				sub table_info ($) {
257				my ($dbh, $catalog, $schema, $table, $type, $attr) = @_;
258				$dbh->{mysql_server_prepare}\|\|= 0;
259				my $mysql_server_prepare_save= $dbh->{mysql_server_prepare};
260				$dbh->{mysql_server_prepare}= 0;
261				my @names = qw(TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS);
262				my @rows;
263
264				my $sponge = DBI->connect("DBI:Sponge:", '','')
265				or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr");
266
267				# Return the list of catalogs
268				if (defined $catalog && $catalog eq "%" &&
269				(!defined($schema) \|\| $schema eq "") &&
270				(!defined($table) \|\| $table eq ""))
271				{
272				@rows = (); # Empty, because MySQL doesn't support catalogs (yet)
273				}
274				# Return the list of schemas
275				elsif (defined $schema && $schema eq "%" &&
276				(!defined($catalog) \|\| $catalog eq "") &&
277				(!defined($table) \|\| $table eq ""))
278				{
279				my $sth = $dbh->prepare("SHOW DATABASES")
280				or ($dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
281				return undef);
282
283				$sth->execute()
284				or ($dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
285				return DBI::set_err($dbh, $sth->err(), $sth->errstr()));
286
287				while (my $ref = $sth->fetchrow_arrayref())
288				{
289				push(@rows, [ undef, $ref->[0], undef, undef, undef ]);
290				}
291				}
292				# Return the list of table types
293				elsif (defined $type && $type eq "%" &&
294				(!defined($catalog) \|\| $catalog eq "") &&
295				(!defined($schema) \|\| $schema eq "") &&
296				(!defined($table) \|\| $table eq ""))
297				{
298				@rows = (
299				[ undef, undef, undef, "TABLE", undef ],
300				[ undef, undef, undef, "VIEW", undef ],
301				);
302				}
303				# Special case: a catalog other than undef, "", or "%"
304				elsif (defined $catalog && $catalog ne "" && $catalog ne "%")
305				{
306				@rows = (); # Nothing, because MySQL doesn't support catalogs yet.
307				}
308				# Uh oh, we actually have a meaty table_info call. Work is required!
309				else
310				{
311				my @schemas;
312				# If no table was specified, we want them all
313				$table \|\|= "%";
314
315				# If something was given for the schema, we need to expand it to
316				# a list of schemas, since it may be a wildcard.
317				if (defined $schema && $schema ne "")
318				{
319				my $sth = $dbh->prepare("SHOW DATABASES LIKE " .
320				$dbh->quote($schema))
321				or ($dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
322				return undef);
323				$sth->execute()
324				or ($dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
325				return DBI::set_err($dbh, $sth->err(), $sth->errstr()));
326
327				while (my $ref = $sth->fetchrow_arrayref())
328				{
329				push @schemas, $ref->[0];
330				}
331				}
332				# Otherwise we want the current database
333				else
334				{
335				push @schemas, $dbh->selectrow_array("SELECT DATABASE()");
336				}
337
338				# Figure out which table types are desired
339				my ($want_tables, $want_views);
340				if (defined $type && $type ne "")
341				{
342				$want_tables = ($type =~ m/table/i);
343				$want_views = ($type =~ m/view/i);
344				}
345				else
346				{
347				$want_tables = $want_views = 1;
348				}
349
350				for my $database (@schemas)
351				{
352				my $sth = $dbh->prepare("SHOW /!50002 FULL/ TABLES FROM " .
353				$dbh->quote_identifier($database) .
354				" LIKE " . $dbh->quote($table))
355				or ($dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
356				return undef);
357
358				$sth->execute() or
359				($dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
360				return DBI::set_err($dbh, $sth->err(), $sth->errstr()));
361
362				while (my $ref = $sth->fetchrow_arrayref())
363				{
364				my $type = (defined $ref->[1] &&
365				$ref->[1] =~ /view/i) ? 'VIEW' : 'TABLE';
366				next if $type eq 'TABLE' && not $want_tables;
367				next if $type eq 'VIEW' && not $want_views;
368				push @rows, [ undef, $database, $ref->[0], $type, undef ];
369				}
370				}
371				}
372
373				my $sth = $sponge->prepare("table_info",
374				{
375				rows => \@rows,
376				NUM_OF_FIELDS => scalar @names,
377				NAME => \@names,
378				})
379				or ($dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
380				return $dbh->DBI::set_err($sponge->err(), $sponge->errstr()));
381
382				$dbh->{mysql_server_prepare}= $mysql_server_prepare_save;
383				return $sth;
384				}
385
386				sub _ListTables {
387				my $dbh = shift;
388				if (!$DBD::mysql::QUIET) {
389				warn "_ListTables is deprecated, use \$dbh->tables()";
390				}
391				return map { $_ =~ s/.*\.//; $_ } $dbh->tables();
392				}
393
394
395				sub column_info {
396				my ($dbh, $catalog, $schema, $table, $column) = @_;
397				$dbh->{mysql_server_prepare}\|\|= 0;
398				my $mysql_server_prepare_save= $dbh->{mysql_server_prepare};
399				$dbh->{mysql_server_prepare}= 0;
400
401				# ODBC allows a NULL to mean all columns, so we'll accept undef
402				$column = '%' unless defined $column;
403
404				my $ER_NO_SUCH_TABLE= 1146;
405
406				my $table_id = $dbh->quote_identifier($catalog, $schema, $table);
407
408				my @names = qw(
409				TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME
410				DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS
411				NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF
412				SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH
413				ORDINAL_POSITION IS_NULLABLE CHAR_SET_CAT
414				CHAR_SET_SCHEM CHAR_SET_NAME COLLATION_CAT COLLATION_SCHEM COLLATION_NAME
415				UDT_CAT UDT_SCHEM UDT_NAME DOMAIN_CAT DOMAIN_SCHEM DOMAIN_NAME
416				SCOPE_CAT SCOPE_SCHEM SCOPE_NAME MAX_CARDINALITY
417				DTD_IDENTIFIER IS_SELF_REF
418				mysql_is_pri_key mysql_type_name mysql_values
419				mysql_is_auto_increment
420				);
421				my %col_info;
422
423				local $dbh->{FetchHashKeyName} = 'NAME_lc';
424				# only ignore ER_NO_SUCH_TABLE in internal_execute if issued from here
425				my $desc_sth = $dbh->prepare("DESCRIBE $table_id " . $dbh->quote($column));
426				my $desc = $dbh->selectall_arrayref($desc_sth, { Columns=>{} });
427
428				#return $desc_sth if $desc_sth->err();
429				if (my $err = $desc_sth->err())
430				{
431				# return the error, unless it is due to the table not
432				# existing per DBI spec
433				if ($err != $ER_NO_SUCH_TABLE)
434				{
435				$dbh->{mysql_server_prepare}= $mysql_server_prepare_save;
436				return undef;
437				}
438				$dbh->set_err(undef,undef);
439				$desc = [];
440				}
441
442				my $ordinal_pos = 0;
443				for my $row (@$desc)
444				{
445				my $type = $row->{type};
446				$type =~ m/^(\w+)(?:$(.?)$)?\s(.*)/;
447				my $basetype = lc($1);
448				my $typemod = $2;
449				my $attr = $3;
450
451				my $info = $col_info{ $row->{field} }= {
452				TABLE_CAT => $catalog,
453				TABLE_SCHEM => $schema,
454				TABLE_NAME => $table,
455				COLUMN_NAME => $row->{field},
456				NULLABLE => ($row->{null} eq 'YES') ? 1 : 0,
457				IS_NULLABLE => ($row->{null} eq 'YES') ? "YES" : "NO",
458				TYPE_NAME => uc($basetype),
459				COLUMN_DEF => $row->{default},
460				ORDINAL_POSITION => ++$ordinal_pos,
461				mysql_is_pri_key => ($row->{key} eq 'PRI'),
462				mysql_type_name => $row->{type},
463				mysql_is_auto_increment => ($row->{extra} =~ /auto_increment/i ? 1 : 0),
464				};
465				#
466				# This code won't deal with a pathalogical case where a value
467				# contains a single quote followed by a comma, and doesn't unescape
468				# any escaped values. But who would use those in an enum or set?
469				#
470				my @type_params= ($typemod && index($typemod,"'")>=0) ?
471				("$typemod," =~ /'(.*?)',/g) # assume all are quoted
472				: split /,/, $typemod\|\|''; # no quotes, plain list
473				s/''/'/g for @type_params; # undo doubling of quotes
474
475				my @type_attr= split / /, $attr\|\|'';
476
477				$info->{DATA_TYPE}= SQL_VARCHAR();
478				if ($basetype =~ /^(char\|varchar\|\wtext\|\wblob)/)
479				{
480				$info->{DATA_TYPE}= SQL_CHAR() if $basetype eq 'char';
481				if ($type_params[0])
482				{
483				$info->{COLUMN_SIZE} = $type_params[0];
484				}
485				else
486				{
487				$info->{COLUMN_SIZE} = 65535;
488				$info->{COLUMN_SIZE} = 255 if $basetype =~ /^tiny/;
489				$info->{COLUMN_SIZE} = 16777215 if $basetype =~ /^medium/;
490				$info->{COLUMN_SIZE} = 4294967295 if $basetype =~ /^long/;
491				}
492				}
493				elsif ($basetype =~ /^(binary\|varbinary)/)
494				{
495				$info->{COLUMN_SIZE} = $type_params[0];
496				# SQL_BINARY & SQL_VARBINARY are tempting here but don't match the
497				# semantics for mysql (not hex). SQL_CHAR & SQL_VARCHAR are correct here.
498				$info->{DATA_TYPE} = ($basetype eq 'binary') ? SQL_CHAR() : SQL_VARCHAR();
499				}
500				elsif ($basetype =~ /^(enum\|set)/)
501				{
502				if ($basetype eq 'set')
503				{
504				$info->{COLUMN_SIZE} = length(join ",", @type_params);
505				}
506				else
507				{
508				my $max_len = 0;
509				length($_) > $max_len and $max_len = length($_) for @type_params;
510				$info->{COLUMN_SIZE} = $max_len;
511				}
512				$info->{"mysql_values"} = \@type_params;
513				}
514				elsif ($basetype =~ /int/)
515				{
516				# big/medium/small/tiny etc + unsigned?
517				$info->{DATA_TYPE} = SQL_INTEGER();
518				$info->{NUM_PREC_RADIX} = 10;
519				$info->{COLUMN_SIZE} = $type_params[0];
520				}
521				elsif ($basetype =~ /^decimal/)
522				{
523				$info->{DATA_TYPE} = SQL_DECIMAL();
524				$info->{NUM_PREC_RADIX} = 10;
525				$info->{COLUMN_SIZE} = $type_params[0];
526				$info->{DECIMAL_DIGITS} = $type_params[1];
527				}
528				elsif ($basetype =~ /^(float\|double)/)
529				{
530				$info->{DATA_TYPE} = ($basetype eq 'float') ? SQL_FLOAT() : SQL_DOUBLE();
531				$info->{NUM_PREC_RADIX} = 2;
532				$info->{COLUMN_SIZE} = ($basetype eq 'float') ? 32 : 64;
533				}
534				elsif ($basetype =~ /date\|time/)
535				{
536				# date/datetime/time/timestamp
537				if ($basetype eq 'time' or $basetype eq 'date')
538				{
539				#$info->{DATA_TYPE} = ($basetype eq 'time') ? SQL_TYPE_TIME() : SQL_TYPE_DATE();
540				$info->{DATA_TYPE} = ($basetype eq 'time') ? SQL_TIME() : SQL_DATE();
541				$info->{COLUMN_SIZE} = ($basetype eq 'time') ? 8 : 10;
542				}
543				else
544				{
545				# datetime/timestamp
546				#$info->{DATA_TYPE} = SQL_TYPE_TIMESTAMP();
547				$info->{DATA_TYPE} = SQL_TIMESTAMP();
548				$info->{SQL_DATA_TYPE} = SQL_DATETIME();
549				$info->{SQL_DATETIME_SUB} = $info->{DATA_TYPE} - ($info->{SQL_DATA_TYPE} * 10);
550				$info->{COLUMN_SIZE} = ($basetype eq 'datetime') ? 19 : $type_params[0] \|\| 14;
551				}
552				$info->{DECIMAL_DIGITS}= 0; # no fractional seconds
553				}
554				elsif ($basetype eq 'year')
555				{
556				# no close standard so treat as int
557				$info->{DATA_TYPE} = SQL_INTEGER();
558				$info->{NUM_PREC_RADIX} = 10;
559				$info->{COLUMN_SIZE} = 4;
560				}
561				else
562				{
563				Carp::carp("column_info: unrecognized column type '$basetype' of $table_id.$row->{field} treated as varchar");
564				}
565				$info->{SQL_DATA_TYPE} \|\|= $info->{DATA_TYPE};
566				#warn Dumper($info);
567				}
568
569				my $sponge = DBI->connect("DBI:Sponge:", '','')
570				or ( $dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
571				return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr"));
572
573				my $sth = $sponge->prepare("column_info $table", {
574				rows => [ map { [ @{$_}{@names} ] } values %col_info ],
575				NUM_OF_FIELDS => scalar @names,
576				NAME => \@names,
577				}) or
578				return ($dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
579				$dbh->DBI::set_err($sponge->err(), $sponge->errstr()));
580
581				$dbh->{mysql_server_prepare}= $mysql_server_prepare_save;
582				return $sth;
583				}
584
585
586				sub primary_key_info {
587				my ($dbh, $catalog, $schema, $table) = @_;
588				$dbh->{mysql_server_prepare}\|\|= 0;
589				my $mysql_server_prepare_save= $dbh->{mysql_server_prepare};
590
591				my $table_id = $dbh->quote_identifier($catalog, $schema, $table);
592
593				my @names = qw(
594				TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
595				);
596				my %col_info;
597
598				local $dbh->{FetchHashKeyName} = 'NAME_lc';
599				my $desc_sth = $dbh->prepare("SHOW KEYS FROM $table_id");
600				my $desc= $dbh->selectall_arrayref($desc_sth, { Columns=>{} });
601				my $ordinal_pos = 0;
602				for my $row (grep { $_->{key_name} eq 'PRIMARY'} @$desc)
603				{
604				$col_info{ $row->{column_name} }= {
605				TABLE_CAT => $catalog,
606				TABLE_SCHEM => $schema,
607				TABLE_NAME => $table,
608				COLUMN_NAME => $row->{column_name},
609				KEY_SEQ => $row->{seq_in_index},
610				PK_NAME => $row->{key_name},
611				};
612				}
613
614				my $sponge = DBI->connect("DBI:Sponge:", '','')
615				or
616				($dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
617				return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr"));
618
619				my $sth= $sponge->prepare("primary_key_info $table", {
620				rows => [ map { [ @{$_}{@names} ] } values %col_info ],
621				NUM_OF_FIELDS => scalar @names,
622				NAME => \@names,
623				}) or
624				($dbh->{mysql_server_prepare}= $mysql_server_prepare_save &&
625				return $dbh->DBI::set_err($sponge->err(), $sponge->errstr()));
626
627				$dbh->{mysql_server_prepare}= $mysql_server_prepare_save;
628
629				return $sth;
630				}
631
632
633				sub foreign_key_info {
634				my ($dbh,
635				$pk_catalog, $pk_schema, $pk_table,
636				$fk_catalog, $fk_schema, $fk_table,
637				) = @_;
638
639				# INFORMATION_SCHEMA.KEY_COLUMN_USAGE was added in 5.0.6
640				my ($maj, $min, $point) = _version($dbh);
641				return if $maj < 5 \|\| ($maj == 5 && $point < 6);
642
643				my $sql = <<'EOF';
644				SELECT NULL AS PKTABLE_CAT,
645				A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM,
646				A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,
647				A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,
648				A.TABLE_CATALOG AS FKTABLE_CAT,
649				A.TABLE_SCHEMA AS FKTABLE_SCHEM,
650				A.TABLE_NAME AS FKTABLE_NAME,
651				A.COLUMN_NAME AS FKCOLUMN_NAME,
652				A.ORDINAL_POSITION AS KEY_SEQ,
653				NULL AS UPDATE_RULE,
654				NULL AS DELETE_RULE,
655				A.CONSTRAINT_NAME AS FK_NAME,
656				NULL AS PK_NAME,
657				NULL AS DEFERABILITY,
658				NULL AS UNIQUE_OR_PRIMARY
659				FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A,
660				INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
661				WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME
662				AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL
663				EOF
664
665				my @where;
666				my @bind;
667
668				# catalogs are not yet supported by MySQL
669
670				# if (defined $pk_catalog) {
671				# push @where, 'A.REFERENCED_TABLE_CATALOG = ?';
672				# push @bind, $pk_catalog;
673				# }
674
675				if (defined $pk_schema) {
676				push @where, 'A.REFERENCED_TABLE_SCHEMA = ?';
677				push @bind, $pk_schema;
678				}
679
680				if (defined $pk_table) {
681				push @where, 'A.REFERENCED_TABLE_NAME = ?';
682				push @bind, $pk_table;
683				}
684
685				# if (defined $fk_catalog) {
686				# push @where, 'A.TABLE_CATALOG = ?';
687				# push @bind, $fk_schema;
688				# }
689
690				if (defined $fk_schema) {
691				push @where, 'A.TABLE_SCHEMA = ?';
692				push @bind, $fk_schema;
693				}
694
695				if (defined $fk_table) {
696				push @where, 'A.TABLE_NAME = ?';
697				push @bind, $fk_table;
698				}
699
700				if (@where) {
701				$sql .= ' AND ';
702				$sql .= join ' AND ', @where;
703				}
704				$sql .= " ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION";
705
706				local $dbh->{FetchHashKeyName} = 'NAME_uc';
707				my $sth = $dbh->prepare($sql);
708				$sth->execute(@bind);
709
710				return $sth;
711				}
712
713
714				sub _version {
715				my $dbh = shift;
716
717				return
718				$dbh->get_info($DBI::Const::GetInfoType::GetInfoType{SQL_DBMS_VER})
719				=~ /(\d+)\.(\d+)\.(\d+)/;
720				}
721
722
723				####################
724				# get_info()
725				# Generated by DBI::DBD::Metadata
726
727				sub get_info {
728				my($dbh, $info_type) = @_;
729				require DBD::mysql::GetInfo;
730				my $v = $DBD::mysql::GetInfo::info{int($info_type)};
731				$v = $v->($dbh) if ref $v eq 'CODE';
732				return $v;
733				}
734
735
736
737				package DBD::mysql::st; # ====== STATEMENT ======
738	3	46µs	15µs	use strict; # spent 12µs making 1 call to strict::import
739
740	1	16µs	16µs	1;
741
742				__END__
743
744				=pod
745
746				=head1 NAME
747
748				DBD::mysql - MySQL driver for the Perl5 Database Interface (DBI)
749
750				=head1 SYNOPSIS
751
752				use DBI;
753
754				$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
755
756				$dbh = DBI->connect($dsn, $user, $password);
757
758
759				$drh = DBI->install_driver("mysql");
760				@databases = DBI->data_sources("mysql");
761				or
762				@databases = DBI->data_sources("mysql",
763				{"host" => $host, "port" => $port, "user" => $user, password => $pass});
764
765				$sth = $dbh->prepare("SELECT * FROM foo WHERE bla");
766				or
767				$sth = $dbh->prepare("LISTFIELDS $table");
768				or
769				$sth = $dbh->prepare("LISTINDEX $table $index");
770				$sth->execute;
771				$numRows = $sth->rows;
772				$numFields = $sth->{'NUM_OF_FIELDS'};
773				$sth->finish;
774
775				$rc = $drh->func('createdb', $database, $host, $user, $password, 'admin');
776				$rc = $drh->func('dropdb', $database, $host, $user, $password, 'admin');
777				$rc = $drh->func('shutdown', $host, $user, $password, 'admin');
778				$rc = $drh->func('reload', $host, $user, $password, 'admin');
779
780				$rc = $dbh->func('createdb', $database, 'admin');
781				$rc = $dbh->func('dropdb', $database, 'admin');
782				$rc = $dbh->func('shutdown', 'admin');
783				$rc = $dbh->func('reload', 'admin');
784
785
786				=head1 EXAMPLE
787
788				#!/usr/bin/perl
789
790				use strict;
791				use DBI();
792
793				# Connect to the database.
794				my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost",
795				"joe", "joe's password",
796				{'RaiseError' => 1});
797
798				# Drop table 'foo'. This may fail, if 'foo' doesn't exist.
799				# Thus we put an eval around it.
800				eval { $dbh->do("DROP TABLE foo") };
801				print "Dropping foo failed: $@\n" if $@;
802
803				# Create a new table 'foo'. This must not fail, thus we don't
804				# catch errors.
805				$dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))");
806
807				# INSERT some data into 'foo'. We are using $dbh->quote() for
808				# quoting the name.
809				$dbh->do("INSERT INTO foo VALUES (1, " . $dbh->quote("Tim") . ")");
810
811				# Same thing, but using placeholders
812				$dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen");
813
814				# Now retrieve data from the table.
815				my $sth = $dbh->prepare("SELECT * FROM foo");
816				$sth->execute();
817				while (my $ref = $sth->fetchrow_hashref()) {
818				print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n";
819				}
820				$sth->finish();
821
822				# Disconnect from the database.
823				$dbh->disconnect();
824
825
826				=head1 DESCRIPTION
827
828				B<DBD::mysql> is the Perl5 Database Interface driver for the MySQL
829				database. In other words: DBD::mysql is an interface between the Perl
830				programming language and the MySQL programming API that comes with
831				the MySQL relational database management system. Most functions
832				provided by this programming API are supported. Some rarely used
833				functions are missing, mainly because noone ever requested
834				them. :-)
835
836				In what follows we first discuss the use of DBD::mysql,
837				because this is what you will need the most. For installation, see the
838				sections on L<INSTALLATION>, and L<WIN32 INSTALLATION>
839				below. See L<EXAMPLE> for a simple example above.
840
841				From perl you activate the interface with the statement
842
843				use DBI;
844
845				After that you can connect to multiple MySQL database servers
846				and send multiple queries to any of them via a simple object oriented
847				interface. Two types of objects are available: database handles and
848				statement handles. Perl returns a database handle to the connect
849				method like so:
850
851				$dbh = DBI->connect("DBI:mysql:database=$db;host=$host",
852				$user, $password, {RaiseError => 1});
853
854				Once you have connected to a database, you can can execute SQL
855				statements with:
856
857				my $query = sprintf("INSERT INTO foo VALUES (%d, %s)",
858				$number, $dbh->quote("name"));
859				$dbh->do($query);
860
861				See L<DBI(3)> for details on the quote and do methods. An alternative
862				approach is
863
864				$dbh->do("INSERT INTO foo VALUES (?, ?)", undef,
865				$number, $name);
866
867				in which case the quote method is executed automatically. See also
868				the bind_param method in L<DBI(3)>. See L<DATABASE HANDLES> below
869				for more details on database handles.
870
871				If you want to retrieve results, you need to create a so-called
872				statement handle with:
873
874				$sth = $dbh->prepare("SELECT * FROM $table");
875				$sth->execute();
876
877				This statement handle can be used for multiple things. First of all
878				you can retreive a row of data:
879
880				my $row = $sth->fetchrow_hashref();
881
882				If your table has columns ID and NAME, then $row will be hash ref with
883				keys ID and NAME. See L<STATEMENT HANDLES> below for more details on
884				statement handles.
885
886				But now for a more formal approach:
887
888
889				=head2 Class Methods
890
891				=over
892
893				=item B<connect>
894
895				use DBI;
896
897				$dsn = "DBI:mysql:$database";
898				$dsn = "DBI:mysql:database=$database;host=$hostname";
899				$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
900
901				$dbh = DBI->connect($dsn, $user, $password);
902
903				A C<database> must always be specified.
904
905				=over
906
907				=item host
908
909				=item port
910
911				The hostname, if not specified or specified as '' or 'localhost', will
912				default to a MySQL server running on the local machine using the default for
913				the UNIX socket. To connect to a MySQL server on the local machine via TCP,
914				you must specify the loopback IP address (127.0.0.1) as the host.
915
916				Should the MySQL server be running on a non-standard port number,
917				you may explicitly state the port number to connect to in the C<hostname>
918				argument, by concatenating the I<hostname> and I<port number> together
919				separated by a colon ( C<:> ) character or by using the C<port> argument.
920
921				To connect to a MySQL server on localhost using TCP/IP, you must specify the
922				hostname as 127.0.0.1 (with the optional port).
923
924				=item mysql_client_found_rows
925
926				Enables (TRUE value) or disables (FALSE value) the flag CLIENT_FOUND_ROWS
927				while connecting to the MySQL server. This has a somewhat funny effect:
928				Without mysql_client_found_rows, if you perform a query like
929
930				UPDATE $table SET id = 1 WHERE id = 1
931
932				then the MySQL engine will always return 0, because no rows have changed.
933				With mysql_client_found_rows however, it will return the number of rows
934				that have an id 1, as some people are expecting. (At least for compatibility
935				to other engines.)
936
937				=item mysql_compression
938
939				As of MySQL 3.22.3, a new feature is supported: If your DSN contains
940				the option "mysql_compression=1", then the communication between client
941				and server will be compressed.
942
943				=item mysql_connect_timeout
944
945				If your DSN contains the option "mysql_connect_timeout=##", the connect
946				request to the server will timeout if it has not been successful after
947				the given number of seconds.
948
949				=item mysql_read_default_file
950
951				=item mysql_read_default_group
952
953				These options can be used to read a config file like /etc/my.cnf or
954				~/.my.cnf. By default MySQL's C client library doesn't use any config
955				files unlike the client programs (mysql, mysqladmin, ...) that do, but
956				outside of the C client library. Thus you need to explicitly request
957				reading a config file, as in
958
959				$dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf";
960				$dbh = DBI->connect($dsn, $user, $password)
961
962				The option mysql_read_default_group can be used to specify the default
963				group in the config file: Usually this is the I<client> group, but
964				see the following example:
965
966				[client]
967				host=localhost
968
969				[perl]
970				host=perlhost
971
972				(Note the order of the entries! The example won't work, if you reverse
973				the [client] and [perl] sections!)
974
975				If you read this config file, then you'll be typically connected to
976				I<localhost>. However, by using
977
978				$dsn = "DBI:mysql:test;mysql_read_default_group=perl;"
979				. "mysql_read_default_file=/home/joe/my.cnf";
980				$dbh = DBI->connect($dsn, $user, $password);
981
982				you'll be connected to I<perlhost>. Note that if you specify a
983				default group and do not specify a file, then the default config
984				files will all be read. See the documentation of
985				the C function mysql_options() for details.
986
987				=item mysql_socket
988
989				As of MySQL 3.21.15, it is possible to choose the Unix socket that is
990				used for connecting to the server. This is done, for example, with
991
992				mysql_socket=/dev/mysql
993
994				Usually there's no need for this option, unless you are using another
995				location for the socket than that built into the client.
996
997				=item mysql_ssl
998
999				A true value turns on the CLIENT_SSL flag when connecting to the MySQL
1000				database:
1001
1002				mysql_ssl=1
1003
1004				This means that your communication with the server will be encrypted.
1005
1006				If you turn mysql_ssl on, you might also wish to use the following
1007				flags:
1008
1009				=item mysql_ssl_client_key
1010
1011				=item mysql_ssl_client_cert
1012
1013				=item mysql_ssl_ca_file
1014
1015				=item mysql_ssl_ca_path
1016
1017				=item mysql_ssl_cipher
1018
1019				These are used to specify the respective parameters of a call
1020				to mysql_ssl_set, if mysql_ssl is turned on.
1021
1022
1023				=item mysql_local_infile
1024
1025				As of MySQL 3.23.49, the LOCAL capability for LOAD DATA may be disabled
1026				in the MySQL client library by default. If your DSN contains the option
1027				"mysql_local_infile=1", LOAD DATA LOCAL will be enabled. (However,
1028				this option is ineffective if the server has also been configured to
1029				disallow LOCAL.)
1030
1031				=item mysql_multi_statements
1032
1033				As of MySQL 4.1, support for multiple statements seperated by a semicolon
1034				(;) may be enabled by using this option. Enabling this option may cause
1035				problems if server-side prepared statements are also enabled.
1036
1037				=item Prepared statement support (server side prepare)
1038
1039				As of 3.0002_1, server side prepare statements were on by default (if your
1040				server was >= 4.1.3). As of 3.0009, they were off by default again due to
1041				issues with the prepared statement API (all other mysql connectors are
1042				set this way until C API issues are resolved). The requirement to use
1043				prepared statements still remains that you have a server >= 4.1.3
1044
1045				To use server side prepared statements, all you need to do is set the variable
1046				mysql_server_prepare in the connect:
1047
1048				$dbh = DBI->connect(
1049				"DBI:mysql:database=test;host=localhost;mysql_server_prepare=1",
1050				"",
1051				"",
1052				{ RaiseError => 1, AutoCommit => 1 }
1053				);
1054
1055				* Note: delimiter for this param is ';'
1056
1057				There are many benefits to using server side prepare statements, mostly if you are
1058				performing many inserts because of that fact that a single statement is prepared
1059				to accept multiple insert values.
1060
1061				To make sure that the 'make test' step tests whether server prepare works, you just
1062				need to export the env variable MYSQL_SERVER_PREPARE:
1063
1064				export MYSQL_SERVER_PREPARE=1
1065
1066
1067				=item mysql_embedded_options
1068
1069				The option <mysql_embedded_options> can be used to pass 'command-line'
1070				options to embedded server.
1071
1072				Example:
1073
1074				use DBI;
1075				$testdsn="DBI:mysqlEmb:database=test;mysql_embedded_options=--help,--verbose";
1076				$dbh = DBI->connect($testdsn,"a","b");
1077
1078				This would cause the command line help to the embedded MySQL server library
1079				to be printed.
1080
1081
1082				=item mysql_embedded_groups
1083
1084				The option <mysql_embedded_groups> can be used to specify the groups in the
1085				config file(I<my.cnf>) which will be used to get options for embedded server.
1086				If not specified [server] and [embedded] groups will be used.
1087
1088				Example:
1089
1090				$testdsn="DBI:mysqlEmb:database=test;mysql_embedded_groups=embedded_server,common";
1091
1092
1093				=back
1094
1095				=back
1096
1097
1098				=head2 Private MetaData Methods
1099
1100				=over
1101
1102				=item B<ListDBs>
1103
1104				my $drh = DBI->install_driver("mysql");
1105				@dbs = $drh->func("$hostname:$port", '_ListDBs');
1106				@dbs = $drh->func($hostname, $port, '_ListDBs');
1107				@dbs = $dbh->func('_ListDBs');
1108
1109				Returns a list of all databases managed by the MySQL server
1110				running on C<$hostname>, port C<$port>. This is a legacy
1111				method. Instead, you should use the portable method
1112
1113				@dbs = DBI->data_sources("mysql");
1114
1115				=back
1116
1117
1118				=head2 Server Administration
1119
1120				=over
1121
1122				=item admin
1123
1124				$rc = $drh->func("createdb", $dbname, [host, user, password,], 'admin');
1125				$rc = $drh->func("dropdb", $dbname, [host, user, password,], 'admin');
1126				$rc = $drh->func("shutdown", [host, user, password,], 'admin');
1127				$rc = $drh->func("reload", [host, user, password,], 'admin');
1128
1129				or
1130
1131				$rc = $dbh->func("createdb", $dbname, 'admin');
1132				$rc = $dbh->func("dropdb", $dbname, 'admin');
1133				$rc = $dbh->func("shutdown", 'admin');
1134				$rc = $dbh->func("reload", 'admin');
1135
1136				For server administration you need a server connection. For obtaining
1137				this connection you have two options: Either use a driver handle (drh)
1138				and supply the appropriate arguments (host, defaults localhost, user,
1139				defaults to '' and password, defaults to ''). A driver handle can be
1140				obtained with
1141
1142				$drh = DBI->install_driver('mysql');
1143
1144				Otherwise reuse the existing connection of a database handle (dbh).
1145
1146				There's only one function available for administrative purposes, comparable
1147				to the m(y)sqladmin programs. The command being execute depends on the
1148				first argument:
1149
1150				=over
1151
1152				=item createdb
1153
1154				Creates the database $dbname. Equivalent to "m(y)sqladmin create $dbname".
1155
1156				=item dropdb
1157
1158				Drops the database $dbname. Equivalent to "m(y)sqladmin drop $dbname".
1159
1160				It should be noted that database deletion is
1161				I<not prompted for> in any way. Nor is it undo-able from DBI.
1162
1163				Once you issue the dropDB() method, the database will be gone!
1164
1165				These method should be used at your own risk.
1166
1167				=item shutdown
1168
1169				Silently shuts down the database engine. (Without prompting!)
1170				Equivalent to "m(y)sqladmin shutdown".
1171
1172				=item reload
1173
1174				Reloads the servers configuration files and/or tables. This can be particularly
1175				important if you modify access privileges or create new users.
1176
1177				=back
1178
1179				=back
1180
1181
1182				=head1 DATABASE HANDLES
1183
1184				The DBD::mysql driver supports the following attributes of database
1185				handles (read only):
1186
1187				$errno = $dbh->{'mysql_errno'};
1188				$error = $dbh->{'mysql_error'};
1189				$info = $dbh->{'mysql_hostinfo'};
1190				$info = $dbh->{'mysql_info'};
1191				$insertid = $dbh->{'mysql_insertid'};
1192				$info = $dbh->{'mysql_protoinfo'};
1193				$info = $dbh->{'mysql_serverinfo'};
1194				$info = $dbh->{'mysql_stat'};
1195				$threadId = $dbh->{'mysql_thread_id'};
1196
1197				These correspond to mysql_errno(), mysql_error(), mysql_get_host_info(),
1198				mysql_info(), mysql_insert_id(), mysql_get_proto_info(),
1199				mysql_get_server_info(), mysql_stat() and mysql_thread_id(),
1200				respectively.
1201
1202
1203				$info_hashref = $dhb->{mysql_dbd_stats}
1204
1205				DBD::mysql keeps track of some statistics in the mysql_dbd_stats attribute.
1206				The following stats are being maintained:
1207
1208				=over
1209
1210				=item auto_reconnects_ok
1211
1212				The number of times that DBD::mysql successfully reconnected to the mysql
1213				server.
1214
1215				=item auto_reconnects_failed
1216
1217				The number of times that DBD::mysql tried to reconnect to mysql but failed.
1218
1219				=back
1220
1221				The DBD::mysql driver also supports the following attribute(s) of database
1222				handles (read/write):
1223
1224				$bool_value = $dbh->{mysql_auto_reconnect};
1225				$dbh->{mysql_auto_reconnect} = $AutoReconnect ? 1 : 0;
1226
1227
1228				=item mysql_auto_reconnect
1229
1230				This attribute determines whether DBD::mysql will automatically reconnect
1231				to mysql if the connection be lost. This feature defaults to off; however,
1232				if either the GATEWAY_INTERFACE or MOD_PERL envionment variable is set,
1233				DBD::mysql will turn mysql_auto_reconnect on. Setting mysql_auto_reconnect
1234				to on is not advised if 'lock tables' is used because if DBD::mysql reconnect
1235				to mysql all table locks will be lost. This attribute is ignored when
1236				AutoCommit is turned off, and when AutoCommit is turned off, DBD::mysql will
1237				not automatically reconnect to the server.
1238
1239				=item mysql_use_result
1240
1241				This attribute forces the driver to use mysql_use_result rather than
1242				mysql_store_result. The former is faster and less memory consuming, but
1243				tends to block other processes. (That's why mysql_store_result is the
1244				default.)
1245
1246				It is possible to set default value of the C<mysql_use_result> attribute
1247				for $dbh using several ways:
1248
1249				- through DSN
1250
1251				$dbh= DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
1252
1253				- after creation of database handle
1254
1255				$dbh->{'mysql_use_result'}=0; #disable
1256				$dbh->{'mysql_use_result'}=1; #enable
1257
1258				It is possible to set/unset the C<mysql_use_result> attribute after
1259				creation of statement handle. See below.
1260
1261				=item mysql_enable_utf8
1262
1263				This attribute determines whether DBD::mysql should assume strings
1264				stored in the database are utf8. This feature defaults to off.
1265
1266				When set, a data retrieved from a textual column type (char, varchar,
1267				etc) will have the UTF-8 flag turned on if necessary. This enables
1268				character semantics on that string. You will also need to ensure that
1269				your database / table / column is configured to use UTF8. See Chapter
1270				10 of the mysql manual for details.
1271
1272				Additionally, turning on this flag tells MySQL that incoming data should
1273				be treated as UTF-8. This will only take effect if used as part of the
1274				call to connect(). If you turn the flag on after connecting, you will
1275				need to issue the command C<SET NAMES utf8> to get the same effect.
1276
1277				This option is experimental and may change in future versions.
1278
1279				=head1 STATEMENT HANDLES
1280
1281				The statement handles of DBD::mysql support a number
1282				of attributes. You access these by using, for example,
1283
1284				my $numFields = $sth->{'NUM_OF_FIELDS'};
1285
1286				Note, that most attributes are valid only after a successfull I<execute>.
1287				An C<undef> value will returned in that case. The most important exception
1288				is the C<mysql_use_result> attribute: This forces the driver to use
1289				mysql_use_result rather than mysql_store_result. The former is faster
1290				and less memory consuming, but tends to block other processes. (That's why
1291				mysql_store_result is the default.)
1292
1293				To set the C<mysql_use_result> attribute, use either of the following:
1294
1295				my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1});
1296
1297				or
1298
1299				my $sth = $dbh->prepare("QUERY");
1300				$sth->{"mysql_use_result"} = 1;
1301
1302				Column dependent attributes, for example I<NAME>, the column names,
1303				are returned as a reference to an array. The array indices are
1304				corresponding to the indices of the arrays returned by I<fetchrow>
1305				and similar methods. For example the following code will print a
1306				header of table names together with all rows:
1307
1308				my $sth = $dbh->prepare("SELECT * FROM $table");
1309				if (!$sth) {
1310				die "Error:" . $dbh->errstr . "\n";
1311				}
1312				if (!$sth->execute) {
1313				die "Error:" . $sth->errstr . "\n";
1314				}
1315				my $names = $sth->{'NAME'};
1316				my $numFields = $sth->{'NUM_OF_FIELDS'};
1317				for (my $i = 0; $i < $numFields; $i++) {
1318				printf("%s%s", $i ? "," : "", $$names[$i]);
1319				}
1320				print "\n";
1321				while (my $ref = $sth->fetchrow_arrayref) {
1322				for (my $i = 0; $i < $numFields; $i++) {
1323				printf("%s%s", $i ? "," : "", $$ref[$i]);
1324				}
1325				print "\n";
1326				}
1327
1328				For portable applications you should restrict yourself to attributes with
1329				capitalized or mixed case names. Lower case attribute names are private
1330				to DBD::mysql. The attribute list includes:
1331
1332				=over
1333
1334				=item ChopBlanks
1335
1336				this attribute determines whether a I<fetchrow> will chop preceding
1337				and trailing blanks off the column values. Chopping blanks does not
1338				have impact on the I<max_length> attribute.
1339
1340				=item mysql_insertid
1341
1342				MySQL has the ability to choose unique key values automatically. If this
1343				happened, the new ID will be stored in this attribute. An alternative
1344				way for accessing this attribute is via $dbh->{'mysql_insertid'}.
1345				(Note we are using the $dbh in this case!)
1346
1347				=item mysql_is_blob
1348
1349				Reference to an array of boolean values; TRUE indicates, that the
1350				respective column is a blob. This attribute is valid for MySQL only.
1351
1352				=item mysql_is_key
1353
1354				Reference to an array of boolean values; TRUE indicates, that the
1355				respective column is a key. This is valid for MySQL only.
1356
1357				=item mysql_is_num
1358
1359				Reference to an array of boolean values; TRUE indicates, that the
1360				respective column contains numeric values.
1361
1362				=item mysql_is_pri_key
1363
1364				Reference to an array of boolean values; TRUE indicates, that the
1365				respective column is a primary key.
1366
1367				=item mysql_is_auto_increment
1368
1369				Reference to an array of boolean values; TRUE indicates that the
1370				respective column is an AUTO_INCREMENT column. This is only valid
1371				for MySQL.
1372
1373				=item mysql_length
1374
1375				=item mysql_max_length
1376
1377				A reference to an array of maximum column sizes. The I<max_length> is
1378				the maximum physically present in the result table, I<length> gives
1379				the theoretically possible maximum. I<max_length> is valid for MySQL
1380				only.
1381
1382				=item NAME
1383
1384				A reference to an array of column names.
1385
1386				=item NULLABLE
1387
1388				A reference to an array of boolean values; TRUE indicates that this column
1389				may contain NULL's.
1390
1391				=item NUM_OF_FIELDS
1392
1393				Number of fields returned by a I<SELECT> or I<LISTFIELDS> statement.
1394				You may use this for checking whether a statement returned a result:
1395				A zero value indicates a non-SELECT statement like I<INSERT>,
1396				I<DELETE> or I<UPDATE>.
1397
1398				=item mysql_table
1399
1400				A reference to an array of table names, useful in a I<JOIN> result.
1401
1402				=item TYPE
1403
1404				A reference to an array of column types. The engine's native column
1405				types are mapped to portable types like DBI::SQL_INTEGER() or
1406				DBI::SQL_VARCHAR(), as good as possible. Not all native types have
1407				a meaningfull equivalent, for example DBD::mysql::FIELD_TYPE_INTERVAL
1408				is mapped to DBI::SQL_VARCHAR().
1409				If you need the native column types, use I<mysql_type>. See below.
1410
1411				=item mysql_type
1412
1413				A reference to an array of MySQL's native column types, for example
1414				DBD::mysql::FIELD_TYPE_SHORT() or DBD::mysql::FIELD_TYPE_STRING().
1415				Use the I<TYPE> attribute, if you want portable types like
1416				DBI::SQL_SMALLINT() or DBI::SQL_VARCHAR().
1417
1418				=item mysql_type_name
1419
1420				Similar to mysql, but type names and not numbers are returned.
1421				Whenever possible, the ANSI SQL name is preferred.
1422
1423				=item mysql_warning_count
1424
1425				The number of warnings generated during execution of the SQL statement.
1426
1427				=back
1428
1429				=head1 TRANSACTION SUPPORT
1430
1431				Beginning with DBD::mysql 2.0416, transactions are supported.
1432				The transaction support works as follows:
1433
1434				=over
1435
1436				=item *
1437
1438				By default AutoCommit mode is on, following the DBI specifications.
1439
1440				=item *
1441
1442				If you execute
1443
1444				$dbh->{'AutoCommit'} = 0;
1445
1446				or
1447
1448				$dbh->{'AutoCommit'} = 1;
1449
1450				then the driver will set the MySQL server variable autocommit to 0 or
1451				1, respectively. Switching from 0 to 1 will also issue a COMMIT,
1452				following the DBI specifications.
1453
1454				=item *
1455
1456				The methods
1457
1458				$dbh->rollback();
1459				$dbh->commit();
1460
1461				will issue the commands COMMIT and ROLLBACK, respectively. A
1462				ROLLBACK will also be issued if AutoCommit mode is off and the
1463				database handles DESTROY method is called. Again, this is following
1464				the DBI specifications.
1465
1466				=back
1467
1468				Given the above, you should note the following:
1469
1470				=over
1471
1472				=item *
1473
1474				You should never change the server variable autocommit manually,
1475				unless you are ignoring DBI's transaction support.
1476
1477				=item *
1478
1479				Switching AutoCommit mode from on to off or vice versa may fail.
1480				You should always check for errors, when changing AutoCommit mode.
1481				The suggested way of doing so is using the DBI flag RaiseError.
1482				If you don't like RaiseError, you have to use code like the
1483				following:
1484
1485				$dbh->{'AutoCommit'} = 0;
1486				if ($dbh->{'AutoCommit'}) {
1487				# An error occurred!
1488				}
1489
1490				=item *
1491
1492				If you detect an error while changing the AutoCommit mode, you
1493				should no longer use the database handle. In other words, you
1494				should disconnect and reconnect again, because the transaction
1495				mode is unpredictable. Alternatively you may verify the transaction
1496				mode by checking the value of the server variable autocommit.
1497				However, such behaviour isn't portable.
1498
1499				=item *
1500
1501				DBD::mysql has a "reconnect" feature that handles the so-called
1502				MySQL "morning bug": If the server has disconnected, most probably
1503				due to a timeout, then by default the driver will reconnect and
1504				attempt to execute the same SQL statement again. However, this
1505				behaviour is disabled when AutoCommit is off: Otherwise the
1506				transaction state would be completely unpredictable after a
1507				reconnect.
1508
1509				=item *
1510
1511				The "reconnect" feature of DBD::mysql can be toggled by using the
1512				L<mysql_auto_reconnect> attribute. This behaviour should be turned off
1513				in code that uses LOCK TABLE because if the database server time out
1514				and DBD::mysql reconnect, table locks will be lost without any
1515				indication of such loss.
1516
1517				=back
1518
1519				=over
1520
1521				=head1 MULTIPLE RESULT SETS
1522
1523				As of version 3.0002_5, DBD::mysql supports multiple result sets (Thanks
1524				to Guy Harrison!). This is the first release of this functionality, so
1525				there may be issues. Please report bugs if you run into them!
1526
1527				The basic usage of multiple result sets is
1528
1529				do
1530				{
1531				while (@row= $sth->fetchrow_array())
1532				{
1533				do stuff;
1534				}
1535				} while ($sth->more_results)
1536
1537				An example would be:
1538
1539				$dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
1540
1541				$dbh->do("create procedure somproc() deterministic
1542				begin
1543				declare a,b,c,d int;
1544				set a=1;
1545				set b=2;
1546				set c=3;
1547				set d=4;
1548				select a, b, c, d;
1549				select d, c, b, a;
1550				select b, a, c, d;
1551				select c, b, d, a;
1552				end") or print $DBI::errstr;
1553
1554				$sth=$dbh->prepare('call someproc()') \|\|
1555				die $DBI::err.": ".$DBI::errstr;
1556
1557				$sth->execute \|\| die DBI::err.": ".$DBI::errstr; $rowset=0;
1558				do {
1559				print "\nRowset ".++$i."\n---------------------------------------\n\n";
1560				foreach $colno (0..$sth->{NUM_OF_FIELDS}) {
1561				print $sth->{NAME}->[$colno]."\t";
1562				}
1563				print "\n";
1564				while (@row= $sth->fetchrow_array()) {
1565				foreach $field (0..$#row) {
1566				print $row[$field]."\t";
1567				}
1568				print "\n";
1569				}
1570				} until (!$sth->more_results)
1571
1572				For more examples, please see the eg/ directory. This is where helpful
1573				DBD::mysql code snippits will be added in the future.
1574
1575				=head2 Issues with Multiple result sets
1576
1577				So far, the main issue is if your result sets are "jagged", meaning, the
1578				number of columns of your results vary. Varying numbers of columns could
1579				result in your script crashing. This is something that will be fixed soon.
1580
1581
1582				=head1 MULTITHREADING
1583
1584				The multithreading capabilities of DBD::mysql depend completely
1585				on the underlying C libraries: The modules are working with handle data
1586				only, no global variables are accessed or (to the best of my knowledge)
1587				thread unsafe functions are called. Thus DBD::mysql is believed
1588				to be completely thread safe, if the C libraries are thread safe
1589				and you don't share handles among threads.
1590
1591				The obvious question is: Are the C libraries thread safe?
1592				In the case of MySQL the answer is "mostly" and, in theory, you should
1593				be able to get a "yes", if the C library is compiled for being thread
1594				safe (By default it isn't.) by passing the option -with-thread-safe-client
1595				to configure. See the section on I<How to make a threadsafe client> in
1596				the manual.
1597
1598
1599				=head1 INSTALLATION
1600
1601				Windows users may skip this section and pass over to L<WIN32
1602				INSTALLATION> below. Others, go on reading.
1603
1604				First of all, you do not need an installed MySQL server for installing
1605				DBD::mysql. However, you need at least the client
1606				libraries and possibly the header files, if you are compiling DBD::mysql
1607				from source. In the case of MySQL you can create a
1608				client-only version by using the configure option --without-server.
1609				If you are using precompiled binaries, then it may be possible to
1610				use just selected RPM's like MySQL-client and MySQL-devel or something
1611				similar, depending on the distribution.
1612
1613				First you need to install the DBI module. For using I<dbimon>, a
1614				simple DBI shell it is recommended to install Data::ShowTable another
1615				Perl module.
1616
1617				I recommend trying automatic installation via the CPAN module. Try
1618
1619				perl -MCPAN -e shell
1620
1621				If you are using the CPAN module for the first time, it will prompt
1622				you a lot of questions. If you finally receive the CPAN prompt, enter
1623
1624				install Bundle::DBD::mysql
1625
1626				If this fails (which may be the case for a number of reasons, for
1627				example because you are behind a firewall or don't have network
1628				access), you need to do a manual installation. First of all you
1629				need to fetch the modules from CPAN search
1630
1631				http://search.cpan.org/
1632
1633				The following modules are required
1634
1635				DBI
1636				Data::ShowTable
1637				DBD::mysql
1638
1639				Then enter the following commands (note - versions are just examples):
1640
1641				gzip -cd DBI-(version).tar.gz \| tar xf -
1642				cd DBI-(version)
1643				perl Makefile.PL
1644				make
1645				make test
1646				make install
1647
1648				cd ..
1649				gzip -cd Data-ShowTable-(version).tar.gz \| tar xf -
1650				cd Data-ShowTable-3.3
1651				perl Makefile.PL
1652				make
1653				make install
1654
1655				cd ..
1656				gzip -cd DBD-mysql-(version)-tar.gz \| tar xf -
1657				cd DBD-mysql-(version)
1658				perl Makefile.PL
1659				make
1660				make test
1661				make install
1662
1663				During "perl Makefile.PL" you will be prompted some questions.
1664				Other questions are the directories with header files and libraries.
1665				For example, of your file F<mysql.h> is in F</usr/include/mysql/mysql.h>,
1666				then enter the header directory F</usr>, likewise for
1667				F</usr/lib/mysql/libmysqlclient.a> or F</usr/lib/libmysqlclient.so>.
1668
1669
1670				=head1 WIN32 INSTALLATION
1671
1672				If you are using ActivePerl, you may use ppm to install DBD-mysql.
1673				For Perl 5.6, upgrade to Build 623 or later, then it is sufficient
1674				to run
1675
1676				ppm install DBI
1677				ppm install DBD::mysql
1678
1679				If you need an HTTP proxy, you might need to set the environment
1680				variable http_proxy, for example like this:
1681
1682				set http_proxy=http://myproxy.com:8080/
1683
1684				As of this writing, DBD::mysql is missing in the ActivePerl 5.8.0
1685				repository. However, Randy Kobes has kindly donated an own
1686				distribution and the following might succeed:
1687
1688				ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd
1689
1690				Otherwise you definitely need a C compiler. And it must be the same
1691				compiler that was being used for compiling Perl itself. If you don't
1692				have a C compiler, the file README.win32 from the Perl source
1693				distribution tells you where to obtain freely distributable C compilers
1694				like egcs or gcc. The Perl sources are available via CPAN search
1695
1696				http://search.cpan.org
1697
1698				I recommend using the win32clients package for installing DBD::mysql
1699				under Win32, available for download on www.tcx.se. The following steps
1700				have been required for me:
1701
1702				=over
1703
1704				=item -
1705
1706				The current Perl versions (5.6, as of this writing) do have a problem
1707				with detecting the C libraries. I recommend to apply the following
1708				patch:
1709
1710				*** c:\Perl\lib\ExtUtils\Liblist.pm.orig Sat Apr 15 20:03:40 2000
1711				--- c:\Perl\lib\ExtUtils\Liblist.pm Sat Apr 15 20:03:45 2000
1712				***************
1713				* 230,235 **
1714				--- 230,239 ----
1715				# add "$Config{installarchlib}/CORE" to default search path
1716				push @libpath, "$Config{installarchlib}/CORE";
1717
1718				+ if ($VC and exists($ENV{LIB}) and defined($ENV{LIB})) {
1719				+ push(@libpath, split(/;/, $ENV{LIB}));
1720				+ }
1721				+
1722				foreach (Text::ParseWords::quotewords('\s+', 0, $potential_libs)){
1723
1724				$thislib = $_;
1725
1726				=item -
1727
1728				Extract sources into F<C:\>. This will create a directory F<C:\mysql>
1729				with subdirectories include and lib.
1730
1731				IMPORTANT: Make sure this subdirectory is not shared by other TCX
1732				files! In particular do not store the MySQL server in the same
1733				directory. If the server is already installed in F<C:\mysql>,
1734				choose a location like F<C:\tmp>, extract the win32clients there.
1735				Note that you can remove this directory entirely once you have
1736				installed DBD::mysql.
1737
1738				=item -
1739
1740				Extract the DBD::mysql sources into another directory, for
1741				example F<C:\src\siteperl>
1742
1743				=item -
1744
1745				Open a DOS shell and change directory to F<C:\src\siteperl>.
1746
1747				=item -
1748
1749				The next step is only required if you repeat building the modules: Make
1750				sure that you have a clean build tree by running
1751
1752				nmake realclean
1753
1754				If you don't have VC++, replace nmake with your flavour of make. If
1755				error messages are reported in this step, you may safely ignore them.
1756
1757				=item -
1758
1759				Run
1760
1761				perl Makefile.PL
1762
1763				which will prompt you for some settings. The really important ones are:
1764
1765				Which DBMS do you want to use?
1766
1767				enter a 1 here (MySQL only), and
1768
1769				Where is your mysql installed? Please tell me the directory that
1770				contains the subdir include.
1771
1772				where you have to enter the win32clients directory, for example
1773				F<C:\mysql> or F<C:\tmp\mysql>.
1774
1775				=item -
1776
1777				Continued in the usual way:
1778
1779				nmake
1780				nmake install
1781
1782				=back
1783
1784				If you want to create a PPM package for the ActiveState Perl version, then
1785				modify the above steps as follows: Run
1786
1787				perl Makefile.PL NAME=DBD-mysql BINARY_LOCATION=DBD-mysql.tar.gz
1788				nmake ppd
1789				nmake
1790
1791				Once that is done, use tar and gzip (for example those from the CygWin32
1792				distribution) to create an archive:
1793
1794				mkdir x86
1795				tar cf x86/DBD-mysql.tar blib
1796				gzip x86/DBD-mysql.tar
1797
1798				Put the files x86/DBD-mysql.tar.gz and DBD-mysql.ppd onto some WWW server
1799				and install them by typing
1800
1801				install http://your.server.name/your/directory/DBD-mysql.ppd
1802
1803				in the PPM program.
1804
1805
1806				=head1 AUTHORS
1807
1808				The current version of B<DBD::mysql> is almost completely written
1809				by Jochen Wiedmann, and is now being maintained by
1810				Patrick Galbraith (I<patg@mysql.com>).
1811				The first version's author was Alligator Descartes, who was aided
1812				and abetted by Gary Shea, Andreas K�nig and Tim Bunce amongst others.
1813
1814				The B<Mysql> module was originally written by Andreas K�nig
1815				<koenig@kulturbox.de>. The current version, mainly an emulation
1816				layer, is from Jochen Wiedmann.
1817
1818
1819				=head1 COPYRIGHT
1820
1821
1822				This module is
1823				Large Portions Copyright (c) 2004-2006 MySQL Patrick Galbraith, Alexey Stroganov,
1824				Large Portions Copyright (c) 2003-2005 Rudolf Lippan; Large Portions
1825				Copyright (c) 1997-2003 Jochen Wiedmann, with code portions
1826				Copyright (c)1994-1997 their original authors This module is
1827				released under the same license as Perl itself. See the Perl README
1828				for details.
1829
1830
1831				=head1 MAILING LIST SUPPORT
1832
1833				This module is maintained and supported on a mailing list,
1834
1835				perl@lists.mysql.com
1836
1837				To subscribe to this list, go to
1838
1839				http://lists.mysql.com/perl?sub=1
1840
1841				Mailing list archives are available at
1842
1843				http://lists.mysql.com/perl
1844
1845				Additionally you might try the dbi-user mailing list for questions about
1846				DBI and its modules in general. Subscribe via
1847
1848				dbi-users-subscribe@perl.org
1849
1850				Mailing list archives are at
1851
1852				http://groups.google.com/group/perl.dbi.users?hl=en&lr=
1853
1854				Also, the main DBI site is at
1855
1856				http://dbi.perl.org/
1857
1858				=head1 ADDITIONAL DBI INFORMATION
1859
1860				Additional information on the DBI project can be found on the World
1861				Wide Web at the following URL:
1862
1863				http://dbi.perl.org
1864
1865				where documentation, pointers to the mailing lists and mailing list
1866				archives and pointers to the most current versions of the modules can
1867				be used.
1868
1869				Information on the DBI interface itself can be gained by typing:
1870
1871				perldoc DBI
1872
1873				right now!
1874
1875
1876				=head1 BUG REPORTING, ENHANCEMENT/FEATURE REQUESTS
1877
1878				Please report bugs, including all the information needed
1879				such as DBD::mysql version, MySQL version, OS type/version, etc
1880				to this link:
1881
1882				http://bugs.mysql.com/
1883
1884
1885				=cut
1886
1887
				# spent 156µs within DBD::mysql::bootstrap which was called # once (156µs+0s) by DynaLoader::bootstrap at line 219 of /usr/lib/perl/5.10/DynaLoader.pm sub DBD::mysql::bootstrap; # xsub
				# spent 2.98ms within DBD::mysql::db::_login which was called # once (2.98ms+0s) by DBD::mysql::dr::connect at line 142 of /usr/lib/perl5/DBD/mysql.pm sub DBD::mysql::db::_login; # xsub
				# spent 3.71ms within DBD::mysql::st::_prepare which was called 507 times, avg 7µs/call: # 507 times (3.71ms+0s) by DBD::mysql::db::prepare at line 224 of /usr/lib/perl5/DBD/mysql.pm, avg 7µs/call sub DBD::mysql::st::_prepare; # xsub