help in SQL and PreparedStatement (error)

[redashley40]

This is my first attempt in SQL and PreparedStatement
I have add the PreparedStatement and I'm not to sure if I'm doing it correctly.
When I do a test run on Choose 1 ,or 2 I get this error.
Error - com.mysql.jdbc.Statement

here is my code below

public class DBAssign {
 
/**
* @param args
*/
static Scanner kbd;
 
static Connection conn = null;
//adding records...
public static void addRecord(String id, String fname, String lname,
String street, String city, String state, String zip,
String hphone, String ophone, String deptasg, int yrsemp,
float mtpy) throws SQLException {
// make variables
PreparedStatement statement = null;
 
String addstring = "Insert into emptable values('" + id + "', '"
+ fname + "', " + lname + ", '" + street + "'" + city + "',"
+ state + "'," + zip + "'," + hphone + "'," + ophone + "'"
+ deptasg + "'," + yrsemp + "'," + mtpy + "',)";
 
try {
statement=conn.prepareStatement("INSERT into user values(?,?)");
 
statement = (PreparedStatement) conn.createStatement();
boolean ret = statement.execute(addstring);
if (ret) {
System.out.println("Updated " + statement.getUpdateCount());
}//close if
 
} catch (SQLException e) {
System.out.println("Error " + e.getMessage());
throw (e);
} finally {
try {
if (statement != null) {
statement.close();
}//close if
} catch (SQLException e2) {
System.out
.println("Error freeing resources " + e2.getMessage());
throw (e2);
}//close e2
}//close finally
 
}//close exception
 
//add to the record
public static void addToTable() {
// get data from keyboard
String id = null;
String fname; // =null;
String lname;
String street;
String city;
String state;
String zip;
String hphone;
String ophone;
String deptasg;
int yrsemp;
float mtpy;
boolean dupkey = true;
 
while (dupkey == true) {
System.out.println("Enter Employee ID");
id = kbd.next();
kbd.nextLine();
dupkey = findRecord(id);
if (dupkey == true) {
System.out.println("Key must be unique - try again");
}
}
 
System.out.println("Enter first name");
fname = kbd.next();
kbd.nextLine();
 
System.out.println("Enter last name");
lname = kbd.next();
kbd.nextLine();
 
System.out.println("Enter street name");
street = kbd.next();
kbd.nextLine();
 
System.out.println("Enter city");
city = kbd.next();
kbd.nextLine();
 
System.out.println("Enter state");
state = kbd.next();
kbd.nextLine();
 
System.out.println("Enter zip code");
zip = kbd.next();
kbd.nextLine();
 
System.out.println("Enter home phone number");
hphone = kbd.next();
kbd.nextLine();
 
System.out.println("Enter office phone");
ophone = kbd.next();
kbd.nextLine();
 
System.out.println("Enter dept name");
deptasg = kbd.next();
kbd.nextLine();
 
System.out.println("Enter years employeed");
yrsemp = kbd.nextInt();
kbd.nextLine();
 
System.out.println("Enter month pay");
mtpy = kbd.nextFloat();
kbd.nextLine();
 
try {
addRecord(id, fname, lname, street, city, state, zip, hphone,
ophone, deptasg, yrsemp, mtpy);
} catch (SQLException e) {
System.out.println("Error adding record " + e.getMessage());
}
 
}//close add
 
//List records 
public static void listRecords() {
PreparedStatement statement = null;
ResultSet rs = null;
 
try {
statement = (PreparedStatement) conn.createStatement();
rs = statement
.executeQuery("Select empid, firstname, lastname, street, city, state, zip, homephone, officephone, department, yearsemploy, monthpay");
if (rs != null) {
while (rs.next()) {
System.out.println(rs.getString("empid") + " - "
+ rs.getString("firstname") + " - "
+ rs.getInt("lastname") + " - "
+ rs.getString("street") + " - "
+ rs.getString("city") + " - "
+ rs.getString("state") + " - "
+ rs.getString("zip") + " - "
+ rs.getString("hphone") + " - "
+ rs.getString("ophone") + " - "
+ rs.getString("department") + " - "
+ rs.getString("yearsemploy") + " - "
+ rs.getString("monthpay"));
}// close the while loop
}// close the if loop
 
} catch (SQLException e) {
System.out.println("Error listing records: " + e.getMessage());
} finally {
try {
if (statement != null) {
statement.close();
}
if (rs != null) {
rs.close();
}
} catch (SQLException e2) {
System.out.println("Error freeing resource" + e2.getMessage());
}
}//close finally
 
}//close list
 
 
//Find a record
public static boolean findRecord(String empid) {
boolean retval = true;
PreparedStatement statement = null;
ResultSet rs = null;
 
int countemps = 0;
 
try {
statement = (PreparedStatement) conn.createStatement();
rs = statement
.executeQuery("Select count(*) as num from emptable where empid = '"
+ empid + "'");
rs.next();
if (rs == null) {
retval = false;
} else {
countemps = rs.getInt(1);
}
 
// System.out.println("Count "+countrecs);
 
if (countemps <= 0) {
retval = false;
}
 
} catch (SQLException e) {
System.out.println("Error finding Employees record "
+ e.getMessage());
} finally {
try {
if (statement != null) {
statement.close();
}
if (rs != null) {
rs.close();
}
} catch (SQLException e2) {
System.out.println("Error freeing resource " + e2.getMessage());
retval = true;
}
}//close finally
 
return retval;
 
}//close find
 
 
public static void showMenu() {
System.out.println("---------------");
System.out.println("1. List employees records");
System.out.println("2. Add employees record");
System.out.println("3. Update employees record");
System.out.println("4. Delete employees record");
System.out.println("5. Exit");
}
 
public static int getOption() {
int optn = 99;
while (optn > 5 || optn < 1) {
System.out.println("---");
System.out.println("Enter option");
System.out.print("===>");
optn = kbd.nextInt();
}//close while
return optn;
}
 
public static void main(String[] args) {
int optn = 99;
 
String host = "localhost";
String database = "empdata";
String user = "root";
String pass = "";
 
// make keyboard object
kbd = new Scanner(System.in);
 
String connstring = "jdbc:mysql://" + host + "/" + database + "?user="
+ user + "&password=" + pass;
 
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(connstring);
while (optn != 5) {
showMenu();
optn = getOption();
 
switch (optn) {
case 1:
listRecords();
break;
 
case 2:
addToTable();
break;
 
case 3:
updateRecord();
break;
 
case 4:
deleteRecord();
 
case 5:
System.out.println("OK - later Come back soon");
 
default:
break;
}//close switch
}//close while
 
} catch (ClassNotFoundException e1) {
System.out.println("ERROR - Class not found " + e1.getMessage());
} catch (SQLException e2) {
System.out.println("ERROR - " + e2.getMessage());
System.out.println("ERROR - " + e2.getSQLState());
} catch (Exception e3) {
System.out.println("Error - " + e3.getMessage());
}
}//close main
}//close class

