0

For some reason, this keeps popping out when I am trying to execute a test that would initialize the database and tables. I am using Spring 4.1.2.RELEASE

When trying to create a trigger:

Caused by: java.sql.SQLException: Unexpected end of command: REFERENCING

The code for the trigger is:

CREATE TRIGGER subscriber_created_at BEFORE INSERT ON subscriber
REFERENCING new ROW AS newrow
FOR EACH ROW BEGIN ATOMIC
    IF newrow.dateCreated IS NULL
    THEN SET newrow.dateCreated = NOW();
    END IF;
END;/;

Then after searching SO, if found this link that tells how the OP fixed it and just for testing, to see if I could get a different error, it tried the OP's code and got the same error.

The code for the procedure is:

 CREATE PROCEDURE p_recordTaskExecution(IN userTaskId BIGINT, 
 IN isSuccess BOOLEAN, 
 IN statusMessage VARCHAR(2000), 
 IN operationsPerformed BIGINT, 
 INOUT procedureStatus BOOLEAN) BEGIN ATOMIC
 IF userTaskId = 1 Then
      SET procedureStatus = true; 
 ELSE
      SET procedureStatus = false;
 END IF;
 END; /;

When trying to create a procedure:

Caused by: java.sql.SQLException: Unexpected token: PROCEDURE in statement [CREATE PROCEDURE]

And for testing, I edited my code in the xml to cut off the commnad when it reaches the word subscriber:

<jdbc:script location="classpath:/input_data/createTriggers.sql" separator="subscriber" />

And it did cut off! The error I got was:

 Unexpected token:  in statement [CREATE TRIGGER ]

I really stumped. Has anyone else encountered this?

fredt
  • 24,044
  • 3
  • 40
  • 61
Francis Zabala
  • 1,037
  • 2
  • 11
  • 30

1 Answers1

1

Finally got the answer this SO question

HSQLDB Trigger Statement ERROR when using SimpleJdbcTestUtils.executeSqlScript()

Turns out that I had to update hsqldb to the 2.3.2.

For some reason, when I google hsqldb maven, the first item that pops out is the 1.8 version. I assumed that that was the latest. My bad.

Community
  • 1
  • 1
Francis Zabala
  • 1,037
  • 2
  • 11
  • 30