Perl, Apache, DBI and DBD::Informix

1
Perl, Apache, DBI and DBDInformix

• Jonathan LefflerOpen Source Architect
• Classic Database Engineering

2
Agenda

• Perl
• DBI
• DBDInformix
• Apache
• mod_perl
• Questions and Answers

DBDInformix - Perls Route to Informix
3
Perl

• Originally written by Larry Wall
• Very widely available
• Current stable versions
• 5.6.0
• some packages (HTMLMason 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
4
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?
5
Perl A Two-Liner

• !/usr/bin/perl -wp
• s/\(A-Za-zRCSfile) (\) \/2/g
• TMTOWTDI
• Theres more than one way to do it!
• (Yes, you could use sed for this)
• perl -wp -e s\\w (\) \1go

Remove RCS Markers for RCS Keywords
6
Another Example
!/usr/bin/perl _at_()Id rename.pl,v 1.1
1992/01/05 223347 jl Exp Rename files using
a Perl substitute command (op shift) die
"Usage 0 perlexpr filenames\n" if (!_at_ARGV)
_at_ARGV ltSTDINgt chop(_at_ARGV) for
(_at_ARGV) was _ eval op die _at_ if
_at_ rename(was, _) unless was eq _
File Renaming
7
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!
8
The Cheetah Book

• The bible for Perl DBI
• Authors
• Alligator Descartes
• Tim Bunce
• OReilly, February 2000
• ISBN 1-56592-699-4
• http//www.oreilly.com/

9
DBI Database Handles

• Create database handles
• dbh DBI-gtconnect(DBIInformixstores7)
• Database methods
• dbh-gtdo(DELETE FROM Customer)
• Transaction control
• dbh-gtrollback
• dbh-gtcommit
• Disconnect
• dbh-gtdisconnect

Database Handles are Crucial
10
DBI Statement Handles

• Create statement handles
• sth dbh-gtprepare(qq DELETE FROM Customer
  WHERE Lname LIKE name AND ZipCode IS NULL
  )
• Statements can be executed
• sth-gtexecute()
• Statement handles can be released
• Implicitly
• When statement handle goes out of scope
• Explicitly
• undef sth

Statement Handles Are Important Too
11
DBI Handling SELECT

• Statement handles are used for SELECT too
• sth dbh-gtprepare(q SELECT FROM Customer
  WHERE Fname ? AND Lname ? ORDER BY Lname,
  Fname)
• sth-gtexecute(firstname, surname)
• _at_results sth-gtfetchall_arrayref
• process results
• undef sth

SELECT is Fairly Simple
12
DBI Handling SELECT

• Many ways to fetch rows
• sth-gtfetchrow_array
• sth-gtfetchrow_hashref
• sth-gtfetchrow_arrayref
• sth-gtfetchall_arrayref
• And some utility functions
• dbh-gtselectall_arrayref
• dbh-gtselectrow_arrayref

TMTOWTDI!
13
DBDInformix At Last

• Using DBDInformix is using DBI
• All the examples work with DBDInformix
• Current version is 1.00.PC1
• Building DBDInformix 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 DBDInformix is Using DBI
14
Installing DBDInformix

• 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!
15
DBDInformix Example
! /usr/bin/perl -w use DBI dbh
DBI-gtconnect(DBIInformixstores7,,,
RaiseError gt 1, PrintErrorgt1) sth
dbh-gtprepare(qSELECT Fname, Lname, Phone
FROM Customer WHERE Customer_num ?
) sth-gtexecute(106) ref sth-gtfetchall_arra
yref() for row (_at_ref) print Name
row0 row1, Phone row2\n dbh-gtdis
connect
Error Checking Automated
16
DBDInformix 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-gtdo(begin work)
• dbh-gtAutoCommit 0
• dbh DBI-gtconnect(DBIInformixstores7,
  , , AutoCommit gt 0 )

AutoCommit Controls Transactions
17
Standard DBI Information

• Standard database attributes
• dbh-gtDriver
• dbh-gtAutoCommit
• dbh-gtPrintError
• dbh-gtRaiseError
• dbh-gtChopBlanks

Standard DBI Attributes
18
Standard DBI Information

• Standard statement attributes
• sth-gtStatement
• sth-gtCursorName
• sth-gtNUM_OF_FIELDS
• sth-gtNUM_OF_PARAMS
• sth-gtNAME
• sth-gtTYPE
• sth-gtNULLABLE

Standard DBI Attributes
19
Standard DBI Information

• Standard handle attributes
• h-gterr
• h-gterrstr
• h-gtstate
• Standard handle methods
• h-gttrace(trace_level)
• h-gttrace_msg(message)

Standard Handle Attributes
20
Informix-only Information

• SQLCA is available
• sth-gtix_sqlcode
• sth-gtix_sqlerrd - an array
• sth-gtix_sqlerrm
• sth-gtix_sqlerrp
• sth-gtix_sqlwarn - an array

Access to SQLCA
21
Informix-only Information

• Non-standard attributes
• dbh-gtix_InformixOnline
• dbh-gtix_LoggedDatabase
• dbh-gtix_ModeAnsiDatabase
• dbh-gtix_InTransaction
• dbh-gtix_ConnectionName
• Standard attribute
• dbh-gtName database name

Informix Database Attributes
22
Informix-only Information

• Non-standard attributes
• drh-gtix_ProductVersion
• a version number for ESQL/C
• drh-gtix_ProductName
• drh-gtix_MultipleConnections
• drh-gtix_ActiveConnections
• drh-gtix_CurrentConnection
• drh-gtix_ServerVersion
• a version number for the database server
• All these attributes can also be found via the
  database handle, dbh

Informix Driver Attributes
23
Informix-only Information

• Non-standard attributes
• sth-gtix_NativeTypeName
• sth-gtix_ColType
• sth-gtix_ColLength

Statement Attributes
24
DBDInformix

• Known Limitations
• Not yet fully aware of 9.x collection types or
  UDTs
• Doesnt handle blobs in UPDATE statements
• Coded in DBDInformix 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 dbd-informix_at_informix.com

DBDInformix is Not Perfect ?
25
DBDInformix Documents

• Primary References
• Pre-install
• README file
• Informix.Licence file
• Post-install
• perldoc DBI
• perldoc DBDInformix
• Books
• Programming Perl, 3rd Edition
• Programming the Perl DBI
• Perl Resource Kits (Unix, Win32) - OReilly

Please Read the README File!
26
DBDInformix 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 dbi-users_at_isc.org mailing list
• eskimo.tamu.edu/jbaker/dbi-examples.html

Help Yourself - Join the Mailing List!
27
Apache

• Apache Web Server
• Most widely used web server
• Version 1.3.14
• unless theres another new version this week
• Modular structure
• Allows special purpose modules to be added
• mod_jserv - Java Server
• mod_perl - Perl Interpreter

Why Arent You Using Apache?
28
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 theres 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
29
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 BundleApache
• Downloads, compiles, tests, install software
• Experimental DBI version available
• perl -MCPAN -e install BundleDBI
• perl -MCPAN -e install BundleDBDInformix

If You Werent Convinced by Perl, Try This!
30
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
• ApacheDBI module
• BUT
• httpd is much bigger
• Also consider FastCGI

Much Faster Web Service
31
Apache Documentation

• http//perl.apache.org/
• Use perldoc for mod_perl info
• mod_perl_traps
• mod_perl_tuning
• ApacheDBI
• cgi_to_mod_perl
• CGI
• HOWTO for Linux at IIUG web site
• http//www.iiug.org/

Theres a Lot of It About
32
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
33

• Your Turn!
• Dont 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

34
Questions and Answers