Transcript Using Perl and DBI/DBD With Informix Databases

Using Perl and DBI/DBD With
Informix Databases
Darryl Priest
Piper Rudnick LLP
[email protected]
Agenda
•
•
•
•
•
•
•
•
•
•
What is DBI & DBD::Informix?
Why Perl?
Why DBI/DBD::Informix?
Perl Basics
Database Connections
Static SQLs
Fetching Data
Other SQLs
Putting It All Together
Supported, But Not Covered
Using Perl & DBI/DBD::Informix
2
Why Perl?
•
•
•
•
•
•
•
•
•
•
•
Easy To Start
Many Modules Available
Autovivification
Garbage Collection
Text Manipulation & Regular Expressions
Portability
Easy Access And Interaction With System Commands
Hashes
CGI
Speed
Code Reusability Using Modules
Using Perl & DBI/DBD::Informix
3
Why DBI/DBD::Informix?
•
•
•
•
•
Very well tested
Data Fetch Method Choices
IBM/Informix Support
Portability
Database Connections
Using Perl & DBI/DBD::Informix
4
Perl Basics
•
•
•
•
•
•
•
#!/usr/bin/perl -w
Variable Types (scalars, arrays, hashes, references)
use DBI;
use strict;
Variable Scope
TMTOWTDI
q# and qq#
Using Perl & DBI/DBD::Informix
5
DBI Generalizations
• Database connections are referred to as database handles
usually named $dbh, etc.
• Select SQLs usually follow the pattern
prepare,
execute, fetch, fetch, fetch …
execute, fetch, fetch, fetch …
• Non-select SQLs usually follow the pattern
prepare,
execute,
execute,
Using Perl & DBI/DBD::Informix
6
Database Connections
$dbh = DBI->connect($data_source, $username, $auth, \%attr);
$dbh = DBI->connect(“DBI:Informix:$database");
$dbh = DBI->connect(“DBI:Informix:$database", '', '',
{ AutoCommit => 0, PrintError => 1 });
my $pm_dbh = DBI->connect("DBI:Informix:pmdata")
or die "PMData Database Open Error: $DBI::errstr\n";
$pm_dbh->{ChopBlanks} = 1;
$pm_dbh->{AutoCommit} = 1;
$pm_dbh->{PrintError} = 1;
$pm_dbh->{RaiseError} = 1;
my $ps_dbh = DBI->connect("DBI:Informix:hrdb\@remote_tcp")
or die "PeopleSoft Database Open Error: $DBI::errstr\n";
$dbh->disconnect();
Using Perl & DBI/DBD::Informix
7
Code Notes
• use strict pragma is most likely in use although variables may
not be defined in the code shown.
• Most SQLs are from either an Elite or PeopleSoft database.
• Where error checking is not shown it is being handled
automatically by DBI and DBD::Informix. (RaiseError and
PrintError).
Using Perl & DBI/DBD::Informix
8
Static SQLs
$el_dbh->do("set isolation to dirty read;");
$sql = qq#create temp table temp_teamleader
(tkinit
char(5),
teamleader
char(5)
) with no log in tempdbs;#;
$el_dbh->do($sql);
$sql = qq#insert into temp_teamleader(tkinit, teamleader)
select udjoin, udvalue
from udf
where udf.udtype = "TK" and udf.udfindex = 55;#;
my $ins_teamleader_sth = $el_dbh->prepare($sql);
$ins_teamleader_sth->execute();
$el_dbh->do("create index teamldr_idx1 on temp_teamleader(tkinit);");
$el_dbh->do("update statistics high for table temp_teamleader;");
Using Perl & DBI/DBD::Informix
9
Fetching Data
(Static Declarations)
$sql = qq#select rttype, rtdesc
from crltype
order by 1;#;
my $get_party_type_sth = $el_dbh->prepare($sql)
or die "Preparing get_party_type_sth, Error: $DBI::errstr\n";
Using Perl & DBI/DBD::Informix
10
Fetching Data
(Declarations With Placeholders)
$sql = qq#select emplid, primary_contact, contact_name, relationship, phone
from ps_emergency_cntct
where emplid = ?
order by primary_contact desc, contact_name;#;
my $get_emerg_contact_sth = $ps_dbh->prepare_cached($sql)
or die "Preparing get_emerg_contact_sth, Error: $DBI::errstr\n";
Using Perl & DBI/DBD::Informix
11
Fetching Data
(Static Execution)
$sql = qq#select rttype, rtdesc
from crltype
order by 1;#;
my $get_party_type_sth = $el_dbh->prepare($sql)
or die "Preparing get_party_type_sth, Error: $DBI::errstr\n";
$get_party_type_sth->execute();
Using Perl & DBI/DBD::Informix
12
Fetching Data
(Execution With Placeholders)
$sql = qq#select emplid, primary_contact, contact_name, relationship, phone
from ps_emergency_cntct
where emplid = ?
order by primary_contact desc, contact_name;#;
my $get_emerg_contact_sth = $ps_dbh->prepare_cached($sql)
or die "Preparing get_emerg_contact_sth, Error: $DBI::errstr\n";
$get_emerg_contact_sth->execute(“12345”)
or die "Executing get_emerg_contact_sth, Error: $DBI::errstr\n";
• Or even better, using a scalar
my $InEmplid = “12345”;
$get_emerg_contact_sth->execute($InEmplid)
or die "Executing get_emerg_contact_sth, Error: $DBI::errstr\n";
Using Perl & DBI/DBD::Informix
13
Processing Fetched Data
$sql = qq#select rttype, rtdesc
from crltype
order by 1;#;
my $get_party_type_sth = $el_dbh->prepare($sql);
$get_party_type_sth->execute();
my (@Row, $PartyTypes);
while ( @Row = $get_party_type_sth->fetchrow_array() ) {
$PartyTypes{$Row[0]} = $Row[1];
}
• Same thing using hash references
my ($Row, %PartyTypes);
while ( $Row = $get_party_type_sth->fetchrow_hashref() ) {
$PartyTypes{$Row->{rttype}} = $Row->{rtdesc};
}
Using Perl & DBI/DBD::Informix
14
Processing Fetched Data, continued
$sql = qq#select count(*), sum(lamount)
from ledger
where linvoice = ? and
lzero != "Y";#;
my $check_sth = $dbh->prepare($sql);
$check_sth->execute($InvoiceNumber);
($NotPaid, $Amount) = $check_sth->fetchrow_array();
if ( $NotPaid > 0 ) { print "Not Paid, $NotPaid Ledger Items"; }
else {
print "Paid, Moving ...";
}
Using Perl & DBI/DBD::Informix
15
Processing Fetched Data, continued
$sql = qq#select fieldname, fieldvalue, xlatlongname, xlatshortname
from xlattable x
where effdt = ((select max(effdt) from xlattable x1
where x1.fieldname = x.fieldname and
x1.fieldvalue = x.fieldvalue and
x1.effdt <= TODAY and
x1.language_cd = "ENG")) and
x.fieldname in ("COMP_FREQUENCY", "EMPL_TYPE", "REG_TEMP", "ACTION",
"MILITARY_STATUS", "ETHNIC_GROUP", "REFERRAL_SOURCE",
"FULL_PART_TIME", "OFFICER_CD", "FLSA_STATUS","SEX",
"MAR_STATUS", "EMPL_STATUS", "HIGHEST_EDUC_LVL",
"PHONE_TYPE") and
x.language_cd = "ENG"
order by 1,2;#;
my $get_xlat_sth = $ps_dbh->prepare($sql);
$get_xlat_sth->execute();
my ($XlatRow);
while ($XlatRow = $get_xlat_sth->fetchrow_hashref()) {
$Xlat{ $XlatRow->{fieldname} }
{ $XlatRow->{fieldvalue} } = { longname => $XlatRow->{xlatlongname},
shortname => $XlatRow->{xlatshortname} };
}
Using Perl & DBI/DBD::Informix
16
Processing Fetched Data, continued
• Previous example loads the %Xlat hash with values such as:
–
–
–
–
–
–
$Xlat{MAR_STATUS}->{A}->{longname} = “Head of Household”
$Xlat{MAR_STATUS}->{A}->{shortname} = “Hd Hsehld”
$Xlat{MAR_STATUS}->{M}->{longname} = “Married”;
$Xlat{MAR_STATUS}->{M}->{shortname} = “Married”;
$Xlat{SEX}->{F}->{longname} = “Female”;
$Xlat{SEX}->{M}->{shortname} = “Male”;
• Hash values are referenced with:
–
–
$Xlat{SEX}->{$Active->{sex}}->{shortname}
$Xlat{MAR_STATUS}->{$Active->{mar_status}}->{longname}
Using Perl & DBI/DBD::Informix
17
Binding Columns To Fetch Data
$sql = qq#select pcode, pdesc
from praccode
where pdesc is not null
order by 1;#;
my $get_praccodes_sth = $el_dbh->prepare($sql);
$get_praccodes_sth->execute();
my ($b_pcode, $b_pdesc);
$get_praccodes_sth->bind_columns(undef, \$b_pcode, \$b_pdesc);
while ( $get_praccodes_sth->fetch ) {
$PracCodes{$b_pcode} = $b_pdesc;
}
Using Perl & DBI/DBD::Informix
18
Binding Columns Continued
$sql = qq#select cmatter, to_char(cdisbdt, '%m/%d/%Y') cdisbdt, cbillamt
from cost
where cmatter is not null;#;
my $get_cost_sth = $el_dbh->prepare($sql);
my (%CostRow);
$get_cost_sth->bind_columns(undef,
\$CostRow{cmatter},
\$CostRow{cdisbdt},
\$CostRow{cbillamt});
while ( $get_cost_sth->fetch() ) {
… Do Something With %CostRow Hash Values …
}
Alternate syntax
$sth->bind_col($col_num, \$col_variable);
$sth->bind_columns(@list_of_refs_to_vars_to_bind);
Using Perl & DBI/DBD::Informix
19
Inserting Rows
• Declare The Insert Statement Handle
$sql = qq#insert into winoutstat(wouser, wouser1, woreport, wotitle, wofile,
wodate0, wotime0, wostat1, wopid)
values(?, ?, ?, ?, ?,
?, ?, ?, ?);#;
my $ins_win_sth = $el_dbh->prepare_cached($sql);
• Do The Insert
$ins_win_sth->execute($Logon, $Logon, "Reminders", $Title, $FileName,
$OutDate, $OutTime, "RUNNING", $$);
my @Errd = @{$ins_win_sth->{ix_sqlerrd}};
$Hold{woindex} = $Errd[1];
Alternate syntax
$Hold{woindex} = $ins_win_sth->{ix_sqlerrd}[1];
Using Perl & DBI/DBD::Informix
20
Deleting Data
• Declare The Delete Statement Handle
$sql = qq#delete from pm_reminders
where matter_num = ? and
location = ? and
run_date = TODAY and
run_by = ?;#;
my $del_remind_sth = $el_dbh->prepare($sql);
• Delete Row(s) Based On Passed Parameters
$del_remind_sth->execute($MatRow->{mmatter},
$Hold{location},
$ThisLogon);
Using Perl & DBI/DBD::Informix
21
Using DBI With CGI
sub show_elite_files {
print header(),
start_html(-title=>"User File Manager",
-style=>{'src'=>'/styles/inSite_Style.css'});
$sql = qq#select woindex, woreport, wotitle, wodate0, wotime0,
wodate1, wotime1, wodesc1
from winoutstat
where (wostat1 = "COMPLETE" or wostat2 = "COMPLETE") and
wouser = ?
order by wodate0 desc, wotime0;#;
my $get_files_sth = $el_dbh->prepare($sql);
$get_files_sth->execute($ThisLogon);
my ($FileRow, $ViewLink, $ShowDate, $Count);
$Count = 0;
while ( $FileRow = $get_files_sth->fetchrow_hashref() ) {
$ViewLink = a({-href=>“getfiles.cgi?Session=${InSession}&FileNum=$FileRow->{woindex}"}, "Archive");
$ShowDate = "$FileRow->{wodate0} $FileRow->{wotime0}";
if ( $FileRow->{wodate0} ne $FileRow->{wodate1} ) {
$ShowDate .= " - " . $FileRow->{wodate1} . " " . $FileRow->{wotime1};
}
elsif ( $FileRow->{wotime0} ne $FileRow->{wotime1} ) {
$ShowDate .= "-" . $FileRow->{wotime1};
}
Using Perl & DBI/DBD::Informix
22
Using DBI With CGI, continued
### If This Is The First File Printed, Print The Headers First
if ( $Count == 0 ) {
my $ThisName = get_user_name($ThisLogon);
print start_table({-width=>'100%%',
-border=>1,
-cellpadding=>'5'}),
$NewLine,
Tr ( th ({-colspan=>'5'}, h4("Elite Report Files For User $ThisName") ) ),
Tr ( th ( "&nbsp" ),
th ( h4("Report") ),
th ( h4("Title") ),
th ( h4("Report Date") ),
th ( h4("Report Description") )
);
}
### Print Information For This File
print Tr ( td ({-align=>'center'}, "$ViewLink"),
td ({-align=>'left'}, "$FileRow->{woreport}"),
td ({-align=>'left'}, "$FileRow->{wotitle}"),
td ({-align=>'center'}, "$ShowDate"),
td ({-align=>'left'}, "$FileRow->{wodesc1}")
);
$Count++;
}
Using Perl & DBI/DBD::Informix
23
Using DBI With CGI, continued
### If No File Rows Found Show Error & Back Button, Otherwise
### Print The End Of The Table
if ( $Count == 0 ) {
print br, br,
textfield(-name=>'ProcessMessage',
-size=>'80',
-style=>$ErrorStyle,
-maxlength=>'80',
-value=>"No Files Were Found In Your Elite File Manager!"),
br, br;
print_back();
return;
}
else { print end_table(); }
print end_html();
} ### End Of SubRoutine show_elite_files
Using Perl & DBI/DBD::Informix
24
Using DBI With CGI, continued
<HTML><HEAD><TITLE>User File Manager</TITLE>
<LINK REL="stylesheet" HREF="/styles/inSite_Style.css">
</HEAD><BODY>
<TABLE BORDER="1" CELLPADDING="5" WIDTH="100%%">
<TR><TH COLSPAN="5"><H4>Elite Report Files For User Darryl Priest</H4></TH></TR>
<TR><TH>&nbsp</TH> <TH><H4>Report</H4></TH><TH><H4>Title</H4></TH>
<TH><H4>Report Date</H4></TH> <TH><H4>Report Description</H4></TH> </TR>
<TR><TD ALIGN="center"><A HREF="getfiles.cgi?Session=?&FileNum=?">Archive</A></TD>
<TD ALIGN="left">Time Batch Report</TD>
<TD ALIGN="left"></TD>
<TD ALIGN="center">10/01/2002 09:09</TD>
<TD ALIGN="left">1 batches processed. 1 reported.</TD></TR>
<TR><TD ALIGN="center">
<A HREF="getfiles.cgi?Session=?&FileNum=?">Archive</A></TD>
<TD ALIGN="left">Time Batch Finalization</TD>
<TD ALIGN="left"></TD>
<TD ALIGN="center">10/01/2002 09:09</TD>
<TD ALIGN="left">1 batches processed. 1 finalized.</TD></TR>
</TABLE></BODY></HTML>
Using Perl & DBI/DBD::Informix
25
Using DBI With CGI, continued
Using Perl & DBI/DBD::Informix
26
Defining Reusable Code
#!/usr/bin/perl
package PMData_Lib;
use strict;
require Exporter;
use vars
qw($VERSION @ISA @EXPORT);
$VERSION = 0.01;
@ISA = qw(Exporter);
@EXPORT = qw(get_names);
sub get_names {
my ($UseDbh, $Emplid) = @_;
my (@RetVals);
my $sql = qq#select name_first2last, name_last2first_s,
first_name, last_name, short_name
from pm_employees_v
where emplid_s = ?;#;
my $get_names_sth = $UseDbh->prepare_cached($sql);
$get_names_sth->execute($Emplid);
@RetVals = $get_names_sth->fetchrow_array();
return @RetVals;
}
1;
Using Perl & DBI/DBD::Informix
27
Using Your Module
#!/usr/bin/perl –w
use DBI;
use strict;
use lib q{/custom/perl/modules/};
use PMData_Lib;
…………
if ( defined $Emplid ) {
my (@RetNames) = PMData_Lib::get_names($pm_dbh, $Emplid);
if ( defined $RetNames[0] ) { $Name = $RetNames[0]; }
else { $Name = “Name Unknown”; }
}
Using Perl & DBI/DBD::Informix
28
Copy Table Example
dbschema -d son_db -t praccode -p all
DBSCHEMA Schema Utility
INFORMIX-SQL Version 7.31.UC2X3
Copyright (C) Informix Software, Inc., 1984-1998
Software Serial Number AAC#J741761
grant dba to "elite";
grant dba to "public";
{ TABLE "elite".praccode row size = 114 number of columns = 3 index size = 9 }
create table "elite".praccode
(
pcode smallint,
pdesc char(48),
pglmask char(64)
);
revoke all on "elite".praccode from "public";
create unique index "elite".i_pra1 on "elite".praccode (pcode);
grant
grant
grant
grant
grant
select on "elite".praccode to "public" as "elite";
update on "elite".praccode to "public" as "elite";
insert on "elite".praccode to "public" as "elite";
delete on "elite".praccode to "public" as "elite";
index on "elite".praccode to "public" as "elite";
Using Perl & DBI/DBD::Informix
29
Copy Table Example, continued
#!/usr/bin/perl -w
############################################################
###
Program Name: copy_table.pl
###
Author: Darryl Priest
###
Description: This script will copy a table from
###
one database to another.
############################################################
$| =1;
use DBI;
use Getopt::Std;
use strict;
use vars qw($opt_d $opt_n $opt_o $opt_s $opt_t);
my $Usage = qq#
Usage: copy_table.pl [ -d DBSpace -n New DB -o Old DB < -s Suffix > -t Table ]
-d
-n
-o
-s
-t
DBSpace In Which To Write The New Table
Database To Which The New Table Will Be Copied
Database From Which The Table Will Be Copied
Optional Suffix To Append To The New Table Name
Table Name To Be Copied
#;
############################################################
### Define Usage Variables And Get From Passed Options
############################################################
my ($OldDB, $NewDB, $OldTable, $Suffix, $NewTable, $DBSpace);
getopts('d:n:o:s:t:');
if ( defined $opt_o ) { $OldDB = $opt_o; }
else { print $Usage; exit; }
Using Perl & DBI/DBD::Informix
30
Copy Table Example, continued
if ( defined $opt_n ) { $NewDB = $opt_n; }
else { print $Usage; exit; }
if ( defined $opt_t ) { $OldTable = $opt_t; }
else { print $Usage; exit; }
if ( defined $opt_d ) { $DBSpace = $opt_d; }
else { print $Usage; exit; }
if ( defined $opt_s ) { $Suffix = $opt_s; }
else { $Suffix = ""; }
if ( length($Suffix) > 0 ) { $NewTable = "${OldTable}_${Suffix}"; }
else { $NewTable = $OldTable; }
print '>' x 60, "\n";
print "Will Move $OldDB:$OldTable -> $NewDB:$NewTable\n";
############################################################
### Use DBSchema To Get The Schema Of The Old Table
############################################################
print "\nGetting DBSchema For $OldDB:$OldTable At ", `date +'%D %r'`;
if ( ! $ENV{INFORMIXDIR} ) { $ENV{INFORMIXDIR} = "/usr/informix"; }
open (SCHEMA, "$ENV{INFORMIXDIR}/bin/dbschema -d $OldDB -t $OldTable -p all |")
or die "Error Opening DBSchema, $!";
Using Perl & DBI/DBD::Informix
31
Copy Table Example, continued
############################################################
### Read Past The Beginning Headers Returned By DBSchema
############################################################
while (<SCHEMA>) { last if ( $_ =~ /\}/ ); }
############################################################
### Get The SQL From DBSchema That Needs To Be Executed
############################################################
my @Sqls = ( );
my $RunSql = "";
while (<SCHEMA>) {
#########################################################
### Skip Blank Lines
#########################################################
next if /^$/;
#########################################################
### Replace All Occurances Of Old Table With New Table
#########################################################
s/$OldTable/$NewTable/g;
#########################################################
### Append This Line From DBSchema To The Current SQL
#########################################################
$RunSql .= " $_";
Using Perl & DBI/DBD::Informix
32
Copy Table Example, continued
#########################################################
### If This Line Ends A SQL Statement
#########################################################
if ( /\;/ ) {
######################################################
### If This Statement Is A Create Table, Append
### DBSpace And Lock Mode
######################################################
if ( $RunSql =~ /create\stable/ ) {
$RunSql =~ s/\)\s*;/\) in $DBSpace lock mode row\;/;
}
######################################################
### If This Statement Is A Create Index, Change The
### Index Name To Reflect The New Table Name
######################################################
if ( $RunSql =~ /create\s*(unique)*\s*index/ ) {
$RunSql =~ s/idx([1-9]+)/idx$1${Suffix}/;
}
######################################################
### Save This SQL To Later Execution
######################################################
push (@Sqls, $RunSql);
$RunSql = "";
}
}
close SCHEMA;
Using Perl & DBI/DBD::Informix
33
Copy Table Example, continued
############################################################
### Open Connection To The New Database
############################################################
my $to_dbh = DBI->connect("DBI:Informix:$NewDB")
or die "$NewDB Database Open Error: $DBI::errstr\n";
$to_dbh->{ChopBlanks} = 1;
$to_dbh->{AutoCommit} = 1;
$to_dbh->{PrintError} = 1;
$to_dbh->{RaiseError} = 1;
############################################################
### Open Connection To The Old Database
############################################################
my $InformixTCP = $ENV{INFORMIXSERVER};
$InformixTCP =~ s/_shm/_tcp/;
my $from_dbh = DBI->connect("DBI:Informix:$OldDB\@$InformixTCP")
or die "$OldDB Database Open Error: $DBI::errstr\n";
$from_dbh->{ChopBlanks} = 1;
$from_dbh->{AutoCommit} = 1;
$from_dbh->{PrintError} = 1;
$from_dbh->{RaiseError} = 1;
############################################################
### Drop The New Table From The New Database, Just In Case
############################################################
print "\nDropping New Table(Just In Case) At " . `date +'%D %r'`;
eval { $to_dbh->do("drop table $NewTable;"); };
Using Perl & DBI/DBD::Informix
34
Copy Table Example, continued
############################################################
### Build The New Table In The New Database
############################################################
print "\nBuilding New Table At " . `date +'%D %r'`;
print "Create Table SQL:\n$Sqls[0]\n";
$to_dbh->do($Sqls[0]);
############################################################
### Build Statement Handle To Select From Old Table
############################################################
$RunSql = qq#select * from $OldTable;#;
my $get_rows_sth = $from_dbh->prepare($RunSql);
$get_rows_sth->execute();
############################################################
### Build Statement Handle To Insert Into New Table
############################################################
$RunSql = "insert into $NewTable values (" . "?," x $get_rows_sth->{NUM_OF_FIELDS};
chop($RunSql);
$RunSql .= ")";
my $ins_rows_sth = $to_dbh->prepare($RunSql);
############################################################
### Copy Rows From Old To New Table
############################################################
print "\nCopying Rows From Old Table To New Table At " . `date +'%D %r'`;
Using Perl & DBI/DBD::Informix
35
Copy Table Example, continued
my ($Row);
my $Count = 1;
while ( $Row = $get_rows_sth->fetchrow_arrayref() ) {
if ( ($Count % 10000) == 0 ) { print "Copied $Count Rows At " . `date +'%D %r'`; }
$ins_rows_sth->execute(@$Row);
$Count++;
}
############################################################
### Build Indexes And Grant Permissions On New Table
############################################################
my ($x);
for ( $x = 1; $x <= $#Sqls; $x++ ) {
print "\nRunning SQL At " . `date +'%D %r'`;
print "$Sqls[$x]\n";
$to_dbh->do($Sqls[$x]);
}
############################################################
### Update Statistics For New Table
############################################################
print "Starting Updating Statistics For $NewTable At ", `date +'%D %r'`;
$to_dbh->do("update statistics low for table $NewTable;");
$from_dbh->disconnect();
$to_dbh->disconnect();
print "\nFinished Moving $OldDB:$OldTable -> $NewDB:$NewTable At ", `date +'%D %r'`;
print '<' x 60, "\n";
Using Perl & DBI/DBD::Informix
36
Supported, But Not Covered
• Accessing The Informix SQLCA Values
–
–
–
–
–
$sqlcode
$sqlerrm
$sqlerrp
@sqlerrd
@sqlwarn
=
=
=
=
=
$sth->{ix_sqlcode};
$sth->{ix_sqlerrm};
$sth->{ix_sqlerrp};
@{$sth->{ix_sqlerrd}};
@{$sth->{ix_sqlwarn}};
• Transactions using $dbh->commit(); and $dbh->rollback();
• Do With Parameters
– $dbh->do($stmt, undef, @parameters);
– $dbh->do($stmt, undef, $param1, $param2);
• $dbh->quote($string)
• $sth->finish and undef $sth
• Blob fields.
Using Perl & DBI/DBD::Informix
37
Supported, But Not Covered, continued
•
•
•
•
•
$sth attributes, NUM_OF_FIELDS, NAME, etc.
DBI->trace($level, $tracefile);
Fetch methods selectrow_array() & selectall_array()
$dbh->func()
Statement Handles For Update
$st1 = $dbh->prepare("SELECT * FROM SomeTable FOR UPDATE");
$wc = "WHERE CURRENT OF $st1->{CursorName}";
$st2 = $dbh->prepare("UPDATE SomeTable SET SomeColumn = ? $wc");
$st1->execute;
$row = $st1->fetch;
$st2->execute("New Value");
• $sth->rows();
Using Perl & DBI/DBD::Informix
38
Additional Information
• dbi.perl.org/ - DBI Home Page
• www.perl.com - Perl
–
www.perl.com/lpt/a/1999/10/DBI.html
• www.perl.org
• www.cpan.org/ - Comprehensive Perl Archive Network
• www.activestate.com
• perldoc DBI – DBI Man Pages
• perldoc DBD::Informix – DBD::Informix Man Pages
• Programming Perl by Larry Wall, Tom Christiansen & Randal Schwartz
• Programming the Perl DBI, by Alligator Descartes and Tim Bunce
• Learning Perl by Randal Schwartz
Using Perl & DBI/DBD::Informix
39
Thanks!
• To the authors who brought us:
– Perl
• Larry Wall
– DBI
• Tim Bunce
• Alligator Descartes
– DBD::Informix
• Jonathan Leffler
Using Perl & DBI/DBD::Informix
40