You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Daniel John Debrunner <dj...@apache.org> on 2006/08/29 18:02:07 UTC
XML in Derby - WAS : [jira] Updated: (DERBY-688) Enhancements to
XML functionality to move toward XPath/XQuery support...
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.
I setup a table with a column that contained a URL as a VARCHAR and an
XML column, a procedure in an INSERT trigger then fetched the data from
the URL and updated the XML column using XMLPARSE, streaming directly
from the remote site using setCharacterStream.
I used this to download DERBY Jira issues, each row holds an XML
document that corresponds to a single Jira issue. E.g. from
http://issues.apache.org/jira/browse/DERBY-434?decorator=none&view=rss
Then I can execute queries against the issues locally, using XPath (and
SQL).
-- Sequence of all comments made by Sunitha against bugs reported by me
-- wd_id is the DERBY-XXX identifier
-- wd_accesstime is the time the data was downloaded from the web.
-- wd_content is the XML column
select wd_id, wd_accesstime,
XMLSERIALIZE(
XMLQUERY('//item/comments/comment[@author="skambha"]' PASSING BY REF
wd_content EMPTY ON EMPTY)
AS VARCHAR(30000))
from wdd.web_docs where
XMLEXISTS('//reporter[text() = "Daniel John Debrunner"]' PASSING BY REF
wd_content);
-- Jira status of all bugs entered by me
select wd_id,
XMLSERIALIZE(
XMLQUERY('//item/status/text()' PASSING BY REF wd_content EMPTY ON EMPTY)
AS VARCHAR(20)),
wd_accesstime
from wdd.web_docs where
XMLEXISTS('//reporter[text() = "Daniel John Debrunner"]' PASSING BY REF
wd_content)
order by 2,1;
I'll think I wil expand this to use it in my talk at ApacheCon US.
Dan.
Re: XML in Derby - WAS : [jira] Updated: (DERBY-688) Enhancements
to XML functionality to move toward XPath/XQuery support...
Posted by Daniel John Debrunner <dj...@apache.org>.
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.
Re: XML in Derby - WAS : [jira] Updated: (DERBY-688) Enhancements to
XML functionality to move toward XPath/XQuery support...
Posted by Kristian Waagan <Kr...@Sun.COM>.
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.
Regards,
--
Kristian
>
> I setup a table with a column that contained a URL as a VARCHAR and an
> XML column, a procedure in an INSERT trigger then fetched the data from
> the URL and updated the XML column using XMLPARSE, streaming directly
> from the remote site using setCharacterStream.
>
> I used this to download DERBY Jira issues, each row holds an XML
> document that corresponds to a single Jira issue. E.g. from
>
> http://issues.apache.org/jira/browse/DERBY-434?decorator=none&view=rss
>
> Then I can execute queries against the issues locally, using XPath (and
> SQL).
>
> -- Sequence of all comments made by Sunitha against bugs reported by me
> -- wd_id is the DERBY-XXX identifier
> -- wd_accesstime is the time the data was downloaded from the web.
> -- wd_content is the XML column
>
> select wd_id, wd_accesstime,
> XMLSERIALIZE(
> XMLQUERY('//item/comments/comment[@author="skambha"]' PASSING BY REF
> wd_content EMPTY ON EMPTY)
> AS VARCHAR(30000))
> from wdd.web_docs where
> XMLEXISTS('//reporter[text() = "Daniel John Debrunner"]' PASSING BY REF
> wd_content);
>
> -- Jira status of all bugs entered by me
>
> select wd_id,
> XMLSERIALIZE(
> XMLQUERY('//item/status/text()' PASSING BY REF wd_content EMPTY ON EMPTY)
> AS VARCHAR(20)),
> wd_accesstime
> from wdd.web_docs where
> XMLEXISTS('//reporter[text() = "Daniel John Debrunner"]' PASSING BY REF
> wd_content)
> order by 2,1;
>
> I'll think I wil expand this to use it in my talk at ApacheCon US.
> Dan.
>
>
>
>
>
>
>
>