Any help would be great.
Red

[JosAH]

statement=conn.prepareStatement("INSERT into user values(?,?)");
 
statement = (PreparedStatement) conn.createStatement();
 

At the second line you've effectively caused an exception. Read the API docs
for the PreparedStatement interface. You're supposed to fill in the
question marks (they're indexed 1, 2, 3 ... from left to right). you assign values
to them and then you execute the prepared statement.

kind regards,

Jos

[redashley]

Thanks Jos for the help...
But I did away with the Prestatement.
Found some mistakes in my coding.
I having troubles with this part thou.
I get this error which is pointer...

Error - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name= 'lkasdj', Street= '3437', City 'Fullerton', State'CA', Zip = '3', Home Pho' at line 1.

I looked at the String updatestring =
where I think the problem is at.

here is my code for the update section.

public class DBAssign {
 
    /**
     * @param args
     */
    static Scanner kbd;
    static Connection conn = null;
 
 
 
    // searches for a emp id.
    public static boolean findRecord(String empid) {
    {
        }// close finally
 
        return retval;
 
    }// close find
 
    // updates the a record
    public static void updateRecord() {
        // get record to update
        String empid = null;
        boolean findemp = false;
        while (!findemp) {
            System.out.println("Enter Employee ID to update");
            empid = kbd.next();
            kbd.nextLine();
            findemp = findRecord(empid);
            if (!findemp) {
                System.out.println("This Employee id record " + empid
                        + " does not exist - try again");
            }
        }// close while
 
        // display data and request update
        Statement stmt = null;
        ResultSet rs = null;
 
        String firstname;
        String lastname;
        String street;
        String city;
        String state;
        String zip;
        String homephone;
        String officephone;
        String department;
        String yearsemploy;
        String monthpay;
 
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("Select * from emptable where empid = '"
                    + empid + "'");
            rs.next();
            System.out.println("Item First Name is "
                    + rs.getString("firstname"));
            System.out.print("Enter new item name: ");
            firstname = kbd.next();
            kbd.nextLine();
 
            System.out.println("Item Last name is " + rs.getString("lastname"));
            System.out.print("Enter new item name: ");
            lastname = kbd.next();
            kbd.nextLine();
 
            System.out.println("Item Street is " + rs.getString("street"));
            System.out.print("Enter new item name: ");
            street = kbd.next();
            kbd.nextLine();
 
            System.out.println("Item City name is " + rs.getString("city"));
            System.out.print("Enter new item name: ");
            city = kbd.next();
            kbd.nextLine();
 
            System.out.println("Item State name is " + rs.getString("state"));
            System.out.print("Enter new item name: ");
            state = kbd.next();
            kbd.nextLine();
 
            System.out.println("Item Zip Code is " + rs.getString("zip"));
            System.out.print("Enter new item name: ");
            zip = kbd.next();
            kbd.nextLine();
 
            System.out.println("Item Home Phone is "
                    + rs.getString("homephone"));
            System.out.print("Enter new item name: ");
            homephone = kbd.next();
            kbd.nextLine();
 
            System.out.println("Item Office Phone is "
                    + rs.getString("officephone"));
            System.out.print("Enter new item name: ");
            officephone = kbd.next();
            kbd.nextLine();
 
            System.out.println("Item Department is "
                    + rs.getString("department"));
            System.out.print("Enter new item name: ");
            department = kbd.next();
            kbd.nextLine();
 
            System.out.println("Item Years Employed is "
                    + rs.getString("yearsemploy"));
            System.out.print("Enter new item name: ");
            yearsemploy = kbd.next();
            kbd.nextLine();
 
            System.out.println("Item Monthly pay is "
                    + rs.getString("monthpay"));
            System.out.print("Enter new item name: ");
            monthpay = kbd.next();
            kbd.nextLine();
 
            String updatestring = "Update emptable set FirstName = '"
                    + firstname + "', Last name= '" + lastname + "', Street= '"
                    + street + "', City '" + city + "', State'" + state
                    + "', Zip = '" + zip + "', Home Phone='" + homephone
                    + "', Office Phone '" + officephone + "', Department '"
                    + department + "', Years Employed'" + yearsemploy
                    + "', Month Pay '" + monthpay + "' where empid = '" + empid
                    + "'";
 
            stmt.execute(updatestring);
            int updatenum = stmt.getUpdateCount();
 
            if (updatenum < 1) {
                System.out.println("Error on update");
            } else {
                System.out.println("Updated " + updatenum + " records");
            }
        } catch (SQLException e) {
            System.out.println("Error - " + e.getMessage());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e2) {
                System.out.println("Error " + e2.getMessage());
            }
        }// close finally
 
    }// close updates
 
 
 
    public static void showMenu() {
        System.out.println("---------------");
        System.out.println("1. List employees records");
        System.out.println("2. Add employees record");
        System.out.println("3. Update employees record");
        System.out.println("4. Delete employees record");
        System.out.println("5. Exit");
    }
 
    public static int getOption() {
        int optn = 99;
        while (optn > 5 || optn < 1) {
            System.out.println("---");
            System.out.println("Enter option");
            System.out.print("===>");
            optn = kbd.nextInt();
        }// close while
        return optn;
    }
 
    public static void main(String[] args) {
        int optn = 99;
 
        String host = "localhost";
        String database = "empdata";
        String user = "root";
        String pass = "";
 
        // make keyboard object
        kbd = new Scanner(System.in);
 
        String connstring = "jdbc:mysql://" + host + "/" + database + "?user="
                + user + "&password=" + pass;
 
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(connstring);
            while (optn != 5) {
                showMenu();
                optn = getOption();
 
                switch (optn) {
                case 1:
                    listRecords();
                    break;
 
                case 2:
                    addToTable();
                    break;
 
                case 3:
                    updateRecord();
                    break;
 
                case 4:
                    deleteRecord();
                    System.out.println("That Employee is deleted");
                    break;
                case 5:
                    System.out.println("OK - later Come back soon");
 
                default:
                    break;
                }// close switch
            }// close while
 
        } catch (ClassNotFoundException e1) {
            System.out.println("ERROR - Class not found " + e1.getMessage());
        } catch (SQLException e2) {
            System.out.println("ERROR - " + e2.getMessage());
            System.out.println("ERROR - " + e2.getSQLState());
        } catch (Exception e3) {
            System.out.println("Error - " + e3.getMessage());
        }
    }// close main
}// close class

