Commit | Line | Data |
---|---|---|
86530b38 AT |
1 | # Our beloved Emacs will give us -*- perl -*- mode :-) |
2 | # | |
3 | # $Id: dbd.pm.in,v 1.6 1999/10/21 20:05:43 joe Exp $ | |
4 | # | |
5 | # Copyright (c) 1994,1995,1996,1997 Alligator Descartes, Tim Bunce | |
6 | # | |
7 | # You may distribute under the terms of either the GNU General Public | |
8 | # License or the Artistic License, as specified in the Perl README file. | |
9 | ||
10 | package DBD::mysql; | |
11 | use strict; | |
12 | use vars qw(@ISA $VERSION $err $errstr $drh); | |
13 | ||
14 | use DBI (); | |
15 | use DynaLoader(); | |
16 | use Carp (); | |
17 | @ISA = qw(DynaLoader); | |
18 | ||
19 | $VERSION = '2.0419'; | |
20 | ||
21 | bootstrap DBD::mysql $VERSION; | |
22 | ||
23 | ||
24 | $err = 0; # holds error code for DBI::err | |
25 | $errstr = ""; # holds error string for DBI::errstr | |
26 | $drh = undef; # holds driver handle once initialised | |
27 | ||
28 | sub driver{ | |
29 | return $drh if $drh; | |
30 | my($class, $attr) = @_; | |
31 | ||
32 | $class .= "::dr"; | |
33 | ||
34 | # not a 'my' since we use it above to prevent multiple drivers | |
35 | $drh = DBI::_new_drh($class, { 'Name' => 'mysql', | |
36 | 'Version' => $VERSION, | |
37 | 'Err' => \$DBD::mysql::err, | |
38 | 'Errstr' => \$DBD::mysql::errstr, | |
39 | 'Attribution' => 'DBD::mysql by Jochen Wiedmann' | |
40 | }); | |
41 | ||
42 | $drh; | |
43 | } | |
44 | ||
45 | sub _OdbcParse($$$) { | |
46 | my($class, $dsn, $hash, $args) = @_; | |
47 | my($var, $val); | |
48 | if (!defined($dsn)) { | |
49 | return; | |
50 | } | |
51 | while (length($dsn)) { | |
52 | if ($dsn =~ /([^:;]*)[:;](.*)/) { | |
53 | $val = $1; | |
54 | $dsn = $2; | |
55 | } else { | |
56 | $val = $dsn; | |
57 | $dsn = ''; | |
58 | } | |
59 | if ($val =~ /([^=]*)=(.*)/) { | |
60 | $var = $1; | |
61 | $val = $2; | |
62 | if ($var eq 'hostname' || $var eq 'host') { | |
63 | $hash->{'host'} = $val; | |
64 | } elsif ($var eq 'db' || $var eq 'dbname') { | |
65 | $hash->{'database'} = $val; | |
66 | } else { | |
67 | $hash->{$var} = $val; | |
68 | } | |
69 | } else { | |
70 | foreach $var (@$args) { | |
71 | if (!defined($hash->{$var})) { | |
72 | $hash->{$var} = $val; | |
73 | last; | |
74 | } | |
75 | } | |
76 | } | |
77 | } | |
78 | } | |
79 | ||
80 | sub _OdbcParseHost ($$) { | |
81 | my($class, $dsn) = @_; | |
82 | my($hash) = {}; | |
83 | $class->_OdbcParse($dsn, $hash, ['host', 'port']); | |
84 | ($hash->{'host'}, $hash->{'port'}); | |
85 | } | |
86 | ||
87 | sub AUTOLOAD { | |
88 | my ($meth) = $DBD::mysql::AUTOLOAD; | |
89 | my ($smeth) = $meth; | |
90 | $smeth =~ s/(.*)\:\://; | |
91 | ||
92 | my $val = constant($smeth, @_ ? $_[0] : 0); | |
93 | if ($! == 0) { eval "sub $meth { $val }"; return $val; } | |
94 | ||
95 | Carp::croak "$meth: Not defined"; | |
96 | } | |
97 | ||
98 | 1; | |
99 | ||
100 | ||
101 | package DBD::mysql::dr; # ====== DRIVER ====== | |
102 | use strict; | |
103 | ||
104 | sub connect { | |
105 | my($drh, $dsn, $username, $password, $attrhash) = @_; | |
106 | my($port); | |
107 | my($cWarn); | |
108 | ||
109 | # Avoid warnings for undefined values | |
110 | $username ||= ''; | |
111 | $password ||= ''; | |
112 | ||
113 | # create a 'blank' dbh | |
114 | my($this, $privateAttrHash); | |
115 | $privateAttrHash = { | |
116 | 'Name' => $dsn, | |
117 | 'user' => $username, | |
118 | 'password' => $password | |
119 | }; | |
120 | ||
121 | DBD::mysql->_OdbcParse($dsn, $privateAttrHash, | |
122 | ['database', 'host', 'port']); | |
123 | ||
124 | if (!defined($this = DBI::_new_dbh($drh, {'Name' => $dsn}, | |
125 | $privateAttrHash))) { | |
126 | return undef; | |
127 | } | |
128 | ||
129 | # Call msqlConnect func in mSQL.xs file | |
130 | # and populate internal handle data. | |
131 | DBD::mysql::db::_login($this, $dsn, $username, $password) | |
132 | or $this = undef; | |
133 | $this; | |
134 | } | |
135 | ||
136 | sub data_sources { | |
137 | my($self) = shift; | |
138 | my(@dsn) = $self->func('', '_ListDBs'); | |
139 | my($i); | |
140 | for ($i = 0; $i < @dsn; $i++) { | |
141 | $dsn[$i] = "DBI:mysql:$dsn[$i]"; | |
142 | } | |
143 | @dsn; | |
144 | } | |
145 | ||
146 | sub admin { | |
147 | my($drh) = shift; | |
148 | my($command) = shift; | |
149 | my($dbname) = ($command eq 'createdb' || $command eq 'dropdb') ? | |
150 | shift : ''; | |
151 | my($host, $port) = DBD::mysql->_OdbcParseHost(shift(@_) || ''); | |
152 | my($user) = shift || ''; | |
153 | my($password) = shift || ''; | |
154 | ||
155 | $drh->func(undef, $command, | |
156 | $dbname || '', | |
157 | $host || '', | |
158 | $port || '', | |
159 | $user, $password, '_admin_internal'); | |
160 | } | |
161 | ||
162 | package DBD::mysql::db; # ====== DATABASE ====== | |
163 | use strict; | |
164 | ||
165 | %DBD::mysql::db::db2ANSI = ("INT" => "INTEGER", | |
166 | "CHAR" => "CHAR", | |
167 | "REAL" => "REAL", | |
168 | "IDENT" => "DECIMAL" | |
169 | ); | |
170 | ||
171 | ### ANSI datatype mapping to mSQL datatypes | |
172 | %DBD::mysql::db::ANSI2db = ("CHAR" => "CHAR", | |
173 | "VARCHAR" => "CHAR", | |
174 | "LONGVARCHAR" => "CHAR", | |
175 | "NUMERIC" => "INTEGER", | |
176 | "DECIMAL" => "INTEGER", | |
177 | "BIT" => "INTEGER", | |
178 | "TINYINT" => "INTEGER", | |
179 | "SMALLINT" => "INTEGER", | |
180 | "INTEGER" => "INTEGER", | |
181 | "BIGINT" => "INTEGER", | |
182 | "REAL" => "REAL", | |
183 | "FLOAT" => "REAL", | |
184 | "DOUBLE" => "REAL", | |
185 | "BINARY" => "CHAR", | |
186 | "VARBINARY" => "CHAR", | |
187 | "LONGVARBINARY" => "CHAR", | |
188 | "DATE" => "CHAR", | |
189 | "TIME" => "CHAR", | |
190 | "TIMESTAMP" => "CHAR" | |
191 | ); | |
192 | ||
193 | sub prepare { | |
194 | my($dbh, $statement, $attribs)= @_; | |
195 | ||
196 | # create a 'blank' dbh | |
197 | my $sth = DBI::_new_sth($dbh, {'Statement' => $statement}); | |
198 | ||
199 | # Populate internal handle data. | |
200 | if (!DBD::mysql::st::_prepare($sth, $statement, $attribs)) { | |
201 | $sth = undef; | |
202 | } | |
203 | ||
204 | $sth; | |
205 | } | |
206 | ||
207 | sub db2ANSI { | |
208 | my $self = shift; | |
209 | my $type = shift; | |
210 | return $DBD::mysql::db::db2ANSI{"$type"}; | |
211 | } | |
212 | ||
213 | sub ANSI2db { | |
214 | my $self = shift; | |
215 | my $type = shift; | |
216 | return $DBD::mysql::db::ANSI2db{"$type"}; | |
217 | } | |
218 | ||
219 | sub admin { | |
220 | my($dbh) = shift; | |
221 | my($command) = shift; | |
222 | my($dbname) = ($command eq 'createdb' || $command eq 'dropdb') ? | |
223 | shift : ''; | |
224 | $dbh->{'Driver'}->func($dbh, $command, $dbname, '', '', '', | |
225 | '_admin_internal'); | |
226 | } | |
227 | ||
228 | sub _SelectDB ($$) { | |
229 | die "_SelectDB is removed from this module; use DBI->connect instead."; | |
230 | } | |
231 | ||
232 | { | |
233 | my $names = ['TABLE_CAT', 'TABLE_SCHEM', 'TABLE_NAME', | |
234 | 'TABLE_TYPE', 'REMARKS']; | |
235 | ||
236 | sub table_info ($) { | |
237 | my $dbh = shift; | |
238 | my @tables = map { [ undef, undef, $_, 'TABLE', undef ] | |
239 | } $dbh->func('_ListTables'); | |
240 | my $dbh2; | |
241 | if (!($dbh2 = $dbh->{'~dbd_driver~_sponge_dbh'})) { | |
242 | $dbh2 = $dbh->{'~dbd_driver~_sponge_dbh'} = | |
243 | DBI->connect("DBI:Sponge:"); | |
244 | if (!$dbh2) { | |
245 | DBI::set_err($dbh, 1, $DBI::errstr); | |
246 | return undef; | |
247 | } | |
248 | } | |
249 | my $sth = $dbh2->prepare("LISTTABLES", { 'rows' => \@tables, | |
250 | 'NAME' => $names }); | |
251 | if (!$sth) { | |
252 | DBI::set_err($sth, $dbh2->err(), $dbh2->errstr()); | |
253 | } | |
254 | $sth; | |
255 | } | |
256 | } | |
257 | ||
258 | package DBD::mysql::st; # ====== STATEMENT ====== | |
259 | use strict; | |
260 | ||
261 | 1; | |
262 | ||
263 | ||
264 | __END__ | |
265 | ||
266 | =head1 NAME | |
267 | ||
268 | DBD::mSQL / DBD::mysql - mSQL and mysql drivers for the Perl5 Database | |
269 | Interface (DBI) | |
270 | ||
271 | =head1 SYNOPSIS | |
272 | ||
273 | use DBI; | |
274 | ||
275 | $driver = "mSQL"; # or "mSQL1"; | |
276 | $dsn = "DBI:$driver:database=$database;host=$hostname"; | |
277 | ||
278 | $dbh = DBI->connect($dsn, undef, undef); | |
279 | ||
280 | or | |
281 | ||
282 | $driver = "mysql"; | |
283 | $dsn = "DBI:$driver:database=$database;host=$hostname;port=$port"; | |
284 | ||
285 | $dbh = DBI->connect($dsn, $user, $password); | |
286 | ||
287 | ||
288 | $drh = DBI->install_driver("mysql"); | |
289 | @databases = $drh->func($host, $port, '_ListDBs'); | |
290 | @tables = $dbh->func( '_ListTables' ); | |
291 | ||
292 | $sth = $dbh->prepare("SELECT * FROM foo WHERE bla"); | |
293 | or | |
294 | $sth = $dbh->prepare("LISTFIELDS $table"); | |
295 | or | |
296 | $sth = $dbh->prepare("LISTINDEX $table $index"); | |
297 | $sth->execute; | |
298 | $numRows = $sth->rows; | |
299 | $numFields = $sth->{'NUM_OF_FIELDS'}; | |
300 | $sth->finish; | |
301 | ||
302 | $rc = $drh->func('createdb', $database, $host, $user, $password, 'admin'); | |
303 | $rc = $drh->func('dropdb', $database, $host, $user, $password, 'admin'); | |
304 | $rc = $drh->func('shutdown', $host, $user, $password, 'admin'); | |
305 | $rc = $drh->func('reload', $host, $user, $password, 'admin'); | |
306 | ||
307 | $rc = $dbh->func('createdb', $database, 'admin'); | |
308 | $rc = $dbh->func('dropdb', $database, 'admin'); | |
309 | $rc = $dbh->func('shutdown', 'admin'); | |
310 | $rc = $dbh->func('reload', 'admin'); | |
311 | ||
312 | ||
313 | =head1 EXAMPLE | |
314 | ||
315 | #!/usr/bin/perl | |
316 | ||
317 | use strict; | |
318 | use DBI(); | |
319 | ||
320 | # Connect to the database. | |
321 | my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost", | |
322 | "joe", "joe's password", | |
323 | {'RaiseError' => 1}); | |
324 | ||
325 | # Drop table 'foo'. This may fail, if 'foo' doesn't exist. | |
326 | # Thus we put an eval around it. | |
327 | eval { $dbh->do("DROP TABLE foo") }; | |
328 | print "Dropping foo failed: $@\n" if $@; | |
329 | ||
330 | # Create a new table 'foo'. This must not fail, thus we don't | |
331 | # catch errors. | |
332 | $dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))"); | |
333 | ||
334 | # INSERT some data into 'foo'. We are using $dbh->quote() for | |
335 | # quoting the name. | |
336 | $dbh->do("INSERT INTO foo VALUES (1, " . $dbh->quote("Tim") . ")"); | |
337 | ||
338 | # Same thing, but using placeholders | |
339 | $dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen"); | |
340 | ||
341 | # Now retrieve data from the table. | |
342 | my $sth = $dbh->prepare("SELECT * FROM foo"); | |
343 | $sth->execute(); | |
344 | while (my $ref = $sth->fetchrow_hashref()) { | |
345 | print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n"; | |
346 | } | |
347 | $sth->finish(); | |
348 | ||
349 | # Disconnect from the database. | |
350 | $dbh->disconnect(); | |
351 | ||
352 | ||
353 | =head1 DESCRIPTION | |
354 | ||
355 | B<DBD::mysql> and B<DBD::mSQL> are the Perl5 Database Interface drivers | |
356 | for the mysql, mSQL 1.I<x> and mSQL 2.I<x> databases. The drivers are part | |
357 | of the I<Msql-Mysql-modules> package. | |
358 | ||
359 | In other words: DBD::mSQL and DBD::mysql are an interface between the Perl | |
360 | programming language and the mSQL or mysql programming API that come with | |
361 | the mSQL any mysql relational database management systems. Most functions | |
362 | provided by the respective programming API's are supported. Some | |
363 | rarely used functions are missing, mainly because noone ever requested | |
364 | them. :-) | |
365 | ||
366 | In what follows we first discuss the use of DBD::mysql and DBD::mSQL, | |
367 | because this is what you will need the most. For installation, see the | |
368 | sections on L<INSTALLATION>, L<WIN32 INSTALLATION>, L<KNOWN PROBLEMS> | |
369 | and L<KNOWN BUGS> below. See L<EXAMPLE> for a simple example above. | |
370 | ||
371 | From perl you activate the interface with the statement | |
372 | ||
373 | use DBI; | |
374 | ||
375 | After that you can connect to multiple mSQL or MySQL database servers | |
376 | and send multiple queries to any of them via a simple object oriented | |
377 | interface. Two types of objects are available: database handles and | |
378 | statement handles. Perl returns a database handle to the connect | |
379 | method like so: | |
380 | ||
381 | $dbh = DBI->connect("DBI:mSQL:database=$db;host=$host", | |
382 | undef, undef, {RaiseError => 1}); | |
383 | ||
384 | or | |
385 | ||
386 | $dbh = DBI->connect("DBI:mysql:database=$db;host=$host", | |
387 | $user, $password, {RaiseError => 1}); | |
388 | ||
389 | Once you have connected to a database, you can can execute SQL | |
390 | statements with: | |
391 | ||
392 | my $query = sprintf("INSERT INTO foo VALUES (%d, %s)", | |
393 | $number, $dbh->quote("name")); | |
394 | $dbh->do($query); | |
395 | ||
396 | See L<DBI(3)> for details on the quote and do methods. An alternative | |
397 | approach is | |
398 | ||
399 | $dbh->do("INSERT INTO foo VALUES (?, ?)", undef, | |
400 | $number, $name); | |
401 | ||
402 | in which case the quote method is executed automatically. See also | |
403 | the bind_param method in L<DBI(3)>. See L<DATABASE HANDLES> below | |
404 | for more details on database handles. | |
405 | ||
406 | If you want to retrieve results, you need to create a so-called | |
407 | statement handle with: | |
408 | ||
409 | $sth = $dbh->prepare("SELECT * FROM $table"); | |
410 | $sth->execute(); | |
411 | ||
412 | This statement handle can be used for multiple things. First of all | |
413 | you can retreive a row of data: | |
414 | ||
415 | my $row = $sth->fetchow_hashref(); | |
416 | ||
417 | If your table has columns ID and NAME, then $row will be hash ref with | |
418 | keys ID and NAME. See L<STATEMENT HANDLES> below for more details on | |
419 | statement handles. | |
420 | ||
421 | But now for a more formal approach: | |
422 | ||
423 | ||
424 | =head2 Class Methods | |
425 | ||
426 | =over 4 | |
427 | ||
428 | =item B<connect> | |
429 | ||
430 | use DBI; | |
431 | ||
432 | $driver = "mSQL"; # or "mSQL1" | |
433 | $dsn = "DBI:$driver:$database"; | |
434 | $dsn = "DBI:$driver:database=$database;host=$hostname"; | |
435 | ||
436 | $dbh = DBI->connect($dsn, undef, undef); | |
437 | ||
438 | or | |
439 | ||
440 | $dsn = "DBI:mysql:$database"; | |
441 | $dsn = "DBI:mysql:database=$database;host=$hostname"; | |
442 | $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"; | |
443 | ||
444 | $dbh = DBI->connect($dsn, $user, $password); | |
445 | ||
446 | A C<database> must always be specified. | |
447 | ||
448 | =over 8 | |
449 | ||
450 | =item host | |
451 | ||
452 | =item port | |
453 | ||
454 | The hostname, if not specified or specified as '', will default to an | |
455 | mysql or mSQL daemon running on the local machine on the default port | |
456 | for the UNIX socket. | |
457 | ||
458 | Should the mysql or mSQL daemon be running on a non-standard port number, | |
459 | you may explicitly state the port number to connect to in the C<hostname> | |
460 | argument, by concatenating the I<hostname> and I<port number> together | |
461 | separated by a colon ( C<:> ) character or by using the C<port> argument. | |
462 | This doesn't work for mSQL 2: You have to create an alternative config | |
463 | file and load it using the msql_configfile attribute, see below. | |
464 | ||
465 | ||
466 | =item mysql_client_found_rows | |
467 | ||
468 | Enables (TRUE value) or disables (FALSE value) the flag CLIENT_FOUND_ROWS | |
469 | while connecting to the MySQL server. This has a somewhat funny effect: | |
470 | Without mysql_client_found_rows, if you perform a query like | |
471 | ||
472 | UPDATE $table SET id = 1 WHERE id = 1 | |
473 | ||
474 | then the MySQL engine will always return 0, because no rows have changed. | |
475 | With mysql_client_found_rows however, it will return the number of rows | |
476 | that have an id 1, as some people are expecting. (At least for compatibility | |
477 | to other engines.) | |
478 | ||
479 | By default this flag is disabled. However, you can enable it by default, | |
480 | when installing the Msql-Mysql-modules with | |
481 | ||
482 | perl Makefile.PL --config --mysql-use-client-found-rows | |
483 | make | |
484 | make install | |
485 | ||
486 | =item mysql_compression | |
487 | ||
488 | As of MySQL 3.22.3, a new feature is supported: If your DSN contains | |
489 | the option "mysql_compression=1", then the communication between client | |
490 | and server will be compressed. | |
491 | ||
492 | =item mysql_connect_timeout | |
493 | ||
494 | If your DSN contains the option "mysql_connect_timeout=##", the connect | |
495 | request to the server will timeout if it has not been successful after | |
496 | the given number of seconds. | |
497 | ||
498 | =item mysql_read_default_file | |
499 | ||
500 | =item mysql_read_default_group | |
501 | ||
502 | These options can be used to read a config file like /etc/my.cnf or | |
503 | ~/.my.cnf. By default MySQL's C client library doesn't use any config | |
504 | files unlike the client programs (mysql, mysqladmin, ...) that do, but | |
505 | outside of the C client library. Thus you need to explicitly request | |
506 | reading a config file, as in | |
507 | ||
508 | $dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf"; | |
509 | $dbh = DBI->connect($dsn, $user, $password) | |
510 | ||
511 | The option mysql_read_default_group can be used to specify the default | |
512 | group in the config file: Usually this is the I<client> group, but | |
513 | see the following example: | |
514 | ||
515 | [perl] | |
516 | host=perlhost | |
517 | ||
518 | [client] | |
519 | host=localhost | |
520 | ||
521 | If you read this config file, then you'll be typically connected to | |
522 | I<localhost>. However, by using | |
523 | ||
524 | $dsn = "DBI:mysql:test;mysql_read_default_group=perl;" | |
525 | . "mysql_read_default_file=/home/joe/my.cnf"; | |
526 | $dbh = DBI->connect($dsn, $user, $password); | |
527 | ||
528 | you'll be connected to I<perlhost>. Note that if you specify a | |
529 | default group and do not specify a file, then the default config | |
530 | files will all be read. See the (missing :-) documentation of | |
531 | the C function mysql_options() for details. | |
532 | ||
533 | =item mysql_socket | |
534 | ||
535 | As of MySQL 3.21.15, it is possible to choose the Unix socket that is | |
536 | used for connecting to the server. This is done, for example, with | |
537 | ||
538 | mysql_socket=/dev/mysql | |
539 | ||
540 | Usually there's no need for this option, unless you are using another | |
541 | location for the socket than that built into the client. | |
542 | ||
543 | ||
544 | =back | |
545 | ||
546 | =back | |
547 | ||
548 | ||
549 | =head2 Private MetaData Methods | |
550 | ||
551 | =over 4 | |
552 | ||
553 | =item B<ListDBs> | |
554 | ||
555 | my $drh = DBI->install_driver("mysql"); | |
556 | @dbs = $drh->func("$hostname:$port", '_ListDBs'); | |
557 | @dbs = $drh->func($hostname, $port, '_ListDBs'); | |
558 | @dbs = $dbh->func('_ListDBs'); | |
559 | ||
560 | Returns a list of all databases managed by the mysql daemon or | |
561 | mSQL daemon running on C<$hostname>, port C<$port>. This method | |
562 | is rarely needed for databases running on C<localhost>: You should | |
563 | use the portable method | |
564 | ||
565 | @dbs = DBI->data_sources("mysql"); | |
566 | ||
567 | or | |
568 | ||
569 | @dbs = DBI->data_sources("mSQL"); | |
570 | ||
571 | whenever possible. It is a design problem of this method, that there's | |
572 | no way of supplying a host name or port number to C<data_sources>, that's | |
573 | the only reason why we still support C<ListDBs>. :-( | |
574 | ||
575 | ||
576 | =item B<ListTables> | |
577 | ||
578 | *WARNING*: This method is obsolete due to DBI's $dbh->table_info(). | |
579 | ||
580 | @tables = $dbh->func('_ListTables'); | |
581 | ||
582 | Once connected to the desired database on the desired mysql or mSQL | |
583 | mSQL daemon with the C<DBI->connect()> method, we may extract a list | |
584 | of the tables that have been created within that database. | |
585 | ||
586 | C<ListTables> returns an array containing the names of all the tables | |
587 | present within the selected database. If no tables have been created, | |
588 | an empty list is returned. | |
589 | ||
590 | @tables = $dbh->func( '_ListTables' ); | |
591 | foreach $table ( @tables ) { | |
592 | print "Table: $table\n"; | |
593 | } | |
594 | ||
595 | ||
596 | ||
597 | =head2 Server Administration | |
598 | ||
599 | =over 4 | |
600 | ||
601 | =item admin | |
602 | ||
603 | $rc = $drh->func("createdb", $dbname, [host, user, password,], 'admin'); | |
604 | $rc = $drh->func("dropdb", $dbname, [host, user, password,], 'admin'); | |
605 | $rc = $drh->func("shutdown", [host, user, password,], 'admin'); | |
606 | $rc = $drh->func("reload", [host, user, password,], 'admin'); | |
607 | ||
608 | or | |
609 | ||
610 | $rc = $dbh->func("createdb", $dbname, 'admin'); | |
611 | $rc = $dbh->func("dropdb", $dbname, 'admin'); | |
612 | $rc = $dbh->func("shutdown", 'admin'); | |
613 | $rc = $dbh->func("reload", 'admin'); | |
614 | ||
615 | For server administration you need a server connection. For obtaining | |
616 | this connection you have two options: Either use a driver handle (drh) | |
617 | and supply the appropriate arguments (host, defaults localhost, user, | |
618 | defaults to '' and password, defaults to ''). A driver handle can be | |
619 | obtained with | |
620 | ||
621 | $drh = DBI->install_driver('mysql'); | |
622 | ||
623 | Otherwise reuse the existing connection of a database handle (dbh). | |
624 | ||
625 | There's only one function available for administrative purposes, comparable | |
626 | to the m(y)sqladmin programs. The command being execute depends on the | |
627 | first argument: | |
628 | ||
629 | =over 8 | |
630 | ||
631 | =item createdb | |
632 | ||
633 | Creates the database $dbname. Equivalent to "m(y)sqladmin create $dbname". | |
634 | ||
635 | =item dropdb | |
636 | ||
637 | Drops the database $dbname. Equivalent to "m(y)sqladmin drop $dbname". | |
638 | ||
639 | It should be noted that database deletion is | |
640 | I<not prompted for> in any way. Nor is it undo-able from DBI. | |
641 | ||
642 | Once you issue the dropDB() method, the database will be gone! | |
643 | ||
644 | These method should be used at your own risk. | |
645 | ||
646 | =item shutdown | |
647 | ||
648 | Silently shuts down the database engine. (Without prompting!) | |
649 | Equivalent to "m(y)sqladmin shutdown". | |
650 | ||
651 | =item reload | |
652 | ||
653 | Reloads the servers configuration files and/or tables. This can be particularly | |
654 | important if you modify access privileges or create new users. | |
655 | ||
656 | =back | |
657 | ||
658 | ||
659 | =back | |
660 | ||
661 | ||
662 | =head1 DATABASE HANDLES | |
663 | ||
664 | The DBD::mysql driver supports the following attributes of database | |
665 | handles (read only): | |
666 | ||
667 | $infoString = $dbh->{'info'}; | |
668 | $threadId = $dbh->{'thread_id'}; | |
669 | $insertId = $dbh->{'mysql_insertid'} | |
670 | ||
671 | These correspond to mysql_info(), mysql_thread_id() and mysql_insertid(), | |
672 | respectively. | |
673 | ||
674 | ||
675 | =head1 STATEMENT HANDLES | |
676 | ||
677 | The statement handles of DBD::mysql and DBD::mSQL support a number | |
678 | of attributes. You access these by using, for example, | |
679 | ||
680 | my $numFields = $sth->{'NUM_OF_FIELDS'}; | |
681 | ||
682 | Note, that most attributes are valid only after a successfull I<execute>. | |
683 | An C<undef> value will returned in that case. The most important exception | |
684 | is the C<mysql_use_result> attribute: This forces the driver to use | |
685 | mysql_use_result rather than mysql_store_result. The former is faster | |
686 | and less memory consuming, but tends to block other processes. (That's why | |
687 | mysql_store_result is the default.) | |
688 | ||
689 | To set the C<mysql_use_result> attribute, use either of the following: | |
690 | ||
691 | my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1}); | |
692 | ||
693 | or | |
694 | ||
695 | my $sth = $dbh->prepare("QUERY"); | |
696 | $sth->{"mysql_use_result"} = 1; | |
697 | ||
698 | Column dependent attributes, for example I<NAME>, the column names, | |
699 | are returned as a reference to an array. The array indices are | |
700 | corresponding to the indices of the arrays returned by I<fetchrow> | |
701 | and similar methods. For example the following code will print a | |
702 | header of table names together with all rows: | |
703 | ||
704 | my $sth = $dbh->prepare("SELECT * FROM $table"); | |
705 | if (!$sth) { | |
706 | die "Error:" . $dbh->errstr . "\n"; | |
707 | } | |
708 | if (!$sth->execute) { | |
709 | die "Error:" . $sth->errstr . "\n"; | |
710 | } | |
711 | my $names = $sth->{'NAME'}; | |
712 | my $numFields = $sth->{'NUM_OF_FIELDS'}; | |
713 | for (my $i = 0; $i < $numFields; $i++) { | |
714 | printf("%s%s", $$names[$i], $i ? "," : ""); | |
715 | } | |
716 | print "\n"; | |
717 | while (my $ref = $sth->fetchrow_arrayref) { | |
718 | for (my $i = 0; $i < $numFields; $i++) { | |
719 | printf("%s%s", $$ref[$i], $i ? "," : ""); | |
720 | } | |
721 | print "\n"; | |
722 | } | |
723 | ||
724 | For portable applications you should restrict yourself to attributes with | |
725 | capitalized or mixed case names. Lower case attribute names are private | |
726 | to DBD::mSQL and DBD::mysql. The attribute list includes: | |
727 | ||
728 | =over 4 | |
729 | ||
730 | =item ChopBlanks | |
731 | ||
732 | this attribute determines whether a I<fetchrow> will chop preceding | |
733 | and trailing blanks off the column values. Chopping blanks does not | |
734 | have impact on the I<max_length> attribute. | |
735 | ||
736 | =item mysql_insertid | |
737 | ||
738 | MySQL has the ability to choose unique key values automatically. If this | |
739 | happened, the new ID will be stored in this attribute. This attribute | |
740 | is not valid for DBD::mSQL. An alternative way for accessing this attribute | |
741 | is via $dbh->{'mysql_insertid'}. (Note we are using the $dbh in this case!) | |
742 | ||
743 | =item mysql_is_blob | |
744 | ||
745 | Reference to an array of boolean values; TRUE indicates, that the | |
746 | respective column is a blob. This attribute is valid for MySQL only. | |
747 | ||
748 | =item mysql_is_key | |
749 | ||
750 | Reference to an array of boolean values; TRUE indicates, that the | |
751 | respective column is a key. This is valid for MySQL only. | |
752 | ||
753 | =item mysql_is_num | |
754 | ||
755 | Reference to an array of boolean values; TRUE indicates, that the | |
756 | respective column contains numeric values. | |
757 | ||
758 | =item mysql_is_pri_key | |
759 | ||
760 | Reference to an array of boolean values; TRUE indicates, that the | |
761 | respective column is a primary key. This is only valid for MySQL | |
762 | and mSQL 1.0.x: mSQL 2.x uses indices. | |
763 | ||
764 | =item mysql_length | |
765 | ||
766 | =item mysql_max_length | |
767 | ||
768 | A reference to an array of maximum column sizes. The I<max_length> is | |
769 | the maximum physically present in the result table, I<length> gives | |
770 | the theoretically possible maximum. I<max_length> is valid for MySQL | |
771 | only. | |
772 | ||
773 | =item NAME | |
774 | ||
775 | A reference to an array of column names. | |
776 | ||
777 | =item NULLABLE | |
778 | ||
779 | A reference to an array of boolean values; TRUE indicates that this column | |
780 | may contain NULL's. | |
781 | ||
782 | =item NUM_OF_FIELDS | |
783 | ||
784 | Number of fields returned by a I<SELECT> or I<LISTFIELDS> statement. | |
785 | You may use this for checking whether a statement returned a result: | |
786 | A zero value indicates a non-SELECT statement like I<INSERT>, | |
787 | I<DELETE> or I<UPDATE>. | |
788 | ||
789 | =item mysql_table | |
790 | ||
791 | A reference to an array of table names, useful in a I<JOIN> result. | |
792 | ||
793 | =item TYPE | |
794 | ||
795 | A reference to an array of column types. The engine's native column | |
796 | types are mapped to portable types like DBI::SQL_INTEGER() or | |
797 | DBI::SQL_VARCHAR(), as good as possible. Not all native types have | |
798 | a meaningfull equivalent, for example DBD::mSQL::IDX_TYPE() or | |
799 | DBD::mysql::FIELD_TYPE_INTERVAL are mapped to DBI::SQL_VARCHAR(). | |
800 | If you need the native column types, use I<mysql_type> or I<msql_type>, | |
801 | respectively. See below. | |
802 | ||
803 | =item mysql_type | |
804 | ||
805 | A reference to an array of MySQL's native column types, for example | |
806 | DBD::mysql::FIELD_TYPE_SHORT() or DBD::mysql::FIELD_TYPE_STRING(). | |
807 | Use the I<TYPE> attribute, if you want portable types like | |
808 | DBI::SQL_SMALLINT() or DBI::SQL_VARCHAR(). | |
809 | ||
810 | =item mysql_type_name | |
811 | ||
812 | Similar to mysql, but type names and not numbers are returned. | |
813 | Whenever possible, the ANSI SQL name is preferred. | |
814 | ||
815 | =back | |
816 | ||
817 | ||
818 | =head1 TRANSACTION SUPPORT | |
819 | ||
820 | Beginning with Msql-Mysql-modules 1.2216, transactions are supported | |
821 | by DBD::mysql. The transaction support works as follows: | |
822 | ||
823 | =over | |
824 | ||
825 | =item * | |
826 | ||
827 | By default AutoCommit mode is on, following the DBI specifications. | |
828 | ||
829 | =item * | |
830 | ||
831 | If you execute | |
832 | ||
833 | $dbh-E<gt>{'AutoCommit'} = 0; | |
834 | ||
835 | or | |
836 | ||
837 | $dbh-E<gt>{'AutoCommit'} = 1; | |
838 | ||
839 | then the driver will set the MySQL server variable autocommit to 0 or | |
840 | 1, respectively. Switching from 0 to 1 will also issue a COMMIT, | |
841 | following the DBI specifications. | |
842 | ||
843 | =item * | |
844 | ||
845 | The methods | |
846 | ||
847 | $dbh-E<gt>rollback(); | |
848 | $dbh-E<gt>commit(); | |
849 | ||
850 | will issue the commands COMMIT and ROLLBACK, respectively. A | |
851 | ROLLBACK will also be issued if AutoCommit mode is off and the | |
852 | database handles DESTROY method is called. Again, this is following | |
853 | the DBI specifications. | |
854 | ||
855 | =back | |
856 | ||
857 | Given the above, you should note the following: | |
858 | ||
859 | =over | |
860 | ||
861 | =item * | |
862 | ||
863 | You should never change the server variable autocommit manually, | |
864 | unless you are ignoring DBI's transaction support. | |
865 | ||
866 | =item * | |
867 | ||
868 | Switching AutoCommit mode from on to off or vice versa may fail. | |
869 | You should always check for errors, when changing AutoCommit mode. | |
870 | The suggested way of doing so is using the DBI flag RaiseError. | |
871 | If you don't like RaiseError, you have to use code like the | |
872 | following: | |
873 | ||
874 | $dbh->{'AutoCommit'} = 0; | |
875 | if ($dbh->{'AutoCommit'}) { | |
876 | # An error occurred! | |
877 | } | |
878 | ||
879 | =item * | |
880 | ||
881 | If you detect an error while changing the AutoCommit mode, you | |
882 | should no longer use the database handle. In other words, you | |
883 | should disconnect and reconnect again, because the transaction | |
884 | mode is unpredictable. Alternatively you may verify the transaction | |
885 | mode by checking the value of the server variable autocommit. | |
886 | However, such behaviour isn't portable. | |
887 | ||
888 | =back | |
889 | ||
890 | ||
891 | =head1 SQL EXTENSIONS | |
892 | ||
893 | Certain metadata functions of mSQL and mysql that are available on the | |
894 | C API level, haven't been implemented here. Instead they are implemented | |
895 | as "SQL extensions" because they return in fact nothing else but the | |
896 | equivalent of a statement handle. These are: | |
897 | ||
898 | =over 4 | |
899 | ||
900 | =item LISTFIELDS $table | |
901 | ||
902 | Returns a statement handle that describes the columns of $table. | |
903 | Ses the docs of msqlListFields or mysql_list_fields (C API) for | |
904 | details. | |
905 | ||
906 | =item LISTINDEX $table $index | |
907 | ||
908 | mSQL only; returns a statement handle that describes the index $index | |
909 | of table $table. See the docs of msqlListIndex for details. | |
910 | ||
911 | =back | |
912 | ||
913 | ||
914 | ||
915 | =head1 COMPATIBILITY ALERT | |
916 | ||
917 | The statement attribute I<TYPE> has changed its meaning, as of | |
918 | Msql-Mysql-modules 1.19_19. Formerly it used to be the an array | |
919 | of native engine's column types, but it is now an array of | |
920 | portable SQL column types. The old attribute is still available | |
921 | as I<mysql_type> or I<msql_type>, respectively. | |
922 | ||
923 | The Msql-Mysql-modules are a moving target, due to a number of reasons: | |
924 | ||
925 | =over 8 | |
926 | ||
927 | =item - | |
928 | ||
929 | Of course they have to conform the DBI guidelines and developments. | |
930 | ||
931 | =item - | |
932 | ||
933 | They have to keep track with the latest MySQL developments. | |
934 | ||
935 | =item - | |
936 | ||
937 | And, surprisingly, they have to be as close to ODBC as possible: This is | |
938 | due to the current direction of DBI. | |
939 | ||
940 | =item - | |
941 | ||
942 | And, last not least, as any tool they have a little bit of own life. | |
943 | ||
944 | =back | |
945 | ||
946 | This means that a lot of things had to and have to be changed. As I am not | |
947 | interested in maintaining a lot of compatibility kludges, which only | |
948 | increase the drivers code without being really usefull, I did and will | |
949 | remove some features, methods or attributes. | |
950 | ||
951 | To ensure a smooth upgrade, the following policy will be applied: | |
952 | ||
953 | =over 8 | |
954 | ||
955 | =item Obsolete features | |
956 | ||
957 | The first step is to declare something obsolete. This means, that no code | |
958 | is changed, but the feature appears in the list of obsolete features. See | |
959 | L<Obsolete Features> below. | |
960 | ||
961 | =item Deprecated features | |
962 | ||
963 | If the feature has been obsolete for quite some time, typically in the | |
964 | next major stable release, warnings will be inserted in the code. You | |
965 | can suppress these warnings by setting | |
966 | ||
967 | $DBD::mysql = 1; | |
968 | ||
969 | In the docs the feature will be moved from the list of obsolete features | |
970 | to the list of deprecated features. See L<Deprecated Features> below. | |
971 | ||
972 | =item Removing features | |
973 | ||
974 | Finally features will be removed silently in the next major stable | |
975 | release. The feature will be shown in the list of historic features. | |
976 | See L<Historic Features> below. | |
977 | ||
978 | =back | |
979 | ||
980 | Example: The statement handle attribute | |
981 | ||
982 | $sth->{'LENGTH'} | |
983 | ||
984 | was declared obsolete in Msql-Mysql-modules 1.18xy. It is considered | |
985 | deprecated in Msql-Mysql-modules 1.20xy (current as of this writing) | |
986 | and will finally be removed in Msql-Mysql-modules 1.22xy. | |
987 | ||
988 | =back | |
989 | ||
990 | ||
991 | =head2 Obsolete Features | |
992 | ||
993 | =over 8 | |
994 | ||
995 | =item _ListTables | |
996 | ||
997 | This method is obsoleted by DBI's new method | |
998 | ||
999 | $dbh->tables() | |
1000 | ||
1001 | See also $dbh->table_info(). Portable applications will prefer | |
1002 | ||
1003 | @tables = map { $_ =~ s/.*\.//; $_ } $dbh->tables() | |
1004 | ||
1005 | because, depending on the engine, the string "user.table" will be | |
1006 | returned, user being the table owner. The method will be deprecated | |
1007 | in 1.23_xy and 1.24xy and removed in 1.25_xy and 1.26xy. | |
1008 | ||
1009 | =back | |
1010 | ||
1011 | ||
1012 | =head2 Deprecated Features | |
1013 | ||
1014 | =over 8 | |
1015 | ||
1016 | =item _InsertID | |
1017 | ||
1018 | The method | |
1019 | ||
1020 | $dbh->func('_InsertID'); | |
1021 | ||
1022 | can be replaced with | |
1023 | ||
1024 | $dbh->{'mysql_insertid'}; | |
1025 | ||
1026 | The method is deprecated as of 1.21_xy, thus it will be removed in | |
1027 | 1.23_xy and 1.24xy. | |
1028 | ||
1029 | =item Statement handle attributes: | |
1030 | ||
1031 | =over 12 | |
1032 | ||
1033 | =item affected_rows | |
1034 | ||
1035 | =item IS_PRI_KEY | |
1036 | ||
1037 | =item is_pri_key | |
1038 | ||
1039 | =item IS_NOT_NULL | |
1040 | ||
1041 | =item is_not_null | |
1042 | ||
1043 | =item IS_KEY | |
1044 | ||
1045 | =item is_key | |
1046 | ||
1047 | =item IS_BLOB | |
1048 | ||
1049 | =item is_blob | |
1050 | ||
1051 | =item IS_NUM | |
1052 | ||
1053 | =item is_num | |
1054 | ||
1055 | =item LENGTH | |
1056 | ||
1057 | =item length | |
1058 | ||
1059 | =item MAXLENGTH | |
1060 | ||
1061 | =item maxlength | |
1062 | ||
1063 | =item NUMROWS | |
1064 | ||
1065 | =item numrows | |
1066 | ||
1067 | =item NUMFIELDS | |
1068 | ||
1069 | =item numfields | |
1070 | ||
1071 | =item RESULT | |
1072 | ||
1073 | =item result | |
1074 | ||
1075 | =item TABLE | |
1076 | ||
1077 | =item table | |
1078 | ||
1079 | =item format_max_size | |
1080 | ||
1081 | =item format_default_size | |
1082 | ||
1083 | =item format_type_name | |
1084 | ||
1085 | =back | |
1086 | ||
1087 | All of the above statement handle attributes are not conforming to DBI's | |
1088 | naming conventions, thus they have been declared deprecated in 1.20xy. | |
1089 | However, I forgot to insert warnings in the driver. These warnings have | |
1090 | been inserted in 1.21_07, thus the attributes will be removed in 1.23_xy | |
1091 | and 1.24xy. | |
1092 | ||
1093 | In most of the above cases the driver name has been added and the resulting | |
1094 | name was lowercased. For example, you use | |
1095 | ||
1096 | $sth->{'mysql_is_num'}; | |
1097 | ||
1098 | now. IS_NOT_NULL can be replaced with NULLABLE (note you need to invert | |
1099 | the logical value!), LENGTH, format_max_size and format_default_size will | |
1100 | be dropped in favour of PRECISION, affected_rows and NUMROWS are identical | |
1101 | with | |
1102 | ||
1103 | $sth->rows(); | |
1104 | ||
1105 | and NUMFIELDS is the same as NUM_OF_FIELDS. Finally format_right_justify | |
1106 | is the same as mysql_type_name. | |
1107 | ||
1108 | =back | |
1109 | ||
1110 | ||
1111 | ||
1112 | =head2 Historic Features | |
1113 | ||
1114 | =over 8 | |
1115 | ||
1116 | =item _CreateDB | |
1117 | ||
1118 | =item _DropDB | |
1119 | ||
1120 | The methods | |
1121 | ||
1122 | $dbh->func($db, '_CreateDB'); | |
1123 | $dbh->func($db, '_DropDB'); | |
1124 | ||
1125 | have been used for creating or dropping databases. They have been removed | |
1126 | in 1.21_07 in favour of | |
1127 | ||
1128 | $drh->func("createdb", $dbname, $host, "admin") | |
1129 | $drh->func("dropdb", $dbname, $host, "admin") | |
1130 | ||
1131 | =item _ListFields | |
1132 | ||
1133 | The method | |
1134 | ||
1135 | $sth = $dbh->func($table, '_ListFields'); | |
1136 | ||
1137 | has been used to list a tables columns names, types and other attributes. | |
1138 | This method has been removed in 1.21_07 in favour of | |
1139 | ||
1140 | $sth = $dbh->prepare("LISTFIELDS $table"); | |
1141 | ||
1142 | =item _ListSelectedFields | |
1143 | ||
1144 | The method | |
1145 | ||
1146 | $sth->func('_ListSelectedFields'); | |
1147 | ||
1148 | use to return a hash ref of attributes like 'IS_NUM', 'IS_KEY' and so | |
1149 | on. These attributes are now accessible via | |
1150 | ||
1151 | $sth->{'mysql_is_num'}; | |
1152 | $sth->{'mysql_is_key'}; | |
1153 | ||
1154 | and so on. Thus the method has been removed in 1.21_07. | |
1155 | ||
1156 | =item _NumRows | |
1157 | ||
1158 | The method | |
1159 | ||
1160 | $sth->func('_NumRows'); | |
1161 | ||
1162 | used to be equivalent to | |
1163 | ||
1164 | $sth->rows(); | |
1165 | ||
1166 | and has been removed in 1.21_07. | |
1167 | ||
1168 | =back | |
1169 | ||
1170 | ||
1171 | =head1 MULTITHREADING | |
1172 | ||
1173 | The multithreading capabilities of the Msql-Mysql-modules depend completely | |
1174 | on the underlying C libraries: The modules are working with handle data | |
1175 | only, no global variables are accessed or (to the best of my knowledge) | |
1176 | thread unsafe functions are called. Thus DBD::mSQL and DBD::mysql are | |
1177 | completely thread safe, if the C libraries thread safe and you don't | |
1178 | share handles among threads. | |
1179 | ||
1180 | The obvious questions is: Are the C libraries thread safe? In the case of | |
1181 | mSQL the answer is definitely "no". The C library has a concept of one | |
1182 | single active connection at a time and that is not what threads like. | |
1183 | ||
1184 | In the case of MySQL the answer is "mostly" and, in theory, you should | |
1185 | be able to get a "yes", if the C library is compiled for being thread | |
1186 | safe (By default it isn't.) by passing the option -with-thread-safe-client | |
1187 | to configure. See the section on I<How to make a threadsafe client> in | |
1188 | the manual. | |
1189 | ||
1190 | ||
1191 | =head1 INSTALLATION | |
1192 | ||
1193 | Windows users may skip this section and pass over to L<WIN32 | |
1194 | INSTALLATION> below. Others, go on reading. | |
1195 | ||
1196 | First of all, you do not need an installed MySQL server for installing | |
1197 | DBD::mSQL and/or DBD::mysql. However, you need at least the client | |
1198 | libraries and possibly the header files, if you are compiling DBD::mysql | |
1199 | or DBD::mSQL from source. In the case of MySQL you can create a | |
1200 | client-only version by using the configure option --without-server. | |
1201 | If you are using precompiled binaries, then it may be possible to | |
1202 | use just selected RPM's like MySQL-client and MySQL-devel or something | |
1203 | similar, depending on the distribution. | |
1204 | ||
1205 | First you need to install the DBI module. For using I<dbimon>, a | |
1206 | simple DBI shell it is recommended to install Data::ShowTable another | |
1207 | Perl module. | |
1208 | ||
1209 | I recommend trying automatic installation via the CPAN module. Try | |
1210 | ||
1211 | perl -MCPAN -e shell | |
1212 | ||
1213 | If you are using the CPAN module for the first time, it will prompt | |
1214 | you a lot of questions. If you finally receive the CPAN prompt, enter | |
1215 | ||
1216 | install Bundle::DBD::mSQL | |
1217 | ||
1218 | or | |
1219 | ||
1220 | install Bundle::DBD::mysql | |
1221 | ||
1222 | If this fails (which may be the case for a number of reasons, for | |
1223 | example because you are behind a firewall or don't have network | |
1224 | access), you need to do a manual installation. First of all you | |
1225 | need to fetch the archives from any CPAN mirror, for example | |
1226 | ||
1227 | ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-module | |
1228 | ||
1229 | The following archives are required (version numbers may have | |
1230 | changed, I choose those which are current as of this writing): | |
1231 | ||
1232 | DBI/DBI-1.13.tar.gz | |
1233 | Data/Data-ShowTable-3.3.tar.gz | |
1234 | DBD/Msql-Mysql-modules-1.2217.tar.gz | |
1235 | ||
1236 | Then enter the following commands: | |
1237 | ||
1238 | gzip -cd DBI-1.13.tar.gz | tar xf - | |
1239 | cd DBI-1.13 | |
1240 | perl Makefile.PL | |
1241 | make | |
1242 | make test | |
1243 | make install | |
1244 | ||
1245 | cd .. | |
1246 | gzip -cd Data-ShowTable-3.3.tar.gz | tar xf - | |
1247 | cd Data-ShowTable-3.3 | |
1248 | perl Makefile.PL | |
1249 | make | |
1250 | make install # Don't try make test, the test suite is broken | |
1251 | ||
1252 | cd .. | |
1253 | gzip -cd Msql-Mysql-modules-1.2217.tar.gz | tar xf - | |
1254 | cd Msql-Mysql-modules-1.2217 | |
1255 | perl Makefile.PL | |
1256 | make | |
1257 | make test | |
1258 | make install | |
1259 | ||
1260 | During "perl Makefile.PL" you will be prompted some questions. In | |
1261 | particular you have to choose the installed drivers (MySQL, mSQL2 | |
1262 | and/or mSQL1). The MySQL driver will be called DBD::mysql, a single | |
1263 | mSQL driver will be called DBD::mSQL. If you want to support both | |
1264 | mSQL1 and mSQL2, they former will be DBD::mSQL1. | |
1265 | ||
1266 | Other questions are the directories with header files and libraries. | |
1267 | For example, of your file F<mysql.h> is in F</usr/include/mysql/mysql.h>, | |
1268 | then enter the header directory F</usr>, likewise for | |
1269 | F</usr/lib/mysql/libmysqlclient.a> or F</usr/lib/libmysqlclient.so>. | |
1270 | For mSQL go looking for F<msql.h> (typically in F</usr/include/msql.h> | |
1271 | and F<libmsql.a> (probably in F</usr/lib/libmsql.a>). | |
1272 | ||
1273 | See the L<KNOWN PROBLEMS> section below if you encounter any problems | |
1274 | within "make" or "make test". | |
1275 | ||
1276 | ||
1277 | =head1 WIN32 INSTALLATION | |
1278 | ||
1279 | I have never attempted to install DBD::mSQL under Win32, so this is only | |
1280 | for MySQL. If anyone succeeds, please let me know what you did. | |
1281 | ||
1282 | If you are using ActivePerl, you may use ppm to install DBD-mysql. | |
1283 | For Perl 5.6, upgrade to Build 623 or later, then it is sufficient | |
1284 | to run | |
1285 | ||
1286 | ppm install DBI | |
1287 | ppm install DBD::mysql | |
1288 | ||
1289 | ||
1290 | The same applied to Perl 5.005. | |
1291 | ||
1292 | Otherwise you definitely *need* a C compiler. And it *must* be the same | |
1293 | compiler that was being used for compiling Perl itself. If you don't | |
1294 | have a C compiler, the file README.win32 from the Perl source | |
1295 | distribution tells you where to obtain freely distributable C compilers | |
1296 | like egcs or gcc. The Perl sources are available on any CPAN mirror in | |
1297 | the src directory, for example | |
1298 | ||
1299 | ftp://ftp.funet.fi/pub/languages/perl/CPAN/src/latest.tar.gz | |
1300 | ||
1301 | I recommend using the win32clients package for installing DBD::mysql | |
1302 | under Win32, available for download on www.tcx.se. The following steps | |
1303 | have been required for me: | |
1304 | ||
1305 | =over 8 | |
1306 | ||
1307 | =item - | |
1308 | ||
1309 | The current Perl versions (5.6, as of this writing) do have a problem | |
1310 | with detecting the C libraries. I recommend to apply the following | |
1311 | patch: | |
1312 | ||
1313 | *** c:\Perl\lib\ExtUtils\Liblist.pm.orig Sat Apr 15 20:03:40 2000 | |
1314 | --- c:\Perl\lib\ExtUtils\Liblist.pm Sat Apr 15 20:03:45 2000 | |
1315 | *************** | |
1316 | *** 230,235 **** | |
1317 | --- 230,239 ---- | |
1318 | # add "$Config{installarchlib}/CORE" to default search path | |
1319 | push @libpath, "$Config{installarchlib}/CORE"; | |
1320 | ||
1321 | + if ($VC and exists($ENV{LIB}) and defined($ENV{LIB})) { | |
1322 | + push(@libpath, split(/;/, $ENV{LIB})); | |
1323 | + } | |
1324 | + | |
1325 | foreach (Text::ParseWords::quotewords('\s+', 0, $potential_libs)){ | |
1326 | ||
1327 | $thislib = $_; | |
1328 | ||
1329 | =item - | |
1330 | ||
1331 | Extract sources into F<C:\>. This will create a directory F<C:\mysql> | |
1332 | with subdirectories include and lib. | |
1333 | ||
1334 | IMPORTANT: Make sure this subdirectory is not shared by other TCX | |
1335 | files! In particular do *not* store the MySQL server in the same | |
1336 | directory. If the server is already installed in F<C:\mysql>, | |
1337 | choose a location like F<C:\tmp>, extract the win32clients there. | |
1338 | Note that you can remove this directory entirely once you have | |
1339 | installed the Msql-Mysql-modules. | |
1340 | ||
1341 | =item - | |
1342 | ||
1343 | Extract the Msql-Mysql-modules sources into another directory, for | |
1344 | example F<C:\src\siteperl> | |
1345 | ||
1346 | =item - | |
1347 | ||
1348 | Open a DOS shell and change directory to F<C:\src\siteperl>. | |
1349 | ||
1350 | =item - | |
1351 | ||
1352 | The next step is only required if you repeat building the modules: Make | |
1353 | sure that you have a clean build tree by running | |
1354 | ||
1355 | nmake realclean | |
1356 | ||
1357 | If you don't have VC++, replace nmake with your flavour of make. If | |
1358 | error messages are reported in this step, you may safely ignore them. | |
1359 | ||
1360 | =item - | |
1361 | ||
1362 | Run | |
1363 | ||
1364 | perl Makefile.PL | |
1365 | ||
1366 | which will prompt you for some settings. The really important ones are: | |
1367 | ||
1368 | Which DBMS do you want to use? | |
1369 | ||
1370 | enter a 1 here (MySQL only), and | |
1371 | ||
1372 | Where is your mysql installed? Please tell me the directory that | |
1373 | contains the subdir include. | |
1374 | ||
1375 | where you have to enter the win32clients directory, for example | |
1376 | F<C:\mysql> or F<C:\tmp\mysql>. | |
1377 | ||
1378 | =item - | |
1379 | ||
1380 | Continued in the usual way: | |
1381 | ||
1382 | nmake | |
1383 | nmake install | |
1384 | ||
1385 | =back | |
1386 | ||
1387 | See the first section of L<KNOWN PROBLEMS> below. | |
1388 | ||
1389 | If you want to create a PPM package for the ActiveState Perl version, then | |
1390 | modify the above steps as follows: Run | |
1391 | ||
1392 | perl Makefile.PL NAME=DBD-mysql BINARY_LOCATION=DBD-mysql.tar.gz | |
1393 | nmake ppd | |
1394 | nmake | |
1395 | ||
1396 | Once that is done, use tar and gzip (for example those from the CygWin32 | |
1397 | distribution) to create an archive: | |
1398 | ||
1399 | mkdir x86 | |
1400 | tar cf x86/DBD-mysql.tar blib | |
1401 | gzip x86/DBD-mysql.tar | |
1402 | ||
1403 | Put the files x86/DBD-mysql.tar.gz and DBD-mysql.ppd onto some WWW server | |
1404 | and install them by typing | |
1405 | ||
1406 | install http://your.server.name/your/directory/DBD-mysql.ppd | |
1407 | ||
1408 | in the PPM program. | |
1409 | ||
1410 | ||
1411 | =head1 KNOWN PROBLEMS | |
1412 | ||
1413 | =over 8 | |
1414 | ||
1415 | =item 1.) | |
1416 | ||
1417 | If the MySQL binaries are compiled with gcc or egcs (as the precompiled | |
1418 | binaries are), but your Perl is using another compiler, it is likely that | |
1419 | you receive an error message like the following when running "make test": | |
1420 | ||
1421 | t/00base............install_driver(mysql) failed: Can't load | |
1422 | '../blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: | |
1423 | ../blib/arch/auto/DBD/mysql/mysql.so: undefined symbol: _umoddi3 | |
1424 | at /usr/local/perl-5.005/lib/5.005/i586-linux-thread/DynaLoader.pm | |
1425 | line 168. | |
1426 | ||
1427 | This means, that your linker doesn't include libgcc.a. You have the | |
1428 | following options: | |
1429 | ||
1430 | =over 12 | |
1431 | ||
1432 | =item a) | |
1433 | ||
1434 | Either recompile Perl or Mysql, it doesn't matter which. The important | |
1435 | thing is that you use the same compiler for both. This is definitely | |
1436 | the recommended solution in the long term. | |
1437 | ||
1438 | =item b) | |
1439 | ||
1440 | A simple workaround is to include libgcc.a manually. Do a "make clean" | |
1441 | and "make" and in the output wait for a line like | |
1442 | ||
1443 | LD_RUN_PATH="/usr/lib/mysql:/lib" egcs -o | |
1444 | ../blib/arch/auto/DBD/mysql/mysql.so -shared -L/usr/local/lib | |
1445 | dbdimp.o mysql.o -L/usr/lib/mysql -L/usr/lib/mysql -lmysqlclient | |
1446 | -lm | |
1447 | ||
1448 | Repeat the same line in the shell by adding | |
1449 | ||
1450 | -L/usr/lib/gcc-lib/i386-redhat-linux/gcc-2.7.2.3 -lgcc | |
1451 | ||
1452 | where the directory is the location of libgcc.a. The best choice | |
1453 | for locating this file is executing | |
1454 | ||
1455 | gcc --print-libgcc-file | |
1456 | ||
1457 | or | |
1458 | ||
1459 | gcc -v | |
1460 | ||
1461 | =back | |
1462 | ||
1463 | ||
1464 | =item 2.) | |
1465 | ||
1466 | There are known problems with shared versions of libmysqlclient, at | |
1467 | least on some Linux boxes. If you receive an error message similar to | |
1468 | ||
1469 | install_driver(mysql) failed: Can't load | |
1470 | '/usr/lib/perl5/site_perl/i586-linux/auto/DBD/mysql/mysql.so' | |
1471 | for module DBD::mysql: File not found at | |
1472 | /usr/lib/perl5/i586-linux/5.00404/DynaLoader.pm line 166 | |
1473 | ||
1474 | then this error message can be misleading: It's not mysql.so that fails | |
1475 | being loaded, but libmysqlclient.so! | |
1476 | ||
1477 | As a workaround, recompile the Msql-Mysql-modules with | |
1478 | ||
1479 | perl Makefile.PL --static --config | |
1480 | make | |
1481 | make test | |
1482 | make install | |
1483 | ||
1484 | This option forces linkage against the static libmysqlclient.a. | |
1485 | ||
1486 | ||
1487 | =item 3.) | |
1488 | ||
1489 | By default mSQL2 is installed to allow local access only. This can break | |
1490 | the test scripts akmisc.t, msql1.t and msql2.t. You might notice a message | |
1491 | like | |
1492 | ||
1493 | t/akmisc............Can't connect to MSQL server on localhost at | |
1494 | t/akmisc.t line 131 | |
1495 | Cannot connect: Can't connect to MSQL server on localhost | |
1496 | It looks as if your server (on localhost) is not up and running. | |
1497 | This test requires a running server. | |
1498 | Please make sure your server is running and retry. | |
1499 | dubious | |
1500 | Test returned status 10 (wstat 2560, 0xa00) | |
1501 | ||
1502 | If this is the case, try to change the value of "Remote_Access" in | |
1503 | your F<msql.conf> file to "True". If the value was set intentionally, | |
1504 | you might restore the old value after the tests ran ok. | |
1505 | ||
1506 | ||
1507 | =item 4.) | |
1508 | ||
1509 | If linking fails under Win32 because of a missing symbol | |
1510 | pthread_cond_init, apply the following patch to dbd/dbdimp.c: | |
1511 | ||
1512 | *** dbd/dbdimp.c.orig Wed Sep 23 14:39:33 1998 | |
1513 | --- dbd/dbdimp.c Fri Oct 02 10:37:16 1998 | |
1514 | *************** | |
1515 | *** 1708,1712 **** | |
1516 | --- 1709,1720 ---- | |
1517 | } | |
1518 | return TRUE; | |
1519 | } | |
1520 | + | |
1521 | + #if !defined(_UNIX_) && defined(WIN32) | |
1522 | + int pthread_cond_init() | |
1523 | + { | |
1524 | + return 0; | |
1525 | + } | |
1526 | + #endif | |
1527 | ||
1528 | #endif | |
1529 | ||
1530 | (I could make this part of the source distribution, but I think this is an | |
1531 | ugly hack and hopefully Monty will fix the missing symbol in the next | |
1532 | release of MyODBC.) | |
1533 | ||
1534 | ||
1535 | =item 5.) | |
1536 | ||
1537 | mSQL 2.0.4 had a bug, that caused the test t/40bindparam.t to fail. | |
1538 | This bug is fixed in later versions, 2.0.6 and higher. | |
1539 | ||
1540 | ||
1541 | =item 6.) | |
1542 | ||
1543 | From time to time compiling DBD::mSQL fails with messages like | |
1544 | ||
1545 | In file included from ../dbd/myMsql.h:55, | |
1546 | from ../dbd/dbdimp.h:32, | |
1547 | from dbdimp.c:29: | |
1548 | /usr/local/Hughes/include/common/portability.h:74: | |
1549 | redefinition of `u_int' | |
1550 | /usr/include/sys/types.h:52: `u_int' previously declared here | |
1551 | /usr/local/Hughes/include/common/portability.h:78: redefinition of | |
1552 | `ssize_t' | |
1553 | /usr/include/sys/types.h:116: `ssize_t' previously declared here | |
1554 | *** Error code 1 | |
1555 | ||
1556 | A workaroud is using | |
1557 | ||
1558 | perl Makefile.PL --config DEFINE="-DHAVE_SSIZE_T -DHAVE_U_INT" | |
1559 | ||
1560 | =back | |
1561 | ||
1562 | ||
1563 | =head1 KNOWN BUGS | |
1564 | ||
1565 | The I<port> part of the first argument to the connect call is | |
1566 | implemented in an unsafe way when using mSQL. In fact it is just | |
1567 | setting the environment variable MSQL_TCP_PORT during the connect | |
1568 | call. If another connect call uses another port and the handles | |
1569 | are used simultaneously, they will interfere. I doubt that this | |
1570 | will ever change. | |
1571 | ||
1572 | ||
1573 | =head1 AUTHORS | |
1574 | ||
1575 | The current versions of B<DBD::mSQL> and B<DBD::mysql> is almost | |
1576 | completely written by Jochen Wiedmann (I<joe@ispsoft.de>). The | |
1577 | first version's author was Alligator Descartes(I<descarte@symbolstone.org>), | |
1578 |