package ecmAglet;

import java.sql.*;
import java.io.*;
import java.net.*;
import java.util.Date;
              
public class log{
   private ResultSet rs;
   private Connection con;
   private Statement stmt;
   //private PreparedStatement pstm;
   //private DatabaseMetaData dma;
   
   ///connection database  
   public void Init(String data_source){   	
   	try{
   		DatabaseMetaData dma;
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");		
			con=DriverManager.getConnection("jdbc:odbc:"+data_source);			
	      checkForWarning(con.getWarnings());
	      dma=con.getMetaData();
	      
	      System.out.println("\nConnected to "+dma.getURL());
	      System.out.println("Driver   "+dma.getDriverName());
	      System.out.println("Version  "+dma.getDriverVersion());
	      System.out.println("");
	      	
		}
		catch(ClassNotFoundException e){
	   	System.out.println("ClassNotFoundExeption");
	   }
	   catch(SQLException ex){
			System.out.println("\n***SQLException caught ***\n");
			while (ex != null){
				System.out.println("SQLState:"+ex.getSQLState());
				System.out.println("Message:"+ex.getMessage());	
				System.out.println("Vendor:"+ex.getErrorCode());
				ex=ex.getNextException();
				System.out.println("");
			}
      }
	}
	////checkFor Warning
	private static boolean checkForWarning(SQLWarning warn) throws SQLException{
		boolean rc=false;
		
		if (warn != null){
			System.out.println("\n***Warning***\n");
			rc=true;
			while(warn != null){
				System.out.println("SQLState:"+warn.getSQLState());
				System.out.println("Message:"+warn.getMessage());
				System.out.println("Vendor:"+warn.getErrorCode());
				System.out.println("");
				warn=warn.getNextWarning();
			}
		}
		return rc;
	}
	////oper query
	public ResultSet Sql(String sql){
		Statement stmt;
		ResultSet rs=null;
		//ResultSet rs2=null;
		try{
			
			
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);			
			//rs2=rs;
			//rs.close();
			//stmt.close();
		}		   	
		catch(SQLException ex){
			System.out.println("\n***SQLException caught22222 ***\n");
			while (ex != null){
				System.out.println("SQLState:"+ex.getSQLState());
				System.out.println("Message:"+ex.getMessage());	
				System.out.println("Vendor:"+ex.getErrorCode());
				ex=ex.getNextException();
				System.out.println("");
			}
		}
		
