SQL error while updating a row

Question

Details of table under consideration :

enter image description here

Code used for updating rows is :

 public void updateEmployeeDetails(Employee employee) {

    Connection conn = JDBCUtility.getConnection();

    try {
        String updateSql = "UPDATE EMPLOYEE_INFO SET emp_name=?, location=?, email=?, dob=? WHERE emp_id=?";

        PreparedStatement ps = (PreparedStatement)conn.prepareStatement(updateSql);
        ps.setString(1, employee.getEmp_name()); /*all the attributes of employee object are of type String */
        ps.setString(2, employee.getLocation());
        ps.setString(3, employee.getEmail());
        ps.setString(4, employee.getDob());
        ps.setString(5, employee.getEmp_id());
        ps.executeUpdate(updateSql);

    } catch (SQLException e) {
        System.out.println(e.getErrorCode());
        e.printStackTrace();
    }
}

Error Received :

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '?, location=?, email=?, dob=? WHERE  emp_id=?' at line 1

Can anyone point out the error because I doubly checked any possible error in my query string? Is varchar(45) of mysql compatible with Java's String? i.e. could ps.setString(column_index, String) be the root cause of error?

DEBUG 1 :

Considering null possibility I tried(in vain) following :

//  ps.setString(1, employee.getEmp_name());
//  ps.setString(2, employee.getLocation());
//  ps.setString(3, employee.getEmail());
//  ps.setString(4, employee.getDob());
//  ps.setString(5, employee.getEmp_id());

ps.setString(1, "Superman");
ps.setString(2, "Sky");
ps.setString(3, "anymail@dooodle.com");
ps.setString(4, "1-1-0111");
ps.setString(5, "501");
ps.executeUpdate(updateSql);

but still the same output is shown .

maybe some string is `null` and this post is relevant for this? http://stackoverflow.com/a/1357449/2186023 — DrCopyPaste, Apr 24 '14 at 09:11

score 0 · Answer 1 · answered Apr 24 '14 at 09:09

0

What I would usually do is:

private static final String UPDATE_ITEM =
        "UPDATE `EMPLOYEE_INFO` SET emp_name=?, location=?, email=?, dob=? WHERE emp_id=?";

public void updateEmployeeDetails(Employee employee) {

    Connection conn = null;
    PreparedStatement ps = null;

    try {

        conn = JDBCUtility.getConnection();
        ps = conn.prepareStatement(UPDATE_ITEM);
        ps.setString(1, employee.getEmp_name()); /*all the attributes of employee object are of type String */
        ps.setString(2, employee.getLocation());
        ps.setString(3, employee.getEmail());
        ps.setString(4, employee.getDob());
        ps.setString(5, employee.getEmp_id());
        ps.executeUpdate();

    } catch (SQLException e) {
        System.out.println(e.getErrorCode());
        e.printStackTrace();
    }
}

But can you double check what this returns employee.getEmp_name()?

answered Apr 24 '14 at 09:09

majidarif

18,694
16
88
133

I did `System.out.println(employee.getEmp_name()+employee.getDob());` and it result was not `null` . It gave what I entered in the form. so no error in that/ – KNU Apr 24 '14 at 09:43
@KNU did you try my answer? – majidarif Apr 24 '14 at 09:44
Also reshuffling statements as suggested by you shouldn't be helpful as I have other function for other queries in exactly same fashion (in same class) which runs fine – KNU Apr 24 '14 at 09:45
@KNU there are actually a few changes but alright. :) – majidarif Apr 24 '14 at 09:47

score 0 · Answer 2 · answered Apr 24 '14 at 11:20

After line by line verification I found error in following line :

ps.executeUpdate(updateSql);

Conclusion: the executeUpdate() was required in this case without passing any argument

Official Documentation :

int executeUpdate() throws SQLException

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Returns:
either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

SQL error while updating a row

2 Answers2