Thanks
Red

[JosAH]

You should use a PreparedStatement. The way you attempt to do it now is
extremely error prone, i.e. you have your quoting wrong. PreparedStatements
solve that for you easily. (have another look at that very clear error diagnostic
message).

kind regards,

Jos

[redashley]

You should use a PreparedStatement. The way you attempt to do it now is
extremely error prone, i.e. you have your quoting wrong. PreparedStatements
solve that for you easily. (have another look at that very clear error diagnostic
message).

kind regards,

Jos

Yes not to sure what it means, but I have another class section where I add new info (data) and it works just fine...
I don't know if I have enought time to learn PreparedStatements this is due in a day...

red
PS Not to sure if the inst. wants PreparedStatements as well?

[redashley]

I'm not to sure if we can use Prepared statements.

I did some corrections and got rid of the headings and now I get this error message

Error - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''David', 'Wie', '1', '2','3','4','5','6','7','8','9' where empid = '234DW'' at line 1


Thanks for the help so far.
red

[JosAH]

I'm not to sure if we can use Prepared statements.

I did some corrections and got rid of the headings and now I get this error message

Error - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''David', 'Wie', '1', '2','3','4','5','6','7','8','9' where empid = '234DW'' at line 1


Thanks for the help so far.
red

When I read that error diagnostic it again seems to be a quoting problem:
before the 'D' in David and after the last 'W' at the end, shouldn't there be a
single quite too? Take it from me: Prepared statements handle all this for
you: you just supply your String values for the question marks. No explicit
quotes are needed except for the normal Java literal String quotes.

kind regards,

Jos