From 7c48e67cf07ee41bfde7139a62bb232bd23a4a48 Mon Sep 17 00:00:00 2001 From: Lorry Tar Creator Date: Wed, 6 Jun 2012 16:41:29 +0000 Subject: Imported from /srv/lorry/lorry-area/perl-dbi-tarball/DBI-1.622.tar.gz. --- lib/DBI/FAQ.pm | 966 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 966 insertions(+) create mode 100644 lib/DBI/FAQ.pm (limited to 'lib/DBI/FAQ.pm') diff --git a/lib/DBI/FAQ.pm b/lib/DBI/FAQ.pm new file mode 100644 index 0000000..1ad760b --- /dev/null +++ b/lib/DBI/FAQ.pm @@ -0,0 +1,966 @@ +### +### $Id: FAQ.pm 14934 2011-09-14 10:02:25Z timbo $ +### +### DBI Frequently Asked Questions POD +### +### Copyright section reproduced from below. +### +### This document is Copyright (c)1994-2000 Alligator Descartes, with portions +### Copyright (c)1994-2000 their original authors. This module is released under +### the 'Artistic' license which you can find in the perl distribution. +### +### This document is Copyright (c)1997-2000 Alligator Descartes. All rights reserved. +### Permission to distribute this document, in full or in part, via email, +### Usenet, ftp archives or http is granted providing that no charges are involved, +### reasonable attempt is made to use the most current version and all credits +### and copyright notices are retained ( the I and I sections ). +### Requests for other distribution rights, including incorporation into +### commercial products, such as books, magazine articles or CD-ROMs should be +### made to Alligator Descartes. +### + +package DBI::FAQ; + +our $VERSION = sprintf("1.%06d", q$Revision: 14934 $ =~ /(\d+)/o); + + +=head1 NAME + +DBI::FAQ -- The Frequently Asked Questions for the Perl5 Database Interface + +=for html + + + + +DBI Frequently Asked Questions v.0.38 + +
+ +Last updated: February 8th, 2000 + + +

+ +=head1 SYNOPSIS + + perldoc DBI::FAQ + +=head1 VERSION + +This document is currently at version I<0.38>, as of I. + +That's B old. A newer FAQ can be found at L + +Neither this document nor that web site are actively maintained. +Volunteers are welcome. + +=head1 DESCRIPTION + +This document serves to answer the most frequently asked questions on both +the DBI Mailing Lists and personally to members of the DBI development team. + +=head1 Basic Information & Information Sources + +=head2 1.1 What is DBI, DBperl, Oraperl and *perl? + +To quote Tim Bunce, the architect and author of DBI: + + DBI is a database access Application Programming Interface (API) + for the Perl Language. The DBI API Specification defines a set + of functions, variables and conventions that provide a consistent + database interface independent of the actual database being used. + +In simple language, the DBI interface allows users to access multiple database +types transparently. So, if you connecting to an Oracle, Informix, mSQL, Sybase +or whatever database, you don't need to know the underlying mechanics of the +3GL layer. The API defined by DBI will work on I these database types. + +A similar benefit is gained by the ability to connect to two I +databases of different vendor within the one perl script, I, I want +to read data from an Oracle database and insert it back into an Informix +database all within one program. The DBI layer allows you to do this simply +and powerfully. + + +=for html +Here's a diagram that demonstrates the principle: +

+ +

