#1477 SQL batch issue in concurrent memory DB

[Mingxin LI]

I run the following code in servlet

when I do the performance test, there may be a batch error occur, the batch fail and the table will not be created. I wont get the exception if I remove the "mem" for connection.

I also update my code, if I catch batch error, I will execute the SQL one by one, then the tables are created successfully.

I think there may be some concurrent issue in HSQL, can you help fixing the issue?

thank you!

**code begin
Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:" + Thread.currentThread().getId() + ";shutdown=true", "sa", "")
Statement stmt = conn.createStatement();
for (String sql : sqls) {
stmt.addBatch(sql);
LogManager.logDebug("Define table : " + sql);
}
stmt.executeBatch();
stmt.close();code end***

the following are the list of SQL in the batch
***SQL begin
CREATE TABLE "COMPANY_DBA_1489727621354" ( COMPANY_DBA_ID_PRIMARY_KEY BIGINT,NAME VARCHAR(255),ADDR1 VARCHAR(255),ADDR2 VARCHAR(255),CITY VARCHAR(255),STATE VARCHAR(255),ZIP BIGINT,PHONE BIGINT,FAX BIGINT,EMAIL VARCHAR(255),COMPANY_LOGO VARCHAR(255),SIGNATURE_FILE VARCHAR(255),EXECUTIVE_NAME VARCHAR(255),TITLE VARCHAR(255),WEB_ADDRESS VARCHAR(255),FREE_TEXT_FOOTER VARCHAR(255),FREE_TEXT_COVER_PAGE_HEADING VARCHAR(255),FREE_TEXT_MKTG_MESSAGE VARCHAR(255),FREE_TEXT_MKTG_MESSAGE_HEADING VARCHAR(255),INCLUDE_COVER_PAGE VARCHAR(255),INCLUDE_SUMMARY VARCHAR(255),INCLUDE_VEHICLE_COVERAGE VARCHAR(255),INCLUDE_NOTICES VARCHAR(255),INCLUDE_INSURANCE_CARDS VARCHAR(255),INCLUDE_CHART VARCHAR(255), PRIMARY KEY ( COMPANY_DBA_ID_PRIMARY_KEY ) )
CREATE TABLE "PRIMARY_ACCOUNT_1489727621354" ( ACCOUNT_ID_PRIMARY_KEY BIGINT,COMPANY_DBA_ID BIGINT,POLICY_NUMBER VARCHAR(255),JURISDICTION VARCHAR(255),EFFECTIVE_DATE VARCHAR(255),TERMINATION_DATE VARCHAR(255),CUSTOMER_SEQUENCE_NUM BIGINT,TITLE VARCHAR(255),FNAME VARCHAR(255),MIDDLE_INITIAL VARCHAR(255),LNAME VARCHAR(255),MAILING_ADDRESS1 VARCHAR(255),MAILING_ADDRESS2 VARCHAR(255),MAILING_ADDRESS_CITY VARCHAR(255),MAILING_ADDRESS_STATE VARCHAR(255),MAILING_ADDRESS_ZIP BIGINT,RESIDENCE_ADDRESS1 VARCHAR(255),RESIDENCE_ADDRESS2 VARCHAR(255),RESIDENCE_ADDRESS_CITY VARCHAR(255),RESIDENCE_ADDRESS_STATE VARCHAR(255),RESIDENCE_ADDRESS_ZIP BIGINT,PHONE BIGINT,FAX BIGINT,EMAIL VARCHAR(255),DOB VARCHAR(255),GENDER VARCHAR(255),MARITAL_STATUS VARCHAR(255),LANGUAGE VARCHAR(255), PRIMARY KEY ( ACCOUNT_ID_PRIMARY_KEY ) )
CREATE TABLE "PRODUCT_1489727621354" ( PK_PRODUCT_ID BIGINT,ACCOUNT_ID_FOREIGN_KEY BIGINT,TYPE VARCHAR(255),NAME VARCHAR(255),DESCRIPTION VARCHAR(255), PRIMARY KEY ( PK_PRODUCT_ID ) )
CREATE TABLE "AUTO_1489727621354" ( PK_AUTO_INSURANCE BIGINT,FK_PRODUCT_ID BIGINT,ACCOUNT_ID_FOREIGN_KEY BIGINT,VIN_NUMBER VARCHAR(255),LIENHOLDER VARCHAR(255),DRIVERS_EXCLUDED VARCHAR(255),TOTAL_PREMIUM FLOAT ,COMBINED_DISCOUNT FLOAT ,YEAR BIGINT,MAKE VARCHAR(255),MODEL VARCHAR(255),ANNUAL_MILES VARCHAR(255),AVERAGE_DAILY_TRIP_MILES VARCHAR(255),DISCOUNT1 VARCHAR(255),DISCOUNT2 VARCHAR(255),DISCOUNT3 VARCHAR(255),DISCOUNT4 VARCHAR(255), PRIMARY KEY ( PK_AUTO_INSURANCE ) )
CREATE TABLE "AUTO_COVERAGE_1489727621354" ( PK_AUTO_INSURANCE_COVERAGE_ID BIGINT,FK_AUTO_INSURANCE BIGINT,DESCRIPTION VARCHAR(255),DEDUCTIBLE VARCHAR(255),PREMIUM FLOAT ,COVERAGE_LIMIT_TEXT VARCHAR(255),COVERAGE_LIMIT_AMOUNT1 VARCHAR(255),COVERAGE_LIMIT_AMOUNT1_DESC VARCHAR(255),COVERAGE_LIMIT_AMOUNT2 VARCHAR(255),COVERAGE_LIMIT_AMOUNT2_DESC VARCHAR(255), PRIMARY KEY ( PK_AUTO_INSURANCE_COVERAGE_ID ) )
CREATE TABLE "AGENT_1489727621354" ( PK_AGENT_ID BIGINT,FK_ACCOUNT_ID BIGINT,TITLE VARCHAR(255),FNAME VARCHAR(255),MIDDLE_INITIAL VARCHAR(255),LNAME VARCHAR(255),ADDRESS1 VARCHAR(255),ADDRESS2 VARCHAR(255),MAILING_ADDRESS_CITY VARCHAR(255),MAILING_ADDRESS_STATE VARCHAR(255),MAILING_ADDRESS_ZIP BIGINT,PHONE BIGINT,FAX BIGINT,EMAIL VARCHAR(255))**SQL end***