import java.awt.*;
//import java.lang.*;
import java.io.*;
import sun.io.*;
import java.sql.*;
import java.util.*;
import java.math.*;
   	
  
class SQL1 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="";

	//----------------------------------
	SQL1 ()
	{
		setLayout(new BorderLayout());
		setSize(800,500);
		setTitle("SQL");
		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 {
				if (!con.isClosed())                                                                                                 	
					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);
					SQLStr = toTwSQL(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 {
		     		if (!con.isClosed())                                                                                  
		     			con.close();                                                                
		     	} catch (SQLException ex)	                                                   
		     	{	showSQLError(ex);                                                           
		     	}                                                                                
		     	System.exit(0);                                                                  
		     }                                                                                     
		     else if (e.target==BT_reconnect)  // reconnect current database                                                    
		     {                                                                                     
		     	try {
		     		if (!con.isClosed())                                                                            
		     			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                                                                         
		     		{    if (!con.isClosed())                                                                             
		     				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;                                                                                                    			
	} 
	// -------------------------
	/** for reading Chinese char in ResultSet of JDBC
	*/
	public String getTwString(ResultSet r, String columnName) 
	{	
		
		try {
			byte[] b = r.getBytes(columnName);
			return(new String(b));
		}	
		catch (SQLException ex)	
		{
			showSQLError(ex);
		}	
		catch (NegativeArraySizeException ex) // if null data
		{
			return ("");
		}
		return("");	
	}
	//---------------------------------------
	public String getTwString(ResultSet r, int ColumnNo)
	{	
		try {
			byte[] b = r.getBytes(ColumnNo);			
			return(new String(b));
		}	
		catch (SQLException ex)	
		{
			showSQLError(ex);
		}	
		catch (NegativeArraySizeException ex) // if null data
		{
			return ("");
		}
		return("");
	}
	//----------------------------------------------------------
	/** for reading Chinese char in ResultSet of JDBC
	*   for SQL type VARCHAR
	*/
	public String getTwString(ResultSet r, 
						String ColumnName,
						int MaxWidth)
	{	
		
		try {
			byte[] b = r.getBytes(ColumnName);
			if (b.length > MaxWidth)
				return (new String(b,0,MaxWidth))+"..";
			else
				return (new String(b));
		}	
		catch (SQLException ex)	
		{
			showSQLError(ex);
		}	
		catch (NegativeArraySizeException ex) // if null data
		{
			return ("");
		}
		return("");	
	}
	//----------------------------------------------------------
	public String getTwString(ResultSet r, 
						int ColumnNo,
						int MaxWidth)
	{	
		
		try {
			byte[] b = r.getBytes(ColumnNo);
			if (b.length > MaxWidth)
				return (new String(b,0,MaxWidth))+"..";
			else
				return (new String(b));
		}	
		catch (SQLException ex)	
		{
			showSQLError(ex);
		}	
		catch (NegativeArraySizeException ex) // if null data
		{
			return ("");
		}
		return("");	
	}
	//----------------------------------
	/** translate SQL statement with Chinese char to JDBC compatible format
	*/
	public String toTwSQL(String s)
	{
		int pos0=0;
		int pos1=0;
		int pos2=0;
		int pos3=0;
		int len = s.length();
		String ret="";	
		try {
			if (s.indexOf("'")>0) // finding ' in SQL statement                                                                          
			{                                                                                                                            
		     	while (pos2<len)                                                                                                        
		     	{	                                                                                                                   
		     		pos1=s.indexOf("'", pos0);                                                                                         
		     		if (pos1 > 0)                                                                                                      
		     		{                                                                                                                  
		     			pos2=s.indexOf("''", pos1+1);                                                                                  
		     			
		     			if (pos2>0)		     			                                                                                                    
		     			{
		     				while ((pos2 > 0) && (pos2+2<=len))
		     				{    
		     					pos3=pos2+2;
		     					pos2=s.indexOf("''", pos2+2); 
		     				}
		     			}		
		     			else                                                                                                  
		     				pos3=pos1+1;
		     			
		     			pos2=s.indexOf("'", pos3);                                                                        
		     			if (pos2>0)                                                                                              
		     			{
		     				ret += s.substring(pos0,pos1)                                                                       
		     					+ charList(s.substring(pos1+1, pos2));                                                         
		     				pos0=pos2+1;	                                                                                     
		     				pos2=pos0;                                                                                          
		     			}
		     			else
		     			{
		     				pos2=len;
		     				ret = s;
		     			}	
		     		                                                                                                         
		     		}                                                                                                                  
		     		else                                                                                                               
		     		{                                                                                                                  
		     			ret += s.substring(pos0);                                                                                     
		     			pos2=len;                                                                                                     
		     		}                                                                                                                  
		     		                                                                                                                   
		     	}                                                                                                                       
		     	TA2.appendText("\n TwSQL="+ret);                                                                                        
		     }	                                                                                                                        
		     else                                                                                                                         
		     {                                                                                                                            
		     	ret = s; // return original SQL statement                                                                               
		     }                                                                                                                            
		}
		catch (StringIndexOutOfBoundsException ex)		     
		{	TA2.appendText("\nString index out of bounds in toTwSQL()");
		}
	     return ret;                                                                                                                  
		
		
	}
	//---------------------------------
	
	/** translate Chinese char to JDBC compatible format
	*/
	public String charList(String s) 
	{
		int i=0;
		byte[] b = s.getBytes();  // save Chinese string to byte array
		String ret="";
		if (b.length>0)
		{
			ret =CharFunc+"("+(b[0]<0 ? (int)(b[0])+256 : b[0])+")";
			
			for (i=1; i<b.length; i++)
			{
				if ((b[i]==39) && (b[i-1]==39) )	i++; // remove one ' when two '
				ret +="+"+CharFunc+"("+(b[i]<0 ? (int)(b[i])+256 : b[i])+")";
			}
		}
		else
			ret = "''";   // return blank string
		
		return(ret); // return string such as "CHR(xxx)+CHR(yyy)+ ...." 
	}  
	//----------------------------------------
	/** 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)
	{
		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 ):                                                                        
		     		return (getTwString(r, ColNo));                                                                
		     	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):    		     		                                                                  
		     		return (getTwString(rs, ColNo, 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 	SQL1(); 
	} 
}