+ +I is the old name for the interface specification. It's usually +now used to denote perlI<4> modules on database interfacing, such as, +I, I, I and so on. These interfaces +didn't have a standard API and are generally I supported. + +Here's a list of DBperl modules, their corresponding DBI counterparts and +support information. I, the author's listed here generally +I maintain the DBI module for the same database. These email +addresses are unverified and should only be used for queries concerning the +perl4 modules listed below. DBI driver queries should be directed to the +I mailing list. + + Module Name Database Required Author DBI + ----------- ----------------- ------ --- + Sybperl Sybase Michael Peppler DBD::Sybase + + Oraperl Oracle 6 & 7 Kevin Stock DBD::Oracle + + Ingperl Ingres Tim Bunce & DBD::Ingres + Ted Lemon + + Interperl Interbase Buzz Moschetti DBD::Interbase + + Uniperl Unify 5.0 Rick Wargo None + + Pgperl Postgres Igor Metz DBD::Pg + + Btreeperl NDBM John Conover SDBM? + + Ctreeperl C-Tree John Conover None + + Cisamperl Informix C-ISAM Mathias Koerber None + + Duaperl X.500 Directory Eric Douglas None + User Agent + +However, some DBI modules have DBperl emulation layers, so, I +comes with an Oraperl emulation layer, which allows you to run legacy oraperl +scripts without modification. The emulation layer translates the oraperl API +calls into DBI calls and executes them through the DBI switch. + +Here's a table of emulation layer information: + + Module Emulation Layer Status + ------ --------------- ------ + DBD::Oracle Oraperl Complete + DBD::Informix Isqlperl Under development + DBD::Ingres Ingperl Complete? + DBD::Sybase Sybperl Working? ( Needs verification ) + DBD::mSQL Msqlperl Experimentally released with + DBD::mSQL-0.61 + +The I emulation is a special case. I is a perl5 driver +for I databases, but does not conform to the DBI Specification. It's +use is being deprecated in favour of I. I may be downloaded +from CPAN I: + + http://www.perl.com/cgi-bin/cpan_mod?module=Msqlperl + +=head2 1.2. Where can I get it from? + +The Comprehensive Perl Archive Network +resources should be used for retrieving up-to-date versions of the DBI +and drivers. CPAN may be accessed I Tom Christiansen's splendid +I program located at: + + http://www.perl.com/CPAN/ + +For more specific version information and exact URLs of drivers, please see +the DBI drivers list and the DBI module pages which can be found on: + + http://dbi.perl.org/ + +This list is automatically generated on a nightly basis from CPAN and should +be up-to-date. + +=head2 1.3. Where can I get more information? + +There are a few information sources on DBI. + +=over 4 + +=item I<"Programming the Perl DBI"> + +"Programming the Perl DBI" is the I book on the DBI written by +Alligator Descartes and Tim Bunce and published by O'Reilly & Associates. +The book was released on February 9th, 2000. + +The table of contents is: + + Preface + 1. Introduction + From Mainframes to Workstations + Perl + DBI in the Real World + A Historical Interlude and Standing Stones + 2. Basic Non-DBI Databases + Storage Managers and Layers + Query Languages and Data Functions + Standing Stones and the Sample Database + Flat-File Databases + Putting Complex Data into Flat Files + Concurrent Database Access and Locking + DBM Files and the Berkeley Database Manager + The MLDBM Module + Summary + 3. SQL and Relational Databases + The Relational Database Methodology + Datatypes and NULL Values + Querying Data + Modifying Data Within Tables + Creating and Destroying Tables + 4. Programming with the DBI + DBI Architecture + Handles + Data Source Names + Connection and Disconnection + Error Handling + Utility Methods and Functions + 5. Interacting with the Database + Issuing Simple Queries + Executing Non-SELECT Statements + Binding Parameters to Statements + Binding Output Columns + do() Versus prepare() + Atomic and Batch Fetching + 6. Advanced DBI + Handle Attributes and Metadata + Handling LONG/LOB Data + Transactions, Locking, and Isolation + 7. ODBC and the DBI + ODBC -- Embraced and Extended + DBI -- Thrashed and Mutated + The Nuts and Bolts of ODBC + ODBC from Perl + The Marriage of DBI and ODBC + Questions and Choices + Moving Between Win32::ODBC and the DBI + And What About ADO? + 8. DBI Shell and Database Proxying + dbish -- The DBI Shell + Database Proxying + A. DBI Specification + B. Driver and Database Characteristics + C. ASLaN Sacred Site Charter + Index + +The book should be available from all good bookshops and can be ordered online +either via O'Reilly & Associates + + http://www.oreilly.com/catalog/perldbi + +or Amazon + + http://www.amazon.com/exec/obidos/ASIN/1565926994/dbi + +=item I + +Is are chunks of documentation usually embedded within perl programs +that document the code ``I'', providing a useful resource for +programmers and users of modules. POD for DBI and drivers is beginning to +become more commonplace, and documentation for these modules can be read +with the C program included with Perl. + +=over 4 + +=item The DBI Specification + +The POD for the DBI Specification can be read with the: + + perldoc DBI + +command. The Specification also forms Appendix A of "Programming the Perl +DBI". + +=item Oraperl + +Users of the Oraperl emulation layer bundled with I, may read +up on how to program with the Oraperl interface by typing: + + perldoc Oraperl + +This will produce an updated copy of the original oraperl man page written by +Kevin Stock for perl4. The oraperl API is fully listed and described there. + +=item Drivers + +Users of the DBD modules may read about some of the private functions +and quirks of that driver by typing: + + perldoc + +For example, the I driver is bundled with driver-specific +documentation that can be accessed by typing + + perldoc DBD::mSQL + +=item Frequently Asked Questions + +This document, the I is also available as POD +documentation! You can read this on your own system by typing: + + perldoc DBI::FAQ + +This may be more convenient to persons not permanently, or conveniently, +connected to the Internet. The I module should be downloaded and +installed for the more up-to-date version. + +The version of I shipped with the C module may be slightly out +of date. + +=item POD in general + +Information on writing POD, and on the philosophy of POD in general, can be +read by typing: + + perldoc perlpod + +Users with the Tk module installed may be interested to learn there is a +Tk-based POD reader available called C, which formats POD in a convenient +and readable way. This is available I CPAN as the module called +I and is highly recommended. + +=back + +=item I + +The driver summaries that were produced for Appendix B of "Programming the +Perl DBI" are available online at: + + http://dbi.perl.org/ + +in the driver information table. These summaries contain standardised +information on each driver and database which should aid you in selecting +a database to use. It will also inform you quickly of any issues within +drivers or whether a driver is not fully compliant with the DBI Specification. + +=item I + + http://dbi.perl.org/tidbits + +There are a series of occasional rambles from various people on the +DBI mailing lists who, in an attempt to clear up a simple point, end up +drafting fairly comprehensive documents. These are quite often varying in +quality, but do provide some insights into the workings of the interfaces. + +=item I + +A list of articles discussing the DBI can be found on the DBI WWW page at: + + http://dbi.perl.org/ + +These articles are of varying quality and age, from the original Perl Journal +article written by Alligator and Tim, to more recent debacles published online +from about.com. + +=item I + +The I files included with each driver occasionally contains +some useful information ( no, really! ) that may be pertinent to the user. +Please read them. It makes our worthless existences more bearable. These +can all be read from the main DBI WWW page at: + + http://dbi.perl.org/ + +=item I + +There are three mailing lists for DBI: + + dbi-announce@perl.org -- for announcements, very low traffic + dbi-users@perl.org -- general user support + dbi-dev@perl.org -- for driver developers (no user support) + +For information on how to subscribe, set digest mode etc, and unsubscribe, +send an email message (the content will be ignored) to: + + dbi-announce-help@perl.org + dbi-users-help@perl.org + dbi-dev-help@perl.org + +=item I + +=over 4 + +=item I + + http://outside.organic.com/mail-archives/dbi-users/ + +Searchable hypermail archives of the three mailing lists, and some of the +much older traffic have been set up for users to browse. + +=item I + + http://www.rosat.mpe-garching.mpg.de/mailing-lists/PerlDB-Interest + +As per the US archive above. + +=back + +=back + +=head1 Compilation Problems + +=head2 2.1. Compilation problems or "It fails the test!" + +First off, consult the README for that driver in case there is useful +information about the problem. It may be a known problem for your given +architecture and operating system or database. You can check the README +files for each driver in advance online at: + + http://dbi.perl.org/ + +If it's a known problem, you'll probably have to wait till it gets fixed. If +you're I needing it fixed, try the following: + +=over 4 + +=item I + +This technique is generally I recommended to the faint-hearted. +If you do think you have managed to fix it, then, send a patch file +( context diff ) to the author with an explanation of: + +=over 4 + +=item * + +What the problem was, and test cases, if possible. + +=item * + +What you needed to do to fix it. Please make sure you mention everything. + +=item * + +Platform information, database version, perl version, module version and +DBI version. + +=back + +=item I Do I whinge! + +Please email the address listed in the WWW pages for whichever driver you +are having problems with. Do I directly email the author at a +known address unless it corresponds with the one listed. + +We tend to have real jobs to do, and we do read the mailing lists for +problems. Besides, we may not have access to > and couldn't be of any +assistance anyway! Apologies for sounding harsh, but that's the way of it! + +However, you might catch one of these creative genii at 3am when we're +doing this sort of stuff anyway, and get a patch within 5 minutes. The +atmosphere in the DBI circle is that we I appreciate the users' +problems, since we work in similar environments. + +If you are planning to email the author, please furnish as much information +as possible, I: + +=over 4 + +=item * + +I the information asked for in the README file in +the problematic module. And we mean I of it. We don't +put lines like that in documentation for the good of our health, or +to meet obscure README file standards of length. + +=item * + +If you have a core dump, try the I module for +generating a stack trace from the core dump. Send us that too. +I can be found on CPAN at: + + http://www.perl.com/cgi-bin/cpan_mod?module=Devel::CoreStack + +=item * + +Module versions, perl version, test cases, operating system versions +and I. + +=back + +Remember, the more information you send us, the quicker we can track +problems down. If you send us no useful information, expect nothing back. + +Finally, please be aware that some authors, including Tim Bunce, specifically +request that you do I mail them directly. Please respect their wishes and +use the email addresses listed in the appropriate module C file. + +=item I + +It's usually a fairly intelligent idea to I the mailing list +anyway with problems. The authors all read the lists, so you lose nothing +by mailing there. + +=back + +=head1 Platform and Driver Issues + +=head2 3.1 What's the difference between ODBC and DBI? + +In terms of architecture - not much: Both define programming +interfaces. Both allow multiple drivers to be loaded to do the +actual work. + +In terms of ease of use - much: The DBI is a 'high level' interface +that, like Perl itself, strives to make the simple things easy while +still making the hard things possible. The ODBC is a 'low level' +interface. All nuts-bolts-knobs-and-dials. + +Now there's an ODBC driver for the DBI (DBD::ODBC) the "What's the +difference" question is more usefully rephrased as: + +Chapter 7 of "Programming the Perl DBI" covers this topic in far more +detail and should be consulted. + +=head2 3.2 What's the difference between Win32::ODBC and DBD::ODBC? + +The DBI, and thus DBD::ODBC, has a different philosophy from the +Win32::ODBC module: + +The Win32::ODBC module is a 'thin' layer over the low-level ODBC API. +The DBI defines a simpler 'higher level' interface. + +The Win32::ODBC module gives you access to more of the ODBC API. +The DBI and DBD::ODBC give you access to only the essentials. +(But, unlike Win32::ODBC, the DBI and DBD::ODBC do support parameter +binding and multiple prepared statements which reduces the load on +the database server and can dramatically increase performance.) + +The Win32::ODBC module only works on Win32 systems. +The DBI and DBD::ODBC are very portable and work on Win32 and Unix. + +The DBI and DBD::ODBC modules are supplied as a standard part of the +Perl 5.004 binary distribution for Win32 (they don't work with the +older, non-standard, ActiveState port). + +Scripts written with the DBI and DBD::ODBC are faster than Win32::ODBC +on Win32 and are trivially portable to other supported database types. + +The DBI offers optional automatic printing or die()ing on errors which +makes applications simpler and more robust. + +The current DBD::ODBC driver version 0.16 is new and not yet fully stable. +A new release is due soon [relative to the date of the next TPJ issue :-] +and will be much improved and offer more ODBC functionality. + +To summarise: The Win32::ODBC module is your best choice if you need +access to more of the ODBC API than the DBI gives you. Otherwise, the +DBI and DBD::ODBC combination may be your best bet. + +Chapter 7 of "Programming the Perl DBI" covers this topic in far more +detail and should be consulted. + +=head2 3.3 Is DBI supported under Windows 95 / NT platforms? + +Finally, yes! Jeff Urlwin has been working diligently on building +I and I under these platforms, and, with the +advent of a stabler perl and a port of I, the project has +come on by great leaps and bounds. + +The I and I Win32 ports are now a standard part of DBI, +so, downloading I of version higher than I<0.81> should work fine as +should using the most recent I version. + +=head2 3.4 Can I access Microsoft Access or SQL-Server databases with DBI? + +Yes, use the I driver. + +=head2 3.5 Is there a DBD for >? + +First check if a driver is available on CPAN by searching for the name of the +database (including common abbreviations and aliases). + +Here's a general query that'll match all distributions: + + http://search.cpan.org/search?query=DBD&mode=dist + +If you can't find a driver that way, you could check if the database supports +ODBC drivers. If so then you could probably use the DBD::ODBC driver: + + http://search.cpan.org/dist/DBD-ODBC/ + +If not, then try asking on the dbi-users mailing list. + +=head2 3.6 What's DBM? And why should I use DBI instead? + +Extracted from ``I'': + + ``UNIX was originally blessed with simple file-based ``databases'', namely + the dbm system. dbm lets you store data in files, and retrieve + that data quickly. However, it also has serious drawbacks. + + File Locking + + The dbm systems did not allow particularly robust file locking + capabilities, nor any capability for correcting problems arising through + simultaneous writes [ to the database ]. + + Arbitrary Data Structures + + The dbm systems only allows a single fixed data structure: + key-value pairs. That value could be a complex object, such as a + [ C ] struct, but the key had to be unique. This was a large + limitation on the usefulness of dbm systems. + + However, dbm systems still provide a useful function for users with + simple datasets and limited resources, since they are fast, robust and + extremely well-tested. Perl modules to access dbm systems have now + been integrated into the core Perl distribution via the + AnyDBM_File module.'' + +To sum up, DBM is a perfectly satisfactory solution for essentially read-only +databases, or small and simple datasets. However, for more +scaleable dataset handling, not to mention robust transactional locking, +users are recommended to use a more powerful database engine I I. + +Chapter 2 of "Programming the Perl DBI" discusses DBM files in detail. + +=head2 3.7 What database do you recommend me using? + +This is a particularly thorny area in which an objective answer is difficult +to come by, since each dataset, proposed usage and system configuration +differs from person to person. + +From the current author's point of view, if the dataset is relatively +small, being tables of less than 1 million rows, and less than 1000 tables +in a given database, then I is a perfectly acceptable solution +to your problem. This database is extremely cheap, is wonderfully robust +and has excellent support. More information is available on the Hughes +Technology WWW site at: + + http://www.hughes.com.au + +You may also wish to look at MySQL which is a more powerful database engine +that has a similar feel to mSQL. + + http://www.tcx.se + +If the dataset is larger than 1 million row tables or 1000 tables, or if you +have either more money, or larger machines, I would recommend I. +Oracle's WWW site is an excellent source of more information. + + http://www.oracle.com + +I is another high-end RDBMS that is worth considering. There are +several differences between Oracle and Informix which are too complex for +this document to detail. Information on Informix can be found on their +WWW site at: + + http://www.informix.com + +In the case of WWW fronted applications, I may be a better option +due to slow connection times between a CGI script and the Oracle RDBMS and +also the amount of resource each Oracle connection will consume. I +is lighter resource-wise and faster. + +These views are not necessarily representative of anyone else's opinions, +and do not reflect any corporate sponsorship or views. They are provided +I. + +=head2 3.8 Is > supported in DBI? + +Given that we're making the assumption that the feature you have requested +is a non-standard database-specific feature, then the answer will be I. + +DBI reflects a I API that will work for most databases, and has +no database-specific functionality. + +However, driver authors may, if they so desire, include hooks to database-specific +functionality through the C method defined in the DBI API. +Script developers should note that use of functionality provided I +the C methods is very unlikely to be portable across databases. + +=head1 Programming Questions + +=head2 4.1 Is DBI any use for CGI programming? + +In a word, yes! DBI is hugely useful for CGI programming! In fact, I would +tentatively say that CGI programming is one of two top uses for DBI. + +DBI confers the ability to CGI programmers to power WWW-fronted databases +to their users, which provides users with vast quantities of ordered +data to play with. DBI also provides the possibility that, if a site is +receiving far too much traffic than their database server can cope with, they +can upgrade the database server behind the scenes with no alterations to +the CGI scripts. + +=head2 4.2 How do I get faster connection times with DBD::Oracle and CGI? + + Contributed by John D. Groenveld + +The Apache C maintains a pool of C children to service client +requests. + +Using the Apache I module by I, the perl +interpreter is embedded with the C children. The CGI, DBI, and your +other favorite modules can be loaded at the startup of each child. These +modules will not be reloaded unless changed on disk. + +For more information on Apache, see the Apache Project's WWW site: + + http://www.apache.org + +The I module can be downloaded from CPAN I: + + http://www.perl.com/cgi-bin/cpan_mod?module=Apache + +=head2 4.3 How do I get persistent connections with DBI and CGI? + + Contributed by John D. Groenveld + +Using Edmund Mergl's I module, database logins are stored in a +hash with each of these C child. If your application is based on a +single database user, this connection can be started with each child. +Currently, database connections cannot be shared between C children. + +I can be downloaded from CPAN I: + + http://www.perl.com/cgi-bin/cpan_mod?module=Apache::DBI + +=head2 4.4 ``When I run a perl script from the command line, it works, but, when I run it under the C, it fails!'' Why? + +Basically, a good chance this is occurring is due to the fact that the user +that you ran it from the command line as has a correctly configured set of +environment variables, in the case of I, variables like +C, C or C. + +The C process usually runs under the user id of C, +which implies there is no configured environment. Any scripts attempting to +execute in this situation will correctly fail. + +One way to solve this problem is to set the environment for your database in a +C block at the top of your script. Another technique is to configure +your WWW server to pass-through certain environment variables to your CGI +scripts. + +Similarly, you should check your C error logfile for any clues, +as well as the ``Idiot's Guide To Solving Perl / CGI Problems'' and +``Perl CGI Programming FAQ'' for further information. It is +unlikely the problem is DBI-related. + +The ``Idiot's Guide To Solving Perl / CGI Problems'' can be located at: + + http://www.perl.com/perl/faq/index.html + +as can the ``Perl CGI Programming FAQ''. Read I these documents +carefully! + +=head2 4.5 How do I get the number of rows returned from a C