# SQLGrammarException



## HibernateTester (13. Aug 2007)

Hi zusammen,

da ich mich in Hibernate einarbeiten will, so dachte ich mir probier einfach mal folgendes Beispiel aus:

www.laliluna.de/first-hibernate-example-tutorial.html

Da ich hierbei HSQL verwenden will, habe ich mir vorher die entsprechende Datenbank mit der Tabelle erzeugt.
Nach der Befolgung des Tutorials erhalte ich beim Start der Testapplikation folgende Fehlermeldung (auf das Wesentliche gekürzt):


```
14:38:36,901  INFO Environment:514 - Hibernate 3.2.4.sp1
14:38:36,901  INFO Environment:547 - hibernate.properties not found
14:38:36,916  INFO Environment:681 - Bytecode provider name : cglib
14:38:36,916  INFO Environment:598 - using JDK 1.4 java.sql.Timestamp handling
14:38:37,010  INFO Configuration:1426 - configuring from resource: /hibernate.cfg.xml
14:38:37,010  INFO Configuration:1403 - Configuration resource: /hibernate.cfg.xml
14:38:37,166  INFO Configuration:553 - Reading mappings from resource : de/laliluna/example/Honey.hbm.xml
14:38:37,291  INFO HbmBinder:300 - Mapping class: de.laliluna.example.Honey -> honey
14:38:37,322  INFO Configuration:1541 - Configured SessionFactory: null
14:38:37,322 DEBUG InitSessionFactory:59 - classic factory
14:38:37,401  INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
14:38:37,401  INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 20
14:38:37,416  INFO DriverManagerConnectionProvider:45 - autocommit mode: false
14:38:37,416  INFO DriverManagerConnectionProvider:80 - using driver: org.hsqldb.jdbcDriver at URL: jdbc:hsqldb:D:/tmp/firsthibernate
14:38:37,416  INFO DriverManagerConnectionProvider:86 - connection properties: {user=sa, password=****}
14:38:37,807  INFO SettingsFactory:89 - RDBMS: HSQL Database Engine, version: 1.8.0
14:38:37,807  INFO SettingsFactory:90 - JDBC driver: HSQL Database Engine Driver, version: 1.8.0
14:38:37,838  INFO Dialect:152 - Using dialect: org.hibernate.dialect.HSQLDialect
14:38:37,854  INFO TransactionFactoryFactory:34 - Transaction strategy: org.hibernate.transaction.JDBCTransactionFactory
14:38:37,854  INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
14:38:37,854  INFO SettingsFactory:143 - Automatic flush during beforeCompletion(): disabled
14:38:37,854  INFO SettingsFactory:147 - Automatic session close at end of transaction: disabled
14:38:37,854  INFO SettingsFactory:154 - JDBC batch size: 15
14:38:37,854  INFO SettingsFactory:157 - JDBC batch updates for versioned data: disabled
14:38:37,854  INFO SettingsFactory:162 - Scrollable result sets: enabled
14:38:37,854  INFO SettingsFactory:170 - JDBC3 getGeneratedKeys(): disabled
14:38:37,854  INFO SettingsFactory:178 - Connection release mode: auto
14:38:37,854  INFO SettingsFactory:205 - Default batch fetch size: 1
14:38:37,854  INFO SettingsFactory:209 - Generate SQL with comments: disabled
14:38:37,854  INFO SettingsFactory:213 - Order SQL updates by primary key: disabled
14:38:37,854  INFO SettingsFactory:217 - Order SQL inserts for batching: disabled
14:38:37,854  INFO SettingsFactory:386 - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
14:38:37,854  INFO ASTQueryTranslatorFactory:24 - Using ASTQueryTranslatorFactory
14:38:37,854  INFO SettingsFactory:225 - Query language substitutions: {}
14:38:37,854  INFO SettingsFactory:230 - JPA-QL strict compliance: disabled
14:38:37,869  INFO SettingsFactory:235 - Second-level cache: enabled
14:38:37,869  INFO SettingsFactory:239 - Query cache: disabled
14:38:37,869  INFO SettingsFactory:373 - Cache provider: org.hibernate.cache.NoCacheProvider
14:38:37,869  INFO SettingsFactory:254 - Optimize cache for minimal puts: disabled
14:38:37,869  INFO SettingsFactory:263 - Structured second-level cache entries: disabled
14:38:37,869  INFO SettingsFactory:283 - Echoing all SQL to stdout
14:38:37,869  INFO SettingsFactory:290 - Statistics: disabled
14:38:37,869  INFO SettingsFactory:294 - Deleted entity synthetic identifier rollback: disabled
14:38:37,869  INFO SettingsFactory:309 - Default entity-mode: pojo
14:38:37,869  INFO SettingsFactory:313 - Named query checking : enabled
14:38:37,916  INFO SessionFactoryImpl:161 - building session factory
14:38:38,276  INFO SessionFactoryObjectFactory:82 - Not binding factory to JNDI, no JNDI name configured
14:38:38,416 DEBUG SQL:401 - select next value for honey_id_seq from dual_honey_id_seq
Hibernate: select next value for honey_id_seq from dual_honey_id_seq
14:38:38,432  WARN JDBCExceptionReporter:77 - SQL Error: -191, SQLState: S0002
14:38:38,432 ERROR JDBCExceptionReporter:78 - Sequence not found: HONEY_ID_SEQ in statement [select next value for honey_id_seq from dual_honey_id_seq]
org.hibernate.exception.SQLGrammarException: could not get next sequence value
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:96)
	at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:99)
	at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
	at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
	at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
	at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
	at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
	at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:535)
	at org.hibernate.impl.SessionImpl.save(SessionImpl.java:523)
	at org.hibernate.impl.SessionImpl.save(SessionImpl.java:519)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
	at $Proxy0.save(Unknown Source)
	at de.laliluna.example.TestExample.createHoney(TestExample.java:76)
	at de.laliluna.example.TestExample.main(TestExample.java:26)
Caused by: java.sql.SQLException: Sequence not found: HONEY_ID_SEQ in statement [select next value for honey_id_seq from dual_honey_id_seq]
	at org.hsqldb.jdbc.Util.throwError(Unknown Source)
	at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
	at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
	at org.hibernate.jdbc.AbstractBatcher.prepareSelectStatement(AbstractBatcher.java:123)
	at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:73)
	... 17 more
```

