Prepared Statements IN Clause Java Microsoft JDBC

Question

I've seen examples here and elsewhere that say this should work.
With '1' parameter in the IN CLAUSE I have success:

SELECT AVG( ( High_Temperature + Low_Temperature ) / 2.0 ) 
  FROM obs_masterAll 
  WHERE Observation_Valid_Time = ? AND 
        Location_ID IN ( ? );

Microsoft JDBC Driver 4.2 for SQL Server  4.2.6420.100
Parameter Count = 2
2010, 36.5

But, with more than one parameter in the IN CLAUSE, I get this:

SELECT AVG( ( High_Temperature + Low_Temperature ) / 2.0 ) 
  FROM obs_masterAll 
  WHERE Observation_Valid_Time = ? 
  AND Location_ID IN ( ?, ? );      <--- PROBLEM HERE

Microsoft JDBC Driver 4.2 for SQL Server  4.2.6420.100
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
    at com.microsoft.sqlserver.jdbc.SQLServerParameterMetaData.<init>(SQLServerParameterMetaData.java:423)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getParameterMetaData(SQLServerPreparedStatement.java:1659)
    at CreateAMultiStationDailyLoadCtrAvgHistogram.main(CreateAMultiStationDailyLoadCtrAvgHistogram.java:68)
Java Result: 1

Here's my code:

  prepStmt = buildPreparedStatement( conn.getConnectionObject() );  //function below
  conn.printDriverInfo();
  sqlDate = new java.sql.Date( beginDate.getTimeInMillis() );
  ParameterMetaData pmData = prepStmt.getParameterMetaData();
  System.out.println( "Parameter Count = " + pmData.getParameterCount() );
  prepStmt.setDate( 1, sqlDate );

  for( i = 0; i < locID.size(); i++ )
  {
    prepStmt.setString( ( i + 2 ), locID.get(i) );
  }

  rslt = prepStmt.executeQuery();

  private PreparedStatement buildPreparedStatement(java.sql.Connection con)
  throws SQLException
  {
    int i;
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT AVG( ( High_Temperature + Low_Temperature ) / 2.0 ) ");
    sb.append("FROM obs_masterAll ");
    sb.append("WHERE Observation_Valid_Time = ? AND " );
    sb.append("Location_ID IN ( " );

    for( i = 0; i < ( locID.size() - 1 ); i++ )
    {
      sb.append( "?" ).append(", ");
    }

    sb.append( "?" ).append(" );");
    System.out.println( sb.toString() );
    return( con.prepareStatement( sb.toString() ) );
  }

Is the problem my code, or the JDBC Driver?

You are not assigning the values to the placeholders properly. You wrote i < locID.size() in your forst for loop and in next for loop you wrote i < ( locID.size() - 1 ). And Check your code properly — The Guest, Dec 15 '15 at 21:48
"The Guest" --> Thanks so much for the quick response! I believe the problem is with the SQL syntax (per the first line of the stack trace above). The code errors out on the creation of the ParameterMetaData object, and doesn't even make it to assigning the parameters. — TheStressMan, Dec 15 '15 at 22:19

score 3 · Answer 1 · answered Dec 15 '15 at 23:02

3

I would say this is a problem in the version of the JDBC driver you using.

I could reproduce this problem using the same version of the SQL Server JDBC driver as you. I could not reproduce this problem running the same query against MySQL nor Oracle. I had to remove the semicolon from the end of the query to get it to run against Oracle, but this same change doesn't fix things for SQL Server.

I also couldn't reproduce the problem if I used the community technology preview edition of version 6.0 of the Microsoft JDBC driver (version 6.0.6629.101) instead.

answered Dec 15 '15 at 23:02

Luke Woodward

63,336
16
89
104

Luke ---> You're Awesome! Especially for going above and beyond and testing this on MySql and Oracle. I thought this was a driver issue, but thought the code review would be appropriate before jumping to conclusions. I'll definitely try the preview edition 6.0 and report back! – TheStressMan Dec 16 '15 at 01:18
@user3225536: thanks! I already had MySQL and Oracle databases installed so it didn't take too much extra effort to check those. The code in your question doesn't do much with the `ParameterMetaData`, so you can avoid the error by not fetching the parameter metadata. I suspect however that the code above has been taken from real code which does need to use the `ParameterMetaData`. Is this correct? – Luke Woodward Dec 16 '15 at 09:51
Luke ---> The ParameterMetaData is just for debugging. This originally presented itself when trying to 'set' the parameters. It complained that there were not enough. I threw that in there to see just how many there were. The rest of the story is told above. Again, I so appreciate you taking a look at this. A second pair of eyes and a code review is always a good thing. Thanks so much! – TheStressMan Dec 16 '15 at 21:08

score 0 · Accepted Answer · answered Feb 05 '16 at 16:46

The response above regarding the JDBC driver got me close. I was using a very old Microsoft JDBC driver, so the upgrade definitely helped. I'm now using the community technology preview edition of version 6.0 of the Microsoft JDBC driver (version 6.0.6629.101) instead as recommended above.

However, I still ran into problems.

When I removed the following line(s) from the code:

ParameterMetaData pmData = prepStmt.getParameterMetaData();
System.out.println( "Parameter Count = " + pmData.getParameterCount() );

Everything works as expected!

I'm not sure why the 'ParameterMetaData' data type does't work, but when removed from the code, it definitely solved my problems.

Fixed!

Prepared Statements IN Clause Java Microsoft JDBC

2 Answers2

Linked