Unable to Prepare a Postgres Statement in Java (java.sql.PreparedStatement)

Question

Consider two statements that I may want to send to my server from Java.

1. Simple SQL: This is a typically insert statement.

insert into table_things (thing_1_value, thing_2_value) values(?, ?);

2. PlPgSQL: I want to avoid round-trips to the database by doing some login in the database. We are not allowed to use stored procedures or functions in the database (the reasons are seem valid).

do $$
declare
    my_thing1 varchar(100) = ?;
    my_thing2 varchar(100) = ?;
begin
    insert into table_things
    (
          thing_1_value
        , thing_2_value
    )
    values
    (
          my_thing1
        , my_thing2
    )
    ;
end
$$;

The code that executes these statements is represented below in Java8 test cases:

package com.somecompany.someservice.test.database;

import org.apache.commons.dbcp2.BasicDataSource;
import org.junit.Assert;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;

public class PreparedStatementDatabaseTest {
    private static final String CONNECTION_URI = "jdbc:postgresql://localhost:5432/somedb?user=someuser&password=somepass";

    private static final String PLPGSQL_STATEMENT = "" +
            "do $$\n" +
            "declare\n" +
            "    my_thing1 varchar(100) = ?;\n" +
            "    my_thing2 varchar(100) = ?;\n" +
            "begin\n" +
            "    insert into table_things\n" +
            "    (\n" +
            "          thing_1_value\n" +
            "        , thing_2_value\n" +
            "    )\n" +
            "    values\n" +
            "    (\n" +
            "          my_thing1\n" +
            "        , my_thing2\n" +
            "    )\n" +
            "    ;\n" +
            "end\n" +
            "$$;";

    private static final String EASY_SQL_STATEMENT = "insert into table_things (thing_1_value, thing_2_value) values(?, ?);";

    @Test
    public void testPlpgsqlStatement() throws Exception {
        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        Connection conn = basicDataSource.getConnection();
        PreparedStatement statement = conn.prepareStatement(PLPGSQL_STATEMENT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        statement.setObject(1, "hello", Types.VARCHAR);
        statement.setObject(2, "world", Types.VARCHAR);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);
    }

    @Test
    public void testEasySqlStatement() throws Exception {
        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        Connection conn = basicDataSource.getConnection();
        PreparedStatement statement = conn.prepareStatement(EASY_SQL_STATEMENT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        statement.setObject(1, "hello", Types.VARCHAR);
        statement.setObject(2, "world", Types.VARCHAR);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);
    }
}

testEasySqlStatement works, but testPlpgsqlStatement throws an exception:

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

Question: how can I send code like PLPGSQL_STATEMENT to the Postgres database?

I could do this, but it is bad practice due to SQL Injection risk:

    @Test
    public void testSqlInjectionRisk() throws Exception {
        String hello = "hello-testSqlInjectionRisk";
        String world = "world-testSqlInjectionRisk";

        String PLPGSQL_STATEMENT = "" +
                "do $$\n" +
                "declare\n" +
                "    my_thing1 varchar(100) = '" + hello + "';\n" +
                "    my_thing2 varchar(100) = '" + world + "';\n" +
                "begin\n" +
                "    insert into table_things\n" +
                "    (\n" +
                "          thing_1_value\n" +
                "        , thing_2_value\n" +
                "    )\n" +
                "    values\n" +
                "    (\n" +
                "          my_thing1\n" +
                "        , my_thing2\n" +
                "    )\n" +
                "    ;\n" +
                "end\n" +
                "$$;";

        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        Connection conn = basicDataSource.getConnection();
        PreparedStatement statement = conn.prepareStatement(PLPGSQL_STATEMENT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);

Question Restated: Is there a problem with the way I am trying to prepare PLPGSQL_STATEMENT? Can PLPGSQL_STATEMENT be prepared?

Update: @Izruo pointed out that I should be using prepareCall, and this seems to be part of the answer. But unfortunately, the following code fails with the same exception:

    @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);

If I copy the sql statement printed by System.out.println(SQL_STATEMENT); into DataGrip (A database IDE by JetBrains) and run it, then DataGrip asks for me to fill in two parameter values (for the two question marks) and successfully runs the sql statement. In other words, the plpgsql code is syntactically valid (once the params are replaced).

It seems there are three possibilities here, and I cannot tell which is true:

1. This functionality (creating a CallableStatement/PreparedStatement with plpgsql variables in it) is unsupported.
2. This functionality is supported but I am doing it wrong.
3. The functionality is supported, I am using it correctly, but there is a bug.

Answer 1

Although Mạnh Quyết Nguyễn's answer is on the right track, it is devoid of an explanation and the suggested workaround is not viable in my case (most cases, I would think).

I received an authoritative answer from postgresql.org.

you attempted to add question marks to a location where they are not interpreted as parameters.

Basically you wrote:

SELECT 'let me say ? ? to you';

Which is a perfectly valid query that has zero input parameters and will return:

"let me say ? ? to you"

It has no input parameters because the question marks you wrote are inside a string literal.

The $$...$$ in your DO statement also denote a string literal.

This is unfortunate, as far as I can tell it means the entire PL/pgSQL language is off-limits if you need to pass parameters into that PL/pgSQL code. (Unless, of course, you compile PL/pgSQL procedures or functions either on the fly or as part of schema development). Looks like I cannot send a PL/pgSQL 'script' to the database along with parameters.