Meine Konfigurationsdatei(hibernate.cfg.xml) befindet sich im Root-Verzeichnis und beinhaltet folgenden Code:

```
<?xml version='1.0' encoding='utf-8'?> 
<!DOCTYPE hibernate-configuration PUBLIC 
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN" 
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> 

<hibernate-configuration> 
    <session-factory> 
         
        <property name="connection.driver_class">org.hsqldb.jdbcDriver</property> 
        <property name="connection.url">jdbc:hsqldb:D:/tmp/firsthibernate</property> 
        <property name="connection.username">sa</property> 
        <property name="connection.password"></property> 
         
        <property name="dialect">org.hibernate.dialect.HSQLDialect</property> 
         
        <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
        <property name="current_session_context_class">thread</property> 
         
        <property name="hibernate.show_sql">true</property> 
        <mapping resource="de/laliluna/example/Honey.hbm.xml"/> 
    </session-factory> 
</hibernate-configuration>
```

Da ich noch neu auf dem Gebiet bin, wäre ich euch sehr dankbar, wenn ihr mir bei der Fehlersuche behilflich sein könntet.


----------



## SlaterB (13. Aug 2007)

>  select next value for honey_id_seq from dual_honey_id_seq 
vielleicht
 select next value as honey_id_seq from dual_honey_id_seq 

-----

