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 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](https://present5.com/presentation/b643b037ef53202ab70a97c6b73f55e5/image-5.jpg "Perl: A Two-Liner #!/usr/bin/perl -wp s/$([A-Z][a-z]+|RCSfile): ([^$]+) $/$2/g; • TMTOWTDI • There’s more than")
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: stores 7’); • 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 Zip. Code 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. PC 1 • 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. PC 1 • more README • perl Makefile. PL • 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: stores 7’, ’’, {Raise. Error => 1, Print. Error=>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 Auto. Commit • Auto. Commit 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->{Auto. Commit} = 0; • $dbh = DBI->connect(‘DBI: Informix: stores 7’, ‘’, { Auto. Commit => 0 }); Auto. Commit Controls Transactions Informix user. conference
Standard DBI Information • Standard database attributes • $dbh->{Driver} • $dbh->{Auto. Commit} • $dbh->{Print. Error} • $dbh->{Raise. Error} • $dbh->{Chop. Blanks} Standard DBI Attributes Informix user. conference
Standard DBI Information • Standard statement attributes • $sth->{Statement} • $sth->{Cursor. Name} • $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->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_Informix. Online} • $dbh->{ix_Logged. Database} • $dbh->{ix_Mode. Ansi. Database} • $dbh->{ix_In. Transaction} • $dbh->{ix_Connection. Name} • Standard attribute • $dbh->{Name} — database name Informix Database Attributes Informix user. conference
Informix-only Information • Non-standard attributes • $drh->{ix_Product. Version} • a version number for ESQL/C • $drh->{ix_Product. Name} • $drh->{ix_Multiple. Connections} • $drh->{ix_Active. Connections} • $drh->{ix_Current. Connection} • $drh->{ix_Server. Version} • 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_Native. Type. Name} • $sth->{ix_Col. Type} • $sth->{ix_Col. Length} 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. PC 1 • At least one memory leak • No support for bind_param_inout methods • Version 1. 00. PC 1 is an Informix product • Officially “Informix Database Driver for Perl” • The support channel is dbd-informix@informix. com 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, 3 rd Edition • Programming the Perl DBI • Perl Resource Kits (Unix, Win 32) - 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 dbi-users@isc. org 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 Fast. CGI 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 D 4 GL 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
Скачать презентацию Perl Apache DBI and DBD Informix Jonathan
b643b037ef53202ab70a97c6b73f55e5.ppt