import java.awt.*;
import java.io.*;
import sun.io.*;
import java.sql.*;
import java.util.*;
import java.math.*;
   	
  
class SQL2 extends Frame
{
	TextArea TA1;	// show SQL SELECT result
	TextArea TA2;	// show SQL error message
	TextField TF1;	// input SQL statement
	Choice CH1;	// choice database
	String CH1_I1 = "dBASE"; 	// database 1
	String CH1_I2 = "Sybase";	// database 2
	String oldCH1Item = CH1_I1;	
	
	
	
	
	Button BT_reconnect; 
	Button BT_ok;
	Button BT_cancel;
	Button BT_exit;
	Panel p_n;
	Panel p_c;
	Panel p_s;
	
	//------- SQL --------------
	String url1 = "jdbc:odbc:occ.dbf";  
	String url2   = "jdbc:odbc:OccupaL";
	String url = url1;
		
	String sUsername1 = "";
	String sPassword1 = "";
		
	String sUsername2 = "GLK";
	String sPassword2 = "fm";
	
	String sUsername = sUsername1; 
	String sPassword = sPassword1; 
	
	String CharFunc1 = "CHR";	// database 1's SQL Character function
	String CharFunc2 = "CHAR";	// database 2's SQL Character function
	String CharFunc = CharFunc1;
	Statement stmt; //SQL statement
	Connection con; //SQL connection
	DatabaseMetaData dma; // SQL DB metadata
	ResultSet rs; //SQL DB resultset   
	String SQLerror="";
	String SQLresult="";
	String SQLStr="";

	//----------------------------------
	SQL2 ()
	{
		setLayout(new BorderLayout());
		setSize(800,500);
		setTitle("SQL2");
		Font Font1 = new Font("Courier New",Font.PLAIN,16);  
		p_n = new Panel();
		p_s = new Panel();
		p_c = new Panel();
		p_c.setLayout(new GridLayout(1, 0));
		
		TF1 = new TextField("", 50);
		TF1.setFont(Font1);
		
		TA1 = new TextArea("");
		TA1.setFont(Font1);
		TA2 = new TextArea("");
		TA2.setFont(Font1);
		
		CH1 = new Choice();
		CH1.addItem(CH1_I1);
		CH1.addItem(CH1_I2);
		
		BT_reconnect = new Button(" Reconnect ");
		BT_ok = new Button(" OK ");
		BT_cancel = new Button(" Cancel ");
		BT_exit = new Button(" Exit ");
		
			
		p_n.add(new Label("SQL:"));
		p_n.add(TF1);
		
		p_c.add(TA1);
		p_c.add(TA2);
		
		p_s.add(BT_reconnect);
		p_s.add(CH1);
		p_s.add(BT_ok);
		p_s.add(BT_cancel);
		p_s.add(BT_exit);
		
		add("North", p_n);
		add("Center", p_c);
		add("South", p_s);
				
		show();
		openDB(url, sUsername, sPassword);
	}
	
