title: HSQLDB function creating Caused by: org.hsqldb.HsqlException:
body: <p>ISSUE: </p>
  
  <p>I am using <code>HSQLDB</code> as inmemory database for my testing, I am able to execute tables  fine, I am getting below exception when I tried to create the function and run.</p>
  
  <p>Exception:</p>
  
  <pre><code>Caused by: org.hsqldb.HsqlException: error in script file line: 54   org.hsqldb.HsqlException: error in script file: CREATE FUNCTION get_mask() 
                      at org.hsqldb.error.Error.error(Unknown Source)
  </code></pre>
  
  <p>Test script for hypersonic:</p>
  
  <pre><code>            SET DATABASE UNIQUE NAME HSQLDB2467A82B33
              SET DATABASE GC 0
              SET DATABASE DEFAULT RESULT MEMORY ROWS 0
              SET DATABASE EVENT LOG LEVEL 0
              SET DATABASE SQL NAMES FALSE
              SET DATABASE SQL REGULAR NAMES TRUE
              SET DATABASE SQL REFERENCES FALSE
              SET DATABASE SQL SIZE TRUE
              SET DATABASE SQL TYPES FALSE
              SET DATABASE SQL TDC DELETE TRUE
              SET DATABASE SQL TDC UPDATE TRUE
              SET DATABASE SQL TRANSLATE TTI TYPES TRUE
              SET DATABASE SQL CONCAT NULLS TRUE
              SET DATABASE SQL NULLS FIRST TRUE
              SET DATABASE SQL UNIQUE NULLS TRUE
              SET DATABASE SQL CONVERT TRUNCATE TRUE
              SET DATABASE SQL AVG SCALE 0
              SET DATABASE SQL DOUBLE NAN TRUE
              SET DATABASE SQL LONGVAR IS LOB FALSE
              SET DATABASE TRANSACTION CONTROL LOCKS
              SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED
              SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE
              SET DATABASE TEXT TABLE DEFAULTS ''
              SET FILES WRITE DELAY 0
              SET FILES BACKUP INCREMENT FALSE
              SET FILES CACHE SIZE 10000
              SET FILES CACHE ROWS 2000
              SET FILES SCALE 64
              SET FILES LOB SCALE 32
              SET FILES DEFRAG 0
              SET FILES NIO FALSE
              SET FILES NIO SIZE 256
              SET FILES LOG TRUE
              SET FILES LOG SIZE 0
              SET DATABASE SQL SYNTAX ORA TRUE
              CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'
              CREATE SCHEMA PUBLIC AUTHORIZATION DBA
              SET SCHEMA PUBLIC
              CREATE MEMORY TABLE PUBLIC.EMPLOYEEDETAILS(EMPNAME VARCHAR(20))
              CREATE MEMORY TABLE PUBLIC.VW_OH_MKT_OFFERS(MKT_OFFER_UID VARCHAR(12) NOT NULL PRIMARY KEY,INTENT VARCHAR(3),LEGACY_OFFER_CODE VARCHAR(12),PRODUCT_LINE_CODE VARCHAR(10),STATUS VARCHAR(1),BEGIN_DATE DATE,END_DATE DATE,SOURCE_KEY_UID VARCHAR(12),SOURCE_KEY_CODE VARCHAR(20),PT_CODE VARCHAR(12),OFFER_GROUP_CODE VARCHAR(12),OFFER_CODE VARCHAR(12),ITEM_CODE VARCHAR(30),VENUE_CODE VARCHAR(20),SUPERVENUE_CODE VARCHAR(12),CURRENCY_CODE VARCHAR(3),INITIAL_UNIT_PRICE DECIMAL(10,2),INITIAL_NUMBER_OF_PAYMENTS NUMERIC(10),INITIAL_FIRST_PAYMENT_AMOUNT DECIMAL(10,2),INITIAL_FIRST_SHIPPING_AMOUNT DECIMAL(10,2),INITIAL_STD_SHIPPING NUMERIC(128),INITIAL_STD_SHIP_AVAILABLE VARCHAR(1),INITIAL_RUSH_SHIPPING DECIMAL(10,2),INITIAL_RUSH_SHIP_AVAILABLE VARCHAR(1),INITIAL_2DAY_SHIPPING NUMERIC(128),INITIAL_2DAY_SHIP_AVAILABLE VARCHAR(1),INITIAL_SDAY_SHIPPING NUMERIC(128),INITIAL_SDAY_SHIP_AVAILABLE VARCHAR(1),INITIAL_REG_NAME VARCHAR(2),INITIAL_REG_STD_SHIPPING NUMERIC(128),INITIAL_REG_STD_SHIP_AVAILABLE VARCHAR(1),WEB_CART_TITLE VARCHAR(30),WEB_CART_TEXT VARCHAR(30),WEB_CART_IMAGE VARCHAR(30),WEB_CART_MAX_QUANTITY VARCHAR(30),FFC_CODE VARCHAR(100),MULTI_NODE_SHIPPING_LOCATION VARCHAR(60),ITEM_CONTINUITY_CODE VARCHAR(30),OMX_CID VARCHAR(30),OMX_KEYCODE VARCHAR(30),OMX_PAYMENT_PLAN_ID VARCHAR(30),OMX_SOC_CONFIGURATION_ID VARCHAR(30),HP_KEY_CODE VARCHAR(20),HP_PRODUCT_NUMBER VARCHAR(20),HP_MPG_CODE VARCHAR(10),LEGACY_SCRIPT_CODE VARCHAR(12),TENANT_ID BIGINT)
              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 PUBLIC
              INSERT INTO EMPLOYEEDETAILS VALUES('Test1')
              INSERT INTO EMPLOYEEDETAILS VALUES('Test2')
              INSERT INTO EMPLOYEEDETAILS VALUES('Test3')
              INSERT INTO VW_OH_MKT_OFFERS VALUES('1142675','TLM','DR1138','PA','C','2008-08-22','2008-08-22','YYYY-MM-DD','34DUN','KPA510C34DUN','PPAJ9R','HPADJPT','OPA18RAG','PA1467','28','5',29.22,1,29.95,5.99,6,'1',5.99,'1',0,'0',0,'0','CA',0,'1',NULL,NULL,NULL,NULL,'EDS',NULL,NULL,NULL,'34DUN',NULL,NULL,'558358','014399','300','DR11',1);
              CREATE FUNCTION get_mask() 
              RETURNS VARCHAR(3)
              BEGIN ATOMIC
              DECLARE max_event VARCHAR(3); 
              SET intent = SELECT INTENT FROM VW_OH_MKT_OFFERS WHERE MKT_OFFER_UID='1142675'; 
              RETURN intent;
              END
  </code></pre>
  
  <p>Java Code:</p>
  
  <pre><code>                           //Java code to execute stored proc
                          Connection connection = null;
                               // resultset
                          ResultSet resultSet = null;
                          Statement statement = null;
                        // driver creation
                          Class.forName("org.hsqldb.jdbcDriver");
  
                                                      // connect to the db                                           
  
  connection =                                    DriverManager.getConnection("jdbc:hsqldb:file:D:\\HyperSQLDB\\EMPLOYEEDB;shutdown=true", "SA", "");
  
  
              // getting statement    
  
  statement = connection.createStatement();
  
              // create callable statement for calling function
                          CallableStatement stat = connection.prepareCall("{call get_mask()}");
                  // Check if the function is executed
                          System.out.println(stat.execute()); // =&gt; true
              // get the result
                          ResultSet rs = stat.getResultSet();
              // get the next result
                          rs.next();
  </code></pre>