Postgresql Update JDBC

Question

I want to update table:

id integer NOT NULL,
"first" character varying(255),
"last" character varying(255),
age integer,
CONSTRAINT registration_pkey PRIMARY KEY (id)

using method:

    void updateTable(String tableName, String columnName, String value,
        String columnName2, String value2) {
    try {

        String sql = "UPDATE " + tableName + " SET " + columnName + " = "
                + value + " WHERE " + columnName2 + " = " + value2;

        stmt.executeUpdate(sql);

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

If i run

.updateTable("employees", "last", "11", "id", "100");

everything is ok, but if i try

.updateTable("employees", "last", "xx", "id", "100");

i recieve

org.postgresql.util.PSQLException: ERROR: column "xx" does not exist
  Position: 29
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998).....

can you tell me, where is the problem?

Maybe try logging out your sql string before you pass it into execute update, just to make sure that everything is playing out the way you expect. What you've given seems fine, but when attempting to execute it with the value2 being placed into a column name position.. — Dave Lugg
– Dave Lugg, Commented Jun 22, 2015 at 14:53

JP Moresmau · Accepted Answer · 2015-06-22 14:59:13Z

1

You shouldn't build SQL by putting your variables directly via string concatenation. What happens here is that with 11, your SQL becomes:

 set last=11

Which is valid SQL (using 11 as a integer literal), while with xx it becomes:

set last=xx

There are no quotes, so the SQL means you're reading the value of another column, hence your error message. Since your value is a string (going into a varchar field), you need to wrap it in quotes.

answered Jun 22, 2015 at 14:59

JP Moresmau

7,42319 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Update JDBC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related