| 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> |