weißt du generell etwas von der honey_id oder auch dual_honey_id,
hat die jemand angelegt?


----------



## HibernateTester (13. Aug 2007)

Ich habe folgendes HSQLDB-Statement zur Generierung der Datenbank verwendet:


```
CREATE TABLE honey(id INTEGER IDENTITY, name VARCHAR, taste VARCHAR)
```

In dem Tutorial werden lediglich die Statements für MySQL oder PostgreSQL verwendet. Beispiele für die Statements aus dem Tutorial:
PostgreSQL: 
	
	
	
	





```
CREATE TABLE "public"."honey" (  id SERIAL,   name text,   taste text,   PRIMARY KEY(id));
```
MySQL:

```
CREATE TABLE `honey` (  `id` int(11) NOT NULL auto_increment,  `name` varchar(250) default NULL,  `taste` varchar(250) default NULL,  PRIMARY KEY  (`id`)) ENGINE=MyISAM CHARSET=latin1
```

Meiner Ansicht nach sollte das HSQL-Statement richtig sein.


----------



## Der Müde Joe (13. Aug 2007)

ich find diese Tuts bringens noch, geht aber nicht auf HSQL ein

http://www.hibernate.org/5.html

(die Reference Dokus)


----------



## Guest (14. Aug 2007)

Lass dir das ganze generieren. Es gibt dazu Ant-Tags (Stichwort: HibernateTool; hbm2ddl).


----------



## HibernateTester (14. Aug 2007)

Guten Morgen zusammen,

ich bin die Schritte des Tutorial nochmal durchgegangen und siehe da, keine Fehlermeldungen   :


