Estado HTTP 500 - Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

Question

I am using Spring with hibernate, firebird when i run as application i´ve got this error. If i connected in mysql it works ok. Wheres is the wrong? Thx very much. It´s a Local server accounting Software Desktop and I need this data for export another format (I'm sorry my English Learning).

causa raíz 

org.hibernate.exception.SQLGrammarException: could not prepare statement
    org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
    org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:188)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:159)
    org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1858)


    INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: create table HT_empresas (CLAVE numeric(18,0) not null, hib_sess_id CHAR(36))
oct 23, 2014 2:58:26 PM org.springframework.web.servlet.handler.AbstractHandlerMethodMapping registerHandlerMethod
Información: Mapped "{[/],methods=[GET],params=[],headers=[],consumes=[],produces=[],custom=[]}" onto public java.lang.String com.atorresbr.springapp.EmpresasController.list(org.springframework.ui.Model)
oct 23, 2014 2:58:27 PM org.springframework.orm.hibernate4.HibernateTransactionManager afterPropertiesSet
Información: Using DataSource [org.apache.commons.dbcp.BasicDataSource@1e3ff47] of Hibernate SessionFactory for HibernateTransactionManager
oct 23, 2014 2:58:27 PM org.springframework.web.servlet.FrameworkServlet initServletBean
Información: FrameworkServlet 'apliconta2web': initialization completed in 3840 ms
oct 23, 2014 2:58:27 PM org.apache.coyote.AbstractProtocol start
Información: Starting ProtocolHandler ["http-bio-8080"]
oct 23, 2014 2:58:27 PM org.apache.coyote.AbstractProtocol start
Información: Starting ProtocolHandler ["ajp-bio-8009"]
oct 23, 2014 2:58:27 PM org.apache.catalina.startup.Catalina start
Información: Server startup in 7945 ms
Hibernate: select empresas0_.CLAVE as CLAVE1_0_, empresas0_.ACT_CONTRASE as ACT2_0_, empresas0_.ADMON as ADMON3_0_, empresas0_.BALANCE as BALANCE4_0_, empresas0_.BALANCE_UE as BALANCE5_0_, empresas0_.COBROS as COBROS6_0_, empresas0_.CODPAIS as CODPAIS7_0_, empresas0_.CODPOSTAL as CODPOSTA8_0_, empresas0_.CONTACTO as CONTACTO9_0_, empresas0_.CONTRASE as CONTRAS10_0_, empresas0_.COOPERA as COOPERA11_0_, empresas0_.CORREO as CORREO12_0_, empresas0_.CRITERIOCAJA as CRITERI13_0_, empresas0_.DNICIF as DNICIF14_0_, empresas0_.DOC3W_AUTOGESTION as DOC15_0_, empresas0_.DOC3W_REFURL as DOC16_0_, empresas0_.EJERCICIO as EJERCIC17_0_, empresas0_.E_RETEN as E18_0_, empresas0_.FAX as FAX19_0_, empresas0_.FECHAFIN as FECHAFI20_0_, empresas0_.FECHAINI as FECHAIN21_0_, empresas0_.FECHA_BAJA as FECHA22_0_, empresas0_.FOLIO_R as FOLIO23_0_, empresas0_.GES_DOCU as GES24_0_, empresas0_.GES_PROYECTOS as GES25_0_, empresas0_.HOJA_R as HOJA26_0_, empresas0_.IGIC_IVA as IGIC27_0_, empresas0_.ILOGO as ILOGO28_0_, empresas0_.INUMERO as INUMERO29_0_, empresas0_.LIBRO_R as LIBRO30_0_, empresas0_.MOVILCONTACTO as MOVILCO31_0_, empresas0_.MUNICIPIO as MUNICIP32_0_, empresas0_.NIVEL as NIVEL33_0_, empresas0_.NOMBRE as NOMBRE34_0_, empresas0_.NUMERO as NUMERO35_0_, empresas0_.OBSERVACIONES as OBSERVA36_0_, empresas0_.OCUPADO as OCUPADO37_0_, empresas0_.OTROS_R as OTROS38_0_, empresas0_.PAG_ASIEN as PAG39_0_, empresas0_.PISO as PISO40_0_, empresas0_.PROVINCIA as PROVINC41_0_, empresas0_.PUERTA as PUERTA42_0_, empresas0_.REGCOMPRAS as REGCOMP43_0_, empresas0_.REGMER as REGMER44_0_, empresas0_.REGVENTAS as REGVENT45_0_, empresas0_.SECCION_R as SECCION46_0_, empresas0_.SIGLA as SIGLA47_0_, empresas0_.SUBC_DESP1 as SUBC48_0_, empresas0_.SUBC_DESP2 as SUBC49_0_, empresas0_.SUBC_DESP3 as SUBC50_0_, empresas0_.SUBC_DESP4 as SUBC51_0_, empresas0_.SUBC_DESP7 as SUBC52_0_, empresas0_.SUBC_DESPC1 as SUBC53_0_, empresas0_.SUBC_DESPI1 as SUBC54_0_, empresas0_.SUBC_DESPI2 as SUBC55_0_, empresas0_.SUBC_DESPI3 as SUBC56_0_, empresas0_.SUBC_DESPI4 as SUBC57_0_, empresas0_.SUBC_DESPI5 as SUBC58_0_, empresas0_.SUBC_DESPI6 as SUBC59_0_, empresas0_.SUB_TYPE as SUB60_0_, empresas0_.TELEFONO as TELEFON61_0_, empresas0_.TELEFONO_CONT as TELEFON62_0_, empresas0_.TIPO_EMP as TIPO63_0_, empresas0_.TIPO_R as TIPO64_0_, empresas0_.TOMO_R as TOMO65_0_, empresas0_.VIAPUBLICA as VIAPUBL66_0_, empresas0_.WEB as WEB67_0_, empresas0_.WEBCONTACTO as WEBCONT68_0_ from empresas empresas0_
oct 23, 2014 2:58:29 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 335544569, SQLState: 42000
oct 23, 2014 2:58:29 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 2122
SUB_TYPE
oct 23, 2014 2:58:29 PM org.apache.catalina.core.StandardWrapperValve invoke
Grave: El Servlet.service() para el servlet [apliconta2web] en el contexto con ruta [/Apliconta2Web] lanzó la excepción [Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement] con causa raíz
org.firebirdsql.gds.GDSException: Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 2122
SUB_TYPE
    at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.readStatusVector(AbstractJavaGDSImpl.java:2092)
    at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.receiveResponse(AbstractJavaGDSImpl.java:2042)
    at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.iscDsqlPrepare(AbstractJavaGDSImpl.java:1465)
    at org.firebirdsql.gds.impl.GDSHelper.prepareStatement(GDSHelper.java:190)
    at org.firebirdsql.jdbc.AbstractStatement.prepareFixedStatement(AbstractStatement.java:1441)
    at org.firebirdsql.jdbc.AbstractPreparedStatement.prepareFixedStatement(AbstractPreparedStatement.java:1285)
    at org.firebirdsql.jdbc.AbstractPreparedStatement.<init>(AbstractPreparedStatement.java:135)
    at org.firebirdsql.jdbc.FBPreparedStatement.<init>(FBPreparedStatement.java:40)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at org.firebirdsql.jdbc.FBStatementFactory.createPreparedStatement(FBStatementFactory.java:89)
    at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:1187)
    at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:942)
    at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:896)
    at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:324)
    at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:161)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:159)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1858)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1835)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1815)
    at org.hibernate.loader.Loader.doQuery(Loader.java:899)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doList(Loader.java:2522)
    at org.hibernate.loader.Loader.doList(Loader.java:2508)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2338)
    at org.hibernate.loader.Loader.list(Loader.java:2333)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:490)
    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:1269)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at com.atorresbr.springapp.EmpresasDAOImpl.findAll(EmpresasDAOImpl.java:26)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.Nativ

