How to specify a null value in MS Access through the JDBC-ODBC bridge?

Question

I cannot call setNull on PreparedStatement using MS Access (sun.jdbc.odbc.JdbcOdbcDriver)

preparedStatement.setNull(index, sqltype). 

Is there a workaround for this? For the LONGBINARY data type, I tried the following calls, did not work.

 setNull(index, java.sql.Types.VARBINARY) setNull(index, java.sql.Types.BINARY) 

  java.sql.SQLException: [Microsoft] [ODBC Microsoft Access Driver] Invalid SQL data type
         at sun.jdbc.odbc.JdbcOdbc.createSQLException (JdbcOdbc.java:6957)
         at sun.jdbc.odbc.JdbcOdbc.standardError (JdbcOdbc.java:7114)
         at sun.jdbc.odbc.JdbcOdbc.SQLBindInParameterNull (JdbcOdbc.java:986)
         at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setNull (JdbcOdbcPreparedStatement.java data63)

Answer 1

The answer that I observed to work "good enough" to bind null to most data types with JDBC 4.1, Java 7, MS Access 2013 and the JDBC-ODBC bridge is the one I built into jOOQ :

 switch (sqlType) { case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: case Types.BLOB: stmt.setNull(nextIndex(), Types.VARCHAR); break; default: stmt.setString(nextIndex(), null); break; } 

Answer 2

I just tested this for the OLE Object field ( LONGBINARY ) in the Access 2010 database, I found that all five of these options allowed me to specify a null value as a parameter for PreparedStatement using vanilla JDBC / ODBC Driver={Microsoft Access Driver (*.mdb, *.accdb)} :

 s.setNull(4, java.sql.Types.LONGNVARCHAR); s.setNull(4, java.sql.Types.LONGVARCHAR); s.setNull(4, java.sql.Types.NCHAR); s.setNull(4, java.sql.Types.NVARCHAR); s.setNull(4, java.sql.Types.VARCHAR); 

It is especially interesting that

 s.setNull(4, java.sql.Types.LONGVARBINARY); 

doesn't work, considering that when we get an OLE Object from the Access database, we get java.sql.Types.LONGVARBINARY according to the ResultSetMetaData object:

 String SQL; SQL = "SELECT Photo FROM City WHERE City_ID = 12"; s = conn.createStatement(); s.executeQuery(SQL); ResultSet rs = s.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); String accessTypeName = rsmd.getColumnTypeName(1); int javaType = rsmd.getColumnType(1); String javaTypeName = ( javaType == java.sql.Types.LONGVARBINARY ? "java.sql.Types.LONGVARBINARY" : "some other Type" ); System.out.println(String.format("The database-specific type name for this column is '%s'", accessTypeName)); System.out.println(String.format("The SQL type for this column is: %d (%s)", javaType, javaTypeName)); 

This returns:

 The database-specific type name for this column is 'LONGBINARY' The SQL type for this column is: -4 (java.sql.Types.LONGVARBINARY) 

The Wikipedia article on ODBC includes history , which suggests that after an earlier effort ("SQL / CLI") became part of the ISO Standard SQL, Microsoft substantially forked its own version and eventually came up with ODBC. If this is the case, early attempts to comply with the ODBC standard may encounter the same difficulties as those attempting to comply with the Microsoft RTF standard “standard”: the “standard” was what Microsoft introduced and changed at Microsoft’s discretion.

However, the Microsoft Office ODBC White Paper, available from the download link here , consistently refers to the "OLE Object" data type as a mapping to "* BINARY" or "raw" (or, in the case of SQL Server, to the legacy IMAGE data type). So the CHAR / BINARY mismatch is not like some early ODBC response that has just been perpetuated.

Of course, this mystery is not new. The forum topic here from ~ 11 years ago suggests that this question arose when something changed after the release of JDK 1.4.

And finally, Oracle announced that the JDBC-ODBC Bridge will be removed in JDK 8 (link: here ). So, if there was no “official” explanation (or correction, for that matter), it is becoming increasingly unlikely that any of them will be ready.

Answer 3

I saw a similar error once when I sent an SQL query with two conditions in the where clause. One of the conditions that needed to be cited. It was a varchar format number. The MSSQL server required the condition to be specified, or I saw the error you received in your question.