```
08:21:37,015  INFO Environment:514 - Hibernate 3.2.4.sp1
08:21:37,031  INFO Environment:547 - hibernate.properties not found
08:21:37,031  INFO Environment:681 - Bytecode provider name : cglib
08:21:37,031  INFO Environment:598 - using JDK 1.4 java.sql.Timestamp handling
08:21:37,125  INFO Configuration:1426 - configuring from resource: /hibernate.cfg.xml
08:21:37,125  INFO Configuration:1403 - Configuration resource: /hibernate.cfg.xml
08:21:37,328  INFO Configuration:553 - Reading mappings from resource : de/laliluna/example/Honey.hbm.xml
08:21:37,500  INFO HbmBinder:300 - Mapping class: de.laliluna.example.Honey -> thoney
08:21:37,546  INFO Configuration:1541 - Configured SessionFactory: null
08:21:37,546 DEBUG InitSessionFactory:113 - classic factory
08:21:37,656  INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
08:21:37,656  INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 20
08:21:37,656  INFO DriverManagerConnectionProvider:45 - autocommit mode: false
08:21:37,671  INFO DriverManagerConnectionProvider:80 - using driver: org.hsqldb.jdbcDriver at URL: jdbc:hsqldb:D:/tmp/firsthibernate
08:21:37,671  INFO DriverManagerConnectionProvider:86 - connection properties: {user=sa, password=****}
08:21:38,093  INFO SettingsFactory:89 - RDBMS: HSQL Database Engine, version: 1.8.0
08:21:38,093  INFO SettingsFactory:90 - JDBC driver: HSQL Database Engine Driver, version: 1.8.0
08:21:38,140  INFO Dialect:152 - Using dialect: org.hibernate.dialect.HSQLDialect
08:21:38,156  INFO TransactionFactoryFactory:34 - Transaction strategy: org.hibernate.transaction.JDBCTransactionFactory
08:21:38,156  INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
08:21:38,156  INFO SettingsFactory:143 - Automatic flush during beforeCompletion(): disabled
08:21:38,156  INFO SettingsFactory:147 - Automatic session close at end of transaction: disabled
08:21:38,156  INFO SettingsFactory:154 - JDBC batch size: 15
08:21:38,156  INFO SettingsFactory:157 - JDBC batch updates for versioned data: disabled
08:21:38,156  INFO SettingsFactory:162 - Scrollable result sets: enabled
08:21:38,156  INFO SettingsFactory:170 - JDBC3 getGeneratedKeys(): disabled
08:21:38,156  INFO SettingsFactory:178 - Connection release mode: auto
08:21:38,156  INFO SettingsFactory:205 - Default batch fetch size: 1
08:21:38,156  INFO SettingsFactory:209 - Generate SQL with comments: disabled
08:21:38,156  INFO SettingsFactory:213 - Order SQL updates by primary key: disabled
08:21:38,156  INFO SettingsFactory:217 - Order SQL inserts for batching: disabled
08:21:38,156  INFO SettingsFactory:386 - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
08:21:38,156  INFO ASTQueryTranslatorFactory:24 - Using ASTQueryTranslatorFactory
08:21:38,171  INFO SettingsFactory:225 - Query language substitutions: {}
08:21:38,171  INFO SettingsFactory:230 - JPA-QL strict compliance: disabled
08:21:38,171  INFO SettingsFactory:235 - Second-level cache: enabled
08:21:38,171  INFO SettingsFactory:239 - Query cache: disabled
08:21:38,171  INFO SettingsFactory:373 - Cache provider: org.hibernate.cache.NoCacheProvider
08:21:38,171  INFO SettingsFactory:254 - Optimize cache for minimal puts: disabled
08:21:38,171  INFO SettingsFactory:263 - Structured second-level cache entries: disabled
08:21:38,187  INFO SettingsFactory:283 - Echoing all SQL to stdout
08:21:38,187  INFO SettingsFactory:290 - Statistics: disabled
08:21:38,187  INFO SettingsFactory:294 - Deleted entity synthetic identifier rollback: disabled
08:21:38,187  INFO SettingsFactory:309 - Default entity-mode: pojo
08:21:38,187  INFO SettingsFactory:313 - Named query checking : enabled
08:21:38,343  INFO SessionFactoryImpl:161 - building session factory
08:21:38,843  INFO SessionFactoryObjectFactory:82 - Not binding factory to JNDI, no JNDI name configured
08:21:38,859  INFO SchemaExport:154 - Running hbm2ddl schema export
08:21:38,859  INFO SchemaExport:179 - exporting generated schema to database
08:21:38,875  INFO SchemaExport:196 - schema export complete
08:21:39,046 DEBUG SQL:401 - select next value for honey_id_seq from dual_honey_id_seq
Hibernate: select next value for honey_id_seq from dual_honey_id_seq
08:21:39,093 DEBUG SQL:401 - insert into thoney (name, taste, id) values (?, ?, ?)
Hibernate: insert into thoney (name, taste, id) values (?, ?, ?)
08:21:39,109 DEBUG SQL:401 - select next value for honey_id_seq from dual_honey_id_seq
Hibernate: select next value for honey_id_seq from dual_honey_id_seq
08:21:39,109 DEBUG SQL:401 - insert into thoney (name, taste, id) values (?, ?, ?)
Hibernate: insert into thoney (name, taste, id) values (?, ?, ?)
08:21:39,125 DEBUG TestExample:36 - Honey: 1 Name: forest honey Taste: very sweet
08:21:39,125 DEBUG TestExample:37 - Honey: 2 Name: country honey Taste: tasty
08:21:39,437 DEBUG SQL:401 - select honey0_.id as id0_, honey0_.name as name0_, honey0_.taste as taste0_ from thoney honey0_
Hibernate: select honey0_.id as id0_, honey0_.name as name0_, honey0_.taste as taste0_ from thoney honey0_
08:21:39,437 DEBUG TestExample:53 - Honey: 1 Name: forest honey Taste: very sweet
08:21:39,437 DEBUG TestExample:53 - Honey: 2 Name: country honey Taste: tasty
08:21:39,453 DEBUG SQL:401 - delete from thoney where id=?
Hibernate: delete from thoney where id=?
08:21:39,453 DEBUG SQL:401 - select honey0_.id as id0_, honey0_.name as name0_, honey0_.taste as taste0_ from thoney honey0_
Hibernate: select honey0_.id as id0_, honey0_.name as name0_, honey0_.taste as taste0_ from thoney honey0_
08:21:39,453 DEBUG TestExample:53 - Honey: 2 Name: country honey Taste: tasty
```

