Getting Serious
with the Perl DBI

Making simple things easy and difficult things possible

Tim Bunce

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 (static and dynamic)

Perl lets you build sharper tools faster

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”

What drives the design of the DBI?

Goals of the DBI

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 with open source code

A ‘higher-level’ interface than ODBC/JDBC

Where does the DBI fit?

A Simple Example

use DBI;

$dbh = DBI->connect('dbi:ODBC:PRICE', 'user', 'password',

{ RaiseError => 1, AutoCommit => 0 });

$upd = $dbh->prepare('UPDATE prices SET price=? WHERE prod=?');

$ins = $dbh->prepare('INSERT INTO prices(prod,price) VALUES (?,?)');

while ($line = <COST>) {

    chop $line;

    ($prod, $price) = split /,/, $line;

    $rows = $upd->execute($price, $prod);

    $ins->execute($prod,$price) if $rows == 0;

}

$db->commit;

So why use the Perl DBI?

Because...

It delivers what it promises

It’s simple and quick to use

It’s fast, flexible and well proven

It’s here, there and everywhere

It’s free, with source

Drivers available for many databases

Commercial support is available

It has a large user base and a strong future

Comparing DBI and Alternatives
(Attempting the Impossible!)

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

Architecture of the DBI Classes #1

Architecture of the DBI Classes #2

Anatomy of a DBI Handle

Method call walk-through

Consider a simple prepare call:
$dbh->prepare(...)

$dbh is reference to a DBI::db object (regardless of driver)

DBI::db::prepare is an alias for DBI dispatch method

Dispatch calls driver’s prepare method something like this:

   my $inner_hash_ref = …     # from tie magic

   my $implementor_class = … # from DBI magic data

   $inner_hash_ref->$implementor_class::prepare(…)

Since driver code gets inner hash it has fast access to the hash contents

Give me SPEED!

DBI designed for speed from day one.

DBI dispatch written in hand-crafted XS/C.

Dispatch to XS driver methods optimized.

Cached attributes returned by dispatch.

Placeholders give maximum server performance.

Binding columns and/or fetchrow_arrayref give maximum client-side fetch performance.

DBI overhead is generally insignificant.

What's the difference?

while(@row = $sth->fetchrow_array) { }

one field: 3100 fetches per cpu second

ten fields: 1000 fetches per cpu second

while($row = $sth->fetchrow_arrayref) { }

one field: 5300 fetches per cpu second

ten fields: 4000 fetches per cpu second

Notes:

Timings made on an old Sparc 10 using DBD::Oracle.

Timings assume instant record fetch within driver.

Fields all one char. @row would be even slower for more/bigger fields.

Give me SAFETY!

A recipe for safety

