import java.sql.*;
import java.io.*;

public class Table1  {
	
	
	private String dsn;
	private Connection conn = null;
	
	public Table1(String database)
	{
		dsn     = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + database;
    
    	try {
      		Driver d = (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
      		conn = DriverManager.getConnection ( dsn, "",  "");
    	} catch (Exception e) {
      		System.out.println("Failed to load JDBC/ODBC driver.");
      		System.out.println(e);
      	
    	}
    
	}
	
	
	
	
	public void createTables()
	{
		String query = "";
		
		if (conn != null)
		{
			try {
				Statement s = conn.createStatement();
				
				try {
					query = "DROP Table Accounts";
					s.executeUpdate(query);	
					query = "DROP Table Transactions";
					s.executeUpdate(query);	
					query = "DROP Table StockQuotes";
					s.executeUpdate(query);	
					query = "DROP Table StockHistory";
					s.executeUpdate(query);	
				} catch (Exception e)
				{
				}
			
			
				query = "CREATE 	TABLE Accounts (CustomerId VARCHAR(16), " +
					"CustomerName VARCHAR(32), OpeningDate INTEGER, OpeningBalance DOUBLE) ";
			
				s.executeUpdate(query);	
				
				query = "CREATE TABLE Transactions (CustomerId VARCHAR(16), " +
					"TransactionDate INTEGER, TransactionType VARCHAR(16), StockSymbol VARCHAR(16), " +
					"Quantity DOUBLE, Price DOUBLE) ";
					
				s.executeUpdate(query);	
				
				query = "CREATE 	TABLE StockQuotes (StockSymbol VARCHAR(16), " +
					"StockPrice DOUBLE) ";
			
				s.executeUpdate(query);	
				
				query = "CREATE 	TABLE StockHistory (StockSymbol VARCHAR(16), " +
					"SDate INTEGER, StockPrice DOUBLE) ";
			
				s.executeUpdate(query);	
				
				s.close();
			} catch (Exception e)
			{
				System.out.println(e);	
			}
		}	
		
	}
	

	public void insertData()
	{
		String query;
		if (conn != null)
		try {
				Statement s = conn.createStatement();
				query = "INSERT INTO Accounts VALUES ('001-12-1234', 'Suresh Kalathur', 5, 1000.0)";
				s.executeUpdate(query);	
				query = "INSERT INTO Accounts VALUES ('001-13-2345', 'George Bush', 10, 10000.0)";
				s.executeUpdate(query);	
				query = "INSERT INTO Accounts VALUES ('001-14-3456', 'Bill Clinton', 7, 5000.0)";
				s.executeUpdate(query);	
				
				query = "INSERT INTO Transactions VALUES ('001-12-1234',  5, 'Buy', 'IBM', 10, 70.0)";
				s.executeUpdate(query);	
				query = "INSERT INTO Transactions VALUES ('001-12-1234',  6, 'Deposit', null, null, 2000.0)";
				s.executeUpdate(query);	
				query = "INSERT INTO Transactions VALUES ('001-12-1234',  7, 'Buy', 'MSFT', 10, 50.0)";
				s.executeUpdate(query);	
				query = "INSERT INTO Transactions VALUES ('001-12-1234',  8, 'Sell', 'MSFT', 5, 52.0)";
				s.executeUpdate(query);
				query = "INSERT INTO Transactions VALUES ('001-12-1234',  8, 'Sell', 'IBM', 7, 65.0)";
				s.executeUpdate(query);
				
				query = "INSERT INTO StockQuotes VALUES ( 'IBM', 65.0)";
				s.executeUpdate(query);
				query = "INSERT INTO StockQuotes VALUES ( 'MSFT', 72.0)";
				s.executeUpdate(query);
				
				for (int i = 1; i <= 30; i++)
				{
					query = "INSERT INTO StockHistory VALUES ( 'IBM', " + i + ", " + (60.0 + i) + ")";
					s.executeUpdate(query);
					query = "INSERT INTO StockHistory VALUES ( 'MSFT', " + i + ", " + (45.0 + i) + ")";
					s.executeUpdate(query);
				}
				
				
				
				s.close();
			
			
			} catch (Exception e)
			{
				System.out.println(e);	
			}	
		
	}
	


	public void close()
	{
		if (conn != null)
			{
				
				try {
						conn.close();	
					}
				catch (Exception e)
				{
						System.out.println(e);		
				}
			}	
	}
	
	
	public static void main(String[] args)
	{
		String database = "c:\\temp\\brokerage.mdb";
		if (args.length == 1)
			database = args[0];
		
		File f = new File(database);
		if (f.exists() == false)
		{
			System.out.println("Database " + database + " does not exist");
			System.exit(0);	
		}
		Table1 app = new Table1(database);
		app.createTables();
		app.insertData();
		app.close();
			
	}
	
	
	
		
	
	
}