Обсуждение: BUG #15842: Unable to run a prepared statement using the org.postgresql Java Library
BUG #15842: Unable to run a prepared statement using the org.postgresql Java Library
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 15842 Logged by: Matthew James Briggs Email address: mjb@bitflip.software 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
Re: BUG #15842: Unable to run a prepared statement using theorg.postgresql Java Library
От
"David G. Johnston"
Дата:
On Mon, Jun 10, 2019 at 4:24 PM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 15842
Logged by: Matthew James Briggs
Email address: mjb@bitflip.software
PostgreSQL version: 10.7
Operating system: Linux (Dockerhub postgres:10)
Description:
This isn't a bug; 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.
I suggest you write an actual CREATE FUNCTION and then call that using:
SELECT function(?, ?)
David J.