Integrating
Perl and Databases

Making simple things easy
and difficult things possible

Tim Bunce

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”

Freedom of expression - “There’s more than one way to do it”

The tool builders tool factory

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

Your Mission...

should you choose to accept it...

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

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;

}

Defining the Output Format

format STDOUT_TOP =

Code   Price    Notes

----   ------- --------

.

format STDOUT =

@>>>   ^###.## ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

$prod, $cost,   $note

~~              ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

                $note

.

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

}

Mission Accomplished!

Code   Price    Notes

----   ------- --------

1201    450.00 Special offer for limited

                period only

1202   1027.00 n/a

1203    493.90 n/a

Change
is the only Constant

Your new mission...

“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";

}

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

“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 ) {

    . . .

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”

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

Making simple things easy
and 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

Getting Technical...

for the techies...

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

A Picture is Worth...

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

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

Comparing DBI and Alternatives
(Attempting the Impossible!)

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

So What?

Success Stories in Industry

Large Scale Data Migration

by Michael Peppler

Database Interfaced Web Pages

by Frank San Filippo

The British Telecom
Call Management Information Service

by Tim Bunce

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

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

The end.

Till next year...


	Making simple things easy and difficult things possible

	Tim Bunce


	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


	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


	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


	open MARGIN, “<margin.csv” or die “open margin.csv: $!”;
	while (<MARGIN>) {
	chop;

	($prod, $margin, $notes) = split /,/;

	$prod_margin{$prod} = $margin;
	$prod_notes{$prod} = $notes;
	}


	format STDOUT_TOP =
	Code Price Notes
	---- ------- --------
	.

	format STDOUT =
	@>>> ^###.## ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
	$prod, $cost, $note
	~~ ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
	$note
	.


	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
	}


	Code Price Notes
	---- ------- --------
	1201 450.00 Special offer for limited
	period only
	1202 1027.00 n/a
	1203 493.90 n/a


	dbmopen(%price_cache, "price_cache", 0644) or die …;

	while ($line = <COST>) {
	. . .
	write; # using format with $prod, $cost and $note

	$price_cache{$prod} = "$cost $note";
	}


	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;


	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 ) {
	. . .


	The standard Database Interface for Perl
	“A perl module and specification that defines a consistent database interface independent of the actual database being used”


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


	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


	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


	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


	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


	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


	Large Scale Data Migration
			by Michael Peppler
	Database Interfaced Web Pages
			by Frank San Filippo
	The British Telecom Call Management Information Service
			by Tim Bunce


	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


	“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