Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 42031 invoked from network); 11 Aug 2006 02:25:40 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 11 Aug 2006 02:25:40 -0000 Received: (qmail 56550 invoked by uid 500); 11 Aug 2006 02:25:40 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 56482 invoked by uid 500); 11 Aug 2006 02:25:39 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 56471 invoked by uid 99); 11 Aug 2006 02:25:39 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 10 Aug 2006 19:25:39 -0700 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [140.211.166.113] (HELO eris.apache.org) (140.211.166.113) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 10 Aug 2006 19:25:38 -0700 Received: by eris.apache.org (Postfix, from userid 65534) id B660C1A981A; Thu, 10 Aug 2006 19:25:11 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r430626 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/load/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/o... Date: Fri, 11 Aug 2006 02:25:09 -0000 To: derby-commits@db.apache.org From: bpendleton@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20060811022511.B660C1A981A@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Author: bpendleton Date: Thu Aug 10 19:25:08 2006 New Revision: 430626 URL: http://svn.apache.org/viewvc?rev=430626&view=rev Log: DERBY-688: Enhancements to XML functionality toward XPath and XQuery support This revision contains d688_phase4_v2.patch. This patch was contributed by Army Brown (qozinx@gmail.com). The phase 4 patch, d688_phase4_v2.patch, adds some additional restrictions to the ways in which XML values can be used. In particular: 1. XML types cannot be used in CREATE PROCEDURE or CREATE FUNCTION statements. 2. XML types cannot be used in import/export functions. 3. XML types cannot be declared as columns in a global temp table. I admit that I'm a bit fuzzy as to *why* these restrictions need to be in place, but these are the restrictions that apply to the other "long" datatypes in Derby (LOBs, LONG VARCHAR) so I'm enforcing them for XML, as well, to be safe. It'll be easier to remove these restrictions in the future than it will be to block them after users have potentially been relying on them. One final restriction added by this patch is as follows: if a parameter is used for the operand to an XMLPARSE operation, the parameter must have an explicit cast to a character string type. I've put this restriction in place because, based on my reading of the spec, this is required for SQL/XML[2006] conformance. Further explanation can be found in comments for the relevant code. d688_phase4_v2.patch also adds some simple test cases for each of these restrictions to the lang/xml_general.sql test, with the corresponding master updates. And finally, the phase 4 patch includes two new error messages: one for the XMLPARSE restriction, and one for missing XML classes, which is actually for phase 5 but I included it in the phase 4 patch so that the two patches can be applied sequentially (phase 4 then phase 5) without conflict. Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/TypeId.java db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ColumnInfo.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/xml_general.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xml_general.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/xml_general.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xmlBinding.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xml_general.sql Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/TypeId.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/TypeId.java?rev=430626&r1=430625&r2=430626&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/TypeId.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/TypeId.java Thu Aug 10 19:25:08 2006 @@ -961,9 +961,14 @@ break; case StoredFormatIds.XML_TYPE_ID: + typePrecedence = XML_PRECEDENCE; javaTypeName = "org.apache.derby.iapi.types.XML"; maxMaxWidth = TypeId.XML_MAXWIDTH; + + // We set this to true in order to disallow use + // of the XML datatype for procedure/function args. + isLongConcatableTypeId = true; break; } Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ColumnInfo.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ColumnInfo.java?rev=430626&r1=430625&r2=430626&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ColumnInfo.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ColumnInfo.java Thu Aug 10 19:25:08 2006 @@ -21,6 +21,8 @@ package org.apache.derby.impl.load; +import org.apache.derby.iapi.services.io.StoredFormatIds; + import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLWarning; @@ -209,7 +211,8 @@ type == java.sql.Types.JAVA_OBJECT || type == java.sql.Types.OTHER || type == java.sql.Types.CLOB || - type == java.sql.Types.BLOB); + type == java.sql.Types.BLOB || + type == StoredFormatIds.XML_TYPE_ID); } Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java?rev=430626&r1=430625&r2=430626&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java Thu Aug 10 19:25:08 2006 @@ -605,14 +605,37 @@ void bindParameter() throws StandardException { - if (operatorType == XMLPARSE_OP) { - // According to the SQL/XML standard, the XMLParse parameter - // takes a string operand. RESOLVE: We use CLOB here because - // an XML string can be arbitrarily long...is this okay? - // The SQL/XML spec doesn't state what the type of the param - // should be; only that it "shall be a character type". - operand.setType( - DataTypeDescriptor.getBuiltInDataTypeDescriptor(Types.CLOB)); + if (operatorType == XMLPARSE_OP) + { + /* SQL/XML[2006] allows both binary and character strings for + * the XMLParse parameter (section 10.16:Function). The spec + * also goes on to say, in section 6.15:Conformance Rules:4, + * that: + * + * "Without Feature X066, XMLParse: BLOB input and DOCUMENT + * option, in conforming SQL language, the declared type of + * the immediately contained in + * shall not be a binary string type." + * + * Thus since Derby doesn't currently support BLOB input, + * we have to ensure that the "declared type" of the parameter + * is not a binary string type; i.e. it must be a character + * string type. Since there's no way to determine what the + * declared type is from the XMLPARSE syntax, the user must + * explicitly declare the type of the parameter, and it must + * be a character string. They way s/he does that is by + * specifying an explicit CAST on the parameter, such as: + * + * insert into myXmlTable (xcol) values + * XMLPARSE(DOCUMENT cast (? as CLOB) PRESERVE WHITESPACE); + * + * If that was done then we wouldn't be here; we only get + * here if the parameter was specified without a cast. That + * means we don't know what the "declared type" is and so + * we throw an error. + */ + throw StandardException.newException( + SQLState.LANG_XMLPARSE_UNKNOWN_PARAM_TYPE); } else if (operatorType == XMLSERIALIZE_OP) { // For now, since JDBC has no type defined for XML, we Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties?rev=430626&r1=430625&r2=430626&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties Thu Aug 10 19:25:08 2006 @@ -1041,6 +1041,8 @@ X0X21.S=Context item must have type ''XML''; ''{0}'' is not allowed. X0X22.S=Values assigned to XML columns must be well-formed DOCUMENT nodes. X0X23.S=Invalid context item for {0} operation; context items must be well-formed DOCUMENT nodes. +X0X24.S=Failed to locate ''{0}'' API or implementation classes. XML operations are not permitted unless these classes are in your classpath. +X0X25.S=Unable to determine the parameter type for XMLPARSE; try using a CAST. X0XML.S=Encountered unexpected error while processing XML; see next exception for details. X0Y16.S=''{0}'' is not a view. If it is a table, then use DROP TABLE instead. Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=430626&r1=430625&r2=430626&view=diff ============================================================================== --- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original) +++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Thu Aug 10 19:25:08 2006 @@ -1217,6 +1217,8 @@ String LANG_INVALID_CONTEXT_ITEM_TYPE = "X0X21.S"; String LANG_INVALID_XML_COLUMN_ASSIGN = "X0X22.S"; String LANG_INVALID_XML_CONTEXT_ITEM = "X0X23.S"; + String LANG_MISSING_XML_CLASSES = "X0X24.S"; + String LANG_XMLPARSE_UNKNOWN_PARAM_TYPE = "X0X25.S"; String LANG_UNEXPECTED_XML_EXCEPTION = "X0XML.S"; // X0Y01 used to be DUPLICATE_KEY_CONSTRAINT Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/xml_general.out URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/xml_general.out?rev=430626&r1=430625&r2=430626&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/xml_general.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/xml_general.out Thu Aug 10 19:25:08 2006 @@ -130,6 +130,34 @@ ERROR X0X67: Columns of type 'XML' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> select i from t1 where x is null order by x; ERROR X0X67: Columns of type 'XML' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. +ij> -- XML cannot be imported or exported. These should all fail. +CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ( + null, 'T1', 'xmlexport.del', null, null, null); +ERROR 38000: The exception 'java.sql.SQLException: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.' was thrown while evaluating an expression. SQLSTATE: X0X15: XML values are not allowed in top-level result sets; try using XMLSERIALIZE. +ij> CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY( + 'select x from t1', 'xmlexport.del', null, null, null); +ERROR 38000: The exception 'java.sql.SQLException: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.' was thrown while evaluating an expression. SQLSTATE: X0X15: XML values are not allowed in top-level result sets; try using XMLSERIALIZE. +ij> CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY ( + 'select xmlserialize(x as clob) from t1', + 'xmlexport.del', null, null, null); +ERROR XIE0B: Column '1' in the table is of type CLOB, it is not supported by the import/export feature. +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ( + null, 'T1', 'shouldntmatter.del', null, null, null, 0); +ERROR XIE0B: Column 'X' in the table is of type XML, it is not supported by the import/export feature. +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA ( + NULL, 'T1', null, '2', 'shouldntmatter.del', null, null, null,0); +ERROR XIE0B: Column 'X' in the table is of type XML, it is not supported by the import/export feature. +ij> -- XML cannot be used with procedures/functions. +create procedure hmmproc (in i int, in x xml) + parameter style java language java external name 'hi.there'; +ERROR 42962: Long column type column or parameter 'X' not permitted in declared global temporary tables or procedure definitions. +ij> create function hmmfunc (i int, x xml) returns int + parameter style java language java external name 'hi.there'; +ERROR 42962: Long column type column or parameter 'X' not permitted in declared global temporary tables or procedure definitions. +ij> -- XML columns cannot be used for global temporary tables. +declare global temporary table SESSION.xglobal (myx XML) + not logged on commit preserve rows; +ERROR 42962: Long column type column or parameter 'MYX' not permitted in declared global temporary tables or procedure definitions. ij> -- XML cols can be used in a SET clause, if target value is XML. create trigger tr2 after insert on t1 for each row mode db2sql update t1 set x = 'hmm'; ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'. @@ -153,6 +181,7 @@ ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'XML' expression. It must be a BOOLEAN expression. ij> insert into t1 values (1, xmlparse(document '' preserve whitespace)); ERROR 2200L: XMLPARSE operand is not an XML document; see next exception for details. SQLSTATE: XJ001: Java exception: 'XML document structures must start and end within the same entity.: xxxFILTERED-SAX-EXCEPTIONxxx'. +ij> prepare ps1 as 'insert into t1(x) values XMLPARSE(document ? preserve whitespace)'; ij> -- These should work. insert into t1 values (5, xmlparse(document '' preserve whitespace)); 1 row inserted/updated/deleted @@ -188,6 +217,10 @@ 5 6 7 +ij> prepare ps1 as 'insert into t3(i, x) values (0, XMLPARSE(document cast (? as CLOB) preserve whitespace))'; +ij> execute ps1 using 'values ''caramba'''; +IJ WARNING: Autocommit may close using result set +1 row inserted/updated/deleted ij> -- "is [not] null" should work with XML. select i from t1 where x is not null; I @@ -777,6 +810,7 @@ ij> select i, xmlserialize(x as char(75)) from t3; I |2 ----- +0 |caramba 0 |attribute ij> -- These should all fail because the result of the XMLQUERY op is ----- not a valid document (it's either an empty sequence, an attribute, @@ -887,6 +921,7 @@ select i, xmlserialize(x as char(75)) from t3; I |2 ----- +0 |caramba 0 |attribute 29 |NULL 30 |NULL @@ -957,6 +992,7 @@ select i, xmlserialize(x as char(75)) from t3; I |2 ----- +0 |caramba 0 |attribute 29 | 30 |attribute Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xml_general.out URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xml_general.out?rev=430626&r1=430625&r2=430626&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xml_general.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xml_general.out Thu Aug 10 19:25:08 2006 @@ -130,6 +130,34 @@ ERROR X0X67: Columns of type 'XML' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> select i from t1 where x is null order by x; ERROR X0X67: Columns of type 'XML' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. +ij> -- XML cannot be imported or exported. These should all fail. +CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ( + null, 'T1', 'xmlexport.del', null, null, null); +ERROR 38000: The exception 'java.sql.SQLException: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.' was thrown while evaluating an expression. SQLSTATE: X0X15: XML values are not allowed in top-level result sets; try using XMLSERIALIZE. +ij> CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY( + 'select x from t1', 'xmlexport.del', null, null, null); +ERROR 38000: The exception 'java.sql.SQLException: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.' was thrown while evaluating an expression. SQLSTATE: X0X15: XML values are not allowed in top-level result sets; try using XMLSERIALIZE. +ij> CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY ( + 'select xmlserialize(x as clob) from t1', + 'xmlexport.del', null, null, null); +ERROR XIE0B: Column '1' in the table is of type CLOB, it is not supported by the import/export feature. +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ( + null, 'T1', 'shouldntmatter.del', null, null, null, 0); +ERROR XIE0B: Column 'X' in the table is of type XML, it is not supported by the import/export feature. +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA ( + NULL, 'T1', null, '2', 'shouldntmatter.del', null, null, null,0); +ERROR XIE0B: Column 'X' in the table is of type XML, it is not supported by the import/export feature. +ij> -- XML cannot be used with procedures/functions. +create procedure hmmproc (in i int, in x xml) + parameter style java language java external name 'hi.there'; +ERROR 42962: Long column type column or parameter 'X' not permitted in declared global temporary tables or procedure definitions. +ij> create function hmmfunc (i int, x xml) returns int + parameter style java language java external name 'hi.there'; +ERROR 42962: Long column type column or parameter 'X' not permitted in declared global temporary tables or procedure definitions. +ij> -- XML columns cannot be used for global temporary tables. +declare global temporary table SESSION.xglobal (myx XML) + not logged on commit preserve rows; +ERROR 42962: Long column type column or parameter 'MYX' not permitted in declared global temporary tables or procedure definitions. ij> -- XML cols can be used in a SET clause, if target value is XML. create trigger tr2 after insert on t1 for each row mode db2sql update t1 set x = 'hmm'; ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'. @@ -153,6 +181,8 @@ ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'XML' expression. It must be a BOOLEAN expression. ij> insert into t1 values (1, xmlparse(document '' preserve whitespace)); ERROR 2200L: XMLPARSE operand is not an XML document; see next exception for details. SQLSTATE: XJ001: Java exception: 'XML document structures must start and end within the same entity.: xxxFILTERED-SAX-EXCEPTIONxxx'. +ij> prepare ps1 as 'insert into t1(x) values XMLPARSE(document ? preserve whitespace)'; +ERROR X0X25: Unable to determine the parameter type for XMLPARSE; try using a CAST. ij> -- These should work. insert into t1 values (5, xmlparse(document '' preserve whitespace)); 1 row inserted/updated/deleted @@ -188,6 +218,10 @@ 5 6 7 +ij> prepare ps1 as 'insert into t3(i, x) values (0, XMLPARSE(document cast (? as CLOB) preserve whitespace))'; +ij> execute ps1 using 'values ''caramba'''; +IJ WARNING: Autocommit may close using result set +1 row inserted/updated/deleted ij> -- "is [not] null" should work with XML. select i from t1 where x is not null; I @@ -777,6 +811,7 @@ ij> select i, xmlserialize(x as char(75)) from t3; I |2 ----- +0 |caramba 0 |attribute ij> -- These should all fail because the result of the XMLQUERY op is ----- not a valid document (it's either an empty sequence, an attribute, @@ -887,6 +922,7 @@ select i, xmlserialize(x as char(75)) from t3; I |2 ----- +0 |caramba 0 |attribute 29 |NULL 30 |NULL @@ -957,6 +993,7 @@ select i, xmlserialize(x as char(75)) from t3; I |2 ----- +0 |caramba 0 |attribute 29 | 30 |attribute Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/xml_general.out URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/xml_general.out?rev=430626&r1=430625&r2=430626&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/xml_general.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/xml_general.out Thu Aug 10 19:25:08 2006 @@ -130,6 +130,36 @@ ERROR X0X67: Columns of type 'XML' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> select i from t1 where x is null order by x; ERROR X0X67: Columns of type 'XML' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. +ij> -- XML cannot be imported or exported. These should all fail. +CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ( + null, 'T1', 'xmlexport.del', null, null, null); +ERROR 38000: The exception 'java.sql.SQLException: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.' was thrown while evaluating an expression. +ERROR X0X15: XML values are not allowed in top-level result sets; try using XMLSERIALIZE. +ij> CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY( + 'select x from t1', 'xmlexport.del', null, null, null); +ERROR 38000: The exception 'java.sql.SQLException: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.' was thrown while evaluating an expression. +ERROR X0X15: XML values are not allowed in top-level result sets; try using XMLSERIALIZE. +ij> CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY ( + 'select xmlserialize(x as clob) from t1', + 'xmlexport.del', null, null, null); +ERROR XIE0B: Column '1' in the table is of type CLOB, it is not supported by the import/export feature. +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ( + null, 'T1', 'shouldntmatter.del', null, null, null, 0); +ERROR XIE0B: Column 'X' in the table is of type XML, it is not supported by the import/export feature. +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA ( + NULL, 'T1', null, '2', 'shouldntmatter.del', null, null, null,0); +ERROR XIE0B: Column 'X' in the table is of type XML, it is not supported by the import/export feature. +ij> -- XML cannot be used with procedures/functions. +create procedure hmmproc (in i int, in x xml) + parameter style java language java external name 'hi.there'; +ERROR 42962: Long column type column or parameter 'X' not permitted in declared global temporary tables or procedure definitions. +ij> create function hmmfunc (i int, x xml) returns int + parameter style java language java external name 'hi.there'; +ERROR 42962: Long column type column or parameter 'X' not permitted in declared global temporary tables or procedure definitions. +ij> -- XML columns cannot be used for global temporary tables. +declare global temporary table SESSION.xglobal (myx XML) + not logged on commit preserve rows; +ERROR 42962: Long column type column or parameter 'MYX' not permitted in declared global temporary tables or procedure definitions. ij> -- XML cols can be used in a SET clause, if target value is XML. create trigger tr2 after insert on t1 for each row mode db2sql update t1 set x = 'hmm'; ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'. @@ -154,6 +184,8 @@ ij> insert into t1 values (1, xmlparse(document '' preserve whitespace)); ERROR 2200L: XMLPARSE operand is not an XML document; see next exception for details. ERROR XJ001: Java exception: 'XML document structures must start and end within the same entity.: xxxFILTERED-SAX-EXCEPTIONxxx'. +ij> prepare ps1 as 'insert into t1(x) values XMLPARSE(document ? preserve whitespace)'; +ERROR X0X25: Unable to determine the parameter type for XMLPARSE; try using a CAST. ij> -- These should work. insert into t1 values (5, xmlparse(document '' preserve whitespace)); 1 row inserted/updated/deleted @@ -189,6 +221,10 @@ 5 6 7 +ij> prepare ps1 as 'insert into t3(i, x) values (0, XMLPARSE(document cast (? as CLOB) preserve whitespace))'; +ij> execute ps1 using 'values ''caramba'''; +IJ WARNING: Autocommit may close using result set +1 row inserted/updated/deleted ij> -- "is [not] null" should work with XML. select i from t1 where x is not null; I @@ -793,6 +829,7 @@ ij> select i, xmlserialize(x as char(75)) from t3; I |2 --------------------------------------------------------------------------------------- +0 |caramba 0 |attribute ij> -- These should all fail because the result of the XMLQUERY op is -- not a valid document (it's either an empty sequence, an attribute, @@ -903,6 +940,7 @@ select i, xmlserialize(x as char(75)) from t3; I |2 --------------------------------------------------------------------------------------- +0 |caramba 0 |attribute 29 |NULL 30 |NULL @@ -973,6 +1011,7 @@ select i, xmlserialize(x as char(75)) from t3; I |2 --------------------------------------------------------------------------------------- +0 |caramba 0 |attribute 29 | 30 |attribute Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xmlBinding.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xmlBinding.java?rev=430626&r1=430625&r2=430626&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xmlBinding.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xmlBinding.java Thu Aug 10 19:25:08 2006 @@ -236,7 +236,7 @@ PreparedStatement pSt = conn.prepareStatement( "insert into xTable.t1(x) values " + - "(XMLPARSE (DOCUMENT ? PRESERVE WHITESPACE))"); + "(XMLPARSE (DOCUMENT CAST (? as CLOB) PRESERVE WHITESPACE))"); // This should work. Note we check binding to // a character stream method in "insertFiles". @@ -396,7 +396,7 @@ PreparedStatement pSt = conn.prepareStatement( "insert into xTable.t1(x) values (" + - "xmlparse(document ? preserve whitespace))"); + "xmlparse(document cast (? as clob) preserve whitespace))"); for (int i = 0; i < numRows; i++) { @@ -499,7 +499,7 @@ docAsString = sBuf.toString(); PreparedStatement pSt = conn.prepareStatement( "insert into xTable.t1(x) values (" + - "xmlparse(document ? preserve whitespace))"); + "xmlparse(document cast (? as clob) preserve whitespace))"); charCount = docAsString.length(); for (int i = 0; i < numRows; i++) { Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xml_general.sql URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xml_general.sql?rev=430626&r1=430625&r2=430626&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xml_general.sql (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xml_general.sql Thu Aug 10 19:25:08 2006 @@ -79,6 +79,29 @@ create index oops_ix on t1(x); select i from t1 where x is null order by x; +-- XML cannot be imported or exported. These should all fail. +CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ( + null, 'T1', 'xmlexport.del', null, null, null); +CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY( + 'select x from t1', 'xmlexport.del', null, null, null); +CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY ( + 'select xmlserialize(x as clob) from t1', + 'xmlexport.del', null, null, null); +CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ( + null, 'T1', 'shouldntmatter.del', null, null, null, 0); +CALL SYSCS_UTIL.SYSCS_IMPORT_DATA ( + NULL, 'T1', null, '2', 'shouldntmatter.del', null, null, null,0); + +-- XML cannot be used with procedures/functions. +create procedure hmmproc (in i int, in x xml) + parameter style java language java external name 'hi.there'; +create function hmmfunc (i int, x xml) returns int + parameter style java language java external name 'hi.there'; + +-- XML columns cannot be used for global temporary tables. +declare global temporary table SESSION.xglobal (myx XML) + not logged on commit preserve rows; + -- XML cols can be used in a SET clause, if target value is XML. create trigger tr2 after insert on t1 for each row mode db2sql update t1 set x = 'hmm'; create trigger tr1 after insert on t1 for each row mode db2sql update t1 set x = null; @@ -93,6 +116,7 @@ select xmlparse(document xmlparse(document '' preserve whitespace) preserve whitespace) from t1; select i from t1 where xmlparse(document '' preserve whitespace); insert into t1 values (1, xmlparse(document '' preserve whitespace)); +prepare ps1 as 'insert into t1(x) values XMLPARSE(document ? preserve whitespace)'; -- These should work. insert into t1 values (5, xmlparse(document '' preserve whitespace)); insert into t1 values (6, xmlparse(document ' bass boosted. ' preserve whitespace)); @@ -103,6 +127,8 @@ update t1 set x = xmlparse(document ' document was inserted as part of an UPDATE ' preserve whitespace) where xmlexists('/update' passing by ref x); select i from t1 where xmlparse(document '' preserve whitespace) is not null; select i from t1 where xmlparse(document '' preserve whitespace) is not null order by i; +prepare ps1 as 'insert into t3(i, x) values (0, XMLPARSE(document cast (? as CLOB) preserve whitespace))'; +execute ps1 using 'values ''caramba'''; -- "is [not] null" should work with XML. select i from t1 where x is not null;