db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
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 GMT
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 <string value expression> immediately contained in
+			 * <XML parse> 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 '<oops>' 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 '<hmm/>' 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 ''<ay>caramba</ay>''';
+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 |<ay>caramba</ay>                                                         
 
 0 |<there><goes><my weight="180">attribute</my></goes></there>
               
 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 |<ay>caramba</ay>                                                         
 
 0 |<there><goes><my weight="180">attribute</my></goes></there>
               
 29 |NULL                                                                       
 30 |NULL                                                                       
@@ -957,6 +992,7 @@
 select i, xmlserialize(x as char(75)) from t3;
 I |2                                                                          
 -----
+0 |<ay>caramba</ay>                                                         
 
 0 |<there><goes><my weight="180">attribute</my></goes></there>
               
 29 |<none><here/></none>                                              
        
 30 |<my height="4.4">attribute</my>                                         
  

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 '<oops>' 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 '<hmm/>' 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 ''<ay>caramba</ay>''';
+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 |<ay>caramba</ay>                                                         
 
 0 |<there><goes><my weight="180">attribute</my></goes></there>
               
 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 |<ay>caramba</ay>                                                         
 
 0 |<there><goes><my weight="180">attribute</my></goes></there>
               
 29 |NULL                                                                       
 30 |NULL                                                                       
@@ -957,6 +993,7 @@
 select i, xmlserialize(x as char(75)) from t3;
 I |2                                                                          
 -----
+0 |<ay>caramba</ay>                                                         
 
 0 |<there><goes><my weight="180">attribute</my></goes></there>
               
 29 |<none><here/></none>                                              
        
 30 |<my height="4.4">attribute</my>                                         
  

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 '<oops>' 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 '<hmm/>' 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 ''<ay>caramba</ay>''';
+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          |<ay>caramba</ay>                                                
          
 0          |<there><goes><my weight="180">attribute</my></goes></there>
               
 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          |<ay>caramba</ay>                                                
          
 0          |<there><goes><my weight="180">attribute</my></goes></there>
               
 29         |NULL                                                                       
 30         |NULL                                                                       
@@ -973,6 +1011,7 @@
 select i, xmlserialize(x as char(75)) from t3;
 I          |2                                                                          
 ---------------------------------------------------------------------------------------
+0          |<ay>caramba</ay>                                                
          
 0          |<there><goes><my weight="180">attribute</my></goes></there>
               
 29         |<none><here/></none>                                      
                
 30         |<my height="4.4">attribute</my>                                 
          

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 '<hein/>' preserve whitespace) preserve
whitespace) from t1;
 select i from t1 where xmlparse(document '<hein/>' preserve whitespace);
 insert into t1 values (1, xmlparse(document '<oops>' preserve whitespace));
+prepare ps1 as 'insert into t1(x) values XMLPARSE(document ? preserve whitespace)';
 -- These should work.
 insert into t1 values (5, xmlparse(document '<hmm/>' preserve whitespace));
 insert into t1 values (6, xmlparse(document '<half> <masted> bass </masted>
boosted. </half>' preserve whitespace));
@@ -103,6 +127,8 @@
 update t1 set x = xmlparse(document '<update2> document was inserted as part of an
UPDATE </update2>' preserve whitespace) where xmlexists('/update' passing by ref x);
 select i from t1 where xmlparse(document '<hein/>' preserve whitespace) is not null;
 select i from t1 where xmlparse(document '<hein/>' 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 ''<ay>caramba</ay>''';
 
 -- "is [not] null" should work with XML.
 select i from t1 where x is not null;



Mime
View raw message