2

Obviously, 

Select Message From "public".USERS Where ID = '1'

Is a perfectly legal statement.
So is 

Select Message From "public".USERS Where ID = '1'

In fact, they're identical. The only difference between the two is that the second is the result of a PreparedStatement that was initially

Select Message From "public".USERS Where ID = ?

And parameterised with the ID value of 1.

In fact, when I attempt to hard code the statement to be executed, things run perfectly fine, and I receive the expected output.

Through testing, I have figured out that the problem is actually that if the parameter is a string initially, there is no problem. So: 

PreparedStatement statement = connection.prepareStatement("Select * from table where ID = ?");  
statement.setString(1, param);

Will work, if param is declared as 

String param = "1";

But not if you declare it in any other way.
For example, 

String param = Integer.toString(1);
String param = String.valueOf(1);
String param = ""+1;

Will all cause the error.

I am completely at a loss as to A) What's causing such a peculiar error, and B) Why even if you convert the int to a string that has the exact same value of a string, the one that was originally an int causes an error.

Potatosaurus
  • 487
  • 2
  • 4
  • 15
  • When you say the second query "is the result of a PreparedStatement that was ... parameterised with the ID value of 1" do you mean that you did a `toString` on the `PreparedStatement` object after assigning the parameter value? Some JDBC drivers allow that to show the "effective" SQL statement, but they can also be deceiving because that may not be *exactly* what gets sent to the server. You could try logging at the server (see [this question](https://stackoverflow.com/q/722221/2144390)) to see what is actually received, and if there's a difference between the scenarios you describe later. – Gord Thompson Oct 17 '17 at 22:13
  • I am unable to reproduce your issue using "postgresql-42.1.4.jre7.jar" against PostgreSQL 9.6.5 on Windows. You may want to [edit] your question to include the versions of PostgreSQL and JDBC driver that you are using, along with the platform on which your application is running. – Gord Thompson Oct 18 '17 at 00:33
  • Looks like your `ID` column is a `varchar`/`text` - what about casting in the query, for example: `SELECT * FROM table WHERE id = ?::text` – Dave Gray Oct 18 '17 at 06:26

0 Answers0