Commit | Line | Data |
---|---|---|
86530b38 AT |
1 | ### |
2 | ### $Id: FAQ.pm,v 10.8 2001/05/29 23:25:55 timbo Exp $ | |
3 | ### | |
4 | ### DBI Frequently Asked Questions POD | |
5 | ### | |
6 | ### Copyright section reproduced from below. | |
7 | ### | |
8 | ### This document is Copyright (c)1994-2000 Alligator Descartes, with portions | |
9 | ### Copyright (c)1994-2000 their original authors. This module is released under | |
10 | ### the 'Artistic' license which you can find in the perl distribution. | |
11 | ### | |
12 | ### This document is Copyright (c)1997-2000 Alligator Descartes. All rights reserved. | |
13 | ### Permission to distribute this document, in full or in part, via email, | |
14 | ### Usenet, ftp archives or http is granted providing that no charges are involved, | |
15 | ### reasonable attempt is made to use the most current version and all credits | |
16 | ### and copyright notices are retained ( the I<AUTHOR> and I<COPYRIGHT> sections ). | |
17 | ### Requests for other distribution rights, including incorporation into | |
18 | ### commercial products, such as books, magazine articles or CD-ROMs should be | |
19 | ### made to Alligator Descartes <I<http://www.symbolstone.org/descarte/contact.html>>. | |
20 | ### | |
21 | ### $Log: FAQ.pm,v $ | |
22 | # Revision 10.8 2001/05/29 23:25:55 timbo | |
23 | # *** empty log message *** | |
24 | # | |
25 | # Revision 10.7 2001/03/28 14:52:07 timbo | |
26 | # Main checkin prior to 1.15 release | |
27 | # | |
28 | ||
29 | package DBI::FAQ; | |
30 | ||
31 | $VERSION = '0.38'; | |
32 | ||
33 | =head1 NAME | |
34 | ||
35 | DBI::FAQ -- The Frequently Asked Questions for the Perl5 Database Interface | |
36 | ||
37 | =for html | |
38 | <BODY BGCOLOR="#ffffff" TEXT="#000000" LINK="#3a15ff" ALINK="#ff0000" VLINK="#ff282d"> | |
39 | <!--#include virtual="/technology/perl/DBI/templatetop.html" --> | |
40 | <CENTER> | |
41 | <FONT SIZE="+2"> | |
42 | DBI Frequently Asked Questions v.0.38 | |
43 | </FONT> | |
44 | <BR> | |
45 | <FONT SIZE="-1"> | |
46 | <I>Last updated: February 8th, 2000</I> | |
47 | </FONT> | |
48 | </CENTER> | |
49 | <P> | |
50 | ||
51 | =head1 SYNOPSIS | |
52 | ||
53 | perldoc DBI::FAQ | |
54 | ||
55 | =head1 VERSION | |
56 | ||
57 | This document is currently at version I<0.38>, as of I<February 8th, 2000>. | |
58 | ||
59 | =head1 DESCRIPTION | |
60 | ||
61 | This document serves to answer the most frequently asked questions on both | |
62 | the DBI Mailing Lists and personally to members of the DBI development team. | |
63 | ||
64 | =head1 Basic Information & Information Sources | |
65 | ||
66 | =head2 1.1 What is DBI, DBperl, Oraperl and *perl? | |
67 | ||
68 | To quote Tim Bunce, the architect and author of DBI: | |
69 | ||
70 | ``DBI is a database access Application Programming Interface (API) | |
71 | for the Perl Language. The DBI API Specification defines a set | |
72 | of functions, variables and conventions that provide a consistent | |
73 | database interface independant of the actual database being used.'' | |
74 | ||
75 | In simple language, the DBI interface allows users to access multiple database | |
76 | types transparently. So, if you connecting to an Oracle, Informix, mSQL, Sybase | |
77 | or whatever database, you don't need to know the underlying mechanics of the | |
78 | 3GL layer. The API defined by DBI will work on I<all> these database types. | |
79 | ||
80 | A similar benefit is gained by the ability to connect to two I<different> | |
81 | databases of different vendor within the one perl script, I<ie>, I want | |
82 | to read data from an Oracle database and insert it back into an Informix | |
83 | database all within one program. The DBI layer allows you to do this simply | |
84 | and powerfully. | |
85 | ||
86 | ||
87 | =for html | |
88 | Here's a diagram that demonstrates the principle: | |
89 | <P> | |
90 | <CENTER> | |
91 | <IMG SRC="img/dbiarch.gif" WIDTH=451 HEIGHT=321 ALT="[ DBI Architecture ]"> | |
92 | </CENTER> | |
93 | <P> | |
94 | ||
95 | I<DBperl> is the old name for the interface specification. It's usually | |
96 | now used to denote perlI<4> modules on database interfacing, such as, | |
97 | I<oraperl>, I<isqlperl>, I<ingperl> and so on. These interfaces | |
98 | didn't have a standard API and are generally I<not> supported. | |
99 | ||
100 | Here's a list of DBperl modules, their corresponding DBI counterparts and | |
101 | support information. I<Please note>, the author's listed here generally | |
102 | I<do not> maintain the DBI module for the same database. These email | |
103 | addresses are unverified and should only be used for queries concerning the | |
104 | perl4 modules listed below. DBI driver queries should be directed to the | |
105 | I<dbi-users> mailing list. | |
106 | ||
107 | Module Name Database Required Author DBI | |
108 | ----------- ----------------- ------ --- | |
109 | Sybperl Sybase Michael Peppler DBD::Sybase | |
110 | <mpeppler@itf.ch> | |
111 | Oraperl Oracle 6 & 7 Kevin Stock DBD::Oracle | |
112 | <dbi-users@perl.org> | |
113 | Ingperl Ingres Tim Bunce & DBD::Ingres | |
114 | Ted Lemon | |
115 | <dbi-users@perl.org> | |
116 | Interperl Interbase Buzz Moschetti DBD::Interbase | |
117 | <buzz@bear.com> | |
118 | Uniperl Unify 5.0 Rick Wargo None | |
119 | <rickers@coe.drexel.edu> | |
120 | Pgperl Postgres Igor Metz DBD::Pg | |
121 | <metz@iam.unibe.ch> | |
122 | Btreeperl NDBM John Conover SDBM? | |
123 | <john@johncon.com> | |
124 | Ctreeperl C-Tree John Conover None | |
125 | <john@johncon.com> | |
126 | Cisamperl Informix C-ISAM Mathias Koerber None | |
127 | <mathias@unicorn.swi.com.sg> | |
128 | Duaperl X.500 Directory Eric Douglas None | |
129 | User Agent | |
130 | ||
131 | However, some DBI modules have DBperl emulation layers, so, I<DBD::Oracle> | |
132 | comes with an Oraperl emulation layer, which allows you to run legacy oraperl | |
133 | scripts without modification. The emulation layer translates the oraperl API | |
134 | calls into DBI calls and executes them through the DBI switch. | |
135 | ||
136 | Here's a table of emulation layer information: | |
137 | ||
138 | Module Emulation Layer Status | |
139 | ------ --------------- ------ | |
140 | DBD::Oracle Oraperl Complete | |
141 | DBD::Informix Isqlperl Under development | |
142 | DBD::Ingres Ingperl Complete? | |
143 | DBD::Sybase Sybperl Working? ( Needs verification ) | |
144 | DBD::mSQL Msqlperl Experimentally released with | |
145 | DBD::mSQL-0.61 | |
146 | ||
147 | The I<Msqlperl> emulation is a special case. I<Msqlperl> is a perl5 driver | |
148 | for I<mSQL> databases, but does not conform to the DBI Specification. It's | |
149 | use is being deprecated in favour of I<DBD::mSQL>. I<Msqlperl> may be downloaded | |
150 | from CPAN I<via>: | |
151 | ||
152 | http://www.perl.com/cgi-bin/cpan_mod?module=Msqlperl | |
153 | ||
154 | =head2 1.2. Where can I get it from? | |
155 | ||
156 | The Comprehensive Perl Archive Network | |
157 | resources should be used for retrieving up-to-date versions of the DBI | |
158 | and drivers. CPAN may be accessed I<via> Tom Christiansen's splendid | |
159 | I<CPAN multiplexer> program located at: | |
160 | ||
161 | http://www.perl.com/CPAN/ | |
162 | ||
163 | For more specific version information and exact URLs of drivers, please see | |
164 | the DBI drivers list and the DBI module pages which can be found on: | |
165 | ||
166 | http://dbi.perl.org/ | |
167 | http://www.symbolstone.org/technology/perl/DBI | |
168 | ||
169 | This list is automatically generated on a nightly basis from CPAN and should | |
170 | be up-to-date. | |
171 | ||
172 | =head2 1.3. Where can I get more information? | |
173 | ||
174 | There are a few information sources on DBI. | |
175 | ||
176 | =over 4 | |
177 | ||
178 | =item I<"Programming the Perl DBI"> | |
179 | ||
180 | "Programming the Perl DBI" is the I<official> book on the DBI written by | |
181 | Alligator Descartes and Tim Bunce and published by O'Reilly & Associates. | |
182 | The book was released on February 9th, 2000. | |
183 | ||
184 | The table of contents is: | |
185 | ||
186 | Preface | |
187 | 1. Introduction | |
188 | From Mainframes to Workstations | |
189 | Perl | |
190 | DBI in the Real World | |
191 | A Historical Interlude and Standing Stones | |
192 | 2. Basic Non-DBI Databases | |
193 | Storage Managers and Layers | |
194 | Query Languages and Data Functions | |
195 | Standing Stones and the Sample Database | |
196 | Flat-File Databases | |
197 | Putting Complex Data into Flat Files | |
198 | Concurrent Database Access and Locking | |
199 | DBM Files and the Berkeley Database Manager | |
200 | The MLDBM Module | |
201 | Summary | |
202 | 3. SQL and Relational Databases | |
203 | The Relational Database Methodology | |
204 | Datatypes and NULL Values | |
205 | Querying Data | |
206 | Modifying Data Within Tables | |
207 | Creating and Destroying Tables | |
208 | 4. Programming with the DBI | |
209 | DBI Architecture | |
210 | Handles | |
211 | Data Source Names | |
212 | Connection and Disconnection | |
213 | Error Handling | |
214 | Utility Methods and Functions | |
215 | 5. Interacting with the Database | |
216 | Issuing Simple Queries | |
217 | Executing Non-SELECT Statements | |
218 | Binding Parameters to Statements | |
219 | Binding Output Columns | |
220 | do() Versus prepare() | |
221 | Atomic and Batch Fetching | |
222 | 6. Advanced DBI | |
223 | Handle Attributes and Metadata | |
224 | Handling LONG/LOB Data | |
225 | Transactions, Locking, and Isolation | |
226 | 7. ODBC and the DBI | |
227 | ODBC -- Embraced and Extended | |
228 | DBI -- Thrashed and Mutated | |
229 | The Nuts and Bolts of ODBC | |
230 | ODBC from Perl | |
231 | The Marriage of DBI and ODBC | |
232 | Questions and Choices | |
233 | Moving Between Win32::ODBC and the DBI | |
234 | And What About ADO? | |
235 | 8. DBI Shell and Database Proxying | |
236 | dbish -- The DBI Shell | |
237 | Database Proxying | |
238 | A. DBI Specification | |
239 | B. Driver and Database Characteristics | |
240 | C. ASLaN Sacred Site Charter | |
241 | Index | |
242 | ||
243 | The book should be available from all good bookshops and can be ordered online | |
244 | either <I>via</I> O'Reilly & Associates | |
245 | ||
246 | http://www.oreilly.com/catalog/perldbi | |
247 | ||
248 | or Amazon | |
249 | ||
250 | http://www.amazon.com/exec/obidos/ASIN/1565926994/dbi | |
251 | ||
252 | =item I<POD documentation> | |
253 | ||
254 | I<POD>s are chunks of documentation usually embedded within perl programs | |
255 | that document the code ``I<in place>'', providing a useful resource for | |
256 | programmers and users of modules. POD for DBI and drivers is beginning to | |
257 | become more commonplace, and documentation for these modules can be read | |
258 | with the C<perldoc> program included with Perl. | |
259 | ||
260 | =over 4 | |
261 | ||
262 | =item The DBI Specification | |
263 | ||
264 | The POD for the DBI Specification can be read with the: | |
265 | ||
266 | perldoc DBI | |
267 | ||
268 | command. The Specification also forms Appendix A of "Programming the Perl | |
269 | DBI". | |
270 | ||
271 | =item Oraperl | |
272 | ||
273 | Users of the Oraperl emulation layer bundled with I<DBD::Oracle>, may read | |
274 | up on how to program with the Oraperl interface by typing: | |
275 | ||
276 | perldoc Oraperl | |
277 | ||
278 | This will produce an updated copy of the original oraperl man page written by | |
279 | Kevin Stock for perl4. The oraperl API is fully listed and described there. | |
280 | ||
281 | =item Drivers | |
282 | ||
283 | Users of the DBD modules may read about some of the private functions | |
284 | and quirks of that driver by typing: | |
285 | ||
286 | perldoc <driver> | |
287 | ||
288 | For example, the I<DBD::mSQL> driver is bundled with driver-specific | |
289 | documentation that can be accessed by typing | |
290 | ||
291 | perldoc DBD::mSQL | |
292 | ||
293 | =item Frequently Asked Questions | |
294 | ||
295 | This document, the I<Frequently Asked Questions> is also available as POD | |
296 | documentation! You can read this on your own system by typing: | |
297 | ||
298 | perldoc DBI::FAQ | |
299 | ||
300 | This may be more convenient to persons not permanently, or conveniently, | |
301 | connected to the Internet. The I<DBI::FAQ> module should be downloaded and | |
302 | installed for the more up-to-date version. | |
303 | ||
304 | The version of I<DBI::FAQ> shipped with the C<DBI> module may be slightly out | |
305 | of date. | |
306 | ||
307 | =item POD in general | |
308 | ||
309 | Information on writing POD, and on the philosophy of POD in general, can be | |
310 | read by typing: | |
311 | ||
312 | perldoc perlpod | |
313 | ||
314 | Users with the Tk module installed may be interested to learn there is a | |
315 | Tk-based POD reader available called C<tkpod>, which formats POD in a convenient | |
316 | and readable way. This is available I<via> CPAN as the module called | |
317 | I<Tk::POD> and is highly recommended. | |
318 | ||
319 | =back | |
320 | ||
321 | =item I<Driver and Database Characteristics> | |
322 | ||
323 | The driver summaries that were produced for Appendix B of "Programming the | |
324 | Perl DBI" are available online at: | |
325 | ||
326 | http://dbi.perl.org/ | |
327 | http://www.symbolstone.org/technology/perl/DBI | |
328 | ||
329 | in the driver information table. These summaries contain standardised | |
330 | information on each driver and database which should aid you in selecting | |
331 | a database to use. It will also inform you quickly of any issues within | |
332 | drivers or whether a driver is not fully compliant with the DBI Specification. | |
333 | ||
334 | =item I<Rambles, Tidbits and Observations> | |
335 | ||
336 | http://dbi.perl.org/tidbits | |
337 | http://www.symbolstone.org/technology/perl/DBI/tidbits | |
338 | ||
339 | There are a series of occasional rambles from various people on the | |
340 | DBI mailing lists who, in an attempt to clear up a simple point, end up | |
341 | drafting fairly comprehensive documents. These are quite often varying in | |
342 | quality, but do provide some insights into the workings of the interfaces. | |
343 | ||
344 | =item I<Articles> | |
345 | ||
346 | A list of articles discussing the DBI can be found on the DBI WWW page at: | |
347 | ||
348 | http://dbi.perl.org/ | |
349 | http://www.symbolstone.org/technology/perl/DBI | |
350 | ||
351 | These articles are of varying quality and age, from the original Perl Journal | |
352 | article written by Alligator and Tim, to more recent debacles published online | |
353 | from about.com. | |
354 | ||
355 | =item I<README files> | |
356 | ||
357 | The I<README> files included with each driver occasionally contains | |
358 | some useful information ( no, really! ) that may be pertinent to the user. | |
359 | Please read them. It makes our worthless existences more bearable. These | |
360 | can all be read from the main DBI WWW page at: | |
361 | ||
362 | http://dbi.perl.org/ | |
363 | http://www.symbolstone.org/technology/perl/DBI | |
364 | ||
365 | =item I<Mailing Lists> | |
366 | ||
367 | There are three mailing lists for DBI: | |
368 | ||
369 | dbi-announce@perl.org -- for announcements, very low traffic | |
370 | dbi-users@perl.org -- general user support | |
371 | dbi-dev@perl.org -- for driver developers (no user support) | |
372 | ||
373 | For information on how to subscribe, set digest mode etc, and unsubscribe, | |
374 | send an email message (the content will be ignored) to: | |
375 | ||
376 | dbi-announce-help@perl.org | |
377 | dbi-users-help@perl.org | |
378 | dbi-dev-help@perl.org | |
379 | ||
380 | =item I<Mailing List Archives> | |
381 | ||
382 | =over 4 | |
383 | ||
384 | =item I<US Mailing List Archives> | |
385 | ||
386 | http://outside.organic.com/mail-archives/dbi-users/ | |
387 | ||
388 | Searchable hypermail archives of the three mailing lists, and some of the | |
389 | much older traffic have been set up for users to browse. | |
390 | ||
391 | =item I<European Mailing List Archives> | |
392 | ||
393 | http://www.rosat.mpe-garching.mpg.de/mailing-lists/PerlDB-Interest | |
394 | ||
395 | As per the US archive above. | |
396 | ||
397 | =back | |
398 | ||
399 | =back | |
400 | ||
401 | =head1 Compilation Problems | |
402 | ||
403 | =head2 2.1. Compilation problems or "It fails the test!" | |
404 | ||
405 | First off, consult the README for that driver in case there is useful | |
406 | information about the problem. It may be a known problem for your given | |
407 | architecture and operating system or database. You can check the README | |
408 | files for each driver in advance online at: | |
409 | ||
410 | http://dbi.perl.org/ | |
411 | http://www.symbolstone.org/technology/perl/DBI | |
412 | ||
413 | If it's a known problem, you'll probably have to wait till it gets fixed. If | |
414 | you're I<really> needing it fixed, try the following: | |
415 | ||
416 | =over 4 | |
417 | ||
418 | =item I<Attempt to fix it yourself> | |
419 | ||
420 | This technique is generally I<not> recommended to the faint-hearted. | |
421 | If you do think you have managed to fix it, then, send a patch file | |
422 | ( context diff ) to the author with an explanation of: | |
423 | ||
424 | =over 4 | |
425 | ||
426 | =item * | |
427 | ||
428 | What the problem was, and test cases, if possible. | |
429 | ||
430 | =item * | |
431 | ||
432 | What you needed to do to fix it. Please make sure you mention everything. | |
433 | ||
434 | =item * | |
435 | ||
436 | Platform information, database version, perl version, module version and | |
437 | DBI version. | |
438 | ||
439 | =back | |
440 | ||
441 | =item I<Email the author> Do I<NOT> whinge! | |
442 | ||
443 | Please email the address listed in the WWW pages for whichever driver you | |
444 | are having problems with. Do I<not> directly email the author at a | |
445 | known address unless it corresponds with the one listed. | |
446 | ||
447 | We tend to have real jobs to do, and we do read the mailing lists for | |
448 | problems. Besides, we may not have access to <I<insert your | |
449 | favourite brain-damaged platform here>> and couldn't be of any | |
450 | assistance anyway! Apologies for sounding harsh, but that's the way of it! | |
451 | ||
452 | However, you might catch one of these creative genii at 3am when we're | |
453 | doing this sort of stuff anyway, and get a patch within 5 minutes. The | |
454 | atmosphere in the DBI circle is that we I<do> appreciate the users' | |
455 | problems, since we work in similar environments. | |
456 | ||
457 | If you are planning to email the author, please furnish as much information | |
458 | as possible, I<ie>: | |
459 | ||
460 | =over 4 | |
461 | ||
462 | =item * | |
463 | ||
464 | I<ALL> the information asked for in the README file in | |
465 | the problematic module. And we mean I<ALL> of it. We don't | |
466 | put lines like that in documentation for the good of our health, or | |
467 | to meet obscure README file standards of length. | |
468 | ||
469 | =item * | |
470 | ||
471 | If you have a core dump, try the I<Devel::CoreStack> module for | |
472 | generating a stack trace from the core dump. Send us that too. | |
473 | I<Devel::CoreStack> can be found on CPAN at: | |
474 | ||
475 | http://www.perl.com/cgi-bin/cpan_mod?module=Devel::CoreStack | |
476 | ||
477 | =item * | |
478 | ||
479 | Module versions, perl version, test cases, operating system versions | |
480 | and I<any other pertinent information>. | |
481 | ||
482 | =back | |
483 | ||
484 | Remember, the more information you send us, the quicker we can track | |
485 | problems down. If you send us no useful information, expect nothing back. | |
486 | ||
487 | Finally, please be aware that some authors, including Tim Bunce, specifically | |
488 | request that you do I<not> mail them directly. Please respect their wishes and | |
489 | use the email addresses listed in the appropriate module C<README> file. | |
490 | ||
491 | =item I<Email the dbi-users Mailing List> | |
492 | ||
493 | It's usually a fairly intelligent idea to I<cc> the mailing list | |
494 | anyway with problems. The authors all read the lists, so you lose nothing | |
495 | by mailing there. | |
496 | ||
497 | =back | |
498 | ||
499 | =head1 Platform and Driver Issues | |
500 | ||
501 | =head2 3.1 What's the difference between ODBC and DBI? | |
502 | ||
503 | In terms of architecture - not much: Both define programming | |
504 | interfaces. Both allow multiple drivers to be loaded to do the | |
505 | actual work. | |
506 | ||
507 | In terms of ease of use - much: The DBI is a 'high level' interface | |
508 | that, like Perl itself, strives to make the simple things easy while | |
509 | still making the hard things possible. The ODBC is a 'low level' | |
510 | interface. All nuts-bolts-knobs-and-dials. | |
511 | ||
512 | Now there's an ODBC driver for the DBI (DBD::ODBC) the "What's the | |
513 | difference" question is more usefully rephrased as: | |
514 | ||
515 | Chapter 7 of "Programming the Perl DBI" covers this topic in far more | |
516 | detail and should be consulted. | |
517 | ||
518 | =head2 3.2 What's the difference between Win32::ODBC and DBD::ODBC? | |
519 | ||
520 | The DBI, and thus DBD::ODBC, has a different philosophy from the | |
521 | Win32::ODBC module: | |
522 | ||
523 | The Win32::ODBC module is a 'thin' layer over the low-level ODBC API. | |
524 | The DBI defines a simpler 'higher level' interface. | |
525 | ||
526 | The Win32::ODBC module gives you access to more of the ODBC API. | |
527 | The DBI and DBD::ODBC give you access to only the essentials. | |
528 | (But, unlike Win32::ODBC, the DBI and DBD::ODBC do support parameter | |
529 | binding and multiple prepared statements which reduces the load on | |
530 | the database server and can dramatically increase performance.) | |
531 | ||
532 | The Win32::ODBC module only works on Win32 systems. | |
533 | The DBI and DBD::ODBC are very portable and work on Win32 and Unix. | |
534 | ||
535 | The DBI and DBD::ODBC modules are supplied as a standard part of the | |
536 | Perl 5.004 binary distribution for Win32 (they don't work with the | |
537 | older, non-standard, ActiveState port). | |
538 | ||
539 | Scripts written with the DBI and DBD::ODBC are faster than Win32::ODBC | |
540 | on Win32 and are trivially portable to other supported database types. | |
541 | ||
542 | The DBI offers optional automatic printing or die()ing on errors which | |
543 | makes applications simpler and more robust. | |
544 | ||
545 | The current DBD::ODBC driver version 0.16 is new and not yet fully stable. | |
546 | A new release is due soon [relative to the date of the next TPJ issue :-] | |
547 | and will be much improved and offer more ODBC functionality. | |
548 | ||
549 | To summarise: The Win32::ODBC module is your best choice if you need | |
550 | access to more of the ODBC API than the DBI gives you. Otherwise, the | |
551 | DBI and DBD::ODBC combination may be your best bet. | |
552 | ||
553 | Chapter 7 of "Programming the Perl DBI" covers this topic in far more | |
554 | detail and should be consulted. | |
555 | ||
556 | =head2 3.3 Is DBI supported under Windows 95 / NT platforms? | |
557 | ||
558 | Finally, yes! Jeff Urlwin has been working diligently on building | |
559 | I<DBI> and I<DBD::ODBC> under these platforms, and, with the | |
560 | advent of a stabler perl and a port of I<MakeMaker>, the project has | |
561 | come on by great leaps and bounds. | |
562 | ||
563 | The I<DBI> and I<DBD::Oracle> Win32 ports are now a standard part of DBI, | |
564 | so, downloading I<DBI> of version higher than I<0.81> should work fine as | |
565 | should using the most recent I<DBD::Oracle> version. | |
566 | ||
567 | =head2 3.4 Can I access Microsoft Access or SQL-Server databases with DBI? | |
568 | ||
569 | Yes, use the I<DBD::ODBC> driver. | |
570 | ||
571 | =head2 3.5 Is the a DBD for <I<insert favourite database here>>? | |
572 | ||
573 | Is is listed on the DBI drivers page? | |
574 | ||
575 | http://dbi.perl.org/ | |
576 | http://www.symbolstone.org/technology/perl/DBI | |
577 | ||
578 | If not, no. A complete absence of a given database driver from that | |
579 | page means that no-one has announced any intention to work on it, not that | |
580 | such a driver is impossible to write. | |
581 | ||
582 | A corollary of the above statement implies that if you see an announcement | |
583 | for a driver I<not> on the above page, there's a good chance it's not | |
584 | actually a I<DBI> driver, and may not conform to the specifications. Therefore, | |
585 | questions concerning problems with that code should I<not> really be addressed | |
586 | to the DBI Mailing Lists. | |
587 | ||
588 | =head2 3.6 What's DBM? And why should I use DBI instead? | |
589 | ||
590 | Extracted from ``I<DBI - The Database Interface for Perl 5>'': | |
591 | ||
592 | ``UNIX was originally blessed with simple file-based ``databases'', namely | |
593 | the dbm system. dbm lets you store data in files, and retrieve | |
594 | that data quickly. However, it also has serious drawbacks. | |
595 | ||
596 | File Locking | |
597 | ||
598 | The dbm systems did not allow particularly robust file locking | |
599 | capabilities, nor any capability for correcting problems arising through | |
600 | simultaneous writes [ to the database ]. | |
601 | ||
602 | Arbitrary Data Structures | |
603 | ||
604 | The dbm systems only allows a single fixed data structure: | |
605 | key-value pairs. That value could be a complex object, such as a | |
606 | [ C ] struct, but the key had to be unique. This was a large | |
607 | limitation on the usefulness of dbm systems. | |
608 | ||
609 | However, dbm systems still provide a useful function for users with | |
610 | simple datasets and limited resources, since they are fast, robust and | |
611 | extremely well-tested. Perl modules to access dbm systems have now | |
612 | been integrated into the core Perl distribution via the | |
613 | AnyDBM_File module.'' | |
614 | ||
615 | To sum up, DBM is a perfectly satisfactory solution for essentially read-only | |
616 | databases, or small and simple datasets. However, for more | |
617 | scaleable dataset handling, not to mention robust transactional locking, | |
618 | users are recommended to use a more powerful database engine I<via> I<DBI>. | |
619 | ||
620 | Chapter 2 of "Programming the Perl DBI" discusses DBM files in detail. | |
621 | ||
622 | =head2 3.7 What database do you recommend me using? | |
623 | ||
624 | This is a particularly thorny area in which an objective answer is difficult | |
625 | to come by, since each dataset, proposed usage and system configuration | |
626 | differs from person to person. | |
627 | ||
628 | From the current author's point of view, if the dataset is relatively | |
629 | small, being tables of less than 1 million rows, and less than 1000 tables | |
630 | in a given database, then I<mSQL> is a perfectly acceptable solution | |
631 | to your problem. This database is extremely cheap, is wonderfully robust | |
632 | and has excellent support. More information is available on the Hughes | |
633 | Technology WWW site at: | |
634 | ||
635 | http://www.hughes.com.au | |
636 | ||
637 | You may also wish to look at MySQL which is a more powerful database engine | |
638 | that has a similar feel to mSQL. | |
639 | ||
640 | http://www.tcx.se | |
641 | ||
642 | If the dataset is larger than 1 million row tables or 1000 tables, or if you | |
643 | have either more money, or larger machines, I would recommend I<Oracle RDBMS>. | |
644 | Oracle's WWW site is an excellent source of more information. | |
645 | ||
646 | http://www.oracle.com | |
647 | ||
648 | I<Informix> is another high-end RDBMS that is worth considering. There are | |
649 | several differences between Oracle and Informix which are too complex for | |
650 | this document to detail. Information on Informix can be found on their | |
651 | WWW site at: | |
652 | ||
653 | http://www.informix.com | |
654 | ||
655 | In the case of WWW fronted applications, I<mSQL> may be a better option | |
656 | due to slow connection times between a CGI script and the Oracle RDBMS and | |
657 | also the amount of resource each Oracle connection will consume. I<mSQL> | |
658 | is lighter resource-wise and faster. | |
659 | ||
660 | These views are not necessarily representative of anyone else's opinions, | |
661 | and do not reflect any corporate sponsorship or views. They are provided | |
662 | I<as-is>. | |
663 | ||
664 | =head2 3.8 Is <I<insert feature here>> supported in DBI? | |
665 | ||
666 | Given that we're making the assumption that the feature you have requested | |
667 | is a non-standard database-specific feature, then the answer will be I<no>. | |
668 | ||
669 | DBI reflects a I<generic> API that will work for most databases, and has | |
670 | no database-specific functionality. | |
671 | ||
672 | However, driver authors may, if they so desire, include hooks to database-specific | |
673 | functionality through the C<func()> method defined in the DBI API. | |
674 | Script developers should note that use of functionality provided I<via> | |
675 | the C<func()> methods is very unlikely to be portable across databases. | |
676 | ||
677 | =head1 Programming Questions | |
678 | ||
679 | =head2 4.1 Is DBI any use for CGI programming? | |
680 | ||
681 | In a word, yes! DBI is hugely useful for CGI programming! In fact, I would | |
682 | tentatively say that CGI programming is one of two top uses for DBI. | |
683 | ||
684 | DBI confers the ability to CGI programmers to power WWW-fronted databases | |
685 | to their users, which provides users with vast quantities of ordered | |
686 | data to play with. DBI also provides the possibility that, if a site is | |
687 | receiving far too much traffic than their database server can cope with, they | |
688 | can upgrade the database server behind the scenes with no alterations to | |
689 | the CGI scripts. | |
690 | ||
691 | =head2 4.2 How do I get faster connection times with DBD::Oracle and CGI? | |
692 | ||
693 | Contributed by John D. Groenveld | |
694 | ||
695 | The Apache C<httpd> maintains a pool of C<httpd> children to service client | |
696 | requests. | |
697 | ||
698 | Using the Apache I<mod_perl> module by I<Doug MacEachern>, the perl | |
699 | interpreter is embedded with the C<httpd> children. The CGI, DBI, and your | |
700 | other favorite modules can be loaded at the startup of each child. These | |
701 | modules will not be reloaded unless changed on disk. | |
702 | ||
703 | For more information on Apache, see the Apache Project's WWW site: | |
704 | ||
705 | http://www.apache.org | |
706 | ||
707 | The I<mod_perl> module can be downloaded from CPAN I<via>: | |
708 | ||
709 | http://www.perl.com/cgi-bin/cpan_mod?module=Apache | |
710 | ||
711 | =head2 4.3 How do I get persistent connections with DBI and CGI? | |
712 | ||
713 | Contributed by John D. Groenveld | |
714 | ||
715 | Using Edmund Mergl's I<Apache::DBI> module, database logins are stored in a | |
716 | hash with each of these C<httpd> child. If your application is based on a | |
717 | single database user, this connection can be started with each child. | |
718 | Currently, database connections cannot be shared between C<httpd> children. | |
719 | ||
720 | I<Apache::DBI> can be downloaded from CPAN I<via>: | |
721 | ||
722 | http://www.perl.com/cgi-bin/cpan_mod?module=Apache::DBI | |
723 | ||
724 | =head2 4.4 ``When I run a perl script from the command line, it works, but, when I run it under the C<httpd>, it fails!'' Why? | |
725 | ||
726 | Basically, a good chance this is occurring is due to the fact that the user | |
727 | that you ran it from the command line as has a correctly configured set of | |
728 | environment variables, in the case of I<DBD::Oracle>, variables like | |
729 | C<ORACLE_HOME>, C<ORACLE_SID> or C<TWO_TASK>. | |
730 | ||
731 | The C<httpd> process usually runs under the user id of C<nobody>, | |
732 | which implies there is no configured environment. Any scripts attempting to | |
733 | execute in this situation will correctly fail. | |
734 | ||
735 | One way to solve this problem is to set the environment for your database in a | |
736 | C<BEGIN { }> block at the top of your script. Another technique is to configure | |
737 | your WWW server to pass-through certain environment variables to your CGI | |
738 | scripts. | |
739 | ||
740 | Similarly, you should check your C<httpd> error logfile for any clues, | |
741 | as well as the ``Idiot's Guide To Solving Perl / CGI Problems'' and | |
742 | ``Perl CGI Programming FAQ'' for further information. It is | |
743 | unlikely the problem is DBI-related. | |
744 | ||
745 | The ``Idiot's Guide To Solving Perl / CGI Problems'' can be located at: | |
746 | ||
747 | http://www.perl.com/perl/faq/index.html | |
748 | ||
749 | as can the ``Perl CGI Programming FAQ''. Read I<BOTH> these documents | |
750 | carefully! | |
751 | ||
752 | =head2 4.5 How do I get the number of rows returned from a C<SELECT> statement? | |
753 | ||
754 | Count them. Read the DBI docs for the C<rows()> method. | |
755 | ||
756 | =head1 Miscellaneous Questions | |
757 | ||
758 | =head2 5.1 Can I do multi-threading with DBI? | |
759 | ||
760 | Perl version 5.005 and later can be built to support multi-threading. | |
761 | The DBI, as of version 1.02, does not yet support multi-threading | |
762 | so it would be unsafe to let more than one thread enter the DBI at | |
763 | the same time. | |
764 | ||
765 | It is expected that some future version of the DBI will at least be | |
766 | thread-safe (but not thread-hot) by automatically blocking threads | |
767 | intering the DBI while it's already in use. | |
768 | ||
769 | For some OCI example code for Oracle that has multi-threaded C<SELECT> | |
770 | statements, see: | |
771 | ||
772 | http://www.symbolstone.org/technology/oracle/oci/orathreads.tar.gz | |
773 | ||
774 | =head2 5.2 How do I handle BLOB data with DBI? | |
775 | ||
776 | Handling BLOB data with the DBI is very straight-forward. BLOB columns are | |
777 | specified in a SELECT statement as per normal columns. However, you also | |
778 | need to specify a maximum BLOB size that the <I>database handle</I> can | |
779 | fetch using the C<LongReadLen> attribute. | |
780 | ||
781 | For example: | |
782 | ||
783 | ### $dbh is a connected database handle | |
784 | $sth = $dbh->prepare( "SELECT blob_column FROM blobby_table" ); | |
785 | $sth->execute; | |
786 | ||
787 | would fail. | |
788 | ||
789 | ### $dbh is a connected database handle | |
790 | ### Set the maximum BLOB size... | |
791 | $dbh->{LongReadLen} = 16384; ### 16Kb...Not much of a BLOB! | |
792 | ||
793 | $sth = $dbh->prepare( "..." ); | |
794 | ||
795 | would succeed <I>provided no column values were larger than the specified | |
796 | value</I>. | |
797 | ||
798 | If the BLOB data is longer than the value of C<LongReadLen>, then an | |
799 | error will occur. However, the DBI provides an additional piece of | |
800 | functionality that will automatically truncate the fetched BLOB to the | |
801 | size of C<LongReadLen> if it is longer. This does not cause an error to | |
802 | occur, but may make your fetched BLOB data useless. | |
803 | ||
804 | This behaviour is regulated by the C<LongTruncOk> attribute which is | |
805 | defaultly set to a false value ( thus making overlong BLOB fetches fail ). | |
806 | ||
807 | ### Set BLOB handling such that it's 16Kb and can be truncated | |
808 | $dbh->{LongReadLen} = 16384; | |
809 | $dbh->{LongTruncOk} = 1; | |
810 | ||
811 | Truncation of BLOB data may not be a big deal in cases where the BLOB | |
812 | contains run-length encoded data, but data containing checksums at the end, | |
813 | for example, a ZIP file, would be rendered useless. | |
814 | ||
815 | =head2 5.3 How can I invoke stored procedures with DBI? | |
816 | ||
817 | The DBI does not define a database-independent way of calling stored procedures. | |
818 | ||
819 | However, most database that support them also provide a way to call | |
820 | them from SQL statements - and the DBI certainly supports that. | |
821 | ||
822 | So, assuming that you have created a stored procedure within the target | |
823 | database, I<eg>, an Oracle database, you can use C<$dbh>->C<do()> to | |
824 | immediately execute the procedure. For example, | |
825 | ||
826 | $dbh->do( "BEGIN someProcedure; END;" ); # Oracle-specific | |
827 | ||
828 | You should also be able to C<prepare> and C<execute>, which is | |
829 | the recommended way if you'll be calling the procedure often. | |
830 | ||
831 | =head2 5.4 How can I get return values from stored procedures with DBI? | |
832 | ||
833 | Contributed by Jeff Urlwin | |
834 | ||
835 | $sth = $dbh->prepare( "BEGIN foo(:1, :2, :3); END;" ); | |
836 | $sth->bind_param(1, $a); | |
837 | $sth->bind_param_inout(2, \$path, 2000); | |
838 | $sth->bind_param_inout(3, \$success, 2000); | |
839 | $sth->execute; | |
840 | ||
841 | Remember to perform error checking, though! ( Or use the C<RaiseError> | |
842 | attribute ). | |
843 | ||
844 | =head2 5.5 How can I create or drop a database with DBI? | |
845 | ||
846 | Database creation and deletion are concepts that are entirely too abstract | |
847 | to be adequately supported by DBI. For example, Oracle does not support the | |
848 | concept of dropping a database at all! Also, in Oracle, the database | |
849 | I<server> essentially I<is> the database, whereas in mSQL, the | |
850 | server process runs happily without any databases created in it. The | |
851 | problem is too disparate to attack in a worthwhile way. | |
852 | ||
853 | Some drivers, therefore, support database creation and deletion through | |
854 | the private C<func()> methods. You should check the documentation for | |
855 | the drivers you are using to see if they support this mechanism. | |
856 | ||
857 | =head2 5.6 How can I C<commit> or C<rollback> a statement with DBI? | |
858 | ||
859 | See the C<commit()> and C<rollback()> methods in the DBI Specification. | |
860 | ||
861 | Chapter 6 of "Programming the Perl DBI" discusses transaction handling within | |
862 | the context of DBI in more detail. | |
863 | ||
864 | =head2 5.7 How are C<NULL> values handled by DBI? | |
865 | ||
866 | C<NULL> values in DBI are specified to be treated as the value C<undef>. | |
867 | C<NULL>s can be inserted into databases as C<NULL>, for example: | |
868 | ||
869 | $rv = $dbh->do( "INSERT INTO table VALUES( NULL )" ); | |
870 | ||
871 | but when queried back, the C<NULL>s should be tested against C<undef>. | |
872 | This is standard across all drivers. | |
873 | ||
874 | =head2 5.8 What are these C<func()> methods all about? | |
875 | ||
876 | The C<func()> method is defined within DBI as being an entry point | |
877 | for database-specific functionality, I<eg>, the ability to create or | |
878 | drop databases. Invoking these driver-specific methods is simple, for example, | |
879 | to invoke a C<createDatabase> method that has one argument, we would | |
880 | write: | |
881 | ||
882 | $rv =$dbh->func( 'argument', 'createDatabase' ); | |
883 | ||
884 | Software developers should note that the C<func()> methods are | |
885 | non-portable between databases. | |
886 | ||
887 | =head2 5.9 Is DBI Year 2000 Compliant? | |
888 | ||
889 | DBI has no knowledge of understanding of what dates are. Therefore, DBI | |
890 | itself does not have a Year 2000 problem. Individual drivers may use date | |
891 | handling code internally and therefore be potentially susceptible to the | |
892 | Year 2000 problem, but this is unlikely. | |
893 | ||
894 | You may also wish to read the ``Does Perl have a Year 2000 problem?'' section | |
895 | of the Perl FAQ at: | |
896 | ||
897 | http://www.perl.com/CPAN/doc/FAQs/FAQ/PerlFAQ.html | |
898 | ||
899 | =head1 Support and Training | |
900 | ||
901 | The Perl5 Database Interface is I<FREE> software. IT COMES WITHOUT WARRANTY | |
902 | OF ANY KIND. See the DBI README for more details. | |
903 | ||
904 | However, some organizations are providing either technical support or | |
905 | training programs on DBI. The present author has no knowledge as | |
906 | to the quality of these services. The links are included for reference | |
907 | purposes only and should not be regarded as recommendations in any way. | |
908 | I<Caveat emptor>. | |
909 | ||
910 | =head2 Commercial Support | |
911 | ||
912 | =over 4 | |
913 | ||
914 | =item The Perl Clinic | |
915 | ||
916 | The Perl Clinic provides commercial support for I<Perl> and Perl | |
917 | related problems, including the I<DBI> and its drivers. Support is | |
918 | provided by the company with whom Tim Bunce, author of I<DBI> and | |
919 | I<DBD::Oracle>, works and ActiveState. For more information on their | |
920 | services, please see: | |
921 | ||
922 | http://www.perlclinic.com | |
923 | ||
924 | =back | |
925 | ||
926 | =head2 Training | |
927 | ||
928 | =over 4 | |
929 | ||
930 | =item Westlake Solutions | |
931 | ||
932 | A hands-on class for experienced Perl CGI developers that teaches | |
933 | how to write database-connected CGI scripts using Perl and DBI.pm. This | |
934 | course, along with four other courses on CGI scripting with Perl, is | |
935 | taught in Washington, DC; Arlington, Virginia; and on-site worldwide upon | |
936 | request. | |
937 | ||
938 | See: | |
939 | ||
940 | http://www.westlake.com/training | |
941 | ||
942 | for more details. | |
943 | ||
944 | =back | |
945 | ||
946 | =head1 Other References | |
947 | ||
948 | In this section, we present some miscellaneous WWW links that may be of | |
949 | some interest to DBI users. These are not verified and may result in | |
950 | unknown sites or missing documents. | |
951 | ||
952 | http://www-ccs.cs.umass.edu/db.html | |
953 | http://www.odmg.org/odmg93/updates_dbarry.html | |
954 | http://www.jcc.com/sql_stnd.html | |
955 | ||
956 | =head1 AUTHOR | |
957 | ||
958 | Alligator Descartes <I<http://www.symbolstone.org/descarte/contact.html>>. | |
959 | Portions are Copyright their original stated authors. | |
960 | ||
961 | =head1 COPYRIGHT | |
962 | ||
963 | This document is Copyright (c)1994-2000 Alligator Descartes, with portions | |
964 | Copyright (c)1994-2000 their original authors. This module is released under | |
965 | the 'Artistic' license which you can find in the perl distribution. | |
966 | ||
967 | This document is Copyright (c)1997-2000 Alligator Descartes. All rights reserved. | |
968 | Permission to distribute this document, in full or in part, via email, | |
969 | Usenet, ftp archives or http is granted providing that no charges are involved, | |
970 | reasonable attempt is made to use the most current version and all credits | |
971 | and copyright notices are retained ( the I<AUTHOR> and I<COPYRIGHT> sections ). | |
972 | Requests for other distribution rights, including incorporation into | |
973 | commercial products, such as books, magazine articles or CD-ROMs should be | |
974 | made to Alligator Descartes <I<http://www.symbolstone.org/descarte/contact.html>>. | |
975 | ||
976 | =for html | |
977 | <!--#include virtual="/technology/perl/DBI/templatebottom.html" --> | |
978 | </BODY> | |
979 | </HTML> |