我使用HSQL作为内存测试数据库来运行集成测试。
在生产中,我有一个oracle 10g数据库。想法是运行db create脚本,该脚本用于在oracle中创建db,以便在hsql中为集成测试创建相同的db。
我遇到了关于语法差异的障碍,尽管尽管向HSQL指出语法是oracle,但我仍然坚持创建存储过程。
我的测试db bean的定义如下:
<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脚本具有以下创建过程语句:
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
并且当我运行测试时,HSQL由于以下原因而失败:
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网站上的文档:http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html
表示我的语法正确,所以我对实际问题感到困惑。
有任何想法吗?
最佳答案
我解决此问题的方法是使用以下方法:
<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>
我可以在脚本文件中指定分隔符。所以我使用了不同的定界符/;在具有创建过程语句的脚本文件中,该过程语句具有;分开的命令。