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

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)

Have you checked the table in access/jet to make sure it allows null values to be entered? — Kevin Ross, Jun 11 '10 at 07:16

score 5 · Answer 1 · answered Oct 31 '13 at 17:13

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;
}

Gord Thompson · Answer 2 · 2013-11-04T15:11:29.407

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.

Nice, so that confirms [my finding](http://stackoverflow.com/a/19712785/521799)... I'd be really interested if there's a sensible reason for this to work (but not for `s.setString(4, null)`, for instance — Lukas Eder, Oct 31 '13 at 23:08
@LukasEder- "so that confirms my finding..." - It certainly seems to, hence my upvote; "I'd be really interested if there's a sensible reason for this to work (but not for `s.setString(4, null)`, for instance" - Me, too, but for the time being: *if it gets the job done...*. — Gord Thompson, Nov 01 '13 at 00:05
Oh, I wasn't actually fishing for votes, but thanks. You got mine too :-) I'll keep on observing this question, as I'd really like to be sure that this is the best way to proceed with JDBC-ODBC here. Because it kind of makes me sad... ;-) — Lukas Eder, Nov 01 '13 at 08:19
Aaagh, the JDBC-ODBC bridge will be removed!? That information by itself is worth a bounty. Thanks for all the research! — Lukas Eder, Nov 04 '13 at 16:15
@LukasEder You're welcome. Sorry to be the bearer of bad news. By the way, from now on you might want to use [Eeeek!](http://meta.stackexchange.com/a/83625/238021). ;) — Gord Thompson, Nov 07 '13 at 23:48

score 1 · Answer 3 · answered Jun 23 '11 at 19:43

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.

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

3 Answers3

Linked

Related