Website Construction Summary (Cont.)
  1. Fetching Data by Using SQL
    2.  This step includes the following two programs:

    • Students.cgi, setting up the web environment for Oracle and

    • Students.java, fetching dynamic data from a web server running SQL and returning a JSON object.

    JSON stands for JavaScript Object Notation.

view source
print?
01[
02 {
03  "Name": "Alfreds Futterkiste",
04  "City": "Berlin",
05  "Country": "Germany"
06 },
07 {
08  "Name": "Berglunds snabbköp",
09  "City": "Luleå",
10  "Country": "Sweden"
11 },
12 {
13  "Name": "Island Trading",
14  "City": "Cowes",
15  "Country": "UK"
16 },
17 {
18  "Name": "Ernst Handel",
19  "City": "Graz",
20  "Country": "Austria"
21 },
22 {
23  "Name": "FISSA Fabrica Inter.",
24  "City": "Madrid",
25  "Country": "Spain" 
26 }
27]
    JSON, much like XML, is syntax for storing and exchanging text information. One example of JSON objects is shown as above. The script is used in the file Students.html, where the method JSON.parse parses a string as JSON.

    ~/public_html/cgi-bin/demo/OracleJSON/Students.cgi
    view source
    print?
    01#!/usr/bin/bash
    02 
    03CLASSPATH=.:/usr/lib/oracle/23/client64
    04CLASSPATH=$CLASSPATH:/usr/lib/oracle/23/client64/lib/ojdbc8.jar
    05CLASSPATH=$CLASSPATH:/usr/lib/oracle/23/client64/lib/ottclasses.zip
    06export CLASSPATH
    07 
    08echo "Content-type: text/plain"
    09echo ""
    10 
    11/usr/bin/java  Students

    ~/public_html/cgi-bin/demo/OracleJSON/Students.java
    view source
    print?
    01/*********************************************************************
    02 
    03  This program shows how to retrieve the student data
    04    in the Students table.
    05 
    06  To use this program, you need to create the table
    07    Students by using the following commands:
    08 
    09  SQL> create table  Students (
    10    2    Name      varchar(32)  not null,
    11    3    City      varchar(16)  not null,
    12    4    Country   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  Students {
    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 Name, City, Country FROM Students";
    40      ResultSet rset = stmt.executeQuery( query );
    41 
    42      // Iterate through the result and save the data.
    43      String  outp = "[";
    44      while ( rset.next( ) ) {
    45        if ( outp != "[" ) outp += ",";
    46        outp += "{\"Name\":\""   + rset.getString(1) + "\",";
    47        outp += "\"City\":\""    + rset.getString(2) + "\",";
    48        outp += "\"Country\":\"" + rset.getString(3) + "\"}";
    49      }
    50      outp += "]" ;
    51      // Print the JSON object outp.
    52      System.out.println( outp );
    53      // Close the ResultSet and Statement.
    54      rset.close( );
    55      stmt.close( );
    56    }
    57    catch ( SQLException ex ) {
    58      System.out.println( ex );
    59    }
    60    // Close the Connection.
    61    conn.close( );
    62  }
    63}




      That’s your best friend and your worst enemy—your own brain.