Problem with new sqlStatementDelimiter of persistence extension version 1.0.0.Alpha6

[willem.vanes]

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"

[bmajsak]

Hi Willem,

that's in fact the difference between Alpha5 and 6. Many thanks for spending time analyzing this problem and apologies for the problems. I don't have Oracle DB in the test suite and for all the others I don't have any problems with SQL scripts. However your case proves that delimiter statement might be expected from the db vendor. I think your idea with additional config parameter might work good. Do you think you could contribute such improvement to the project? You can count on my full support! Eternal fame in the open source universe is waiting

Cheers,

Bartosz

[willem.vanes]

Hi Bartosz,

I think I will have some time for that this weekend. Is there some instruction manual for making contributions? (like what needs to be configured in your IDE; How to check out a local copy from the repository and from what branch).

Regards,

Willem

[bmajsak]

That would be just great! Please have a look at Shrinkwrap contribution guide, we follow the same path.

Built is pretty much self contained for the default profile. I can take care of the rest (ms sql, mysql, postgres etc.)

In any case, I'm here to help.

[bmajsak]

Hi Willem,

did you have a chance to look at it? Can I help somehow?

[willem.vanes]

Hi Bartosz,

it took a while to find some time to pursue this problem a little further. Moreover, I am new to Git so I did a little reading before I took off. I will now report on some quick and rather dirty research I did on the problem.

I registered on GitHub and forked https://github.com/arquillian/arquillian-extension-persistence to https://github.com/willem-van-es/arquillian-extension-persistence.git, which I subsequently cloned as instructed in https://community.jboss.org/wiki/ShrinkWrapDevelopmentAndContribution.

Just to check whether my suspicions were correct I locally modified org.jboss.arquillian.persistence.script.ScriptExecutor.execute(String) to remove any statementDelimiter from the end of a statement just before execution:

        for (String statement : statements) {

            // TODO remove

            System.out

                .println("willem-van-es Inside org.jboss.arquillian.persistence.script.ScriptExecutor.execute(String)\n"

                    + " added SQL statement: " + statement);

            if (statement.endsWith(statementDelimiter)) {

                statement = statement.substring(0, statement.indexOf(statementDelimiter));

                System.out.println("willem-van-es Statement with statementDelimiter removed: " + statement);

            }

            executeStatement(statement);

        }

Then I built this from the commandline: mvn clean install -DskipTests to get the modification into my local maven repository.

Now I could rerun the test with the afore mentioned test annotated @CleanupUsingScript(phase = TestExecutionPhase.AFTER, value="scripts/restore_affected_employees.sql") in my existing Eclipse project after setting the version of the persistence extension in the pom file to 1.0.0.Final-SNAPSHOT instead of 1.0.0.Alpha6.

This did the trick, because my test ran smoothly: the five updated rows matched the spreadsheet with the predicted values and afterwards the scripts/restore_affected_employees.sql was executed to bring the five rows back to their original state. In the logging I could see each sql statement first ending with the statementDelimiter and then with that ending removed.

My next step would be to do things properly and add suitable integration tests to the persistence extention project.

First of all I think I would like to get it running with the jbossas-7.1-managed-postgresql profile. Maybe you have some pointers on how I get it running in Eclipse, so I can debug the code. Right now I get an error pointing to the pom.xml of each of the four child projects:

Description          Resource          Path          Location          Type

maven-dependency-plugin (goals "copy-dependencies", "unpack") is not supported by m2e.          pom.xml          /arquillian-persistence-api          line 6          Maven Project Build Lifecycle Mapping Problem

Glossing over some googled solutions it probably has something to do with declaring build/plugins/plugin/executions/execution/id/unpack within /arquillian-persistence-parent/pom.xml, but I haven't looked into it any further yet.

When I looked at existing tests relevant to this issue

/arquillian-persistence-integration-tests/src/test/java/org/jboss/arquillian/integration/persistence/test/cleanup/DataCleanupUsingScriptEventHandlingTest.java and probably also

/arquillian-persistence-integration-tests/src/test/java/org/jboss/arquillian/integration/persistence/test/customscripts/ApplyingCustomScriptsTest.java

I saw that the corresponding scripts had only a single line or two statements on two separate lines without any semicolon as statement delimitor

Are these still used and up to date?

Later I found /arquillian-persistence-impl/src/test/java/org/jboss/arquillian/persistence/script/ScriptExecutorTest.java, which tests sql scripts with multiple statements and a semicolon statement delimitor.

Of course it would be great to finally add an Oracle profile to the project, but I guess that would still take me quite some time as the opportunities for me to work on it are few and far between. But if you aren't in hurry to solve it I would gladly give it a try.

[mgraciano]

Any news on this one? I can help to fix it too. There is any patch in progress that I can fork and help?

Regards

[mgraciano]

I am working on a patch for this one and I have a question. Should by default the delimiter be removed or it should be a property?

I already have a patch working, as you can see https://github.com/mgraciano/arquillian-extension-persistence/commit/9c2d696590481f5356cf2798f5e2192e099494ec

It still need some work, mostly crating new tests. Any comments are really welcome.

[bmajsak]

Hi Michel,

sorry that I have missed that one. I will incorporate your pull request and add a property to with "keep" delimiter as a default. Most likely today

Cheers,

Bartosz

[saravanpa]

Hi Willem,

Just if you need a quick fix for this.

I faced the same problem in oracle . Its a bug in the implementation.

But i overcame this by setting sqlStatementDelimiter to nothing like below

   <extension qualifier="persistence-script">

        <property name="sqlStatementDelimiter"></property>

    </extension>

And you will have to put your scripts like below

update employees es set es.salary = 9000 where es.employee_id = 103

update employees es set es.salary = 6000 where es.employee_id = 104

[bmajsak]

I have introduced proper Oracle SQL handling with some PL/SQL bits, so that you can have 'drop table if exists' constructs from example.  Have a look at SQL scripts introduced in this commit to have a better pictury.

New version out soon. Thanks all for your help and patience.

[bmajsak]

I hope with the latest 1.0.0.Alpha7 version things have improved. Any feedback?

[mgraciano]

bmajsak just for the record, the changes worked for me. The only gotcha was I had to insert sqlDialect property in my arquillian.xml, as follow:

<extension qualifier="persistence-script">

        <property name="sqlDialect">oracle</property>

</extension>

Everything else worked as expected. Thanks.