Support for 'xml' type in IBM DB2?

> Hello everybody,
>
> this is my first post to this list. My name is Thilo and I am
> embarking (with others) to build a generic form-based medical
> application with XForms and an XML (enabled) DB.
>
> I am currently testing how well Orbeon and DB2 work together.
>
> This is what I got working so far:
>
> 1. Adapted the SQL Address Book example to work with DB2 using the IBM
> JDBC driver (had to do some changes the the SQL statements as there
> where incompatibilities.
> 2. From there I wanted to replace the "standard" relational table with
> one that only has two columns: on for the id and another one for the
> data (containing an xml structure). I got this working for both
> reading *and* writing (in contrast to the documentation that says only
> reading would work) using a CLOB type for the data column.
>
> Obviously I want to use the pureXML features of DB2 and therefore I
> need to use the 'xml' type. Again it works to retrieve data. But in
> accordance to the documentation (says that it only works with Oracle
> 9) it doesn't work to retrieve data with this statement
> :<sql:update>INSERT INTO orbeon_xml_address_book (data) VALUES
> (<sql:param select="/*" type="odt:xmlFragment"
> sql-type="xmltype"/>)</sql:update>
>
> 2008-03-12 16:22:14,968 ERROR SQLProcessor  - PreparedStatement:
> INSERT INTO orbeon_xml_address_book (data) VALUES ( ? )
> 2008-03-12 16:22:14,968 INFO  DatabaseContext  - Rolling back JDBC
> connection for datasource:
> [com.ibm.db2.jcc.DB2Driver|jdbc:db2://localhost:50000/XF_TESTS|THILO].
> 2008-03-12 16:22:15,030 ERROR ProcessorService  - Exception at line 46
> of oxf:/apps/xforms-xml-address-book/init-database/init-address-
> book.xpl
> java.lang.UnsupportedOperationException
> at  
> org
> .orbeon
> .oxf
> .processor
> .sql
> .delegates
> .SQLProcessorGenericDelegate.setDOM(SQLProcessorGenericDelegate.java:
> 48)
> at  
> org
> .orbeon
> .oxf
> .processor
> .sql.interpreters.QueryInterpreter.end(QueryInterpreter.java:455)
>        ...
>
> The documentation states one should contact Orbeon to ask about more
> supported DBMS. Thought I would do it through the list as it might
> interest anybody else. So after much talking my question especially
> for the Orbeon team: Has this been solved in the past? If not, is it
> hard to do and/or will it be done in the near future.
>
> At the end of this lengthy first post I would like to say that the
> Orbeon effort (incl. the documentation) is impressive. I think
> combining it with the industry strength IBM DB2 V9.5 (pureXML feature)
> would be a great fit particular since the Express-C version is free.
> What does everybody else think?
>
> Cheers, Thilo

> Thilo,
>
>  Somehow I remember we tried storing XML into DB2 this way a long time
>  ago, when DB2 has alpha support for a native XML type, but I can't
>  find a trace of it now.
>
>  Unfortunately, database vendors all came up with their own non-
>  interoperable extensions, so we resorted to creating specific classes,
>  like for example SQLProcessorOracleGenericDelegate for Oracle. But we
>  haven't done this for DB2 at this point.
>
>  If you have Java skills, you could create the appropriate delegate
>  class. It shouldn't be too hard, and it would be a great contribution
>  to the code :-)
>
>  -Erik

> Hi Erik,
>
> thanks for the reply.
>
> I am in favour of such a contribution. We have a team meeting (3
> students) on Friday, so I will discuss that and get back to you.
>
> Glanced at the source code for the SQLProcessorOracleGenericDelegate
> and its predecessors and I think we should be able to figure it out.
>
> -Thilo
>
> On Wed, Mar 12, 2008 at 10:36 PM, Erik Bruchez <[hidden email]>  
> wrote:
>> Thilo,
>>
>> Somehow I remember we tried storing XML into DB2 this way a long time
>> ago, when DB2 has alpha support for a native XML type, but I can't
>> find a trace of it now.
>>
>> Unfortunately, database vendors all came up with their own non-
>> interoperable extensions, so we resorted to creating specific  
>> classes,
>> like for example SQLProcessorOracleGenericDelegate for Oracle. But we
>> haven't done this for DB2 at this point.
>>
>> If you have Java skills, you could create the appropriate delegate
>> class. It shouldn't be too hard, and it would be a great contribution
>> to the code :-)
>>
>> -Erik
>
> --
> You receive this message as a subscriber of the [hidden email]  
> mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> OW2 mailing lists service home page: http://www.ow2.org/wws

