Changing 'commit' option in XML SQL statement does NOT work

[ngson2000]

Hi All,

To set the transaction-isolation level to NO COMMIT in a XML SQL document, I created the following XML:

<?xml version='1.0'?>
<myscript>
<sql>
<connect options='opt1' />
<options options='opt1' autocommit='off' commit='none' />
<query>
<![CDATA[
... Do a UPDATE/DELETE query an unjournaled object/member
]]>
</query>
<rowcount stmt='stmt1' />
</sql>
</myscript>

I got the error: Member <...> not journaled to journal *N. It looked like the option commit='none' did not work.

After doing lots of trying, the following XML worked for me:

<?xml version='1.0'?>
<myscript>
<sql>
<options options='opt1' commit='none' />
<connect options='opt1' />
<query>
<![CDATA[
... Do a UPDATE/DELETE query an unjournaled object/member
]]>
</query>
<rowcount stmt='stmt1' />
</sql>
</myscript>

I'd say there is a bug in parsing options in XML document and the developer guide http://yips.idevcloud.com/wiki/index.php/XMLService/XMLSERVICEQuick is not right.

1. The must be declare before
2. Does the order of attributes in <options ...> take into account?

[jimoibm]

@ngson2000
The sequence is mattered. That is just a flow like calling DB SQL CLI for you. Xmlservice won't do a full parse first and then re-order to call system interfaces. Users need to be aware of that.

My network is blocked to the link you pointed out. But I searched '<options ' in the document source. I noticed all examples having ahead of . That is correct. Can you reference a piece of text from that webpage where it tells a wrong usage. BTW, I don't think that webpages will be maintained even if things are corrected in here. All will go Github.

[kadler]

@jimobit The link points to the wiki I dumped in to the docs directory. The local text is https://github.com/IBM/xmlservice/blob/master/docs/wiki-dump/functions.txt#L1270-L1293

[kadler]

Reminds me I should work to get #40 merged

[jimoibm]

Thanks @kadler
What @ngson2000 referenced was not an example :-) It explains the definitions of and . It doesn't indicate the declaration orders... Please refer to other pages with real example snippet...

[kadler]

So from what I gather, an options template is basically an object which gets instantiated. In order to use it, you must have already declared and instantiated it, so the order of the tags matters. From what I can tell, it doesn't matter the order of the attributes within the options tag, however.

It seems like options objects stick around between calls in stateful XMLSERVICE, so you can instantiate one once and re-use it on subsequent calls. For normal stateless XMLSERVICE, you wouldn't be able to do this though. This means you must also free the options object after you are done with it, just like other SQL objects.

[kadler]

We should definitely work on documenting these restrictions and obligations better.

[jimoibm]

Yes you are correct. Looking at the cacScanOpt(), 'options' cache is designed to be common to all other tags with 'options' elements. For stateless, the cache should also work before cache is cleared out when the call is ended.