foxmlj / for xml - issues

[Grigory Kagan]

Dear All,

I have noticed strange behavior generating xml result set, using

foxmlj () and for xml functionalities, when deriving

text data type, SOH character delimited FIX protocol message.

Database environment: ASE 15.0.2 GA (Solaris SPARC) + RTDS 4.5

Application activity steps:

a) Java client inserts message strings into a database table

insert messages (beginstring, sendercompid, targetcompid, session_qualifier,
msgseqnum, message)

values ('test', 'FIX-eSpeed', 'FIX-InfoReach', newid(), 1,
'8=FIX.4.29=16735=849=Bigbroker56=Fats Fast
Market52=20071006-21:51:24.46234=68540=254=155=CREAF11=038=10044=33.514=032=031=0.059=06=0.060=20071006-21:51:24.46220=010=084')

b) table trigger for insert -> generating XML string from newly
arrived data

c) the same table trigger for insert -> propagates formed XML to JMS bus
(RTDS)

Issues Description

1. Option value "entitize=no" has no affect on foxmlj (), but works in for
xml

(Note: compare original message string before and after the transformation)

a) with foxmlj ()

select forxmlj("select top 1 beginstring, sendercompid, targetcompid,
session_qualifier, msgseqnum, message from messages",

"entitize=no")

<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row>
<beginstring>test </beginstring>
<sendercompid>FIX-eSpeed</sendercompid>
<targetcompid>FIX-InfoReach</targetcompid>
<session_qualifier>834d09e20bdd49a88af8e9ccabee34ed</session_qualifier>
<msgseqnum>1</msgseqnum>
<message>8=FIX.4.2?9=169?35=8?49=Bigbroker?56=Fats Fast
Market?52=20071010-19:10:49.509?34=11927?40=2?54=1?55=CREAF?11=0?38=100?44=33.5?14=0?32=0?31=0.0?59=0?6=0.0?60=20071010-19:10:49.509?20=0?10=195?</message>
</row> </resultset>

b) with for xml

select top 1 beginstring, sendercompid, targetcompid, session_qualifier,
msgseqnum, message from messages for xml option "entitize=no"

<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row>
<beginstring>test </beginstring>
<sendercompid>FIX-eSpeed</sendercompid>
<targetcompid>FIX-InfoReach</targetcompid>
<session_qualifier>834d09e20bdd49a88af8e9ccabee34ed</session_qualifier>
<msgseqnum>1</msgseqnum>
<message>8=FIX.4.29=16935=849=Bigbroker56=Fats Fast
Market52=20071010-19:10:49.50934=1192740=254=155=CREAF11=038=10044=33.514=032=031=0.059=06=0.060=20071010-19:10:49.50920=010=195</message>
</row> </resultset>

2. When calling select.. for xml with variable (@id) inside of the trigger,
application insert fails with java.sql.SQLException: Received error 154 :
Variable is not allowed in procedure

declare messages_cursor cursor

for select

session_qualifier

from inserted i

for read only

open messages_cursor

fetch messages_cursor into @id

while ( @@sqlstatus = 0 )

begin

set @msgtext = (select * from messages readpast where session_qualifier =
@id for xml option 'entitize=no, binary=base64' returns varchar(16384))

fetch messages_cursor into @id

end --while loop

-- clean-up

close messages_cursor

deallocate cursor messages_cursor

Please advice,

Thank You in advance,

Kind Regards,

PS

Still looking for an answer in Sybooks,