How to specify null value in MS Access through the

Question

I am not able to call setNull on PreparedStatement using MS Access (sun.jdbc.odbc.JdbcOdbcDriver)

preparedStatement.setNull(index, sqltype). 

Is there a workaround for this? For LONGBINARY data type, I tried the following calls, neither worked.

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:363)

Answer 1

The answer that I have observed to work "quite well" for binding null to most data types with JDBC 4.1, Java 7, MS Access 2013 and the JDBC-ODBC bridge is this one, which I've 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 saw a similar error once when I was sending a SQL query with 2 conditions in the where clause. One of the conditions needed to be quoted. It was a number in varchar format. The MSSQL server required that the condition be quoted or else I saw the error You got in your question.

Answer 3

I just tested this and for an OLE Object (LONGBINARY) field in an Access 2010 database I found that all five of these variations allowed me to specify a null value as the parameter to a 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 particularly interesting that

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

does not work, considering that when we retrieve an OLE Object from an Access database what we get is a java.sql.Types.LONGVARBINARY according to a 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));

That 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 a history suggesting that after an earlier effort ("SQL/CLI") became part of the ISO SQL standard, Microsoft essentially forked their own version and eventually came up with ODBC. If that is the case, then early efforts to conform to an "ODBC 'standard'" may have faced the same difficulties as those trying to conform to Microsoft's RTF document "standard": the "standard" was whatever Microsoft implemented and was subject to change at Microsoft's sole discretion.

However, Microsoft's 1995 ODBC White Paper, available via the download link here, consistently refers to the "OLE Object" datatype as mapping to "*BINARY" or "raw" types (or, in the case of SQL Server, to the now-deprecated IMAGE datatype). So, the CHAR/BINARY discrepancy doesn't appear to be a case of some early ODBC quirk that just got perpetuated.

Certainly this mystery is not new. A forum thread here from ~11 years ago suggests that this issue arose when something changed after JDK 1.4 was released.

And finally, Oracle has stated that the JDBC-ODBC Bridge "will be removed in JDK 8" (ref: here). So, if there hasn't been an "official" explanation (or a fix, for that matter), it is becoming increasingly unlikely that any will be forthcoming.