|
1 # -*- cperl -*- |
|
2 |
|
3 package DBD::mysql; |
|
4 use strict; |
|
5 use vars qw(@ISA $VERSION $err $errstr $drh); |
|
6 |
|
7 use DBI (); |
|
8 use DynaLoader(); |
|
9 use Carp (); |
|
10 @ISA = qw(DynaLoader); |
|
11 |
|
12 $VERSION = '4.014'; |
|
13 |
|
14 bootstrap DBD::mysql $VERSION; |
|
15 |
|
16 |
|
17 $err = 0; # holds error code for DBI::err |
|
18 $errstr = ""; # holds error string for DBI::errstr |
|
19 $drh = undef; # holds driver handle once initialised |
|
20 |
|
21 sub driver{ |
|
22 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', |
|
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 sub _OdbcParse($$$) { |
|
43 my($class, $dsn, $hash, $args) = @_; |
|
44 my($var, $val); |
|
45 if (!defined($dsn)) { |
|
46 return; |
|
47 } |
|
48 while (length($dsn)) { |
|
49 if ($dsn =~ /([^:;]*)[:;](.*)/) { |
|
50 $val = $1; |
|
51 $dsn = $2; |
|
52 } else { |
|
53 $val = $dsn; |
|
54 $dsn = ''; |
|
55 } |
|
56 if ($val =~ /([^=]*)=(.*)/) { |
|
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; |
|
96 |
|
97 |
|
98 package DBD::mysql::dr; # ====== DRIVER ====== |
|
99 use strict; |
|
100 use DBI qw(:sql_types); |
|
101 use DBI::Const::GetInfoType; |
|
102 |
|
103 sub connect { |
|
104 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, |
|
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, |
|
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) |
|
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 use strict; |
|
187 use DBI qw(:sql_types); |
|
188 |
|
189 %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 %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 my($dbh, $statement, $attribs)= @_; |
|
219 |
|
220 # create a 'blank' dbh |
|
221 my $sth = DBI::_new_sth($dbh, {'Statement' => $statement}); |
|
222 |
|
223 # Populate internal handle data. |
|
224 if (!DBD::mysql::st::_prepare($sth, $statement, $attribs)) { |
|
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|\w*text|\w*blob)/) |
|
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 use strict; |
|
739 |
|
740 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_init_command |
|
950 |
|
951 If your DSN contains the option "mysql_init_command_timeout=##", then |
|
952 this SQL statement is executed when connecting to the MySQL server. |
|
953 It is automatically re-executed if reconnection occurs. |
|
954 |
|
955 =item mysql_read_default_file |
|
956 |
|
957 =item mysql_read_default_group |
|
958 |
|
959 These options can be used to read a config file like /etc/my.cnf or |
|
960 ~/.my.cnf. By default MySQL's C client library doesn't use any config |
|
961 files unlike the client programs (mysql, mysqladmin, ...) that do, but |
|
962 outside of the C client library. Thus you need to explicitly request |
|
963 reading a config file, as in |
|
964 |
|
965 $dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf"; |
|
966 $dbh = DBI->connect($dsn, $user, $password) |
|
967 |
|
968 The option mysql_read_default_group can be used to specify the default |
|
969 group in the config file: Usually this is the I<client> group, but |
|
970 see the following example: |
|
971 |
|
972 [client] |
|
973 host=localhost |
|
974 |
|
975 [perl] |
|
976 host=perlhost |
|
977 |
|
978 (Note the order of the entries! The example won't work, if you reverse |
|
979 the [client] and [perl] sections!) |
|
980 |
|
981 If you read this config file, then you'll be typically connected to |
|
982 I<localhost>. However, by using |
|
983 |
|
984 $dsn = "DBI:mysql:test;mysql_read_default_group=perl;" |
|
985 . "mysql_read_default_file=/home/joe/my.cnf"; |
|
986 $dbh = DBI->connect($dsn, $user, $password); |
|
987 |
|
988 you'll be connected to I<perlhost>. Note that if you specify a |
|
989 default group and do not specify a file, then the default config |
|
990 files will all be read. See the documentation of |
|
991 the C function mysql_options() for details. |
|
992 |
|
993 =item mysql_socket |
|
994 |
|
995 As of MySQL 3.21.15, it is possible to choose the Unix socket that is |
|
996 used for connecting to the server. This is done, for example, with |
|
997 |
|
998 mysql_socket=/dev/mysql |
|
999 |
|
1000 Usually there's no need for this option, unless you are using another |
|
1001 location for the socket than that built into the client. |
|
1002 |
|
1003 =item mysql_ssl |
|
1004 |
|
1005 A true value turns on the CLIENT_SSL flag when connecting to the MySQL |
|
1006 database: |
|
1007 |
|
1008 mysql_ssl=1 |
|
1009 |
|
1010 This means that your communication with the server will be encrypted. |
|
1011 |
|
1012 If you turn mysql_ssl on, you might also wish to use the following |
|
1013 flags: |
|
1014 |
|
1015 =item mysql_ssl_client_key |
|
1016 |
|
1017 =item mysql_ssl_client_cert |
|
1018 |
|
1019 =item mysql_ssl_ca_file |
|
1020 |
|
1021 =item mysql_ssl_ca_path |
|
1022 |
|
1023 =item mysql_ssl_cipher |
|
1024 |
|
1025 These are used to specify the respective parameters of a call |
|
1026 to mysql_ssl_set, if mysql_ssl is turned on. |
|
1027 |
|
1028 |
|
1029 =item mysql_local_infile |
|
1030 |
|
1031 As of MySQL 3.23.49, the LOCAL capability for LOAD DATA may be disabled |
|
1032 in the MySQL client library by default. If your DSN contains the option |
|
1033 "mysql_local_infile=1", LOAD DATA LOCAL will be enabled. (However, |
|
1034 this option is *ineffective* if the server has also been configured to |
|
1035 disallow LOCAL.) |
|
1036 |
|
1037 =item mysql_multi_statements |
|
1038 |
|
1039 As of MySQL 4.1, support for multiple statements seperated by a semicolon |
|
1040 (;) may be enabled by using this option. Enabling this option may cause |
|
1041 problems if server-side prepared statements are also enabled. |
|
1042 |
|
1043 =item Prepared statement support (server side prepare) |
|
1044 |
|
1045 As of 3.0002_1, server side prepare statements were on by default (if your |
|
1046 server was >= 4.1.3). As of 3.0009, they were off by default again due to |
|
1047 issues with the prepared statement API (all other mysql connectors are |
|
1048 set this way until C API issues are resolved). The requirement to use |
|
1049 prepared statements still remains that you have a server >= 4.1.3 |
|
1050 |
|
1051 To use server side prepared statements, all you need to do is set the variable |
|
1052 mysql_server_prepare in the connect: |
|
1053 |
|
1054 $dbh = DBI->connect( |
|
1055 "DBI:mysql:database=test;host=localhost;mysql_server_prepare=1", |
|
1056 "", |
|
1057 "", |
|
1058 { RaiseError => 1, AutoCommit => 1 } |
|
1059 ); |
|
1060 |
|
1061 * Note: delimiter for this param is ';' |
|
1062 |
|
1063 There are many benefits to using server side prepare statements, mostly if you are |
|
1064 performing many inserts because of that fact that a single statement is prepared |
|
1065 to accept multiple insert values. |
|
1066 |
|
1067 To make sure that the 'make test' step tests whether server prepare works, you just |
|
1068 need to export the env variable MYSQL_SERVER_PREPARE: |
|
1069 |
|
1070 export MYSQL_SERVER_PREPARE=1 |
|
1071 |
|
1072 |
|
1073 =item mysql_embedded_options |
|
1074 |
|
1075 The option <mysql_embedded_options> can be used to pass 'command-line' |
|
1076 options to embedded server. |
|
1077 |
|
1078 Example: |
|
1079 |
|
1080 use DBI; |
|
1081 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_options=--help,--verbose"; |
|
1082 $dbh = DBI->connect($testdsn,"a","b"); |
|
1083 |
|
1084 This would cause the command line help to the embedded MySQL server library |
|
1085 to be printed. |
|
1086 |
|
1087 |
|
1088 =item mysql_embedded_groups |
|
1089 |
|
1090 The option <mysql_embedded_groups> can be used to specify the groups in the |
|
1091 config file(I<my.cnf>) which will be used to get options for embedded server. |
|
1092 If not specified [server] and [embedded] groups will be used. |
|
1093 |
|
1094 Example: |
|
1095 |
|
1096 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_groups=embedded_server,common"; |
|
1097 |
|
1098 |
|
1099 =back |
|
1100 |
|
1101 =back |
|
1102 |
|
1103 |
|
1104 =head2 Private MetaData Methods |
|
1105 |
|
1106 =over |
|
1107 |
|
1108 =item B<ListDBs> |
|
1109 |
|
1110 my $drh = DBI->install_driver("mysql"); |
|
1111 @dbs = $drh->func("$hostname:$port", '_ListDBs'); |
|
1112 @dbs = $drh->func($hostname, $port, '_ListDBs'); |
|
1113 @dbs = $dbh->func('_ListDBs'); |
|
1114 |
|
1115 Returns a list of all databases managed by the MySQL server |
|
1116 running on C<$hostname>, port C<$port>. This is a legacy |
|
1117 method. Instead, you should use the portable method |
|
1118 |
|
1119 @dbs = DBI->data_sources("mysql"); |
|
1120 |
|
1121 =back |
|
1122 |
|
1123 |
|
1124 =head2 Server Administration |
|
1125 |
|
1126 =over |
|
1127 |
|
1128 =item admin |
|
1129 |
|
1130 $rc = $drh->func("createdb", $dbname, [host, user, password,], 'admin'); |
|
1131 $rc = $drh->func("dropdb", $dbname, [host, user, password,], 'admin'); |
|
1132 $rc = $drh->func("shutdown", [host, user, password,], 'admin'); |
|
1133 $rc = $drh->func("reload", [host, user, password,], 'admin'); |
|
1134 |
|
1135 or |
|
1136 |
|
1137 $rc = $dbh->func("createdb", $dbname, 'admin'); |
|
1138 $rc = $dbh->func("dropdb", $dbname, 'admin'); |
|
1139 $rc = $dbh->func("shutdown", 'admin'); |
|
1140 $rc = $dbh->func("reload", 'admin'); |
|
1141 |
|
1142 For server administration you need a server connection. For obtaining |
|
1143 this connection you have two options: Either use a driver handle (drh) |
|
1144 and supply the appropriate arguments (host, defaults localhost, user, |
|
1145 defaults to '' and password, defaults to ''). A driver handle can be |
|
1146 obtained with |
|
1147 |
|
1148 $drh = DBI->install_driver('mysql'); |
|
1149 |
|
1150 Otherwise reuse the existing connection of a database handle (dbh). |
|
1151 |
|
1152 There's only one function available for administrative purposes, comparable |
|
1153 to the m(y)sqladmin programs. The command being execute depends on the |
|
1154 first argument: |
|
1155 |
|
1156 =over |
|
1157 |
|
1158 =item createdb |
|
1159 |
|
1160 Creates the database $dbname. Equivalent to "m(y)sqladmin create $dbname". |
|
1161 |
|
1162 =item dropdb |
|
1163 |
|
1164 Drops the database $dbname. Equivalent to "m(y)sqladmin drop $dbname". |
|
1165 |
|
1166 It should be noted that database deletion is |
|
1167 I<not prompted for> in any way. Nor is it undo-able from DBI. |
|
1168 |
|
1169 Once you issue the dropDB() method, the database will be gone! |
|
1170 |
|
1171 These method should be used at your own risk. |
|
1172 |
|
1173 =item shutdown |
|
1174 |
|
1175 Silently shuts down the database engine. (Without prompting!) |
|
1176 Equivalent to "m(y)sqladmin shutdown". |
|
1177 |
|
1178 =item reload |
|
1179 |
|
1180 Reloads the servers configuration files and/or tables. This can be particularly |
|
1181 important if you modify access privileges or create new users. |
|
1182 |
|
1183 =back |
|
1184 |
|
1185 =back |
|
1186 |
|
1187 |
|
1188 =head1 DATABASE HANDLES |
|
1189 |
|
1190 The DBD::mysql driver supports the following attributes of database |
|
1191 handles (read only): |
|
1192 |
|
1193 $errno = $dbh->{'mysql_errno'}; |
|
1194 $error = $dbh->{'mysql_error'}; |
|
1195 $info = $dbh->{'mysql_hostinfo'}; |
|
1196 $info = $dbh->{'mysql_info'}; |
|
1197 $insertid = $dbh->{'mysql_insertid'}; |
|
1198 $info = $dbh->{'mysql_protoinfo'}; |
|
1199 $info = $dbh->{'mysql_serverinfo'}; |
|
1200 $info = $dbh->{'mysql_stat'}; |
|
1201 $threadId = $dbh->{'mysql_thread_id'}; |
|
1202 |
|
1203 These correspond to mysql_errno(), mysql_error(), mysql_get_host_info(), |
|
1204 mysql_info(), mysql_insert_id(), mysql_get_proto_info(), |
|
1205 mysql_get_server_info(), mysql_stat() and mysql_thread_id(), |
|
1206 respectively. |
|
1207 |
|
1208 |
|
1209 $info_hashref = $dhb->{mysql_dbd_stats} |
|
1210 |
|
1211 DBD::mysql keeps track of some statistics in the mysql_dbd_stats attribute. |
|
1212 The following stats are being maintained: |
|
1213 |
|
1214 =over |
|
1215 |
|
1216 =item auto_reconnects_ok |
|
1217 |
|
1218 The number of times that DBD::mysql successfully reconnected to the mysql |
|
1219 server. |
|
1220 |
|
1221 =item auto_reconnects_failed |
|
1222 |
|
1223 The number of times that DBD::mysql tried to reconnect to mysql but failed. |
|
1224 |
|
1225 =back |
|
1226 |
|
1227 The DBD::mysql driver also supports the following attribute(s) of database |
|
1228 handles (read/write): |
|
1229 |
|
1230 $bool_value = $dbh->{mysql_auto_reconnect}; |
|
1231 $dbh->{mysql_auto_reconnect} = $AutoReconnect ? 1 : 0; |
|
1232 |
|
1233 |
|
1234 =item mysql_auto_reconnect |
|
1235 |
|
1236 This attribute determines whether DBD::mysql will automatically reconnect |
|
1237 to mysql if the connection be lost. This feature defaults to off; however, |
|
1238 if either the GATEWAY_INTERFACE or MOD_PERL envionment variable is set, |
|
1239 DBD::mysql will turn mysql_auto_reconnect on. Setting mysql_auto_reconnect |
|
1240 to on is not advised if 'lock tables' is used because if DBD::mysql reconnect |
|
1241 to mysql all table locks will be lost. This attribute is ignored when |
|
1242 AutoCommit is turned off, and when AutoCommit is turned off, DBD::mysql will |
|
1243 not automatically reconnect to the server. |
|
1244 |
|
1245 =item mysql_use_result |
|
1246 |
|
1247 This attribute forces the driver to use mysql_use_result rather than |
|
1248 mysql_store_result. The former is faster and less memory consuming, but |
|
1249 tends to block other processes. (That's why mysql_store_result is the |
|
1250 default.) |
|
1251 |
|
1252 It is possible to set default value of the C<mysql_use_result> attribute |
|
1253 for $dbh using several ways: |
|
1254 |
|
1255 - through DSN |
|
1256 |
|
1257 $dbh= DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", ""); |
|
1258 |
|
1259 - after creation of database handle |
|
1260 |
|
1261 $dbh->{'mysql_use_result'}=0; #disable |
|
1262 $dbh->{'mysql_use_result'}=1; #enable |
|
1263 |
|
1264 It is possible to set/unset the C<mysql_use_result> attribute after |
|
1265 creation of statement handle. See below. |
|
1266 |
|
1267 =item mysql_enable_utf8 |
|
1268 |
|
1269 This attribute determines whether DBD::mysql should assume strings |
|
1270 stored in the database are utf8. This feature defaults to off. |
|
1271 |
|
1272 When set, a data retrieved from a textual column type (char, varchar, |
|
1273 etc) will have the UTF-8 flag turned on if necessary. This enables |
|
1274 character semantics on that string. You will also need to ensure that |
|
1275 your database / table / column is configured to use UTF8. See Chapter |
|
1276 10 of the mysql manual for details. |
|
1277 |
|
1278 Additionally, turning on this flag tells MySQL that incoming data should |
|
1279 be treated as UTF-8. This will only take effect if used as part of the |
|
1280 call to connect(). If you turn the flag on after connecting, you will |
|
1281 need to issue the command C<SET NAMES utf8> to get the same effect. |
|
1282 |
|
1283 This option is experimental and may change in future versions. |
|
1284 |
|
1285 =item mysql_bind_type_guessing |
|
1286 |
|
1287 This attribute causes the driver (emulated prepare statements) |
|
1288 to attempt to guess if a value being bound is a numeric value, |
|
1289 and if so, doesn't quote the value. This was created by |
|
1290 Dragonchild and is one way to deal with the performance issue |
|
1291 of using quotes in a statement that is inserting or updating a |
|
1292 large numeric value. This was previously called |
|
1293 C<unsafe_bind_type_guessing> because it is experimental. I have |
|
1294 successfully run the full test suite with this option turned on, |
|
1295 the name can now be simply C<mysql_bind_type_guessing>. |
|
1296 |
|
1297 See bug: https://rt.cpan.org/Ticket/Display.html?id=43822 |
|
1298 |
|
1299 C<mysql_bind_type_guessing> can be turned on via |
|
1300 |
|
1301 - through DSN |
|
1302 |
|
1303 my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass', |
|
1304 { mysql_bind_type_guessing => 1}) |
|
1305 |
|
1306 - OR after handle creation |
|
1307 |
|
1308 $dbh->{mysql_bind_type_guessing} = 1; |
|
1309 |
|
1310 =item mysql_no_autocommit_cmd |
|
1311 |
|
1312 This attribute causes the driver to not issue 'set autocommit' |
|
1313 either through explicit or using mysql_autocommit(). This is |
|
1314 particularly useful in the case of using MySQL Proxy. |
|
1315 |
|
1316 See the bug report: |
|
1317 |
|
1318 https://rt.cpan.org/Public/Bug/Display.html?id=46308 |
|
1319 |
|
1320 As well as: |
|
1321 |
|
1322 http://bugs.mysql.com/bug.php?id=32464 |
|
1323 |
|
1324 C<mysql_no_autocommit_cmd> can be turned on via |
|
1325 |
|
1326 - through DSN |
|
1327 |
|
1328 my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass', |
|
1329 { mysql_no_autocommit_cmd => 1}) |
|
1330 |
|
1331 - OR after handle creation |
|
1332 |
|
1333 $dbh->{mysql_no_autocommit_cmd} = 1; |
|
1334 |
|
1335 |
|
1336 |
|
1337 =head1 STATEMENT HANDLES |
|
1338 |
|
1339 The statement handles of DBD::mysql support a number |
|
1340 of attributes. You access these by using, for example, |
|
1341 |
|
1342 my $numFields = $sth->{'NUM_OF_FIELDS'}; |
|
1343 |
|
1344 Note, that most attributes are valid only after a successfull I<execute>. |
|
1345 An C<undef> value will returned in that case. The most important exception |
|
1346 is the C<mysql_use_result> attribute: This forces the driver to use |
|
1347 mysql_use_result rather than mysql_store_result. The former is faster |
|
1348 and less memory consuming, but tends to block other processes. (That's why |
|
1349 mysql_store_result is the default.) |
|
1350 |
|
1351 To set the C<mysql_use_result> attribute, use either of the following: |
|
1352 |
|
1353 my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1}); |
|
1354 |
|
1355 or |
|
1356 |
|
1357 my $sth = $dbh->prepare("QUERY"); |
|
1358 $sth->{"mysql_use_result"} = 1; |
|
1359 |
|
1360 Column dependent attributes, for example I<NAME>, the column names, |
|
1361 are returned as a reference to an array. The array indices are |
|
1362 corresponding to the indices of the arrays returned by I<fetchrow> |
|
1363 and similar methods. For example the following code will print a |
|
1364 header of table names together with all rows: |
|
1365 |
|
1366 my $sth = $dbh->prepare("SELECT * FROM $table"); |
|
1367 if (!$sth) { |
|
1368 die "Error:" . $dbh->errstr . "\n"; |
|
1369 } |
|
1370 if (!$sth->execute) { |
|
1371 die "Error:" . $sth->errstr . "\n"; |
|
1372 } |
|
1373 my $names = $sth->{'NAME'}; |
|
1374 my $numFields = $sth->{'NUM_OF_FIELDS'}; |
|
1375 for (my $i = 0; $i < $numFields; $i++) { |
|
1376 printf("%s%s", $i ? "," : "", $$names[$i]); |
|
1377 } |
|
1378 print "\n"; |
|
1379 while (my $ref = $sth->fetchrow_arrayref) { |
|
1380 for (my $i = 0; $i < $numFields; $i++) { |
|
1381 printf("%s%s", $i ? "," : "", $$ref[$i]); |
|
1382 } |
|
1383 print "\n"; |
|
1384 } |
|
1385 |
|
1386 For portable applications you should restrict yourself to attributes with |
|
1387 capitalized or mixed case names. Lower case attribute names are private |
|
1388 to DBD::mysql. The attribute list includes: |
|
1389 |
|
1390 =over |
|
1391 |
|
1392 =item ChopBlanks |
|
1393 |
|
1394 this attribute determines whether a I<fetchrow> will chop preceding |
|
1395 and trailing blanks off the column values. Chopping blanks does not |
|
1396 have impact on the I<max_length> attribute. |
|
1397 |
|
1398 =item mysql_insertid |
|
1399 |
|
1400 MySQL has the ability to choose unique key values automatically. If this |
|
1401 happened, the new ID will be stored in this attribute. An alternative |
|
1402 way for accessing this attribute is via $dbh->{'mysql_insertid'}. |
|
1403 (Note we are using the $dbh in this case!) |
|
1404 |
|
1405 =item mysql_is_blob |
|
1406 |
|
1407 Reference to an array of boolean values; TRUE indicates, that the |
|
1408 respective column is a blob. This attribute is valid for MySQL only. |
|
1409 |
|
1410 =item mysql_is_key |
|
1411 |
|
1412 Reference to an array of boolean values; TRUE indicates, that the |
|
1413 respective column is a key. This is valid for MySQL only. |
|
1414 |
|
1415 =item mysql_is_num |
|
1416 |
|
1417 Reference to an array of boolean values; TRUE indicates, that the |
|
1418 respective column contains numeric values. |
|
1419 |
|
1420 =item mysql_is_pri_key |
|
1421 |
|
1422 Reference to an array of boolean values; TRUE indicates, that the |
|
1423 respective column is a primary key. |
|
1424 |
|
1425 =item mysql_is_auto_increment |
|
1426 |
|
1427 Reference to an array of boolean values; TRUE indicates that the |
|
1428 respective column is an AUTO_INCREMENT column. This is only valid |
|
1429 for MySQL. |
|
1430 |
|
1431 =item mysql_length |
|
1432 |
|
1433 =item mysql_max_length |
|
1434 |
|
1435 A reference to an array of maximum column sizes. The I<max_length> is |
|
1436 the maximum physically present in the result table, I<length> gives |
|
1437 the theoretically possible maximum. I<max_length> is valid for MySQL |
|
1438 only. |
|
1439 |
|
1440 =item NAME |
|
1441 |
|
1442 A reference to an array of column names. |
|
1443 |
|
1444 =item NULLABLE |
|
1445 |
|
1446 A reference to an array of boolean values; TRUE indicates that this column |
|
1447 may contain NULL's. |
|
1448 |
|
1449 =item NUM_OF_FIELDS |
|
1450 |
|
1451 Number of fields returned by a I<SELECT> or I<LISTFIELDS> statement. |
|
1452 You may use this for checking whether a statement returned a result: |
|
1453 A zero value indicates a non-SELECT statement like I<INSERT>, |
|
1454 I<DELETE> or I<UPDATE>. |
|
1455 |
|
1456 =item mysql_table |
|
1457 |
|
1458 A reference to an array of table names, useful in a I<JOIN> result. |
|
1459 |
|
1460 =item TYPE |
|
1461 |
|
1462 A reference to an array of column types. The engine's native column |
|
1463 types are mapped to portable types like DBI::SQL_INTEGER() or |
|
1464 DBI::SQL_VARCHAR(), as good as possible. Not all native types have |
|
1465 a meaningfull equivalent, for example DBD::mysql::FIELD_TYPE_INTERVAL |
|
1466 is mapped to DBI::SQL_VARCHAR(). |
|
1467 If you need the native column types, use I<mysql_type>. See below. |
|
1468 |
|
1469 =item mysql_type |
|
1470 |
|
1471 A reference to an array of MySQL's native column types, for example |
|
1472 DBD::mysql::FIELD_TYPE_SHORT() or DBD::mysql::FIELD_TYPE_STRING(). |
|
1473 Use the I<TYPE> attribute, if you want portable types like |
|
1474 DBI::SQL_SMALLINT() or DBI::SQL_VARCHAR(). |
|
1475 |
|
1476 =item mysql_type_name |
|
1477 |
|
1478 Similar to mysql, but type names and not numbers are returned. |
|
1479 Whenever possible, the ANSI SQL name is preferred. |
|
1480 |
|
1481 =item mysql_warning_count |
|
1482 |
|
1483 The number of warnings generated during execution of the SQL statement. |
|
1484 |
|
1485 =back |
|
1486 |
|
1487 =head1 TRANSACTION SUPPORT |
|
1488 |
|
1489 Beginning with DBD::mysql 2.0416, transactions are supported. |
|
1490 The transaction support works as follows: |
|
1491 |
|
1492 =over |
|
1493 |
|
1494 =item * |
|
1495 |
|
1496 By default AutoCommit mode is on, following the DBI specifications. |
|
1497 |
|
1498 =item * |
|
1499 |
|
1500 If you execute |
|
1501 |
|
1502 $dbh->{'AutoCommit'} = 0; |
|
1503 |
|
1504 or |
|
1505 |
|
1506 $dbh->{'AutoCommit'} = 1; |
|
1507 |
|
1508 then the driver will set the MySQL server variable autocommit to 0 or |
|
1509 1, respectively. Switching from 0 to 1 will also issue a COMMIT, |
|
1510 following the DBI specifications. |
|
1511 |
|
1512 =item * |
|
1513 |
|
1514 The methods |
|
1515 |
|
1516 $dbh->rollback(); |
|
1517 $dbh->commit(); |
|
1518 |
|
1519 will issue the commands COMMIT and ROLLBACK, respectively. A |
|
1520 ROLLBACK will also be issued if AutoCommit mode is off and the |
|
1521 database handles DESTROY method is called. Again, this is following |
|
1522 the DBI specifications. |
|
1523 |
|
1524 =back |
|
1525 |
|
1526 Given the above, you should note the following: |
|
1527 |
|
1528 =over |
|
1529 |
|
1530 =item * |
|
1531 |
|
1532 You should never change the server variable autocommit manually, |
|
1533 unless you are ignoring DBI's transaction support. |
|
1534 |
|
1535 =item * |
|
1536 |
|
1537 Switching AutoCommit mode from on to off or vice versa may fail. |
|
1538 You should always check for errors, when changing AutoCommit mode. |
|
1539 The suggested way of doing so is using the DBI flag RaiseError. |
|
1540 If you don't like RaiseError, you have to use code like the |
|
1541 following: |
|
1542 |
|
1543 $dbh->{'AutoCommit'} = 0; |
|
1544 if ($dbh->{'AutoCommit'}) { |
|
1545 # An error occurred! |
|
1546 } |
|
1547 |
|
1548 =item * |
|
1549 |
|
1550 If you detect an error while changing the AutoCommit mode, you |
|
1551 should no longer use the database handle. In other words, you |
|
1552 should disconnect and reconnect again, because the transaction |
|
1553 mode is unpredictable. Alternatively you may verify the transaction |
|
1554 mode by checking the value of the server variable autocommit. |
|
1555 However, such behaviour isn't portable. |
|
1556 |
|
1557 =item * |
|
1558 |
|
1559 DBD::mysql has a "reconnect" feature that handles the so-called |
|
1560 MySQL "morning bug": If the server has disconnected, most probably |
|
1561 due to a timeout, then by default the driver will reconnect and |
|
1562 attempt to execute the same SQL statement again. However, this |
|
1563 behaviour is disabled when AutoCommit is off: Otherwise the |
|
1564 transaction state would be completely unpredictable after a |
|
1565 reconnect. |
|
1566 |
|
1567 =item * |
|
1568 |
|
1569 The "reconnect" feature of DBD::mysql can be toggled by using the |
|
1570 L<mysql_auto_reconnect> attribute. This behaviour should be turned off |
|
1571 in code that uses LOCK TABLE because if the database server time out |
|
1572 and DBD::mysql reconnect, table locks will be lost without any |
|
1573 indication of such loss. |
|
1574 |
|
1575 =back |
|
1576 |
|
1577 =over |
|
1578 |
|
1579 =head1 MULTIPLE RESULT SETS |
|
1580 |
|
1581 As of version 3.0002_5, DBD::mysql supports multiple result sets (Thanks |
|
1582 to Guy Harrison!). This is the first release of this functionality, so |
|
1583 there may be issues. Please report bugs if you run into them! |
|
1584 |
|
1585 The basic usage of multiple result sets is |
|
1586 |
|
1587 do |
|
1588 { |
|
1589 while (@row= $sth->fetchrow_array()) |
|
1590 { |
|
1591 do stuff; |
|
1592 } |
|
1593 } while ($sth->more_results) |
|
1594 |
|
1595 An example would be: |
|
1596 |
|
1597 $dbh->do("drop procedure if exists someproc") or print $DBI::errstr; |
|
1598 |
|
1599 $dbh->do("create procedure somproc() deterministic |
|
1600 begin |
|
1601 declare a,b,c,d int; |
|
1602 set a=1; |
|
1603 set b=2; |
|
1604 set c=3; |
|
1605 set d=4; |
|
1606 select a, b, c, d; |
|
1607 select d, c, b, a; |
|
1608 select b, a, c, d; |
|
1609 select c, b, d, a; |
|
1610 end") or print $DBI::errstr; |
|
1611 |
|
1612 $sth=$dbh->prepare('call someproc()') || |
|
1613 die $DBI::err.": ".$DBI::errstr; |
|
1614 |
|
1615 $sth->execute || die DBI::err.": ".$DBI::errstr; $rowset=0; |
|
1616 do { |
|
1617 print "\nRowset ".++$i."\n---------------------------------------\n\n"; |
|
1618 foreach $colno (0..$sth->{NUM_OF_FIELDS}) { |
|
1619 print $sth->{NAME}->[$colno]."\t"; |
|
1620 } |
|
1621 print "\n"; |
|
1622 while (@row= $sth->fetchrow_array()) { |
|
1623 foreach $field (0..$#row) { |
|
1624 print $row[$field]."\t"; |
|
1625 } |
|
1626 print "\n"; |
|
1627 } |
|
1628 } until (!$sth->more_results) |
|
1629 |
|
1630 For more examples, please see the eg/ directory. This is where helpful |
|
1631 DBD::mysql code snippits will be added in the future. |
|
1632 |
|
1633 =head2 Issues with Multiple result sets |
|
1634 |
|
1635 So far, the main issue is if your result sets are "jagged", meaning, the |
|
1636 number of columns of your results vary. Varying numbers of columns could |
|
1637 result in your script crashing. This is something that will be fixed soon. |
|
1638 |
|
1639 |
|
1640 =head1 MULTITHREADING |
|
1641 |
|
1642 The multithreading capabilities of DBD::mysql depend completely |
|
1643 on the underlying C libraries: The modules are working with handle data |
|
1644 only, no global variables are accessed or (to the best of my knowledge) |
|
1645 thread unsafe functions are called. Thus DBD::mysql is believed |
|
1646 to be completely thread safe, if the C libraries are thread safe |
|
1647 and you don't share handles among threads. |
|
1648 |
|
1649 The obvious question is: Are the C libraries thread safe? |
|
1650 In the case of MySQL the answer is "mostly" and, in theory, you should |
|
1651 be able to get a "yes", if the C library is compiled for being thread |
|
1652 safe (By default it isn't.) by passing the option -with-thread-safe-client |
|
1653 to configure. See the section on I<How to make a threadsafe client> in |
|
1654 the manual. |
|
1655 |
|
1656 |
|
1657 =head1 INSTALLATION |
|
1658 |
|
1659 Windows users may skip this section and pass over to L<WIN32 |
|
1660 INSTALLATION> below. Others, go on reading. |
|
1661 |
|
1662 First of all, you do not need an installed MySQL server for installing |
|
1663 DBD::mysql. However, you need at least the client |
|
1664 libraries and possibly the header files, if you are compiling DBD::mysql |
|
1665 from source. In the case of MySQL you can create a |
|
1666 client-only version by using the configure option --without-server. |
|
1667 If you are using precompiled binaries, then it may be possible to |
|
1668 use just selected RPM's like MySQL-client and MySQL-devel or something |
|
1669 similar, depending on the distribution. |
|
1670 |
|
1671 First you need to install the DBI module. For using I<dbimon>, a |
|
1672 simple DBI shell it is recommended to install Data::ShowTable another |
|
1673 Perl module. |
|
1674 |
|
1675 I recommend trying automatic installation via the CPAN module. Try |
|
1676 |
|
1677 perl -MCPAN -e shell |
|
1678 |
|
1679 If you are using the CPAN module for the first time, it will prompt |
|
1680 you a lot of questions. If you finally receive the CPAN prompt, enter |
|
1681 |
|
1682 install Bundle::DBD::mysql |
|
1683 |
|
1684 If this fails (which may be the case for a number of reasons, for |
|
1685 example because you are behind a firewall or don't have network |
|
1686 access), you need to do a manual installation. First of all you |
|
1687 need to fetch the modules from CPAN search |
|
1688 |
|
1689 http://search.cpan.org/ |
|
1690 |
|
1691 The following modules are required |
|
1692 |
|
1693 DBI |
|
1694 Data::ShowTable |
|
1695 DBD::mysql |
|
1696 |
|
1697 Then enter the following commands (note - versions are just examples): |
|
1698 |
|
1699 gzip -cd DBI-(version).tar.gz | tar xf - |
|
1700 cd DBI-(version) |
|
1701 perl Makefile.PL |
|
1702 make |
|
1703 make test |
|
1704 make install |
|
1705 |
|
1706 cd .. |
|
1707 gzip -cd Data-ShowTable-(version).tar.gz | tar xf - |
|
1708 cd Data-ShowTable-3.3 |
|
1709 perl Makefile.PL |
|
1710 make |
|
1711 make install |
|
1712 |
|
1713 cd .. |
|
1714 gzip -cd DBD-mysql-(version)-tar.gz | tar xf - |
|
1715 cd DBD-mysql-(version) |
|
1716 perl Makefile.PL |
|
1717 make |
|
1718 make test |
|
1719 make install |
|
1720 |
|
1721 During "perl Makefile.PL" you will be prompted some questions. |
|
1722 Other questions are the directories with header files and libraries. |
|
1723 For example, of your file F<mysql.h> is in F</usr/include/mysql/mysql.h>, |
|
1724 then enter the header directory F</usr>, likewise for |
|
1725 F</usr/lib/mysql/libmysqlclient.a> or F</usr/lib/libmysqlclient.so>. |
|
1726 |
|
1727 |
|
1728 =head1 WIN32 INSTALLATION |
|
1729 |
|
1730 If you are using ActivePerl, you may use ppm to install DBD-mysql. |
|
1731 For Perl 5.6, upgrade to Build 623 or later, then it is sufficient |
|
1732 to run |
|
1733 |
|
1734 ppm install DBI |
|
1735 ppm install DBD::mysql |
|
1736 |
|
1737 If you need an HTTP proxy, you might need to set the environment |
|
1738 variable http_proxy, for example like this: |
|
1739 |
|
1740 set http_proxy=http://myproxy.com:8080/ |
|
1741 |
|
1742 As of this writing, DBD::mysql is missing in the ActivePerl 5.8.0 |
|
1743 repository. However, Randy Kobes has kindly donated an own |
|
1744 distribution and the following might succeed: |
|
1745 |
|
1746 ppm install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd |
|
1747 |
|
1748 Otherwise you definitely *need* a C compiler. And it *must* be the same |
|
1749 compiler that was being used for compiling Perl itself. If you don't |
|
1750 have a C compiler, the file README.win32 from the Perl source |
|
1751 distribution tells you where to obtain freely distributable C compilers |
|
1752 like egcs or gcc. The Perl sources are available via CPAN search |
|
1753 |
|
1754 http://search.cpan.org |
|
1755 |
|
1756 I recommend using the win32clients package for installing DBD::mysql |
|
1757 under Win32, available for download on www.tcx.se. The following steps |
|
1758 have been required for me: |
|
1759 |
|
1760 =over |
|
1761 |
|
1762 =item - |
|
1763 |
|
1764 The current Perl versions (5.6, as of this writing) do have a problem |
|
1765 with detecting the C libraries. I recommend to apply the following |
|
1766 patch: |
|
1767 |
|
1768 *** c:\Perl\lib\ExtUtils\Liblist.pm.orig Sat Apr 15 20:03:40 2000 |
|
1769 --- c:\Perl\lib\ExtUtils\Liblist.pm Sat Apr 15 20:03:45 2000 |
|
1770 *************** |
|
1771 *** 230,235 **** |
|
1772 --- 230,239 ---- |
|
1773 # add "$Config{installarchlib}/CORE" to default search path |
|
1774 push @libpath, "$Config{installarchlib}/CORE"; |
|
1775 |
|
1776 + if ($VC and exists($ENV{LIB}) and defined($ENV{LIB})) { |
|
1777 + push(@libpath, split(/;/, $ENV{LIB})); |
|
1778 + } |
|
1779 + |
|
1780 foreach (Text::ParseWords::quotewords('\s+', 0, $potential_libs)){ |
|
1781 |
|
1782 $thislib = $_; |
|
1783 |
|
1784 =item - |
|
1785 |
|
1786 Extract sources into F<C:\>. This will create a directory F<C:\mysql> |
|
1787 with subdirectories include and lib. |
|
1788 |
|
1789 IMPORTANT: Make sure this subdirectory is not shared by other TCX |
|
1790 files! In particular do *not* store the MySQL server in the same |
|
1791 directory. If the server is already installed in F<C:\mysql>, |
|
1792 choose a location like F<C:\tmp>, extract the win32clients there. |
|
1793 Note that you can remove this directory entirely once you have |
|
1794 installed DBD::mysql. |
|
1795 |
|
1796 =item - |
|
1797 |
|
1798 Extract the DBD::mysql sources into another directory, for |
|
1799 example F<C:\src\siteperl> |
|
1800 |
|
1801 =item - |
|
1802 |
|
1803 Open a DOS shell and change directory to F<C:\src\siteperl>. |
|
1804 |
|
1805 =item - |
|
1806 |
|
1807 The next step is only required if you repeat building the modules: Make |
|
1808 sure that you have a clean build tree by running |
|
1809 |
|
1810 nmake realclean |
|
1811 |
|
1812 If you don't have VC++, replace nmake with your flavour of make. If |
|
1813 error messages are reported in this step, you may safely ignore them. |
|
1814 |
|
1815 =item - |
|
1816 |
|
1817 Run |
|
1818 |
|
1819 perl Makefile.PL |
|
1820 |
|
1821 which will prompt you for some settings. The really important ones are: |
|
1822 |
|
1823 Which DBMS do you want to use? |
|
1824 |
|
1825 enter a 1 here (MySQL only), and |
|
1826 |
|
1827 Where is your mysql installed? Please tell me the directory that |
|
1828 contains the subdir include. |
|
1829 |
|
1830 where you have to enter the win32clients directory, for example |
|
1831 F<C:\mysql> or F<C:\tmp\mysql>. |
|
1832 |
|
1833 =item - |
|
1834 |
|
1835 Continued in the usual way: |
|
1836 |
|
1837 nmake |
|
1838 nmake install |
|
1839 |
|
1840 =back |
|
1841 |
|
1842 If you want to create a PPM package for the ActiveState Perl version, then |
|
1843 modify the above steps as follows: Run |
|
1844 |
|
1845 perl Makefile.PL NAME=DBD-mysql BINARY_LOCATION=DBD-mysql.tar.gz |
|
1846 nmake ppd |
|
1847 nmake |
|
1848 |
|
1849 Once that is done, use tar and gzip (for example those from the CygWin32 |
|
1850 distribution) to create an archive: |
|
1851 |
|
1852 mkdir x86 |
|
1853 tar cf x86/DBD-mysql.tar blib |
|
1854 gzip x86/DBD-mysql.tar |
|
1855 |
|
1856 Put the files x86/DBD-mysql.tar.gz and DBD-mysql.ppd onto some WWW server |
|
1857 and install them by typing |
|
1858 |
|
1859 install http://your.server.name/your/directory/DBD-mysql.ppd |
|
1860 |
|
1861 in the PPM program. |
|
1862 |
|
1863 |
|
1864 =head1 AUTHORS |
|
1865 |
|
1866 The current version of B<DBD::mysql> is almost completely written |
|
1867 by Jochen Wiedmann, and is now being maintained by |
|
1868 Patrick Galbraith (I<patg@mysql.com>). |
|
1869 The first version's author was Alligator Descartes, who was aided |
|
1870 and abetted by Gary Shea, Andreas König and Tim Bunce amongst others. |
|
1871 |
|
1872 The B<Mysql> module was originally written by Andreas König |
|
1873 <koenig@kulturbox.de>. The current version, mainly an emulation |
|
1874 layer, is from Jochen Wiedmann. |
|
1875 |
|
1876 |
|
1877 =head1 COPYRIGHT |
|
1878 |
|
1879 |
|
1880 This module is |
|
1881 Large Portions Copyright (c) 2004-2006 MySQL Patrick Galbraith, Alexey Stroganov, |
|
1882 Large Portions Copyright (c) 2003-2005 Rudolf Lippan; Large Portions |
|
1883 Copyright (c) 1997-2003 Jochen Wiedmann, with code portions |
|
1884 Copyright (c)1994-1997 their original authors This module is |
|
1885 released under the same license as Perl itself. See the Perl README |
|
1886 for details. |
|
1887 |
|
1888 |
|
1889 =head1 MAILING LIST SUPPORT |
|
1890 |
|
1891 This module is maintained and supported on a mailing list, |
|
1892 |
|
1893 perl@lists.mysql.com |
|
1894 |
|
1895 To subscribe to this list, go to |
|
1896 |
|
1897 http://lists.mysql.com/perl?sub=1 |
|
1898 |
|
1899 Mailing list archives are available at |
|
1900 |
|
1901 http://lists.mysql.com/perl |
|
1902 |
|
1903 Additionally you might try the dbi-user mailing list for questions about |
|
1904 DBI and its modules in general. Subscribe via |
|
1905 |
|
1906 dbi-users-subscribe@perl.org |
|
1907 |
|
1908 Mailing list archives are at |
|
1909 |
|
1910 http://groups.google.com/group/perl.dbi.users?hl=en&lr= |
|
1911 |
|
1912 Also, the main DBI site is at |
|
1913 |
|
1914 http://dbi.perl.org/ |
|
1915 |
|
1916 =head1 ADDITIONAL DBI INFORMATION |
|
1917 |
|
1918 Additional information on the DBI project can be found on the World |
|
1919 Wide Web at the following URL: |
|
1920 |
|
1921 http://dbi.perl.org |
|
1922 |
|
1923 where documentation, pointers to the mailing lists and mailing list |
|
1924 archives and pointers to the most current versions of the modules can |
|
1925 be used. |
|
1926 |
|
1927 Information on the DBI interface itself can be gained by typing: |
|
1928 |
|
1929 perldoc DBI |
|
1930 |
|
1931 right now! |
|
1932 |
|
1933 |
|
1934 =head1 BUG REPORTING, ENHANCEMENT/FEATURE REQUESTS |
|
1935 |
|
1936 Please report bugs, including all the information needed |
|
1937 such as DBD::mysql version, MySQL version, OS type/version, etc |
|
1938 to this link: |
|
1939 |
|
1940 http://bugs.mysql.com/ |
|
1941 |
|
1942 |
|
1943 =cut |
|
1944 |
|
1945 |