I'm trying to extract XML stored in a CLOB from an oracle database using the sql module for BaseX. When I select the clob value, I get this sort of thing:
<sql:column name="MSG">oracle.sql.CLOB@49458ef3</sql:column>
I've tried casting it to XMLType (similar message) and to char (buffer size too small for the XML I'm dealing with).
Is there a way of doing this short of creating a specialized Java module for it?
Thanks, Scott
Hi Scott,
I'm trying to extract XML stored in a CLOB from an oracle database using the sql module for BaseX. When I select the clob value, I get this sort of thing:
<sql:column name="MSG">oracle.sql.CLOB@49458ef3</sql:column>
I can well imagine that our SQL Module implementation [1] could be extended to support more data types, but we should probably try to stick with the standard Java SQL types. Do you know if oracle.sql.CLOB inherits some class from the "java.sql" package?
Christian
[1] https://github.com/BaseXdb/basex/blob/master/basex-core/src/main/java/org/ba...
Christian, thanks for the response. It does look like Oracle JDBC implements java.sql.* interfaces:
http://docs.oracle.com/cd/E18283_01/java.112/e16548/jdbcvers.htm
Turns out the existing SQL module does read java.sql.SQLXML object values (line 373). To make it work, you need the ojdbc6.jar, xdb.jar and xmlparserv2.jar files on your classpath, and set this property: -Doracle.jdbc.getObjectReturnsXMLType="false"
I got it working, but the results were not what I was expecting: the XML is escaped (i.e. >element_name<, etc) within the sql:column element. Maybe this is the "fallback" string representation (line 380 in the module)? Any suggestions for getting the actual XML into BaseX?
Thanks, Scott
-----Original Message----- From: Christian Grün [mailto:christian.gruen@gmail.com] Sent: Monday, March 24, 2014 2:19 AM To: Bednar, Scott E Cc: basex-talk@mailman.uni-konstanz.de Subject: Re: [basex-talk] XML clob w/ SQL module
Hi Scott,
I'm trying to extract XML stored in a CLOB from an oracle database using the sql module for BaseX. When I select the clob value, I get this sort of thing:
<sql:column name="MSG">oracle.sql.CLOB@49458ef3</sql:column>
I can well imagine that our SQL Module implementation [1] could be extended to support more data types, but we should probably try to stick with the standard Java SQL types. Do you know if oracle.sql.CLOB inherits some class from the "java.sql" package?
Christian
[1] https://github.com/BaseXdb/basex/blob/master/basex-core/src/main/java/org/ba...
Hi Scott,
I got it working, but the results were not what I was expecting: the XML is escaped (i.e. >element_name<, etc) within the sql:column element. Maybe this is the "fallback" string representation (line 380 in the module)? Any suggestions for getting the actual XML into BaseX?
Yes, it seems as if the input is not recognized as valid XML. I have just uploaded a new snapshot [1]. If you start BaseX with the -d flag, it generates some additional debugging info. Could you give it a try? As an alternative, you could also send me one or two of the escaped XML snippets.
Christian
[1] http://files.basex.org/releases/latest/
Thanks, Scott
-----Original Message----- From: Christian Grün [mailto:christian.gruen@gmail.com] Sent: Monday, March 24, 2014 2:19 AM To: Bednar, Scott E Cc: basex-talk@mailman.uni-konstanz.de Subject: Re: [basex-talk] XML clob w/ SQL module
Hi Scott,
I'm trying to extract XML stored in a CLOB from an oracle database using the sql module for BaseX. When I select the clob value, I get this sort of thing:
<sql:column name="MSG">oracle.sql.CLOB@49458ef3</sql:column>
I can well imagine that our SQL Module implementation [1] could be extended to support more data types, but we should probably try to stick with the standard Java SQL types. Do you know if oracle.sql.CLOB inherits some class from the "java.sql" package?
Christian
[1] https://github.com/BaseXdb/basex/blob/master/basex-core/src/main/java/org/ba...
basex-talk@mailman.uni-konstanz.de
-
Bednar, Scott E -
Christian Grün