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

public class MyDb {

	public Connection con;
	public String errStr;
	public String lastQuery = "";
	public String pointReached = "";
	public String currentOS = "";
	public String lastID = "";
	public MyDb(){
		try {
			//Load the JDBC-ODBC bridge,
			//I have this set up to load ODBC when on windows,
			//MySQL otherwise.
			if (System.getProperty("os.name").equals("Windows 2000")){
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				this.currentOS = "windows";
			}
			else {
				Class.forName("org.gjt.mm.mysql.Driver");
			}

		}
		catch (Exception e) {
			this.errStr = "JDBC driver failed to load.\n";
			this.errStr = e + "";
		}
		try {

			if (System.getProperty("os.name").equals("Windows 2000")){
				//get a connection
				this.con = DriverManager.getConnection
				("jdbc:odbc:your_dsn_here","","");

			}
			else {
				this.con = DriverManager.getConnection
				("jdbc:mysql://your_host:3306/dbName","dbUser","dbPass");
			}

		}
		catch (Exception e){
			this.errStr = "JDBC driver loaded, couldn't get statement.\n";
			this.errStr = e + "";
		}
		this.errStr = null;
	}

	public Statement getHandle(){
		try {
			return this.con.createStatement();
		}
		catch (Exception e){
			this.errStr = "Couldn't get handle: " + e;
			return null;
		}
	}

	public Hashtable getOneRow(ResultSet rs){

		Hashtable ret = new Hashtable();
		Object temp;

		try {

			String cName;
			String cType;

			ResultSetMetaData rsMd = rs.getMetaData();

			int numberOfColumns = rsMd.getColumnCount();
			for (int i = 1; i <= numberOfColumns; i++) {

				cName = "unknown";
				try {
					cName = rsMd.getColumnName(i);
					cType = rsMd.getColumnTypeName(i);


					//types and rs methods are:
					/*
					INTEGER		getInt
					DOUBLE		getDouble
					CHAR		getString
					LONGCHAR	getString
					DATETIME	getDate
					*/
					//finish here by checking the cType
					//and then using the right get method above
					//to put the value into the hash.
					//if (cType == "INTEGER"){
					//	ret.put(cName, new Integer(rs.getInt(i)));
					//}
					//else {
						temp = rs.getObject(i);

						if(temp.getClass().getName() == "java.lang.String"){
							ret.put(cName,temp.toString().trim());
						}
						else {
							ret.put(cName,temp);
						}
					//}
				}
				catch (Exception e){
					ret.put(cName, "");
				}

				//if (cType == "INTEGER"||cType == "COUNTER"){
				//	ret.put(cName, new Integer(rs.getInt(i)));
				//}
				//else if (cType == "Double"){
				//	ret.put(cName, new Double(rs.getDouble(i)));
				//}
				//else if (cType == "DATETIME"){
				//	ret.put(cName,rs.getDate(i));
				//}
				//else {
				//	ret.put(cName, rs.getString(i));
				//}
			}

			return ret;

		}
		catch (Exception e){
			this.errStr = "" + e;
			return null;
		}


	}

	public Hashtable getOneRow(String qString){
		Hashtable ret = new Hashtable();

		try {

			String cName;
			String cType;
			this.lastQuery = qString;
			ResultSet rs = this.getHandle().executeQuery(qString);
			rs.next();
			return this.getOneRow(rs);

		}
		catch (Exception e){
			this.errStr = "" + e;
			return null;
		}

	}

	public Object firstOfFirst(String sql){
		try {
			this.lastQuery = sql;
			ResultSet rs = this.getHandle().executeQuery(sql);
			if(rs.next()){
				return rs.getObject(1);
			}
			else {
				this.errStr = "No Record";
				return null;
			}
		}
		catch (Exception e){
			this.errStr = "" + e;
			return null;
		}


	}

	public Vector getColumnVector(String sql){

		Vector ret = new Vector();

		try {

			ResultSet rs = this.getHandle().executeQuery(sql);
			while (rs.next()){
				ret.addElement(rs.getObject(1));
			}
			return ret;

		}
		catch (Exception e){
			this.errStr = "" + e;
			return null;
		}

	}

	public Vector getRowVector(String qString){

		Vector ret = new Vector();

		try {

			String cName;
			String cType;
			ResultSet rs = this.getHandle().executeQuery(qString);
			//ResultSetMetaData rsMd = rs.getMetaData();
			while (rs.next()){
				ret.addElement(this.getOneRow(rs));
			}
			return ret;

		}
		catch (Exception e){
			this.errStr = "" + e;
			return null;
		}

	}

