javahibernatefirebirdjaybirdfirebird-embedded

Hibernate, GDS Exception. 335544569. Dynamic SQL Error with Embedded Firebird

I get this exception when I configure Hibernate for embedded firebird. this seems to be a dialect issue (not sure though).

Here is the situation I am in:

  1. I am trying to build an web application using Spring, Hibernate, Firebird (embedded). I am using Quartz for my scheduling part.
  2. I configure setting for Hibernate, Quartz using .properties files.
  3. I am able to get Quartz connected to my DB. But, unable to get this working for Hibernate.
  4. I use Jaybird-full-2.2.9.jar
  5. My database dialect is 3.

here is my app log with hibernate logs.

2016/05/05 18:34:45:978, DEBUG, , Obtaining JDBC connection,  
2016/05/05 18:34:46:021, DEBUG, , Obtained JDBC connection,  
2016/05/05 18:34:46:022, DEBUG, , initial autocommit status: true,  
2016/05/05 18:34:46:022, DEBUG, , disabling autocommit,  
2016/05/05 18:34:46:022, TRACE, , Unable to locate HQL query plan in cache; generating (from UserTable u where u.loginName = :loginName),  
2016/05/05 18:34:46:022, DEBUG, , parse() - HQL: from com.device.manager.entities.UserTable u where u.loginName = :loginName,  
2016/05/05 18:34:46:034, DEBUG, , --- HQL AST ---
 \-[QUERY] Node: 'query'
    +-[SELECT_FROM] Node: 'SELECT_FROM'
    |  \-[FROM] Node: 'from'
    |     \-[RANGE] Node: 'RANGE'
    |        +-[DOT] Node: '.'
    |        |  +-[DOT] Node: '.'
    |        |  |  +-[DOT] Node: '.'
    |        |  |  |  +-[DOT] Node: '.'
    |        |  |  |  |  +-[IDENT] Node: 'com'
    |        |  |  |  |  \-[IDENT] Node: 'device'
    |        |  |  |  \-[IDENT] Node: 'manager'
    |        |  |  \-[IDENT] Node: 'entities'
    |        |  \-[IDENT] Node: 'UserTable'
    |        \-[ALIAS] Node: 'u'
    \-[WHERE] Node: 'where'
       \-[EQ] Node: '='
          +-[DOT] Node: '.'
          |  +-[IDENT] Node: 'u'
          |  \-[IDENT] Node: 'loginName'
          \-[COLON] Node: ':'
             \-[IDENT] Node: 'loginName'
