//
// creating a small table that we can safely modify and play
// with in redwood Oracle student database, java account
// (let's make a sequence for setting its key, too)
//
// also an example of Statement class executeUpdate() method,
// used for executing SQL insert, update, delete commands,
// and SQL commands (such as create table!) that return nothing.
//
// modified from/inspired by a small method in Sunderraman, "Oracle 8
// Programming: A Primer", Addison-Wesley, pp. 222-223
//
// modified by: Sharon M. Tuttle
// last modified: 11-10-00

import java.sql.*;

public class CreateTable480
{
	public static void main(String[] args)
	{
		Statement	myStmt = null;
		String		mySQLCmd;
		Connection	con = null;
		
		try
		{
			// load driver, connect to student db on redwood
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection
                                ("jdbc:oracle:thin:@redwood:1521:student",
                                 "java", "java");

			// set up statement
			myStmt = con.createStatement();

			// first command: let's drop the
			// current version of the table, if any
			mySQLCmd = "drop table Table480";

			// note that I use executeUpdate() here instead
			// of executeQuery() --- drop table does
			// note return a ResultSet, after all --- it
			// does not even return anything...!
			try
			{
				myStmt.executeUpdate(mySQLCmd);
			}
			catch (SQLException exc)
			{
				System.out.println("CreateTable480: "
					+ "could not drop table.");
				// this looked intriguing, in 
				// Sunderraman's example...
				while (exc != null)
				{
					System.out.println("Message: " +
						exc.getMessage());
					exc = exc.getNextException();
				}
				// I do not WANT to close statement
				// or connection here --- I want to
				// keep going. Can I? looks like it! 8-)
			}

			// now let's try to create the table
			mySQLCmd = "create table Table480" +
				   "(item_num   integer, " +
				   " item	varchar2(15), " +
				   " flavor	varchar2(10), " +
				   " quantity   integer, " +
				   " primary key(item_num))";

			// again, executeUpdate() is the appropriate
			// method --- no value returned by creating
			// a table
			try
			{
				myStmt.executeUpdate(mySQLCmd);
			}
			catch (SQLException exc)
			{
				System.out.println("CreateTable480: "
					+ "could not create table.");
				// this looked intriguing, in
				// Sunderraman's example...
				while (exc != null)
				{
					System.out.println("Message: " +
						exc.getMessage());
					exc = exc.getNextException();
				}
				myStmt.close();
				con.close();
				// I want to exit if cannot create
				// dropped table;
				System.exit(0);
			}

			// drop sequence if it already existed
			mySQLCmd = "drop sequence table480_seq";

			try
			{
				myStmt.executeUpdate(mySQLCmd);
			}
			catch (SQLException exc)
			{
				System.out.println("CreateTable480: " +
					"could not drop sequence.");
				while (exc != null)
				{
					System.out.println("Message: " +
						exc.getMessage());
					exc = exc.getNextException();
				}
				// I want to keep going, too,
				// even if seq couldn't be dropped
			}

			// set up creation of sequence
			mySQLCmd = "create sequence table480_seq " +
				   "increment by 1 " +
				   "start with 100";

			// again, here executeUpdate() is the appropriate
			// method, rather than executeQuery() --- nothing
			// is returned;
			try
			{
				myStmt.executeUpdate(mySQLCmd);
			}
			catch (SQLException exc)
			{
				System.out.println("CreateTable480: " +
					"could not create sequence.");
				while (exc != null)
				{
					System.out.println("Message: " +
						exc.getMessage());
					exc.getNextException();
				}
				myStmt.close();
				con.close();
				// I want to exit if cannot create sequence
				System.exit(0);
			}
		}
		catch (Exception exc)
		{
			System.out.println(exc);
		}
		// will this help me make SURE to close everything?
		finally
		{
			System.out.println("CreateTable480: closing "
				+ "statement and connection objects");
			try
			{
				if (myStmt != null)
				{
		
					myStmt.close();
				}

				if (con != null)
				{
					con.close();
				}
			}
			catch (Exception exc)
			{
			}
		}	
	}
}