Ucanaccess problem in Select with Between Dates

Navigation
0

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

Inquiry: 

//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
    
asked by anonymous 01.06.2017 / 15:08

1 answer

0

Well, I was able to solve the problem, the error was the way the time was passing, it was in the format 'HH: mm' and Ucanaccess requires the format to be 'HH: mm: ss', follow 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#");
    
01.06.2017 / 19:14
Data entered in SQLite3 is not shown in the PyQT4 application accent problem when saving to database [duplicate]