Use transactions (don't use AutoCommit)

Use RaiseError

Use eval { … } to catch errors and rollback

Safety by example

use DBI;

use Text::Wrap;

$dbh = DBI->connect(…, …, …, {

    AutoCommit => 0, RaiseError => 1

});

eval {

    $sth = $dbh->prepare('insert into table values (?)');

    foreach (@array_of_long_strings) {

        $text = wrap($_);

        $sth->execute($test);

    }

};

$@ ? $dbh->rollback : $dbh->commit;

Give me (a vague impression of ) SAFETY!

Safety on the cheap (without transactions)

push change information onto an array

undo in reverse order, in place of rollback

consider signals etc.

Wheels within wheels
Watching the DBI in action

DBI has detailed call tracing built-in

The trace be very helpful in understanding application behavior and for debugging

Shows parameters and results

Trace information can be written to a file

Not used often enough!

Our program for today...

1: #!/usr/bin/perl -w

2:

3: use DBI;

4:

5: $dbh = DBI->connect('user', 'passwd', { RaiseError => 1 });

6:

7: $upd = $dbh->prepare("UPDATE prices SET price=? WHERE prod=?");

8: $ins = $dbh->prepare("INSERT INTO prices (prod,price) VALUES(?,?)");

9: $rows = $upd->execute(42, "Widgets");

10: $ins->execute("Widgets", 42) if $rows == 0;

11:

12: $dbh->disconnect;

Trace Level 1

Trace Level 1 shows results only:

    <- connect= DBI::db=HASH(0xe0abc) at DBI.pm line 356.

    <- STORE('PrintError', 1)= 1 at DBI.pm line 382.

    <- STORE('AutoCommit', 1)= 1 at DBI.pm line 382.

    <- STORE('RaiseError', 1)= 1 at DBI.pm line 382.

    <- prepare= DBI::st=HASH(0xe1238) at test.pl line 7.

    <- prepare= DBI::st=HASH(0xe1504) at test.pl line 8.

    <- execute= '0E0' at test.pl line 9.

    <- execute= 1 at test.pl line 10.

    <- disconnect= 1 at test.pl line 11.

    <- DESTROY= undef

    <- DESTROY= undef

    <- DESTROY= undef

Trace Level 2

Trace Level 2 shows calls with parameters and more:

    -> connect for DBD::ODBC::dr (DBI::dr=HASH(0x13dfec)~0xe14a4

                                  'demo' 'user' 'passwd' HASH(0xe0a10))

    <- connect= DBI::db=HASH(0xe0ab0) at DBI.pm line 356.

    -> STORE for DBD::ODBC::db (DBI::db=HASH(0xe0abc)~INNER 'PrintError' 1)

    <- STORE= 1 at DBI.pm line 382.

    -> prepare for DBD::ODBC::db (DBI::db=HASH(0xe0ab0)~0xe0abc

                                  'UPDATE prices SET price=? WHERE prod=?')

    dbd_preparse scanned 2 distinct placeholders

    <- prepare= DBI::st=HASH(0xe1274) at test.pl line 7.

Trace Level 2 (continued)

    -> execute for DBD::ODBC::st (DBI::st=HASH(0xe1274)~0xe110c 42 'Widgets')

    bind 1 <== 42 (size 2/3/0, ptype 5, otype 1)

    bind 2 <== 'Widgets' (size 7/8/0, ptype 4, otype 1)

    dbd_st_execute (for sql f1499360 after)...

    dbd_describe sql 1499360: num_fields=0

    dbd_describe skipped (no result cols) (sql f1499360)

    <- execute= '0E0' at test.pl line 9.

    -> execute for DBD::ODBC::st (DBI::st=HASH(0xe10d0)~0x142e6c 'Widgets' 42)

    bind 1 <== 'Widgets' (size 7/8/0, ptype 4, otype 1)

    bind 2 <== 42 (size 2/3/0, ptype 5, otype 1)

    dbd_st_execute (for sql f1511752 after)...

    dbd_describe sql 1511752: num_fields=0

    dbd_describe skipped (no result cols) (sql f1511752)

    <- execute= 1 at test.pl line 10.

Trace Level 2 (concluded)

    -> disconnect for DBD::ODBC::db (DBI::db=HASH(0xe0ab0)~0xe0abc)

    <- disconnect= 1 at test.pl line 11.

       (outer handle DESTROY ignored)

       (outer handle DESTROY ignored)

    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0xe110c)~INNER)

    <- DESTROY= undef

       (outer handle DESTROY ignored)

    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x142e6c)~INNER)

    <- DESTROY= undef

    -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0xe0abc)~INNER)

    <- DESTROY= undef

State of the Nation

DBI builds and runs on many systems

Unix, Windows, VMS etc.

DBI has many available drivers

ODBC, Oracle, Informix, Ingres, mSQL, mysql, DB2, Adabas, Illustra, Solid, Sybase, Xbase, Postgress, Quickbase, Empress, Fulcrum, Altera, ...

Merging the ODBC/JDBC ‘inheritance’

data types, meta-data etc.

Specifying the unspecified

Documenting the undocumented… at last!

What's New in the DBI?

Methods

$sth->prepare_cached($statement)

$dbh->table_info and $dbh->tables

$dbh->type_info

Attributes

$sth->{TYPE} (Standards based type values)

$sth->{SCALE}

$sth->{PRECISION}

And finally… Version 1.00!

The Interactive DBI::Shell

Starting the shell

The long way:

     perl -MDBI::shell -e shell dsn user passwd

     perl -MDBI::shell -e shell

The short way:

    dbish ...

Interactive DSN selection

Command and Result History (uses ReadLine)

Output Redirection for all commands

DBD::Proxy & DBI::ProxyServer

Networking for Non-networked databases

DBD::Proxy driver forwards calls over network to remote DBI::ProxyServer

Proxy can be made transparent by setting DBI_AUTOPROXY environment variable

DBI::ProxyServer doesn't work on Win32 yet (work in progress)

DBD::Proxy & DBI::ProxyServer

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

So What's Next Now?

Extend the Shell (batch mode, plug-ins etc)

Driver catch-up to DBI version 1.00

Common test suite for all drivers

Multi-threading (at least thread safety)

Binding In/Out Variables (for DBD::ODBC)

Binding Arrays (for DBD::Oracle)

Non-blocking (method calls return at once)

fetch_scroll (next, previous, first, last etc)

SQL Parser (ODBC) for greater script portability