,  
2016/05/05 18:34:46:034, DEBUG, , throwQueryException() : no errors,  
2016/05/05 18:34:46:034, DEBUG, , select << begin [level=1, statement=select],  
2016/05/05 18:34:46:034, DEBUG, , FromClause{level=1} : com.device.manager.entities.UserTable (u) -> usertable0_,  
2016/05/05 18:34:46:041, DEBUG, , Resolved : u -> usertable0_.ID,  
2016/05/05 18:34:46:044, TRACE, , Handling property dereference [com.device.manager.entities.UserTable (u) -> loginName (class)],  
2016/05/05 18:34:46:044, DEBUG, , getDataType() : loginName -> org.hibernate.type.StringType@559855aa,  
2016/05/05 18:34:46:046, DEBUG, , Resolved : u.loginName -> usertable0_.LoginName,  
2016/05/05 18:34:46:049, DEBUG, , select : finishing up [level=1, statement=select],  
2016/05/05 18:34:46:049, DEBUG, , processQuery() :  ( SELECT ( FromClause{level=1} UserTable usertable0_ ) ( where ( = ( usertable0_.LoginName usertable0_.ID loginName ) ? ) ) ),  
2016/05/05 18:34:46:049, DEBUG, , Derived SELECT clause created.,  
2016/05/05 18:34:46:050, DEBUG, , Using FROM fragment [UserTable usertable0_],  
2016/05/05 18:34:46:050, DEBUG, , select >> end [level=1, statement=select],  
2016/05/05 18:34:46:050, DEBUG, , --- SQL AST ---
 \-[SELECT] QueryNode: 'SELECT'  querySpaces (UserTable)
    +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'usertable0_.ID as ID91_' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=u,role=null,tableName=UserTable,tableAlias=usertable0_,origin=null,columns={,className=com.device.manager.entities.UserTable}}}
    |  \-[SQL_TOKEN] SqlFragment: 'usertable0_.Address as Address91_, usertable0_.EmailId as EmailId91_, usertable0_.FirstName as FirstName91_, usertable0_.IsADSUser as IsADSUser91_, usertable0_.IsFirstTime as IsFirstT6_91_, usertable0_.IsInactiveUser as IsInacti7_91_, usertable0_.IsUserCreated as IsUserCr8_91_, usertable0_.LastName as LastName91_, usertable0_.LoginName as LoginName91_, usertable0_.Password as Password91_, usertable0_.Roles_FK_ID as Roles13_91_, usertable0_.UserCreatedDate as UserCre12_91_'
    +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[u], fromElementByTableAlias=[usertable0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'UserTable usertable0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=u,role=null,tableName=UserTable,tableAlias=usertable0_,origin=null,columns={,className=com.device.manager.entities.UserTable}}
    \-[WHERE] SqlNode: 'where'
       \-[EQ] BinaryLogicOperatorNode: '='
          +-[DOT] DotNode: 'usertable0_.LoginName' {propertyName=loginName,dereferenceType=ALL,propertyPath=loginName,path=u.loginName,tableAlias=usertable0_,className=com.device.manager.entities.UserTable,classAlias=u}
          |  +-[ALIAS_REF] IdentNode: 'usertable0_.ID' {alias=u, className=com.device.manager.entities.UserTable, tableAlias=usertable0_}
          |  \-[IDENT] IdentNode: 'loginName' {originalText=loginName}
          \-[NAMED_PARAM] ParameterNode: '?' {name=loginName, expectedType=org.hibernate.type.StringType@559855aa}
,  
2016/05/05 18:34:46:050, DEBUG, , throwQueryException() : no errors,  
2016/05/05 18:34:46:050, DEBUG, , HQL: from com.device.manager.entities.UserTable u where u.loginName = :loginName,  
2016/05/05 18:34:46:050, DEBUG, , SQL: select usertable0_.ID as ID91_, usertable0_.Address as Address91_, usertable0_.EmailId as EmailId91_, usertable0_.FirstName as FirstName91_, usertable0_.IsADSUser as IsADSUser91_, usertable0_.IsFirstTime as IsFirstT6_91_, usertable0_.IsInactiveUser as IsInacti7_91_, usertable0_.IsUserCreated as IsUserCr8_91_, usertable0_.LastName as LastName91_, usertable0_.LoginName as LoginName91_, usertable0_.Password as Password91_, usertable0_.Roles_FK_ID as Roles13_91_, usertable0_.UserCreatedDate as UserCre12_91_ from UserTable usertable0_ where usertable0_.LoginName=?,  
2016/05/05 18:34:46:050, DEBUG, , throwQueryException() : no errors,  
2016/05/05 18:34:46:054, TRACE, , HQL param location recognition took 1 mills (from UserTable u where u.loginName = :loginName),  
2016/05/05 18:34:46:056, TRACE, , Located HQL query plan in cache (from UserTable u where u.loginName = :loginName),  
2016/05/05 18:34:46:056, TRACE, , Find: from UserTable u where u.loginName = :loginName,  
2016/05/05 18:34:46:056, TRACE, , Named parameters: {loginName=admin},  
2016/05/05 18:34:46:057, TRACE, , Handling invocation of connection method [prepareStatement],  
2016/05/05 18:34:46:058, DEBUG, , GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 400
 [n/a],  
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 400

    at org.firebirdsql.jdbc.AbstractPreparedStatement.<init>(AbstractPreparedStatement.java:135)
    at org.firebirdsql.jdbc.FBPreparedStatement.<init>(FBPreparedStatement.java:45)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at org.firebirdsql.jdbc.FBStatementFactory.createPreparedStatement(FBStatementFactory.java:89)
    at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:1135)
    at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:890)
    at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:844)
    at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:310)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at com.sun.proxy.$Proxy78.prepareStatement(Unknown Source)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1720)
    at org.hibernate.loader.Loader.doQuery(Loader.java:828)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    at org.hibernate.loader.Loader.doList(Loader.java:2447)
    at org.hibernate.loader.Loader.doList(Loader.java:2433)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2263)
    at org.hibernate.loader.Loader.list(Loader.java:2258)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1215)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at com.device.manager.spring.dao.user.UserDaoImpl.getUserByName(UserDaoImpl.java:113)
    at com.device.manager.spring.services.user.UserServiceImpl.getUserByName(UserServiceImpl.java:55)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy64.getUserByName(Unknown Source)
    at com.device.manager.controllers.LoginController.LoginController(LoginController.java:53)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:217)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
    at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2503)
    at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2492)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.firebirdsql.gds.GDSException: Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 400

    at org.firebirdsql.gds.impl.jni.JniGDSImpl.native_isc_dsql_prepare(Native Method)
    at org.firebirdsql.gds.impl.jni.BaseGDSImpl.iscDsqlPrepare(BaseGDSImpl.java:576)
    at org.firebirdsql.gds.impl.GDSHelper.prepareStatement(GDSHelper.java:195)
    at org.firebirdsql.jdbc.AbstractStatement.prepareFixedStatement(AbstractStatement.java:1401)
    at org.firebirdsql.jdbc.AbstractPreparedStatement.prepareFixedStatement(AbstractPreparedStatement.java:1261)
    at org.firebirdsql.jdbc.AbstractPreparedStatement.<init>(AbstractPreparedStatement.java:132)
    ... 86 more
