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
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))");
		}

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

Your DDL statements seems oracle syntax. It wont work with access database.

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.

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.