Using Postgres's prepared statements from JDBC fails with "there is no parameter $1"

Question

I'm attempting to use Postgres 9.1's prepared statements feature from a Scala app using Postgres's official JDBC driver 9.3-1100.

If I do this, everything works fine and I get "foo" back:

conn.prepareStatement("PREPARE prep(text) AS SELECT $1").executeUpdate();
val cmd = conn.prepareStatement("EXECUTE prep('foo')");

However, if I attempt to parameterize it:

conn.prepareStatement("PREPARE prep(text) AS SELECT $1").executeUpdate();
val cmd = conn.prepareStatement("EXECUTE prep(?)");
cmd.setString(1, "foo");

Then I get this error upon executing the command:

org.postgresql.util.PSQLException: ERROR: there is no parameter $1
  Position: 14
  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
  org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
  org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
  org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
  org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
  org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
  com.jolbox.bonecp.PreparedStatementHandle.execute(PreparedStatementHandle.java:140)

I've tried this in several different ways but I can't find any way to parameterize this query successfully.

I've done a similar thing from a .net app using the Npgsql driver and it works, so I can't figure out why the JDBC driver won't let me do this. Is this just a limitation of the JDBC driver? Is there any work-around?

So you create a prepared statement of a prepared statement. Hm... — Milen A. Radev
– Milen A. Radev, Commented Feb 12, 2014 at 12:41
What is a purpose of this prepared statement ? Could you explain ? For me it does not make sense to assing a string to a parameter, then do SELECT string and retrieve that string as a resultset from the database. — krokodilko
– krokodilko, Commented Feb 12, 2014 at 13:06
Check out the link at the top of my question. Prepared statements allow you to PREPARE a statement once, and then EXECUTE it many times, saving on server resources. The server then only has to parse and plan the query once. This is a trivial example of a prepared statement just to demonstrate the error I'm running into. — fickerra
– fickerra, Commented Feb 12, 2014 at 13:10
I don't know if its relevant, but the JDBC documentation says that you can get the JDBC prepared statement functions to actually use server side prepares (jdbc.postgresql.org/documentation/91/server-prepare.html) which might give you the effect you're after. — Gary - Stand with Ukraine
– Gary - Stand with Ukraine, Commented Feb 12, 2014 at 16:56

Mark Rotteveel · Accepted Answer · 2014-02-12 12:41:45Z

2

When you prepare a statement with JDBC, you need to prepare the actual statement. You are currently trying to prepare and execute the PostgreSQL specific PREPARE and EXECUTE commands (which is probably what is used internally by the driver).

You need to do this instead:

val cmd = conn.prepareStatement("SELECT ?");
cmd.setString(1, "foo");

Although I am not sure if this is going to work as there is no way for PostgreSQL to decide the parameter type here.

answered Feb 12, 2014 at 12:41

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

fickerra Over a year ago

Thanks for the reply, but PREPARE and EXECUTE are not what is internally used by the driver. I'm trying to use Postgres's prepared statements feature (see the link at the top of my question). To use this feature, you have to send PREPARE and then EXECUTE to Postgres. I know it's a bit confusing since JDBC has a function named "prepareStatement" but that's actually just a coincidence, this isn't related to Postgres's prepared statements.

Mark Rotteveel Over a year ago

@fickerra, PostgreSQL JDBC PreparedStatement implementation already does all that for you.

Mark Rotteveel Over a year ago

Specifically (using the protocol equivalent of PREPARE): github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/core/v3/…

fickerra Over a year ago

You're right it can, but I was porting an app from .net that made its own prepared statements manually and I wanted to implement the app in the same way. It sounds like JDBC doesn't allow that, though, and instead expects you to use its automatic prepared statements system.

Craig Ringer Over a year ago

@fickerra Consider changing to using JDBC prepared statements part of the porting effort. I really don't recommend trying to bypass that.

Collectives™ on Stack Overflow

Using Postgres's prepared statements from JDBC fails with "there is no parameter $1"

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related