#258 Add Oracle support for XMLData

General
closed
Rob Manning
5
2007-09-13
2007-09-04
Barney Pitt
No

Oracle XMLData types always come back as <Unknown(2,007)>.

For some reason Oracle (10.2) JDBC returns an oracle OPAQUE type for XMLData fields.

This can be unwrapped to a String as in this code snippet ...

public String toString(Object object)
{
if (object instanceof OPAQUE)
{
try
{
OPAQUE opaque=(OPAQUE) object;
XMLType xml=XMLType.createXML(opaque);
result=xml.getStringVal();
}
catch (Exception e)
{
throw error(e);
}
}
else
{
result=object.toString();
}
}

This requires the following two Oracle Jars ...
xmlparserv2.jar
xdb.jar

Discussion

  • Rob Manning
    Rob Manning
    2007-09-05

    Logged In: YES
    user_id=1287991
    Originator: NO

    Thanks for the info. I am working on this at the moment and hope to make it available in 2.6 release. You will still need the XDK jars (the ones you've mentioned) to use the updates that I am planning to add to the Oracle Plugin to be able to view and edit SYS.XMLTYPE fields.

    Rob

     
  • Rob Manning
    Rob Manning
    2007-09-09

    Logged In: YES
    user_id=1287991
    Originator: NO

    Experimental support for this has been added and is available in the latest snapshot. You need the Oracle plugin and the Oracle XDK jars. Just add the XDK jars to the "Extra Classpath" for the driver. I tested basic display and editing in contents tab and SQL results panel. I am aware that the file import popup appears to work but doesn't update the data. That will get fixed soon.
    Enjoy and let me know if there are any problems using this.

    Rob

     
  • Barney Pitt
    Barney Pitt
    2007-09-11

    Logged In: YES
    user_id=1462737
    Originator: YES

    Great turnaround!

    Thanks, I'll try the snapshot.

     
  • Barney Pitt
    Barney Pitt
    2007-09-12

    Logged In: YES
    user_id=1462737
    Originator: YES

    Thanks, I've tried the snapshot and it works a treat.

    When documenting this you might note that it requires the latest thin JDBC driver - I initially got a NoSuchMethodException when testing with an older thin JDBC.

     
  • Rob Manning
    Rob Manning
    2007-09-12

    Logged In: YES
    user_id=1287991
    Originator: NO

    Cool - thanks for trying it out!

    I could possibly check the version of the JDBC driver in the plugin and disable XMLType support (and log a message) Can you tell me which version of the driver gave you the NoSuchMethodException? (You can click on the top node in the object tree and select the Metadata tab to find the value of getDriverVersion)

    BTW, I've discovered that my use of PreparedStatement.setString to bind the variable value prevents you from inserting/updating if the new value > 4000 characters. This is why the popup dialog for importing files didn't work for me at first. It will only work if the file being imported is less than 4k. I found some documentation that indicated the use of PreparedStatement.setObject(XMLType.createXml(value)) - for me this yields an exception that I'm still trying to track down the cause for. Stay tuned!

    Rob

     
  • Barney Pitt
    Barney Pitt
    2007-09-12

    Logged In: YES
    user_id=1462737
    Originator: YES

    From the manifest of the original (non-working) driver ...

    Manifest-Version: 1.0
    Specification-Title: "Oracle JDBC driver classes for use with JDK1.4"
    Specification-Version: "Oracle JDBC Driver version - 9.0.2.0.0"
    Specification-Vendor: "Oracle Corporation" .
    Implementation-Title: "ojdbc14.jar"
    Implementation-Version: "Oracle JDBC Driver version - 9.0.2.0.0"
    Implementation-Vendor: "Oracle Corporation"
    Implementation-Time: "Fri Jun 9 04:31:01 2006"

     
  • Rob Manning
    Rob Manning
    2007-09-13

    Logged In: YES
    user_id=1287991
    Originator: NO

    OK, so I tried a driver (with the same version) that came with my old version of Oracle 9i and got the NoSuchMethodException. However, that was with the XDK 10g. So I downloaded the 9i XDK and used those files instead and it worked fine. So, I'm inclined to ignore this type of error since it is an Oracle packaging issue that would be a challenge to correctly identify.

    In other news, I got the reflection code working properly now so that you can insert large documents using the popup file import/export panel. You can also export to file using this popup too. I'm done with this for now.

    Enjoy!

    Rob

     
  • Rob Manning
    Rob Manning
    2007-09-13

    • status: open --> closed