title: Can't create schema on initialize-database if hsqlbd is on server-mode
body: <p>I have Hsqldb 1) in memory</p>
  
  <pre><code>&lt;jdbc:embedded-database id="idtSimulatorDataSource" type="HSQL"&gt;
          &lt;jdbc:script location="classpath:hsql/idt_schema.sql"/&gt;
      &lt;/jdbc:embedded-database&gt;
  </code></pre>
  
  <p>2) on server-mode</p>
  
  <pre><code>&lt;bean id="idtSimulatorDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"&gt;
          &lt;property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver"/&gt;
          &lt;property name="username" value="sa"/&gt;
          &lt;property name="password" value=""/&gt;
          &lt;property name="url" value="jdbc:hsqldb:hsql://localhost:9001/idt_simulatordb"/&gt;
      &lt;/bean&gt;
  </code></pre>
  
  <p>1) all works properly
  2) can't create schema. If I create schema manually, then hibernate create all necessary DDL manipulations and everethyng works properly too.
  But I don't know where should I assign property for db initialization script</p>
  
  <p>Here are my persistence file</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0"&gt;
      &lt;persistence-unit name="IDTSimulator" transaction-type="RESOURCE_LOCAL"&gt;
          &lt;provider&gt;org.hibernate.ejb.HibernatePersistence&lt;/provider&gt;
  
          &lt;class&gt;com.nxsystems.simulator.idt.domain.IDTAccount&lt;/class&gt;
          &lt;class&gt;com.nxsystems.simulator.idt.domain.IDTCard&lt;/class&gt;
          &lt;class&gt;com.nxsystems.simulator.idt.domain.IDTCurrency&lt;/class&gt;
          &lt;class&gt;com.nxsystems.simulator.idt.domain.IDTCardHolder&lt;/class&gt;
          &lt;class&gt;com.nxsystems.simulator.idt.domain.IDTProgram&lt;/class&gt;
          &lt;class&gt;com.nxsystems.simulator.idt.domain.IDTTransaction&lt;/class&gt;
  
          &lt;exclude-unlisted-classes&gt;true&lt;/exclude-unlisted-classes&gt;
  
          &lt;properties&gt;
              &lt;!-- --&gt;
              &lt;property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/&gt;
              &lt;property name="hibernate.default_schema" value="simulators"/&gt;
              &lt;!-- --&gt;
              &lt;property name="hibernate.show_sql" value="true"/&gt;
              &lt;property name="hibernate.format_sql" value="true"/&gt;
              &lt;!-- --&gt;
              &lt;property name="hibernate.cache.use_second_level_cache" value="true"/&gt;
              &lt;property name="hibernate.cache.use_query_cache" value="false"/&gt;
              &lt;property name="hibernate.cache.region.factory_class" value="net.sf.ehcache.hibernate.EhCacheRegionFactory"/&gt;
              &lt;!-- --&gt;
              &lt;!--&lt;property name="hibernate.hbm2ddl.auto" value="create"/&gt;--&gt;
              &lt;property name="hibernate.hbm2ddl.auto" value="create-drop"/&gt;
              &lt;property name="hibernate.connection.pool_size" value="10"/&gt;
  
  
          &lt;/properties&gt;
      &lt;/persistence-unit&gt;
  &lt;/persistence&gt;
  </code></pre>
  
  <p>And here are my init file script</p>
  
  <pre><code>DROP SCHEMA IF EXISTS simulators CASCADE;
  CREATE SCHEMA SIMULATORS AUTHORIZATION SA -- not work yet
  CREATE TABLE SIMULATORS.dirtyhack();
  </code></pre>
  
  <p>Maybe it would be better to create schema automatically, but I don't know how</p>
  
  <p><strong>UPDATE</strong>
  I've founf how to launch db init script:</p>
  
  <blockquote>
    <p>jdbc:initialize-database data-source="idtSimulatorDataSource">
                
            </p>
  </blockquote>
  
  <p>Also I set </p>
  
  <pre><code>&lt;property name="hibernate.hbm2ddl.auto" value="create"/&gt;
  </code></pre>
  
  <p>But now I've got following error:</p>
  
  <pre><code>11:15:57,337  WARN (main) [JDBCExceptionReporter] SQL Error: -5501, SQLState: 42501
  11:15:57,370 ERROR (main) [JDBCExceptionReporter] user lacks privilege or object not found: IDT_CARD
  </code></pre>
  
  <p><a href="http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/rzala/rzalastc.html" rel="nofollow">http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/rzala/rzalastc.html</a></p>
  
  <pre><code>SQLSTATE Value:      42501  
  Meaning: The authorization ID does not have the privilege to perform the specified operation on the identified object.
  </code></pre>
  
  <p>What are privileges ?????</p>
  
  <p><strong>UPDATE:</strong>
  Now I'm really confused.
  I look into log. And see following:</p>
  
  <blockquote>
    <p>/<em>C2</em>/SET SCHEMA PUBLIC DROP SCHEMA IF EXISTS simulators CASCADE
    CREATE SCHEMA SIMULATORS AUTHORIZATION SA CREATE TABLE
    SIMULATORS.dirtyhack() DISCONNECT /<em>C3</em>/SET SCHEMA PUBLIC DISCONNECT
    /<em>C4</em>/SET SCHEMA PUBLIC DROP SCHEMA IF EXISTS simulators CASCADE
    CREATE SCHEMA SIMULATORS AUTHORIZATION SA DISCONNECT /<em>C5</em>/SET SCHEMA
    PUBLIC DISCONNECT /<em>C6</em>/SET SCHEMA PUBLIC</p>
  </blockquote>
  
  <p>But I recently changed my init script:</p>
  
  <pre><code>DROP SCHEMA IF EXISTS simulators CASCADE;
  CREATE SCHEMA SIMULATORS AUTHORIZATION sa -- not work yet
  CREATE TABLE SIMULATORS.dirtyhack1();
  </code></pre>
  
  <p>Where is my  <strong>dirtyhack1</strong> () table???
  I deleted all mentions about previous launches</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I've found the reason.
  This block is executed after Hibernate create its DDL manipulations:</p>
  
  <pre><code>&lt;jdbc:embedded-database id="idtSimulatorDataSource" type="HSQL"&gt;
          &lt;jdbc:script location="classpath:hsql/idt_schema.sql"/&gt;
      &lt;/jdbc:embedded-database&gt;
  </code></pre>
  
  <p>And my code contains such lines:</p>
  
  <pre><code>public static Server server = new Server();
  
      @AfterClass
      public static void tearDown1() throws Exception {
          server.shutdown();
      }
  
  
      @BeforeClass
      public static void setUp() throws Exception {
          HsqlProperties p = new HsqlProperties();
          p.setProperty("server.database.0", "file:./idt_simulatordb");
          p.setProperty("server.dbname.0", "idt_simulatordb");
          p.setProperty("server.port", "9001");
          server.setProperties(p);
          server.start();
  
          Connection conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:9001/idt_simulatordb", "sa", "");
          conn.setAutoCommit(true);
          Statement st = conn.createStatement();    // statements
          String expression1 = "DROP SCHEMA IF EXISTS simulators CASCADE;\n";
          (1)String expression2 = "CREATE SCHEMA SIMULATORS AUTHORIZATION SA;";
          String expression3 = "CREATE TABLE SIMULATORS.dirtyhack1();";
          st.executeUpdate(expression1);    // run the query
          st.executeUpdate(expression2);    // run the query
          st.executeUpdate(expression3);    // run the query
          st.close();
          conn.close();
      }
  </code></pre>
  
  <p>That's why I didn't find my dirtyhack1 table, and that's why I got error. Init-database just removed my simulators schema</p>
  
  <p>So there is a question. How to launch init script before Hibernate? Cause if I wouldn't write line (1) then I get error about wrong schema export</p>