Slide 6.6: Forms and input text fields

Forms and Input Text Fields

This slide shows how to use HTML form to send the user input from a single-line input text field to a server for processing. If you type a region, Central, East, or West, in the text field below and click the “List” button, the browser will call the following scripts:

6.html ⇒ List.cgi ⇒ List.pl ⇒ List.java

and list the corresponding stores in that region. An empty region will list all stores.

01<form method="post" action="http://undcemcs01.und.edu/~wen.chen.hu/cgi-bin/jdbc/List.cgi"> 
02  Region: 
03  <input type="text"   name="region" /> 
04  <input type="submit" name="act" value="List" /> 
05  <input type="submit" name="act" value="HTML source" />
06  <input type="submit" name="act" value="CGI source" />
07  <input type="submit" name="act" value="Perl source" />
08  <input type="submit" name="act" value="Java source" />
09  <input type="submit" name="act" value="Help" />
10  <input type="reset"             value="Reset" />
11</form>

Region:

The button “List” will activate the following code, List.cgi, which is used to set the web environment for the Oracle database:

~/public_html/cgi-bin/jdbc/List.cgi

1#!/usr/bin/bash
2 
3CLASSPATH=.:/usr/lib/oracle/23/client64
4CLASSPATH=$CLASSPATH:/usr/lib/oracle/23/client64/lib/ojdbc8.jar
5CLASSPATH=$CLASSPATH:/usr/lib/oracle/23/client64/lib/ottclasses.zip
6export  CLASSPATH
7 
8/usr/bin/perl List.pl

The following Perl script is mainly used to process the web inputs. It could be skipped by letting the shell and Java scripts handle the web inputs. However, Perl is known for its strong string processing, which is greatly needed for web processing, so the Perl script is kept to do most part of string processing.

~/public_html/cgi-bin/jdbc/List.pl

01#!/usr/bin/perl
02use CGI;
03$query  = new CGI;
04$act    = $query->param( 'act' );
05$region = $query->param( 'region' );
06 
07if ( $act eq "List" ) {
08  # Print HTML.
09  print ( "Content-type: text/html\n\n" );
10 
11# Use "here-doc" syntax.
12print <<EndofHTML;
13  <html>
14   <head>
15    <link rel="stylesheet" type="text/css" href="http://undcemcs01.und.edu/~wen.chen.hu/css/1.css" />
16   </head>
17   <body text="#000000" vLink="#3366CC" link="#3366CC" bgColor="#ffffff"
18     alink="#3366CC" background="http://undcemcs01.und.edu/~wen.chen.hu/bg/63.png">
19    <center>
20     <font color="#3366CC">
21EndofHTML
22 
23  # Remove leading and trailing spacing.
24  $region =~ s/^\s*(\S*)\s*$/$1/;
25  # For security, remove some Unix metacharacters.
26  $region =~ s/;|>|>>|<|\*|\?|\&|\|//g;
27  # Compose a Java command.
28  $cmd    =  "/usr/bin/java -Djava.security.egd=file:/dev/./urandom List '";
29  $cmd   .=  $region . "'";
30  print( $cmd );    system( $cmd );
31 
32print <<EndofHTML;
33     </b></font>
34    </center>
35   </body>
36  </html>
37EndofHTML
38}
39elsif ( $act eq "HTML source" ) {
40  # Print plain text.
41  print ( "Content-type: text/plain\n\n" );
42  $cmd  = "/usr/bin/lynx -dump -source " . $ENV{HTTP_REFERER};
43  $cmd .= "; echo \n\n\n\n";
44  system( $cmd );
45}
46elsif ( $act eq "CGI source" ) {
47  print ( "Content-type: text/plain\n\n" );
48  system( "/bin/cat List.cgi; echo \n\n\n\n" );
49}
50elsif ( $act eq "Perl source" ) {
51  print ( "Content-type: text/plain\n\n" );
52  system( "/bin/cat List.pl; echo \n\n\n\n" );
53}
54elsif ( $act eq "Java source" ) {
55  print ( "Content-type: text/plain\n\n" );
56  system( "/bin/cat List.java; echo \n\n\n\n" );
57}
58elsif ( $act eq "Help" ) {
59  print ( "Content-type: text/html\n\n" );
60  system( "/bin/cat  Help.html" );
61}
62else {
63  print( "Content-type: text/html\n\n" );
64  print( "No such option: <em>$act</em>" );
65}

The following JDBC script accesses the database and retrieves the stores.

~/public_html/cgi-bin/jdbc/List.java

01/*******************************************************************
02 
03  This program shows how to list the stores in the
04    stores table.
05 
06  To use this program, you need to create a table
07    stores by using the following commands:
08 
09  SQL> create table  stores (
10    2    store_key  INTEGER PRIMARY KEY,
11    3    city       VARCHAR(32) NOT NULL,
12    4    region     VARCHAR(16) NOT NULL );
13  Table created.
14 
15*******************************************************************/
16 
17// Import the following packages to use JDBC.
18import  java.sql.*;
19import  java.io.*;
20import  oracle.jdbc.*;
21import  oracle.jdbc.pool.OracleDataSource;
22 
23class  List {
24  public static void  main( String args[ ] ) throws SQLException {
25    String user     = "C##user_id";
26    String password = "password";
27    String database = "20.185.147.112:1521/xe";
28 
29    // Open an OracleDataSource and get a connection.
30    OracleDataSource ods = new OracleDataSource( );
31    ods.setURL     ( "jdbc:oracle:thin:@" + database );
32    ods.setUser    ( user );
33    ods.setPassword( password );
34    Connection conn = ods.getConnection( );
35 
36    try {
37      // Create, compose, and execute a statement.
38      Statement stmt = conn.createStatement( );
39      String  query  = "select store_key, city, region from stores ";
40              query += "where region like '%" + args[0].trim( ) + "%'";
41      System.out.println( query + "<b>" );
42      ResultSet rset = stmt.executeQuery( query );
43 
44      // Iterate through the result and print the data.
45      while ( rset.next( ) ) {
46        System.out.print( rset.getString(1) + ", " + rset.getString(2) );
47        System.out.print( ", " + rset.getString(3) );
48      }
49      // Close the ResultSet and Statement.
50      rset.close( );
51      stmt.close( );
52    }
53    catch ( SQLException ex ) {
54      System.out.println( ex );
55    }
56    // Close the Connection.
57    conn.close( );
58  }
59}