2016/05/05 18:34:46:059, WARN, , SQL Error: 335544569, SQLState: 42000,  
2016/05/05 18:34:46:059, ERROR, , GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 400
,  
2016/05/05 18:34:46:077, DEBUG, , committing,  
2016/05/05 18:34:46:077, TRACE, , Automatically flushing session,  
2016/05/05 18:34:46:077, TRACE, , before transaction completion,  
2016/05/05 18:34:46:077, DEBUG, , committed JDBC Connection,  
2016/05/05 18:34:46:077, DEBUG, , re-enabling autocommit,  
2016/05/05 18:34:46:077, TRACE, , after transaction completion,  
2016/05/05 18:34:46:077, TRACE, , after transaction completion,  
2016/05/05 18:34:46:078, TRACE, , Handling invocation of connection method [isReadOnly],  
2016/05/05 18:34:46:078, TRACE, , Closing session,  
2016/05/05 18:34:46:078, TRACE, , Closing logical connection,  
2016/05/05 18:34:46:078, TRACE, , Handling invocation of connection method [close],  
2016/05/05 18:34:46:078, TRACE, , Invalidating connection handle,  
2016/05/05 18:34:46:078, TRACE, , Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcResourceRegistryImpl@6516dcba],  
2016/05/05 18:34:46:078, DEBUG, , Releasing JDBC connection,  
2016/05/05 18:34:46:081, DEBUG, , Released JDBC connection,  
2016/05/05 18:34:46:081, DEBUG, , HHH000163: Logical connection releasing its physical connection,  
2016/05/05 18:34:46:081, DEBUG, , HHH000163: Logical connection releasing its physical connection,  
2016/05/05 18:34:46:081, DEBUG, , HHH000163: Logical connection releasing its physical connection,  
2016/05/05 18:34:46:081, DEBUG, , HHH000163: Logical connection releasing its physical connection,  
2016/05/05 18:34:46:081, TRACE, , Logical connection closed,  
2016/05/05 18:34:46:084, INFO, LoginController, adminInvalid UserName, 1

Any help is appreciated. I searched through net for this. but I suppose I am not able to hit the right search criteria.

Connection properties for Hibernate:

#Firebird properties:
#db.driver=org.firebirdsql.jdbc.FBDriver
#db.url=jdbc\:firebirdsql\://localhost\:3050/C\:/DM/DM_fdb/DM.FDB
#db.username=SYSDBA
#db.password= masterkey

#Embedded Firebird properties:
db.driver=org.firebirdsql.jdbc.FBDriver
db.url=jdbc\:firebirdsql\:embedded\:C\:/DM/DM_fdb/DM.FDB
db.username=SYSDBA
db.password=


#Hibernate Configuration:
hibernate.dialect=org.hibernate.dialect.FirebirdDialect
hibernate.show_sql=false
entitymanager.packages.to.scan=com.device.manager.entities
hibernate.hbm2ddl.auto=update

Code which triggers this exception.

@Override
    public List<Device> getAllDevice() {
        Criteria criteria = sessionFactory.getCurrentSession().createCriteria(
                Device.class);
        criteria.addOrder(Order.asc("model"));
        List<Device> deviceList = (List<Device>) criteria.list();
        return deviceList;
    }
Solution

  • Updating from embedded Firebird version 2.1.7 to 2.5.x solved my problem. I am able to connect now. Thank you very much Mark for hinting about the change from 2.1 to 2.5 version.