/** A mini client, to check SQL statements
 * 
*/

import java.io.*;
import java.util.*;
import java.sql.*;

public class myClient {

   // Oracle server at cs.cmu
  static final String DbURL = 
    "jdbc:oracle:thin:@dbclass.intro.cs.cmu.edu:1521:dbintro";

  // Oracle driver
  static final String OraDriver = 
    "oracle.jdbc.driver.OracleDriver";

  static String User;
  static String Passwd;

  public static void main(String[] args) {

    Connection con = null;

    try {
      // Load the Oracle Driver
      Class.forName(OraDriver);

      // get user-name and password
      BufferedReader is = new BufferedReader(
	 new InputStreamReader(System.in));
      String inputLine ;
      System.out.println("user name? ");
      User = is.readLine();
      System.out.println("password? ");
      Passwd = is.readLine();


      // Get a Connection to the database
      con = DriverManager.getConnection(DbURL, User, Passwd);

      // Create a Statement object
      Statement stmt = con.createStatement();

      // prompt the user for a select stmnt
      String sqlSt = "";
      String prompt = "your one-line SQL stmnt (ctrl-D to exit)> ";


      ResultSet rs = null;
      // to get info about the result set: # of columns, names etc
      ResultSetMetaData md =  null;
      int colCount=0;

      System.out.println(prompt);
      while ((inputLine = is.readLine() ) != null ){
	  inputLine = inputLine.trim();
	  if( !inputLine.startsWith("select") ) {
	      // update statement
	      sqlSt = inputLine;
	      int n = stmt.executeUpdate(sqlSt);
	      System.out.println("  *** " + n + " rows affected");
	  }
	  else {
	      System.out.println("  **** selection statement");
              System.out.println( "  ** executing : " + inputLine);
              System.out.println( "  " );
	      sqlSt = inputLine;
              rs = stmt.executeQuery(sqlSt);

	      md = rs.getMetaData();
	      colCount = md.getColumnCount();

              String outputLine = "";
              for( int i=1; i<= colCount; i++){
	          outputLine = outputLine + " | " + md.getColumnName(i);
	      }
              System.out.println( outputLine );
              outputLine = "";
              while (rs.next()) {
	          // for each column of the response set:
	          for( int i=1; i<= colCount; i++){
		      outputLine = outputLine + " | " + rs.getString(i);
	          }
	          System.out.println( outputLine );
	          outputLine = "";

              } // end while rs
	  }
          System.out.println( "  ** end of execution : " + inputLine);
          System.out.println(" ");
          System.out.println(prompt);
      } // end while inputLine
      System.out.println(" ");
      System.out.println("*** good bye! ");


       // in.close();
       // con.commit();
    } // end try
    catch (ClassNotFoundException e) {
       System.out.println("Could not load database driver: " + e.getMessage());
    }
    catch (SQLException e) {
       System.out.println("SQLException caught : " + e.getMessage());
    }
    catch (Exception e) {
       System.out.println("Other Exception caught : " + e.getMessage());
    }
    finally {
      // Always close the database connection
       try {
         if (con != null) con.close();
       } catch (SQLException ignored) {}
    }
  }			//end of main
}			//end of class