// File: Jdbc2.java (Module 14)
//
// Author: Rahul Simha
// Created: Dec 8, 1998
//
// A simple JDBC application

import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.sql.*;
import java.net.*;

class JDBCClient extends Frame {

  TextField       // TextFields to read stuff from user.
    queryfield,   // SQL query.
    columnfield;  // A column name.

  String         
    query,        // The string that will hold the query.
    column;       // The column name.
    
  boolean is_connected = false;

  Panel message_panel;      // To display the results
  GridLayout grid_layout;   // in a scrollable panel.
  ScrollPane sc;


  // Constructor.

  public JDBCClient ()
  {
    // Create the frame.
    this.setSize (600,400);
    this.setLocation (100,100);
    this.setTitle ("JDBC Client");
    this.setBackground (Color.cyan);
    // this.setLayout (new BorderLayout());

    // This is where messages will be displayed:
    grid_layout = new GridLayout (1,1);
    message_panel = new Panel (grid_layout);
    message_panel.setBackground (Color.white);
    sc = new ScrollPane ();
    sc.add (message_panel);
    this.add (sc, BorderLayout.CENTER);

    // A top panel for the query and column name.
    Panel top_panel = new Panel();
    top_panel.setLayout (new GridLayout (2,2));

    // The query textfield.
    Label L = new Label ("QUERY: ");
    L.setFont (new Font("Serif", Font.BOLD, 20));
    top_panel.add (L);
    queryfield = new TextField (40);
    queryfield.setForeground (Color.blue);
    queryfield.addTextListener (
      new TextListener () {		       
        public void textValueChanged (TextEvent t)
	{
	  query = queryfield.getText();
	}
      }
    );
    top_panel.add (queryfield);

    // The column textfield.
    L = new Label ("COLUMN: ");
    L.setFont (new Font("Serif", Font.BOLD, 20));
    top_panel.add (L);
    columnfield = new TextField (10);
    columnfield.setForeground (Color.blue);
    columnfield.addTextListener (
      new TextListener () {		       
        public void textValueChanged (TextEvent t)
	{
	  column = columnfield.getText();
	}
      }
    );
    top_panel.add (columnfield);

    // Add the top panel to the frame.
    this.add (top_panel, BorderLayout.NORTH);

    // A bottom panel for three buttons.
    Panel bottom_panel = new Panel ();

    // A quit button.
    Button quitb = new Button ("QUIT");
    quitb.setFont (new Font ("Serif", Font.PLAIN, 20));
    quitb.setBackground (Color.red);
    quitb.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent a)
	{
	  quit ();
	}
      }
    );
    bottom_panel.add (quitb);

    // A connect button.
    Button connectb = new Button ("CONNECT");
    connectb.setFont (new Font ("Serif", Font.PLAIN, 20));
    connectb.setBackground (Color.green);
    connectb.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent a)
	{
	  connect ();
	}
      }
    );
    bottom_panel.add (connectb);

    // A submit button to submit a query.
    Button submitb = new Button ("SUBMIT");
    submitb.setFont (new Font ("Serif", Font.PLAIN, 20));
    submitb.setBackground (Color.green);
    submitb.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent a)
	{
	  submit ();
	}
      }
    );
    bottom_panel.add (submitb);

    // Add the bottom panel.
    this.add (bottom_panel, BorderLayout.SOUTH);

    // Display the frame.
    this.setVisible (true);
  }


  // Display text on a message panel using Label's.

  void display_text (String s, Color c)
  {
    // Create the label.
    Label L = new Label (s);
    L.setForeground (c);

    // Add to the message panel.
    message_panel.add (L);
    message_panel.invalidate();

    // Increment the layout rows for the next message.
    grid_layout.setRows (grid_layout.getRows() + 1);

    // Scroll to the bottom.
    int height = sc.getViewportSize().height;
    sc.setScrollPosition (0, height);

    // Re-compute the display.
    this.validate();
  }


  // These variables will be used in the connection dialog.
  Dialog connect_dialog;       // A connection dialog.
  TextField                    // Textfields in the dialog.
    Unixnamef, Unixpassf,
    Oraclenamef, Oraclepassf;
  String                       // String variables for the
    Unixname, Unixpass,        // usernames and passwords.
    Oraclename, Oraclepass;

  void connect ()
  {
    // If already connected, return.
    if (is_connected) return;

    // Set up the dialog.
    connect_dialog = new Dialog (this, true);
    connect_dialog.setTitle ("Connect to Oracle");
    connect_dialog.setSize (400,200);
    connect_dialog.setBackground (Color.cyan);
    connect_dialog.setLayout (new GridLayout(5,2));
    connect_dialog.setLocation (200,200);

    // Unix username textfield.
    Label L = new Label ("UNIX USERNAME:");
    connect_dialog.add (L);
    Unixnamef = new TextField (20);
    Unixnamef.setForeground (Color.blue);
    Unixnamef.addTextListener (
      new TextListener () {		       
        public void textValueChanged (TextEvent t)
	{
	  Unixname = Unixnamef.getText();
	}
      }
    );
    connect_dialog.add (Unixnamef);

    // Unix password textfield.
    L = new Label ("UNIX PASSWORD:");
    connect_dialog.add (L);
    Unixpassf = new TextField (20);
    Unixpassf.setEchoChar ('*');
    Unixpassf.setForeground (Color.blue);
    Unixpassf.addTextListener (
      new TextListener () {		       
        public void textValueChanged (TextEvent t)
	{
	  Unixpass = Unixpassf.getText();
	}
      }
    );
    connect_dialog.add (Unixpassf);

    // Oracle username textfield.
    L = new Label ("ORACLE USERNAME:");
    connect_dialog.add (L);
    Oraclenamef = new TextField (20);
    Oraclenamef.setForeground (Color.blue);
    Oraclenamef.addTextListener (
      new TextListener () {		       
        public void textValueChanged (TextEvent t)
	{
	  Oraclename = Oraclenamef.getText();
	}
      }
    );
    connect_dialog.add (Oraclenamef);

    // Oracle password textfield.
    L = new Label ("ORACLE PASSWORD:");
    connect_dialog.add (L);
    Oraclepassf = new TextField (20);
    Oraclepassf.setEchoChar ('*');
    Oraclepassf.setForeground (Color.blue);
    Oraclepassf.addTextListener (
      new TextListener () {		       
        public void textValueChanged (TextEvent t)
	{
	  Oraclepass = Oraclepassf.getText();
	}
      }
    );
    connect_dialog.add (Oraclepassf);

    // A connect button.
    Button connectb = new Button ("CONNECT");
    connectb.setBackground (Color.green);
    connectb.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent a)
	{
	  connect_db ();
	  connect_dialog.dispose ();
	}
      }
    );
    connect_dialog.add (connectb);

    // A cancel button.
    Button cancelb = new Button ("CANCEL");
    cancelb.setBackground (Color.red);
    cancelb.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent a)
	{
	  connect_dialog.dispose ();
	}
      }
    );
    connect_dialog.add (cancelb);

    // Display the dialog.
    connect_dialog.setVisible (true);
  }


  Connection con;

  void connect_db ()
  {
    try {
      // Load the JDBC Driver.
      Class.forName ("intersolv.jdbc.sequelink.SequeLinkDriver");

      // Set up the connection URL
      String connection_url =
	"jdbc:sequelink://delphi:4003/[Oracle];OSUser=" + Unixname +
        ";OSPassword=" + Unixpass;
      
      display_text ("Trying connection ... ", Color.blue);

      con = DriverManager.getConnection (connection_url, 
					 Oraclename,
					 Oraclepass);

      // It worked.
      display_text ("Connection succeeded", Color.blue);
      display_text ("Enter query above", Color.blue);
      is_connected = true;
    }
    catch(SQLException e){ 
      System.out.println(e);
      display_text ("Connection attempt failed", Color.blue);
    }
    catch (ClassNotFoundException e) { System.out.println(e); }
  }


  // Handle a query.

  void submit ()
  {
    // If not connected, don't do anything.
    if (!is_connected) {
      display_text ("Not connected ... try connecting", Color.red);
      return;
    }

    // If bad strings are given, pass.
    if ( (query == null) || (column == null) ) {
      display_text ("Null query or column... try again", Color.red);
      return;
    }

    // Otherwise, execute the query.
    try {
      display_text ("Trying SQL: " + query, Color.blue);

      // Get a Statement instance first.
      Statement st = con.createStatement();

      // Execute the query.
      ResultSet rs = st.executeQuery (query);

      display_text ("SQL execution returned with:", Color.blue);

      int index = 1;
      try {
	// Use the given column name.
	index = rs.findColumn (column);
      }
      catch (SQLException e){ 
	display_text ("Column name not found ... try again", Color.blue);
	return;
      }
      
      // If the column name was valid, get the values.
      while (rs.next()) {
        String result = rs.getString (index);
	display_text (result, Color.black);
      }
    }
    catch (SQLException e){ 
      System.out.println(e);
      display_text ("SQL query failed ... try again", Color.blue);
    }
  }


  // Exit gracefully.

  void quit ()
  {
    try {
      con.close ();
      System.exit (0);
    }
    catch (SQLException e){ System.out.println(e);}
  }

}


public class Jdbc2 {

  public static void main (String[] argv)
  {
    JDBCClient jc = new JDBCClient ();
  }

}