Perl, Apache, DBI and DBD::Informix

1
Perl, Apache, DBI and DBDInformix

• Jonathan LefflerArchitect, Foundation Engineering

2
Agenda

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

3
Perl - Practical Extraction and Report Language

• Originally written by Larry Wall
• Version 1.0 in 1987
• Version 4.0 in 1991 under GPL and Artistic
  License
• Version 5.0 in 1994
• Very widely available
• Current stable versions
• 5.6.1
• 5.005_03 (with optional thread support)
• 5.004_04 (without thread support)
• Obtain via CPAN
• Comprehensive Perl Archive Network
• http//www.cpan.org/

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

5
Remove RCS Keyword Markers

• !/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

6
Remove HTML Markup
/usr/bin/perl -wp s/ltparamgtltgtlt\/paramgt//go s
/ltgtgt//go s/\quot/"/go s/\lt/lt/go s/\g
t/gt/go s/\amp//go s/\nbsp/ /go
7
File Renaming
!/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 _
8
Perl Database Interface

• DBI written by Tim Bunce
• Standard way to access databases with Perl
• Many database drivers available
• Including ODBC
• And DB2
• And Oracle
• And, of course, Informix
• And many others
• Current version 1.15
• Using Perl and a database? Use DBI!

9
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/

10
DBI - Database Handles

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

11
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
• Statement handle goes out of scope
• Explicitly
• undef sth

12
DBI - Handling SELECT

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

13
DBI - Handling SELECT

• Many ways to fetch rows
• sth-gtfetchrow_array
• sth-gtfetchrow_hashref
• sth-gtfetchrow_arrayref
• sth-gtfetchall_arrayref
• Also utility methods
• dbh-gtselectrow_array
• dbh-gtselectall_arrayref

14
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 (eg
  ClientSDK)
• ANSI C compiler (code uses prototypes)
• Test database with DBA privileges
• Perl version 5.004 or later
• 5.005_03 or 5.6.1 strongly recommended
• DBI version 1.02 or later
• Version 1.14 or later strongly recommended

15
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!

16
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
17
DBDInformix 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 )

18
Standard DBI Information

• Standard database attributes
• dbh-gtDriver
• dbh-gtAutoCommit
• dbh-gtPrintError
• dbh-gtRaiseError
• dbh-gtChopBlanks
• There are others, too.

19
Standard DBI Information

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

20
Standard DBI Information

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

21
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

22
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

23
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

24
Informix-only Information

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

25
DBDInformix

• Known Limitations
• Not yet fully aware of 9.x collection types or
  UDTs
• Doesnt handle blobs in UPDATE statements
• Coded in DBDInformix 1.01.PC1
• sth-gtbind_param(3, blobval, ix_typegtIX_TEXT)
  
• No support for bind_param_inout method
• Version 1.10.PC1 is an official Informix product
• Officially Informix Database Driver for Perl
• The support channel is the mailing list
• dbd-informix_at_informix.com

26
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

27
DBDInformix - Web Sites

• Use CPAN to obtain the software
• http//www.cpan.org
• DBI web sites
• http//www.symbolstone.org/technology/perl/DBI
• http//www.perl.org/dbi-lists.html
• Sign up for dbi-users_at_iperl.org mailing list
• http//eskimo.tamu.edu/jbaker/dbi-examples.html
• Help yourself - join the mailing list!

28
Apache

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

29
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

30
Apache and mod_perl

• Use mod_perl version 1.24_01 or later
• Requires Perl 5.004 or later
• Can automatically download pre-requisites
• perl -MCPAN -e install BundleApache
• Downloads, compiles, tests, installs software
• Simlarly for DBI and DBDInformix
• perl -MCPAN -e install BundleDBDInformix
• You need to see this in action to believe it!
• Also consider FastCGI technology
• http//www.fastcgi.com/

31
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

Much faster web service
32
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
33
Apache and mod_java

• Apache-Jserv
• Version 1.1.2 or later
• 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

34
Questions and Answers

• Your Turn!
• Dont forget to check out
• http//www.iiug.org/
• http//www.perl.com/
• http//www.apache.org/
• Thank You for Listening

35
Questions and Answers