> Thilo,
>
>  That would be great.
>
>  Best,
>
>  -Erik
>
>
>
>  On Mar 12, 2008, at 4:05 PM, Thilo Schuler wrote:
>
>  > Hi Erik,
>  >
>  > thanks for the reply.
>  >
>  > I am in favour of such a contribution. We have a team meeting (3
>  > students) on Friday, so I will discuss that and get back to you.
>  >
>  > Glanced at the source code for the SQLProcessorOracleGenericDelegate
>  > and its predecessors and I think we should be able to figure it out.
>  >
>  > -Thilo
>  >
>  > On Wed, Mar 12, 2008 at 10:36 PM, Erik Bruchez <[hidden email]>
>  > wrote:
>  >> Thilo,
>  >>
>  >> Somehow I remember we tried storing XML into DB2 this way a long time
>  >> ago, when DB2 has alpha support for a native XML type, but I can't
>  >> find a trace of it now.
>  >>
>  >> Unfortunately, database vendors all came up with their own non-
>  >> interoperable extensions, so we resorted to creating specific
>  >> classes,
>  >> like for example SQLProcessorOracleGenericDelegate for Oracle. But we
>  >> haven't done this for DB2 at this point.
>  >>
>  >> If you have Java skills, you could create the appropriate delegate
>  >> class. It shouldn't be too hard, and it would be a great contribution
>  >> to the code :-)
>  >>
>  >> -Erik
>  >
>  > --
>  > You receive this message as a subscriber of the [hidden email]
>  > mailing list.
>  > To unsubscribe: mailto:[hidden email]
>  > For general help: mailto:[hidden email]?subject=help
>  > OW2 mailing lists service home page: http://www.ow2.org/wws
>
>
>
>  --
>  Orbeon Forms - Web Forms for the Enterprise Done the Right Way
>  http://www.orbeon.com/
>
>
>
>  --
>  You receive this message as a subscriber of the [hidden email] mailing list.
>  To unsubscribe: mailto:[hidden email]
>  For general help: mailto:[hidden email]?subject=help
>  OW2 mailing lists service home page: http://www.ow2.org/wws
>
>

> Erik,
>
> I just had a look in the DB2 Developer Handbook
> (http://www.redbooks.ibm.com/abstracts/sg247301.html) on page 224 it
> says XML data can be written to an XML column using - besides others -
> setClob(). So I tried it (hadn't done it before) with
> "<sql:update>INSERT INTO orbeon_xml_address_book (data) VALUES
> (<sql:param select="/*" type="odt:xmlFragment"
> sql-type="clob"/>)</sql:update>" and...
>
> ... it worked!
>
> So now i have completely adapted the Address Book example to working
> with an DB2 pureXML table [CREATE TABLE orbeon_xml_address_book (id
> INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, data XML)].
>
> That's cool!
>
> I will still discuss whether we need any of the other features
> described in the above mentioned guide. So we still might consider to
> implement a delegate class for DB2.
>
> -Thilo
>
> On Thu, Mar 13, 2008 at 12:13 AM, Erik Bruchez <[hidden email]>  
> wrote:
>> Thilo,
>>
>> That would be great.
>>
>> Best,
>>
>> -Erik
>>
>>
>>
>> On Mar 12, 2008, at 4:05 PM, Thilo Schuler wrote:
>>
>>> Hi Erik,
>>>
>>> thanks for the reply.
>>>
>>> I am in favour of such a contribution. We have a team meeting (3
>>> students) on Friday, so I will discuss that and get back to you.
>>>
>>> Glanced at the source code for the SQLProcessorOracleGenericDelegate
>>> and its predecessors and I think we should be able to figure it out.
>>>
>>> -Thilo
>>>
>>> On Wed, Mar 12, 2008 at 10:36 PM, Erik Bruchez <[hidden email]>
>>> wrote:
>>>> Thilo,
>>>>
>>>> Somehow I remember we tried storing XML into DB2 this way a long  
>>>> time
>>>> ago, when DB2 has alpha support for a native XML type, but I can't
>>>> find a trace of it now.
>>>>
>>>> Unfortunately, database vendors all came up with their own non-
>>>> interoperable extensions, so we resorted to creating specific
>>>> classes,
>>>> like for example SQLProcessorOracleGenericDelegate for Oracle.  
>>>> But we
>>>> haven't done this for DB2 at this point.
>>>>
>>>> If you have Java skills, you could create the appropriate delegate
>>>> class. It shouldn't be too hard, and it would be a great  
>>>> contribution
>>>> to the code :-)
>>>>
>>>> -Erik
>>>
>>> --
>>> You receive this message as a subscriber of the [hidden email]
>>> mailing list.
>>> To unsubscribe: mailto:[hidden email]
>>> For general help: mailto:[hidden email]?subject=help
>>> OW2 mailing lists service home page: http://www.ow2.org/wws
>>
>>
>>
>> --
>> Orbeon Forms - Web Forms for the Enterprise Done the Right Way
>> http://www.orbeon.com/
>>
>>
>>
>> --
>> You receive this message as a subscriber of the [hidden email]  
>> mailing list.
>> To unsubscribe: mailto:[hidden email]
>> For general help: mailto:[hidden email]?subject=help
>> OW2 mailing lists service home page: http://www.ow2.org/wws
>>
>>
>
> --
> You receive this message as a subscriber of the [hidden email]  
> mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> OW2 mailing lists service home page: http://www.ow2.org/wws