//  DISCLAIMER OF WARRANTIES.
//  The following [enclosed] code is sample code created by IBM
//  Corporation.  This sample code is not part of any standard IBM
//  product and is provided to you solely for the purpose of assisting
//  you in the development of your applications.  The code is provided
//  "AS IS".  IBM MAKES NO WARRANTIES, EXPRESS OR IMPLIED, INCLUDING BUT
//  NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
//  FOR A PARTICULAR PURPOSE, REGARDING THE FUNCTION OR PERFORMANCE OF
//  THIS CODE.  THIS CODE MAY CONTAIN ERRORS.  IBM shall not be liable
//  for any damages arising out of your use of the sample code, even
//  if it has been advised of the possibility of such damages.
//
//  Change History in Skyline Services Inc.
//
//	2001-03-17	- downloaded from ftp://ftp.software.ibm.com/software/websphere/info/tools/jdbctest/jdbctest.java
//			- modified to work for Sun JDBC/ODBC bridge and with mySQL).
//			- added options 4 and 5 for Sun and MySQL respectively.
//			
//			Michael Cook, Skyline Services Inc. 

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


public class jdbctest
{

 public String driverclassname;
 public String DBURL;
 public String dbprefix="jdbc:";
 public Connection con;
 public Statement stmt;
 public BufferedReader instream;
 public long operationTimer;

 public String globaluid = null; 
 public String globalpwd = null; 
 
 // Capture window events...
 static private class FrameCloser extends WindowAdapter
  {
   public void windowClosing(WindowEvent we)
    {
     System.out.println("windowClosing..." + we);
     we.getWindow().dispose();
     System.exit(0);
    }
  }

 // Capture key events...
 static private class KeyInterceptor implements KeyListener
  {
   public void keyTyped(java.awt.event.KeyEvent ke) {}
   public void keyPressed(java.awt.event.KeyEvent ke) {}
   public void keyReleased(java.awt.event.KeyEvent ke)
    {
     // System.out.println("keyReleased..." + ke);
     if(ke.getKeyCode() == KeyEvent.VK_ENTER)
      {
       TextField tf = (TextField) ke.getComponent();
       synchronized(tf)
        {tf.notify();
        }
      } // if enter key is pressed
    }
  }

 public static void main(String args[])
  {
    System.out.println("JDBC Test starting...\nThis version based on IBM example\nmodified 2001-03-18 for use with MS Access and MySQL by\nSkyline Services Inc. http://skyprod.com.\n");		


    jdbctest ct = new jdbctest();
    ct.init(args); // Loads jdbc driver
    ct.connect();  // sets connection
    if(ct.con == null)
     {System.out.println("Connection to "+ct.DBURL+" failed!");
      System.exit(33);
     }
    
    System.out.println("Connection Successful: " + ct.con);
    System.out.println("Connection took " + ct.operationTimer + " milliseconds to complete");
    try 
     {
       System.out.println("AutoCommit is: " + ct.con.getAutoCommit());
     }
    catch(SQLException s)
     {
      s.printStackTrace();
      System.out.println("Error code is: " + s.getErrorCode());
      System.out.println("SQLState is: " + s.getSQLState());
      System.exit(33);
     }
    do
     {
      try
       {System.out.println("Create new statement...");
        ct.stmt = ct.con.createStatement();
       }
      catch(SQLException se)
       {
        se.printStackTrace();
        System.out.println("Error code is: " + se.getErrorCode());
        System.out.println("SQLState is: " + se.getSQLState());
        System.exit(33);
       }
     }
    while(ct.process());
    try {
    ct.stmt.close();
    ct.con.close();
    } catch(Exception e) 
    {e.printStackTrace();}


    // System.exit ???
    System.exit(33);

  }

