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