Hibernate with HSQLdb - SQLException requires collation in statement

[Padma Priya]

Hi all,

I am new to Hibernate. And i am at my wits end as none of the programs are working out. Could anyone please help me out with these errors.

Its a simple program from Harnessing Hibernate chapter 2. It deals with creating java files from the hbm.xml file using hbm2java and generating schema from hbm.xml file

I am using ant tasks for Maven and hibernate tools.


build.xml

  
<?xml version="1.0"?>
<project name="Harnessing Hibernate 3 (Developer's Notebook Second Edition)"
         default="db" basedir="."
         xmlns:artifact="antlib:org.apache.maven.artifact.ant">

  <!-- Set up properties containing important project directories -->
  <property name="source.root" value="src"/>
  <property name="class.root" value="classes"/>
  <property name="data.dir" value="data"/>

  <artifact:dependencies pathId="dependency.class.path">
    <dependency groupId="hsqldb" artifactId="hsqldb" version="1.8.0.7"/>
    <dependency groupId="org.hibernate" artifactId="hibernate"
                version="3.2.5.ga">
      <exclusion groupId="javax.transaction" artifactId="jta"/>
    </dependency>
    <dependency groupId="org.hibernate" artifactId="hibernate-tools"
                version="3.2.0.beta9a"/>
    <dependency groupId="org.apache.geronimo.specs"
                artifactId="geronimo-jta_1.1_spec" version="1.1"/>
    <dependency groupId="log4j" artifactId="log4j" version="1.2.14"/>
  </artifact:dependencies>

  <!-- Set up the class path for compilation and execution -->
  <path id="project.class.path">
      <!-- Include our own classes, of course -->
      <pathelement location="${class.root}" />
      <!-- Add the dependencies classpath -->
      <path refid="dependency.class.path"/>
  </path>

  <!-- Teach Ant how to use the Hibernate Tools -->
  <taskdef name="hibernatetool"
           classname="org.hibernate.tool.ant.HibernateToolTask"
           classpathref="project.class.path"/>

  <target name="db" description="Runs HSQLDB database management UI 
against the database file--use when application is not running">
      <java classname="org.hsqldb.util.DatabaseManager"
            fork="yes">
         <classpath refid="project.class.path"/>
         <arg value="-driver"/>
         <arg value="org.hsqldb.jdbcDriver"/>
         <arg value="-url"/>
         <arg value="jdbc:hsqldb:${data.dir}/music"/>
         <arg value="-user"/>
         <arg value="sa"/>
      </java>
  </target>

  <!-- Generate the java code for all mapping files in our source tree -->
  <target name="codegen" depends="prepare"
          description="Generate Java source from the O/R mapping files">
    <hibernatetool destdir="${source.root}">
      <configuration>
        <fileset dir="${source.root}">
          <include name="**/*.xml"/>
        </fileset>
      </configuration>
      <hbm2java/>
    </hibernatetool>
  </target>

  <!-- Create our runtime subdirectories and copy resources into them -->
  <target name="prepare" description="Sets up build structures">
    <mkdir dir="${class.root}"/>

    <!-- Copy our property files and O/R mappings for use at runtime -->
    <copy todir="${class.root}" >
      <fileset dir="${source.root}" >
        <include name="**/*.properties"/>
        <include name="**/*.hbm.xml"/>
      </fileset>
    </copy>
  </target>

  <!-- Generate the schemas for all mapping files in our class tree -->
  <target name="schema" depends="prepare"
          description="Generate DB schema from the O/R mapping files">

    <hibernatetool destdir="${source.root}">
      <configuration>
        <fileset dir="${class.root}">
          <include name="**/*.hbm.xml"/>
        </fileset>
      </configuration>
      <hbm2ddl drop="yes" />
    </hibernatetool>
  </target>

</project>
  

hibernate.properties

hibernate.dialect=org.hibernate.dialect.HSQLDialect
hibernate.connection.driver_class=org.hsqldb.jdbcDriver
hibernate.connection.url=jdbc:hsqldb:data/music
hibernate.connection.username=sa
hibernate.connection.password=
hibernate.connection.shutdown=true
hibernate.jdbc.batch_size=0

log4j.properties

  ### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file hibernate.log ###
#log4j.appender.file=org.apache.log4j.FileAppender
#log4j.appender.file.File=hibernate.log
#log4j.appender.file.layout=org.apache.log4j.PatternLayout
#log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ###

log4j.rootLogger=warn, stdout

log4j.logger.org.hibernate=info
#log4j.logger.org.hibernate=debug

### log HQL query parser activity
#log4j.logger.org.hibernate.hql.ast.AST=debug