	//------------------------------
	public boolean handleEvent(Event e)  
	{	 
	  switch (e.id)                                                                                                                	
	  {
	    case (Event.WINDOW_DESTROY):   
		                                                                            	
			dispose();       
			try {                                                                                           	
				con.close();                                                                                            	
			} catch(SQLException ex)	
			{	showSQLError(ex);
			}
			System.exit(0);    
			return true;                                                                                           	
	    case (Event.ACTION_EVENT):                                                                                                           	
		                                                                                                               	
			if (e.target==BT_ok)   // Begin to excute SQL                                                                         	
			{    
				try {
					SQLStr=TF1.getText().trim();
					stmt = con.createStatement ();
					TA2.appendText("\n SQL="+SQLStr);
					
					if (SQLStr.toUpperCase().startsWith("SELECT"))
					{
	          			rs = stmt.executeQuery (SQLStr);
	          			showSQLResult(rs);
	          			rs.close();
	          		}	
	          		else // UPDATE, INSERT, INPUT, CREATE, DROP
	          		{
	          			int UpdateCount=stmt.executeUpdate(SQLStr) ;
	          			TA1.setText("UpdateCount="+UpdateCount);
	          		}         		          	
	          		
	          		stmt.close();
	          	
		     	}                                                                  	
				catch (SQLException ex)                                                                                     	
				{                           
 					showSQLError(ex);			                                                                                                    
                   	}                                                                                                           	
    	     		   
     			
     			TF1.requestFocus();                                                                                    	
				return true;                                                                                                	
			}                                                                                     
		     else if (e.target==BT_cancel) // clear SQL input line                                                         
		     {                                                                                     
		     	TF1.setText("");                                                                 
		     	TF1.requestFocus();                                                              
		     	return true;                                                                     
		     }                                                                                     
		     else if (e.target==BT_exit)                                                           
		     {                                                                                     
		     	dispose();                                                                       
		     	try {                                                                            
		     		con.close();                                                                
		     	} catch (SQLException ex)	                                                   
		     	{	showSQLError(ex);                                                           
		     	}                                                                                
		     	System.exit(0);                                                                  
		     }                                                                                     
		     else if (e.target==BT_reconnect)  // reconnect current database                                                    
		     {                                                                                     
		     	try {                                                                            
		     		con.close();                                                                
		     		openDB(url, sUsername, sPassword);                                                                
		     	} catch (SQLException ex)	                                                   
		     	{	showSQLError(ex);                                                           
		     	}                                                                                
		     	                                                                                 
		     }                                                                                     
		     else if (e.target==CH1)   // choose another database                                                            
		     {	
		     	String curItem = CH1.getSelectedItem();                                          
		     	if (curItem !=oldCH1Item)  // if choose other database                                                     
		     	{                                                                                
		     		oldCH1Item = curItem;                                                       
		     		try                                                                         
		     		{                                                                           
		     			con.close();                                                           
		     		} catch (SQLException ex)	                                              
		     		{
		     			showSQLError(ex);                                                      
		     		}
		     		
		     		if (curItem==CH1_I1)                                                        
		     		{
		     			url = url1;                                                            
		     			CharFunc = CharFunc1;
		     			sUsername=sUsername1;
		     			sPassword=sPassword1;
		     		}	
		     		else if (curItem==CH1_I2)                                                   
		     		{
		     			url = url2;          
		     			CharFunc = CharFunc2;
		     			sUsername=sUsername2;
		     			sPassword=sPassword2;
		     		}	                                                  
		     		                                                                            
		     		openDB(url, sUsername, sPassword);   // connect to database                                                             
		     	}                                                                                
		     }                                                                                     
	  	}				                                                                                             	
  	  	return false;                                                                                                    			
	} 
	
