StockTrackerDB login error

[Matchuel]

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 classpublic class MakeDB
{
    public static void main(String[] args) throws Exception
    {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        String url <strong class="highlight">=</strong> "jdbc:odbc:StockTracker";

        Connection con <strong class="highlight">=</strong> DriverManager.getConnection(url);
        Statement stmt <strong class="highlight">=</strong> 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 <strong class="highlight">=</strong> "admin01";
        String <strong class="highlight">firstName</strong> <strong class="highlight">=</strong> "Default";
        String lastName <strong class="highlight">=</strong> "Admin";
        String initialPswd <strong class="highlight">=</strong> "admin01";
        Password pswd <strong class="highlight">=</strong> new Password(initialPswd);
        boolean admin <strong class="highlight">=</strong> true;

        PreparedStatement pStmt <strong class="highlight">=</strong>
                   con.prepareStatement("INSERT INTO Users VALUES (?,?,?,?,?)");
        try
        {
            pStmt.setString(1, userID);
            pStmt.setString(2, lastName);
            pStmt.setString(3, <strong class="highlight">firstName</strong>);
            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 <strong class="highlight">in</strong> the database.
        ResultSet rs <strong class="highlight">=</strong> 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 <strong class="highlight">=</strong> null;

        while(rs.next())
        {
            System.out.println("Logon ID         <strong class="highlight">=</strong> "
                              + rs.getString("userID"));
            System.out.println("First name       <strong class="highlight">=</strong> "
                              + rs.getString("firstName"));
            System.out.println("Last name        <strong class="highlight">=</strong> "+ rs.getString("lastName"));
            System.out.println("Administratvie   <strong class="highlight">=</strong> "+ rs.getBoolean("admin"));
            System.out.println("Initial password <strong class="highlight">=</strong> "+ initialPswd);

            // Do NOT use with JDK 1.2.2 using JCBC - ODBC bridge as
            // SQL NULL data value is not handled correctly
               buf <strong class="highlight">=</strong> rs.getBytes("pswd");
               if (buf != null)
               {
                   System.out.println("Password Object   <strong class="highlight">=</strong> "
                                     + (pswdFromDB=(Password)deserializeObj(buf)));
                   System.out.println(" AutoExpires      <strong class="highlight">=</strong> "+ pswdFromDB.getAutoExpires());
                   System.out.println(" Expiring now     <strong class="highlight">=</strong> "+ pswdFromDB.isExpiring());
                   System.out.println(" Remaining uses   <strong class="highlight">=</strong> "
                                     + pswdFromDB.getRemainingUses() + "\n");
               }
               else
                    System.out.println("Password Object <strong class="highlight">=</strong> NULL!");
        }

        rs <strong class="highlight">=</strong> 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 <strong class="highlight">=</strong> 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 <strong class="highlight">=</strong> new ByteArrayOutputStream();
        ObjectOutputStream objOStream <strong class="highlight">=</strong> 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 <strong class="highlight">=</strong> null;

        if (buf != null)
        {
            ObjectInputStream objIStream <strong class="highlight">=</strong>
              new ObjectInputStream(new ByteArrayInputStream(buf));

            obj <strong class="highlight">=</strong> objIStream.readObject(); // throws IOException, ClassNotFoundException
        }
        return obj;
    }
} // end of class

Please assist!!!

[stephen84s]

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’

Can you please post the complete error you are getting or the complete query you are using, I cant seem to trace it in your code.

Now making a blind guess here, it appears your SQL Statement syntax is invalid, Whenever you are testing a column in a database table for any string value, the value should be enclosed within single quotes as show below:-

SELECT *
FROM User
WHERE UserName = 'xyz' AND Password = 'yza';

Now consider if you value "xyz" is coming from a string variable and you are constructing the query from with your Java program, then it would look like this:-

String user = "xyz";
ResultSet rs = st.executeQuery("SELECT * " + 
    "FROM User WHERE UserName ='" + user + "'");

Where "st" is of type java.sql.Statement.
Or you could alternatively opt for using PreparedStatement and get rid of this single quote issue permanently.

[Matchuel]

here's a full code for the StockTrackerDB.java and the Error im getting when i logon to the STLogon.java(also attached below) states-

[Microsoft][ODBC Microsoft Acess Driver] Syntax error (missing operator) in query expression 'Pa_RaM000 fistName = Pa_RaM001'.

import javax.swing.*;
import javax.swing.border.TitledBorder;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.io.*;
import java.sql.*;

public class STLogon extends JFrame implements ActionListener, Activator
{
    StockTrackerDB db;
    User user = null;
    String userID;
    String password;

    JTextField userIDField;
    JPasswordField passwordField;
    JButton jbtLogon;

    public STLogon()
    {
        super("Stock Tracker"); // call super (JFrame) constructor

        int width = 300;
        int height = 100;

        try{
            db = new StockTrackerDB();
        }
        catch(ClassNotFoundException ex){
                JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "Class not found exception creating database object",
                JOptionPane.ERROR_MESSAGE);
                System.exit(0);
        }
        catch(SQLException ex){
                JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "SQL exception creating database object",
                JOptionPane.ERROR_MESSAGE);
                System.exit(0);
        }

        // define GUI components
        JLabel label1 = new JLabel("User ID: ");
        userIDField = new JTextField(20);

        JLabel label2 = new JLabel("Password:   ");
        passwordField = new JPasswordField(20);
        passwordField.setEchoChar('*');

        jbtLogon = new JButton("Log on");

        // set up GUI
        JPanel userPanel= new JPanel(new BorderLayout());
        userPanel.add(label1,BorderLayout.CENTER);
        userPanel.add(userIDField,BorderLayout.EAST);

        JPanel pswdPanel= new JPanel(new BorderLayout());
        pswdPanel.add(label2,BorderLayout.CENTER);
        pswdPanel.add(passwordField,BorderLayout.EAST);

        JPanel buttonPanel= new JPanel(new FlowLayout());
        buttonPanel.add(jbtLogon);

        JPanel contentPanel= new JPanel(new BorderLayout());
        contentPanel.add(userPanel, BorderLayout.NORTH);
        contentPanel.add(pswdPanel, BorderLayout.CENTER);
        contentPanel.add(buttonPanel, BorderLayout.SOUTH);
        contentPanel.setBorder(new TitledBorder("Log on"));

        setContentPane(contentPanel);

        // add listeners
        jbtLogon.addActionListener(this);

        addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent e)
                        {
                            try {db.close();
                            }
                            catch(Exception ex)
                            {}
                            System.exit(0);
                        }
            });

        // Enable Enter key for each JButton
        InputMap map;
        map = jbtLogon.getInputMap();
        if (map != null){
            map.put(KeyStroke.getKeyStroke(KeyEvent.VK_ENTER,0,false), "pressed");
            map.put(KeyStroke.getKeyStroke(KeyEvent.VK_ENTER,0,true), "released");
        }

        pack();
        if( width < getWidth())              // prevent setting width too small
           width = getWidth();
        if(height < getHeight())         // prevent setting height too small
            height = getHeight();
        centerOnScreen(width, height);
    }

    public void centerOnScreen(int width, int height)
    {
      int top, left, x, y;

      // Get the screen dimension
      Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();

      // Determine the location for the top left corner of the frame
      x = (screenSize.width - width)/2;
      y = (screenSize.height - height)/2;
      left = (x < 0) ? 0 : x;
      top = (y < 0) ? 0 : y;

      // Set the frame to the specified location & size
      this.setBounds(left, top, width, height);
    }

    private boolean validUser(String userID,String password)
                throws PasswordException,SQLException,IOException,ClassNotFoundException
    {
        boolean userOK = false;
        user = db.getUser(userID); // get user object from DB for this ID
        if(user != null)
        {
            user.validate(password); // throws PasswordException
            userOK = true;
            if(user.pswdAutoExpires())  // if tracking uses
                db.updUser(user);       // update DB for this use
        }

        return userOK;
    }

    private void doStockActivity()throws PasswordException,SQLException,
                                         IOException,ClassNotFoundException
    {
        StockTracker f = new StockTracker(user,this,db);
        f.pack();
        this.setVisible(false);
        f.setVisible(true);
    }

    public void activate()
    {
        this.setVisible(true);
        userIDField.setText("");
        userIDField.requestFocus();
        user = null;
    }

    public void actionPerformed(ActionEvent e)
    {
        try
        {
            userID = userIDField.getText();
            if(userID.equals(""))
            {
                JOptionPane.showMessageDialog(this,
                    "Please enter a valid user ID.",
                    "Missing User ID.",
                    JOptionPane.ERROR_MESSAGE);
                userIDField.requestFocus();
            }
            else
            {
                password = new String(passwordField.getPassword());
                if(password.equals(""))
                {
                    JOptionPane.showMessageDialog(this,
                        "Please enter a valid password.",
                        "Missing Password.",
                        JOptionPane.ERROR_MESSAGE);
                    passwordField.requestFocus();
                }
                else
                {
                    try
                    {
                        // See if userID exists and validate password
                        if(validUser(userID,password))
                        {
                            if(user.pswdIsExpiring())
                                JOptionPane.showMessageDialog(this,
                                            user.getUserID()+" logon successful; "
                                           +user.getPswdUses()+" use(s) remaining.");
                            if(e.getSource() == jbtLogon)
                                doStockActivity();
                        }
                        else
                            JOptionPane.showMessageDialog(this, "Invalid user.");
                    }
                    catch (PasswordExpiredException ex)
                    {
                        JPasswordField pf1 = new JPasswordField();
                        JPasswordField pf2 = new JPasswordField();
                        Object[] message1 = new Object[]
                                {"Password has expired. Please enter a new password.", pf1};
                        Object[] options = new String[] {"OK", "Cancel"};
                        JOptionPane op1 = new JOptionPane(message1,
                                                    JOptionPane.WARNING_MESSAGE,
                                                    JOptionPane.OK_CANCEL_OPTION, null, options);
                        JDialog dialog1 = op1.createDialog(null, "Change Password");
                        dialog1.show();

                        if(op1.getValue() != null && options[0].equals(op1.getValue()))
                        {
                            String pswd1 = new String(pf1.getPassword());
                            if(pswd1 != null)
                            {
                                Object[] message2 = new Object[]
                                                {"Please verify new password.", pf2};
                                JOptionPane op2 = new JOptionPane(message2,
                                                            JOptionPane.WARNING_MESSAGE,
                                                            JOptionPane.OK_CANCEL_OPTION,
                                                            null, options);
                                JDialog dialog2 = op2.createDialog(null, "Verify Password");
                                dialog2.show();
                                if(op2.getValue() != null && options[0].equals(op2.getValue()))
                                {
                                    String pswd2 = new String(pf2.getPassword());
                                    if(pswd2 != null)
                                    {
                                        if(pswd1.equals(pswd2))
                                        {
                                            user.changePassword(password, pswd1);
                                            db.updUser(user);
                                            doStockActivity();
                                        }
                                        else
                                            JOptionPane.showMessageDialog(this,
                                            "Both passwords are not identical.",
                                            "Password not changed",
                                            JOptionPane.ERROR_MESSAGE);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            userIDField.setText("");
            passwordField.setText("");
            userIDField.requestFocus();
        }// end of try
        catch (PasswordUsedException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "Password Previously Used. Try again.",
                JOptionPane.ERROR_MESSAGE);
        }
        catch (PasswordSizeException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "Invalid password size. Try again.",
                JOptionPane.ERROR_MESSAGE);
        }
        catch (PasswordInvalidFormatException ex)
        {
            if(ex.getCount() > 2) // allows only 3 tries, then exits program
                System.exit(0);
            else
                JOptionPane.showMessageDialog(this,ex.getMessage()+", count:"+ex.getCount(),
                                                   "Invalid password format. Try again.",
                                                   JOptionPane.ERROR_MESSAGE);
        }
        catch (PasswordInvalidException ex)
        {
            if(ex.getCount() > 2) // allows only 3 tries, then exits program
                System.exit(0);
            else
                JOptionPane.showMessageDialog(this,ex.getMessage()+", count:"+ex.getCount(),
                                                   "Invalid password. Try again.",
                                                   JOptionPane.ERROR_MESSAGE);
        }
        catch (PasswordException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "PasswordException.",
                JOptionPane.ERROR_MESSAGE);
        }

        catch (IOException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "IOException.",
                JOptionPane.ERROR_MESSAGE);
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "SQLException.",
                JOptionPane.ERROR_MESSAGE);
        }
        catch (ClassNotFoundException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "ClassNotFoundException.",
                JOptionPane.ERROR_MESSAGE);
        }
    }

    public static void main(String[] argv)
    {
        final STLogon f = new STLogon();
        f.setVisible(true);
    }
}



