I am using Arquillian with its persistence extension with an Oracle 11g XE edition with a copy of their HR example schema to try out my skills with the JPQL and criteria queries of JPA 2.
In one test method I was evaluating a service method that would update 5 rows in the employees table. I tried to compare the result with an excel spreadsheet by using @ShouldMatchDataSet and then restore the 5 rows to their previous states with multiple update statements in a sql script file configured with @CleanupUsingScript(phase = TestExecutionPhase.AFTER, value="scripts/restore_affected_employees.sql") with the following content:
update employees es set es.salary = 9000 where es.employee_id = 103;
update employees es set es.salary = 6000 where es.employee_id = 104;
update employees es set es.salary = 4800 where es.employee_id = 105;
update employees es set es.salary = 4800 where es.employee_id = 106;
update employees es set es.salary = 4200 where es.employee_id = 107;
In persistence extension version 1.0.0.Alpha5 the @ShouldMatchDataSet annotation was not working due to unpredictable row order. When I was able to switch to version 1.0.0.Alpha6, which now supports the orderBy attribute It does work, thank you for that.
The @CleanupUsingScript functionality, however, becomes problematic in version 1.0.0.Alpha6.
As I understand it 1.0.0.Alpha5 used a line break as statement separator, so as long as you kept each statement on a single line, you were fine.
In version 1.0.0.Alpha6 you can specify a sqlStatementDelimiter in arquillian.xml, which defaults to a semicolon
I put the following in the arquillian.xml file
As was pointed out in https://docs.jboss.org/author/display/ARQ/Persistence#Persistence-Additionalconfiguration
When I ran the test, however, I got a stacktrace starting with:
org.jboss.arquillian.persistence.dbunit.exception.DBUnitDataSetHandlingException: Unable to execute statement: update employees es set es.salary = 9000 where es.employee_id = 103;
With further on in the stacktrace:
Caused by: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
I suspect that the problem is caused by the sqlStatementDelimiter being included to the JDBC statement, which isn't accepted by Oracle.
When I substituted 'GO' for ';' as sqlStatementDelimiter in both the cleanup script file and the arquillian.xml file I got a similar exception message:
org.jboss.arquillian.persistence.dbunit.exception.DBUnitDataSetHandlingException: Unable to execute statement: UPDATE employees es SET es.salary = 9000 WHERE es.employee_id = 103 GO
This implies that the sqlStatementDelimiter is included to the statement.
Maybe the problem lies in the use of the StringTokenizer in
As I understand the API doc the delimiter is not included by default, but maybe it helps to explicitly instantiate it with the constructor with the boolean returnDelims parameter set to false.
I haven't tried to reproduce it with another database yet, so this problem could be limited to use with Oracle. Furthermore, I used Java SE 1.7.0_13 with JBoss AS 7.1.1.Final "Brontes"