Beginner's Guide to Web-Enabling your Informix Database

1
Beginner's Guide to Web-Enabling your Informix
Database
(Using Perl on NT)

• Peter Schmidt
• Consultant
• July 20, 1999

2
The Premise

• I had a nice, fast NT workstation at home, with
  the new Informix IDS 7.30 loaded and running.
• I had loaded my personal videotape database with
  over 600 entries.

3
The Problem

• But here I am - an old Informix-4gl programmer on
  a system with no 4GL language available.
• I knew I wanted to use my web browser to manage
  the database, but I couldn't justify purchasing
  the web-datablade with my budget (of zero).

4
The Solution

• This session is about how I solved that problem
  easily and very inexpensively (free) using
• Informix (database)
• Perl (application language)
• Apache (web server)
• NT (OS)

5
PrerequisitesHardware/OS

• Microsoft NT 4.0 SP3 (Workstation or Server)
• 233 Mhz
• 64 Meg Memory

6
PrerequisitesInstall and Configure Software

• Informix IDS 7.30 for NT (was free on TNT)
• Apache WebServer
• http//www.apache.org
• (or any other web server)
• Perl
• http//www.cpan.org/ports (shareware)

7
Architecture (Typical)
Web Server
CGI
Perl
Internet
Database Server (Informix)
Web Browser (Client)
8
Architecture (All on one computer)
Web Server
NT Operating System
CGI
Perl
Web Browser (Client)
Database Server (Informix)
9
Using CGI to run Perl

• The Web server must load, execute, and
  terminate a new CGI/Perl program for each user
  access.
• Under heavy server loads, this can degrade
  performance.

10
PrerequisitesLanguages Used

• Perl
• Informix SQL
• HTML
• Javascript
• DOS/NT Batch Script

11
Perl Demo 1pdemo1.pl
Pro
gram pdemo1.pl Author Peter R.
Schmidt Description Print "Hello
World"
Note The 2 newlines in the statement below
are required print "Content-type
text/html\n\n" print "ltHTMLgtltBODYgt\n" print
"ltCENTERgtltH1gtHello World.ltBRgtltBRgt\n" print
"(pdemo1.pl) lt/H1gtlt/CENTERgtltBRgt\n" print
"lt/BODYgtlt/HTMLgt\n"
12
Perl Demo 1pdemo1.pl - View Source
ltHTMLgtlt BODY gt ltCENTERgtltH1gtHello
World.ltBRgtltBRgt (pdemo1.pl) lt/H1gtlt/CENTERgtltBRgt lt/BO
DYgtlt/HTMLgt
13
Perl -- Informix-4GL
Perl - display a literal print
"ltCENTERgtltH1gtHello World.ltBRgtltBRgt\n"
Informix-4gl display "ltCENTERgtltH1gtHello
World.ltBRgtltBRgt"

• Comparison
• "print v. "display"
• Perl statements followed by a semi-colon.
• Perl requires newline characters for
  end-of-line (\n).
• Both use "" to indicate comments.
• Perl is case sensitive.

14
pdemo2.pl
WHERE TO FIND STANDARD PERL FUNCTIONS push
(_at_INC,C/perl5/lib") WHERE TO FIND STANDARD
PERL FUNCTIONS require "ctime.pl" INCLUDE
THIS FILE AS PART OF MY PROGRAM

SYS_DT time GET THE SYSTEM
TIME DT_STR ctime(SYS_DT) REFORMAT INTO
A STRING Note The 2 newlines in the statement
below are required print "Content-type
text/html\n\n" print "ltHTMLgtltBODYgt\n" print
"ltCENTERgtltH1gtHello World.ltBRgtltBRgt\n" print
"(pdemo2.pl)ltBRgtltBRgtltBRgt\n" print "The date and
time is ltBRgt\n" print "DT_STR" print
"lt/H1gtlt/CENTERgt\n" print "lt/BODYgtlt/HTMLgt\n"
15
Perl -- Informix-4GL
Perl - append to an array push
(_at_INC,C/perl5/lib")
Informix-4gl DEFINE A_MAX INTEGER DEFINE INC
ARRAY?? OF CHAR(??) LET A_MAX (number of
populated elements in array) 1 LET INCA_MAX
C/perl5/lib"
Note Array name is preceded by an "_at_" sign.
16
Perl -- Informix-4GL
Perl - include external files require
"ctime.pl" Include this file as part of my
program
Informix-4gl No equivalent statement. External
references resolved at link time.
Purpose Include external modules as a part of
this program. (Like linking a module at
compile/link time.)
17
Perl -- Informix-4GL
Perl - display system date/time SYS_DT
time GET THE SYSTEM TIME DT_STR
ctime(SYS_DT) REFORMAT INTO A STRING
Informix-4gl DEFINE SYS_DT DATETIME YEAR
TO SECOND DEFINE DT_STR CHAR(40) LET
SYS_DT CURRENT YEAR TO SECOND LET DT_STR
SYS_DT
Note Perl variable name is preceded by a
sign.
18
pdemo3.pl
push (_at_INC,C/perl5/lib") WHERE TO FIND
STANDARD PERL FUNCTIONS require "ctime.pl"
INCLUDE THIS FILE AS PART OF MY
PROGRAM
SYS_DT time
GET THE SYSTEM TIME DT_STR
ctime(SYS_DT) REFORMAT INTO A
STRING
print ltltTAGPOINT
Note a blank line is required after
Content-type Content-type text/html ltHTMLgtltBODYgt
ltCENTERgtltH1gtHello World.ltBRgtltBRgt (pdemo3.pl)ltBRgtlt
BRgtltBRgt The date and time is ltBRgt DT_STR lt/H1gtlt/C
ENTERgt lt/BODYgtlt/HTMLgt TAGPOINT
19
Perl -- Informix-4GL
Perl - display a long string print ltltTAGPOINT
Note a blank line is required after
Content-type Content-type text/plain ltHTMLgtltBOD
YgtltCENTERgtltH1gtHello World.ltBRgtltBRgt (pdemo3.pl)ltBRgt
ltBRgtltBRgt The date and time is ltBRgt DT_STR lt/H1gtlt/
CENTERgt lt/BODYgtlt/HTMLgt TAGPOINT
Informix-4gl display Content-type
text/htmlgt\n\n\ ltHTMLgtltBODYgtltCENTERgtltH1gtHello
World.ltBRgtltBRgt\n\ (pdemo3.pl)ltBRgtltBRgtltBRgt\n The
date and time is ltBRgt\n, END FIRST STRING
HERE DT_STR, DISPLAY THE DATE/TIME, THEN
START NEW STRING lt/H1gtlt/CENTERgt\n\ lt/BODYgtlt/HTMLgt
\n END SECOND STRING HERE
20
Lets see the demo already!
video_db1.pl
A web-to-database program (written in Perl)
21
Perl -- Informix-4GL
Perl - call a function main
initialize_custom_variables Initialize
unique variables initialize_global_variables
Initialize global variables
display_heading0 Display a standard
heading
Informix-4gl main call initialize_custom_variabl
es() Initialize unique variables call
initialize_global_variables() Initialize
global variables call display_heading0()
Display a standard heading end main
Note Use the sign instead of the word
call. Parentheses are not required. Perl
does not require a main.
22
Perl -- Informix-4GL
Perl - populate array _at_LIST2 ( "Movies",
"Deep Space 9", "Star Trek \(Original\)",
"Star Trek the Next Generation", "Star Trek
Voyager", "Home Movies", "Max Headroom",
"Other" )
Informix-4gl define LIST2 array8 of
char(40) let LIST21 "Movies" let LIST22
"Deep Space 9" let LIST23 "Star Trek
(Original)" let LIST24 "Star Trek the Next
Generation" let LIST25 "Star Trek
Voyager" let LIST26 "Home Movies" let
LIST27 "Max Headroom" let LIST28 "Other"
Note Parentheses in the Perl string are
preceded by backslashes.
23
Perl get form variables
Perl - get form variables ReadParse(input)
Call function to read in all the variables
set by the form. Place them into the
input associative array.
Purpose Go collect all of the variables passed by
the previous form. Pass the name of the array as
an argument (not the array itself). Place the
results into an associative array named input.
24
Associative Arrays in Perl

• Array with a string as an index.
  (Keyword/value pairs)
• Examples
• inputSERIAL_ID 0In the input array, set
  the array element associated with the keyword
  SERIAL_ID to zero.
• print The serial id is inputSERIAL_ID\n"Dis
  play the value associated with the keyword
  SERIAL_ID.
• Almost works like two informix arrays, where one
  array defines keywords and another defines
  values.
• Or like a informix record with associated
  columns.

25
Perl -- Informix-4GL
Perl - if/then clause if ( (inputSTAGE 1)
(inputSTAGE 8) ) add_mode_1()
Add Mode 1 - collect info - new/same
Informix-4gl if ( (input.STAGE 1) or
(input.STAGE 8) ) then call add_mode_1()
Add Mode 1 - collect info - new/same end if
Note In Perl, use for or, for
and, ! for not.
26
Perl -- Informix-4GL
Perl - define a function sub main_menu
display_heading1("Main Menu") display_menu1

Informix-4gl FUNCTION main_menu () call
display_heading1("Main Menu") call
display_menu1() END FUNCTION
Note Perl functions begin with the keyword sub
and use curly brackets.
27
Perl -- Informix-4GL
Perl - pass arguments sub display_heading1
local (HEADING_STR) _at__ print
HEADING_STR\n
Informix-4gl FUNCTION display_heading1
(HEADING_STR) define HEADING_STR
CHAR(80) display HEADING_STR END FUNCTION
Note Perl functions receive input arguments from
the _at__ array. Variables are global unless
defined as local.
28
Perl -- Informix-4GL
Perl - return arguments if (sql_stdout)
sub sql_stdout return FALSE
Informix-4gl if ( sql_stdout() ) then end
if FUNCTION sql_stdout () return FALSE END
FUNCTION
Note Perl functions automatically return the
last value referenced.
29
-- Break --
Where to download the perl source code or
presentation
Peter Schmidt web www.prstech.com email peter_at_p
rstech.com
30
Perl -- Informix-4GL
Perl Example - writing to an ascii flat file
Create a name that will open for writing
SQL_FILENAME1 "gt my_filename.sql"
open (TEMP_SQL,SQL_FILENAME1) print
"Can't open temp sql output file
SQL_FILENAME1 in shelter_query2.plltBRgt\n!ltBRgt
\n" SQL "unload to SQL_FILENAME3\n"
Populate string SQL . "select from
TABLE_NAME1\n" Append to string print
(TEMP_SQL "SQL\n") Write the SQL statement to
the temp file close TEMP_SQL Close the temp
SQL file unlink (SQL_FILENAME2) Delete the
temp SQL file
Informix-4gl No native equivalent capability.
31
Perl -- Informix-4GL
Perl - string substitution SQL_FILENAME4
s/unl/out/g Replace ".unl with
".out SQL_FILENAME3 s/\//\\\\/g
Convert forward slashes to back-slashes
Informix-4gl No native equivalent capability.
32
Perl -- Informix-4GL
Perl - string comparison if (SQL_ROW
/Error/i) . . . Error found ? - not
case sensitive!! if ( COL_NAME
/TYPE_CODETITLECOMMENT/i) . . .
Informix-4gl if (SQL_ROW matches Error) then
. . . Error found ? case COL_NAME when
TYPE_CODE . . . when TITLE . . . when
COMMENT . . . end case
33
Perl -- Informix-4GL
Perl - make a pass through an associative
array foreach COL_NAME (sort keys(input))
print keyword COL_NAME\n print value
inputCOL_NAME\n
Informix-4gl define a_key arrayx of
char40 an array of keywords define a_val
arrayx of char40 an array of values FOR X
1 to arr_count() display keyword ,
a_keyx display value , a_valx END FOR
34
sql_runner.bat
_at_echo off REM REM Arg1 Database Name REM Arg2
Filename containing SQL script REM Arg3 Filename
of ".unl" file (if any) REM Arg4 Filename to
write stdout/stderr to REM set INFORMIXDIRC\Info
rmix.730 set INFORMIXSERVERol_demo1 set
ONCONFIGONCONFIG.ol_demo1 set PATHC\Informix.73
0\binPATH dbaccess 1 2 gt 4 2gt1 if
not exist 4 goto TAG4 type 4 erase 4 TAG4 if
not exist 3 goto TAG3 type 3 erase 3 TAG3
35
Execute sql_runner.bat
RUNTXT join("", "\_at_SQL_DATA\SQL_RUNNER_PA
TH SQL_RUNNER_NAME", "DATABASE
", "SQL_FILENAME2 ", .SQL
statement "\"SQL_FILENAME3\" ", .UNL
filename (May not be used) "\"SQL_FILENAME4\"
", .OUT filename (STDOUT/STDERR) "\
") End with a trailing back-tick eval
RUNTXT Execute the SQL statement
Populate the _at_SQL_DATA array with the results
Note This section uses the Perl "eval" function,
which executes a mini-perl program, that is
contained in a variable (in this case RUNTXT).
The statement being executed contains another
statement, surrounded by back-ticks, which works
just like a unix shell-script (which executes the
statement surrounded by back-ticks - in this case
"sql_runner.bat)". The array _at_SQL_DATA is
populated with the result of the query.
36
Output from sql_runner.bat
Database selected. 11 row(s) unloaded. Database
closed. 610MOVIE181Maverick2.5James Garner,
Mel Gibson 612MOVIE181Presumed
InnocentHarrison Ford 613MOVIE182A League
of Their Own2.5Madonna 611MOVIE182White
Christmas2.5Bing Crosby 614MOVIE183Beverly
Hills Cop2.0 615MOVIE183Toy
Story2.0 616MOVIE184On Golden
Pond2.0 617MOVIE184True Lies3.0Arnold
Schwarzenegger 635MOVIE185The Santa
Clause2.0Tim Allen 637MOVIE185Get
Shorty2.0 636MOVIE185Flintstones2.0John
Goodman
Note _at_SQL_DATA gets populated with the above
data. _at_UNLOAD_LIST contains only the
pipe-delimited records.
37
Perl -- Informix-4GL
Perl - compare 2 strings or numbers if (SQL_ROW
/Database selected/i) next Partial
match (string only) if (SEARCH_STR eq "")
Exact match (string only) if
(inputSTAGE 2) Exact match
(number)
Informix-4gl if (SQL_ROW matches Database
selected) then continue Partial match
end if if (SEARCH_STR "") then end
if Exact match if (STAGE 2) then end
if Exact match (number)
Note Lowercase i (eye) after search string
indicates ignore case.
38
Perlparse output from sql_runner.bat
foreach SQL_ROW (_at_SQL_DATA) if (SQL_ROW eq
"\n") next Exclude this line if
(SQL_ROW /Database selected/i) next
Exclude this line if (SQL_ROW /Database
closed/i) next Exclude this line if
(SQL_ROW /\/) Pipe delimited record
?? push (_at_UNLOAD_LIST,SQL_ROW) Append to
array CNT_FOUND Count records
unloaded else must be
stdout/stderr if (SQL_ROW /Error/i)
Error found ? FLAG_ERRORTRUE Flag if
error is found if (SQL_ROW /1 row\(s\)
inserted\./i) Successful insert
? FLAG_INSERTTRUE print
"SQL_ROWltBRgt\n" Display this line to
output
39
Perl - split Split a string into sub-parts based
on a delimiter.
foreach ITEM (split(/\/,SQL_ROW)) . . .
Parse pipe-delimited record sub
populate_form_variables (
inputSERIAL_ID, inputTYPE_CODE,
inputTAPE_NUM, inputTITLE,
inputHOURS, inputCOMMENT,
PLACEHOLDER )
split(/\/,UNLOAD_LIST0,7)
40
Perl - parse delimited record (in _at_unload_list)
foreach SQL_ROW (_at_UNLOAD_LIST)
CNT_FOUND print "lttrgt\n" New table
row ITEM_NUM0 foreach ITEM
(split(/\/,SQL_ROW)) Parse pipe-delimited
record ITEM_NUM if ( ITEM_NUM lt 1
ITEM_NUM gt 7 ) next if (length(ITEM)
0) print "lttdgtnbsplt/tdgt\n" If
empty, prt non-breaking space else
print "lttdgtITEMlt/tdgt\n" Not empty - Table
data print "lt\trgt\n" End table row
41
Tips and Tricks
Content-type text/html must be followed by 2
newlines. Test your Perl program from the DOS
prompt. Use the -w feature of Perl for warning
messages. Get textpad from Helios Software
(shareware). http//www.textpad.com 27.00 for
full license
42
Tips and Tricks
Use javascript for field validation in the
browser. On a netscape browser, type in
javascript as the url to bring up the javascript
console. Know what version of javascript you are
running. JavaScript 1.0 Supported by Netscape
Navigator 2.x JavaScript 1.1 Supported by
Netscape Navigator 3.x JavaScript 1.2 Supported
by Netscape Navigator 4.x
43
Questions ?
Where to download the perl source code or
presentation
Peter Schmidt web www.prstech.com email peter_at_p
rstech.com