12 Replies Latest reply on Jul 15, 2014 2:16 PM by Michel Graciano

    Problem with new sqlStatementDelimiter of persistence extension version 1.0.0.Alpha6

    Willem van Es Newbie

      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
      <extension qualifier="persistence-script">
      <property name="sqlStatementDelimiter">;</property>
      </extension>
      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

      org.jboss.arquillian.persistence.script.ScriptExecutor#splitInlineStatements

      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"