Hello,
I have found following problem with JDBC driver:
When an updatable result set is created for a table without primary key, any update fails with error: "syntax error at end of input"
The driver generates invalid SQL query to update the table.
JDBC driver version: 42.2.18 (jre8)
Server version: PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit
Sample code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestPgSql {
/* test table schema:
CREATE TABLE public.sample
(
id integer,
value character varying(255) COLLATE pg_catalog."default"
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
INSERT INTO public.sample(id, value)
VALUES (1, 'abcd');
*/
public static void main(String args[]) throws Exception {
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/...?user=....&password=....");
String sql = "SELECT * FROM sample WHERE id = 1;";
PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_FORWARD_ONLY);
ResultSet rs = stmt.executeQuery();
rs.next();
rs.updateString("value", "something");
rs.updateRow();
rs.close();
}
}
Expected behavior:
The code successfully updates the table OR throws an error explaining primary key is not present in result set and is required for updatable result set.
Actual behavior:
Incorrect SQL command is generated internally: "UPDATE sample SET "value" = $1 WHERE "
The query is missing the WHERE condition expression.
This results in following exception being thrown:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at end of input
Position: 39
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
at org.postgresql.jdbc.PgResultSet.updateRow(PgResultSet.java:1445)
at TestPgSql.main(TestPgSql.java:35)
I think this is a bug in JDBC driver.
Best regards, Tomas Janco