Good Day, I am using an Ms Access Database to design a StockTracker applicaton. The application is used to check the stocks of users. Firstly I need to sign on as an administrator and then be able to add users and their stocks to the database. I have created the database in the program MakeDB.java. I have created another program StockTrackerDB.java that adds new records to the database, updates existing records and deletes old records. Then I use STLogon.java, to sign on to the StockTracker program. When I sign on the first time, I need to sign on as an administrator with the userid: admin01 and the same details for the password. When I run the program STLogon.java, it gives me the logon screen, then I enter the userid and password, but when I press enter, I get the error:
[Microsoft][ODBC Microsoft Access Driver]Syntax error (missing operator) in query expression ‘Pa_RaM000 firstName = Pa_RaM001’
I have searched for the error on the Web and it states that a parameter has been entered incorrectly. It refers to using " instead of ' signs, but I am using " where the firstName parameter is used in the code to create the first administrator (I will color this green). I have been looking in the MakeDB.java code to find the firstName parameter and don't know what it is I need to change here to get the program to work. Or am I looking in the wrong place? The code is very long and there are other programs and classes that also play roles in the execution of the application, which I am not attaching due to the fact that it would clutter the Thread.
The code for MakeDB.java:
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
If anyone could please assist, I would greatly appreciate it. Thank You