app-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:mvc="http://www.springframework.org/schema/mvc" 
  xmlns:beans="http://www.springframework.org/schema/beans"
  xmlns:context="http://www.springframework.org/schema/context" 
  xmlns:tx="http://www.springframework.org/schema/tx"
  xmlns:p="http://www.springframework.org/schema/p"
  xsi:schemaLocation="http://www.springframework.org/schema/mvc 
  http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
    http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/tx 
    http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
    http://www.springframework.org/schema/context 
    http://www.springframework.org/schema/context/spring-context-3.2.xsd">

    <!-- Scans the classpath of this application for @Components @Service @Repository to deploy as 
        beans -->
    <context:component-scan base-package="com.atorresbr.springapp" />

    <!-- Configures the @Controller programming model -->
    <mvc:annotation-driven />

      <!-- Map simple view name such as "test" into /WEB-INF/test.jsp -->
  <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
    <property name="prefix" value="/WEB-INF/" />
    <property name="suffix" value=".jsp" />
  </bean>

  <bean id="propertyConfigurer"
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
        p:location="/WEB-INF/jdbc.properties" />

  <!-- JDBC Data Source. It is assumed you have MySQL running on localhost port 3306 with
       username root and blank password. Change below if it's not the case -->

       <bean id="myDataSource"
        class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
        p:driverClassName="${jdbc.driverClassName}"
        p:url="${jdbc.databaseurl}" 
        p:username="${jdbc.username}"
        p:password="${jdbc.password}" 
        p:validationQuery="${jdbc.validationQuery}" />
 <!--       
  <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/pizzashop"/>
    <property name="username" value="root"/>
    <property name="password" value=""/>
    <property name="validationQuery" value="SELECT 1"/>
  </bean>   -->

  <!-- Hibernate Session Factory -->
  <bean id="mySessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="myDataSource"/>
    <property name="packagesToScan">
      <array>
        <value>com.atorresbr.springapp</value>
      </array>
    </property>
    <property name="hibernateProperties">
    <props>
                <prop key="hibernate.dialect">${jdbc.dialect}</prop>
                <prop key="hibernate.show_sql">true</prop> 
            </props>

    </property>
  </bean>

  <!-- Hibernate Transaction Manager -->
  <bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="mySessionFactory"/>
  </bean>

  <!-- Activates annotation based transaction management -->
  <tx:annotation-driven transaction-manager="transactionManager"/>
