How to check NULL value of Date/Time while using UCanAccess JDBC Driver?

Question

While accessing MS Access DB using UCanAccess JDBC Driver i am facing too many problems in processing Date. I want to check where a Date/Time field is Null or Not. If there is Null then want to Update but i Cannot do that. The field 'logout_time' is 'Date/Time' data type in MSAccess database.

String sqlString = "update user_Log set logout_time = #" + 
                                    strEndDate + "# where (user_Id ="+ 
                                    userId +") & (logout_time = NULL)";


                    int l=stmt.executeUpdate(sqlString);

This will sends sqlString

update user_Log set logout_time = #12/13/2015 10:33:28# where (user_Id =3) & (logout_time = NULL);

This SQL statement execute well from MSAccess Query. But it can not execute from UCanAccess JDBC Driver. it will generating error:

    net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.3 incompatible data types in combination 
 This exception may  happen if you add integers representing units of time directly to datetime values using the arithmetic plus operator but without specifying the unit of date. 
   In this specific case you have to use, for example, <dateColumn> + 1 DAY.
    at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:222)

Gord Thompson Gord Thompson · Accepted Answer · 2015-12-13T16:55:09

This SQL statement execute well from MSAccess Query.

Not really. It may execute, but it also might not do what you want. Your code ...

WHERE ... (logout_time = NULL)

... is non-standard SQL which can yield unexpected results (details here). You want

WHERE ... (logout_time IS NULL)

You should also stop using dynamic SQL and instead use a PreparedStatement and a parameterized query to perform your update.

How to check NULL value of Date/Time while using UCanAccess JDBC Driver?

1 Answers