### log just the SQL
#log4j.logger.org.hibernate.SQL=debug

### log JDBC bind parameters ###
log4j.logger.org.hibernate.type=info
#log4j.logger.org.hibernate.type=debug

### log schema export/update ###
log4j.logger.org.hibernate.tool.hbm2ddl=debug

### log HQL parse trees
#log4j.logger.org.hibernate.hql=debug

### log cache activity ###
#log4j.logger.org.hibernate.cache=debug

### log transaction activity
#log4j.logger.org.hibernate.transaction=debug

### log JDBC resource acquisition
#log4j.logger.org.hibernate.jdbc=debug

### enable the following line if you want to track down connection ###
### leakages when using DriverManagerConnectionProvider ###
#log4j.logger.org.hibernate.connection.DriverManagerConnectionProvider=trace
  

pom.xml

  <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <parent>
        <groupId>com.oreilly.hh</groupId>
        <artifactId>hib-dev-parent</artifactId>
        <version>2.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>
    <artifactId>hib-dev-ch02</artifactId>
    <packaging>jar</packaging>
    <name>Harnessing Hibernate: Chapter Two: Mapping</name>
    <version>2.0-SNAPSHOT</version>
</project>

  

So according to the build file i first run

D:\JavaProjects\MyProjects\HarnessingHibernate\ch02>ant prepare

Now Under D:\JavaProjects\MyProjects\HarnessingHibernate\ch02 I have

a classes folder


Then i run D:\JavaProjects\MyProjects\HarnessingHibernate\ch02>ant codegen

My src folder now has track.java.


Then i run D:\JavaProjects\MyProjects\HarnessingHibernate\ch02>ant schema

under D:\JavaProjects\MyProjects\HarnessingHibernate\ch02 data folder is created which contains music.script music.lck and music.properties files.

All the above mentioned targets run fine without any prob

But whe i run D:\JavaProjects\MyProjects\HarnessingHibernate\ch02>ant schema



I get the follwing error.

  The system cannot find message text for message number 0x2350 in the message fil
e for Application.

Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

D:\JavaProjects\MyProjects\HarnessingHibernate\ch02>ant db
Buildfile: D:\JavaProjects\MyProjects\HarnessingHibernate\ch02\build.xml

db:
     [java] java.sql.SQLException: error in script file line: 1 Unexpected token
 NAME, requires COLLATION in statement [SET DATABASE NAME]
     [java]     at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
     [java]     at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source)
     [java]     at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
     [java]     at org.hsqldb.jdbcDriver.connect(Unknown Source)
     [java]     at java.sql.DriverManager.getConnection(DriverManager.java:582)
     [java]     at java.sql.DriverManager.getConnection(DriverManager.java:185)
     [java]     at org.hsqldb.util.ConnectionDialog.createConnection(Unknown Sou
rce)
     [java]     at org.hsqldb.util.DatabaseManager.main(Unknown Source)
  

What does

[java] java.sql.SQLException: error in script file line: 1 Unexpected token
NAME, requires COLLATION in statement [SET DATABASE NAME] mean


main.script

SET DATABASE NAME HSQLDB2AAD2F63B1
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE SQL REFERENCES FALSE
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL NAMES FALSE
SET DATABASE TRANSACTION CONTROL LOCKS
SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED
SET FILES WRITE DELAY 500 MILLIS
SET FILES BACKUP INCREMENT TRUE
SET FILES CACHE SIZE 10000
SET FILES CACHE ROWS 50000
SET FILES SCALE 8
SET FILES LOB SCALE 32
SET FILES DEFRAG 20
SET FILES NIO TRUE
SET FILES LOG TRUE
SET FILES LOG SIZE 50
SET DATABASE TEXT TABLE DEFAULTS ''
CREATE USER SA PASSWORD ''
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE MEMORY TABLE PUBLIC.TRACK(TRACK_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,TITLE VARCHAR(255) NOT NULL,FILEPATH VARCHAR(255) NOT NULL,PLAYTIME TIME,ADDED DATE,VOLUME SMALLINT NOT NULL)
ALTER TABLE PUBLIC.TRACK ALTER COLUMN TRACK_ID RESTART WITH 1
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT DBA TO SA
SET SCHEMA SYSTEM_LOBS
INSERT INTO BLOCKS VALUES(0,2147483647,0)

music.properties

#HSQL Database Engine 2.0.0
#Wed Aug 25 23:56:59 PDT 2010
version=2.0.0
modified=no

Could anyone please help me in solving this problem as this example lays a strong foundation for my future examples and under standing Hibenate.

Thanks in advance for your time and patience.

With Regard
Deepthi