Drop-Down Lists and Hyperlinks

This slide shows how to send the user input including a drop-down list to a server for processing. If you select some options below and click the “Submit” button, the browser will call the following scripts: 
10.html ⇒ Select.cgi ⇒ Select.pl ⇒ Select.java ⇒ HTML ⇒ Hyperlink.pl
and list the descriptions hyperlinked with the corresponding cities based on the selected brands.


view source
print?
01<form method="post" action="http://undcemcs01.und.edu/~wen.chen.hu/cgi-bin/jdbc/Select.cgi">
02  <select name="brand" multiple size="3">
03   <option value="MKF Studios" /> MKF Studios
04   <option value="Wolf" /> Wolf
05   <option value="Parabuster Inc." /> Parabuster Inc.
06   <option value="Big Studios" /> Big Studios
07  </select>
08  <input type="submit" name="act" value="Submit" />
09  <input type="submit" name="act" value="HTML source" />
10  <input type="submit" name="act" value="CGI source" />
11  <input type="submit" name="act" value="Perl source" />
12  <input type="submit" name="act" value="Java source" />
13  <input type="submit" name="act" value="Hyperlink source" />
14  <input type="submit" name="act" value="Help" />
15  <input type="reset"             value="Reset" />
16</form>
         

     

The following code, Select.cgi is used to set the web environment for the Oracle database:

~/public_html/cgi-bin/jdbc/Select.cgi
view source
print?
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  Select.pl

The following Perl script is mainly used to process the web inputs including the drop-down list.