 public void init(String args[])
  {
   instream = new BufferedReader((new InputStreamReader(System.in)));

   while(driverclassname == null) {
     System.out.println("Please respond:\n\t1 - For DB2\n\t2 - For DB2/Pure Network Client\n\t3 - For Oracle\n\t4 - For Sun JdbcOdbc (good for MS Access)\n\t5 - For mySQL (needs a driver like mm.mysql-2.0.3-bin.jar\n\t\tadded to your CLASSPATH).\n\tUse: javap <driver_name> to verify the class is in your CLASSPATH.");
     String response = readLine();
     if(response.equals("1"))
      {driverclassname = "COM.ibm.db2.jdbc.app.DB2Driver";
       dbprefix += "db2:";
      }
     else if(response.equals("2")) {
      driverclassname = "COM.ibm.db2.jdbc.net.DB2Driver";
       dbprefix += "db2:";
      }
     else if(response.equals("3"))
      {driverclassname = "oracle.jdbc.driver.OracleDriver";
       dbprefix += "oracle:";
      }
     else if(response.equals("4"))
      {driverclassname = "sun.jdbc.odbc.JdbcOdbcDriver";
       dbprefix += "";
      }
      else if(response.equals("5"))
      {driverclassname = "org.gjt.mm.mysql.Driver";
       dbprefix += "mysql://";
      }			


     else System.out.println("Invalid Selection, specify 1, 2, 3, 4, or 5.");
    }

   System.out.println("Loading jdbc driver: " + driverclassname);
    try
     {
      Class.forName(driverclassname);
      System.out.println(driverclassname + " was loaded successfully");
     }
    catch(Exception e)
     {
      e.printStackTrace();
      System.out.println("Please modify you classpath to include the class: " +
                          driverclassname);
      
      System.out.println("To be sure that you have this class in your classpath, issue:\n javap " + driverclassname);
      System.exit(33);
     }
   
  }

 public void connect()
  {
   while(DBURL == null)
    {System.out.println("Please enter connection URL, e.g:");
	  
     if(driverclassname.endsWith(".app.DB2Driver")) {
      System.out.println("jdbc:db2:dbname or dbname or ");
	  }
     else if(driverclassname.endsWith(".net.DB2Driver")) {
      System.out.println("jdbc:db2://serverName:port/dbName or //serverName:port/dbName");
	  }
     else
      System.out.println("jdbc:oracle:thin:@hostname:1521:dbname or thin:@hostname:1521:dbname\n (or specify\n\t jdbc:odbc:DSN for Sun, or\n\t jdbc:mysql://HOST_NAME/DATABASE_NAME for mySQL).");

     DBURL = readLine();
    }

   if(!DBURL.startsWith(dbprefix))
    DBURL = dbprefix + DBURL;

   String userid = null;
    {System.out.println("Please enter userid for connection to "+ DBURL);
     userid = readLine();
     System.out.println("userid is: '" + userid + "'");
    }
   String password = null;
   if(userid != null && !userid.equals(""))
    {System.out.println("Please enter password   " +
      " =====>  WARNING: PASSWORD NOT HIDDEN   <====== "); 
     System.out.println("enter 'gui' instead of you password for a secure GUI prompt)");
     password = readLine();
     if(password.equals("gui") || password.equals("GUI"))
      password = getPassword(userid);
    }

   globaluid = userid;
   globalpwd = password;

   try
   {
    if(userid == null || userid.equals(""))
     {
      long start = System.currentTimeMillis();
      con = DriverManager.getConnection(DBURL);
      operationTimer = System.currentTimeMillis() - start;
     }
    else
     {
      long start = System.currentTimeMillis();
      con = DriverManager.getConnection(DBURL,userid,password);
      operationTimer = System.currentTimeMillis() - start;
     }
    }
   catch(SQLException se)
    {
     se.printStackTrace();
     System.out.println("Error code is: " + se.getErrorCode());
     System.out.println("SQLState is: " + se.getSQLState());
     // See if we can help the user with diagnosing the problem:
     boolean network_connect_problem = false;

     if(driverclassname.equals("oracle.jdbc.driver.OracleDriver"))
      {
       if( (se.getSQLState() == null) &&
           (se.getErrorCode() == 17002)    )
        { System.out.println("Connection to the Oracle server failed.\n"); 
          System.out.println("Unable to connect to @hostname:port specified in the DB URL." );
          network_connect_problem = true;
        }
      }
       


     if(driverclassname.equals("COM.ibm.db2.jdbc.net.DB2Driver"))
      {
       if( (se.getSQLState() == "08S01") &&
           (se.getErrorCode() == -99999)   )
        { System.out.println("Connection to the DB2 server failed.\n"); 
          System.out.println("Unable to connect to //hostname:port specified in the DB URL." );
          System.out.println("Verify that db2jstrt is running on the target host with the specified port as a parameter...");
          network_connect_problem = true;
        }
      }
       
     if(network_connect_problem)
      {
       System.out.println("Verify that the host and port that you are connecting to are correct.");
       System.out.println("Also verify that the host you are connecting to is listening on the specified port.");
       System.out.println("Hint: use telnet hostname port.");
       System.out.println("\tIf you get connection refused then the host is not listening on the specified port.");
       System.out.println("\tIf telnet simply hangs then the host is listening on the port.");
       System.out.println();
      }


      } // End catch connect exception

  }

