incompatible data type in conversion when using TIME type‏

[GuillaumeF]

Hi,

I have a simple piece of code who try to insert a java.sql.Time value into an Access database column of Type DATE/TIME, but when i execute this code, it throws an exception during setTime upon a PreparedStatement.

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver"); // sun.jdbc.odbc.JdbcOdbcDriver

final Connection c = DriverManager.getConnection("jdbc:ucanaccess://C:/Access/TestTime.accdb", "", "");
//jdbc:odbc:Driver={Microsoft Access Driver (.mdb, .accdb)};Dbq=C:/Access/TestTime.accdb

final PreparedStatement st = c.prepareStatement("INSERT INTO T_TEST_DATE (COL_DATE) VALUES(?)");

//st.setTimestamp(1, new java.sql.Timestamp(Calendar.getInstance().getTime().getTime()));
//st.setDate(1, new java.sql.Date(Calendar.getInstance().getTime().getTime()));
st.setTime(1, new java.sql.Time(Calendar.getInstance().getTime().getTime()));

st.execute();

1) The setTimestamp and the setDate both runs well.
2) The setTime throws :

net.ucanaccess.jdbc.UcanaccessSQLException: incompatible data type in conversion
at net.ucanaccess.jdbc.UcanaccessPreparedStatement.setTime(UcanaccessPreparedStatement.java:517)
at test.Test.main(Test.java:59)
Caused by: java.sql.SQLSyntaxErrorException: incompatible data type in conversion
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.throwError(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.setParameter(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.setTime(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessPreparedStatement.setTime(UcanaccessPreparedStatement.java:515)
... 1 more
Caused by: org.hsqldb.HsqlException: incompatible data type in conversion
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.types.DateTimeType.convertJavaToSQL(Unknown Source)
... 4 more

3) If i use the JdbcOdbcDriver with the same code, the setTime runs well.

4) I took a look in the class net.ucanaccess.jdbc.UCanAccessPreparedStatement and i saw that UCanAccess delegate the work to hsqldb JDBCPreparedStatement.
The type of the column in the JDBCPreparedStatement is DateType("TIMESTAMP").
It appear that java.sql.Date is correctly converted to java.sql.Timestamp (during setDate) but java.sql.Time not.

Thanks in advance for your answer.

Best regards
Guillaume.

[Marco Amadei]

Time type doesn't exist in access: it uses a datetime with a conventional day-month-year to represent a time instance.
Because you don't have to know these boring details, I have tried to support the PreparedStatement.setTime(and the needed conversions) in the 2.0.9.
Could you try it?
Cheers Marco

[Ihsan Ibrahim]

hi Marco,

For my latest project I'm using the following for my select MS Access statement :-

select Nz([Connect_Time],'0') AS Expr1 from table

I'm trying to get rid of null values using the Nz function.

The message displayed when I run with apache-tomcat-7.0.63 is as follows :-

HTTP Status 500 - net.ucanaccess.jdbc.UcanaccessSQLException: routine signature not found for: PUBLIC.NZ(TIMESTAMP,CHARACTER)

type Exception report

message net.ucanaccess.jdbc.UcanaccessSQLException: routine signature not found for: PUBLIC.NZ(TIMESTAMP,CHARACTER)

description The server encountered an internal error that prevented it from fulfilling this request.

exception

java.lang.RuntimeException: net.ucanaccess.jdbc.UcanaccessSQLException: routine signature not found for: PUBLIC.NZ(TIMESTAMP,CHARACTER)
Symproj.data.NewsItemDAO.findWorkingTran(NewsItemDAO.java:308)
Symproj.web.monthlyProcessServlet.doPost(monthlyProcessServlet.java:216)
javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

root cause

net.ucanaccess.jdbc.UcanaccessSQLException: routine signature not found for: PUBLIC.NZ(TIMESTAMP,CHARACTER)
net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:506)
Symproj.data.NewsItemDAO.findWorkingTran(NewsItemDAO.java:299)
Symproj.web.monthlyProcessServlet.doPost(monthlyProcessServlet.java:216)
javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

root cause

java.sql.SQLSyntaxErrorException: routine signature not found for: PUBLIC.NZ(TIMESTAMP,CHARACTER)
org.hsqldb.jdbc.Util.sqlException(Unknown Source)
org.hsqldb.jdbc.Util.sqlException(Unknown Source)
org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:504)
Symproj.data.NewsItemDAO.findWorkingTran(NewsItemDAO.java:299)
Symproj.web.monthlyProcessServlet.doPost(monthlyProcessServlet.java:216)
javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

root cause

org.hsqldb.HsqlException: routine signature not found for: PUBLIC.NZ(TIMESTAMP,CHARACTER)
org.hsqldb.error.Error.error(Unknown Source)
org.hsqldb.error.Error.error(Unknown Source)
org.hsqldb.RoutineSchema.getSpecificRoutine(Unknown Source)
org.hsqldb.FunctionSQLInvoked.resolveTypes(Unknown Source)
org.hsqldb.QuerySpecification.resolveExpressionTypes(Unknown Source)
org.hsqldb.QuerySpecification.resolveTypesPartOne(Unknown Source)
org.hsqldb.QueryExpression.resolve(Unknown Source)
org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
org.hsqldb.ParserCommand.compilePart(Unknown Source)
org.hsqldb.ParserCommand.compileStatement(Unknown Source)
org.hsqldb.Session.compileStatement(Unknown Source)
org.hsqldb.StatementManager.compile(Unknown Source)
org.hsqldb.Session.execute(Unknown Source)
org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:504)
Symproj.data.NewsItemDAO.findWorkingTran(NewsItemDAO.java:299)
Symproj.web.monthlyProcessServlet.doPost(monthlyProcessServlet.java:216)
javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

Appreciate your assistance on the above problem.

Thanking u in advance.

[Marco Amadei]

'0' isn't a datetime so it can't be used as alternative value. Nevertheless Nz(datetime,datetime) isn't implemented yet, and this will be in the 3.0.5. In the meantime you may want to use the nvl function.