Sql Syntax error unknown field list but looking for wrong field?

So I created the following function to write to a database:

public static void updateBuyer(String ID, String name, Location latlong) {
    float lat = latlong.latitude;
    float lon = latlong.longitude;
    try {
        // new com.mysql.jdbc.Driver();
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        // conn =
        // DriverManager.getConnection("jdbc:mysql://localhost:3306/testdatabase?user=testuser&password=testpassword");
        conn = DriverManager.getConnection(connectionUrl, connectionUser,
                connectionPassword);
        stmt = conn.createStatement();
        String sql = "UPDATE Buyer " + "SET latitude ="
                + Float.toString(lat) + " WHERE idBuyer in (" + ID + ")";
        String sql2 = "UPDATE Buyer " + "SET longitude ="
                + Float.toString(lon) + " WHERE idBuyer in (" + ID + ")";
        String sql3 = "UPDATE Buyer " + "SET Name =" + name
                + " WHERE idBuyer in (" + ID + ")";

        stmt.executeUpdate(sql);
        stmt.executeUpdate(sql2);
        stmt.executeUpdate(sql3);
        conn.close();
    } catch (Exception e) {
        System.err.println(e);
    }
}

Lets say I passed the following parameters: (12,craigs,location object) Now when I run the function I get the following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'craigs' in 'field list'

12 is the ID i retrieved from the databases earlier, craigs is the random name I am trying to insert, and location object is just a set of coordinates (lat,long)

Which leads me to think that it is looking for a field called "craigs" in the table for some reason, but why would it do that?

Jon Skeet
people
quotationmark

The problem is that you've got SQL like this:

UDPATE Buyer SET Name = craigs WHERE idBuer in (Whatever)

That's trying to copy the value from a column named "craigs".

Now you could just add apostrophes - but don't. Instead, use parameterized SQL with a prepared statement. That way you'll avoid SQL injection attacks, your code will be simpler, and you'll avoid unnecessary string conversions which can cause problems, particularly with date values.

Additionally, you only need a single statement, which can update all three columns:

String sql = "UPDATE Buyer SET Name=?, latitude=?, longitude=? WHERE idBuyer=?";
try (PreparedStatement statement = conn.prepareStatement(sql)) {
    statement.setString(1, name);
    statement.setFloat(2, lat);
    statement.setFloat(3, lon);
    statement.setString(4, ID);
    statement.executeUpdate();
}

(Note that I've assumed you're actually only trying to update a single buyer - it's not clear why you were using IN at all. Also note that I'm using a try-with-resources statement, which will automatically close the statement afterwards.)

Additionally, I would *strongly *advise you to avoid just catching Exception. Catch SQLException if you must - but you'd actually probably be better letting it just propagate up the call stack.

people

See more on this question at Stackoverflow