Re: JDBC driver error: "syntax error at end of input" when no primary key

On Fri, 27 Nov 2020 at 09:11, Tomas Janco <tomas(dot)janco(at)myinphinity(dot)com>
wrote:

> 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.
>

Certainly an undesirable feature.

There is no way to update a row without a primary key. The driver is
looking for the primary key. You are correct the error thrown is not
desirable

I've created an issue https://github.com/pgjdbc/pgjdbc/issues/1975

Thanks,

Dave

>