</beans>

jdbc.propierties

jdbc.driverClassName= org.firebirdsql.jdbc.FBDriver
jdbc.dialect=org.hibernate.dialect.FirebirdDialect
jdbc.databaseurl=jdbc:firebirdsql:PROSYTH-PC/3050:C:/aplifisa/contcs/CONTBD
jdbc.username=SYSDBA
jdbc.password=masterkey
jdbc.validationQuery=Select 1 from rdb$database

EmpresasController.java

package com.atorresbr.springapp;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.atorresbr.springapp.EmpresasService;
import com.atorresbr.springapp.Empresas;

@Controller
@RequestMapping("/")
public class EmpresasController {
     // @Autowired private EmpresasDAO empresasDAO;
      @Autowired private EmpresasService empresasService;

    protected final Log logger = LogFactory.getLog(getClass());


    @RequestMapping(method = RequestMethod.GET)
    public String list(Model model) {
      List<Empresas> empresas = empresasService.findAll();
      model.addAttribute("empresas", empresas);
      return "views/index";}

EmpresasServiceIMPL

package com.atorresbr.springapp;
//imports..
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.atorresbr.springapp.Empresas; 

@Service

public class EmpresasServiceImpl implements EmpresasService {

    @Autowired
    private EmpresasDAO empresasDAO;

/**
* @Transactional annotation below will trigger Spring Hibernate transaction manager to automatically create
* a hibernate session. See src/main/webapp/WEB-INF/servlet-context.xml
*/
@Transactional
public List<Empresas> findAll() {
    return empresasDAO.findAll();

        }
}

EmpresasDAOIMPL

package com.atorresbr.springapp;

//imports..
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;


@Repository
@SuppressWarnings({"unchecked", "rawtypes"})


public class EmpresasDAOImpl implements EmpresasDAO {
@Autowired private SessionFactory sessionFactory;

/**
* @Transactional annotation below will trigger Spring Hibernate transaction manager to automatically create
* a hibernate session. See src/main/webapp/WEB-INF/servlet-context.xml
*/
@Transactional
public List<Empresas> findAll() {
 Session session = sessionFactory.getCurrentSession();
 List empresas = session.createQuery("from Empresas").list();
 return empresas;
}
}

Empresas.java

    package com.atorresbr.springapp;
    // imports ..
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.Table;

