Cron + HSQLDB: Unexpected token: in statement [SELECT 1]

[Andreas Hartmann <an...@apache.org>]

Hi all,

I'm trying to run the cron block with the TX job store and the
built-in HSQL DB. My configurations:

<store delegate="org.quartz.impl.jdbcjobstore.HSQLDBDelegate" type="tx">
   <datasource provider="excalibur">LenyaScheduler</datasource>
</store>

<datasources>
   <jdbc logger="core.datasources.lenya.scheduler" name="LenyaScheduler">
     <pool-controller max="10" min="5"/>
     <dburl>jdbc:hsqldb:hsql://localhost:9002</dburl>
     <user>sa</user>
     <password/>
   </jdbc>
</datasources>


I added the Quartz tables to cocoondb.script. When I start Jetty, I can
connect to the DB with the org.hsqldb.util.DatabaseManager browse the tables.

But during the cron block startup, I get the following exception:


main WARN  lenya.scheduler - Could not prepare test statement, connection 
recycled on basis of time.
java.sql.SQLException: Unexpected token:  in statement [SELECT 1]
         at org.hsqldb.jdbc.Util.throwError(Unknown Source)
         at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
         ...


It looks like a test statement is created, but HSQL couldn't parse it and
threw an exception. I found the following message in a forum:


 >>DriverManager.getConnection("jdbc:hsqldb:hsql://localhost")
 >>  trying driver[className=org.hsqldb.jdbcDriver,org.hsqldb.jdbcDriver@93385d]
 >>getConnection returning
 >>driver[className=org.hsqldb.jdbcDriver,org.hsqldb.jdbcDriver@93385d]
 >>SQLException: SQLState(37000) vendor code(-11)
 >>java.sql.SQLException: Unexpected token: 37000 Unexpected token:  in statement
 >>[SELECT 1]
 >>
 >>
 >
 >The SQL is correct. Yet another SQL incompatibility in HSQL.


This seems to be a known issue with HSQL. Updating to the newest
version (1.8.0-RC9) didn't solve the problem.

Is there anything I can do about it?

Thanks a lot!

-- Andreas


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org

[Solved] Re: Cron + HSQLDB: Unexpected token: in statement [SELECT 1]

[Andreas Hartmann <an...@apache.org>]

Andreas Hartmann wrote:
> Hi all,
> 
> I'm trying to run the cron block with the TX job store and the
> built-in HSQL DB. My configurations:
> 
> <store delegate="org.quartz.impl.jdbcjobstore.HSQLDBDelegate" type="tx">
>   <datasource provider="excalibur">LenyaScheduler</datasource>
> </store>
> 
> <datasources>
>   <jdbc logger="core.datasources.lenya.scheduler" name="LenyaScheduler">
>     <pool-controller max="10" min="5"/>
>     <dburl>jdbc:hsqldb:hsql://localhost:9002</dburl>
>     <user>sa</user>
>     <password/>
>   </jdbc>
> </datasources>

The problem is indeed that HSQL cannot parse the default keep-alive statement
"SELECT 1". But you can provide your own keep-alive statement:

     <jdbc logger="core.datasources.lenya.scheduler" name="LenyaScheduler">
       <pool-controller max="10" min="5">
         <!-- use custom keep-alive query because HSQL does not accept 'SELECT 
1' -->
         <keep-alive>SELECT 1 FROM QRTZ_LOCKS</keep-alive>
       </pool-controller>
       <dburl>jdbc:hsqldb:hsql://localhost:9002</dburl>
       <user>sa</user>
       <password/>
     </jdbc>


-- Andreas


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org