1 Replies - 1580 Views - Last Post: 23 May 2008 - 08:02 PM

#1 NiekieM   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 30
  • Joined: 06-February 08

Creating a new table for an existing database

Post icon  Posted 23 May 2008 - 07:30 AM

Hi guys.I have to add an extra table to an existing database.The errors that are thrown include cannot create StockTrade table, error in CONSTRAINT clause ,cannot create UserStocks table, error in CONSTRAINT clause.Anybody input will be appreciated.
import java.sql.*;
import java.io.*;
import java.util.*;

public class MakeDB
{
	public static void main(String[] args) throws Exception
	{


		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url = "jdbc:odbc:StockTracker2";

		Connection con = DriverManager.getConnection(url);
		Statement stmt = con.createStatement();

		System.out.println("Dropping indexes & tables...");

		try
		{
			stmt.executeUpdate("DROP INDEX PK_UserStocks ON UserStocks");
		}
		catch(Exception e)
		{
			System.out.println("Could not drop primary key on UserStocks table: "
								+ e.getMessage());
		}
		try
		{
			stmt.executeUpdate("DROP TABLE UserStocks");
		}
		catch(Exception e)
		{
			System.out.println("Could not drop UserStocks table: "
										+e.getMessage());
		}


		try
		{
			stmt.executeUpdate("DROP TABLE StockTrade");
		}
		catch (Exception e)
		{
			System.out.println("Could not drop StockTrade Table: "
												+ e.getMessage());
		}


		try
		{
			stmt.executeUpdate("DROP TABLE Users");
		}
		catch (Exception e)
		{
			System.out.println("Could not drop Users Table: "
								+ e.getMessage());
		}

		try
		{
			stmt.executeUpdate("DROP TABLE Stocks");
		}
		catch(Exception e)
		{
			System.out.println("Could not drop Stocks Table: "
										+ e.getMessage());
		}

		///////////Create the database tables/////////////////////////
		System.out.println("\nCreating tables...........");

		//Create Stock Table with primary StockIndex
		try
		{
			System.out.println("Creating Stocks Table with primary key index...");
			stmt.executeUpdate("CREATE TABLE Stocks ("
								+"symbol TEXT(8)NOT NULL  "
								+"CONSTRAINT PK_Stocks PRIMARY KEY, "
								+"name TEXT(50)"
								+")");
		}
		catch(Exception e)
		{
			System.out.println("Exception creating Stocks table: "
								+ e.getMessage());
		}

		//Create User table with primary key StockIndex
		try
		{
			System.out.println("Creating Users table with primary key index...");
			stmt.executeUpdate("CREATE TABLE Users ("
								+"userID TEXT(20) NOT NULL "
								+"CONSTRAINT PK_Users PRIMARY KEY, "
								+"lastName TEXT(30) NOT NULL, "
								+"firstName TEXT(30) NOT NULL, "
								+"pswd LONGBINARY, "
								+"admin BIT"
								+")");
		}
		catch(Exception e)
		{
			System.out.println("Exception creating Users Table: "
								+ e.getMessage());
		}



		try				
		{				
																						
			System.out.println("Creating StockTrade table with primary key index...");	
			stmt.executeUpdate("CREATE TABLE StockTrade ("
								+"StockIndex integer NOT NULL , "
								+"CONSTRAINT PK_StockTrade PRIMARY KEY,"
								+"buy BIT , "
								+"sell BIT , "
								+"pShare double, "
								+"bought integer, "
								+"sold integer, "
								+"pPaid double, "
								+"pReceived double,"
								+"fees double"
								+")");


		}
		catch(Exception e)
		{
			System.out.println("Exception creating StockTrade table: "
								+ e.getMessage());
		}



		//Creating UserStocks table with foreign keys to Users,Stocks and StockTrade
		try
		{
			System.out.println("Creating UserStocks table...");
			stmt.executeUpdate("CREATE TABLE UserStocks ("
								+"userID TEXT(20) NOT NULL, "
								+"CONSTRAINT FK1_UserStocks REFERENCES Users(userID),"
								+"symbol TEXT(8) NOT NULL), "
								+"CONSTRAINT FK2_UserStocks FOREIGN KEY (Symbol) "
								+"REFERENCES Stocks (symbol),"
								+"CONSTRAINT FK3_UserStocks FOREIGN KEY(symbol) "
								+"REFERENCES StockTrade (StockIndex))");




		}
		catch(Exception e)
		{
			System.out.println("Exception creating UserStocks table: "
								+ e.getMessage());
		}

		//Create UserStocks table primary key StockIndex
		try
		{
			System.out.println("Creating UserStocks table primary key StockIndex...");
			stmt.executeUpdate("CREATE UNIQUE INDEX PK_UserStocks "
								+"ON UserStocks (userID, symbol) "
								+"WITH PRIMARY DISALLOW NULL");

		}
		catch(Exception e)
		{
			System.out.println("Exception creating UserStocks StockIndex: "
								+ e.getMessage());
		}


		//Creating one administrative user with password as initial data
		String userID = "niekie01";
		String firstName = "Default";
		String lastName = "Admin";
		String initialPswd = "admin01";
		Password pswd = new Password(initialPswd);
		boolean admin = true;

		PreparedStatement pStmt =
							con.prepareStatement("INSERT INTO Users VALUES(?,?,?,?,?)");

		try
		{
			pStmt.setString(1,userID);
			pStmt.setString(2,lastName);
			pStmt.setString(3,firstName);
			pStmt.setBytes(4,serializeObj(pswd));
			pStmt.setBoolean(5,admin);
			pStmt.executeUpdate();


		}
		catch(Exception e)
		{
			System.out.println("Exception inserting user: "
								+ e.getMessage());
		}

		//Read and display all User data in the database
		ResultSet rs  = stmt.executeQuery("SELECT * FROM Users");

		System.out.println("Database created.\n");
		System.out.println("Displaying data from database...\n");
		System.out.println("Users table contains:");

		Password pswdFromDB;
		byte[] buf = null;

			while(rs.next())
			{
				System.out.println("Logon ID		= "
											+rs.getString("userID"));
				System.out.println("First Name		= "+rs.getString("firstName"));
				System.out.println("Last name		= "+rs.getString("lastName"));
				System.out.println("Administrative		= "+rs.getBoolean("admin"));
				System.out.println("Initial password	= "+initialPswd);

					buf = rs.getBytes("pswd");
					if(buf != null)
					{
						System.out.println("Password Object		= "
											+(pswdFromDB=(Password)deserializeObj(buf)));
						System.out.println("AutoExpires		= "+pswdFromDB.getAutoExpires());
						System.out.println("Remaining uses 		= "
											+pswdFromDB.getRemainingUses()+"\n");
					}
					else
						System.out.println("Password object	  = null!");
				}


		//Creating values for StockTrade
		String symbol = "MSFT";
		String name = "Microsoft Computers";

		pStmt = con.prepareStatement("INSERT INTO Stocks VALUES(?,?)");

		try
		{

			pStmt.setString(1,symbol);
			pStmt.setString(2,name);
			pStmt.executeUpdate();


		}
		catch(Exception e)
		{
			System.out.println("Exception inserting Stocks");
		}





		rs = stmt.executeQuery("SELECT * FROM Stocks");
		while(rs.next())
		{
			System.out.println("Stock symbol		= "+rs.getString("symbol"));
			System.out.println("Description		= "+rs.getString("name"));

		}
		if(!rs.next())
		{
			System.out.println("Stock table contains no records");
		}


		int StockIndex = 1;
		boolean buy = true;
		boolean sell = true;
		double pShare = 20.00;
		int bought = 20;
		int sold = 10;
		double pReceived = 200;
		double pPaid = 400;
		double fees = 0.0;


		pStmt = con.prepareStatement("INSERT INTO StockTrade VALUES(?,?,?,?,?,?,?,?,?)");

		try
		{

			pStmt.setInt(1,StockIndex);
			pStmt.setBoolean(2,buy);
			pStmt.setBoolean(3,sell);
			pStmt.setDouble(4,pShare);
			pStmt.setInt(5,bought);
			pStmt.setInt(6,sold);
			pStmt.setDouble(7,pReceived);
			pStmt.setDouble(8,pPaid);
			pStmt.setDouble(9,fees);
			pStmt.executeUpdate();

		}
		catch(Exception e)
		{
			System.out.println("Exception inserting StockTrade");
		}
		rs = stmt.executeQuery("SELECT * FROM StockTrade");
		while(rs.next())
		{


			System.out.println("StockTrade Table:");
			System.out.println("User name: 		= "+rs.getInt("StockIndex"));
			System.out.println("Buy stocks				="+rs.getBoolean("buy"));
			System.out.println("Sell stocks				="+rs.getBoolean("sell"));
			System.out.println("Price per share			="+rs.getDouble("pShare"));
			System.out.println("Number of shares bought ="+rs.getInt("bought"));
			System.out.println("Number of shares sold	="+rs.getInt("sold"));
			System.out.println("Price received			="+rs.getDouble("pReceived"));
			System.out.println("Price paid 				="+rs.getDouble("pPaid"));
			System.out.println("Fees paid				="+rs.getDouble("fees"));
		}
		if(!rs.next())
		{
			System.out.println("StockTrade table contains no records");
		}




		pStmt.close();
		stmt.close();
	}

	public static byte[] serializeObj(Object obj) throws IOException
	{
		ByteArrayOutputStream baOStream = new ByteArrayOutputStream();
		ObjectOutputStream objOStream = new ObjectOutputStream(baOStream);

		objOStream.writeObject(obj);
		objOStream.flush();
		objOStream.close();
		return baOStream.toByteArray();
	}



	public static Object deserializeObj(byte[] buf) throws IOException, ClassNotFoundException
	{
		Object obj = null;

		if(buf != null)
		{
			ObjectInputStream objIStream = new ObjectInputStream(new ByteArrayInputStream(buf));

			obj = objIStream.readObject();
		}

		return obj;
	}
}


Is This A Good Question/Topic? 0
  • +

Replies To: Creating a new table for an existing database

#2 dedman   User is offline

  • D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 52
  • Joined: 22-April 05

Re: Creating a new table for an existing database

Posted 23 May 2008 - 08:02 PM

Try declaring the column and its definition then add the constraint

Ex:
PK_Stocks int Auto_Increment Not null,
CONSTRAINT PK_Stocks PRIMARY KEY

It looks as if you are trying to add a constraint to a column that does not exist.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1