Re: JDBC driver error: "syntax error at end of input" when no primary key - Mailing list pgsql-jdbc
From | Dave Cramer |
---|---|
Subject | Re: JDBC driver error: "syntax error at end of input" when no primary key |
Date | |
Msg-id | CADK3HHJZbD7gHgx7a0na2h=VApY9f0egCcuMsNXSPruaCnyrqQ@mail.gmail.com Whole thread Raw |
In response to | JDBC driver error: "syntax error at end of input" when no primary key (Tomas Janco <tomas.janco@myinphinity.com>) |
List | pgsql-jdbc |
On Fri, 27 Nov 2020 at 09:11, Tomas Janco <tomas.janco@myinphinity.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-bitSample 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 inputPosition: 39at 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
pgsql-jdbc by date: