1 Reply Latest reply on Feb 14, 2013 8:20 AM by Wolf-Dieter Fink

    Custom stored procedures in embedded H2

    Daniel Susín Newbie

      Hello,

       

      I have programmed some stored procedures in java for H2. While I am able to use them with no problem in a standalone instance of H2 (I simply add the jar with the SPs to the H2 classpath at launch time), I have not been able to use my library with the embedded H2 instance of JBOSS. The stored procedures simply perform some conversions from "weird data sources" to be accessed later by TEIID. Persistence is not a problem, in fact, a temporary memory only database is the desired behaviour because, ideally, changes on the original sources should carry on in "real time" to the final frontend.

       

      I have tried to define a global module in standalone.xml:

       

      <subsystem xmlns="urn:jboss:domain:ee:1.0">
          <global-modules>
               <module name="org.mylibs.h2conversor" slot="main"/>
          </global-modules>
      </subsystem>
      

       

      and I have added the module to the folder modules/org/mylibs/h2conversor with the next module.xml content:

       

      <?xml version="1.0" encoding="UTF-8"?>
      <module xmlns="urn:jboss:module:1.1" name="org.mylibs.h2conversor">
          <resources>
              <resource-root path="h2conversorsps.jar"/>
          </resources>
      </module> 
      

       

      the datasource's connection url is as follows:

      jdbc:h2:mem:conversion_db;INIT=drop schema if exists conversion_db\;create schema if not exists conversion_db\;set password 'sa'\;runscript from '/home/user/h2sql/populate.sql'

       

      and the populate.sql file contains commands to fill the tables using the custom SPs.

       

      It works like a charm on a standalone H2 instance, but I'd like to use JBOSS' embedded one.

       

      Am I doing it right? Is it possible to achieve what I am trying to do?

       

      Thanks!