 public String readLine()
  {
   String response = null;
   try
    {response = instream.readLine();
    }
   catch(IOException e)
    {
     e.printStackTrace();
    }
    return response;
  }


 public boolean process()
  {

   String instring = null;
   while(instring == null)
    {System.out.println("Please enter sql statement to execute...(q to quit)\n(or maxconn to test maximum connections possible to this database)");
     instring = readLine();
     if(instring.equals("q"))
      {System.out.println("Ok, quitting!");
       return false;
      }
     else if(instring.equals("maxconn"))
      {
       System.out.println("testing for maximun connections...");
       maxconn();
       return true;
      }
    }

   boolean rc = false;
   try
    {
     long start = System.currentTimeMillis();
     rc = stmt.execute(instring);
     operationTimer = System.currentTimeMillis() - start;
    }
   catch(Exception e)
    {
     e.printStackTrace();
    } 

   System.out.println("Operation took " + operationTimer + " milliseconds to complete");
   ResultSet rs = null;

   System.out.println("Just tried "+rc+" = stmt.execute(\""+instring+"\");");

   if(rc)
    {
     try
      {
       System.out.println("Getting result set...");
       rs = stmt.getResultSet();
       ResultSetMetaData rsm = rs.getMetaData();
       // Display names of columns fetched
       int colcount = rsm.getColumnCount();
       System.out.println( colcount + " column(s) in result");
       int[] coltype = new int[colcount+1];  // Do not slot 0
       for(int i = 1; i < colcount+1; i++)
        {System.out.print(rsm.getColumnName(i) + "   ");
         coltype[i] = rsm.getColumnType(i);
        }
       
       System.out.println();
       System.out.println("-----------------------------------");
       
       while(rs.next())
        {
         for(int j = 1; j < colcount+1; j++)
          {
           if(j!=1) System.out.print(",");
           switch(coltype[j])
            {
             case Types.TINYINT:
              System.out.print(""+rs.getShort(j));
              break;

             case Types.SMALLINT:
              System.out.print(""+rs.getShort(j));
              break;

             case Types.INTEGER:
              System.out.print(""+rs.getInt(j));
              break;

             case Types.BIGINT:
              System.out.print(""+rs.getLong(j));
              break;

             case Types.FLOAT:
              System.out.print(""+rs.getFloat(j));
              break;

             case Types.REAL:
              System.out.print(""+rs.getDouble(j));
              break;

             case Types.DOUBLE:
              System.out.print(""+rs.getDouble(j));
              break;

             case Types.NUMERIC:
              System.out.print(""+rs.getInt(j));
              break;

             case Types.DECIMAL:
              System.out.print(""+rs.getInt(j));
              break;

             case Types.CHAR:
//              System.out.print(""+rs.getByte(j));
              System.out.print(""+rs.getString(j));
              break;

             case Types.VARCHAR:
              System.out.print(""+rs.getString(j));
              break;

             case Types.LONGVARCHAR:
              System.out.print(""+rs.getString(j));
              break;

             case Types.DATE:
              System.out.print(""+rs.getDate(j));
              break;

             case Types.TIME:
              System.out.print(""+rs.getTime(j));
              break;

             case Types.TIMESTAMP:
              System.out.print(""+rs.getTimestamp(j));
              break;

             case Types.BINARY:
             case Types.BIT:
             case Types.VARBINARY:
             case Types.LONGVARBINARY:
              byte b[] = rs.getBytes(j);
              for(int n=0;n<b.length;n++)
               System.out.print(""+b[n]+"|");
              break;

             case Types.NULL:
              System.out.print("-");
              break;
             
             case Types.OTHER:
              System.out.print("OTHER");
              break;

             default:
              System.out.print("UNKNOWN-TYPE");
            }
          }
         System.out.println();

        }
       System.out.println();

       rs.close();
      }
     catch(SQLException se)
      {
       se.printStackTrace();
       System.out.println("Error code is: " + se.getErrorCode());
       System.out.println("SQLState is: " + se.getSQLState());
      }
     }
         
  
   return true;

  }

