#!/usr/bin/bash

CLASSPATH=.:/usr/lib/oracle/23/client64
CLASSPATH=$CLASSPATH:/usr/lib/oracle/23/client64/lib/ojdbc8.jar
CLASSPATH=$CLASSPATH:/usr/lib/oracle/23/client64/lib/ottclasses.zip
export CLASSPATH

echo "Content-type: text/plain"
echo ""

/usr/bin/java  Students

 /*********************************************************************

  This program shows how to retrieve the student data
    in the Students table.

  To use this program, you need to create the table
    Students by using the following commands:

  SQL> create table  Students (
    2    Name      varchar(32)  not null,
    3    City      varchar(16)  not null,
    4    Country   varchar(16)  not null );
  Table created.

*******************************************************************/

// Import the following packages to use JDBC.
import  java.sql.*;
import  java.io.*;
import  oracle.jdbc.*;
import  oracle.jdbc.pool.OracleDataSource;

class  Students {
  public static void  main( String args[ ] ) throws SQLException {
    String user     = "C##user_id";
    String password = "password";
    String database = "20.185.147.112:1521/xe";

    // Open an OracleDataSource and get a connection.
    OracleDataSource ods = new OracleDataSource( );
    ods.setURL     ( "jdbc:oracle:thin:@" + database );
    ods.setUser    ( user );
    ods.setPassword( password );
    Connection conn = ods.getConnection( );

    try {
      // Create, compose, and execute a statement.
      Statement stmt = conn.createStatement( );
      String query = "SELECT Name, City, Country FROM Students";
      ResultSet rset = stmt.executeQuery( query );

      // Iterate through the result and save the data.
      String  outp = "[";
      while ( rset.next( ) ) {
        if ( outp != "[" ) outp += ",";
        outp += "{\"Name\":\""   + rset.getString(1) + "\",";
        outp += "\"City\":\""    + rset.getString(2) + "\",";
        outp += "\"Country\":\"" + rset.getString(3) + "\"}";
      }
      outp += "]" ;
      // Print the JSON object outp.
      System.out.println( outp );
      // Close the ResultSet and Statement.
      rset.close( );
      stmt.close( );
    }
    catch ( SQLException ex ) {
      System.out.println( ex );
    }
    // Close the Connection.
    conn.close( );
  }
}