CODEWITHSUNDEEP
javapostgresqljdbc
fickerra
fickerra

Reputation: 80

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

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?

Upvotes: 0

Views: 1964

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108933

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.

Upvotes: 2

Related Questions