Damit sollte alles funktionieren. Ich könnte aber schwören, dass ich mich auch schon vorher exakt an die Angaben gehalten habe.


----------



## Javalearner (14. Aug 2007)

Hallo,

ich habe auch das Tutorial durchgearbeitet. Bei mir tauchen auch keine Fehlermeldungen auf, jedoch werden die Daten nicht in der Datenbank gespeichert.

Hier meine hibernate.cfg.xml:

```
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">


<hibernate-configuration>

  <session-factory>
    
    <property name="connection.url">jdbc:hsqldb:D:/tmp/firsthibernate</property>
    <property name="connection.username">sa</property>
    <property name="connection.password"></property>
    <property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
    <property name="dialect">org.hibernate.dialect.HSQLDialect</property>

    <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <!--  thread is the short name for
      org.hibernate.context.ThreadLocalSessionContext
      and let Hibernate bind the session automatically to the thread
    -->
    <property name="hibernate.connection.shutdown">true</property>
    <property name="current_session_context_class">thread</property>
    
    <property name="hibernate.show_sql">true</property>
    
    <property name="hibernate.hbm2ddl.auto">create</property>
    <mapping resource="de/laliluna/example/Honey.hbm.xml" />

  </session-factory>

</hibernate-configuration>
```

Meine Testklasse:

```
package de.laliluna.example;

import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;

import de.laliluna.hibernate.InitSessionFactory;

public class TestExample {

  private static Logger log =Logger.getLogger(TestExample.class);
  /**
   * @param args
   */
  public static void main(String[] args) {
    Honey forestHoney = new Honey();
    forestHoney.setName("forest honey");
    forestHoney.setTaste("very sweet");
    Honey countryHoney = new Honey();
    countryHoney.setName("country honey");
    countryHoney.setTaste("tasty");
    createHoney(forestHoney);
    createHoney(countryHoney);
    // our instances have a primary key now:
    log.debug(forestHoney);
    log.debug(countryHoney);
    listHoney();
//    deleteHoney(forestHoney);
//    listHoney();

  }

  private static void listHoney() {
    Transaction tx = null;
    Session session = InitSessionFactory.getInstance().getCurrentSession();
    try {
      tx = session.beginTransaction();
      List honeys = session.createQuery("select h from Honey as h")
          .list();
      for (Iterator iter = honeys.iterator(); iter.hasNext();) {
        Honey element = (Honey) iter.next();
        log.debug(element);
      }
      tx.commit();
    } catch (HibernateException e) {
      e.printStackTrace();
      if (tx != null && tx.isActive())
        tx.rollback();

    }
  }

  private static void deleteHoney(Honey honey) {
    Transaction tx = null;
    Session session = InitSessionFactory.getInstance().getCurrentSession();
    try {
      tx = session.beginTransaction();
      session.delete(honey);
      tx.commit();
    } catch (HibernateException e) {
      e.printStackTrace();
      if (tx != null && tx.isActive())
        tx.rollback();
    }
  }

  private static void createHoney(Honey honey) {
    Transaction tx = null;
    Session session = InitSessionFactory.getInstance().getCurrentSession();
    try {
      tx = session.beginTransaction();
      session.save(honey);
      tx.commit();
    } catch (HibernateException e) {
      e.printStackTrace();
      if (tx != null && tx.isActive())
        tx.rollback();
    }
  }
}
```
Liegt vielleicht ein Fehler in der config-Datei?


----------

