Ucanaccess problem in Select with Between Dates

Asked

Viewed 38 times

0

I am having a problem when performing a query having as condition a period of two dates, this query in MS Access works perfectly however via code with ucanaccess library I get the error of 'Unknow Token', I believe the problem is in the character #, how to get around this situation ?

Consultation:

//turno 1
ResultSet rs = stmAccess.executeQuery("SELECT Avg(RENDIMENTO) FROM TabelaRendimento "
+ "WHERE ((DTH_UM_CARGA BETWEEN #'"+ano+"-"+mes+"-"+dia+" 22:50'#) And #'"+ano+"-"+mes+"-"+dia+" 06:49'#) AND TURNO ='"+turno+"'");
if(rs.next()) {                             
RendTurno1 = rs.getDouble(1);                                                               
}

Logcat:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.2 unknown token: 
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210)
    at GoogleSheets.Inicio$4.mousePressed(Inicio.java:865)
    at java.awt.AWTEventMulticaster.mousePressed(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$500(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: unknown token: 
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208)
    ... 33 more
java java-ee java-8

1 answer

0


Good people, I was able to solve the problem, the error was the way the time was passing, which was in the format 'HH:mm' and the Ucanaccess requires the format to be in 'HH:mm:ss', follows the updated code:

ResultSet rs = stmAccess.executeQuery("SELECT Avg(RENDIMENTO) FROM TabelaRendimento "
                                + "WHERE DTH_UM_CARGA BETWEEN #"+ano+"-"+mes+"-"+(dia-1)+" 22:50:00# AND #"+ano+"-"+mes+"-"+dia+" 06:49:59#");

Browser other questions tagged java java-ee java-8

You are not signed in. Login or sign up in order to post.