What's new in DBD::Oracle?

Some, but not all, of DBI 1.00 is supported.

Should catch up in the next release or so.

Now bundles Alan Burlison's explain tool.

Slide 33

The end.

Till next year...


	Making simple things easy and difficult things possible

	Tim Bunce


	Ideally suited to data processing:
		parsing, cleansing / filtering / merging and formatting
	Applications
		Data Migration
		Data acquisition and reporting
		Web Page creation (static and dynamic)
	Perl lets you build sharper tools faster


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


	Goals of the DBI
		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 with open source code
	A ‘higher-level’ interface than ODBC/JDBC


	use DBI;

	$dbh = DBI->connect('dbi:ODBC:PRICE', 'user', 'password',
	{ RaiseError => 1, AutoCommit => 0 });
	$upd = $dbh->prepare('UPDATE prices SET price=? WHERE prod=?');
	$ins = $dbh->prepare('INSERT INTO prices(prod,price) VALUES (?,?)');

	while ($line = <COST>) {
	chop $line;
	($prod, $price) = split /,/, $line;
	$rows = $upd->execute($price, $prod);
	$ins->execute($prod,$price) if $rows == 0;
	}
	$db->commit;


	Because...
		It delivers what it promises
		It’s simple and quick to use
		It’s fast, flexible and well proven
		It’s here, there and everywhere
		It’s free, with source
		Drivers available for many databases
		Commercial support is available
		It has a large user base and a strong future


	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


	Consider a simple prepare call: $dbh->prepare(...)
	$dbh is reference to a DBI::db object (regardless of driver)
	DBI::db::prepare is an alias for DBI dispatch method
	Dispatch calls driver’s prepare method something like this:
	my $inner_hash_ref = … # from tie magic
	my $implementor_class = … # from DBI magic data
	$inner_hash_ref->$implementor_class::prepare(…)
	Since driver code gets inner hash it has fast access to the hash contents


	DBI designed for speed from day one.
	DBI dispatch written in hand-crafted XS/C.
	Dispatch to XS driver methods optimized.
	Cached attributes returned by dispatch.
	Placeholders give maximum server performance.
	Binding columns and/or fetchrow_arrayref give maximum client-side fetch performance.
	DBI overhead is generally insignificant.


	while(@row = $sth->fetchrow_array) { }
		one field: 3100 fetches per cpu second
		ten fields: 1000 fetches per cpu second

	while($row = $sth->fetchrow_arrayref) { }
		one field: 5300 fetches per cpu second
		ten fields: 4000 fetches per cpu second

	Notes:
		Timings made on an old Sparc 10 using DBD::Oracle.
		Timings assume instant record fetch within driver.
		Fields all one char. @row would be even slower for more/bigger fields.


	A recipe for safety
		Use transactions (don't use AutoCommit)
		Use RaiseError
		Use eval { … } to catch errors and rollback


	use DBI;
	use Text::Wrap;
	$dbh = DBI->connect(…, …, …, {
	AutoCommit => 0, RaiseError => 1
	});
	eval {
	$sth = $dbh->prepare('insert into table values (?)');
	foreach (@array_of_long_strings) {
	$text = wrap($_);
	$sth->execute($test);
	}
	};
	$@ ? $dbh->rollback : $dbh->commit;


	Safety on the cheap (without transactions)
		push change information onto an array
		undo in reverse order, in place of rollback
		consider signals etc.


	DBI has detailed call tracing built-in
	The trace be very helpful in understanding application behavior and for debugging
	Shows parameters and results
	Trace information can be written to a file
	Not used often enough!


	1: #!/usr/bin/perl -w
	2:
	3: use DBI;
	4:
	5: $dbh = DBI->connect('user', 'passwd', { RaiseError => 1 });
	6:
	7: $upd = $dbh->prepare("UPDATE prices SET price=? WHERE prod=?");
	8: $ins = $dbh->prepare("INSERT INTO prices (prod,price) VALUES(?,?)");
	9: $rows = $upd->execute(42, "Widgets");
	10: $ins->execute("Widgets", 42) if $rows == 0;
	11:
	12: $dbh->disconnect;


	Trace Level 1 shows results only:

	<- connect= DBI::db=HASH(0xe0abc) at DBI.pm line 356.
	<- STORE('PrintError', 1)= 1 at DBI.pm line 382.
	<- STORE('AutoCommit', 1)= 1 at DBI.pm line 382.
	<- STORE('RaiseError', 1)= 1 at DBI.pm line 382.

	<- prepare= DBI::st=HASH(0xe1238) at test.pl line 7.
	<- prepare= DBI::st=HASH(0xe1504) at test.pl line 8.
	<- execute= '0E0' at test.pl line 9.
	<- execute= 1 at test.pl line 10.
	<- disconnect= 1 at test.pl line 11.

	<- DESTROY= undef
	<- DESTROY= undef
	<- DESTROY= undef


	Trace Level 2 shows calls with parameters and more:

	-> connect for DBD::ODBC::dr (DBI::dr=HASH(0x13dfec)~0xe14a4
	'demo' 'user' 'passwd' HASH(0xe0a10))
	<- connect= DBI::db=HASH(0xe0ab0) at DBI.pm line 356.

	-> STORE for DBD::ODBC::db (DBI::db=HASH(0xe0abc)~INNER 'PrintError' 1)
	<- STORE= 1 at DBI.pm line 382.

	-> prepare for DBD::ODBC::db (DBI::db=HASH(0xe0ab0)~0xe0abc
	'UPDATE prices SET price=? WHERE prod=?')
	dbd_preparse scanned 2 distinct placeholders
	<- prepare= DBI::st=HASH(0xe1274) at test.pl line 7.


	-> execute for DBD::ODBC::st (DBI::st=HASH(0xe1274)~0xe110c 42 'Widgets')
	bind 1 <== 42 (size 2/3/0, ptype 5, otype 1)
	bind 2 <== 'Widgets' (size 7/8/0, ptype 4, otype 1)
	dbd_st_execute (for sql f1499360 after)...
	dbd_describe sql 1499360: num_fields=0
	dbd_describe skipped (no result cols) (sql f1499360)
	<- execute= '0E0' at test.pl line 9.

	-> execute for DBD::ODBC::st (DBI::st=HASH(0xe10d0)~0x142e6c 'Widgets' 42)
	bind 1 <== 'Widgets' (size 7/8/0, ptype 4, otype 1)
	bind 2 <== 42 (size 2/3/0, ptype 5, otype 1)
	dbd_st_execute (for sql f1511752 after)...
	dbd_describe sql 1511752: num_fields=0
	dbd_describe skipped (no result cols) (sql f1511752)
	<- execute= 1 at test.pl line 10.



	-> disconnect for DBD::ODBC::db (DBI::db=HASH(0xe0ab0)~0xe0abc)
	<- disconnect= 1 at test.pl line 11.

	(outer handle DESTROY ignored)
	(outer handle DESTROY ignored)
	-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0xe110c)~INNER)
	<- DESTROY= undef
	(outer handle DESTROY ignored)
	-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x142e6c)~INNER)
	<- DESTROY= undef
	-> DESTROY for DBD::ODBC::db (DBI::db=HASH(0xe0abc)~INNER)
	<- DESTROY= undef


	DBI builds and runs on many systems
		Unix, Windows, VMS etc.
	DBI has many available drivers
		ODBC, Oracle, Informix, Ingres, mSQL, mysql, DB2, Adabas, Illustra, Solid, Sybase, Xbase, Postgress, Quickbase, Empress, Fulcrum, Altera, ...
	Merging the ODBC/JDBC ‘inheritance’
		data types, meta-data etc.
	Specifying the unspecified
		Documenting the undocumented… at last!


	Methods
		$sth->prepare_cached($statement)
		$dbh->table_info and $dbh->tables
		$dbh->type_info
	Attributes
		$sth->{TYPE} (Standards based type values)
		$sth->{SCALE}
		$sth->{PRECISION}
	And finally… Version 1.00!


	Starting the shell
		The long way:
	perl -MDBI::shell -e shell dsn user passwd
	perl -MDBI::shell -e shell
		The short way:
		dbish ...
	Interactive DSN selection
	Command and Result History (uses ReadLine)
	Output Redirection for all commands


	Networking for Non-networked databases
	DBD::Proxy driver forwards calls over network to remote DBI::ProxyServer
	Proxy can be made transparent by setting DBI_AUTOPROXY environment variable
	DBI::ProxyServer doesn't work on Win32 yet (work in progress)


	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


	Extend the Shell (batch mode, plug-ins etc)
	Driver catch-up to DBI version 1.00
	Common test suite for all drivers
	Multi-threading (at least thread safety)
	Binding In/Out Variables (for DBD::ODBC)
	Binding Arrays (for DBD::Oracle)
	Non-blocking (method calls return at once)
	fetch_scroll (next, previous, first, last etc)
	SQL Parser (ODBC) for greater script portability


	Some, but not all, of DBI 1.00 is supported.
	Should catch up in the next release or so.
	Now bundles Alan Burlison's explain tool.