		return rs;
	}

	///add a record         
	public int Add(String add){
		int i=0;
		PreparedStatement pstm;
		try{		
			pstm=con.prepareStatement(add);
         //pstm.setString(1,url);
         //pstm.setString(2,com);
         i=pstm.executeUpdate();  
      	//pstm.close();
      	//rs.close();
			
      }                   
		catch(SQLException ex){
			System.out.println("\n***SQLException caught ***\n");
			while (ex != null){
				System.out.println("SQLState:"+ex.getSQLState());
				System.out.println("Message:"+ex.getMessage());	
				System.out.println("Vendor:"+ex.getErrorCode());
				ex=ex.getNextException();
				System.out.println("");
			}
		}
		
      return i;     	
	}
	
	public int Add(String type,String describe){
		int i=0;
		Date ndate = new Date();
		String logtime=ndate.toString();
		//String logtime="aaaa"
		PreparedStatement pstm;
		try{		/////////
			pstm=con.prepareStatement("insert into log (type,describe,logtime) values ('"
						+type+"','"+describe+"','"+logtime+"')");
			//System.out.println("insert into log (type,describe,logtime) values ('"
			//			+type+"','"+describe+"','"+logtime+"')");
         i=pstm.executeUpdate();  
      	//pstm.close();
      	//rs.close();
			
      }                   
		catch(SQLException ex){
			System.out.println("\n***SQLException caught ***\n");
			while (ex != null){
				System.out.println("SQLState:"+ex.getSQLState());
				System.out.println("Message:"+ex.getMessage());	
				System.out.println("Vendor:"+ex.getErrorCode());
				ex=ex.getNextException();
				System.out.println("");
			}
		}
		
      return i;     	
	}
	///delete records
	public int Clear(String Del){
		int j=0;
		PreparedStatement pstm;
		try{
			pstm=con.prepareStatement(Del);
			j=pstm.executeUpdate(); 
			pstm.close();
			//rs.close();
			
		}
		catch(SQLException ex){
			System.out.println("\n***SQLException caught ***\n");
			while (ex != null){
				System.out.println("SQLState:"+ex.getSQLState());
				System.out.println("Message:"+ex.getMessage());	
				System.out.println("Vendor:"+ex.getErrorCode());
				ex=ex.getNextException();
				System.out.println("");
			}
		}
		
      return j;
	}
	/////
	public int ClearAll(){
		int j=0;
		PreparedStatement pstm;
		try{
			pstm=con.prepareStatement("delete from log");
			j=pstm.executeUpdate(); 
			pstm.close();
			//rs.close();
			
		}
		catch(SQLException ex){
			System.out.println("\n***SQLException caught ***\n");
			while (ex != null){
				System.out.println("SQLState:"+ex.getSQLState());
				System.out.println("Message:"+ex.getMessage());	
				System.out.println("Vendor:"+ex.getErrorCode());
				ex=ex.getNextException();
				System.out.println("");
			}
		}
		
      return j;
	}
   ///display records
   public String[][] Save(ResultSet rs) {
   	int i;
   	String tmpstr;
   	int recordNumber;
   	String[][] recordArray=null;
   	try{
   		ResultSetMetaData rsmd=rs.getMetaData();
   		int numCols=rsmd.getColumnCount();
   		int no=0;///no use for order of record
   		recordNumber=300;
   		recordArray=new String[recordNumber][numCols];
   	
   	//display each column title
   		for(i=1;i<=numCols;i++){
   			recordArray[no][i-1]=rsmd.getColumnLabel(i);
   			
   			//if (i>1) System.out.print(",");
   			//System.out.print(rsmd.getColumnLabel(i));
   		}
   		//System.out.println("");
   	
   	//display each column data
   	
   		while (rs.next()){				
				no++;
				for(i=1;i<=numCols;i++){
					//if (i>1) System.out.print(",");
					tmpstr=rs.getString(i);
					//tmpstr=rs.getObject(i).toString();			
					if(rs.wasNull()){
						//System.out.print("NULL");
						recordArray[no][i-1]="NULL";
						//System.out.print(recordArray[no][i-1]);
					}
					else {
						recordArray[no][i-1]=tmpstr;
						//System.out.print(recordArray[no][i-1]);
					}
				}
				//System.out.println("");
			}
		//rs.close();
		}
		catch(SQLException ex){
			System.out.println("\n***SQLException333 caught ***\n");
			while (ex != null){
				System.out.println("SQLState:"+ex.getSQLState());
				System.out.println("Message:"+ex.getMessage());	
				System.out.println("Vendor:"+ex.getErrorCode());
				ex=ex.getNextException();
				System.out.println("");
			}
		}
		
		return recordArray;			   	
	}
	
	public void display(String[][] recordArray){
		int l=recordArray.length;
		int ll=recordArray[1].length;
		System.out.println("array lenth is :"+l);
		System.out.println("record lenth is :"+ll);
		for (int i=0;i<l;i++){
			for (int j=0;j<ll;j++){
				if (recordArray[i][j]!=null)
					System.out.print(recordArray[i][j]+",");
				//else if break la;
				else return;
			}
			System.out.println(""); ///換行顯示
	 	}
	 	

	}
		
	public void closeConnect(){
		try{
			//rs.close();
			//stmt.close();
			con.close();
		}
		catch(SQLException ex){
			System.out.println("\n***SQLException caught ***\n");
			System.out.println("SQLState:"+ex.getSQLState());
			System.out.println("Message:"+ex.getMessage());	
			System.out.println("Vendor:"+ex.getErrorCode());
      }            	
	}
	public void closeResult(){
		try{
			rs.close();
			stmt.close();
			//con.close();
		}
		catch(SQLException ex){
			System.out.println("\n***SQLException caught ***\n");
			System.out.println("SQLState:"+ex.getSQLState());
			System.out.println("Message:"+ex.getMessage());	
			System.out.println("Vendor:"+ex.getErrorCode());
      }            	
	}
	
	public static void main(String args[])
	{
		log log=new log();
		log.Init("Ecmdb");
		//log.Add("err","aaaaaaaa");	
		log.Add("insert into log (type,describe,logtime) values ('aaaqq','bbbqqqq','cccqq')" );
		log.Add("err","aaaaaaaa");		
		
		log.ClearAll();
		log.Sql("select * from log");
	}
}