db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: XML in Derby - WAS : [jira] Updated: (DERBY-688) Enhancements to XML functionality to move toward XPath/XQuery support...
Date Tue, 29 Aug 2006 22:51:20 GMT
Kristian Waagan wrote:

> Daniel John Debrunner wrote:
> 
>> David Van Couvering wrote:
>>
>>> Wow, great work Army, and thanks for the careful review and effort by
>>> Brian and Yip!  It's great to have this in.  If I could only grok
>>> exactly what the feature is and how I might use it :)  I am telling
>>> people "we have XML features" and I know it's something to do with
>>> XQuery and XPath, but I couldn't say what.
>>
>>
>> I was messing with the XML support last night for buddy testing and it's
>> very cool. Three new Derby features combined together to make the
>> application development easier:
>>
>> 1) XML support
>> 2) CALL procedure in trigger
>> 3) Lengthless overrides for PreparedStatement.setCharacterStream
>>
>> OK - I didn't test 3) cos I would have to set up Xalan for Mustang and I
>> didn't want to spend time on how to figure that out, but it would have
>> been useful.
> 
> 
> Hi Dan,
> 
> This sounds really cool :)
> Any change you can share your trigger procedure?
> Then maybe someone else can take on the work to test the new lengthless
> overrides added by JDBC 4.0. I don't think they have received much
> testing yet. They are now in the 10.2 branch, but I believe they are not
> (fully) included in the latest beta (10.2.1.1). I'll add an entry to the
> buddy testing page when the time is right.

Here's the Java method:

    public static void get_url_content(String id, String path)
       throws SQLException, IOException
    {
        Connection conn =
DriverManager.getConnection("jdbc:default:connection");

        PreparedStatement ps = conn.prepareStatement(
                "UPDATE WDD.WEB_DOCS SET WD_CONTENT = " +
                "XMLPARSE (DOCUMENT CAST (? AS CLOB) PRESERVE WHITESPACE)" +
                " , WD_ACCESSTIME = CURRENT TIMESTAMP " +
                "WHERE WD_ID = ?");
        ps.setString(2, id);


        URL url = new URL(path);

        URLConnection urlConn = url.openConnection();

        urlConn.connect();

        int length = urlConn.getContentLength();
        String enc = urlConn.getContentEncoding();

        if (enc == null)
            enc = "UTF-8";

        InputStream in = urlConn.getInputStream();
        InputStreamReader isr = new InputStreamReader(in, enc);

        // HACK - Assume number of characters will be
        // the same as the number of bytes.
        ps.setCharacterStream(1, isr, length);

        ps.execute();

        in.close();

        ps.close();
        conn.close();
    }

and the SQL

DROP TABLE WDD.WEB_DOCS;
CREATE TABLE WDD.WEB_DOCS (
  WD_ID VARCHAR(128) PRIMARY KEY,
  WD_URL VARCHAR(1000),
  WD_CONTENT XML,
  WD_ACCESSTIME TIMESTAMP
);

DROP PROCEDURE WDD.GET_URL_CONTENT;
CREATE PROCEDURE WDD.GET_URL_CONTENT(ID VARCHAR(128), URL VARCHAR(1000))
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'wdd.get_url_content';

CREATE TRIGGER WDD.WD_I AFTER INSERT
ON WDD.WEB_DOCS
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
CALL WDD.GET_URL_CONTENT(NEW.WD_ID, NEW.WD_URL);

Dan.


Mime
View raw message