0

Good Day All, I have a problem with a database program. The program is to create a database for a Stocktracker program. The details for the code, I got directly from our textbook, and it compiles perfectly, but as soon as I run the program it gives me the following runtime errors:
Exception creating UserStocks table: [Microsoft][ODBC Microsoft Access Driver] S
yntax error in CREATE TABLE statement.
Exception creating UserStocks index: [Microsoft][ODBC Microsoft Access Driver] C
annot find table or constraint
It is clear to me that something is going wrong with the creation of the Table, and I am not sure what it is that is wrong. Can anyone please assist me to help me understand what it is that I am doing wrong and what I should do differently ... and even perhaps, why?
I am attaching the code to create the database as well:

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

public class MakeDB
{
	public static void main(String[] args) throws Exception
	{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

		String url = "jdbc:odbc:StockTracker";

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

		// The following code deletes each index and table, if they exist.
		// If they do not exist, a message is displayed and execution continues.
		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 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 Stocks table with primary key index
		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 Users table with primary key index
		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());
		}

		// Create UserStocks table with foreign keys to Users and Stocks tables
		try
		{
			System.out.println("Creating UserStocks table ... ");
			stmt.executeUpdate("CREATE TABLE UserStocks ("
			                  + "userID TEXT(20) "
			                  + "CONTRAINT FK1_UserStocks REFERENCES Users (userID), "
			                  + "symbol TEXT(8), "
			                  + "CONSTRAINT FK2_UserStocks FOREIGN KEY (symbol) "
			                  + "REFERENCES Stocks (symbol))");
		}
		catch (Exception e)
		{
			System.out.println("Exception creating UserStocks table: "
			                  + e.getMessage());
		}

		// Create UserStocks table primary key index
		try
		{
			System.out.println("Creating UserStocks table primary key index ... ");
			stmt.executeUpdate("CREATE UNIQUE INDEX PK_UserStocks "
			                  + "ON UserStocks (userID, symbol) "
			                  + "WITH PRIMARY DISALLOW NULL");
		}
		catch (Exception e)
		{
			System.out.println("Exception creating UserStocks index: "
			                  + e.getMessage());
		}


		// Create one administrative user with password as initial data
		String userID = "admin01";
		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());
		}

		pStmt.close();

		// 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 databae ... \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("Administratvie   = "+ rs.getBoolean("admin"));
			System.out.println("Initial password = "+ initialPswd);

			// Do NOT use with JDK 1.2.2 using JCBC - ODBC bridge as
			// SQL NULL data value is not handled correctly
			   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(" Expiring now     = "+ pswdFromDB.isExpiring());
				   System.out.println(" Remaining uses   = "
				                     + pswdFromDB.getRemainingUses() + "\n");
			   }
			   else
			   		System.out.println("Password Object = NULL!");
		}

		rs = stmt.executeQuery("SELECT * FROM Stocks");
		if(!rs.next())
			System.out.println("Stocks table contains no records.");
		else
			System.out.println("Stocks table still contains records!");

		rs = stmt.executeQuery("SELECT * FROM UserStocks");
		if(!rs.next())
			System.out.println("UserStocks table contains no records.");
		else
			System.out.println("UserStocks table still contains records!");

		stmt.close(); // closing Statment also closes ResultSet

	} // end of main()

	// Method to write object to byte array and then insert into prepared statement
	public static byte[] serializeObj(Object obj)
	                          throws IOException
	{
		ByteArrayOutputStream baOStream = new ByteArrayOutputStream();
		ObjectOutputStream objOStream = new ObjectOutputStream(baOStream);

		objOStream.writeObject(obj); // object must be Serializable
		objOStream.flush();
		objOStream.close();
		return baOStream.toByteArray(); // returns stream as byte array
	}

	// Method to read bytes from result set into a byte array and then
	// create an input stream and read the data into an object
	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(); // throws IOException, ClassNotFoundException
		}
		return obj;
	}
} // end of class
3
Contributors
5
Replies
6
Views
7 Years
Discussion Span
Last Post by suretd
0
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());
		}

		// Create UserStocks table with foreign keys to Users and Stocks tables
		try
		{
			System.out.println("Creating UserStocks table ... ");
			stmt.executeUpdate("CREATE TABLE UserStocks ("
			                  + "userID TEXT(20) "
			                  + "CONTRAINT FK1_UserStocks REFERENCES Users (userID), "
			                  + "symbol TEXT(8), "
			                  + "CONSTRAINT FK2_UserStocks FOREIGN KEY (symbol) "
			                  + "REFERENCES Stocks (symbol))");
		}
0

Good Day, I am still struggling with this code. I have deleted everything and started from scratch and I still get the same errors. If there is anyone out there that can assist and give me ideas, please do. I have gone and tried to manually create the tables as well, but this also did not work.

Any help and advise would be greatly appreciated.

Thank You

0

Hi All,

This code creates the first two tables within the Database (User & Stocks) but the third table (UserStocks) does not get created. I have tried to create it manually as well, by opening up the Access Database and inserting the table. I do not know how to create the primary key and foreign keys. But this did not help either, i still get error when i run the program.

I just do not understand why the first two tables get created but not the third.

0

Hi All,

I have found my error ... i was missing an "s" in the word CONSTRAINT in creating the UserStocks table.

I feel very silly ... i have missed it again and again and again ... perhaps i need new glasses ... or some sleep ...

Thanks all for you posts ... i have learned some valuable info and tips in the process.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.