Transcript Perl, Apache, DBI and DBD::Informix

Perl, Apache, DBI
and DBD::Informix
Jonathan Leffler
Open Source Architect
Classic Database Engineering
Agenda
•
•
•
•
•
•
Perl
DBI
DBD::Informix
Apache
mod_perl
Questions and Answers
DBD::Informix - Perl’s Route to Informix
Informix
user.conference
Perl
• Originally written by Larry Wall
• Very widely available
• Current stable versions:
• 5.6.0
• some packages (HTML::Mason 0.89) do not work with this
• 5.005_03 (with optional thread support)
• 5.004_04 (without thread support)
• Obtain via CPAN
• Comprehensive Perl Archive Network
• http://www.cpan.org/
Practical Extraction and Report Language
Informix
user.conference
Perl
• Script Language
• Does not require compilation
•
•
•
•
•
Complex looking code
Can be incredibly terse
Can be quite legible
Excellent at string handling
Excellent access to operating system
Do You Need Anything Else?
Informix
user.conference
Perl: A Two-Liner
#!/usr/bin/perl -wp
s/\$([A-Z][a-z]+|RCSfile): ([^\$]+) \$/$2/g;
• TMTOWTDI
• There’s more than one way to do it!
• (Yes, you could use sed for this)
perl -wp -e ’s%\$\w+: ([^\$]+) \$%$1%go;’ $*
Remove RCS Markers for RCS Keywords
Informix
user.conference
Another Example
#!/usr/bin/perl
# @(#)$Id: rename.pl,v 1.1 1992/01/05 22:33:47 jl Exp $
# Rename files using a Perl substitute command
($op = shift) || die "Usage: $0 perlexpr [filenames]\n";
if (!@ARGV) {
@ARGV = <STDIN>;
chop(@ARGV);
}
for (@ARGV){
$was = $_;
eval $op;
die $@ if $@;
rename($was, $_) unless $was eq $_;
}
File Renaming
Informix
user.conference
Perl Database Interface
• DBI written by Tim Bunce
• Standard way to access databases with Perl
• Many database drivers available
•
•
•
•
Including ODBC
And Oracle
And, of course, Informix
And many others
• Current version 1.14
Using Perl and a Database? Use DBI!
Informix
user.conference
The Cheetah Book
• The ‘bible’ for Perl DBI
• Authors:
• Alligator Descartes
• Tim Bunce
• O’Reilly, February 2000
• ISBN 1-56592-699-4
• http://www.oreilly.com/
Informix
user.conference
8
DBI: Database Handles
• Create database handles
• $dbh = DBI->connect(‘DBI:Informix:stores7’);
• Database methods
• $dbh->do(‘DELETE FROM Customer’);
• Transaction control
• $dbh->rollback;
• $dbh->commit;
• Disconnect
• $dbh->disconnect;
Database Handles are Crucial
Informix
user.conference
DBI: Statement Handles
• Create statement handles
• $sth = $dbh->prepare(qq{ DELETE FROM
Customer WHERE Lname LIKE ‘%$name%’ AND
ZipCode IS NULL });
• Statements can be executed
• $sth->execute();
• Statement handles can be released
• Implicitly
• When statement handle goes out of scope
• Explicitly
• undef $sth;
Statement Handles Are Important Too
Informix
user.conference
DBI: Handling SELECT
• Statement handles are used for SELECT too
• $sth = $dbh->prepare(q% SELECT * FROM
Customer WHERE Fname = ? AND Lname = ?
ORDER BY Lname, Fname%);
• $sth->execute($firstname, $surname);
• @results = $sth->fetchall_arrayref;
• …process results
• undef $sth;
SELECT is Fairly Simple
Informix
user.conference
DBI: Handling SELECT
• Many ways to fetch rows
• $sth->fetchrow_array
• $sth->fetchrow_hashref
• $sth->fetchrow_arrayref
• $sth->fetchall_arrayref
• And some utility functions
• $dbh->selectall_arrayref
• $dbh->selectrow_arrayref
TMTOWTDI!
Informix
user.conference
DBD::Informix: At Last
• Using DBD::Informix is using DBI
• All the examples work with DBD::Informix
• Current version is 1.00.PC1
• Building DBD::Informix is easy
•
•
•
•
Requires working ESQL/C 5.00 or later
ANSI C compiler (code uses prototypes)
Test database with DBA privileges
Perl version 5.004 or later
• 5.005_03 or 5.6.0 strongly recommended
• DBI version 1.02 or later
• Version 1.14 strongly recommended
Using DBD::Informix is Using DBI
Informix
user.conference
Installing DBD::Informix
• Download software from CPAN
• cd DBD-Informix-1.00.PC1
• more README
• perl Makefile.PL
• make
• make test
• make install
• Have fun!
• Same rules apply to all Perl modules
Building Perl Modules is Easy!
Informix
user.conference
DBD::Informix: Example
#! /usr/bin/perl -w
use DBI;
$dbh = DBI->connect(‘DBI:Informix:stores7’,’’,’’,
{RaiseError => 1, PrintError=>1});
$sth = $dbh->prepare(q%SELECT Fname, Lname, Phone
FROM Customer WHERE Customer_num = ? %);
$sth->execute(106);
$ref = $sth->fetchall_arrayref();
for $row (@$ref) {
print “Name: $$row[0] $$row[1], Phone: $$row[2]\n”;
}
$dbh->disconnect;
Error Checking Automated
Informix
user.conference
DBD::Informix and AutoCommit
• AutoCommit is on by default
•
•
•
•
To comply with DBI requirements
Cannot be unset on unlogged databases
Simulates MODE ANSI on logged databases
Explicit BEGIN WORK is possible
• $dbh->do(‘begin work’);
• $dbh->{AutoCommit} = 0;
• $dbh = DBI->connect(‘DBI:Informix:stores7’,
‘’, ‘’, { AutoCommit => 0 });
AutoCommit Controls Transactions
Informix
user.conference
Standard DBI Information
• Standard database attributes
• $dbh->{Driver}
• $dbh->{AutoCommit}
• $dbh->{PrintError}
• $dbh->{RaiseError}
• $dbh->{ChopBlanks}
Standard DBI Attributes
Informix
user.conference
Standard DBI Information
• Standard statement attributes
• $sth->{Statement}
• $sth->{CursorName}
• $sth->{NUM_OF_FIELDS}
• $sth->{NUM_OF_PARAMS}
• $sth->{NAME}
• $sth->{TYPE}
• $sth->{NULLABLE}
Standard DBI Attributes
Informix
user.conference
Standard DBI Information
• Standard handle attributes
• $h->err
• $h->errstr
• $h->state
• Standard handle methods
• $h->trace($trace_level)
• $h->trace_msg(“message”)
Standard Handle Attributes
Informix
user.conference
Informix-only Information
• SQLCA is available
• $sth->{ix_sqlcode}
• $sth->{ix_sqlerrd} - an array
• $sth->{ix_sqlerrm}
• $sth->{ix_sqlerrp}
• $sth->{ix_sqlwarn} - an array
Access to SQLCA
Informix
user.conference
Informix-only Information
• Non-standard attributes
• $dbh->{ix_InformixOnline}
• $dbh->{ix_LoggedDatabase}
• $dbh->{ix_ModeAnsiDatabase}
• $dbh->{ix_InTransaction}
• $dbh->{ix_ConnectionName}
• Standard attribute
• $dbh->{Name} — database name
Informix Database Attributes
Informix
user.conference
Informix-only Information
• Non-standard attributes
• $drh->{ix_ProductVersion}
• a version number for ESQL/C
• $drh->{ix_ProductName}
• $drh->{ix_MultipleConnections}
• $drh->{ix_ActiveConnections}
• $drh->{ix_CurrentConnection}
• $drh->{ix_ServerVersion}
• a version number for the database server
• All these attributes can also be found via the
database handle, $dbh
Informix Driver Attributes
Informix
user.conference
Informix-only Information
• Non-standard attributes
• $sth->{ix_NativeTypeName}
• $sth->{ix_ColType}
• $sth->{ix_ColLength}
Statement Attributes
Informix
user.conference
DBD::Informix
• Known Limitations
• Not yet fully aware of 9.x collection types or UDTs
• Doesn’t handle blobs in UPDATE statements
• Coded in DBD::Informix 1.10.PC1
• At least one memory leak
• No support for bind_param_inout methods
• Version 1.00.PC1 is an Informix product
• Officially “Informix Database Driver for Perl”
• The support channel is [email protected]
DBD::Informix is Not Perfect 
Informix
user.conference
DBD::Informix Documents
• Primary References
• Pre-install
• README file
• Informix.Licence file
• Post-install
• perldoc DBI
• perldoc DBD::Informix
• Books
• Programming Perl, 3rd Edition
• Programming the Perl DBI
• Perl Resource Kits (Unix, Win32) - O’Reilly
Please Read the README File!
Informix
user.conference
DBD::Informix: Web Sites
• Use CPAN to obtain the software
• www.cpan.org
• DBI web sites
• www.symbolstone.org/technology/perl/DBI
• www.isc.org/dbi-lists.html
• Sign up for [email protected] mailing list
• eskimo.tamu.edu/~jbaker/dbi-examples.html
Help Yourself - Join the Mailing List!
Informix
user.conference
Apache
• Apache Web Server
• Most widely used web server
• Version 1.3.14
• unless there’s another new version this week
• Modular structure
• Allows special purpose modules to be added
• mod_jserv - Java Server
• mod_perl - Perl Interpreter
Why Aren’t You Using Apache?
Informix
user.conference
Apache, Perl and CGI
•
•
•
•
CGI scripts drive the web
Many of them are Perl scripts
Most of those use the CGI module
http://www.somewhere.com/cgi-bin/script.pl
• Giveaway that there’s a Perl script in use
• Often not that easy to spot
• Can be slow on heavily loaded servers
• New Perl interpreter loaded for every hit
Perl Drives a Lot of Web Sites
Informix
user.conference
Apache and mod_perl
• Use mod_perl version 1.24_01
• Requires Perl 5.004 or later
• Can automatically download pre-requisites
• perl -MCPAN -e ‘install Bundle::Apache’
• Downloads, compiles, tests, install software
• Experimental DBI version available
• perl -MCPAN -e ‘install Bundle::DBI’
• perl -MCPAN -e ‘install Bundle::DBD::Informix’
If You Weren’t Convinced by Perl, Try This!
Informix
user.conference
Greased Lightning
• Apache with mod_perl
• Uses same CGI scripts
• Cleaned up for multiple use
• Loads scripts once and reuses them
• Without starting a separate process each hit
• Can even re-use database connections
• Apache::DBI module
• BUT
• httpd is much bigger
• Also consider FastCGI
Much Faster Web Service
Informix
user.conference
Apache Documentation
• http://perl.apache.org/
• Use perldoc for mod_perl info
•
•
•
•
•
mod_perl_traps
mod_perl_tuning
Apache::DBI
cgi_to_mod_perl
CGI
• HOWTO for Linux at IIUG web site
• http://www.iiug.org/
There’s a Lot of It About
Informix
user.conference
Apache and mod_java
• Apache-Jserv
• Currently version 1.1.2
• Similar to Apache & Perl
• Requires JDK 1.1.x and JSDK 2.0
• Obtain from Apache web site
• http://java.apache.org
• HOWTO in D4GL corner of IDN web site
Java-enabled Web Servers
Informix
user.conference
Your Turn!
Don’t forget to check out
http://www.informix.com/idn
http://www.iiug.org/
http://www.perl.com/
http://www.apache.org/
Thank You for Listening
Informix
user.conference
Questions and Answers
Informix
user.conference