import java.sql.*;
import java.util.*;
import java.io.*;
import java.text.SimpleDateFormat;

class main
{
	static Connection conn = null;
	static Properties prop = null;
	
	public static void main(String [] args)
	{
		try
		{
			if(args.length > 0) prop = loadProp(args[0]);
			else usage();
			String url = prop.getProperty("url");
			String user = prop.getProperty("user");
			String pass = prop.getProperty("pass");
			String driver = prop.getProperty("driver");
			Class.forName(driver);
			conn = DriverManager.getConnection(url,user,pass);
			conn.setAutoCommit(false);
			createTable();
			executeInsert(1,"expected to succeed");
			executeInsert(1,"expected to fail");
			executeInsert(2,"expected to fail");
			conn.commit();
			dumpTable("tempextest");
			conn.commit();
			dropTable();
			conn.commit();
			conn.close();
			log("all done");
		}

		catch(Exception ex)
		{
			log(ex);
			System.exit(1);
		}
	}

	static void executeInsert(int id, String msg)
	{
		log("executeInsert, id[" + id + "] msg[" + msg + "]");
		PreparedStatement stmt = null;
		try{
		stmt = conn.prepareStatement("INSERT INTO tempextest(id,msg) VALUES(?,?)");
		stmt.setInt(1,id);
		stmt.setString(2,msg);
		stmt.executeUpdate();
		conn.commit();
		} catch (SQLException sqlex) {
			log(sqlex);
			closeStatement(stmt);
		} 
	}

	static void createTable()
	{
		log("Creating table tempextest");
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement("CREATE TABLE tempextest(id INT PRIMARY KEY, msg VARCHAR(20))");
			stmt.executeUpdate();	
		} catch (SQLException sqlex) {
			log(sqlex);
			log("error creating table tempextest, can't proceed");
			closeStatement(stmt);
			System.exit(1);

		}
		log("Table tempextest created");
	}

	static void dropTable()
	{
		log("Dropping table tempextest");
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement("DROP TABLE tempextest");
			stmt.executeUpdate();
		} catch (SQLException sqlex) {
			log(sqlex);
			closeStatement(stmt);
		}
		log("Table tempextest dropped");
	}

	static Properties loadProp(String fileName)
	{
		try{
		Properties prop = new Properties();
		FileInputStream fis = new FileInputStream(fileName);
		prop.load(fis);
		return prop;
		} catch (Exception ex) {
			log("exception loading properties: " + ex);
			usage();
		}
		return null;
	}

	static void usage()
	{
		System.err.println("Usage: java main propfile");
		System.exit(1);
	}

	static void log(String msg)
	{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S");
		System.out.println(sdf.format(new java.util.Date()) + ":" + msg);
	}

	static void log(Exception ex)
	{
		log(ex.toString());
		ex.printStackTrace();
	}

	static void log(Object o)
	{
		log(o.toString());
	}

	static void closeStatement(Statement st)
	{
		try{
			st.close();
		}catch(SQLException sqlex) {
			log(sqlex);
		}
	}

	static void dumpTable(String tableName)
	{
		log("dumping contents of table " + tableName);
		try{
			Statement s = conn.createStatement();        
			ResultSet rs = s.executeQuery("SELECT * FROM " + tableName);
			ResultSetMetaData rsmd = rs.getMetaData();
			for(int i = 1; i <= rsmd.getColumnCount(); i++)
			{
				System.out.print(rsmd.getColumnName(i) + "\t");
					if(i != rsmd.getColumnCount())
						System.out.print("|");	
			}
			System.out.println("\n---------------------------------------------------------------------");
			while(rs.next())
			{
				for(int i = 1; i <= rsmd.getColumnCount(); i++)
				{
					System.out.print(rs.getString(i) + "\t");
						if(i != rsmd.getColumnCount())
							System.out.print("|");	
				}
				System.out.println();
			}
			rs.close();
			s.close();
		}catch(SQLException sqlex) {
			System.err.println(sqlex);
		}
	}

}