Integrating Perl and Databases

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 Cant
  see the wood for the trees
• Freedom of expression - Theres 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, ltmargin.csv or die open
  margin.csv !
• while (ltMARGINgt)
• chop
• (prod, margin, notes) split /,/
• prod_marginprod margin
• prod_notesprod notes

8
Defining the Output Format

• format STDOUT_TOP
• Code Price Notes
• ---- ------- --------
• .
• format STDOUT
• _at_gtgtgt . ltltltltltltltltltltltltltltltltltltltltltltltltltltltltltltltltlt
  
• prod, cost, note
• ltltltltltltltltltltltltltltltltltltltltltltltltltltltltltltltltlt
  
• note
• .

9
Writing the New Report

• open COST, "ltcost.txt" or die "open cost.txt
  !"
• while (line ltCOSTgt)
• next unless line m/\d/
• (prod, cost) unpack 'A4 _at_20 A7', line
• cost prod_marginprod 1.10
• note prod_notesprod '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 ltCOSTgt)
• . . .
• write using format with prod, cost and
  note
• price_cacheprod "cost note"

13
Update the Prices in a Database!

• use DBI
• db DBI-gtconnect('dbiODBCPRICE', 'user',
  'password',
• RaiseError gt 1 )
• upd db-gtprepare('UPDATE prices SET price?
  WHERE prod?')
• ins db-gtprepare('INSERT INTO
  prices(prod,price) VALUES (?,?)')
• while (line ltCOSTgt)
• . . .
• rows upd-gtexecute(price, prod)
• ins-gtexecute(prod,price) if rows 0
• db-gtcommit

14
Now get Cost Data from a Database!

• Original Code
• open COST, "ltcost.txt" or die "open cost.txt
  !"
• while (line ltCOSTgt)
• next unless line m/\d/
• (prod, cost) unpack 'A4 _at_20 A7', line
• . . .
• New Code
• costs cost_db-gtprepare('SELECT prod,cost FROM
  costs')
• costs-gtexecute
• while ( (prod, cost) costs-gtfetchrow_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
DBDOther
DBDInformix
DBDOracle
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
Native API
ODBC/ JDBC and drivers
DBI and Drivers
Win32ODBC
SybaseDBlib
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
• Its here, there and everywhere
• Its fast, flexible and well proven
• Its 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 doesnt
  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...