 String getPassword(String userid)
  {
   Frame f; Panel p; Label l; TextField tf;

   // Create the frame
   f = new Frame("Password Prompt for JDBC tester");
   f.setLocation(400,400);
   f.setSize(350,100);
   // Reister ourselves for 'Close' events
   f.addWindowListener(new FrameCloser());

   // Create the panel
   p = new Panel();
   f.add(p,"Center");
   l = new Label("Please enter password for Data Base user: " + userid);
   tf = new TextField(20);
   tf.setEchoChar('*');
   p.add(l,"Center");
   p.add(tf,"Center");
   try
    { f.show(); 
    }
   catch(Exception ex)
    {
     ex.printStackTrace();
     System.out.println("Your ===> DISPLAY <=== Environment Variable is not exported properly.  Please correct and retry.");
    }
    
   try {Thread.sleep(1000); } catch(InterruptedException ie) {}
   f.show(); 
   tf.requestFocus(); 
   tf.addKeyListener(new KeyInterceptor());

   //   System.out.println("Waiting for password to be entered...");


   synchronized(tf)
    {try { tf.wait(); } catch(InterruptedException e) {}
    }
   String password = tf.getText();
   f.transferFocus();
   f.dispose();
   return password;
  }


 public void maxconn()
  {

   Connection c[] = new Connection[125];

   System.out.println("1 connection assmued...looping...");

   int i;
   

   for( i=0; i < 125; i++)
   try
   {
    if(globaluid == null || globaluid.equals(""))
     {
      long start = System.currentTimeMillis();
      c[i] = DriverManager.getConnection(DBURL);
      operationTimer = System.currentTimeMillis() - start;
     }
    else
     {
      long start = System.currentTimeMillis();
      c[i] = DriverManager.getConnection(DBURL,globaluid,globalpwd);
      operationTimer = System.currentTimeMillis() - start;
     }
     
    System.out.println("Connection Successful: " + c[i]);
    System.out.println("Connection " + (i+2) + " took " + operationTimer + " milliseconds to complete");
    }
   catch(SQLException se)
    {
     se.printStackTrace();
     System.out.println("Error code is: " + se.getErrorCode());
     System.out.println("SQLState is: " + se.getSQLState());
     i--; break;
    }

   System.out.println("Maximum connections to DB:" + DBURL + " is " 
                       + (i+2) + ".");
   System.out.println("closing all connections except initial connection...");

   for( i=0; i < 125; i++)
    if(c[i] != null)
     try
      {
       c[i].close();
       System.out.print(".");
      }
     catch(SQLException se2)
      {
       se2.printStackTrace();
       System.out.println("Error code is: " + se2.getErrorCode());
       System.out.println("SQLState is: " + se2.getSQLState());
      }
   System.out.println("Done!");

  }

}