	public Hashtable getMetaData(String tableName){

		String sql = "select * from " + tableName;
		Hashtable ret = new Hashtable();
		Hashtable temp = new Hashtable();

		try {
			//this would be better, but mysql does not support
			//getMetadata on a prepared statement.
			//PreparedStatement pst = this.con.prepareStatement(sql);
			//ResultSetMetaData rsmd = pst.getMetaData();

			ResultSet rs = this.getHandle().executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();

			for(int i=1; i <= rsmd.getColumnCount(); i++){
				temp = new Hashtable();
				temp.put("type",rsmd.getColumnTypeName(i));
				ret.put(rsmd.getColumnName(i), temp);
			}
			return ret;
		}
		catch (Exception e){
			this.errStr = "" + e;
			return null;
		}



	}
public boolean insert(String table, Vector keys, Hashtable data){

	String statementType = "insert";
	String where = "";
	String fieldName = "";
	Vector wheres = new Vector();
	String type = "";
	String sql = "";
	Enumeration fieldNames;
	String valueList = "";
	String fieldList = "";
	String whereClause = "";

	Hashtable meta = this.getMetaData(table);
	try {
		if (keys != null){
			while (keys.size() > 0){
				fieldName = (String) keys.remove(keys.size()-1);

				if ((data.get(fieldName) != null)&&(!data.get(fieldName).toString().equals(""))){

					type = (String)((Hashtable)meta.get(fieldName)).get("type");
					if (type.equals("INTEGER")||type.equals("COUNTER")||type.equals("LONG")||type.equals("FLOAT")){
						wheres.add(fieldName + " = " + data.get(fieldName));
					}
					else {
						wheres.add(fieldName + " = '" + data.get(fieldName) + "'");
					}
				}
			}
			if (wheres.size() > 0){
				sql = "select * from " + table + " where ";
				while (wheres.size() > 1){
					whereClause += wheres.remove(wheres.size()-1) + " and ";
				}
				whereClause += wheres.remove(wheres.size()-1);
				sql += whereClause;
				this.lastQuery = sql;
				this.pointReached = "seeing if it's an update.";
				ResultSet rs = this.getHandle().executeQuery(sql);
				if (rs.next()){
					statementType = "update";
				}
			}

		} //end if keys not equal null (test for update)
		if (statementType == "insert"){
			sql = "insert into " + table;
			fieldNames = data.keys();
			valueList = "";
			fieldList = "";
			while (fieldNames.hasMoreElements()){
				fieldName = (String) fieldNames.nextElement();


				type = (String)((Hashtable)meta.get(fieldName)).get("type");
				System.out.println(type);
				if(type.equals("COUNTER")){
					continue;
				}
				else if (type.equals("INTEGER")||type.equals("LONG")||type.equals("FLOAT")||type.equals("DOUBLE")){
					if((data.get(fieldName) == null)||(data.get(fieldName).equals(""))){
						//continue;
						valueList += "0, ";
					}
					else {
						valueList += data.get(fieldName) + ", ";
					}
				}
				else {
					valueList += "'" + data.get(fieldName) + "', ";
				}
				fieldList += fieldName + ", ";

			}
			fieldList = fieldList.substring(0,fieldList.length()-2);
			valueList = valueList.substring(0,valueList.length()-2);
			sql += " (" + fieldList + ") values (" + valueList + ")";
		}
		else {
			sql = "update " + table;
			fieldNames = data.keys();
			fieldList = "";
			while (fieldNames.hasMoreElements()){
				fieldName = (String) fieldNames.nextElement();

				type = (String)((Hashtable)meta.get(fieldName)).get("type");
				if (type.equals("COUNTER")){
					continue;
				}


				if (type.equals("INTEGER")||type.equals("LONG")||type.equals("FLOAT")){
					if((data.get(fieldName) == null)||(data.get(fieldName).equals(""))){
						continue;
					}
					fieldList += fieldName + " = " + data.get(fieldName) + ", ";
				}
				else {
					fieldList += fieldName + " = '" + data.get(fieldName) + "', ";
				}


			}
			fieldList = fieldList.substring(0,fieldList.length()-2);
			sql += " set " + fieldList + " where " + whereClause;

		}
		this.lastQuery = sql;
		this.getHandle().executeUpdate(sql);
		if (statementType.equals("insert")){
			if (!this.currentOS.equals("windows")){
				this.lastID = ("" + this.firstOfFirst("select LAST_INSERT_ID()"));
			}
		}

		return true;
	}
	catch (Exception e){
		this.errStr = "" + e;
		return false;
	}

	//return null;
}
	public String myVersion(){
		return "v5";
	}

}