~/public_html/cgi-bin/jdbc/Select.pl
view source
print?
01#!/usr/bin/perl
02use CGI;
03$query = new CGI;
04$act   = $query->param( 'act' );
05 
06if ( $act eq "Submit" ) {
07  # Print HTML.
08  print ( "Content-type: text/html\n\n" );
09 
10# Use "here-doc" syntax.
11print <<EndofHTML;
12  <html>
13   <head>
14    <link rel="stylesheet" type="text/css" href="http://undcemcs01.und.edu/~wen.chen.hu/css/1.css" />
15   </head>
16   <body text="#000000" vLink="#3366CC" link="#3366CC" bgColor="#ffffff"
17     alink="#3366CC" background="http://undcemcs01.und.edu/~wen.chen.hu/bg/63.png">
18    <center>
19     <font size="+0" color="#3366CC">
20EndofHTML
21 
22  # Compose a Java command.
23  $cmd = "/usr/bin/java -Djava.security.egd=file:/dev/./urandom Select ";
24  my @brands = $query->param( 'brand' );
25  foreach my $brand (@brands) { $cmd .= "'" . $brand . "' "; }
26  print( $cmd );      system( $cmd );
27 
28print <<EndofHTML;
29     </b></font>
30    </center>
31   </body>
32  </html>
33EndofHTML
34}
35elsif ( $act eq "HTML source" ) {
36  # Print plain text.
37  print ( "Content-type: text/plain\n\n" );
38  $cmd  = "/usr/bin/lynx -dump -source " . $ENV{HTTP_REFERER};
39  $cmd .= "; echo \n\n\n\n";
40  system( $cmd );
41}
42elsif ( $act eq "CGI source" ) {
43  print ( "Content-type: text/plain\n\n" );
44  system( "/bin/cat Select.cgi; echo \n\n\n\n" );
45}
46elsif ( $act eq "Perl source" ) {
47  print ( "Content-type: text/plain\n\n" );
48  system( "/bin/cat Select.pl; echo \n\n\n\n" );
49}
50elsif ( $act eq "Java source" ) {
51  print ( "Content-type: text/plain\n\n" );
52  system( "/bin/cat Select.java; echo \n\n\n\n" );
53}
54elsif ( $act eq "Hyperlink source" ) {
55  print ( "Content-type: text/plain\n\n" );
56  system( "/bin/cat Hyperlink.pl; 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 descriptions and cities.

~/public_html/cgi-bin/jdbc/Select.java
view source
print?
01/*******************************************************************
02 
03  This program shows how to list the cities and
04    descriptions in the stores and productions tables
05    based on the sales.
06 
07  To use this program, you need to create the following
08    three tables by using the following commands:
09 
10  SQL> CREATE TABLE stores (
11    2    store_key  INTEGER PRIMARY KEY,
12    3    city       VARCHAR(32) NOT NULL,
13    4    region     VARCHAR(16) NOT NULL );
14 
15  SQL> CREATE TABLE products (
16    2    product_key  INTEGER PRIMARY KEY,
17    3    description  VARCHAR(32) NOT NULL,
18    4    brand        VARCHAR(32) NOT NULL );
19 
20  SQL> CREATE TABLE sales_fact (
21    2    store_key    INTEGER,
22    3    product_key  INTEGER,
23    4    sales        NUMBER(5,2) NOT NULL,
24    5    cost         NUMBER(5,2) NOT NULL,
25    6    profit       NUMBER(5,2) NOT NULL,
26    7    PRIMARY KEY ( store_key, product_key ),
27    8    FOREIGN KEY ( store_key ) REFERENCES stores( store_key ) ON DELETE CASCADE,
28    9    FOREIGN KEY ( product_key ) REFERENCES products( product_key ) ON DELETE CASCADE );
29 
30*******************************************************************/
31 
32// Import the following packages to use JDBC.
33import  java.sql.*;
34import  java.io.*;
35import  oracle.jdbc.*;
36import  oracle.jdbc.pool.OracleDataSource;
37 
38class  Select {
39  public static void  main( String args[ ] ) throws SQLException {
40    String user     = "C##user_id";
41    String password = "password";
42    String database = "20.185.147.112:1521/xe";
43 
44    // Open an OracleDataSource and get a connection.
45    OracleDataSource ods = new OracleDataSource( );
46    ods.setURL     ( "jdbc:oracle:thin:@" + database );
47    ods.setUser    ( user );
48    ods.setPassword( password );
49    Connection conn = ods.getConnection( );
50 
51    try {
52      // Create, compose, and execute a statement.
53      Statement stmt  = conn.createStatement( );
54      String   query  = "select city, description from sales_fact f, stores s, ";
55               query += "products p where (";
56      if ( args.length == 0 ) query += "brand=' ')";
57      else {
58        for ( int i=0;  i < args.length;  i++ )
59          if ( i == 0 ) query += "brand='"      + args[i].trim( );
60          else          query += "' or brand='" + args[i].trim( );
61        query += "') and f.product_key=p.product_key and f.store_key=s.store_key";
62      }
63      System.out.println( query + "<b>" );
64      ResultSet rset = stmt.executeQuery( query );
65 
66      // Iterate through the result and print the data.
67      while ( rset.next( ) ) {
68        System.out.print( "<a href='http://undcemcs01.und.edu/~wen.chen.hu/cgi-bin/jdbc/Hyperlink.pl?city=" );
69        System.out.print( rset.getString(1) + "'>" + rset.getString(2) + "<a>" );
70      }
71      // Close the ResultSet and Statement.
72      rset.close( );
73      stmt.close( );
74    }
75    catch ( SQLException ex ) {
76      System.out.println( ex );
77    }
78    // Close the Connection.
79    conn.close( );
80  }
81}

The following Perl script is used to display the corresponding web page based on the city submitted by the GET method. The command 
   print "Location: $url\n\n";
tells the browser to redirect to the URL at $url.

~/public_html/cgi-bin/jdbc/Hyperlink.pl?city=
view source
print?
01#!/usr/bin/perl
02use CGI;
03$query = new CGI;
04$city  = $query->url_param( "city" );
05 
06if ( $city eq "New York" ) {
07  $url = "http://www.nyc.gov/";
08}
09elsif ( $city eq "Chicago" ) {
10  $url = "http://www.cityofchicago.org/";
11}
12elsif ( $city eq "Atlanta" ) {
13  $url = "http://www.atlantaga.gov/";
14}
15elsif ( $city eq "Los Angles" ) {
16  $url = "http://www.lacity.org/";
17}
18elsif ( $city eq "San Francisco" ) {
19  $url = "https://en.wikipedia.org/wiki/San_Francisco";
20}
21elsif ( $city eq "Philadelphia" ) {
22  $url = "http://www.phila.gov/";
23}
24else {
25  $url = "http://www.grandforksgov.com/";
26}
27print "Location: $url\n\n";




      Women only call me ugly until they find out how much money I make.    
      Then they call me ugly and poor.