0

I have the procedure where i want to pass the exact values from jdbc that is passed from user after query format.

but when i am using preparedstatement with $$ it gives me an error saying :

org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2.

however without $$ it's working perfectly fine.please help me how to pass argument with $$ with preparestatement?

conn = DriverManager.getConnection(jdbcUrl, username, password);
String prepareselectSQL="SELECT get_sum(?,?,$$?$$)";
stmt=conn.prepareStatement(prepareselectSQL);
stmt.setInt(1,10);
stmt.setInt(2,200);
stmt.setString(3,"");
rs = stmt.executeQuery();
while (rs.next()) {
      System.out.println(rs.getString(1));
}

see below is my example query:

select  * from getdashboarddata_010('application','(upload + download)',?,'2018-02-05 00:00:00','2018-02-05 00:00:00','0','5-1','','hits','desc','','true');

below is the value which needs to replace with ? :

(select * ,  ''::text as srczonename, ''::text as srczonetype, ''::text as firewall_cat_type, ''::text as destzonename, ''::text as destzonetype ,  null::int as appresolver from fwapp_applicationutfv5_12hr ) fwapp_applicationutfv5_12hr where "5mintime" >='2018-05-04 00:00:00' and "5mintime" < '2018-05-04 23:59:59'

so,you can see my value already contains single quotes for that reason i need to pass this with dollar quoting within prepare statement.

Asha Koshti
  • 2,763
  • 4
  • 22
  • 30
  • 1
    what does `$$` mean? – Youcef LAIDANI May 07 '18 at 09:48
  • 1
    @YCF_L actually my query is containing $$,as $$ signifies in postgres , the exact way like single quotes should pass. https://stackoverflow.com/questions/12144284/what-are-used-for-in-pl-pgsql – Asha Koshti May 07 '18 at 09:50
  • Try `'?'` ($$ seems to be the **P/SQL** delimiter) and remove setString. This should work. Otherwise leave `?` and do `stmt.setString(3, "?")` – Joop Eggen May 07 '18 at 09:57
  • Placeholders should never be quoted. Neither with single quotes, nor with dollar quoting –  May 07 '18 at 10:03
  • "*for that reason i need to pass this with dollar quoting*" - no you do not need to do that. The `PreparedStatement` will do the work for you. Simply remove those quotes and everything will work just fine –  May 07 '18 at 10:32

1 Answers1

2

I think you are trying to use a "dollar quoted" string literal, and substitute a parameter into the string. You cannot do that a ? inside an SQL string literal. Within the SQL string literal, the dollar character is not recognized as a parameter placeholder. (It is just an ordinary literal dollar character ... in the string.)

If you need to do something that entails assembling a string value, you should you should do the assembly in Java. Something like this:

String sql = "SELECT get_sum(?, ?, ?)";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, 10);
stmt.setInt(2, 200);
stmt.setString(3, "'" + someString + "'");

The above will pass a string value that starts and ends with the ' character to the SQL get_sum stored procedure.

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
  • that somevalue already contains single quotes and it is formatted with messageformat.so one occurance of single quote gets disappear.after whatever is remained i need to pass as it is.so i need $$ in place of single quotes – Asha Koshti May 07 '18 at 10:04
  • I don't think that is correct. Please amend your question with an MCVE that demonstrates that behavior. I would be very, very surprised if Postgres is stripping quotes from a string that is passed via a `?` and `setString`. – Stephen C May 07 '18 at 10:09
  • @AshaKoshti: no you don't need that - that's precisely what a `PreparedStatement` is for: you don't have to worry about escaping embedded single quotes in your values. All you need is `SELECT get_sum(?, ?, ?)` and then `setString(3, someString)` - regardless of what `someString` contains –  May 07 '18 at 10:09
  • @StephenC i have updated the question.you can see that – Asha Koshti May 07 '18 at 10:14
  • 1
    Your updates don't make sense to me. Sorry. You appear to be trying to use `?` to insert SQL into SQL. You can't do that. – Stephen C May 07 '18 at 10:48