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.
