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

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

class JDBCClient extends JFrame {

  JTextField      // 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;

  JTextArea message_board;  // To display the results

  JScrollPane sc;


  // Constructor.

  public JDBCClient ()
  {
    // Create the frame.
    this.setSize (600,400);
    this.setLocation (100,100);
    this.setTitle ("JDBC Client");

    Container cPane = this.getContentPane();
    // cPane.setLayout (new BorderLayout());

    // This is where messages will be displayed:
    message_board = new JTextArea ();
    message_board.setBackground (Color.white);
    sc = new JScrollPane (message_board);
    cPane.add (sc, BorderLayout.CENTER);

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

    // The query textfield.
    JLabel L = new JLabel ("QUERY: ");
    L.setFont (new Font("Serif", Font.BOLD, 20));
    top_panel.add (L);
    queryfield = new JTextField (40);
    queryfield.setForeground (Color.blue);
    queryfield.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent t)
	{
	  query = queryfield.getText();
	}
      }
    );
    top_panel.add (queryfield);

    // The column textfield.
    L = new JLabel ("COLUMN: ");
    L.setFont (new Font("Serif", Font.BOLD, 20));
    top_panel.add (L);
    columnfield = new JTextField (10);
    columnfield.setForeground (Color.blue);
    columnfield.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent t)
	{
	  column = columnfield.getText();
	}
      }
    );
    top_panel.add (columnfield);

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

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

    // A quit button.
    JButton quitb = new JButton ("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.
    JButton connectb = new JButton ("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.
    JButton submitb = new JButton ("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.
    cPane.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)
  {
    message_board.setForeground (c);
    message_board.setText ( message_board.getText() + "\n" + s);

    // Scroll to the bottom.
    JViewport vp = sc.getViewport();
    Dimension D = vp.getViewSize();
    vp.setViewPosition ( new Point (0, D.height) );
  }


  // These variables will be used in the connection dialog.
  JDialog connect_dialog;      // A connection dialog.
  JTextField                   // Textfields in the dialog.
    Unixnamef, 
    Oraclenamef;
  JPasswordField
   Unixpassf,
   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 JDialog (this, true);
    connect_dialog.setTitle ("Connect to Oracle");
    connect_dialog.setSize (400,200);
    connect_dialog.setLocation (200,200);

    Container cPane = connect_dialog.getContentPane();
    cPane.setBackground (Color.cyan);
    cPane.setLayout (new GridLayout(5,2));

    // Unix username textfield.
    JLabel L = new JLabel ("UNIX USERNAME:");
    cPane.add (L);
    Unixnamef = new JTextField (20);
    Unixnamef.setForeground (Color.blue);
    Unixnamef.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent t)
	{
	  Unixname = Unixnamef.getText();
	}
      }
    );
    cPane.add (Unixnamef);

    // Unix password textfield.
    L = new JLabel ("UNIX PASSWORD:");
    cPane.add (L);
    Unixpassf = new JPasswordField (20);
    Unixpassf.setForeground (Color.blue);
    Unixpassf.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent t)
	{
	  Unixpass = new String (Unixpassf.getPassword());
	}
      }
    );
    cPane.add (Unixpassf);

    // Oracle username textfield.
    L = new JLabel ("ORACLE USERNAME:");
    cPane.add (L);
    Oraclenamef = new JTextField (20);
    Oraclenamef.setForeground (Color.blue);
    Oraclenamef.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent t)
	{
	  Oraclename = Oraclenamef.getText();
	}
      }
    );
    cPane.add (Oraclenamef);

    // Oracle password textfield.
    L = new JLabel ("ORACLE PASSWORD:");
    cPane.add (L);
    Oraclepassf = new JPasswordField (20);
    Oraclepassf.setForeground (Color.blue);
    Oraclepassf.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent t)
	{
	  Oraclepass = new String (Oraclepassf.getPassword());
	}
      }
    );
    cPane.add (Oraclepassf);

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

    // A cancel button.
    JButton cancelb = new JButton ("CANCEL");
    cancelb.setBackground (Color.red);
    cancelb.addActionListener (
      new ActionListener () {		       
        public void actionPerformed (ActionEvent a)
	{
	  connect_dialog.dispose ();
	}
      }
    );
    cPane.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 ();
  }

}