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

Поиск
Список
Период
Сортировка
От Tomas Janco
Тема JDBC driver error: "syntax error at end of input" when no primary key
Дата
Msg-id dab7ea6f918e038bb425e426489229583fe541be.camel@myinphinity.com
обсуждение исходный текст
Ответы Re: JDBC driver error: "syntax error at end of input" when no primary key  (Dave Cramer <davecramer@postgres.rocks>)
Список pgsql-jdbc
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



-- 

TOMÁŠ JANČO DEVELOPER

 


 

E-MAIL:   tomas.janco@myinphinity.com

Вложения

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Dave Cramer
Дата:
Сообщение: Re: Community Driver : Time data is getting truncated while read
Следующее
От: Dave Cramer
Дата:
Сообщение: [pgjdbc/pgjdbc] 8dd955: add version to javadoc title (#1973)