A System to Generate A Simple and Reusable Web
Contents
1. T nprint lt lt EOM n T lt FORM METHOD POST ACTION Sscript gt VA E T lt INPUT TYPE HIDDEN NAME qk value Sarray QUERY_NAME gt An T lt INPUT TYPE HIDDEN NAME username3 value Sarray username gt n T lt INPUT TYPE HIDDEN NAME authname3 value Sarray authname gt n T lt html gt n T lt head gt n T lt title gt Variable register page lt title gt n T lt head gt n T lt body bgcolor faafaf gt n T lt CENTER gt n T lt TABLE BORDER 0 WIDTH 500 gt n T nEOM n T foreach S field sort keys Squery An T n T Svariable_name Squery Sfield n T printy Bh H chen N T print lt TD gt lt FONT FACE Arial Helvetica gt lt FONT AVENSE TOTANA MA T lt FONT gt lt TD gt n T print lt TD gt lt INPUT TYPE TEXT NAME Svariable_name T VALUE Sarray Svariable_name gt n T print lt TD gt 4 n Ty T print Hen Ra MA y T n T nprint lt lt EOM An TESTER VA Ke T lt TD COLSPAN 2 align right gt lt hr noshade gt lt input type submit option value execut qu ry gt n T lt TD gt n T lt TR gt n T lt TABLE gt n T lt CENTER gt n T lt BODY gt n T lt HTML gt n T lt FORM gt n T nEOM n T d2. Sdbh gt disconnect n UT return Serrors Serrors_list n UT n UT sub save dataNn UT An W I UT X username Sarray username2 An GT n Sauth 1 array authname2 An UT Sdbh DBI gt connect A database Susername UT TE ANSdbisrerrstr Art Bas UT n UT Serrorst An UT P Serrors_list dbi errstr n UT n WE UT Stable_key Sarray edit n UT NStable name Stables S table_key n UT Stable table_name An UT VStable name type Stable_name type n DRM Stabletype VStable name type An UI N X sql INSERT INTO Suseraccount Stable_name An UT Ssql An UT T foreach M field sort keys Stable Ain UT XSvariable Stable Sfield n UT X sqlv Svariable An UT Ssqlv ENTENG n ss UT if Stabletype Svariable integer i etype Svariable smallint i UT Stabletype Svariable decimal i etype Svariable double i n UT n Hie UT Sarray Svariable s X X X g UT XSinputvalue Sarray S variable n UT else n UT Sarray Svariable s g UT Sinputvalue Sarray Svariable UT An K UT Ssqlval Sinputvalue An UT Ssqlval An UT An UT Ssqlv n UT Ssqls 1 An UT Ssqlval n UT Ssqlvals
3. print OUT T mn print OUT S sth gt execute n print OUT print lt html gt An print OUT print lt head gt An print OUT print lt title gt Query result lt title gt An print OUT print lt head gt An print OUT print lt body bgcolor ffaaff gt An print OUT print lt center gt An print OUT print lt table border 1 gt n print OUT print lt TR gt n print OUT for s 0 s lt Ssth gt NUM_OF_FIELDS s print OUT NA print OUT print lt TD gt lt B gt lt FONT FACE Arial SIZE 1 gt sth gt NAME gt s lt FONT gt lt B gt lt TD gt An print OUT e NS print OUT print lt TR gt n print OUT while row sth gt fetchrow n print OUT print lt TR gt n print OUT for Si 0 Si lt Ssth gt NUM_OF_FIELDS Si An print OUT print lt TD gt lt FONT FACE Arial Helvetica gt lt FONT SIZE 1 gt row i print OUT lt FONT gt lt TD gt n print OUT np for n print OUT print lt TR gt n print OUT while An print OUT print lt table gt n print OUT H print lt center gt An print OUT T print lt body gt n print OUT print lt html gt An print OUT S sth gt finish n print OUT S dbh gt disconnect An prank OUT ANa Ty print OUT sub print_errors
4. print OUT lt FONT gt lt B gt lt TD gt n print OUT lt TR gt n print OUT lt hr noshade gt print OUT lt TABLE BORDER 0 WIDTH 500 gt n print OUT lt TR gt n print OUT lt TD gt lt FONT FACE Arial Helvetica gt lt FONT SIZE 1 gt User Name lt FONT gt lt TD gt n print OUT lt TD gt lt INPUT TYPE TEXT NAME USER VALUE Sarray USER gt n print OUT lt TD gt n print OUT lt TR gt n print OUT lt TR gt n 39 prin 1 gt Pa prin prin prin prin prin name prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin Plea prin lt FO prin prin prin prin prin prin prin prin KEES KE ETE Fs CT E IT FEAS AD KE St FS TEEL Oe T EE Ee ee ET CT Ct 44 Gro g Cr FE EET e EP ET E N Arial value Sarray USER gt n Helvetica gt Ww OUT lt TD gt lt FONT FACE Arial Helvetica gt lt FONT SIZI sword lt FONT gt lt TD gt n OUT lt TD gt lt INPUT TYPE PASSWORD NAME AUTH VALUE Sarray AUTH gt n OUT lt TD gt n OUT lt TR gt n OUT lt TR gt n OUT lt TD COLSPAN 2 align center gt lt hr noshade gt lt input type subm
5. The table structure description gives the information for creating the tables in the database Because all of the table creation statements have the same syntax this description part is given in a natural language format For the queries some variable values needs to be bound dynamically In addition the number of variables varies So data binding has to be used to integrate the variable values into the gueries at run time Hence the syntax for the guery part should describe the variables like the first guery in Figure 1 The formats of the text file are designed to represent these ideas as clearly as possible Figure 1 gives an example of the text file dbserver richert cac psu edu user axz110 dbname axz110 D o le work_order i Customer name name char 10 yes yes d Bldg Location address char 20 yes yes room room char 10 yes no phone phone char 15 no no budget budget char 5 yes no Problem problem char 50 no no Date submitted submit date date yes no Date logged log date date yes no Date printed print date date no no Date_completed complete_date date yes no Work_order_number Work_order_no integer yes no Technician_name tech_name char 20 yes no CT rr rr CT rr et e gz d da d da da de df da CC GE E le eguip name Department department char 20 yes yes Contact_person contact_name char 20 yes yes Equipment_needed equipment char
6. amp print errors An 38 print OUT Fin MES print OUT else n print OUT amp print gueryresult n print OUT amp print thankyou An print OUT in print OUT n print OUT elsif Sarray option eq execute query n print OUT Serrors Serrors_list amp query_with_variable An print OUT if Serrors gt 0 n print OUT n print OUT amp print top2 An print OUT amp print errors An print OUT ENE H print OUT T else in print OUT amp print thankyou An print OUT amp print guery withvariable result An print OUT n print OUT n print OUT exit 1 n print OUT NN PEER EEE HE EEE RR EE EE EE EEE EEE EEE EEE EEE EE RRA n ma print OUT Wii program subroutines VERTEER EER EER EER EER EER EER EER EER KERR OO print OUT sub login n print OUT n print OUT print lt lt EOM An print OUT lt FORM METHOD POST ACTION Sscript gt Vos print OUT lt html gt Ni print OUT lt head gt Aun ait print OUT lt title gt login page lt title gt An print OUT lt head gt An print OUT lt body bgcolor faafaf gt n print OUT lt CENTER gt n print OUT lt TR gt n print OUT lt TD COLAPAN 4 gt lt B gt lt FONT FACE Arial Helvetica gt Welcome to the Web query print OUT System n
7. n print OUT nEOM n print OUT N PAG close OUT die can t close Soutputl SI open OUT gt Soutput2 die can t create Soutput2 SI print OUT connect to print OUT Sdbname print OUT An tables split table i tablein foreach Stables tables chmop s cM cJ if Stables MS Stables s St s Scurrenttable 1 Sstmt CREATE TABLE Sstmt Stables if currenttable ne pkstring split currenttable SScurrenttable join pkstring Sstmt PRIMARY KEY S currenttable else Sstmt s Sstmt 1 Sstmt Sstmt s n g print OUT Sstmt print out the create table statement to another file 48 print OUT if foreach close INPUT close OUT exit 0 r NATS die can t close input die can t close Soutput2 SE Sit 49
8. are designed to meet various business needs and e business objectives across different industries This paper introduces a specific generating system that generates a web enabled solution for database queries This generating system first reads a text file that defines the table and queries in a simple format then generates a target CGI Common Gateway Interface program The target program provides a user friendly interface to run the queries on the Internet Hence the target program simplifies the database query work for database administrators and makes these queries reusable on the Internet The main advantages of our system are reusability portability and simplicity The original idea for our overall system came from database administrators DBAs They complain that they have to issue hundreds of database queries every day Additionally they create many tables that have similar structures and insert many records into these tables each day They also do many queries corresponding to these tables and values This work is repetitive and tedious Thus they want software that can reduce this redundant work Furthermore this software needs to be portable to as many database systems as possible Another issue considered is simplicity Not only database administrators but also most of the non professional users should be able to operate the system Thus it frees the professional from redundant work Our overall system is designed to
9. chmop s cM cJd if hashquerys S Squerys m Squerys Squerys Shashquerys Squerys n Sm foreach Squerys n print OUT Squerys print out hash table for query description hashtables split Shashtable Shashfinal Stables qw Shashfinal n Sn 1 foreach Shashtables hashtables chmop s cM cJ Shashfinal n Shashfinal Shashfinal Shashtables Shashfinal n Sn Shashfinal n print OUT Shashfinal print out hash table for table name index eachtablenocomma split Seachtable Seachtableresult join eachtablenocomma eachtablehash split table i Seachtableresult foreach Seachtablehash eachtablehash chmop s XcMieJ1 36 if Seachtablehash S Seacht Seacht Seachtablehash Seacht n print OUT Seacht print out hash tables for each table s attribute description name and actual name foreacha eachtabletypenocomma split Seachtabletype Seachtabletyperesult join eachtabletypenocomma eachtabletypehash split table i Seachtabletyperesult foreach Seachtabletypehash eachtabletypehash chmop s cM cd if Seachtabletypehash S Seachttype n Seachttype Seachtabletypehash Seachttype n print OUT Seachttype print out hash tables for each table s a
10. items of ipment in a specific department partment dept ct contact_name om gxzZ110 eguip name department dept roup by contact_name ing count gt all select count from qxz110 equip_name where department dept group by contact_name q amp s f q amp s f W q e s Figure 1 Example text file The following paragraph explains the text file s syntax by using Figure 1 Each part of the definition is separated by the symbol For the database server and database names the format is dbserver user_server_name dbname user_database_name For instance the server name is richert cac psu edu and the database name is gxz 10 in Figurel The ID of the user who will create the following tables should also be described here Since a user can input the values into tables that are created by other users the account ID will be used to indicate the table s original creator in the table name selection page as in Figure 4 This is required by DB2 For the same reason the table name in the query definition part should also be prefixed by the original creator s ID such as qxz1 10 work order and gxz110 eguip name in Figure 1 For the table structure the user needs to give the table name first by the format table table name and then define each attribute for this table using the syntax attri description_name actual_attribute_name type yes no yes no T
11. n ns T sub query_without_variable n T An Ws T Sstmt querystmt Sarray QUERY_NAME n 43 prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin LEI FE a LEI g Cr EU CT e Cf Ct CT ee CT FE Cr FT E O OO OO OO OO OO OO OO OO O HHAHHHHHHHHHHHHHHHHHA LE LET Ab ES CT ET EST AE Et ES LET EF ES ER EP GE GE er ek e CA Cr q SE Gr OF SE EP SE SE CES CH O H 0000000000000 ls Ed HA Ed Had e HEHE ER OH O OOOOOOOOOOOOOOOOOOO Ed Sak FH Ed A F dd FH FR Ed Ed HA Ed Ed Ed od HA Susername Sarray username n Sauth Sarray authname n Sdbh DBI gt connect S database Susername Ssth Sdbh gt prepare Sstmt An if Sdbh gt errstr An p Anew Serrorst n XSerrors_list dbh gt errstr An An Ssth gt execute n if Ssth gt errstr An Et Ms VSerrorstt An XSerrors_list 1 sth gt errstr An pAn Ssth gt finish n Sdbh gt disconnect An return Serrors Serrors_list An y sub query_with_variable n n Ws An VE NSstmtstr querystmt Sarray qk n Sstmt stmtstr An each split WV S st
12. to be bound later a hash table for this query is created as guerynamel qw Customer_name name Roomno room For a guery that does not need any variable values like the fifth guery find all of the work orders no queryname5 exists in the program Whenever a query is selected we can use the code if defined query where guery name queryname query_key query query_name to check if such a hash table exists then implement the appropriate operation query_key is obtained from the web users dynamically The qeury_name is a hash reference and the code query query_name is to dereference this hash reference From these examples it is evident that the hash table with its references makes the programming job simple After these hash tables are created we output them into the beginning of the target CGI program The rest of the code in the target CGI program will be written based on these hash tables by using the references to these hash tables In this sense the remaining code 24 is almost fixed code The generating program simply outputs the same code for this part each time The target program is a web CGI script program Essentially CGI is a protocol The concept of CGI is quite simple the user provides some information on the web page and the browser sends this information to the web server The web server passes this information to a particular system This system processes the
13. to group and list corresponding attribute names for each selected table The hash table provides an efficient data structure for these relationships For each database table s attribute names and description names we just create a hash table whose name is also the name of table in the database Within the hash table the indices are description attribute names and the value for each index is its actual attribute name For example the hash table for saving the attribute information in the database table work order in Figure 1 is work order qw Customer_name name Bldg Location address room room phone phone budget budget Problem problem Date_submitted submit_date Date_logged log_date Date_printed print_date Date_completed complete_date Work_order_number Work_order_no Technician_nam tech_nam 22 The relationship between the table name the attribute description name and attribute actual name can be described as work_order room room for the attribute room Query information is saved by a similar structure but keeping track of this information is more complicated For each query we need to keep track of the description sentence the query statement and the dynamic variable names if there are any for this query Our implementation idea is to create one hash table for all of the query description sentences indexed by the number then each query statement is saved as a string The string name for each quer
14. 1 An UT Ssql Ssqls An UT Ssql VALUES n UT Ssql Ssqlvals An UT Ssql An UT Ssth Sdbh gt prepare sql An UT if Sdbh gt errstr An UT n UT Serrorst t n 45 prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin SIZE prin prin prin prin prin prin NSro prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin LG AE AT EE AE EP GE ST YE GE er O H Lt AE ET T Gr ET Fe MT EE TE T KEE E S Ebert ek ch ER gg O OG EI EN u OO DOOD OO Eh E EE EE Ee EEN E E E OD Fi Si Hd HA Ed Rd Ei ES CT ET FE LET ME SE CL KE ER ET GE EZE T er OO OO OO OO CO E Sch HH EES ca Ei O H O H O H O OOOOOOO HH Sak Hd H H Hs ls Gi OHOH H zc HO ER HH dd A A AA Sir XSerrors list dbh gt errstr An n K NX sth gt execute An if Ssth gt errstr An n r m Serrorst n y Serrors_list 1 sth gt errstr An An Ms Sdbh gt disconnect An return Serrors V errors list An An me sub print_queryresult in Sdbh DBI gt connect Sdatabase Susername Sauth An V sth Sdbh gt prepare Sstmt An XSsth gt execut
15. 20 yes yes Brand_of_equipment brand char 20 yes no Date_needed date_needed date yes no Daytime_phone_no phone char 20 no no Email_address email char 20 no no Date_submitted submit_date date yes no Equipment_order_number equip_order_no char 10 Serial_number serial_no char 10 yes no Model_number model_no char 10 yes no EE OF ET OT ER AE KL GT Ek EE Ef AT TE Ft EE EI TE TT EE EK EE EP G t a a a a a a a a a a a a tab a a a a a a a a a a a a ery find all of the work orders by the name and room number roomno room Customer_name name select from qxz110 work_order where name na and room query find all the information in the equip_name table amp select from ogxz110 eguip name continued ga query find all of the work orders by submitted date amp Date submitted date submit select from ogxz110 work order where submit dates date submit query find the customer names in work order by sumbmitted day amp Date submitted subdate select name from ogxz110 work order where submit date subdate ue ry ind all of the work orders gxzZ110 work order ry ind name and submitted date by room number in the work_order table oom_number rono ect name submit_date qxz110 work_order re room FONO ind the name of the person who is the contact for the most
16. DBI driver and HTML are also tools frequently used in our implementation The diagram for our overall system is given in Figure 10 3 Conclusion This paper has introduced a specific system that is able to generate a web enabled solution for database querying from the user prospective and the programmer view The input to this generating system is a text file that contains information about tables and queries in a user friendly format The output of this generating system is a web CGI program which provides an Internet interface for implementing the database queries defined in the input text file The friendly interface of the target web based program and the easy usage of the generating system allow even non professional DBAs to administer the database Hence our overall system frees DBAs from unnecessary and repetitious work However even though the users can be non professionals they still need to call in a database administrator or consultant in certain circumstances Here are some items that the users can manage themselves once the database is up and running Tau99 Adding new tables or new fields to an existing database Adding new records to the database Running existing queries with different inputs Creating queries similar to existing ones Maintenance items that may require the assistance of a DBA or a consultant include 29 Creating new kinds of queries Upgrading the database software Setting up the e
17. For example if we choose the query find all of the work orders by the name and room number in Figure 4 the variable value entry page will appear as in Figure 7 pa ha a f a En ZS fttp cachert ca pau EE vi Figure 7 Variable value entry page for guery Tf the user inputs gingru for name and W25 for roomno in Figure 7 the query result page will be Figure 8 16 p f richert car pat edu cgi bin vekls outputd cgi Your requirement es saccessfelty mplemented 5 EE DATE AEN DATE PANT DATE EE EE E I je ELE ER BE GEGEE NEE ES GET IE am N ad El me AL NN NEE We eie ie SIE NONNE MEN bus Figure 9 Error message page Tf there is an incorrect value input for some table attribute or dynamic variable a page for error messages will appear The user should go back to the previous page check and fix the errors and click the appropriate operation again For example if the user inputs a long string for the roomno as in Figure 7 which is larger than the size of attribute room the error message page will appear as in Figure 9 This completes the system description from the user perspective The next part will describe the system implementation from the programmer s view Implementation ideas and techniques will be described in detail 17 2 22 System implementation from the programmer s view Our overall system is like a compiler Reading the text file is similar to the scanning
18. The Pennsylvania State University The Graduate School Capital College A System to Generate a Simple and Reusable Web enabled Solution For Database Oueries A Master s paper in Computer Science By Oingru Zhang 2000 Qingru Zhang Submitted in Partial Fulfillment of the Requirements for the Degree of Master of Science February 2000 Abstract Database administrators have to issue many repetitive and tedious database gueries every day They want software that can reduce this redundant work Furthermore this software should be portable to as many database systems as possible and should be usable by non professional users This paper describes a system that can generate such software automatically The users of this system define their queries in a text file using some simple formats and then this system reads this text file and generates the corresponding web enabled program to implement these queries on the Internet In this sense this system is a compiler The generating system and target program are developed in Perl The target web enabled program can easily be used with a variety of database systems Due to the interoperability and reusability of Internet components it is also reusable and easy to use Table of contents ADS ii Ee is See Re ee De Ee Re GR ee ie Table of contents ass ef Si ER sii de ER do RE BEE OE OE RE N EE T Intro UC AN ER EE EE EE EEN 2 System Description and Implementation E R
19. Wall L Christiansen T Schwartz RL Potter S Programming Perl Sebastopol Wei99 CA O Reilly amp Associates 1996 645p Weiss A Who s Afraid of Perl 26 Apr 1999 Online posting 20 Mar 2000 lt http wdvl com Authoring Languages Perl Perlforthe Web afraid html gt 32 FEAE HE AE AE RR REKE AE ER AE FE KERR E RR RR RR RR RR RR ER ER RR RR AE FE AE RR RR RR RR RR RR RR RR AE FE FE RE RE RE RE E E H EEEH A system to generate a simple and reusable web enabled solution for database queries Qingru Zhang FEAE HE aE AE AE AE aE AE FE FE AE FE FE AE FE KERR RR RR RR R RR RR RR E AE RR RR RR RR AE RR RR RR RR RR RR RR RR AE FE FE RE RE RE RE E E H EEEH se 4 HE HE bt MH MH MH HE SE MH HE afs psu edu rs_aix42 usr local bin perl Sinput Soutputl Soutput2 ARGV if Sinput eq die First paramerter is empty reading file name is necessary if Soutputl eq die Second paramenter is empty writing file name is necessary if Soutput2 eq die Third paramenter is empty writing file name is necessary if e outputl die There is already an Soutputl file exist Continue to use this file name will overwrite the old name Please give another name if e Soutput2 die There is already an Soutput2 file exist Continue to use this file name will overwrite the old name Please give another name open INPUT Sinput die c
20. ame auth An print OUT if defined A dbh An print OUT Serrorstt n print OUT Serrors_list SDBI errstr An print OUT amp print_top2 An print OUT amp print_errors An print OUT else n print OUT amp print_forml n print OUT S dbh gt disconnect An print OUT n print OUT elsif Sarray option eq insert table value n print OUT amp print_top2 n print OUT amp print_form2 An print OUT n print OUT elsif Sarray option eq implement selected query An print OUT Squery_key Sarray QUERY_NAME An print OUT Squery_name queryname Squery_key An print OUT Squery Squery_name An print OUT if defined query n print OUT Serrors Serrors_list amp query_without_variable n print OUT if Serrors gt 0 An print OUT NA Ms print OUT amp print_top2 n print OUT amp print_errors An print OUT Ae Wis print QUT JP else n print OUT amp print thankyou An print OUT amp print gueryresult An print OUT Jn print OUT else An print OUT amp print form3 An print OUT Liz Te print OUT n print OUT elsif Sarray option eg done in H print OUT Serrors Serrors_list amp save_data n print OUT if Serrors gt 0 An H print OUT FM Te print OUT amp print top2 An print OUT
21. an t open Sinput for reading open OUT gt Soutputl die can t create Soutputl SI print OUT afs psu edu rs_aix42 usr local bin perl n Smode 0755 chmod mode Soutputl while lt INPUT gt chomp s cM cJd 33 dbs Shostname user Suseraccount _ dbna Sdbname 1 TE if Shashtable Shashtable Stablename Seachtable Seachtable Seachtable Seachtablet Seachtablet Seachtablet Stablein Stablein Stablein recognize if _ Seac Seac Seac Seac Seac Seac Seac Seac Seac Seac table i tab at att table table table table table table tablet tablet tablet tablet Seachtablet Seachtablet Stablein Stablein Stablein Stablein if 4 D eq St else tablein ENV DBI_NAM erver s St recognize hostname 1 st S recognize user ID 1 meXs XS recongize database name recognize table definition part leXs 1S 1 recognize table name ype ype ype Xin tribute name ri st S s St s AS s St s NS 1 2 A ype ype 2 ype ype 3 ype r ype ENNA Ms 1c 2 We Ww F Ww r uc 3 yes ablein NOT NULL Ww if 5 eq yes SStablename 2 Stablein n
22. and parsing phases in the compilation process Then the generating system generates the target CGI program to implement the functions defined in the text file The target CGI program is equivalent to the target code in the compiler The processing of our overall system includes three main phases read the text file save the information to an organized data structure and output the web based target CGI program Therefore a language that is able to implement these phases efficiently is very important for our implementation After careful comparison and consideration we finally chose Perl Sher96 as our programming language because it provides all of the powerful functions our implementation needs Its key features Cher98 include m Powerful text manipulation regular expressions formatted output E Powerful file management W Convenient data type management list and hash sorting searching not strongly typed optional function prototypes M Rapid development and prototyping E Compact code for complex operations W Easy CGI scripting 18 W Function references Our implementation mainly uses Perl s powerful text manipulation features to retrieve information from the text file organize and save information into hash tables and generate the web based target CGI program using Perl s CGI module After our implementation we feel that Perl is exactly the language we wanted Since Perl s advantages play an important role in our entir
23. append each attribute value to the information for the whole table the code is eachtable attribute This concatenation implementation is very fast This is an advantage over the TCL language In TCL strings are C strings Getting the length or adding to the end requires a search through the entire string for a null Sher96 Since most of the data structures in Perl including hash tables that are frequently used in our implementation are implemented as strings this leads to some costly handling if using the TCL language 21 Another advantage of Perl is its strong support for arrays and hash tables Perl uses the to refer to the array as a whole and for the hash table The size of an array can be easily accessed by row if the array name is row Moreover a hash table has advantages over a regular array A regular array uses whole numbers for indices but the indices of a hash are always strings Its values may be any arbitrary scalar values including references Using references as values we can create hashes that hold not merely strings or numbers but also arrays or objects or rather references to arrays hashes or objects The flexibility of the hash table reduces many complex algorithms to simple variable accesses Chris97 Hash tables play a core role in our implementation After reading the text file we need to keep track of each table with its attribute names This information will be used in the target CGI program
24. cording to a specific format This format can be understood very easily It hides most of the programming work so that the user is not required to be a programmer Currently similar software is on the market Microsoft FrontPage Tau99 Basically FrontPage provides friendly user wizards and templates to enable the users to create their web pages and applications without knowing the back end programming language It uses an ODBC Open Database Connectivity Tau99 driver to connect to different database systems The basic web server for FrontPage is the Microsoft Personal Web Server MSPWS If the users want to create sophisticated web applications they must use another web server other than MSPWS So the FrontPage server extensions need to be installed to operate with different web servers Tau99 FrontPage is very good software for creating web applications It runs under the Windows operating system including Windows 95 98 Windows 2000 and Windows NT but can not run under UNIX operating systems Our overall system can run under both the Windows and Unix operating systems 1f Perl and the DBI driver are installed Thus our system is more flexible than FrontPage in terms of the running environment Moreover the FrontPage server extensions need to be installed on the users Personal Web Server MSPWS and the web server the users are using Sometimes the users must negotiate with the system administrators to install the extra packages Such a
25. d of matching engine is not guaranteed to return the longest overall match just the longest leftmost match In this sense Perl s greed is left to right directed not globally greedy Another kind of matching engine is the DFA a deterministic finite state automaton Choosing NFAs or DFAs pattern matching engine depends mainly on the answers to two questions do the expressions use backreferences and what needs to be returned yes no range of whole match ranges of subexpressions If the language does not need to support backreferences and only needs to return a yes no answer or the range of the whole match then it should use DFAs as in awk egrep and lex A DFA is faster and simpler If the language needs to support backreferences and needs to know which parts of the string were matched by which parts of the pattern then it should use NFAs as in ed regex and Perl An NFA is slow because it requires potentially exponential run times but significant performance can be gained when we want to exploit how the particular NFA pattern part is implemented Chris97 Frie97 The last and most powerful feature of Perl s regular expressions is backtracking For a pattern to match the entire regular expression must match not just part of it Thus if the beginning of a pattern containing a quantifier succeeds in a way that causes later parts in the pattern to fail the matching engine backs up and tries to find another match for the beginning part w
26. date needed DATE NO ULL phone CHAR 20 email CHAR 20 submit_date DATE NOT NULL equip_order_no CHAR 10 NOT NULL serial no CHAR 10 NOT NULL model no CHAR 10 OT NULL PRIMARY KEY department contact_name equipment Figure 2 Create table statements 12 To send these statements to the DB2 system issue the command db2 f outputl Then the tables work order and eguip name are created in the database gxz1 10 This paper describes the database system based on the DB2 database system because our target CGI program is using the Perl DBI driver for DB2 However it can be extended to other database systems such as Oracle by installing the appropriate DBI driver and modifying some environment variables in the httpd conf file of the web server The reader can consult the DBA or the user s manual of the database system for details of this configuration 3 Run the target CGI program on the Internet A web browser such as Netscape Navigator or Internet Explorer is needed to run the target CGI program Step 1 The user should consult with the system administrator to establish which URL should be used to run the generated system For example if the system administrator places the CGI program in the web server s directory cgi bin wahls then we should access the target CGI file outputl cgi from the URL richert cac psu edu cgi bin wahls outputl cgi where richert cac psu edu is the hostname f
27. e n printY lt html gt 1 n H print lt head gt NA Ns print lt title gt Query result lt title gt An hi print lt head gt cn Es 7 print lt body bgcolor faafaf gt An print lt center gt An print lt table border 1 gt 1 An n ER Et erte W for s 0 s lt sth gt NUM_OF_FIELDS M s An n print lt TD gt lt B gt lt FONT FACE Arial Helvetica gt lt FONT sth gt NAME gt X s lt FONT gt lt B gt lt TD gt 1 An No print AMUSE ZTRIAT NE while row sth gt fetchrow n BERE VESEREN TEN AN for Si s0 Si sNS sth NUM OF FIELDS Si An orint lt TD gt lt FONT FACE Arial Helvetica gt lt FONT SIZE 1 gt lt FONT gt lt TD gt n for n ihe BEILEED NOT while n T print lt table gt n print lt center gt n print lt body gt n tus print bal y yn WM Ssth gt finish n Sdbh gt disconnect An n Mae sub print_query_withvariable_resultin n m D Sdbh DBI gt connect Sdatabase Susername Sauth An NX sth X dbh gt prepare 1 stmt An for id 1 id lt vararray Sid An n K m var_val vararray id An D X values Sarray var_val An print OUT X sth gt bind_param A id Svalues An
28. e Da aro Seh eum Pet Sa Swe Sa i af Boo hina rhe 4 Location re Frichert cac psu edo cgi bin wahLs outputl ogi tr Wrot s Reiated i Y Workers kel Y Correci n Y Prum SrenUpdst Y MURE I EE kr Oe at nr sipste Liege naire D Dates t a Dote Oos Tate peites PERRERA Figure 5 Table value input page After finishing inputting the values the user should click done If the value for each attribute is of the correct type and the new record does not violate a primary key constraint these values will be successfully inserted into the selected table as a record Otherwise a page with the corresponding error message will appear Step 5 To run one of the queries in the query list the user should select the query name and click implement selected query in the selection page as in Figure 4 If the selected query does 15 not need any user input then the guery result will display immediately For example if we choose the guery find all of the work orders in the guery list in Figure 4 the next page will list its result as in Figure 6 e e N IK i Spe Fetz Arbeit PENT DATE A 50 25 i pasis ERG aaa FEE MES ag Eg mm EEN EI GEE EIE kd Pas O EIE EN Tag Rss WW Ma od DEER IE EI IE II TE N EED EA EE Figure 6 Query result without dynamic variable value input Tf the selected query needs some variable values to be input first then the variable value entry page will appear
29. e system and retrieve the query result from the database In our target program the DBI driver used is for DB2 That is we installed the Perl DBI driver on our web server and issued code in the generated program such as Use DBI Use DBD DB2 Constants Use DBD DB2 There are also other DBI drivers for Oracle MSQL Access and so on Our target program could be portable to different database systems easily by installing the appropriate DBI driver and changing the few codes as above Ano99 The remaining use of DBI is universal for example dbh DBI gt connect database username password connect to database sth dbh gt prepare stmt send the query into database sth gt execute execute the query sth gt fetchrow retrieve the query result dbh disconnect disconnect from database Thus our target program is portable due to the portability of the Perl DBI driver In terms of the interface of our target CGI program in the web browser HTML forms are necessary because the web browser needs to collect the user s requests and data and send them to the web server Normally a browser can request a document in a number of ways called methods and form values can be encoded in both GET and POST methods With 26 the GET method values are encoded in the URL That means it can be conveniently bookmarked for canned reguests For example if we use the GET method for the login
30. e system we will introduce our implementation idea and techniques by analyzing the features of Perl in the following sections Our first implementation part reading and retrieving information from the text file is based on the regular expressions of PERL Regular expressions are a powerful tool for manipulating text The core of regular expressions is pattern matching Most modern programming languages offer primitive pattern matching tools through an extra library whereas Perl s patterns are integrated directly into the language core As most of the Unix shell interpreters and TCL Ous94 language do patterns of Perl provide powerful algorithm accessibility which is normally only available to computer science scholars The common issues of pattern matching are greed eagerness and backtracking and how these issues interact with each other Chris97 Greed is the principle that if a quantifier like can match a varying number of times it will prefer to match as long a substring as it can Eagerness is the notion that the leftmost match wins The engine is very eager to return a match as quickly as possible For example the pattern select i AO find the multiple lines 19 between the keyword select and for finding the query statement in Figure 1 in Perl returns one query statement each time not all seven query statements The reason is that Perl uses a traditional NFA a non deterministic finite state automaton This kin
31. ey Sarray TABLE_NAME n T Stable_name Stables Stable_key n T Stable Stable_name An T foreach Sfield sort keys Stable An T Sv_name Stable field n 1 print lt IRS n D T print lt TD gt lt FONT FACE Arial Helvetica gt lt FONT SIZE 1 gt TY S ield lt FONT gt lt TD gt n T print lt TD gt lt INPUT TYPE TEXT NAME Sv_name Sarray Sv_name gt n T printy lt TD gt kt Ty T BELRENGSLTRSNIS KO iy E n Wi T nprint lt lt EOM n T lt TR gt n T lt TD COLSPAN 2 align right gt lt hr noshade gt lt input type submit option value done gt n T lt TD gt n T lt TR gt n T lt TABLE gt n T lt CENTER gt n T lt BODY gt n T lt HTML gt n T lt FORM gt n T nEOM n T no T sub print_form3 n T An e 42 prin prin prin prin o prin Ww r prin prin prin prin prin prin prin prin prin prin prin prin prin SIZE prin prin prin prin prin prin prin prin prin name prin prin prin prin prin prin prin prin prin prin prin prin TEE ET O TE SA EE EE vr ET LF Ier cot ct ct co ct CF CF ch EE cht ct O OOOO Ce Bes EE LET Ce ee CF O OOOO O O O OOOOO OS AE oo U U
32. for attri for table if query i recognize query definition part if S s S recognize description sentence Squerydeso _ queryde split Nst Squerydeso Squerydess join queryde Squerydes querydess if S amp s S recognize variable definition sentence Sbindingvar _ if select i recognize query statement Sstmts Sstmts n while statment split stmts s 1 foreach Sstatment statment chmop s cM cd if statment S Squerystmt AS querystmt Squerystmt s Squerystmt Squerystmt Sstatment Squerystmt Squerystmt Squerystmt n Ss print OUT querystmt each query statement foreach bindingvars split amp Sbindingvar Sindex 0 foreach bindingvars bindingvars chmop s cM cJ if bindingvars S Squeryvars gueryname 35 one Squeryvars Sindex Squeryvars qw An varfields split Sbindingvars Svarresult join n varfields Squeryvars varresult Squeryvars n n print OUT queryvars print dynamic ariable hash table for each query if there exist Sindex foreach hashquerys split Squerydes Squerys Squerys qw Squerys n m 1 foreach Shashquerys hashquerys
33. he first yes no is to indicate if this attribute can be null or not If the attribute cannot be null say yes otherwise say no The second yes no is to show if this attribute is a primary key or not For example there are two tables defined in Figure 1 The first table name is work order and the first attribute is name with the description name customer name The attribute type is char 10 It cannot be null and is a primary key attribute The customer name here will be used to give the description for inputting the value for attribute name from a web browser later See Figure 5 for an example The user should notice that each description name and attribute name must be one word or a single string If multiple words are used the user must connect them to form a single string such as customer_name The table definition ends with the symbol If the users want to define another table they simply need to repeat the above definition again In Figure 1 the second table is equip_name with 11 attributes 10 For the guery description the whole syntax is guery guery description sentence amp dynamic variable description line Actual guery statement The format starts with the keyword query Then the users give the description sentence for this query that starts with the symbol on a new line This description sentence will be used to index each query on a web page as in Figure 4 The next line is the variable description If thi
34. hich is backtracking This means that the engine will try different possibilities systematically investigating alternatives until it finds one match that works However some pattern matching implementations keep backtracking in case other submatches make the 20 overall match longer Perls matcher does not do that as long as one possibility works it stops and uses that unless something later in the pattern fails Chris97 Perl s eagerness and just one possible result of backtracking are very helpful in our implementation For the same example as above there are seven queries defined in Figure 1 The pattern to match each query is select i A If the eagerness takes a back seat to greed or the pattern matching implementations keeps backtracking to make the overall match as long as possible then we always get all of the seven query statements That will be hard for us to fetch each query s information in detail Perl s eagerness and backtracking features help with implementing our system easily and efficiently Another advantage of Perl that is used in our implementation frequently is its easy string handling In Perl strings carry a length so getting the length is efficient and adding to the end of the string does not require reading the whole string Sher96 The syntax is simple as well For example suppose the string holding all of the table information is eachtable and the string holding each attribute value is attribute To
35. information and returns some results to the web server which then passes the results back to the user s browser CGI simply defines the means by which the user s data passes from the web server to the processing system and back The system that processes the user s data can be a database system or a program written in any computer language as long as these system and languages can run on the server and communicate with the server via CGI Perl can speak CGI fluently and happens to be well suited to manipulating data Leveraged on the flexibility of Perl almost any conceivable type of processing can be done Thus when the Perl program receives the user s data it could follow any number of paths from retrieving information from a database to constructing and delivering whole new web pages on the fly Wei99 In our target CGI program the processing is done by a DB2 database system since the target system s purpose is to implement the database queries requested by the users from the web browser and to send the query results back to the web browser The CGI interaction written in Perl is the bridge between the web server and the DB2 system Whenever the web server receives the data from the web browser it sends the data to DB2 and fetches the query result via our target CGI program What our target CGI program really does is to use Perl s powerful database interface driver DBI driver to connect to and send the query to the databas
36. it AtoprioniiAWn e OUT value login gt n OUT lt TD gt An OUT lt TR gt An OUT lt TABLE gt An OUT lt CENTER gt n OUT lt BODY gt An OUT lt HTML gt An OUT lt FORM gt n n OUT EOM n OUT d n OUT sub print_forml n OUT An LE OUT Anprint lt lt EOM n OUT lt FORM METHOD post ACTION Sscript gt n OUT lt INPUT TYPE HIDDEN NAME usernam OUT lt INPUT TYPE HIDDEN NAME authname value Sarray AUTH gt n OUT lt html gt n OUT lt head gt n OUT lt title gt selection page lt title gt n OUT lt head gt n OUT lt body bgcolor faafaf gt n OUT lt hr noshade gt n OUT lt TABLE BORDER 1 gt n OUT lt TR gt n OUT lt TD COLSPAN 2 WIDTH 30 gt lt B gt lt FONT FAC e m OUT choose the table name you want to insert value to T gt lt B gt lt TD gt n OUT lt TD WIDTH 70 gt lt SELECT NAME TABLE NAME gt n OUT lt OPTION VALUE gt select your table n n OUT nEOM n OUT foreach Stable sort keys Stables An OUT n OUT print lt OPTION VALUE S table gt Suseraccount Stables table n n OUT n ns OUT Anprint lt lt EOM n prin prin prin prin prin prin prin valu prin prin prin prin prin prin prin Plea prin Ww r prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin
37. meet all of these requirements First the generating system generates a web CGI script to run queries on the Internet Because of the interoperability and reusability of the Internet components this CGI solution program enables the repeated works to be defined once and then reused on the Internet It also provides a friendly interface for inserting the values into the tables Secondly the generating system and the target CGI program use Perl Practical Extraction and Report Language Wall96 Sch97 as the programming language Perl can run on virtually every popular operating system It also has a powerful DBI Database Interface driver module ANO99 to connect to almost all kinds of database systems Therefore our generating system can run under most Unix and Windows environments and the target program is portable to different database systems by installing the appropriate DBI driver The database system used in our target program is DB2 Cham96 and the Perl DBI driver used in the example code is for DB2 However the target program can use the Oracle database system by installing the DBI driver for Oracle or use the mSQL database system by installing the DBI driver for mSQL and so on Little code modification is needed to use a different DBI driver The remaining uses of the DBI driver are universal The details of this can be found on page 26 Thirdly our generating system allows the users to define their tables and queries in a text file ac
38. mers partners and suppliers without the barriers of geography and 30 time differences The back end database system can store large amounts of data about products and users feedback Therefore for the IT professional the main concern here is the available technigues to support this strategy and methods to implement it Our overall system introduced in this paper provides an example implementation of this kind of strategy The database in our system is DB2 The web compatible programming language used is Perl and its DBI driver for DB2 There are a number of other implementation techniques that can be chosen For example the database can be Oracle mSQL or Access The programming language can be Java with a JDBC database driver Visual Basic with an ODEC driver and so on The user can also use the Microsoft FrontPage package to generate a simple web enabled solution without programming language knowledge Compare to FrontPage our implementation has its own advantages It is portable to almost all database systems by installing the appropriate Perl DBI driver Perl and its DBI driver are widely used in current E commerce design and web application development In short this paper describes a specific web enabled solution generating system with its usage and implementation techniques in detail It gives useful information for web enablement an important issue in the current business and IT market and can benefit web developers and pr
39. mt n vararray An foreach ch each n EAn if ch VANA AA S 4 Ag n NAT push vararray 1 n n n V stmt s S F g An Susername Sarray username3 n Sauth Sarray authname3 n Sdbh DBI gt connect A database Susername Ssth Sdbh gt prepare stmt An if Sdbh gt errstr An NA Ms Serrorstt n NSerrors list 1 dbh gt errstr An E e Sauth for Sid 1 Sid lt S vararray Sid An tn X var_val vararray id n Svalues Sarray var_val n sth gt bind_param S id A values An Ansi Ssth gt execute n if MSsth gt errstr An n Serrorst n Serrors_list sth gt errstr An n Sauth Ww I n prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin K prin prin prin prin prin prin ASt prin ASt prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin EF EE ct ct O TEEF SE EL KEE Eie KE ET CC KE ET KEE KS ET EE VET AE ET Fs ETER LT ST FE CC ME A EF ER Gr EI ER CT FE EE ELF EI O OOOOOOOOOOOOOO O O O OOOOOOOOOOOOOOOOOOOOO Sauth An ALEA al We UT n Ths UT Ssth gt finish n UT
40. n print OUT An print OUT print lt lt _ERRORS_ n print OUT lt Center gt lt table width 500 border 0 bgcolor FFFFCC gt lt tr gt lt td gt n print OUT lt font face ARIAL color Red siz 1 gt There were som your submission print OUT please take a look at the information below and make necessary corrections n print OUT lt ol gt Serrors_list lt ol gt n print OUT lt ol gt sql lt ol gt n print OUT lt td gt lt tr gt lt table gt lt center gt n print OUT _ERRORS_ n print OUT ENE MS print OUT sub print_top2 n print OUT hp print OUT nprint lt lt END DOC An n Helvetica gt lt FONT Ww r rrors in the 47 print OUT lt html gt n print OUT lt head gt n print OUT lt title gt Table value insert page lt title gt n print OUT lt head gt n print OUT lt html gt n print OUT n_END_DOC_ n print QUE EVER As print OUT sub print_thankyou n print OUT An print OUT nprint lt lt EOM An print OUT lt body bgcolor faafaf gt n print OUT KIRAN print OUT lt TD COLAPAN 2 gt lt B gt lt FONT FACE Arial Helvetica gt Your requirement is print OUT successfully implemented lt FONT gt lt B gt lt TD gt n print OUT H lt TD gt n print OUT H lt TR gt n print OUT lt BODY gt
41. n extra installation is not needed in our system Overall FrontPage focuses on providing a general and comprehensive solution for Web design and it is Windows based Our overall system is a specific solution to the database queries and it is both Unix and Windows based The next section will describe the use of our system in detail Some implementation technigues will also discussed 2 System Description and Implementation lt 2 1 gt System Description from the user view The purpose of our generating system is to reduce the redundant work for DBAs The use of the generating system is quite simple There are three main tasks define the database and the queries in the text file run the generating system and then run the target CGI program on a web server to implement the queries The most difficult part is the text file definition but the formats are still easy to follow The instruction for these three tasks follows 1 Define the database and the queries in the text file From the user s perspective the text file ought to be as simple as possible so the user can master it quickly There are five parts in the text file the database server name the database name the name of the user who will create the tables the table structure description and the query description The database server and database names are necessary so our target CGI solution program will know which database server and database to connect to at run time
42. nvironment variables Making regular backups Upgrading the hardware of the machine on which the database software runs As mentioned before the advantages of our overall system are simplicity reusability portability and web enablement Among them web enablement is the most important The simplicity and reusability are gained from the web enablement due to the intuitive and easy usage of the web browsers and the reusability of the Internet components Currently web enablement is an important trend in the business community A large percentage of Fortune 1000 companies are already being or are converting to Web Enabled Enterprises WEE to improve their flows of electronic transactions via web based solutions From the business perspective effective communications are always the underlying foundation for success in business Successful organizations must communicate information on many different levels such as with co workers partners suppliers and most importantly with customers Furthermore the availability of information is important The WEE wants to give information about its products and services and get as much feedback from users as possible Coen98 From the IT professional s view a web enabled solution integrated with a back end database system is the right strategy for these business objectives Web enabled solutions can provide a platform that enables an organization to work more closely and efficiently with its custo
43. ogrammers 31 References ANO99 anonymous DBI for Perl Course CSCI E 13 Harvard DCE Fall 99 Online Posting 12 Dec 1999 lt lab dce harvard edu extension csciel3 library dbi DBI htm gt Cham96 Chamberlin DD Using the new DB2 San Francisco Calif Morgan Kaufmann Cher98 Publishers 1996 682 p Chervitz SA Perl Tradeoffs 23 Dec 1998 Online posting 6 Mar 2000 lt genome www stanford edu PerlOOP bioPerl oib97 Perl_tradeoffs html gt Chris97 Christiansen T Nathan T Perl cookbook Sebastopol CA O Reilly 1998 I 757p Coen98 Coen R Hoogenboom MC Web enabled applications programmed on the net Eug96 Mor98 Ous94 Sch97 Sher96 Tau99 how to become a web enabled enterprise New York McGraw Hill 1998 574 p Eugnene EK CGI developer s guide Indianapolis Ind Sams net 1996 497 p Moran M DBD DB2 0 68 tar gz 1998 Online Posting 6 June 1999 lt http theory uwinnipeg ca CPAN data DB2 DB2 html gt Ousterhout J K TCL and the TK toolkit Reading MA Addision Wesley 1994 458 p Schwartz RL Christiansen T Learning Perl Sebastopol CA O Reilly amp Associates 1997 269 p Sherman A Aaron Sherman s Tcl vs Perl Comparison 1996 Online posting 6 June 1999 lt http language Perl com versus asherman on tcl html gt Tauber DA Kienan B Holzschlag ME Mastering Mocrosoft Frontpage 2000 San Francisco SYBEX 1999 677 p Wall96
44. or the web server Step 2 The first page of the target CGI program will be the user name and password login page as in Figure 3 The user s user name and password for the account on the database system such as DB2 in our current system should be input See the example in Figure 3 13 Welcome to Ge Web query System Figure 3 Login page Step 3 Tf the login succeeds the selection page for table and query names will appear The user should then select the table to insert into or the query to run Figure 4 is the corresponding selection page for the text file in Figure 1 zk rsch Lu aat eng erter aat 10 egg Am EG egent emm you want to implement dee por ar Lt find d wt the work seders br che saree aime aber find al the iveerration ix the att nacre table atenent steed guey A a ste vu cry ask dee thd the msme same in wed order by sunbadred Ley fend d ol the vak seders Had sarsa end beige date by oan rearber in the work ordas tabla Mad the pame el the pars an whe Lo the contact de the moet items sl ep ta e specifi Lepet Figure 4 Table and query selection page 14 Step 4 To insert data into a table the user should select the table name and click insert table value Then the form for collecting the attribute values of that table will appear The example for the table work_order is in Figure 5 d ie oia Nior e ati fa ES 3 gt Had a a z N Bak o
45. page the URL will be where the user name and password are exposed This situation should be avoided With the POST method values are encoded in a different part of the HTTP request that the browser sends to the server If the form values in the example URL above were sent with a POST request the user server and CGI script all see the URL http richert cac psu edu cgi bin wahls outputl cgi Thus forms that update information on the server such as mailing in feedback or querying and updating a database should use POST Chris97 667 Another reason for this 1s that client browsers and intervening proxies are free to cache and refresh the results of GET requests but they may not cache POST requests GET is only safe for short read only requests whereas POST is safe for forms of any size as well as for updates and feedback responses Therefore our target system uses the POST method Another thing to mention is that the CGI program is called each time the web server needs a dynamic document target The CGI program does not run continuously with the browser calling different parts of the program Each request for a partial URL corresponding to the CGI program starts a new copy The CGI program generates a page for that request then quits Therefore the values of variables do not transfer between different pages automatically If we want to keep a variable state on a different page we must save it as a HIDDEN field type in the ne
46. prin valu prin prin prin prin prin prin prin EF GE seh Et CT CT rr Or rr ct ct CT MN ct ct 0 00 COLO CEA GE AN EL ch EE EP EI EE Et cr cf o ct ct ct O OOO O O OO GOD O OG OO O OV OVO OD O oo U U U T lt SELECT gt n Ts T lt TD gt n T lt TRADn e T lt TABLE gt An T lt hr noshade gt n T lt TR gt n T lt TD COLSPAN 2 align left gt lt input type submit name option insert table value gt n TM TOER E T lt TR gt n T lt hr noshade gt n T lt hr noshade gt n T lt TABLE BORDER 1 gt n T lt TR gt n T lt TD COLSPAN 2 WIDTH 30 gt lt B gt lt FONT FACE Arial Helvetica gt T choose the query name you want to implement lt FONT gt lt B gt lt TD gt n T lt TD WIDTH 70 gt lt SELECT NAME QUERY_NAME gt n T lt OPTION VALUE gt select your query n T nEOM n T foreach Squery sort keys Squerys An T n T Squerys Squery s A g n T print lt OPTION VALUE Squery gt Squerys Squery n T mn T Anprint lt lt EOM n T lt SELECT gt n To P lt TDS N A T gt T lt TR gt n T lt TABLE gt An T lt hr noshade gt n T Y lt IB gt AN Y T lt TD COLSPAN 2 align center gt lt input type submi
47. s query does not need a dynamic variable value then only the symbol amp is given here as the second and fifth queries in Figure 1 If this query needs one or more dynamic variable values then the pair of the variable description name and actual name follows the symbol amp The syntax is 8 description_name actual_name Each description name and actual name must be one word or a single string as with the attribute definition However the description name for the variable does not have to match the description name for the corresponding attribute of the same table The third fourth and sixth queries in Figure 1 are such queries If the query has more than one variable then additional pairs of description name and actual names are used Each pair is divided by the symbol The syntax is amp description_name actual name description name actual name such as amp description namel actual namel description name actual name The first guery in Figure 1 is such a guery The last definition in the query part is the actual query statement This is given as a standard SQL Structured Query Language Cham96 statement except that the names of variables can be used If there is a location where a dynamic variable value is needed the user should 11 use the corresponding variable name from the variable description sentence here using the syntax variablename There is no space between and variablename The actual query is no
48. ss Se lt 2 1 gt System Description from the user ViCW ENEE 7 lt 2 2 gt System implementation from the programmer s view 18 3 Conclusion Se SE 29 RELE A EL EA ER RE HE 32 Table of Figures Figure 1 Example textiles ici o ira 9 Figure 2 Cr ate NESARA AAA ON 12 Figure 3 Login page 14 Figure 4 Table and query selection page Figure 5 Table value input page esse sesse sees ek sk se se gek se ee ee coran Ss LG we 15 Figure 6 Query result without dynamic variable value input E 16 Figure 7 Variable value entry page fOr ouerg esse sees se see se se Ge ee Ge Gee Ge Gee Ge Gee Ge Gee Ge Gee Ge Gee Ge Gee Ge Gee Ge Gee be Gee Figure 8 Query result with dynamic variable value needed ae ae S Figtire 9 Error message D EE ENEE EE Figure 10 Diagram for our overall system ENEE 28 1 Introduction Electronic commerce is rapidly growing in the current information technology IT field It has been estimated that Internet based business will be the main trade method in the world in the near future Coen98 The reason is that Internet components have several advantages including integration interoperability and reusability and the most important worldwide accessibility E commerce is such a fast growing market that many computer companies are engaging in providing all kinds of the web enabled solutions for industries The typical products are IBM s customer relationship management CRM solutions that
49. t completed so far The value for each variable will be obtained from the web browser and put into the appropriate variablename location in the query during the running of our target CGI program The query statement will actually be run at that time 2 Run the generating system After the text file is ready issue the command our system name text_filename output script name create table statement name For example suppose our system s name is final cgi it can be renamed to any name the text file name is input the output solution name is output 1 cgi and the file containing the creating table statement is outputl then the command should be final cgi input outputl cgi output The output file contains the statements for creating tables if there is any table structure information defined in the input file For instance the output corresponding to the text file in Figure 1 will be Connect to gxz110 CREATE TABLE work order name CHAR 10 NOT NULL address CHAR 20 NOT NULL room CHAR 10 NOT NULL phone CHAR 15 budget CHAR 5 NOT NULL problem CHAR 50 submit date DATE NOT NULL log date DATE NOT NULL print date DATE complete date DATE NOT NULL work order no INTEGER NOT NULL tech name CHAR 20 OT NULL PRIMARY KEY name address CREATE TABLE equip_name department CHAR 20 NOT NULL contact name CHAR 20 NOT NULL equipment CHAR 20 NOT NULL brand CHAR 20 NOT NULL
50. t name option implement selected query gt n T lt TD gt NN Ve T he RO Ts T lt BODY gt NN T lt HTML gt n T lt FORM gt n T nEOM n T in ws 41 prin prin prin prin prin prin We E Prin We L prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin prin VALUI prin prin prin prin prin prin name prin prin prin prin prin prin prin prin prin prin prin ER Gr e EE bh C Gh EE Gk Gh O ct choc ct ct Lei TE EE EE AE Et DI ET 0000000 Il cr cr cr cr cr cT pa ETA EE AT E ct ct ct ct O ct ct O O OOOO O OO O OOOO O OOOO oo U Cc U T sub print_form2 n T in T nprint lt lt EOM n T lt FORM METHOD post ACTION Sscript gt n ia T lt INPUT TYPE HIDDEN NAME edit value Sarray TABLE_NAME gt n VE T lt INPUT TYPE HIDDEN NAME username2 value Sarray username gt n T lt INPUT TYPE HIDDEN NAME authname2 value Sarray authname gt n T lt html gt n T lt head gt n T lt title gt Table value insert page lt title gt n T lt head gt n T lt body bgcolor faafaf gt n T lt CENTER gt n T lt TABLE BORDER 0 WIDTH 500 gt n T nEOM n T Stable_k
51. ttribute name and type foreacha print out fixed CGI code into output file print OUT Sscript print OUT Soutputl print OUT N s n print OUT Sdatabase DBI DB2 print OUT Sdbname print OUT DNH kn print OUT use DBI n print OUT use DBD DB2 Constants n print OUT use DBD DB2 n print OUT require cgi lib pl An print OUT amp ReadParse array An print OUT Serrors 0 An print OUT N errors list n print OUT 1 flush output immediately n print OUT print Content type text html n n n print OUT if Sarray option eg An print OUT amp login An print OUT n print OUT elsif Sarray option eg login An print OUT Susername Sarray USER n 37 print OUT Sauth Sarray AUTH n HH HHH HH HH EE EH HH HE EH HH HEE EH HH HE EH HH HEE EH HH EE EH HH HEE EH HH EE EH HH HEE EH HH HF HH HH HH HHH HEE EH HH HF for future remote hostname connection the syntax should be Sattr hostname gt Shostname D I Sattr Sattr Sdbh DBI gt connect database username Sauth Sdrivername such as DB2 here attr aE aE aE aE aE aE AE a aE FE AE EE EE EE HEE HEE HEE EEE aE FR RR EEE RR RR RR RR RR RR RR RR RR RE RE RE EE E H RR RR RR RR RR RR RR RR RR H E 4 4 4 4 4 print OUT S dbh DBI gt connect database usern
52. w form s HTML code Eug96 Chris97 This embeds the variable state in the new form In our target system to insert values into tables or 27 implement gueries the different pages such as Figure 5 and Figure 7 need to connect to and disconnect from the DB2 database separately by using the user name and password information from the login page in Figure 3 Therefore the user name and password information has to be transferred to every page In order to keep user name and password secure we save them in a temporary file and passed the file name by the HIDDEN field type in the following form systematically Generating program Dynamic hash tables Target CGI program Create table statements Fixed CGI code that refers to hash table by their references Figure 10 Diagram for our overall system After going through each part of our implementation in detail the overall processing 1s summarized here Both our generating system and target system are developed in Perl The generating system scans the text file generates and puts the create table statements into a file and organizes the dynamic information into hash tables Then the generating system puts these hash tables into the beginning of the target CGI program Finally it writes 28 the fixed code into the rest of the target CGI program The hash tables are the core part for generating the dynamic target CGI program Regular expressions string manipulation the
53. y statement should contain its corresponding index number in the hash table for the query description sentence If there are dynamic variables needed for this query we create a hash table for this query s dynamic variable information as well This hash table s name should contain the same index number as the corresponding query statement string name For example the hash table for all of the query description sentences in Figure 1 should be querys qw Afind all of the work orders by the name and room number find all the information in the eouip name table find all of the work order by submitted date find the customer name in work order by sumbmitted day find all of the work orders find name and submitted date by room number in the work order table find the name of the person who is the contact for the most items of equipment in a specific department D The use of A here is to make each description sentence a no space string since hash tables use spaces to recognize each pair of key and value The corresponding guery statement for the first guery find all of the work orders by the name and room number should be 23 Squerystmtl select from work_order where name Dame and room room Also because there are two dynamic variable values needed
Download Pdf Manuals
Related Search