    import java.sql.Blob;
    import java.util.Date;


@Entity
@Table(name = "empresas")
public class Empresas {

@Id 
@Column(name = "CLAVE")
@GeneratedValue private long CLAVE;

@Column(name = "NOMBRE")
private String NOMBRE;

@Column(name = "DNICIF")
private String  DNICIF;

@Column(name = "SIGLA")
private String SIGLA;

@Column(name = "VIAPUBLICA")
private String VIAPUBLICA;

@Column(name = "NUMERO")
private String NUMERO;

@Column(name = "PISO")
private String PISO;

@Column(name = "PUERTA")
private String PUERTA;

@Column(name = "MUNICIPIO")
private String MUNICIPIO;

@Column(name = "CODPOSTAL")
private String CODPOSTAL;

@Column(name = "PROVINCIA")
private String PROVINCIA;

@Column(name = "TELEFONO")
private String TELEFONO;

@Column(name = "FAX")
private String FAX;

@Column(name = "ADMON")
private String ADMON;

@Column(name = "NIVEL")
private long NIVEL;

@Column(name = "EJERCICIO")
private String EJERCICIO;

@Column(name = "FECHAINI")
private Date FECHAINI;

@Column(name = "FECHAFIN")
private Date FECHAFIN;

@Column(name = "PAG_ASIEN")
private String PAG_ASIEN;
private String COBROS;

@Column(name = "OCUPADO")
private String OCUPADO;

@Column(name = "CONTRASE")
private String CONTRASE;

@Column(name = "ACT_CONTRASE")
private String ACT_CONTRASE;

@Column(name = "BALANCE")
private String BALANCE;

@Column(name = "COOPERA")
private String COOPERA;

@Column(name = "WEB")
private String WEB;

@Column(name = "CORREO")
private String CORREO;

@Column(name = "IGIC_IVA")
private String IGIC_IVA;

@Column(name = "OBSERVACIONES")
private String OBSERVACIONES;

@Column(name = "CONTACTO")
private String CONTACTO;

@Column(name = "TELEFONO_CONT")
private String TELEFONO_CONT;

@Column(name = "CODPAIS")
private String CODPAIS;

@Column(name = "TIPO_EMP")
private String TIPO_EMP;

@Column(name = "BALANCE_UE")
private String BALANCE_UE;

@Column(name = "E_RETEN")
private String E_RETEN;

@Column(name = "FECHA_BAJA")
private Date FECHA_BAJA;

@Column(name = "GES_PROYECTOS")
private String GES_PROYECTOS;

@Column(name = "TOMO_R")
private String TOMO_R;

@Column(name = "LIBRO_R")
private String LIBRO_R;

@Column(name = "SECCION_R")
private String SECCION_R;

@Column(name = "FOLIO_R")
private String FOLIO_R;

@Column(name = "TIPO_R")
private String TIPO_R;

@Column(name = "HOJA_R")
private String HOJA_R;

@Column(name = "OTROS_R")
private String OTROS_R;

@Column(name = "REGMER")
private String REGMER;

@Column(name = "SUB_TYPE")
private Blob SUB_TYPE;

@Column(name = "ILOGO")
private String ILOGO;

@Column(name = "INUMERO")
private String INUMERO;

@Column(name = "WEBCONTACTO")
private String WEBCONTACTO;

@Column(name = "MOVILCONTACTO")
private String MOVILCONTACTO;

@Column(name = "REGVENTAS")
private String REGVENTAS;

@Column(name = "REGCOMPRAS")
private String REGCOMPRAS;

@Column(name = "SUBC_DESP1")
private String SUBC_DESP1;

@Column(name = "SUBC_DESP2")
private String SUBC_DESP2;

@Column(name = "SUBC_DESP3")
private String SUBC_DESP3;

@Column(name = "SUBC_DESP4")
private String SUBC_DESP4;

@Column(name = "SUBC_DESPI1")
private String SUBC_DESPI1;

@Column(name = "SUBC_DESPI2")
private String SUBC_DESPI2;

@Column(name = "SUBC_DESPI3")
private String SUBC_DESPI3;

@Column(name = "SUBC_DESPI4")
private String SUBC_DESPI4;

@Column(name = "SUBC_DESPI5")
private String SUBC_DESPI5;

@Column(name = "SUBC_DESPI6")
private String SUBC_DESPI6;

@Column(name = "SUBC_DESPC1")
private String SUBC_DESPC1;

@Column(name = "SUBC_DESP7")
private String SUBC_DESP7;

@Column(name = "GES_DOCU")
private String GES_DOCU;

@Column(name = "CRITERIOCAJA")
private String CRITERIOCAJA;

@Column(name = "DOC3W_REFURL")
private String DOC3W_REFURL;

@Column(name = "DOC3W_AUTOGESTION")
private String DOC3W_AUTOGESTION;

         /* getters & setters */

      public long getCLAVE() {
        return CLAVE;
    }
    public void setCLAVE(long CLAVE) {
        this.CLAVE = CLAVE;
    }
     public String getNOMBRE() {
        return NOMBRE;
    }
    public void setNOMBRE(String NOMBRE) {
        this.NOMBRE = NOMBRE;
    }
    .
    .
    .
Etc etc //setters.

Answer 1

Given the error:

Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 2122
SUB_TYPE

and character 2122 in the generated query is at SUB_TYPE in the generated query (around ..., empresas0_.SUB_TYPE as SUB60_0_, ...); and given that SUB_TYPE is a keyword (but not a reserved word) used to indicate the type of a blob (eg in create table or casts), my initial guess would be with:

@Column(name = "SUB_TYPE")
private Blob SUB_TYPE;

However a quick test - against a snapshot of Firebird 3 - shows that this is a valid column name. I currently don't have access to a released Firebird version (eg Firebird 2.5.3), so it is possible that some of the parser rules have been relaxed between Firebird 2.5 (or the version you are using) and Firebird 3. If that is the case, then forcing Hibernate to quote the object name might help (note the extra quotes):

@Column(name = "`SUB_TYPE`")
private Blob SUB_TYPE;