Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 46607 invoked from network); 9 Jun 2005 06:49:05 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 9 Jun 2005 06:49:05 -0000 Received: (qmail 42005 invoked by uid 500); 9 Jun 2005 06:49:04 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 41944 invoked by uid 500); 9 Jun 2005 06:49:04 -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 41872 invoked by uid 500); 9 Jun 2005 06:49:03 -0000 Delivered-To: apmail-incubator-derby-cvs@incubator.apache.org Received: (qmail 41842 invoked by uid 99); 9 Jun 2005 06:49:01 -0000 X-ASF-Spam-Status: No, hits=-9.8 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from minotaur.apache.org (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.28) with SMTP; Wed, 08 Jun 2005 23:48:55 -0700 Received: (qmail 46496 invoked by uid 65534); 9 Jun 2005 06:48:45 -0000 Message-ID: <20050609064845.46494.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: svn commit: r189721 [3/5] - in /incubator/derby/code/trunk: java/engine/org/apache/derby/catalog/types/ java/engine/org/apache/derby/iapi/ java/engine/org/apache/derby/iapi/reference/ java/engine/org/apache/derby/iapi/services/io/ java/engine/org/apache/derby/iapi/sql/compile/ java/engine/org/apache/derby/iapi/types/ java/engine/org/apache/derby/impl/jdbc/ java/engine/org/apache/derby/impl/sql/compile/ java/engine/org/apache/derby/loc/ java/testing/org/apache/derbyTesting/functionTests/master/ java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/ java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ java/testing/org/apache/derbyTesting/functionTests/tests/lang/ java/testing/org/apache/derbyTesting/functionTests/tests/lang/xmlTestFiles/ tools/jar/ Date: Thu, 09 Jun 2005 06:48:36 -0000 To: derby-cvs@incubator.apache.org From: bandaram@apache.org X-Mailer: svnmailer-1.0.0-dev X-Virus-Checked: Checked X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/func= tionTests/master/DerbyNetClient/xml_general.out URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/= org/apache/derbyTesting/functionTests/master/DerbyNetClient/xml_general.out= ?rev=3D189721&view=3Dauto =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/DerbyNetClient/xml_general.out (added) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/DerbyNetClient/xml_general.out Wed Jun 8 23:48:34 2005 @@ -0,0 +1,582 @@ +ij> create table t0 (si smallint, i int, bi bigint, vcb varchar (32) for b= it data, nu numeric(10,2), f float, d double, vc varchar(20), da date, ti t= ime, ts timestamp, cl clob, bl blob); +0 rows inserted/updated/deleted +ij> -- XML column declarations should work like other built-in types. +create table t1 (i int, x xml); +0 rows inserted/updated/deleted +ij> create table t2 (i int, x xml not null); +0 rows inserted/updated/deleted +ij> create table t3 (i int, x xml default null); +0 rows inserted/updated/deleted +ij> create table t4 (vc varchar(100)); +0 rows inserted/updated/deleted +ij> create table t5 (x2 xml not null); +0 rows inserted/updated/deleted +ij> alter table t5 add column x1 xml; +0 rows inserted/updated/deleted +ij> -- Check insertion of null XML values. +----- Next four should work. +insert into t1 values (1, null); +1 row inserted/updated/deleted +ij> insert into t1 values (2, cast (null as xml)); +1 row inserted/updated/deleted +ij> insert into t1 (i) values (4); +1 row inserted/updated/deleted +ij> insert into t1 values (3, default); +1 row inserted/updated/deleted +ij> -- Next two should fail. +insert into t2 values (1, null); +ERROR 23502: Column 'X' cannot accept a NULL value. +ij> insert into t2 values (2, cast (null as xml)); +ERROR 23502: Column 'X' cannot accept a NULL value. +ij> -- XML cols can't hold non-XML types. +insert into t1 values (3, 'hmm'); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'.=20 +ij> insert into t1 values (1, 2); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'INTEGER'.=20 +ij> insert into t1 values (1, 123.456); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'DECIMAL'.=20 +ij> insert into t1 values (1, x'01'); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR () FOR= BIT DATA'.=20 +ij> insert into t1 values (1, x'ab'); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR () FOR= BIT DATA'.=20 +ij> insert into t1 values (1, current date); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'DATE'.=20 +ij> insert into t1 values (1, current time); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'TIME'.=20 +ij> insert into t1 values (1, current timestamp); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'TIMESTAMP'.=20 +ij> insert into t1 values (1, ('hmm' | | 'andstuff')); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'.=20 +ij> -- XML can't be stored in non-XML cols. +insert into t0 (si) values (cast (null as xml)); +ERROR 42821: Columns of type 'SMALLINT' cannot hold values of type 'XML'.=20 +ij> insert into t0 (i) values (cast (null as xml)); +ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'XML'.=20 +ij> insert into t0 (bi) values (cast (null as xml)); +ERROR 42821: Columns of type 'BIGINT' cannot hold values of type 'XML'.=20 +ij> insert into t0 (vcb) values (cast (null as xml)); +ERROR 42821: Columns of type 'VARCHAR () FOR BIT DATA' cannot hold values = of type 'XML'.=20 +ij> insert into t0 (nu) values (cast (null as xml)); +ERROR 42821: Columns of type 'NUMERIC' cannot hold values of type 'XML'.=20 +ij> insert into t0 (f) values (cast (null as xml)); +ERROR 42821: Columns of type 'DOUBLE' cannot hold values of type 'XML'.=20 +ij> insert into t0 (d) values (cast (null as xml)); +ERROR 42821: Columns of type 'DOUBLE' cannot hold values of type 'XML'.=20 +ij> insert into t0 (vc) values (cast (null as xml)); +ERROR 42821: Columns of type 'VARCHAR' cannot hold values of type 'XML'.=20 +ij> insert into t0 (da) values (cast (null as xml)); +ERROR 42821: Columns of type 'DATE' cannot hold values of type 'XML'.=20 +ij> insert into t0 (ti) values (cast (null as xml)); +ERROR 42821: Columns of type 'TIME' cannot hold values of type 'XML'.=20 +ij> insert into t0 (ts) values (cast (null as xml)); +ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'XML'.=20 +ij> insert into t0 (cl) values (cast (null as xml)); +ERROR 42821: Columns of type 'CLOB' cannot hold values of type 'XML'.=20 +ij> insert into t0 (bl) values (cast (null as xml)); +ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'XML'.=20 +ij> -- No casting is allowed. +insert into t1 values (1, cast ('hmm' as xml)); +ERROR 42846: Cannot convert types 'CHAR' to 'XML'. +ij> insert into t1 values (1, cast (2 as xml)); +ERROR 42846: Cannot convert types 'INTEGER' to 'XML'. +ij> insert into t1 values (1, cast (123.456 as xml)); +ERROR 42846: Cannot convert types 'DECIMAL' to 'XML'. +ij> insert into t1 values (1, cast (x'01' as xml)); +ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'XML'. +ij> insert into t1 values (1, cast (x'ab' as xml)); +ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'XML'. +ij> insert into t1 values (1, cast (current date as xml)); +ERROR 42846: Cannot convert types 'DATE' to 'XML'. +ij> insert into t1 values (1, cast (current time as xml)); +ERROR 42846: Cannot convert types 'TIME' to 'XML'. +ij> insert into t1 values (1, cast (current timestamp as xml)); +ERROR 42846: Cannot convert types 'TIMESTAMP' to 'XML'. +ij> insert into t1 values (1, cast (('hmm' | | 'andstuff') as xml)); +ERROR 42846: Cannot convert types 'CHAR' to 'XML'. +ij> -- XML can't be used in non-XML operations. +select i + x from t1; +ERROR 42Y95: The '+' operator with a left operand type of 'INTEGER' and a = right operand type of 'XML' is not supported. +ij> select i * x from t1; +ERROR 42Y95: The '*' operator with a left operand type of 'INTEGER' and a = right operand type of 'XML' is not supported. +ij> select i / x from t1; +ERROR 42Y95: The '/' operator with a left operand type of 'INTEGER' and a = right operand type of 'XML' is not supported. +ij> select i - x from t1; +ERROR 42Y95: The '-' operator with a left operand type of 'INTEGER' and a = right operand type of 'XML' is not supported. +ij> select -x from t1; +ERROR 42X37: The unary '-' operator is not allowed on the 'XML' type. +ij> select 'hi' | | x from t1; +ERROR 42846: Cannot convert types 'XML' to 'VARCHAR'. +ij> select substr(x, 0) from t1; +ERROR 42X25: The 'SUBSTR' function is not allowed on the 'XML' type. +ij> select i from t1 where x like 'hmm'; +ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having = compatible arguments was found. +ij> select max(x) from t1; +ERROR 42Y22: Aggregate MAX cannot operate on type XML. +ij> select min(x) from t1; +ERROR 42Y22: Aggregate MIN cannot operate on type XML. +ij> select length(x) from t1; +ERROR 42X25: The 'length' function is not allowed on the 'XML' type. +ij> -- Comparsions against XML don't work. +select i from t1 where x =3D 'hmm'; +ERROR 42818: Comparisons between 'XML' and 'CHAR' are not supported. +ij> select i from t1 where x > 0; +ERROR 42818: Comparisons between 'XML' and 'INTEGER' are not supported. +ij> select i from t1 where x > x; +ERROR 42818: Comparisons between 'XML' and 'XML' are not supported. +ij> select i from t1 where x > 'some char'; +ERROR 42818: Comparisons between 'XML' and 'CHAR' are not supported. +ij> -- Indexing/ordering on XML cols is not allowed. +create index oops_ix on t1(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 compa= risons 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 compa= risons are not supported for that type. +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 s= et x =3D 'hmm'; +ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'.=20 +ij> create trigger tr1 after insert on t1 for each row mode db2sql update = t1 set x =3D null; +0 rows inserted/updated/deleted +ij> drop trigger tr1; +0 rows inserted/updated/deleted +ij> -- Test XMLPARSE operator. +----- These should fail. +insert into t1 values (1, xmlparse(document '' strip whitespace)); +ERROR X0X18: XML feature not supported: 'STRIP WHITESPACE'. +ij> insert into t1 values (1, xmlparse(document '')); +ERROR X0X16: XML syntax error; missing keyword(s): 'PRESERVE WHITESPACE'. +ij> insert into t1 values (1, xmlparse('' preserve whitespace)); +ERROR X0X16: XML syntax error; missing keyword(s): 'DOCUMENT'. +ij> insert into t1 values (1, xmlparse(content '' preserve whitespac= e)); +ERROR X0X18: XML feature not supported: 'CONTENT'. +ij> select xmlparse(document xmlparse(document '' preserve whitespa= ce) preserve whitespace) from t1; +ERROR 42X25: The 'XMLParse' function is not allowed on the 'XML' type. +ij> select i from t1 where xmlparse(document '' preserve whitespace= ); +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 whitespa= ce)); +ERROR 2200L: XMLPARSE operand is not an XML document; see next exception f= or details. SQLSTATE: XJ001: Java exception: 'XML document structures must = start and end within the same entity.: org.xml.sax.SAXParseException'. +ij> -- These should work. +insert into t1 values (5, xmlparse(document '' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t1 values (6, xmlparse(document ' bass boosted. ' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t2 values (1, xmlparse(document ' work as planned = ' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t5 (x1, x2) values (null, xmlparse(document '' p= reserve whitespace)); +1 row inserted/updated/deleted +ij> update t1 set x =3D xmlparse(document ' document was inserted = as part of an UPDATE ' preserve whitespace) where i =3D 1; +1 row inserted/updated/deleted +ij> update t1 set x =3D xmlparse(document ' document was inserted= as part of an UPDATE ' preserve whitespace) where xmlexists('/up= date' passing by value x); +1 row inserted/updated/deleted +ij> select i from t1 where xmlparse(document '' preserve whitespace= ) is not null; +I =20 +----- +1 =20 +2 =20 +4 =20 +3 =20 +5 =20 +6 =20 +ij> select i from t1 where xmlparse(document '' preserve whitespace= ) is not null order by i; +I =20 +----- +1 =20 +2 =20 +3 =20 +4 =20 +5 =20 +6 =20 +ij> -- "is [not] null" should work with XML. +select i from t1 where x is not null; +I =20 +----- +1 =20 +5 =20 +6 =20 +ij> select i from t1 where x is null; +I =20 +----- +2 =20 +4 =20 +3 =20 +ij> -- XML columns can't be returned in a top-level result set. +select x from t1; +ERROR X0X15: XML values are not allowed in top-level result sets; try usin= g XMLSERIALIZE. +ij> select * from t1; +ERROR X0X15: XML values are not allowed in top-level result sets; try usin= g XMLSERIALIZE. +ij> select xmlparse(document vc preserve whitespace) from t4; +ERROR X0X15: XML values are not allowed in top-level result sets; try usin= g XMLSERIALIZE. +ij> values xmlparse(document '' preserve whitespace); +ERROR X0X15: XML values are not allowed in top-level result sets; try usin= g XMLSERIALIZE. +ij> values xmlparse(document '' preserve whitespace); +ERROR X0X15: XML values are not allowed in top-level result sets; try usin= g XMLSERIALIZE. +ij> -- Test XMLSERIALIZE operator. +insert into t4 values (''); +1 row inserted/updated/deleted +ij> insert into t4 values 'no good'; +1 row inserted/updated/deleted +ij> -- These should fail. +select xmlserialize(x) from t1; +ERROR X0X16: XML syntax error; missing keyword(s): 'AS '. +ij> select xmlserialize(x as) from t1; +ERROR X0X16: XML syntax error; missing keyword(s): 'AS '. +ij> select xmlserialize(x as int) from t1; +ERROR X0X17: Invalid target type for XMLSERIALIZE: 'INTEGER'. +ij> select xmlserialize(x as varchar(20) for bit data) from t1; +ERROR X0X17: Invalid target type for XMLSERIALIZE: 'VARCHAR () FOR BIT DAT= A'. +ij> select xmlserialize(y as char(10)) from t1; +ERROR 42X04: Column 'Y' is either not in any table in the FROM list or app= ears within a join specification and is outside the scope of the join speci= fication or appears in a HAVING clause and is not in the GROUP BY list. If = this is a CREATE or ALTER TABLE statement then 'Y' is not a column in the = target table. +ij> select xmlserialize(xmlserialize(x as clob) as clob) from t1; +ERROR 42X25: The 'XMLSerialize' function is not allowed on the 'CLOB' type. +ij> values xmlserialize(' dokie ' as clob); +ERROR 42X25: The 'XMLSerialize' function is not allowed on the 'CHAR' type. +ij> -- These should succeed. +select xmlserialize(x as clob) from t1; +1 = =20 +----- + document was inserted as part of an UPDATE = =20 +NULL = =20 +NULL = =20 +NULL = =20 + = =20 + bass boosted. = =20 +ij> select xmlserialize(x1 as clob), xmlserialize(x2 as clob) from t5; +1 |2 = =20 +----- +NULL | = =20 +ij> select xmlserialize(x as char(100)) from t1; +1 = =20 +----- + document was inserted as part of an UPDATE = =20 +NULL = =20 +NULL = =20 +NULL = =20 + = =20 + bass boosted. = =20 +ij> select xmlserialize(x as varchar(300)) from t1; +1 = =20 +----- + document was inserted as part of an UPDATE = =20 +NULL = =20 +NULL = =20 +NULL = =20 + = =20 + bass boosted. = =20 +ij> -- These should succeed at the XMLEXISTS level, but fail with +----- parse/truncation errors. +select xmlserialize(xmlparse(document vc preserve whitespace) as char(10))= from t4; +1 =20 +----- +ERROR 2200L: XMLPARSE operand is not an XML document; see next exception f= or details. SQLSTATE: XJ001: Java exception: 'Content is not allowed in pro= log.: org.xml.sax.SAXParseException'. +ij> select xmlserialize(x as char) from t1; +1 =20 +----- +ERROR 22001: A truncation error was encountered trying to shrink CHAR ' document was inserted as part of an UPDATE select xmlserialize(x as clob(10)) from t1; +1 =20 +----- +ERROR 22001: A truncation error was encountered trying to shrink CLOB ' document was inserted as part of an UPDATE select xmlserialize(x as char(1)) from t1; +1 =20 +----- +ERROR 22001: A truncation error was encountered trying to shrink CHAR ' document was inserted as part of an UPDATE select length(xmlserialize(x as char(1))) from t1; +1 =20 +----- +ERROR 22001: A truncation error was encountered trying to shrink CHAR ' document was inserted as part of an UPDATE select xmlserialize(x as varchar(1)) from t1; +1 =20 +----- +ERROR 22001: A truncation error was encountered trying to shrink VARCHAR '= document was inserted as part of an UPDATE select length(xmlserialize(x as varchar(1))) from t1; +1 =20 +----- +ERROR 22001: A truncation error was encountered trying to shrink VARCHAR '= document was inserted as part of an UPDATE -- These checks verify that the XMLSERIALIZE result is the correct +----- type (the type is indicated as part of the error message). +create table it (i int); +0 rows inserted/updated/deleted +ij> insert into it values (select xmlserialize(x as varchar(10)) from t1); +ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'VARCHAR= '=2E=20 +ij> insert into it values (select xmlserialize(x as char(10)) from t1); +ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'CHAR'.=20 +ij> insert into it values (select xmlserialize(x as clob(10)) from t1); +ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'CLOB'.=20 +ij> -- Test XMLPARSE/XMLSERIALIZE combinations. +----- These should fail. +select xmlserialize(xmlparse(document '' preserve whitespace) as clob= ) from t2; +1 = =20 +----- +ERROR 2200L: XMLPARSE operand is not an XML document; see next exception f= or details. SQLSTATE: XJ001: Java exception: 'XML document structures must = start and end within the same entity.: org.xml.sax.SAXParseException'. +ij> select xmlserialize(xmlparse(document x preserve whitespace) as char(1= 00)) from t1; +ERROR 42X25: The 'XMLParse' function is not allowed on the 'XML' type. +ij> -- These should succeed. +select xmlserialize(xmlparse(document '' preserve whitespace) as clo= b) from t2; +1 = =20 +----- + = =20 +ij> select xmlserialize(xmlparse(document xmlserialize(x as clob) preserve= whitespace) as clob) from t1; +1 = =20 +----- + document was inserted as part of an UPDATE = =20 +NULL = =20 +NULL = =20 +NULL = =20 + = =20 + bass boosted. = =20 +ij> values xmlserialize(xmlparse(document ' dokie ' preserve = whitespace) as clob); +1 = =20 +----- + dokie = =20 +ij> select i from t1 where xmlparse(document xmlserialize(x as clob) prese= rve whitespace) is not null order by i; +I =20 +----- +1 =20 +5 =20 +6 =20 +ij> -- Test XMLEXISTS operator. +insert into t1 values (7, xmlparse(document ' this out = ' preserve whitespace)); +1 row inserted/updated/deleted +ij> create table t7 (i int, x1 xml, x2 xml not null); +0 rows inserted/updated/deleted +ij> insert into t7 values (1, null, xmlparse(document '' preserve whi= tespace)); +1 row inserted/updated/deleted +ij> -- These should fail. +select i from t1 where xmlexists(x); +ERROR 42X01: Syntax error: Encountered ")" at line 1, column 35. +ij> select i from t1 where xmlexists(i); +ERROR 42X01: Syntax error: Encountered ")" at line 1, column 35. +ij> select i from t1 where xmlexists('//*'); +ERROR 42X01: Syntax error: Encountered ")" at line 1, column 39. +ij> select i from t1 where xmlexists('//*' x); +ERROR 42X01: Syntax error: Encountered "x" at line 1, column 40. +ij> select i from t1 where xmlexists('//*' passing x); +ERROR 42X01: Syntax error: Encountered "x" at line 1, column 48. +ij> select i from t1 where xmlexists('//*' passing by ref x); +ERROR X0X18: XML feature not supported: 'BY REF'. +ij> select i from t1 where xmlexists('//*' passing by value i); +ERROR 42Y95: The 'XMLExists' operator with a left operand type of 'CHAR' a= nd a right operand type of 'INTEGER' is not supported. +ij> -- These should succeed. +select i from t1 where xmlexists('//*' passing by value x); +I =20 +----- +1 =20 +5 =20 +6 =20 +7 =20 +ij> select i from t1 where xmlexists('//person' passing by value x); +I =20 +----- +ij> select i from t1 where xmlexists('//lets' passing by value x); +I =20 +----- +7 =20 +ij> select xmlexists('//lets' passing by value x) from t1; +1 =20 +----- +0 =20 +NULL =20 +NULL =20 +NULL =20 +0 =20 +0 =20 +1 =20 +ij> select xmlexists('//try[text()=3D'' this out '']' passing by value x) = from t1; +1 =20 +----- +0 =20 +NULL =20 +NULL =20 +NULL =20 +0 =20 +0 =20 +1 =20 +ij> select xmlexists('//let' passing by value x) from t1; +1 =20 +----- +0 =20 +NULL =20 +NULL =20 +NULL =20 +0 =20 +0 =20 +0 =20 +ij> select xmlexists('//try[text()=3D'' this in '']' passing by value x) f= rom t1; +1 =20 +----- +0 =20 +NULL =20 +NULL =20 +NULL =20 +0 =20 +0 =20 +0 =20 +ij> select i, xmlexists('//let' passing by value x) from t1; +I |2 =20 +----- +1 |0 =20 +2 |NULL =20 +4 |NULL =20 +3 |NULL =20 +5 |0 =20 +6 |0 =20 +7 |0 =20 +ij> select i, xmlexists('//lets' passing by value x) from t1; +I |2 =20 +----- +1 |0 =20 +2 |NULL =20 +4 |NULL =20 +3 |NULL =20 +5 |0 =20 +6 |0 =20 +7 |1 =20 +ij> values xmlexists('//let' passing by value xmlparse(document ' tr= y this ' preserve whitespace)); +1 =20 +----- +0 =20 +ij> values xmlexists('//lets' passing by value xmlparse(document ' t= ry this ' preserve whitespace)); +1 =20 +----- +1 =20 +ij> select xmlserialize(x1 as clob) from t5 where xmlexists('//*' passing = by value x1); +1 = =20 +----- +ij> select xmlserialize(x2 as clob) from t5 where xmlexists('//*' passing = by value x2); +1 = =20 +----- + = =20 +ij> select xmlserialize(x1 as clob), xmlexists('//*' passing by value xmlp= arse(document '' preserve whitespace)) from t5; +1 |2 =20 +----- +NULL |1 =20 +ij> select xmlserialize(x1 as clob), xmlexists('//goodboy' passing by valu= e xmlparse(document '' preserve whitespace)) from t5; +1 |2 =20 +----- +NULL |0 =20 +ij> select i, xmlserialize(x1 as char(10)), xmlserialize (x2 as char(10)) = from t7; +I |2 |3 =20 +----- +1 |NULL | =20 +ij> select i from t7 where xmlexists('/ok' passing by value x1) and xmlexi= sts('/ok' passing by value x2); +I =20 +----- +ij> select i from t7 where xmlexists('/ok' passing by value x1) or xmlexis= ts('/ok' passing by value x2); +I =20 +----- +1 =20 +ij> -- XMLEXISTS can be used wherever a boolean function is allowed, +----- for ex, a check constraint... +create table t6 (i int, x xml check (xmlexists('//should' passing by value= x))); +0 rows inserted/updated/deleted +ij> insert into t6 values (1, xmlparse(document '' preserve white= space)); +1 row inserted/updated/deleted +ij> insert into t6 values (1, xmlparse(document '' preserve whi= tespace)); +ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated whil= e performing an INSERT or UPDATE on table 'APP.T6'. +ij> select xmlserialize(x as char(20)) from t6; +1 =20 +----- + =20 +ij> -- Do some namespace queries/examples. +create table t8 (i int, x xml); +0 rows inserted/updated/deleted +ij> insert into t8 values (1, xmlparse(document '' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t8 values (2, xmlparse(document '' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t8 values (3, xmlparse(document '' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t8 values (4, xmlparse(document '' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t8 values (5, xmlparse(document '' preserve whitespac= e)); +1 row inserted/updated/deleted +ij> select xmlexists('//child::*[name()=3D"none"]' passing by value x) fro= m t8; +1 =20 +----- +0 =20 +0 =20 +0 =20 +0 =20 +0 =20 +ij> select xmlexists('//child::*[name()=3D''hi'']' passing by value x) fro= m t8; +1 =20 +----- +0 =20 +0 =20 +0 =20 +0 =20 +1 =20 +ij> select xmlexists('//child::*[local-name()=3D''hi'']' passing by value = x) from t8; +1 =20 +----- +1 =20 +1 =20 +0 =20 +0 =20 +1 =20 +ij> select xmlexists('//child::*[local-name()=3D''bye'']' passing by value= x) from t8; +1 =20 +----- +0 =20 +0 =20 +1 =20 +1 =20 +0 =20 +ij> select xmlexists('//*[namespace::*[string()=3D''http://www.hi.there'']= ]' passing by value x) from t8; +1 =20 +----- +1 =20 +1 =20 +0 =20 +1 =20 +0 =20 +ij> select xmlexists('//*[namespace::*[string()=3D''http://www.good.bye'']= ]' passing by value x) from t8; +1 =20 +----- +0 =20 +0 =20 +1 =20 +0 =20 +0 =20 +ij> select xmlexists('//child::*[local-name()=3D''hi'' and namespace::*[st= ring()=3D''http://www.hi.there'']]' passing by value x) from t8; +1 =20 +----- +1 =20 +1 =20 +0 =20 +0 =20 +0 =20 +ij> select xmlexists('//child::*[local-name()=3D''bye'' and namespace::*[s= tring()=3D''http://www.good.bye'']]' passing by value x) from t8; +1 =20 +----- +0 =20 +0 =20 +1 =20 +0 =20 +0 =20 +ij> select xmlexists('//child::*[local-name()=3D''bye'' and namespace::*[s= tring()=3D''http://www.hi.there'']]' passing by value x) from t8; +1 =20 +----- +0 =20 +0 =20 +0 =20 +1 =20 +0 =20 +ij> -- clean up. +drop table t0; +0 rows inserted/updated/deleted +ij> drop table t1; +0 rows inserted/updated/deleted +ij> drop table t2; +0 rows inserted/updated/deleted +ij> drop table t3; +0 rows inserted/updated/deleted +ij> drop table t4; +0 rows inserted/updated/deleted +ij> drop table t5; +0 rows inserted/updated/deleted +ij> drop table t6; +0 rows inserted/updated/deleted +ij> drop table t7; +0 rows inserted/updated/deleted +ij> drop table t8; +0 rows inserted/updated/deleted +ij>=20 Propchange: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting= /functionTests/master/DerbyNetClient/xml_general.out ---------------------------------------------------------------------------= --- svn:eol-style =3D native Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/func= tionTests/master/xmlBinding.out URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/= org/apache/derbyTesting/functionTests/master/xmlBinding.out?rev=3D189721&vi= ew=3Dauto =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/xmlBinding.out (added) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/xmlBinding.out Wed Jun 8 23:48:34 2005 @@ -0,0 +1,48 @@ +[ Beginning XML binding tests. ] +XML column -- insertion via parameter: PASS -- caught expected error X0X1= 4=2E +Trying to bind to XML in XMLSERIALIZE: PASS -- caught expected error X0X14. +Trying to bind to XML in XMLEXISTS: PASS -- caught expected error X0X14. +XML value in result set: PASS -- caught expected error X0X15. +[ End XML binding tests. ] +[ Beginning XMLPARSE tests. ] +Test insertions from file:=20 +Inserted roughly 40k of data. +Inserted roughly 40k of data. +Inserted roughly 1k of data. +Inserted roughly 1k of data. +Inserted roughly 1k of data. +Inserted roughly 1k of data. +--> Insertions all PASS. +Binding string in XMLPARSE: PASS -- Completed without exception, as expect= ed. +Binding Java null string in XMLPARSE: PASS -- Completed without exception,= as expected. +Binding SQL NULL string in XMLPARSE: PASS -- Completed without exception, = as expected. +[ End XMLPARSE tests. ] +[ Beginning XMLSERIALIZE tests. ] +1, [ roughly 40k ] +2, [ roughly 40k ] +3, [ roughly 1k ] +4, [ roughly 1k ] +5, [ roughly 1k ] +6, [ roughly 1k ] +7, [ roughly 0k ] +8, NULL +9, NULL +[ End XMLSERIALIZE tests. ] +[ Begin XMLEXISTS tests. ] +Running XMLEXISTS with: //abb +--> Matching rows: 1 +Running XMLEXISTS with: //d50 +--> Matching rows: 1 +Running XMLEXISTS with: //person/email +--> Matching rows: 4 +Running XMLEXISTS with: /personnel +--> Matching rows: 5 +Running XMLEXISTS with: //person/@id +--> Matching rows: 4 +Running XMLEXISTS with: //person/@noteTwo +--> Matching rows: 1 +Binding string in XMLEXISTS: PASS -- Completed without exception, as expec= ted. +Binding Java null string in XMLEXISTS: PASS -- Completed without exception= , as expected. +Binding SQL NULL string in XMLEXISTS: PASS -- Completed without exception,= as expected. +[ End XMLEXISTS tests. ] +[ Done. ] Propchange: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting= /functionTests/master/xmlBinding.out ---------------------------------------------------------------------------= --- svn:eol-style =3D native Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/func= tionTests/master/xml_general.out URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/= org/apache/derbyTesting/functionTests/master/xml_general.out?rev=3D189721&v= iew=3Dauto =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/xml_general.out (added) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/xml_general.out Wed Jun 8 23:48:34 2005 @@ -0,0 +1,586 @@ +ij> create table t0 (si smallint, i int, bi bigint, vcb varchar (32) for b= it data, nu numeric(10,2), f float, d double, vc varchar(20), da date, ti t= ime, ts timestamp, cl clob, bl blob); +0 rows inserted/updated/deleted +ij> -- XML column declarations should work like other built-in types. +create table t1 (i int, x xml); +0 rows inserted/updated/deleted +ij> create table t2 (i int, x xml not null); +0 rows inserted/updated/deleted +ij> create table t3 (i int, x xml default null); +0 rows inserted/updated/deleted +ij> create table t4 (vc varchar(100)); +0 rows inserted/updated/deleted +ij> create table t5 (x2 xml not null); +0 rows inserted/updated/deleted +ij> alter table t5 add column x1 xml; +0 rows inserted/updated/deleted +ij> -- Check insertion of null XML values. +-- Next four should work. +insert into t1 values (1, null); +1 row inserted/updated/deleted +ij> insert into t1 values (2, cast (null as xml)); +1 row inserted/updated/deleted +ij> insert into t1 (i) values (4); +1 row inserted/updated/deleted +ij> insert into t1 values (3, default); +1 row inserted/updated/deleted +ij> -- Next two should fail. +insert into t2 values (1, null); +ERROR 23502: Column 'X' cannot accept a NULL value. +ij> insert into t2 values (2, cast (null as xml)); +ERROR 23502: Column 'X' cannot accept a NULL value. +ij> -- XML cols can't hold non-XML types. +insert into t1 values (3, 'hmm'); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'.=20 +ij> insert into t1 values (1, 2); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'INTEGER'.=20 +ij> insert into t1 values (1, 123.456); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'DECIMAL'.=20 +ij> insert into t1 values (1, x'01'); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR () FOR= BIT DATA'.=20 +ij> insert into t1 values (1, x'ab'); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR () FOR= BIT DATA'.=20 +ij> insert into t1 values (1, current date); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'DATE'.=20 +ij> insert into t1 values (1, current time); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'TIME'.=20 +ij> insert into t1 values (1, current timestamp); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'TIMESTAMP'.=20 +ij> insert into t1 values (1, ('hmm' || 'andstuff')); +ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'.=20 +ij> -- XML can't be stored in non-XML cols. +insert into t0 (si) values (cast (null as xml)); +ERROR 42821: Columns of type 'SMALLINT' cannot hold values of type 'XML'.=20 +ij> insert into t0 (i) values (cast (null as xml)); +ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'XML'.=20 +ij> insert into t0 (bi) values (cast (null as xml)); +ERROR 42821: Columns of type 'BIGINT' cannot hold values of type 'XML'.=20 +ij> insert into t0 (vcb) values (cast (null as xml)); +ERROR 42821: Columns of type 'VARCHAR () FOR BIT DATA' cannot hold values = of type 'XML'.=20 +ij> insert into t0 (nu) values (cast (null as xml)); +ERROR 42821: Columns of type 'NUMERIC' cannot hold values of type 'XML'.=20 +ij> insert into t0 (f) values (cast (null as xml)); +ERROR 42821: Columns of type 'DOUBLE' cannot hold values of type 'XML'.=20 +ij> insert into t0 (d) values (cast (null as xml)); +ERROR 42821: Columns of type 'DOUBLE' cannot hold values of type 'XML'.=20 +ij> insert into t0 (vc) values (cast (null as xml)); +ERROR 42821: Columns of type 'VARCHAR' cannot hold values of type 'XML'.=20 +ij> insert into t0 (da) values (cast (null as xml)); +ERROR 42821: Columns of type 'DATE' cannot hold values of type 'XML'.=20 +ij> insert into t0 (ti) values (cast (null as xml)); +ERROR 42821: Columns of type 'TIME' cannot hold values of type 'XML'.=20 +ij> insert into t0 (ts) values (cast (null as xml)); +ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'XML'.=20 +ij> insert into t0 (cl) values (cast (null as xml)); +ERROR 42821: Columns of type 'CLOB' cannot hold values of type 'XML'.=20 +ij> insert into t0 (bl) values (cast (null as xml)); +ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'XML'.=20 +ij> -- No casting is allowed. +insert into t1 values (1, cast ('hmm' as xml)); +ERROR 42846: Cannot convert types 'CHAR' to 'XML'. +ij> insert into t1 values (1, cast (2 as xml)); +ERROR 42846: Cannot convert types 'INTEGER' to 'XML'. +ij> insert into t1 values (1, cast (123.456 as xml)); +ERROR 42846: Cannot convert types 'DECIMAL' to 'XML'. +ij> insert into t1 values (1, cast (x'01' as xml)); +ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'XML'. +ij> insert into t1 values (1, cast (x'ab' as xml)); +ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'XML'. +ij> insert into t1 values (1, cast (current date as xml)); +ERROR 42846: Cannot convert types 'DATE' to 'XML'. +ij> insert into t1 values (1, cast (current time as xml)); +ERROR 42846: Cannot convert types 'TIME' to 'XML'. +ij> insert into t1 values (1, cast (current timestamp as xml)); +ERROR 42846: Cannot convert types 'TIMESTAMP' to 'XML'. +ij> insert into t1 values (1, cast (('hmm' || 'andstuff') as xml)); +ERROR 42846: Cannot convert types 'CHAR' to 'XML'. +ij> -- XML can't be used in non-XML operations. +select i + x from t1; +ERROR 42Y95: The '+' operator with a left operand type of 'INTEGER' and a = right operand type of 'XML' is not supported. +ij> select i * x from t1; +ERROR 42Y95: The '*' operator with a left operand type of 'INTEGER' and a = right operand type of 'XML' is not supported. +ij> select i / x from t1; +ERROR 42Y95: The '/' operator with a left operand type of 'INTEGER' and a = right operand type of 'XML' is not supported. +ij> select i - x from t1; +ERROR 42Y95: The '-' operator with a left operand type of 'INTEGER' and a = right operand type of 'XML' is not supported. +ij> select -x from t1; +ERROR 42X37: The unary '-' operator is not allowed on the 'XML' type. +ij> select 'hi' || x from t1; +ERROR 42846: Cannot convert types 'XML' to 'VARCHAR'. +ij> select substr(x, 0) from t1; +ERROR 42X25: The 'SUBSTR' function is not allowed on the 'XML' type. +ij> select i from t1 where x like 'hmm'; +ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having = compatible arguments was found. +ij> select max(x) from t1; +ERROR 42Y22: Aggregate MAX cannot operate on type XML. +ij> select min(x) from t1; +ERROR 42Y22: Aggregate MIN cannot operate on type XML. +ij> select length(x) from t1; +ERROR 42X25: The 'length' function is not allowed on the 'XML' type. +ij> -- Comparsions against XML don't work. +select i from t1 where x =3D 'hmm'; +ERROR 42818: Comparisons between 'XML' and 'CHAR' are not supported. +ij> select i from t1 where x > 0; +ERROR 42818: Comparisons between 'XML' and 'INTEGER' are not supported. +ij> select i from t1 where x > x; +ERROR 42818: Comparisons between 'XML' and 'XML' are not supported. +ij> select i from t1 where x > 'some char'; +ERROR 42818: Comparisons between 'XML' and 'CHAR' are not supported. +ij> -- Indexing/ordering on XML cols is not allowed. +create index oops_ix on t1(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 compa= risons 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 compa= risons are not supported for that type. +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 s= et x =3D 'hmm'; +ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'.=20 +ij> create trigger tr1 after insert on t1 for each row mode db2sql update = t1 set x =3D null; +0 rows inserted/updated/deleted +ij> drop trigger tr1; +0 rows inserted/updated/deleted +ij> -- Test XMLPARSE operator. +-- These should fail. +insert into t1 values (1, xmlparse(document '' strip whitespace)); +ERROR X0X18: XML feature not supported: 'STRIP WHITESPACE'. +ij> insert into t1 values (1, xmlparse(document '')); +ERROR X0X16: XML syntax error; missing keyword(s): 'PRESERVE WHITESPACE'. +ij> insert into t1 values (1, xmlparse('' preserve whitespace)); +ERROR X0X16: XML syntax error; missing keyword(s): 'DOCUMENT'. +ij> insert into t1 values (1, xmlparse(content '' preserve whitespac= e)); +ERROR X0X18: XML feature not supported: 'CONTENT'. +ij> select xmlparse(document xmlparse(document '' preserve whitespa= ce) preserve whitespace) from t1; +ERROR 42X25: The 'XMLParse' function is not allowed on the 'XML' type. +ij> select i from t1 where xmlparse(document '' preserve whitespace= ); +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 whitespa= ce)); +ERROR 2200L: XMLPARSE operand is not an XML document; see next exception f= or details. +ERROR XJ001: Java exception: 'XML document structures must start and end w= ithin the same entity.: org.xml.sax.SAXParseException'. +ij> -- These should work. +insert into t1 values (5, xmlparse(document '' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t1 values (6, xmlparse(document ' bass boosted. ' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t2 values (1, xmlparse(document ' work as planned = ' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t5 (x1, x2) values (null, xmlparse(document '' p= reserve whitespace)); +1 row inserted/updated/deleted +ij> update t1 set x =3D xmlparse(document ' document was inserted = as part of an UPDATE ' preserve whitespace) where i =3D 1; +1 row inserted/updated/deleted +ij> update t1 set x =3D xmlparse(document ' document was inserted= as part of an UPDATE ' preserve whitespace) where xmlexists('/up= date' passing by value x); +1 row inserted/updated/deleted +ij> select i from t1 where xmlparse(document '' preserve whitespace= ) is not null; +I =20 +----------- +1 =20 +2 =20 +4 =20 +3 =20 +5 =20 +6 =20 +ij> select i from t1 where xmlparse(document '' preserve whitespace= ) is not null order by i; +I =20 +----------- +1 =20 +2 =20 +3 =20 +4 =20 +5 =20 +6 =20 +ij> -- "is [not] null" should work with XML. +select i from t1 where x is not null; +I =20 +----------- +1 =20 +5 =20 +6 =20 +ij> select i from t1 where x is null; +I =20 +----------- +2 =20 +4 =20 +3 =20 +ij> -- XML columns can't be returned in a top-level result set. +select x from t1; +ERROR X0X15: XML values are not allowed in top-level result sets; try usin= g XMLSERIALIZE. +ij> select * from t1; +ERROR X0X15: XML values are not allowed in top-level result sets; try usin= g XMLSERIALIZE. +ij> select xmlparse(document vc preserve whitespace) from t4; +ERROR X0X15: XML values are not allowed in top-level result sets; try usin= g XMLSERIALIZE. +ij> values xmlparse(document '' preserve whitespace); +ERROR X0X15: XML values are not allowed in top-level result sets; try usin= g XMLSERIALIZE. +ij> values xmlparse(document '' preserve whitespace); +ERROR X0X15: XML values are not allowed in top-level result sets; try usin= g XMLSERIALIZE. +ij> -- Test XMLSERIALIZE operator. +insert into t4 values (''); +1 row inserted/updated/deleted +ij> insert into t4 values 'no good'; +1 row inserted/updated/deleted +ij> -- These should fail. +select xmlserialize(x) from t1; +ERROR X0X16: XML syntax error; missing keyword(s): 'AS '. +ij> select xmlserialize(x as) from t1; +ERROR X0X16: XML syntax error; missing keyword(s): 'AS '. +ij> select xmlserialize(x as int) from t1; +ERROR X0X17: Invalid target type for XMLSERIALIZE: 'INTEGER'. +ij> select xmlserialize(x as varchar(20) for bit data) from t1; +ERROR X0X17: Invalid target type for XMLSERIALIZE: 'VARCHAR () FOR BIT DAT= A'. +ij> select xmlserialize(y as char(10)) from t1; +ERROR 42X04: Column 'Y' is either not in any table in the FROM list or app= ears within a join specification and is outside the scope of the join speci= fication or appears in a HAVING clause and is not in the GROUP BY list. If = this is a CREATE or ALTER TABLE statement then 'Y' is not a column in the = target table. +ij> select xmlserialize(xmlserialize(x as clob) as clob) from t1; +ERROR 42X25: The 'XMLSerialize' function is not allowed on the 'CLOB' type. +ij> values xmlserialize(' dokie ' as clob); +ERROR 42X25: The 'XMLSerialize' function is not allowed on the 'CHAR' type. +ij> -- These should succeed. +select xmlserialize(x as clob) from t1; +1 = =20 +--------------------------------------------------------------------------= ------------------------------------------------------ + document was inserted as part of an UPDATE = =20 +NULL = =20 +NULL = =20 +NULL = =20 + = =20 + bass boosted. = =20 +ij> select xmlserialize(x1 as clob), xmlserialize(x2 as clob) from t5; +1 = |2 = = =20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------- +NULL = | = = =20 +ij> select xmlserialize(x as char(100)) from t1; +1 = =20 +--------------------------------------------------------------------------= -------------------------- + document was inserted as part of an UPDATE = =20 +NULL = =20 +NULL = =20 +NULL = =20 + = =20 + bass boosted. = =20 +ij> select xmlserialize(x as varchar(300)) from t1; +1 = =20 +--------------------------------------------------------------------------= ------------------------------------------------------ + document was inserted as part of an UPDATE = =20 +NULL = =20 +NULL = =20 +NULL = =20 + = =20 + bass boosted. = =20 +ij> -- These should succeed at the XMLEXISTS level, but fail with +-- parse/truncation errors. +select xmlserialize(xmlparse(document vc preserve whitespace) as char(10))= from t4; +1 =20 +---------- + =20 +ERROR 2200L: XMLPARSE operand is not an XML document; see next exception f= or details. +ERROR XJ001: Java exception: 'Content is not allowed in prolog.: org.xml.s= ax.SAXParseException'. +ij> select xmlserialize(x as char) from t1; +1 =20 +---- +ERROR 22001: A truncation error was encountered trying to shrink CHAR ' document was inserted as part of an UPDATE select xmlserialize(x as clob(10)) from t1; +1 =20 +---------- +ERROR 22001: A truncation error was encountered trying to shrink CLOB ' document was inserted as part of an UPDATE select xmlserialize(x as char(1)) from t1; +1 =20 +---- +ERROR 22001: A truncation error was encountered trying to shrink CHAR ' document was inserted as part of an UPDATE select length(xmlserialize(x as char(1))) from t1; +1 =20 +----------- +ERROR 22001: A truncation error was encountered trying to shrink CHAR ' document was inserted as part of an UPDATE select xmlserialize(x as varchar(1)) from t1; +1 =20 +---- +ERROR 22001: A truncation error was encountered trying to shrink VARCHAR '= document was inserted as part of an UPDATE select length(xmlserialize(x as varchar(1))) from t1; +1 =20 +----------- +ERROR 22001: A truncation error was encountered trying to shrink VARCHAR '= document was inserted as part of an UPDATE -- These checks verify that the XMLSERIALIZE result is the correct +-- type (the type is indicated as part of the error message). +create table it (i int); +0 rows inserted/updated/deleted +ij> insert into it values (select xmlserialize(x as varchar(10)) from t1); +ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'VARCHAR= '=2E=20 +ij> insert into it values (select xmlserialize(x as char(10)) from t1); +ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'CHAR'.=20 +ij> insert into it values (select xmlserialize(x as clob(10)) from t1); +ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'CLOB'.=20 +ij> -- Test XMLPARSE/XMLSERIALIZE combinations. +-- These should fail. +select xmlserialize(xmlparse(document '' preserve whitespace) as clob= ) from t2; +1 = =20 +--------------------------------------------------------------------------= ------------------------------------------------------ +ERROR 2200L: XMLPARSE operand is not an XML document; see next exception f= or details. +ERROR XJ001: Java exception: 'XML document structures must start and end w= ithin the same entity.: org.xml.sax.SAXParseException'. +ij> select xmlserialize(xmlparse(document x preserve whitespace) as char(1= 00)) from t1; +ERROR 42X25: The 'XMLParse' function is not allowed on the 'XML' type. +ij> -- These should succeed. +select xmlserialize(xmlparse(document '' preserve whitespace) as clo= b) from t2; +1 = =20 +--------------------------------------------------------------------------= ------------------------------------------------------ + = =20 +ij> select xmlserialize(xmlparse(document xmlserialize(x as clob) preserve= whitespace) as clob) from t1; +1 = =20 +--------------------------------------------------------------------------= ------------------------------------------------------ + document was inserted as part of an UPDATE = =20 +NULL = =20 +NULL = =20 +NULL = =20 + = =20 + bass boosted. = =20 +ij> values xmlserialize(xmlparse(document ' dokie ' preserve = whitespace) as clob); +1 = =20 +--------------------------------------------------------------------------= ------------------------------------------------------ + dokie = =20 +ij> select i from t1 where xmlparse(document xmlserialize(x as clob) prese= rve whitespace) is not null order by i; +I =20 +----------- +1 =20 +5 =20 +6 =20 +ij> -- Test XMLEXISTS operator. +insert into t1 values (7, xmlparse(document ' this out = ' preserve whitespace)); +1 row inserted/updated/deleted +ij> create table t7 (i int, x1 xml, x2 xml not null); +0 rows inserted/updated/deleted +ij> insert into t7 values (1, null, xmlparse(document '' preserve whi= tespace)); +1 row inserted/updated/deleted +ij> -- These should fail. +select i from t1 where xmlexists(x); +ERROR 42X01: Syntax error: Encountered ")" at line 2, column 35. +ij> select i from t1 where xmlexists(i); +ERROR 42X01: Syntax error: Encountered ")" at line 1, column 35. +ij> select i from t1 where xmlexists('//*'); +ERROR 42X01: Syntax error: Encountered ")" at line 1, column 39. +ij> select i from t1 where xmlexists('//*' x); +ERROR 42X01: Syntax error: Encountered "x" at line 1, column 40. +ij> select i from t1 where xmlexists('//*' passing x); +ERROR 42X01: Syntax error: Encountered "x" at line 1, column 48. +ij> select i from t1 where xmlexists('//*' passing by ref x); +ERROR X0X18: XML feature not supported: 'BY REF'. +ij> select i from t1 where xmlexists('//*' passing by value i); +ERROR 42Y95: The 'XMLExists' operator with a left operand type of 'CHAR' a= nd a right operand type of 'INTEGER' is not supported. +ij> -- These should succeed. +select i from t1 where xmlexists('//*' passing by value x); +I =20 +----------- +1 =20 +5 =20 +6 =20 +7 =20 +ij> select i from t1 where xmlexists('//person' passing by value x); +I =20 +----------- +ij> select i from t1 where xmlexists('//lets' passing by value x); +I =20 +----------- +7 =20 +ij> select xmlexists('//lets' passing by value x) from t1; +1 =20 +----- +false +NULL=20 +NULL=20 +NULL=20 +false +false +true=20 +ij> select xmlexists('//try[text()=3D'' this out '']' passing by value x) = from t1; +1 =20 +----- +false +NULL=20 +NULL=20 +NULL=20 +false +false +true=20 +ij> select xmlexists('//let' passing by value x) from t1; +1 =20 +----- +false +NULL=20 +NULL=20 +NULL=20 +false +false +false +ij> select xmlexists('//try[text()=3D'' this in '']' passing by value x) f= rom t1; +1 =20 +----- +false +NULL=20 +NULL=20 +NULL=20 +false +false +false +ij> select i, xmlexists('//let' passing by value x) from t1; +I |2 =20 +----------------- +1 |false +2 |NULL=20 +4 |NULL=20 +3 |NULL=20 +5 |false +6 |false +7 |false +ij> select i, xmlexists('//lets' passing by value x) from t1; +I |2 =20 +----------------- +1 |false +2 |NULL=20 +4 |NULL=20 +3 |NULL=20 +5 |false +6 |false +7 |true=20 +ij> values xmlexists('//let' passing by value xmlparse(document ' tr= y this ' preserve whitespace)); +1 =20 +----- +false +ij> values xmlexists('//lets' passing by value xmlparse(document ' t= ry this ' preserve whitespace)); +1 =20 +----- +true=20 +ij> select xmlserialize(x1 as clob) from t5 where xmlexists('//*' passing = by value x1); +1 = =20 +--------------------------------------------------------------------------= ------------------------------------------------------ +ij> select xmlserialize(x2 as clob) from t5 where xmlexists('//*' passing = by value x2); +1 = =20 +--------------------------------------------------------------------------= ------------------------------------------------------ + = =20 +ij> select xmlserialize(x1 as clob), xmlexists('//*' passing by value xmlp= arse(document '' preserve whitespace)) from t5; +1 = |2 =20 +--------------------------------------------------------------------------= ------------------------------------------------------------ +NULL = |true=20 +ij> select xmlserialize(x1 as clob), xmlexists('//goodboy' passing by valu= e xmlparse(document '' preserve whitespace)) from t5; +1 = |2 =20 +--------------------------------------------------------------------------= ------------------------------------------------------------ +NULL = |false +ij> select i, xmlserialize(x1 as char(10)), xmlserialize (x2 as char(10)) = from t7; +I |2 |3 =20 +--------------------------------- +1 |NULL | =20 +ij> select i from t7 where xmlexists('/ok' passing by value x1) and xmlexi= sts('/ok' passing by value x2); +I =20 +----------- +ij> select i from t7 where xmlexists('/ok' passing by value x1) or xmlexis= ts('/ok' passing by value x2); +I =20 +----------- +1 =20 +ij> -- XMLEXISTS can be used wherever a boolean function is allowed, +-- for ex, a check constraint... +create table t6 (i int, x xml check (xmlexists('//should' passing by value= x))); +0 rows inserted/updated/deleted +ij> insert into t6 values (1, xmlparse(document '' preserve white= space)); +1 row inserted/updated/deleted +ij> insert into t6 values (1, xmlparse(document '' preserve whi= tespace)); +ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated whil= e performing an INSERT or UPDATE on table 'APP.T6'. +ij> select xmlserialize(x as char(20)) from t6; +1 =20 +-------------------- + =20 +ij> -- Do some namespace queries/examples. +create table t8 (i int, x xml); +0 rows inserted/updated/deleted +ij> insert into t8 values (1, xmlparse(document '' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t8 values (2, xmlparse(document '' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t8 values (3, xmlparse(document '' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t8 values (4, xmlparse(document '' preserve whitespace)); +1 row inserted/updated/deleted +ij> insert into t8 values (5, xmlparse(document '' preserve whitespac= e)); +1 row inserted/updated/deleted +ij> select xmlexists('//child::*[name()=3D"none"]' passing by value x) fro= m t8; +1 =20 +----- +false +false +false +false +false +ij> select xmlexists('//child::*[name()=3D''hi'']' passing by value x) fro= m t8; +1 =20 +----- +false +false +false +false +true=20 +ij> select xmlexists('//child::*[local-name()=3D''hi'']' passing by value = x) from t8; +1 =20 +----- +true=20 +true=20 +false +false +true=20 +ij> select xmlexists('//child::*[local-name()=3D''bye'']' passing by value= x) from t8; +1 =20 +----- +false +false +true=20 +true=20 +false +ij> select xmlexists('//*[namespace::*[string()=3D''http://www.hi.there'']= ]' passing by value x) from t8; +1 =20 +----- +true=20 +true=20 +false +true=20 +false +ij> select xmlexists('//*[namespace::*[string()=3D''http://www.good.bye'']= ]' passing by value x) from t8; +1 =20 +----- +false +false +true=20 +false +false +ij> select xmlexists('//child::*[local-name()=3D''hi'' and namespace::*[st= ring()=3D''http://www.hi.there'']]' passing by value x) from t8; +1 =20 +----- +true=20 +true=20 +false +false +false +ij> select xmlexists('//child::*[local-name()=3D''bye'' and namespace::*[s= tring()=3D''http://www.good.bye'']]' passing by value x) from t8; +1 =20 +----- +false +false +true=20 +false +false +ij> select xmlexists('//child::*[local-name()=3D''bye'' and namespace::*[s= tring()=3D''http://www.hi.there'']]' passing by value x) from t8; +1 =20 +----- +false +false +false +true=20 +false +ij> -- clean up. +drop table t0; +0 rows inserted/updated/deleted +ij> drop table t1; +0 rows inserted/updated/deleted +ij> drop table t2; +0 rows inserted/updated/deleted +ij> drop table t3; +0 rows inserted/updated/deleted +ij> drop table t4; +0 rows inserted/updated/deleted +ij> drop table t5; +0 rows inserted/updated/deleted +ij> drop table t6; +0 rows inserted/updated/deleted +ij> drop table t7; +0 rows inserted/updated/deleted +ij> drop table t8; +0 rows inserted/updated/deleted +ij>=20 Propchange: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting= /functionTests/master/xml_general.out ---------------------------------------------------------------------------= --- svn:eol-style =3D native Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/func= tionTests/tests/lang/xmlBinding.java URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/= org/apache/derbyTesting/functionTests/tests/lang/xmlBinding.java?rev=3D1897= 21&view=3Dauto =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/lang/xmlBinding.java (added) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/lang/xmlBinding.java Wed Jun 8 23:48:34 2005 @@ -0,0 +1,643 @@ +/* + + Derby - Class org.apache.derbyTesting.functionTests.tests.lang.xmlBindi= ng + + Copyright 2005 The Apache Software Foundation or its licensors, as appl= icable. + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + + */ + +package org.apache.derbyTesting.functionTests.tests.lang; + +import java.io.FileReader; +import java.io.InputStream; +import java.io.InputStreamReader; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.Statement; +import java.sql.SQLException; +import java.sql.Types; + +import org.apache.derby.tools.ij; +import org.apache.derby.tools.JDBCDisplayUtil; + +/** + * This class checks to make sure that the XML data type and + * the corresponding XML operations all work as expected + * from the JDBC side of things. In particular, this test + * verifies that 1) it is NOT possible to bind to/from an XML + * datatype (because the JDBC specification doesn't indicate + * how that should be done), and 2) the correct behavior + * occurs when null values (both Java and SQL) are bound + * into the bindable parameters for the XML operators. + * This file also checks that insertion from XML files + * via a character stream works, which is important since + * XML files can be arbitrarily long and thus stream-based + * processing is a must. + */ +public class xmlBinding +{ + /** + * Create an instance of this class and do the test. + */ + public static void main(String [] args) + { + new xmlBinding().go(args); + } + + /** + * Create a JDBC connection using the arguments passed + * in from the harness, and then run the binding + * tests. + * @param args Arguments from the harness. + */ + public void go(String [] args) + { + try { + + // use the ij utility to read the property file and + // make the initial connection. + ij.getPropertyArg(args); + Connection conn =3D ij.startJBMS(); + + // Create our test table. + Statement st =3D conn.createStatement(); + st.execute("create table xTable.t1 " + + "(i int generated always as identity, x xml)"); + + // Do the tests. + doBindTests(conn); + doXMLParseTests(conn); + doXMLSerializeTests(conn); + doXMLExistsTests(conn); + + // Clean up. + st.close(); + conn.close(); + + System.out.println("[ Done. ]\n"); + + } catch (Exception e) { + + System.out.println("Unexpected error: "); + e.printStackTrace(System.out); + + } + } + + /** + * Performs a series of binding checks to make sure + * binding to an XML value never works. + * @param conn A connection to the test database. + */ + private void doBindTests(Connection conn) + { + // Make sure that attempts to bind _to_ XML will fail. + System.out.println("\n[ Beginning XML binding tests. ]\n"); + + // Binding to an XML column. + PreparedStatement pSt =3D null; + try { + + // If we're running in embedded mode or else with + // the Derby Client, then the next line will fail + // because there is NO deferred prepare. If, however, + // we're running with JCC, the default is to defer + // the prepare until execution, so the next line will + // be fine, but the following four checks will fail. + // This difference in behavior okay--it requires two + // different masters, but ultimately it's a good way + // to check behavior in both cases. + pSt =3D conn.prepareStatement( + "insert into xTable.t1(x) values (?)"); + + System.out.print("XML column -- bind String to XML: "); + bindAndExecute(pSt, 1, Types.VARCHAR, "shouldn't work", "X0X14= ", false); + + System.out.print("XML column -- bind Java null to XML: "); + bindAndExecute(pSt, 1, Types.VARCHAR, null, "X0X14", false); + + System.out.print("XML column -- bind SQL NULL to XML: "); + bindAndExecute(pSt, 1, Types.VARCHAR, null, "X0X14", true); + + System.out.print("XML column -- bind integer to XML: "); + bindAndExecute(pSt, 1, Types.INTEGER, new Integer(8), "X0X14",= false); + + } catch (SQLException se) { + // Must be running with embedded or Derby Network Client. + System.out.print("XML column -- insertion via parameter: "); + checkException(se, "X0X14"); + } + + // Binding to an XML value in the XMLSERIALIZE operator. + // Should get compile-time error saying that=20 + // parameters aren't allowed for XML data values. + System.out.print("Trying to bind to XML in XMLSERIALIZE: "); + try { + pSt =3D conn.prepareStatement( + "select XMLSERIALIZE(? AS CLOB) FROM XTABLE.T1"); + bindAndExecute(pSt, 1, Types.VARCHAR, null, "X0X14", true); + } catch (SQLException se) { + checkException(se, "X0X14"); + } + + // Binding to an XML value in the XMLEXISTS operator. + // Should get compile-time error saying that=20 + // parameters aren't allowed for XML data values. + System.out.print("Trying to bind to XML in XMLEXISTS: "); + try { + pSt =3D conn.prepareStatement( + "select i from xTable.t1 where " + + "XMLEXISTS('//*' PASSING BY VALUE ?)"); + bindAndExecute(pSt, 1, Types.VARCHAR, null, "X0X14", true); + } catch (SQLException se) { + checkException(se, "X0X14"); + } + + // Make sure that attempts to bind _from_ XML will fail. + // We should fail at compile time, even before + // we get a chance to execute the query. + System.out.print("XML value in result set: "); + try { + pSt =3D conn.prepareStatement("select x from xTable.t1"); + pSt.execute(); + } catch (SQLException se) { + checkException(se, "X0X15"); + } + + System.out.println("\n[ End XML binding tests. ]\n"); + } + + /** + * Test insertion of documents larger than 32K (this + * will test stream processing of XML data), and + * test binding of null values in the XMLPARSE + * operator. + * @param conn A connection to the test database. + */ + private void doXMLParseTests(Connection conn) + { + System.out.println("\n[ Beginning XMLPARSE tests. ]\n"); + + System.out.println("Test insertions from file: "); + try {=20 + + // Test parsing of > 32K XML documents. + insertFiles(conn, "xTable.t1", "xmlTestFiles/wide40k.xml", 1); + insertFiles(conn, "xTable.t1", "xmlTestFiles/deep40k.xml", 1); + + // Test parsing of docs that use schemas. Since server + // and client tests run in a subdirectory, we have to modify + // the XML documents that use DTDs so that they can find + // the DTD files. + + insertDocWithDTD(conn, "xTable.t1", "xmlTestFiles/dtdDoc.xml", + "personal.dtd", 1); + insertFiles(conn, "xTable.t1", "xmlTestFiles/xsdDoc.xml", 1); + + // XMLPARSE is not supposed to validate, so the following + // inserts should SUCCEED, even though the documents + // don't adhere to their schemas. + insertDocWithDTD(conn, "xTable.t1", + "xmlTestFiles/dtdDoc_invalid.xml", "personal.dtd", 1); + insertFiles(conn, "xTable.t1", + "xmlTestFiles/xsdDoc_invalid.xml", 1); + + System.out.println("--> Insertions all PASS."); + + } catch (SQLException se) { + System.out.println("FAIL: Unexpected exception: "); + while (se !=3D null) { + se.printStackTrace(System.out); + se =3D se.getNextException(); + } + } catch (Exception e) { + System.out.println("FAIL: Unexpected exception: "); + e.printStackTrace(System.out); + } + + // Test binding nulls to the XMLPARSE operand. + + try { + + PreparedStatement pSt =3D conn.prepareStatement( + "insert into xTable.t1(x) values " + + "(XMLPARSE (DOCUMENT ? PRESERVE WHITESPACE))"); + + // This should work. Note we check binding to + // a character stream method in "insertFiles". + System.out.print("Binding string in XMLPARSE: "); + bindAndExecute(pSt, 1, Types.CHAR, " doc ", + null, false); + + // Null should work, too. + System.out.print("Binding Java null string in XMLPARSE: "); + bindAndExecute(pSt, 1, Types.CHAR, null, null, false); + System.out.print("Binding SQL NULL string in XMLPARSE: "); + bindAndExecute(pSt, 1, Types.CLOB, null, null, true); + + } catch (Exception e) { + System.out.println("Unexpected exception: "); + e.printStackTrace(System.out); + } + + System.out.println("\n[ End XMLPARSE tests. ]\n"); + } + + /** + * Test serialization of the XML values inserted by + * the doXMLParseTests() method above. For the documents + * that are larger than 32K, this tests that they can + * be correctly read from disk as a stream (instead of + * just as as string). + * @param conn A connection to the test database. + */ + private void doXMLSerializeTests(Connection conn) + { + System.out.println("\n[ Beginning XMLSERIALIZE tests. ]\n"); + + try { + + PreparedStatement pSt =3D conn.prepareStatement( + "select i, XMLSERIALIZE(X AS CLOB) FROM xTable.t1"); + ResultSet rs =3D pSt.executeQuery(); + + String xResult =3D null; + int rowCount =3D 0; + while (rs.next()) { + xResult =3D rs.getString(2); + if (!rs.wasNull()) { + System.out.println(rs.getInt(1) + ", " + + "[ roughly " + (xResult.length() / 1000) + "k ]"); + } + else + System.out.println(rs.getInt(1) + ", NULL"); + rowCount++; + } + + } catch (Exception e) { + System.out.println("Unexpected exception: "); + e.printStackTrace(System.out); + } + + // Test binding to the XMLSERIALIZE operand. Since + // the operand is an XML value, and since we don't + // allow binding to an XML value (see "doBindTests()" + // above), there's nothing more to do here. + + System.out.println("\n[ End XMLSERIALIZE tests. ]\n"); + } + + /** + * Run some simple XPath queries against the documents + * inserted in doXMLParseTests() above, and then test + * binding of null values in the XMLEXISTS operator. + * @param conn A connection to the test database. + */ + private void doXMLExistsTests(Connection conn) + { + System.out.println("\n[ Begin XMLEXISTS tests. ]\n"); + + // Run some sample queries. + try { + + existsQuery(conn, "xTable.t1", "//abb"); + existsQuery(conn, "xTable.t1", "//d50"); + existsQuery(conn, "xTable.t1", "//person/email"); + existsQuery(conn, "xTable.t1", "/personnel"); + existsQuery(conn, "xTable.t1", "//person/@id"); + + // This next one is important because it verifies + // that implicit/default values which are defined + // in a DTD _are_ actually processed, even though + // we don't perform validation. Thus this next + // query _should_ return a match. + int rowCount =3D existsQuery(conn, "xTable.t1", "//person/@not= eTwo"); + if (rowCount =3D=3D 0) { + System.out.println("FAILED: Query on DTD default didn't " + + "return any matches."); + } + + } catch (Exception e) { + System.out.println("Unexpected exception: "); + e.printStackTrace(System.out); + } + + // Test binding nulls to the XMLEXISTS operands. Binding + // of the second (XML) operand is not allowed and was + // checked in "doBindTests()" above. Here we just + // check binding of the first operand, which should be + // a string. + try { + + PreparedStatement pSt =3D conn.prepareStatement( + "select i from xTable.t1 where " + + "XMLEXISTS (? PASSING BY VALUE x)"); + + System.out.print("Binding string in XMLEXISTS: "); + bindAndExecute(pSt, 1, Types.CHAR, "//d48", null, false); + + // Null should work, too. + System.out.print("Binding Java null string in XMLEXISTS: "); + bindAndExecute(pSt, 1, Types.CHAR, null, null, false); + System.out.print("Binding SQL NULL string in XMLEXISTS: "); + bindAndExecute(pSt, 1, Types.VARCHAR, null, null, true); + + } catch (Exception e) { + System.out.println("Unexpected exception: "); + e.printStackTrace(System.out); + } + + System.out.println("\n[ End XMLEXISTS tests. ]\n"); + } + + /** + * Helper method. Inserts the contents of a file into + * the received table using "setCharacterStream". + * @param conn A connection to the test database. + * @param tableName Name of the target table + * @param fName Name of the file whose content we + * want to insert. + * @param numRows Number of times we should insert + * the received file's content. + */ + private void insertFiles(Connection conn,=20 + String tableName, String fName, int numRows) + throws Exception + { + // First we have to figure out many chars long the + // file is. + InputStream iS =3D this.getClass().getResourceAsStream(fName); + InputStreamReader reader =3D new InputStreamReader(iS); + char [] cA =3D new char[1024]; + int charCount =3D 0; + for (int len =3D reader.read(cA, 0, cA.length); len !=3D -1; + charCount +=3D len, len =3D reader.read(cA, 0, cA.length)); + + reader.close(); + + // Now that we know the number of characters, we can + // insert using a stream. + + PreparedStatement pSt =3D conn.prepareStatement( + "insert into xTable.t1(x) values (" + + "xmlparse(document ? preserve whitespace))"); + + for (int i =3D 0; i < numRows; i++) { + + iS =3D this.getClass().getResourceAsStream(fName); + reader =3D new InputStreamReader(iS); + pSt.setCharacterStream(1, reader, charCount); + pSt.execute(); + reader.close(); + System.out.println("Inserted roughly " + + (charCount / 1000) + "k of data."); + + } + } + + /** + * Helper method. Inserts an XML document into the + * received table using setString. This method + * parallels "insertFiles" above, except that it + * should be used for documents that require a DTD + * in order to be complete. In that case, the + * location of the DTD has to modified _in_ the + * document so that it can be found regardless of + * whether we're running in embedded mode or in + * server/client mode. + * @param conn A connection to the test database. + * @param tableName Name of the target table + * @param fName Name of the file whose content we + * want to insert. + * @param dtdName Name of the DTD file that the + * received file uses. + * @param numRows Number of times we should insert + * the received file's content. + */ + private void insertDocWithDTD(Connection conn,=20 + String tableName, String fName, String dtdName, + int numRows) throws Exception + { + boolean needsUpdate =3D true; + String currPath =3D System.getProperty("user.dir"); + String fileSep =3D System.getProperty("file.separator"); + + String dtdPath =3D currPath; + boolean foundDTD =3D false; + while (!foundDTD) { + + try { + + FileReader fR =3D new FileReader(dtdPath + + fileSep + dtdName); + + // If we get here, then we found the DTD in + // the current path, so we're done. + foundDTD =3D true; + dtdPath =3D "file:///" + dtdPath + fileSep + dtdName; + break; + + } catch (java.io.IOException ie) { + + // Couldn't find the DTD in the current path. + // The harness uses a lot of subdirectories when + // running tests (for client, or server, or + // suites, or nested suites...etc.), so we + // back up one directory and try again. + + int pos =3D dtdPath.lastIndexOf(fileSep); + if (pos =3D=3D -1) { + // we're at the top of the path and haven't + // found the DTD yet. This shouldn't happen. + throw new Exception("Couldn't find DTD '" + + dtdName + "' for insertion of file '" + + fName + "'."); + } + dtdPath =3D dtdPath.substring(0, pos); + + } + } + + // Read the file into memory so we can update it. + InputStream iS =3D this.getClass().getResourceAsStream(fName); + InputStreamReader reader =3D new InputStreamReader(iS); + char [] cA =3D new char[1024]; + StringBuffer sBuf =3D new StringBuffer(); + int charCount =3D 0; + for (int len =3D reader.read(cA, 0, cA.length); len !=3D -1; + charCount +=3D len, len =3D reader.read(cA, 0, cA.length)) + { + sBuf.append(cA, 0, len); + } + + reader.close(); + + // Now replace the DTD location, if needed. + String docAsString =3D sBuf.toString(); + int pos =3D docAsString.indexOf(dtdName); + if (pos !=3D -1) + sBuf.replace(pos, pos + dtdName.length(), dtdPath); + + // Now (finally) do the insert using the in-memory + // document with the correct DTD location. + docAsString =3D sBuf.toString(); + PreparedStatement pSt =3D conn.prepareStatement( + "insert into xTable.t1(x) values (" + + "xmlparse(document ? preserve whitespace))"); + + charCount =3D docAsString.length(); + for (int i =3D 0; i < numRows; i++) { + + pSt.setString(1, docAsString); + pSt.execute(); + System.out.println("Inserted roughly " + + (charCount / 1000) + "k of data."); + + } + } + + /** + * Helper method. Selects all rows from the received + * table name that have at least one node matching + * the received XPath expression. Does this query + * using the XMLEXISTS operator. + * @param conn A connection to the test database. + * @param tableName Table to query. + * @param xPath The XPath expression to evaluate. + * @return The number of rows that match the + * XPath expression. + */ + private int existsQuery(Connection conn, + String tableName, String xPath) throws Exception + { + PreparedStatement pSt =3D conn.prepareStatement( + "select i from " + tableName + " where " + + "xmlexists('" + xPath + "' passing by value x)"); + + System.out.println("Running XMLEXISTS with: " + xPath); + ResultSet rs =3D pSt.executeQuery(); + String xResult =3D null; + int rowCount =3D 0; + while (rs.next()) { + rowCount++; + } + + System.out.println("--> Matching rows: " + rowCount); + return rowCount; + } + + /** + * Helper method. Attempts to bind a parameter to a + * given value using the given type, and then prints + * the result of that attempt (PASS/FAIL). + * @param pSt The prepared statement holding the parameter + * that we want to bind. + * @param paramNum Which parameter in pSt we want to bind. + * @param paramType The type of the value to be bound. + * @param bindValue The value to be used for binding. + * @param sqlState The expected SQLState for the binding + * error, if one is expected. Null if the bind is expected + * to succeed. + * @param bindSqlNull True if we should bind using a SQL + * NULL (i.e. "setNull()"). + */ + private void bindAndExecute(PreparedStatement pSt, int paramNum, + int paramType, Object bindValue, String sqlState, + boolean bindSqlNull) + { + SQLException actualException =3D null; + try { + + // First try to bind. + if (bindSqlNull) { + pSt.setNull(paramNum, paramType); + } + else { + switch (paramType) + { + case Types.CHAR: + case Types.VARCHAR: + + pSt.setString(paramNum, (String)bindValue); + break; + + case Types.INTEGER: + + pSt.setInt(paramNum, ((Integer)bindValue).intValue= ()); + break; + + default: + + System.out.println("ERROR: Unexpected bind type ("= + + paramType + ") in call to doBind."); + break; + } + } + + // Now try to execute. + pSt.execute(); + + } catch (SQLException e) { + actualException =3D e; + } + + checkException(actualException, sqlState); + } + + /** + * Helper method. Checks to see if the received SQLException + * has a SQLState that matches the target/expected SQLState. + * Prints out a message saying the result of this check, and + * in the case where the actual error is NOT the expected + * error, prints a full stack trace to System.out. + * @param se The SQLException to be checked. + * @param targetState The expected SQLState; null if no + * error was expected. + */ + private void checkException(SQLException se, + String targetState) + { + if (targetState =3D=3D null) { + if (se =3D=3D null) { + System.out.println("PASS -- Completed without exception, "= + + "as expected."); + } + else { + System.out.println("FAIL -- Was expected to succeed, but "= + + "failed with error " + se.getSQLState() + "."); + se.printStackTrace(System.out); + } + return; + } + + if (se =3D=3D null) { + System.out.println("FAIL -- Completed without exception when "= + + "error " + targetState + " was expected."); + return; + } + + if (!targetState.equals(se.getSQLState())) { + System.out.println("FAIL: Caught error " + se.getSQLState() + + " when was expecting error " + targetState + "."); + se.printStackTrace(System.out); + return; + } + + System.out.println("PASS -- caught expected error " + + targetState + "."); + } +} Propchange: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting= /functionTests/tests/lang/xmlBinding.java ---------------------------------------------------------------------------= --- svn:eol-style =3D native Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/func= tionTests/tests/lang/xmlBinding_app.properties URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/= org/apache/derbyTesting/functionTests/tests/lang/xmlBinding_app.properties?= rev=3D189721&view=3Dauto =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/lang/xmlBinding_app.properties (added) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/lang/xmlBinding_app.properties Wed Jun 8 23:48:34 2005 @@ -0,0 +1,2 @@ +usedefaults=3Dtrue +supportfiles=3Dtests/lang/xmlTestFiles/personal.dtd,tests/lang/xmlTestFile= s/personal.xsd Propchange: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting= /functionTests/tests/lang/xmlBinding_app.properties ---------------------------------------------------------------------------= --- svn:eol-style =3D native