Error in test suite

[angoca]

Hi, I am facing errors in the XML tests. I do not know the reason, and sometimes it works. It is something about the conditional compilation.

Exception(2): SQLCode -1354, SQLState 560CE-SQL1354N  An SQL variable in routine "TEST_XML_02_DIFF" is not available for referen

Probably, the set ccflags should be defined in the ONE_TIME_SETUP, but I did it, and the problem still appears.

I put a couple of TODOs where the error appears.

[angoca]

@dibdotdob Can you help me with this issue.

[dibdotdob]

I've taken a look at the xml_to_char function and see an issue, though not sure if it is related to the error you are getting. This statement will fail is the XML is greater than 500 bytes.

SET RET = XMLCAST(XMLSERIALIZE(VALUE) AS VARCHAR(500));

The following is better for returning the first 500 bytes of a serialised xml document.

SET RET = SUBSTR(XMLSERIALIZE(VALUE AS CLOB), 1, 500); 

The error you are getting is interesting and I'll see if I can replicate in my environment and then come up with a fix.

[dibdotdob]

This error isn't associated to conditional compilation. The following reference describes the circumstances for the SQL1354N error.

http://www-01.ibm.com/support/knowledgecenter/?lang=en#!/SSEPGG_10.1.0/com.ibm.db2.luw.apdv.routines.doc/doc/c0023913.html

The following procedure demonstrates this.

create or replace procedure dbautil.test_xml_var()
language sql
specific test_xml_var
begin
  declare document xml ;--
  set document = xmlparse( document '<a/>' ) ;--
  commit ;-- causes SQL1354N to be raised
  call dbms_output.put_line( xmlserialize( document as clob ) ) ;--
end ;

Calling this procedure you get.

call dbautil.test_xml_var 
SQL1354N  An SQL variable in routine "TEST_XML_VAR" is not available for 
reference due to a recent commit or rollback operation.  SQLSTATE=560CE

Remove the commit and the procedure works. You said that the test case works and sometimes not. Are you able to determine where a COMMIT or ROLLBACK is happening to causing this situation?

[angoca]

It seems like XML are not completely supported in DB2 :( , there are still some workarounds as the one you found.

Removing commits in the db2unit is not an option, because there are many messages and steps during the execution of a test suite that requires registering the status of the execution. If they are removed, and one of the tests perform a rollback, the messages will be lost. I do not use savepoints because a test could perform a commit, and that changes the logic of db2unit. I prefer to use commits each time a test is executed in order to have a clean environment without open transaction.

In this case, I prefer to modify the tesst in order to put the same the information in a VARCHAR, and assign it to XML each time I use them.

[dibdotdob]

Yes, there are some quirks with XML. The work around you propose would work ( though as you've seen I prefer CLOBs as XML documents can get very big ). I've also been working on test cases for XML. I've opted to create a table and populate it with all XML documents as part of the one time setup. Each test case then retrieves the XML documents required for the test. I've yet to experience the problem you've encountered.

[angoca]

Yes, it is weird, and depends on the order of the tests. As you can see in the TravisCI execution, the execution that is not random, the problem does not appear. However, when the other one is executed, the error appears. That's why I couldn't propose a recette to reproduce the error.

What I am going to do is to create the string in a varchar, and then assign it to a CLOB or an XML according. I am not going to modify the assertions, just the tests.

---

I have created another ticket for what you are proposing, the values in a table for the test suite: #70
This feature will change a lot the execution of db2unit (the randomness of the tests, the way the values are retrieved).