Commit | Line | Data |
---|---|---|
86530b38 AT |
1 | # -*- perl -*- |
2 | ||
3 | package Mysql; | |
4 | ||
5 | use 5.004; | |
6 | use strict; | |
7 | ||
8 | require Carp; | |
9 | require DynaLoader; | |
10 | require Exporter; | |
11 | require DBI; | |
12 | require Mysql::Statement; | |
13 | require DBD::mysql; | |
14 | ||
15 | use vars qw($QUIET @ISA @EXPORT @EXPORT_OK $VERSION $db_errstr); | |
16 | ||
17 | $db_errstr = ''; | |
18 | $QUIET = 0; | |
19 | @ISA = qw(DBI); # Inherits Exporter and DynaLoader via DBI | |
20 | $VERSION = '1.2219'; | |
21 | ||
22 | # @EXPORT is a relict from old times... | |
23 | @EXPORT = qw( | |
24 | CHAR_TYPE | |
25 | INT_TYPE | |
26 | REAL_TYPE | |
27 | ); | |
28 | @EXPORT_OK = qw( | |
29 | IDENT_TYPE | |
30 | NULL_TYPE | |
31 | TEXT_TYPE | |
32 | DATE_TYPE | |
33 | UINT_TYPE | |
34 | MONEY_TYPE | |
35 | TIME_TYPE | |
36 | IDX_TYPE | |
37 | SYSVAR_TYPE | |
38 | ); | |
39 | ||
40 | my $FETCH_map = { | |
41 | 'HOST' => '_host', | |
42 | 'DATABASE' => 'database' | |
43 | }; | |
44 | ||
45 | sub FETCH ($$) { | |
46 | my($self, $key) = @_; | |
47 | if ($key eq 'COMPATIBILITY') { | |
48 | return $self->{'COMPATIBILITY'}; | |
49 | } | |
50 | if (exists($FETCH_map->{$key})) { | |
51 | $key = $FETCH_map->{$key}; | |
52 | } | |
53 | my($dbh) = $self->{'dbh'}; | |
54 | $dbh->{$key}; | |
55 | } | |
56 | ||
57 | sub STORE ($$$) { | |
58 | my($self, $key, $val) = @_; | |
59 | if ($key eq 'COMPATIBILITY') { | |
60 | $self->{'COMPATIBILITY'} = $val; | |
61 | } else { | |
62 | $self->{'dbh'}->{$key} = $val; | |
63 | } | |
64 | } | |
65 | ||
66 | sub connect ($;$$$$) { | |
67 | my($class, $host, $db, $user, $password) = @_; | |
68 | my($self) = { 'host' => ($host || ''), | |
69 | 'user' => $user, | |
70 | 'password' => $password, | |
71 | 'db' => $db, | |
72 | 'driver' => 'mysql', | |
73 | 'COMPATIBILITY' => 1 }; | |
74 | bless($self, $class); | |
75 | $self->{'drh'} = DBI->install_driver($self->{'driver'}); | |
76 | if ($db) { | |
77 | my $dsn = "DBI:mysql:database=$db;host=$host"; | |
78 | my $dbh = $class->SUPER::connect($dsn, $user, $password); | |
79 | if (!$dbh) { | |
80 | $db_errstr = $DBI::errstr; | |
81 | return undef; | |
82 | } | |
83 | $self->{'dbh'} = $dbh; | |
84 | $dbh->{'CompatMode'} = 1; | |
85 | $dbh->{'PrintError'} = !$Mysql::QUIET; | |
86 | } | |
87 | $self; | |
88 | } | |
89 | ||
90 | sub DESTROY { | |
91 | my $self = shift; | |
92 | my $dbh = $self->{'dbh'}; | |
93 | if ($dbh) { | |
94 | local $SIG{'__WARN__'} = sub {}; | |
95 | $dbh->disconnect(); | |
96 | } | |
97 | } | |
98 | ||
99 | sub selectdb ($$) { | |
100 | my($self, $db) = @_; | |
101 | my $dsn = "DBI:mysql:database=$db:host=" . $self->{'host'}; | |
102 | my $dbh = DBI->connect($dsn, $self->{'user'}, $self->{'password'}); | |
103 | if (!$dbh) { | |
104 | $db_errstr = $self->{'errstr'} = $DBI::errstr; | |
105 | $self->{'errno'} = $DBI::err; | |
106 | undef; | |
107 | } else { | |
108 | if ($self->{'dbh'}) { | |
109 | local $SIG{'__WARN__'} = sub {}; | |
110 | $self->{'dbh'}->disconnect(); | |
111 | } | |
112 | $self->{'dbh'} = $dbh; | |
113 | $self->{'db'} = $db; | |
114 | $self; | |
115 | } | |
116 | } | |
117 | ||
118 | sub listdbs ($) { | |
119 | my($self) = shift; | |
120 | my $drh = $self->{'drh'}; | |
121 | my @dbs = $drh->func($self->{'host'}, '_ListDBs'); | |
122 | $db_errstr = $drh->errstr(); | |
123 | @dbs; | |
124 | } | |
125 | ||
126 | sub listtables ($) { | |
127 | my($self) = shift; | |
128 | $self->{'dbh'}->func('_ListTables'); | |
129 | } | |
130 | ||
131 | sub quote ($$) { | |
132 | my($self) = shift; | |
133 | my $obj = (ref($self) && $self->{'dbh'}) ? | |
134 | $self->{'dbh'} : 'DBD::mysql::db'; | |
135 | $obj->quote(shift); | |
136 | } | |
137 | ||
138 | sub errmsg ($) { | |
139 | my $self = shift; | |
140 | if (!ref($self)) { | |
141 | $DBI::errstr || $db_errstr; | |
142 | } elsif ($self->{'dbh'}) { | |
143 | $self->{'dbh'}->errstr(); | |
144 | } else { | |
145 | $self->{'drh'}->errstr(); | |
146 | } | |
147 | } | |
148 | ||
149 | sub errno ($) { | |
150 | my $self = shift; | |
151 | if ($self->{'dbh'}) { | |
152 | $self->{'dbh'}->err(); | |
153 | } else { | |
154 | $self->{'drh'}->err(); | |
155 | } | |
156 | } | |
157 | ||
158 | sub listfields ($$) { | |
159 | my($self, $table) = @_; | |
160 | $self->query("LISTFIELDS $table"); | |
161 | } | |
162 | ||
163 | sub query ($$) { | |
164 | my($self, $statement) = @_; | |
165 | my $dbh = $self->{'dbh'}; | |
166 | my $sth = $dbh->prepare($statement); | |
167 | if (!$sth) { | |
168 | $db_errstr = $dbh->errstr(); | |
169 | return undef; | |
170 | } | |
171 | $sth->{'PrintError'} = !$Mysql::QUIET; | |
172 | my $result = $sth->execute(); | |
173 | if (!$result) { | |
174 | $db_errstr = $sth->errstr(); | |
175 | return undef; | |
176 | } | |
177 | $sth->{'CompatMode'} = 1; | |
178 | bless($sth, ref($self) . "::Statement"); | |
179 | undef $db_errstr; | |
180 | $sth; | |
181 | } | |
182 | ||
183 | sub shutdown ($) { | |
184 | my($self) = shift; | |
185 | if ($self->{'dbh'}) { | |
186 | $self->{'dbh'}->admin('shutdown', 'admin'); | |
187 | } else { | |
188 | $self->{'drh'}->func('shutdown', $self->{'host'}, $self->{'user'}, | |
189 | $self->{'password'}, 'admin'); | |
190 | } | |
191 | } | |
192 | ||
193 | sub createdb ($$) { | |
194 | my($self, $db) = @_; | |
195 | if ($self->{'dbh'}) { | |
196 | $self->{'dbh'}->admin('createdb', $db, 'admin'); | |
197 | } else { | |
198 | $self->{'drh'}->func('createdb', $db, $self->{'host'}, | |
199 | $self->{'user'}, $self->{'password'}, 'admin'); | |
200 | } | |
201 | } | |
202 | ||
203 | sub dropdb ($$) { | |
204 | my($self, $db) = @_; | |
205 | if ($self->{'dbh'}) { | |
206 | $self->{'dbh'}->admin('dropdb', $db, 'admin'); | |
207 | } else { | |
208 | $self->{'drh'}->func('dropdb', $db, $self->{'host'}, | |
209 | $self->{'user'}, $self->{'password'}, 'admin'); | |
210 | } | |
211 | } | |
212 | ||
213 | sub host ($) { shift->{'host'} } | |
214 | sub database ($) { shift->{'db'} } | |
215 | sub info ($) { shift->{'dbh'}->{'info'} } | |
216 | sub sock ($) { shift->{'dbh'}->{'sock'} } | |
217 | sub sockfd ($) { shift->{'dbh'}->{'sockfd'} } | |
218 | ||
219 | ||
220 | sub AUTOLOAD { | |
221 | my $meth = $Mysql::AUTOLOAD; | |
222 | my $converted = 0; | |
223 | ||
224 | my $class; | |
225 | if ($meth =~ /(.*)::(.*)/) { | |
226 | $meth = $2; | |
227 | $class = $1; | |
228 | } else { | |
229 | $class = "main"; | |
230 | } | |
231 | ||
232 | ||
233 | TRY: { | |
234 | my $val = DBD::mysql::constant($meth, @_ ? $_[0] : 0); | |
235 | if ($! == 0) { | |
236 | eval "sub $Mysql::AUTOLOAD { $val }"; | |
237 | return $val; | |
238 | } | |
239 | ||
240 | if (!$converted) { | |
241 | $meth =~ s/_//g; | |
242 | $meth = lc($meth); | |
243 | $converted = 1; | |
244 | } | |
245 | ||
246 | if (defined &$meth) { | |
247 | no strict 'refs'; | |
248 | *$meth = \&{$meth}; | |
249 | return &$meth(@_); | |
250 | } elsif ($meth =~ s/(.*)type$/uc($1)."_TYPE"/e) { | |
251 | # Try to determine the type that was requested by | |
252 | # translating inttype to INT_TYPE Not that I consider it | |
253 | # good style to write inttype, but we once allowed it, | |
254 | # so... | |
255 | redo TRY; | |
256 | } | |
257 | } | |
258 | ||
259 | Carp::croak("$Mysql::AUTOLOAD: Not defined in $class and not" | |
260 | . " autoloadable (last try $meth)"); | |
261 | } | |
262 | ||
263 | ||
264 | sub gethostinfo ($) { shift->{'dbh'}->{'hostinfo'} } | |
265 | sub getprotoinfo ($) { shift->{'dbh'}->{'protoinfo'} } | |
266 | sub getserverinfo ($) { shift->{'dbh'}->{'serverinfo'} } | |
267 | sub getserverstats ($) { shift->{'dbh'}->{'stats'} } | |
268 | ||
269 | ||
270 | Mysql->init_rootclass(); | |
271 | ||
272 | package Mysql::dr; | |
273 | @Mysql::dr::ISA = qw(DBI::dr); | |
274 | ||
275 | package Mysql::db; | |
276 | @Mysql::db::ISA = qw(DBI::db); | |
277 | ||
278 | package Mysql::st; | |
279 | @Mysql::st::ISA = qw(Mysql::Statement); | |
280 | ||
281 | 1; | |
282 | __END__ | |
283 | ||
284 | =head1 NAME | |
285 | ||
286 | Msql / Mysql - Perl interfaces to the mSQL and mysql databases | |
287 | ||
288 | =head1 SYNOPSIS | |
289 | ||
290 | use Msql; | |
291 | ||
292 | $dbh = Msql->connect($host); | |
293 | $dbh = Msql->connect($host, $database); | |
294 | ||
295 | or | |
296 | ||
297 | use Mysql; | |
298 | ||
299 | $dbh = Mysql->connect(undef, $database, $user, $password); | |
300 | $dbh = Mysql->connect($host, $database, $user, $password); | |
301 | ||
302 | or | |
303 | ||
304 | $dbh = Msql1->connect($host); | |
305 | $dbh = Msql1->connect($host, $database); | |
306 | ||
307 | ||
308 | $dbh->selectdb($database); | |
309 | ||
310 | @arr = $dbh->listdbs; | |
311 | @arr = $dbh->listtables; | |
312 | ||
313 | $quoted_string = $dbh->quote($unquoted_string); | |
314 | $error_message = $dbh->errmsg; | |
315 | $error_number = $dbh->errno; # MySQL only | |
316 | ||
317 | $sth = $dbh->listfields($table); | |
318 | $sth = $dbh->query($sql_statement); | |
319 | ||
320 | @arr = $sth->fetchrow; # Array context | |
321 | $firstcol = $sth->fetchrow; # Scalar context | |
322 | @arr = $sth->fetchcol($col_number); | |
323 | %hash = $sth->fetchhash; | |
324 | ||
325 | $sth->dataseek($row_number); | |
326 | ||
327 | $sth->as_string; | |
328 | ||
329 | @indices = $sth->listindices # only in mSQL 2.0 | |
330 | @arr = $dbh->listindex($table,$index) # only in mSQL 2.0 | |
331 | ($step,$value) = $dbh->getsequenceinfo($table) # only in mSQL 2.0 | |
332 | ||
333 | $rc = $dbh->shutdown(); | |
334 | $rc = $dbh->createdb($database); | |
335 | $rc = $dbh->dropdb($database); | |
336 | ||
337 | =head1 OBSOLETE SOFTWARE | |
338 | ||
339 | As of Msql-Mysql-modules 1.19_10 M(y)sqlPerl is no longer a separate module. | |
340 | Instead it is emulated using the DBI drivers. You are strongly encouraged | |
341 | to implement new code with DBI directly. See L<COMPATIBILITY NOTES> | |
342 | below. | |
343 | ||
344 | =head1 DESCRIPTION | |
345 | ||
346 | This package is designed as close as possible to its C API | |
347 | counterpart. The manual that comes with mSQL or MySQL describes most things | |
348 | you need. Due to popular demand it was decided though, that this interface | |
349 | does not use StudlyCaps (see below). | |
350 | ||
351 | As of March 1998, the Msql and Mysql modules are obsoleted by the | |
352 | DBI drivers DBD::mSQL and DBD::mysql, respectively. You are strongly | |
353 | encouraged to implement new code with the DBI drivers. In fact, | |
354 | Msql and Mysql are currently implemented as emulations on top of | |
355 | the DBI drivers. | |
356 | ||
357 | Internally you are dealing with the two classes C<Msql> and | |
358 | C<Msql::Statement> or C<Mysql> and C<Mysql::Statement>, respectively. | |
359 | You will never see the latter, because you reach | |
360 | it through a statement handle returned by a query or a listfields | |
361 | statement. The only class you name explicitly is Msql or Mysql. They | |
362 | offer you the connect command: | |
363 | ||
364 | $dbh = Msql->connect($host); | |
365 | $dbh = Msql->connect($host, $database); | |
366 | ||
367 | or | |
368 | ||
369 | $dbh = Mysql->connect($host, undef, $user, $password); | |
370 | $dbh = Mysql->connect($host, $database, $user, $password); | |
371 | ||
372 | or | |
373 | ||
374 | $dbh = Msql1->connect($host); | |
375 | $dbh = Msql1->connect($host, $database); | |
376 | ||
377 | ||
378 | This connects you with the desired host/database. With no argument or | |
379 | with an empty string as the first argument it connects to the UNIX | |
380 | socket, which has a much better performance than | |
381 | the TCP counterpart. A database name as the second argument selects | |
382 | the chosen database within the connection. The return value is a | |
383 | database handle if the connect succeeds, otherwise the return value is | |
384 | undef. | |
385 | ||
386 | You will need this handle to gain further access to the database. | |
387 | ||
388 | $dbh->selectdb($database); | |
389 | ||
390 | If you have not chosen a database with the C<connect> command, or if | |
391 | you want to change the connection to a different database using a | |
392 | database handle you have got from a previous C<connect>, then use | |
393 | selectdb. | |
394 | ||
395 | $sth = $dbh->listfields($table); | |
396 | $sth = $dbh->query($sql_statement); | |
397 | ||
398 | These two work rather similar as descibed in the mSQL or MySQL manual. They | |
399 | return a statement handle which lets you further explore what the | |
400 | server has to tell you. On error the return value is undef. The object | |
401 | returned by listfields will not know about the size of the table, so a | |
402 | numrows() on it will return the string "N/A"; | |
403 | ||
404 | @arr = $dbh->listdbs(); | |
405 | @arr = $dbh->listtables; | |
406 | ||
407 | An array is returned that contains the requested names without any | |
408 | further information. | |
409 | ||
410 | @arr = $sth->fetchrow; | |
411 | ||
412 | returns an array of the values of the next row fetched from the | |
413 | server. Be carefull with context here! In scalar context the method | |
414 | behaves different than expected and returns the first column: | |
415 | ||
416 | $firstcol = $sth->fetchrow; # Scalar context! | |
417 | ||
418 | Similar does | |
419 | ||
420 | %hash = $sth->fetchhash; | |
421 | ||
422 | return a complete hash. The keys in this hash are the column names of | |
423 | the table, the values are the table values. Be aware, that when you | |
424 | have a table with two identical column names, you will not be able to | |
425 | use this method without trashing one column. In such a case, you | |
426 | should use the fetchrow method. | |
427 | ||
428 | @arr = $sth->fetchcol($colnum); | |
429 | ||
430 | returns an array of the values of each row for column $colnum. Note that | |
431 | this reads the entire table and leaves the row offset at the end of the | |
432 | table; be sure to use $sth->dataseek() to reset it if you want to | |
433 | re-examine the table. | |
434 | ||
435 | $sth->dataseek($row_number); | |
436 | ||
437 | lets you specify a certain offset of the data associated with the | |
438 | statement handle. The next fetchrow will then return the appropriate | |
439 | row (first row being 0). | |
440 | ||
441 | =head2 No close statement | |
442 | ||
443 | Whenever the scalar that holds a database or statement handle loses | |
444 | its value, Msql chooses the appropriate action (frees the result or | |
445 | closes the database connection). So if you want to free the result or | |
446 | close the connection, choose to do one of the following: | |
447 | ||
448 | =over 4 | |
449 | ||
450 | =item undef the handle | |
451 | ||
452 | =item use the handle for another purpose | |
453 | ||
454 | =item let the handle run out of scope | |
455 | ||
456 | =item exit the program. | |
457 | ||
458 | =back | |
459 | ||
460 | =head2 Error messages | |
461 | ||
462 | Both drivers, Msql and Mysql implement a method -E<gt>errmsg(), which | |
463 | returns a textual error message. Mysql additionally supports a method | |
464 | -E<gt>errno returning the corresponding error number. | |
465 | ||
466 | Usually you do fetch error messages with | |
467 | ||
468 | $errmsg = $dbh->errmsg(); | |
469 | ||
470 | In situations where a $dbh is not available (for example when | |
471 | connect() failed) you may instead do a | |
472 | ||
473 | $errmsg = Msql->errmsg(); | |
474 | or | |
475 | $errmsg = Mysql->errmsg(); | |
476 | or | |
477 | $errmsg = Msql1->errmsg(); | |
478 | ||
479 | ||
480 | =head2 The C<-w> switch | |
481 | ||
482 | With Msql and Mysql the C<-w> switch is your friend! If you call your perl | |
483 | program with the C<-w> switch you get the warnings from -E<gt>errmsg on | |
484 | STDERR. This is a handy method to get the error messages from the msql | |
485 | server without coding it into your program. | |
486 | ||
487 | If you want to know in greater detail what's going on, set the | |
488 | environment variables that are described in David's manual. David's | |
489 | debugging aid is excellent, there's nothing to be added. | |
490 | ||
491 | By default errors are printed as warnings. You can suppress this | |
492 | behaviour by using the PrintError attribute of the respective handles: | |
493 | ||
494 | $dbh->{'dbh'}->{'PrintError'} = 0; | |
495 | ||
496 | ||
497 | =head2 -E<gt>quote($str [, $length]) | |
498 | ||
499 | returns the argument enclosed in single ticks ('') with any special | |
500 | character escaped according to the needs of the API. | |
501 | ||
502 | For mSQL this means, any single tick within the string is escaped with | |
503 | a backslash and backslashes are doubled. Currently (as of msql-1.0.16) | |
504 | the API does not allow to insert NUL's (ASCII 0) into tables. The quote | |
505 | method does not fix this deficiency. | |
506 | ||
507 | MySQL allows NUL's or any other kind of binary data in strings. Thus | |
508 | the quote method will additionally escape NUL's as \0. | |
509 | ||
510 | If you pass undefined values to the quote method, it returns the | |
511 | string C<NULL>. | |
512 | ||
513 | If a second parameter is passed to C<quote>, the result is truncated | |
514 | to that many characters. | |
515 | ||
516 | =head2 NULL fields | |
517 | ||
518 | NULL fields in tables are returned to perl as undefined values. | |
519 | ||
520 | =head2 Metadata | |
521 | ||
522 | Now lets reconsider the above methods with regard to metadata. | |
523 | ||
524 | =head2 Database Handle | |
525 | ||
526 | As said above you get a database handle with the connect() method. | |
527 | The database handle knows about the socket, the host, and the database | |
528 | it is connected to. | |
529 | ||
530 | You get at the three values with the methods | |
531 | ||
532 | $scalar = $dbh->sock; | |
533 | $scalar = $dbh->host; | |
534 | $scalar = $dbh->database; | |
535 | ||
536 | Mysql additionally supports | |
537 | ||
538 | $scalar = $dbh->user; | |
539 | $scalar = $dbh->sockfd; | |
540 | ||
541 | where the latter is the file descriptor of the socket used by the | |
542 | database connection. This is the same as $dbh->sock for mSQL. | |
543 | ||
544 | =head2 Statement Handle | |
545 | ||
546 | Two constructor methods return a statement handle: | |
547 | ||
548 | $sth = $dbh->listfields($table); | |
549 | $sth = $dbh->query($sql_statement); | |
550 | ||
551 | $sth knows about all metadata that are provided by the API: | |
552 | ||
553 | $scalar = $sth->numrows; | |
554 | $scalar = $sth->numfields; | |
555 | ||
556 | @arr = $sth->table; the names of the tables of each column | |
557 | @arr = $sth->name; the names of the columns | |
558 | @arr = $sth->type; the type of each column, defined in msql.h | |
559 | and accessible via Msql::CHAR_TYPE, | |
560 | &Msql::INT_TYPE, &Msql::REAL_TYPE or | |
561 | &Mysql::FIELD_TYPE_STRING, | |
562 | &Mysql::FIELD_TYPE_LONG, ... | |
563 | @arr = $sth->isnotnull; array of boolean | |
564 | @arr = $sth->isprikey; array of boolean | |
565 | @arr = $sth->isnum; array of boolean | |
566 | @arr = $sth->length; array of the possibble maximum length of each | |
567 | field in bytes | |
568 | @arr = $sth->maxlength; array of the actual maximum length of each field | |
569 | in bytes. Be careful when using this attribute | |
570 | under MsqlPerl: The server doesn't offer this | |
571 | attribute, thus it is calculated by fetching | |
572 | all rows. This might take a long time and you | |
573 | might need to call $sth->dataseek. | |
574 | ||
575 | Mysql additionally supports | |
576 | ||
577 | $scalar = $sth->affectedrows number of rows in database affected by query | |
578 | $scalar = $sth->insertid the unique id given to a auto_increment field. | |
579 | $string = $sth->info() more info from some queries (ALTER TABLE...) | |
580 | $arrref = $sth->isblob; array of boolean | |
581 | ||
582 | The array methods (table, name, type, is_not_null, is_pri_key, length, | |
583 | affected_rows, is_num and blob) return an array in array context and | |
584 | an array reference (see L<perlref> and L<perlldsc> for details) when | |
585 | called in a scalar context. The scalar context is useful, if you need | |
586 | only the name of one column, e.g. | |
587 | ||
588 | $name_of_third_column = $sth->name->[2] | |
589 | ||
590 | which is equivalent to | |
591 | ||
592 | @all_column_names = $sth->name; | |
593 | $name_of_third_column = $all_column_names[2]; | |
594 | ||
595 | =head2 New in mSQL 2.0 | |
596 | ||
597 | The query() function in the API returns the number of rows affected by | |
598 | a query. To cite the mSQL API manual, this means... | |
599 | ||
600 | If the return code is greater than 0, not only does it imply | |
601 | success, it also indicates the number of rows "touched" by the query | |
602 | (i.e. the number of rows returned by a SELECT, the number of rows | |
603 | modified by an update, or the number of rows removed by a delete). | |
604 | ||
605 | As we are returning a statement handle on selects, we can easily check | |
606 | the number of rows returned. For non-selects we behave just the same | |
607 | as mSQL-2. | |
608 | ||
609 | To find all indices associated with a table you can call the | |
610 | C<listindices()> method on a statement handle. To find out the columns | |
611 | included in an index, you can call the C<listindex($table,$index)> | |
612 | method on a database handle. | |
613 | ||
614 | There are a few new column types in mSQL 2. You can access their | |
615 | numeric value with these functions defined in the Msql package: | |
616 | IDENT_TYPE, NULL_TYPE, TEXT_TYPE, DATE_TYPE, UINT_TYPE, MONEY_TYPE, | |
617 | TIME_TYPE, IDX_TYPE, SYSVAR_TYPE. | |
618 | ||
619 | You cannot talk to a 1.0 server with a 2.0 client. | |
620 | ||
621 | You cannot link to a 1.0 library I<and> to a 2.0 library I<at the same | |
622 | time>. So you may want to build two different Msql modules at a time, | |
623 | one for 1.0, another for 2.0, and load whichever you need. Check out | |
624 | what the C<-I> switch in perl is for. | |
625 | ||
626 | Everything else seems to remain backwards compatible. | |
627 | ||
628 | =head2 @EXPORT | |
629 | ||
630 | For historical reasons the constants CHAR_TYPE, INT_TYPE, and | |
631 | REAL_TYPE are in @EXPORT instead of @EXPORT_OK. This means, that you | |
632 | always have them imported into your namespace. I consider it a bug, | |
633 | but not such a serious one, that I intend to break old programs by | |
634 | moving them into EXPORT_OK. | |
635 | ||
636 | =head2 Displaying whole tables in one go | |
637 | ||
638 | A handy method to show the complete contents of a statement handle is | |
639 | the as_string method. This works similar to the msql monitor with a | |
640 | few exceptions: | |
641 | ||
642 | =over 2 | |
643 | ||
644 | =item the width of a column | |
645 | ||
646 | is calculated by examining the width of all entries in that column | |
647 | ||
648 | =item control characters | |
649 | ||
650 | are mapped into their backslashed octal representation | |
651 | ||
652 | =item backslashes | |
653 | ||
654 | are doubled (C<\\ instead of \>) | |
655 | ||
656 | =item numeric values | |
657 | ||
658 | are adjusted right (both integer and floating point values) | |
659 | ||
660 | =back | |
661 | ||
662 | The differences are illustrated by the following table: | |
663 | ||
664 | Input to msql (a real carriage return here replaced with ^M): | |
665 | ||
666 | CREATE TABLE demo ( | |
667 | first_field CHAR(10), | |
668 | second_field INT | |
669 | ) \g | |
670 | ||
671 | INSERT INTO demo VALUES ('new | |
672 | line',2)\g | |
673 | INSERT INTO demo VALUES ('back\\slash',1)\g | |
674 | INSERT INTO demo VALUES ('cr^Mcrnl | |
675 | nl',3)\g | |
676 | ||
677 | Output of msql: | |
678 | ||
679 | +-------------+--------------+ | |
680 | | first_field | second_field | | |
681 | +-------------+--------------+ | |
682 | | new | |
683 | line | 2 | | |
684 | | back\slash | 1 | | |
685 | crnlr | |
686 | nl | 3 | | |
687 | +-------------+--------------+ | |
688 | ||
689 | Output of pmsql: | |
690 | ||
691 | +----------------+------------+ | |
692 | |first_field |second_field| | |
693 | +----------------+------------+ | |
694 | |new\012line | 2| | |
695 | |back\\slash | 1| | |
696 | |cr\015crnl\012nl| 3| | |
697 | +----------------+------------+ | |
698 | ||
699 | ||
700 | =head2 Version information | |
701 | ||
702 | The version of Msql and Mysql is always stored in $Msql::VERSION or | |
703 | $Mysql::VERSION as it is perl standard. | |
704 | ||
705 | The mSQL API implements methods to access some internal configuration | |
706 | parameters: gethostinfo, getserverinfo, and getprotoinfo. All three | |
707 | are available both as class methods or via a database handle. But | |
708 | under no circumstances they are associated with a database handle. All | |
709 | three return global variables that reflect the B<last> connect() | |
710 | command within the current program. This means, that all three return | |
711 | empty strings or zero I<before> the first call to connect(). | |
712 | ||
713 | This situation is better with MySQL: The methods are valid only | |
714 | in connection with a database handle. | |
715 | ||
716 | =head2 Administration | |
717 | ||
718 | shutdown, createdb, dropdb, reloadacls are all accessible via a | |
719 | database handle and implement the corresponding methods to what | |
720 | msqladmin does. | |
721 | ||
722 | The mSQL and MySQL engines do not permit that these commands are invoked by | |
723 | users without sufficient privileges. So please make sure | |
724 | to check the return and error code when you issue one of them. | |
725 | ||
726 | $rc = $dbh->shutdown(); | |
727 | $rc = $dbh->createdb($database); | |
728 | $rc = $dbh->dropdb($database); | |
729 | ||
730 | It should be noted that database deletion is I<not prompted for> in | |
731 | any way. Nor is it undo-able from within Perl. | |
732 | ||
733 | B<Once you issue the dropdb() method, the database will be gone!> | |
734 | ||
735 | These methods should be used at your own risk. | |
736 | ||
737 | ||
738 | =head2 StudlyCaps | |
739 | ||
740 | Real Perl Programmers (C) usually don't like to type I<ListTables> but | |
741 | prefer I<list_tables> or I<listtables>. The mSQL API uses StudlyCaps | |
742 | everywhere and so did early versions of MsqlPerl. Beginning with | |
743 | $VERSION 1.06 all methods are internally in lowercase, but may be | |
744 | written however you please. Case is ignored and you may use the | |
745 | underline to improve readability. | |
746 | ||
747 | The price for using different method names is neglectible. Any method | |
748 | name you use that can be transformed into a known one, will only be | |
749 | defined once within a program and will remain an alias until the | |
750 | program terminates. So feel free to run fetch_row or connecT or | |
751 | ListDBs as in your old programs. These, of course, will continue to | |
752 | work. | |
753 | ||
754 | =head1 PREREQUISITES | |
755 | ||
756 | mSQL is a database server and an API library written by David | |
757 | Hughes. To use the adaptor you definitely have to install these first. | |
758 | ||
759 | MySQL is a libmysqlclient.a library written by Michael Widenius | |
760 | This was originally inspired by MySQL. | |
761 | ||
762 | ||
763 | =head1 COMPATIBILITY NOTES | |
764 | ||
765 | M(y)sql used to be a separate module written in C. This is no longer | |
766 | the case, instead the old modules are emulated by their corresponding | |
767 | DBI drivers. I did my best to remove any incompatibilities, but the | |
768 | following problems are known to remain: | |
769 | ||
770 | =over 4 | |
771 | ||
772 | =item Static methods | |
773 | ||
774 | For whatever reason, mSQL implements some functions independent from | |
775 | the respective database connection that really depend on it. This | |
776 | made it possible to implement | |
777 | ||
778 | Msql->errmsg | |
779 | ||
780 | or | |
781 | ||
782 | Msql->getserverinfo | |
783 | ||
784 | as static methods. This is no longer the case, it never was for | |
785 | MysqlPerl. Instead you have to use | |
786 | ||
787 | $dbh->errmsg | |
788 | ||
789 | or | |
790 | ||
791 | $dbh->getserverinfo | |
792 | ||
793 | =item $M(Y)SQL::QUIET | |
794 | ||
795 | This variable used to turn off the printing of error messages. Unfortunately | |
796 | DBI uses a completely different mechanism for that: The C<PrintError> | |
797 | attribute of the database and/or statement handles. We try to emulate | |
798 | the old behaviour by setting the C<PrintError> attribute to the current | |
799 | value of $M(Y)SQL::QUIET when a handle is created, that is when | |
800 | M(y)sql->connect or $dbh->query() are called. | |
801 | ||
802 | You can overwrite this by using something like | |
803 | ||
804 | $dbh->{'dbh'}->{'PrintError'} = 1; | |
805 | ||
806 | or | |
807 | ||
808 | $sth->{'PrintError'} = 0; | |
809 | ||
810 | =back | |
811 | ||
812 | ||
813 | =head1 AUTHORS | |
814 | ||
815 | Andreas Koenig C<koenig@franz.ww.TU-Berlin.DE> wrote the original | |
816 | MsqlPerl. Jochen Wiedmann C<joe@ispsoft.de> wrote the M(y)sqlPerl | |
817 | emulation using DBI. | |
818 | ||
819 | ||
820 | =head1 SEE ALSO | |
821 | ||
822 | Alligator Descartes wrote a database driver for Tim Bunce's DBI. I | |
823 | recommend anybody to carefully watch the development of this module | |
824 | (C<DBD::mSQL>). Msql is a simple, stable, and fast module, and it will | |
825 | be supported for a long time. But it's a dead end. I expect in the | |
826 | medium term, that the DBI efforts result in a richer module family | |
827 | with better support and more functionality. Alligator maintains an | |
828 | interesting page on the DBI development: | |
829 | ||
830 | http://www.symbolstone.org/technology/perl/DBI | |
831 | ||
832 | =cut |