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?

Answer 1

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.

Answer 2

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!