1 / 29

Integrating Perl and Databases

Integrating Perl and Databases. Making simple things easy and difficult things possible Tim Bunce. August 1997. Why?. Why Perl?. Fewer lines of code faster development / fewer errors / easier maintenance “A picture is worth a thousand words” vs “Can’t see the wood for the trees”

yolanda-terrell
Download Presentation

Integrating Perl and Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IntegratingPerl and Databases Making simple things easyand difficult things possible Tim Bunce August 1997

  2. Why?

  3. Why Perl? • Fewer lines of code • faster development / fewer errors / easier maintenance • “A picture is worth a thousand words” vs “Can’t see the wood for the trees” • Freedom of expression - “There’s more than one way to do it” • The tool builders tool factory

  4. Why Perl for Databases? • Ideally suited to data processing: • parsing, cleansing / filtering / merging and formatting • Applications • Data Migration • Data acquisition and reporting • Web Page creation • Building sharper tools faster

  5. Your Mission... should you choose to accept it...

  6. The Task • Read a tabular text report file of product codes and costs CODE DESCRIPTION COST OTHER NOTES ---- ----------- ------ ------------------- 1201 WIDGET 37A 500.00 LEFT HANDED ONLY 1202 BRACKET X7J 790.00 REDUCED FROM 800.00 1203 FLANGE 9Q7 449.00 LIMITED STOCK • Read a CSV file of product codes, price markup and comments 1201,0.90,Special Offer for limited period only 1202,1.30, • Output a new tabular report containing the new product prices and comments word wrapped in a narrow field

  7. Reading the CSV file open MARGIN, “<margin.csv” or die “open margin.csv: $!”; while (<MARGIN>) { chop; ($prod, $margin, $notes) = split /,/; $prod_margin{$prod} = $margin; $prod_notes{$prod} = $notes; }

  8. Defining the Output Format format STDOUT_TOP = Code Price Notes ---- ------- -------- . format STDOUT = @>>> ^###.## ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< $prod, $cost, $note ~~ ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< $note .

  9. Writing the New Report open COST, "<cost.txt" or die "open cost.txt: $!"; while ($line = <COST>) { next unless $line =~ m/^\d/; ($prod, $cost) = unpack 'A4 @20 A7', $line; $cost *= $prod_margin{$prod} || 1.10; $note = $prod_notes{$prod} || 'n/a'; write; # using format with $prod, $cost and $note }

  10. Mission Accomplished! Code Price Notes ---- ------- -------- 1201 450.00 Special offer for limited period only 1202 1027.00 n/a 1203 493.90 n/a

  11. Changeis the only Constant Your new mission...

  12. “Cache the data for other uses!” dbmopen(%price_cache, "price_cache", 0644) or die …; while ($line = <COST>) { . . . write; # using format with $prod, $cost and $note $price_cache{$prod} = "$cost $note"; }

  13. “Update the Prices in a Database!” use DBI; $db = DBI->connect('dbi:ODBC:PRICE', 'user', 'password', { RaiseError => 1 }); $upd = $db->prepare('UPDATE prices SET price=? WHERE prod=?'); $ins = $db->prepare('INSERT INTO prices(prod,price) VALUES (?,?)'); while ($line = <COST>) { . . . $rows = $upd->execute($price, $prod); $ins->execute($prod,$price) if $rows == 0; } $db->commit;

  14. “Now get Cost Data from a Database!” • Original Code open COST, "<cost.txt" or die "open cost.txt: $!"; while ($line = <COST>) { next unless $line =~ m/^\d/; ($prod, $cost) = unpack 'A4 @20 A7', $line; . . . • New Code $costs = $cost_db->prepare('SELECT prod,cost FROM costs'); $costs->execute; while ( ($prod, $cost) = $costs->fetchrow_array ) { . . .

  15. What Is the Perl DBI? • The standard Database Interface for Perl • “A perl module and specification that defines a consistent database interface independent of the actual database being used”

  16. Why the Perl DBI? • Once upon a time… • One language, many database interfaces • A vision of the future... “DBperl” • Talk, talk, talk - the dbperl-interest mailing list • Perl 5 - A new way • Modules and Objects. The DBI is born. • The future is now… • ODBC, Oracle, Informix, Ingres, mSQL, mysql, DB2, Solid,Sybase, Postgress,Quickbase,Empress,Fulcrum, ...

  17. Making simple things easyand difficult things possible • Goals • Be simple to use for simple applications • Have sufficient flexibility to accommodate unusual functionality and non-SQL databases • Conform to applicable standards (ODBC etc.) • Enable the creation of database-independent Perl scripts without being limited to the lowest functionality • Be free. • A ‘higher-level’ interface than ODBC/JDBC

  18. Getting Technical... for the techies...

  19. Under the Hood • DBI defines and implements an interface • Driver modules do much of the real work • DBI provides default methods, functions, tools etc for drivers • Not limited to the lowest common denominator - mechanism provided for driver specific extensions • Designed and built for speed • Valuable detailed call tracing/debugging built-in

  20. A Picture is Worth... Perl Application DBI Module DBD::Oracle DBD::Informix DBD::Other Oracle Server Informix Server Other Server

  21. State of the Nation • DBI builds and runs on many systems • Unix, Windows 95, Windows NT, VMS etc • Many drivers exist • more in development • Merging the ODBC/JDBC ‘inheritance’ • data types, meta-data etc • Specifying the unspecified • Documenting the undocumented

  22. What Next? • SQL for Non-SQL data sources • Networking for Non-Networked data sources • Template driver for command line interfaces • Data types, especially date/time/interval • Non-blocking (method calls return at once) • Multi-threading • Binding In/Out Variables • Binding Arrays

  23. Comparing DBI and Alternatives(Attempting the Impossible!) “Power” DBI and Drivers Sybase::DBlib Win32::ODBC Native API ODBC/ JDBC and drivers Precompilers Command Line TCL (oratcl, sybtcl) “Effort” Please note: This chart is correct only for some values of correct!

  24. So why use the Perl DBI? • Because... • It delivers what it promises • It’s here, there and everywhere • It’s fast, flexible and well proven • It’s free, with source • Commercial support is available • It has a large user base and a strong future

  25. So What?

  26. Success Stories in Industry • Large Scale Data Migration • by Michael Peppler • Database Interfaced Web Pages • by Frank San Filippo • The British TelecomCall Management Information Service • by Tim Bunce

  27. Too Good to Be True? • Just three examples of many • Thousands of Perl DBI developers around the world • Millions of users of DBI powered web sites • Oracle and Apache web servers with built-in Perl and DBI • Commercial support available from The Perl Clinic

  28. And finally... • “We have replaced all Netscape JavaScript code with Perl DBI/DBD to Informix” - Del Simmons, Cox Interactive Media. • “I have found performance and stability to be exceptional” - Stuart Huges, PECC. • “Perl and DBI saved our butt. The tool we came closest to purchasing was SQR but it just doesn’t have the flexibility and power of Perl/DBI.” - Jared Still, RxNet. • “I have been able to recommend the Perl/DBI route to a number of our clients and all of them have been pleased with the results” - Paul Rubenis, Ulysses Telemedia Networks

  29. The end. Till next year...

More Related