PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15842
PG Version10.7
OSLinux (Dockerhub postgres:10)
Opened2019-06-10 23:23:24+00
Reported byMatthew James Briggs
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15842
Logged by:          Matthew James Briggs
Email address:      (redacted)
PostgreSQL version: 10.7
Operating system:   Linux (Dockerhub postgres:10)
Description:        

org.postgresql
I'm not sure yet how to detect the version of org.postgresql that I am
using. I will update this once I determine the version. I am using Java 8.

The problem I am having is that with the org.postgresql import java.sql.*
library, I am unable to prepare a callable statement with parameters. The
following Java code produces an exception, but I would expect it to work.

```
package com.somecompany.somepackage.test.database;

import org.apache.commons.dbcp2.BasicDataSource;
import org.junit.Assert;
import org.junit.Test;
import org.junit.jupiter.api.Disabled;

import java.sql.*;

@Disabled
public class PreparedStatementDatbaseTest {
    private static final String CONNECTION_URI =
"jdbc:postgresql://localhost:5432/blah_service?user=blah_service_user&password=masterpass1";

    @Test
    public void testEasySqlStatement2() throws Exception {
        final String SQL_STATEMENT = "" +
                "do $$\n" +
                "declare\n" +
                "    x varchar(100) = ? ;\n" +
                "    y varchar(100) = ? ;\n" +
                "begin\n" +
                "    insert into table_things\n" +
                "    (\n" +
                "          my_thing1\n" +
                "        , my_thing2\n" +
                "    )\n" +
                "    values\n" +
                "    (\n" +
                "          x\n" +
                "        , y\n" +
                "    )\n" +
                "    ;\n" +
                "end\n" +
                "$$;";

        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        System.out.println(SQL_STATEMENT);
        Connection conn = basicDataSource.getConnection();
        CallableStatement statement = conn.prepareCall(SQL_STATEMENT);
        statement.setObject(1, "hello", Types.VARCHAR);
        statement.setObject(2, "world", Types.VARCHAR);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);
    }
}
```

I expect this to work, but it chokes on the call to setObject with: 

```
org.postgresql.util.PSQLException: The column index is out of range: 1,
number of columns: 0.

    at
org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:65)
    at
org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:128)
    at
org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:996)
    at
org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:326)
    at
org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:528)
    at
org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:881)
    at
org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:185)
    at
org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:185)
    at
com.somecompany.someservice.test.database.PreparedStatementDatabaseTest.testPlpgsqlStatement(PreparedStatementDatabaseTest.java:44)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at
com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at
com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at
com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
```

I have a detailed write up here (with bounty)
https://stackoverflow.com/questions/56497853

Messages

DateAuthorSubject
2019-06-10 23:23:24+00PG Bug reporting formBUG #15842: Unable to run a prepared statement using the org.postgresql Java Library
2019-06-10 23:39:57+00"David G(dot) Johnston"Re: BUG #15842: Unable to run a prepared statement using the org.postgresql Java Library