HSQL Create Procedure Syntax does not seem to match documentation

I use HSQL as my db memory test to run integration tests. In production, I have an oracle 10g database. The idea is to run the db creation scripts that I used to create db in oracle to create the same db in hsql for integration tests.

I ran into hurdles related to differences in syntax, despite the fact that although HSQL indicates that the syntax is an oracle, I continue to get stuck when creating stored procedures.

My db bean test is defined as such:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="org.hsqldb.jdbcDriver"/> <property name="url" value="jdbc:hsqldb:mem:test;sql.syntax_ora=true"/> <property name="username" value="sa"/> <property name="password" value=""/> </bean> <jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS"> <jdbc:script location="file:Artifacts/sql/install.sql"/> <jdbc:script location="file:Artifacts/sql/patchset/1.0.0.02.create_survey_tables.sql"/> <jdbc:script location="file:Artifacts/sql/patchset/1.00.01.update.sql"/> <jdbc:script location="file:Artifacts/sql/patchset/1.00.03.insert_surveyQA2.sql"/> <jdbc:script location="file:Artifacts/sql/patchset/1.00.05.insert_surveyQA4.sql"/> </jdbc:initialize-database> 

sql script I have the following create procedure statement:

 CREATE PROCEDURE reg_create( OUT vregID NUMBER, vsessionID VARCHAR2(254), vextrnID VARCHAR2(100), vindivID VARCHAR2(100), vfirstName VARCHAR2(100), vlastName VARCHAR2(100), vemail VARCHAR2(320), vaddrLine1 VARCHAR2(254), vaddrLine2 VARCHAR2(254), vcity VARCHAR2(100), vstate VARCHAR2(2), vzipCode VARCHAR2(5), vdiagnosed NUMBER, vrelationship NUMBER, vpatientFirstName VARCHAR2(100), vpatientLastName VARCHAR2(100), vbirthdate DATE, vtreatment NUMBER, vcfCenter NUMBER, vofflineMaterial NUMBER, vmoc VARCHAR2(100), voptIn NUMBER, vcreateDate DATE, vpendingSend NUMBER) MODIFIES SQL DATA BEGIN ATOMIC DECLARE vregID NUMBER; SET vregID = (SELECT NVL(MAX(REG_ID), 0)+1 FROM CFLIVING_REG); INSERT INTO REG( REG_ID, SESSION_ID, EXTRN_ID, INDIV_ID, FIRST_NAME, LAST_NAME, EMAIL, ADDR_LINE1, ADDR_LINE2, CITY, STATE, ZIP_CODE, DIAGNOSED, RELATIONSHIP, PATIENT_FIRST_NAME, PATIENT_LAST_NAME, BIRTHDATE, TREATMENT, CF_CENTER, OFFLINE_MATERIAL, MOC, OPT_IN, CREATE_DATE, PENDING_SEND ) VALUES (vregID, vsessionID, vextrnID, vindivID, vfirstName, vlastName, vemail, vaddrLine1, vaddrLine2, vcity, vstate, vzipCode, vdiagnosed, vrelationship, vpatientFirstName, vpatientLastName, vbirthdate, vtreatment, vcfCenter, vofflineMaterial, vmoc, voptIn, vcreateDate, vpendingSend); END 

And when I run the test, HSQL fails due to the following:

 Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 18 of resource URL [file:Artifacts/sql/install.sql]: CREATE PROCEDURE cfliving_reg_create( OUT vregID NUMBER, vsessionID VARCHAR2(254), vextrnID VARCHAR2(100), vindivID VARCHAR2(100), vfirstName VARCHAR2(100), vlastName VARCHAR2(100), vemail VARCHAR2(320), vaddrLine1 VARCHAR2(254), vaddrLine2 VARCHAR2(254), vcity VARCHAR2(100), vstate VARCHAR2(2), vzipCode VARCHAR2(5), vdiagnosed NUMBER, vrelationship NUMBER, vpatientFirstName VARCHAR2(100), vpatientLastName VARCHAR2(100), vbirthdate DATE, vtreatment NUMBER, vcfCenter NUMBER, vofflineMaterial NUMBER, vmoc VARCHAR2(100), voptIn NUMBER, vcreateDate DATE, vpendingSend NUMBER) MODIFIES SQL DATA BEGIN ATOMIC DECLARE vregID NUMBER Caused by: java.sql.SQLSyntaxErrorException: unexpected end of statement: required: ; 

HSQL website documentation: http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html indicates that my syntax is correct, so I am confused as to what the real problem is.

any ideas?

+4
source share
1 answer

The way I solved this was as follows:

  <jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS"> <jdbc:script location="file:Artifacts/Hsql Version Scripts/install/droptables.sql" separator=";"/> <jdbc:script location="file:Artifacts/Hsql Version Scripts/install/install.sql" separator="/;"/> <jdbc:script location="file:Artifacts/Hsql Version Scripts/patchset/1.0.0.02.create_survey_tables.sql" separator="/;"/> <jdbc:script location="file:Artifacts/Hsql Version Scripts/patchset/1.00.01.update.sql" separator=";"/> <jdbc:script location="file:Artifacts/Hsql Version Scripts/patchset/1.00.03.insert_surveyQA2.sql" separator=";"/> <jdbc:script location="file:Artifacts/Hsql Version Scripts/patchset/1.00.05.insert_surveyQA4.sql" separator=";"/> </jdbc:initialize-database> 

I can specify the delimiter in the script files. So I used a different delimiter /; in script files in which procedure statements were created; divided teams.

+2
source

Source: https://habr.com/ru/post/920571/


All Articles