--------------------------------------------------------------------------------------




/*
Programmer: Matchuel Mashamaite
Date:       July 2009
Filename:   StockTrackerDB.java
*/

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

public class StockTrackerDB
{
    private Connection con = null;

    // Constructor; makes database connection
    public StockTrackerDB() throws ClassNotFoundException,SQLException
    {
        if(con == null)
        {
            String url = "jdbc:odbc:StockTracker";

            try
            {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            }
            catch(ClassNotFoundException ex)
            {
                throw new ClassNotFoundException(ex.getMessage() +
                          "\nCannot locate sun.jdbc.odbc.JdbcOdbcDriver");
            }

            try
            {
                con = DriverManager.getConnection(url);
            }
            catch(SQLException ex)
            {
                throw new SQLException(ex.getMessage()+
                          "\nCannot open database connection for "+url);
            }
        }
    }

    //Close makes database connection: null reference to connection
    public void close() throws SQLException,IOException,ClassNotFoundException
    {
        con.close();
        con = null;
    }

    // Method to serialize object to byte array
    private 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();
    }

    //method to deserialize bytes
    private 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;
    }

    ///////////////////////////////////////////////////////////
    // Methods for adding a record to a table
    //////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////
    public void addStock(String stockSymbol, String stockDesc) throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("INSERT INTO Stocks VALUES ('"
                          +stockSymbol+"'"
                          +",'"+stockDesc+"')");
        stmt.close();
    }
    // add to the Users Table
    public boolean addUser(User user) throws SQLException,IOException,ClassNotFoundException
    {
        boolean result = false;

        String dbUserID;
        String dbLastName;
        String dbFirstName;
        Password dbPswd;
        boolean isAdmin;

        dbUserID = user.getUserID();

        if(getUser(dbUserID) == null)
        {
            dbLastName = user.getLastName();
            dbFirstName = user.getFirstName();
            Password pswd = user.getPassword();
            isAdmin = user.isAdmin();

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

            pStmt.setString(1, dbUserID);
            pStmt.setString(2, dbLastName);
            pStmt.setString(3, dbFirstName);
            pStmt.setBytes(4, serializeObj(pswd));
            pStmt.setBoolean(5, isAdmin);
            pStmt.executeUpdate();
            pStmt.close();
            result = true;

        }
        else
            throw new IOException("User exists - cannot add.");

            return result;

        }

        // add to the userStocks table
        public void addUserStocks(String userID,String stockSymbol) throws SQLException, IOException, ClassNotFoundException
        {
            Statement stmt = con.createStatement();

            stmt.executeUpdate("INSERT INTO UserStocks VALUES ('"
                              +userID+"'"
                              +",'"+stockSymbol+"')");

            stmt.close();
        }

        /////////////////////////////////////////////////////////////////
        // Methods for updating a record in a table
        /////////////////////////////////////////////////////////////////

        // updting the Users Table
        public boolean updUser(User user) throws SQLException, IOException, ClassNotFoundException
        {
            boolean result = false;

            String dbUserID;
            String dbLastName;
            String dbFirstName;
            Password dbPswd;
            boolean isAdmin;

            dbUserID = user.getUserID();

            if(getUser(dbUserID) != null)
            {
                dbLastName = user.getLastName();
                dbFirstName = user.getFirstName();
                Password pswd = user.getPassword();
                isAdmin = user.isAdmin();

            PreparedStatement pStmt = con.prepareStatement("UPDATE Users SET lastName = ?"
                                    +" firstName = ?, pswd = ?, admin = ? WHERE userID = ?");

            pStmt.setString(1, dbLastName);
            pStmt.setString(2, dbFirstName);
            pStmt.setBytes(3, serializeObj(pswd));
            pStmt.setBoolean(4, isAdmin);
            pStmt.setString(5, dbUserID);

            pStmt.executeUpdate();
            pStmt.close();
            result = true;
        }
        else
            throw new IOException("User not exist - cannot update.");

            return result;
        }

            ///////////////////////////////////////////////////
            // Methods  for deleting a record from a table
            ///////////////////////////////////////////////////

            //delete a record from the Stock Table
            private void delStock(String stockSymbol) throws SQLException,IOException,ClassNotFoundException
            {
                Statement stmt = con.createStatement();
                stmt.executeUpdate("DELETE FROM Stocks WHERE "
                                  +"symbol = '"+stockSymbol+"'");
                stmt.close();
            }

            // delete a record from the Users Table
            public void delUser(User user) throws SQLException,IOException,ClassNotFoundException
            {
                String dbUserID;
                String stockSymbol;

                Statement stmt = con.createStatement();

                try
                {
                    con.setAutoCommit(false);

                    dbUserID = user.getUserID();
                    if(getUser(dbUserID) != null)
                    {
                        ResultSet rs1 = stmt.executeQuery("SELECT userID, symbol "
                                          +"FROM UserStocks WHERE userID = '"+dbUserID+"'");

                        while(rs1.next())
                        {
                            try
                            {
                                stockSymbol = rs1.getString("symbol");
                                delUserStocks(dbUserID, stockSymbol);
                            }
                            catch(SQLException ex)
                            {
                                throw new SQLException("Deletion of user stock holding failed: "
                                                       +ex.getMessage());

                            }
                        }

                        try
                        {
                            // holdings deleted, now delete user
                            stmt.executeUpdate("DELETE FROM Users WHERE "
                                          +"userID = '"+dbUserID+"'");
                        }
                        catch(SQLException ex)
                        {
                            throw new SQLException("User deletion failed: "+ex.getMessage());
                        }
                    }
                    else
                        throw new SQLException("User not found in database - cannot delete.");

                    try
                    {
                        con.commit();
                    }
                    catch(SQLException ex)
                    {
                        throw new SQLException("Transaction commit failed: "+ex.getMessage());
                    }
                }
                catch(SQLException ex)
                {
                    try
                    {
                        con.rollback();
                    }
                    catch (SQLException sqx)
                    {
                        throw new SQLException("Transaction failed then rollback failed: "+sqx.getMessage());
                    }

                    // Transaction failed, was rolled back
                    throw new SQLException("Transaction failed; was rolled back: "
                                          +ex.getMessage());
                 }

                 stmt.close();
             }

             // delete a record from the UserStocks Table
             public void delUserStocks(String userID, String stockSymbol) throws SQLException,IOException,ClassNotFoundException
             {
                 Statement stmt = con.createStatement();
                 ResultSet rs;

                 stmt.executeUpdate("DELETE FROM UserStocks WHERE "
                                   +"userID = '"+userID+"'"
                                   +"AND symbol = '"+stockSymbol+"'");

                 rs = stmt.executeQuery("SELECT symbol FROM UserStocks "
                                       +"WHERE symbol = '"+stockSymbol+"'");

                 if(!rs.next())
                    delStock(stockSymbol);

                 stmt.close();

             }

             ////////////////////////////////////////////////////////////////
             // Methods for listing record data from a table
             // Odered by :
             //      methods that obtain individual field(s),
             //      methods that obtain a complete record, and
             //      methods that obtain multible records
             /////////////////////////////////////////////////////////////////

             // Methods to access one or more individual fields

             // get a stock describtion from the Stocks Table
             public String getStockDesc(String stockSymbol) throws SQLException,IOException,ClassNotFoundException
             {
                 Statement stmt = con.createStatement();
                 String stockDesc = null;

                 ResultSet rs = stmt.executeQuery("SELECT symbol, name FROM Stocks "
                                                 +"WHERE symbol = '"+stockSymbol+"'");
                 if(rs.next())
                    stockDesc = rs.getString("name");
                 rs.close();
                 stmt.close();

                 return stockDesc;

             }

             // Methods to access a complete record

             // get User data from the Users Table
             public User getUser(String userID) throws SQLException,IOException,ClassNotFoundException
             {
                 Statement stmt = con.createStatement();
                 String stockDesc = null;

                 String dbUserID;
                 String dbLastName;
                 String dbFirstName;
                 Password dbPswd;
                 boolean isAdmin;

                 byte[] buf = null;
                 User user = null;
                 ResultSet rs = stmt.executeQuery("SELECT * FROM Users WHERE userID = '"
                                                 +userID+"'");
                 if(rs.next())
                 {
                     dbUserID = rs.getString("userID");
                     dbLastName = rs.getString("lastName");
                     dbFirstName = rs.getString("firstName");

                     // Do Not use with JDK 1.2.2 using JDBC-ODBC bridge as
                     // SQL NULL data value is not handled correctly

                     buf = rs.getBytes("pswd");
                     dbPswd=(Password)deserializeObj(buf);

                     isAdmin = rs.getBoolean("admin");
                     user = new User(dbUserID,dbFirstName,dbLastName,dbPswd,isAdmin);
                 }
                 rs.close();
                 stmt.close();

                 return user;
             }

             //Methods to access a list of records

             // get list selected fields for all records from the users Table
             public ArrayList listUsers() throws SQLException,IOException,ClassNotFoundException
             {
                 ArrayList aList = new ArrayList();
                 Statement stmt = con.createStatement();

                 ResultSet rs = stmt.executeQuery("SELECT userID, firstName,lastName, admin "
                                                 +"FROM Users ORDER BY userID");
                 while(rs.next())
                 {
                     aList.add(rs.getString("userID"));
                     aList.add(rs.getString("firstName"));
                     aList.add(rs.getString("lastName"));
                     aList.add(new Boolean(rs.getBoolean("admin")));
                 }

                 rs.close();
                 stmt.close();

                 return aList;
             }

             //get all fields in all records for a given user from the Userstocks Table
             public ArrayList listUserStocks(String userID) throws SQLException,IOException,ClassNotFoundException
             {
                 ArrayList aList = new ArrayList();
                 Statement stmt = con.createStatement();

                 ResultSet rs = stmt.executeQuery("SELECT * FROM UserStocks "
                                                 +"WHERE userID = '"+userID+"' ORDER BY symbol");
                 while(rs.next())
                      aList.add(rs.getString("symbol"));

                      rs.close();
                      stmt.close();

                      return aList;
                  }
              }