	//----------------------------------------
	/** Show ResultSet of SQL 
	*/
	public void showSQLResult(ResultSet rs) 
	{
		int i=0;
		try {
			ResultSetMetaData rsmd = rs.getMetaData ();
			int ColumnCount = rsmd.getColumnCount ();

			boolean more = rs.next();
	          if (more)
	          {	
	          	TA1.setText(""); // Clear TextArea TA1
	          
	          	SQLresult="";
	          	while (more)
	          	{
	          		TA1.appendText(getField(rs, 1));
	          		
	          		for (i=2; i<=ColumnCount; i++)
	          		{
	          			TA1.appendText(", "+getField(rs, i));
	          		}
	          		TA1.appendText("\n");
	          		
	          		more = rs.next();
	          	}
	          } 
	          else
	          	TA2.appendText("\nNo result.");
	          
	          	
	     } 
	     catch (SQLException ex)     	
	     {
	     	showSQLError(ex);
	     }	
	     
	}
	//--------------------------------
	/** Read data from ResultSet
	*/
	public String getField(ResultSet r, int ColNo)
	{	int i=0;
		try 
		{
			ResultSetMetaData rsmd = r.getMetaData ();                                                               
		     switch(rsmd.getColumnType(ColNo))                                                                        
		     {                                                                                                        
		     	case (java.sql.Types.BIGINT):                                                                       
		     		return (String.valueOf(r.getLong(ColNo)));                                                     
		     	case (java.sql.Types.BINARY):                                                                       
		     		return ("-BIN-");                                                                              
		     	case (java.sql.Types.BIT ):                                                                         
		     		return (String.valueOf(r.getBoolean(ColNo)));                                                  
		     	case (java.sql.Types.CHAR ): 
		     		String s1=r.getString(ColNo);
					byte b1[]=s1.getBytes();
					TA2.appendText("\n"+rsmd.getColumnName(ColNo)+"=");		     		
					for (i=0; i<b1.length; i++)
						TA2.appendText(b1[i]+" ");
					
		     		return (s1);  
		     		                                                              
		     	case (java.sql.Types.DATE ):                                                                        
		     		return (String.valueOf(r.getDate(ColNo)));                                                     
		     	case (java.sql.Types.DECIMAL):                                                                      
		     		                                                                                               
		     		return (String.valueOf(r.getBigDecimal(ColNo, rsmd.getScale(ColNo))));                         
		     	case (java.sql.Types.DOUBLE):                                                                       
		     		return (String.valueOf(r.getDouble(ColNo)));                                                   
		     	case (java.sql.Types.FLOAT):                                                                        
		     		return (String.valueOf(r.getDouble(ColNo)));                                                   
		     	case (java.sql.Types.INTEGER):                                                                      
		     		return (String.valueOf(r.getInt(ColNo)));                                                      
		     	case (java.sql.Types.LONGVARBINARY):                                                                
		     		return ("-LVB-");                                                                              
		     	case (java.sql.Types.LONGVARCHAR):                                                                  
		     		return ("-LVC-");                                                                              
		     	case (java.sql.Types.NULL):                                                                         
		     		return ("-NUL-");                                                                              
		     	case (java.sql.Types.NUMERIC):                                                                      
		     		return (String.valueOf(r.getBigDecimal(ColNo, rsmd.getScale(ColNo))));                         
		     	case (java.sql.Types.OTHER):                                                                        
		     		return (r.getObject(ColNo).toString());                                                        
		     	case (java.sql.Types.REAL):                                                                         
		     		return (String.valueOf(r.getFloat(ColNo)));                                                    
		     	case (java.sql.Types.SMALLINT):                                                                     
		     		return (String.valueOf(r.getShort(ColNo)));                                                    
		     	case (java.sql.Types.TIME):                                                                         
		     		return (String.valueOf(r.getTime(ColNo)));                                                     
		     	case (java.sql.Types.TIMESTAMP):                                                                    
		     		return (r.getTimestamp(ColNo).toString());                                                     
		     	case (java.sql.Types.TINYINT):                                                                      
		     		return (String.valueOf(r.getByte(ColNo)));                                                     
		     	case (java.sql.Types.VARBINARY):                                                                    
		     		return ("-VB-");                                                                               
		     	case (java.sql.Types.VARCHAR):   
		     		String s2=r.getString(ColNo); 		     		                                                                  
		     		return (s2.substring(0,(s2.length()<8 ? s2.length() : 8)));  // set MaxWidth=8                                                         
		     }                                                                                                        
		} catch (SQLException ex)		     
		{	showSQLError(ex);
		}
			
		return "---";
	}
	//--------------------------------
	
	//---------------------------
	/** show SQL error message
	*/
	public void showSQLError(SQLException ex) 
	{
		SQLerror = "";
		while (ex != null) {  
				
			SQLerror = SQLerror + "\n*** SQLException caught ***"        
			     +"\nSQLState: " + ex.getSQLState ()
			     +"\nMessage:  " + ex.getMessage ()
			     +"\nVendor:   " + ex.getErrorCode (); 
			ex = ex.getNextException ();     
		}                                                                               	
          TA2.appendText(SQLerror);                                                                                                                     	
     }               
	// ------------------------------------
	/** connect to database via JDBC-ODBC bridge      
	*/
	public void openDB(String url, String username, String password) 
	{
     	try 
     	{           
          	Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");                		 
                                                                           
               DriverManager.setLogStream(                                       
               	new PrintStream(                                              
               		new DataOutputStream(                                    
               			new FileOutputStream("JDBCLog.txt"))));             
                                                                               
                                                                                
               con = DriverManager.getConnection (                               
                       url, username, password);                                        
                                                                              
               dma = con.getMetaData ();                                         
                                                                                 
               String dmaString = 
               "\nConnected to " + dma.getURL()                                  
               +"\nDriver       " +  dma.getDriverName()                         
               +"\nVersion      " +  dma.getDriverVersion();                      
                                 
               TA2.setText(dmaString);
		}
		catch (SQLException ex) {
			showSQLError(ex);
          }  
          catch (java.lang.Exception ex) {
               ex.printStackTrace ();
          }
	}                                                                                                                      
	//--------------------------- 
	public static void main(String args[]) 
	{ 
		new 	SQL2(); 
	} 
}