You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by su...@apache.org on 2007/02/27 03:06:38 UTC
svn commit: r512109 [2/2] - in /db/derby/code/trunk/java:
engine/org/apache/derby/catalog/ engine/org/apache/derby/impl/load/
engine/org/apache/derby/impl/sql/catalog/
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTe...
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ieptests.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ieptests.out?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ieptests.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ieptests.out Mon Feb 26 18:06:37 2007
@@ -1,20 +1,20 @@
ij> --
--- Licensed to the Apache Software Foundation (ASF) under one or more
--- contributor license agreements. See the NOTICE file distributed with
--- this work for additional information regarding copyright ownership.
--- The ASF licenses this file to You 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.
---
---table used for export
+----- Licensed to the Apache Software Foundation (ASF) under one or more
+----- contributor license agreements. See the NOTICE file distributed with
+----- this work for additional information regarding copyright ownership.
+----- The ASF licenses this file to You 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.
+-----
+-----table used for export
create table ex_emp(id int , name char(7) , skills varchar(200), salary decimal(10,2)) ;
0 rows inserted/updated/deleted
ij> --table used for import
@@ -916,5 +916,127 @@
ij> drop table inventory.orderTable;
0 rows inserted/updated/deleted
ij> --end derby-390 related test cases.
+-----DERBY-378 , Add support to import/export of lobs
+create table books(id int , name varchar(30) , abstract clob(90), pic blob(100));
+0 rows inserted/updated/deleted
+ij> create table imp_books(id int , name varchar(30), abstract clob(90), pic blob(100));
+0 rows inserted/updated/deleted
+ij> insert into books values(1, 'effective java' ,
+ 'describes how to write " quality java " code',
+ cast (X'124594322143423214ab35f2e34c' as blob));
+1 row inserted/updated/deleted
+ij> insert into books values(2, 'Apache Derby ' ,
+ 'This books covers Apache Derby Database Engine',
+ cast (X'134594322143423214ab35f2e44d' as blob));
+1 row inserted/updated/deleted
+ij> insert into books values(3, 'Transaction Processing' ,
+ 'This books covers "Transaction" "processing" concepts',
+ cast (X'144594322143423214ab35f2e54e' as blob));
+1 row inserted/updated/deleted
+ij> select * from books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export lobs to an external file.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_IN_EXTFILE (null, 'BOOKS','extinout/books.dat',
+ null, null, null, 'extinout/books_lobs.dat') ;
+Statement executed.
+ij> -- import lobs stored in an external file.
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE(null, 'IMP_BOOKS' ,
+ 'extinout/books.dat', null, null, null, 0) ;
+Statement executed.
+ij> select * from imp_books ;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- import only some columns;
+call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE(null,
+ 'IMP_BOOKS' ,'ID, ABSTRACT, PIC', '1,3,4',
+ 'extinout/books.dat', null, null, null, 1) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |NULL |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |NULL |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |NULL |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- import columns in different order, this will test reading lob data
+----- from the file in a random order.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE(null,
+ 'IMP_BOOKS' ,'PIC, ID, ABSTRACT, NAME', '4,1,3,2',
+ 'extinout/books.dat', null, null, null, 1) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export using a query.
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_IN_EXTFILE('select * from books where id > 2',
+ 'extinout/books.dat' , null, null, null, 'extinout/books_lobs.dat') ;
+Statement executed.
+ij> delete from imp_books where id > 2;
+1 row inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE(null, 'IMP_BOOKS' ,
+ 'extinout/books.dat' ,
+ null, null, null, 0) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export lob data also to given export file. This should write blob
+----- data in hex format and clob data in double-delimiter to
+----- the export file.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'BOOKS','extinout/books.dat',
+ null, null, null) ;
+Statement executed.
+ij> delete from imp_books;
+3 rows inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_BOOKS' , 'extinout/books.dat' ,
+ null, null, null, 0) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- import only some columns;
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_BOOKS' ,
+ 'ID, ABSTRACT, PIC', '1,3,4',
+ 'extinout/books.dat', null, null, null, 1) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |NULL |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |NULL |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |NULL |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export using a query.
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from books where id > 2',
+ 'extinout/books.dat' , null, null, null) ;
+Statement executed.
+ij> delete from imp_books where id > 2;
+1 row inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE(null, 'IMP_BOOKS' , 'extinout/books.dat' ,
+ null, null, null, 0) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |NULL |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |NULL |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> --drop table imp_books;
+-----drop table books;
+-----END DERBY-378 , Add support to import/export of lobs
;
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/importExport.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/importExport.out?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/importExport.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/importExport.out Mon Feb 26 18:06:37 2007
@@ -14,9 +14,6 @@
testing IE with delimiters(%, &) and UTF-16
testing IE with delimiters(^, #) and WRONG ENCODEINGH
An IOException occurred while writing data to the file.
-testing datatypes that does not have Export Supprt
-Column 'C1' in the table is of type BLOB, it is not supported by the import/export feature.
-Column 'C1' in the table is of type BLOB, it is not supported by the import/export feature.
The exception 'java.sql.SQLException: Read endOfFile at unexpected place on line 2.' was thrown while evaluating an expression. SQLSTATE: XIE0E: Read endOfFile at unexpected place on line 2.
PASS: finished testing import and export
PASS: teardown complete
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/ieptests.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/ieptests.out?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/ieptests.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/ieptests.out Mon Feb 26 18:06:37 2007
@@ -1,20 +1,20 @@
ij> --
--- Licensed to the Apache Software Foundation (ASF) under one or more
--- contributor license agreements. See the NOTICE file distributed with
--- this work for additional information regarding copyright ownership.
--- The ASF licenses this file to You 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.
---
---table used for export
+----- Licensed to the Apache Software Foundation (ASF) under one or more
+----- contributor license agreements. See the NOTICE file distributed with
+----- this work for additional information regarding copyright ownership.
+----- The ASF licenses this file to You 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.
+-----
+-----table used for export
create table ex_emp(id int , name char(7) , skills varchar(200), salary decimal(10,2)) ;
0 rows inserted/updated/deleted
ij> --table used for import
@@ -916,5 +916,127 @@
ij> drop table inventory.orderTable;
0 rows inserted/updated/deleted
ij> --end derby-390 related test cases.
+-----DERBY-378 , Add support to import/export of lobs
+create table books(id int , name varchar(30) , abstract clob(90), pic blob(100));
+0 rows inserted/updated/deleted
+ij> create table imp_books(id int , name varchar(30), abstract clob(90), pic blob(100));
+0 rows inserted/updated/deleted
+ij> insert into books values(1, 'effective java' ,
+ 'describes how to write " quality java " code',
+ cast (X'124594322143423214ab35f2e34c' as blob));
+1 row inserted/updated/deleted
+ij> insert into books values(2, 'Apache Derby ' ,
+ 'This books covers Apache Derby Database Engine',
+ cast (X'134594322143423214ab35f2e44d' as blob));
+1 row inserted/updated/deleted
+ij> insert into books values(3, 'Transaction Processing' ,
+ 'This books covers "Transaction" "processing" concepts',
+ cast (X'144594322143423214ab35f2e54e' as blob));
+1 row inserted/updated/deleted
+ij> select * from books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export lobs to an external file.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_IN_EXTFILE (null, 'BOOKS','extinout/books.dat',
+ null, null, null, 'extinout/books_lobs.dat') ;
+Statement executed.
+ij> -- import lobs stored in an external file.
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE(null, 'IMP_BOOKS' ,
+ 'extinout/books.dat', null, null, null, 0) ;
+Statement executed.
+ij> select * from imp_books ;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- import only some columns;
+call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE(null,
+ 'IMP_BOOKS' ,'ID, ABSTRACT, PIC', '1,3,4',
+ 'extinout/books.dat', null, null, null, 1) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |NULL |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |NULL |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |NULL |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- import columns in different order, this will test reading lob data
+----- from the file in a random order.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE(null,
+ 'IMP_BOOKS' ,'PIC, ID, ABSTRACT, NAME', '4,1,3,2',
+ 'extinout/books.dat', null, null, null, 1) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export using a query.
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_IN_EXTFILE('select * from books where id > 2',
+ 'extinout/books.dat' , null, null, null, 'extinout/books_lobs.dat') ;
+Statement executed.
+ij> delete from imp_books where id > 2;
+1 row inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE(null, 'IMP_BOOKS' ,
+ 'extinout/books.dat' ,
+ null, null, null, 0) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export lob data also to given export file. This should write blob
+----- data in hex format and clob data in double-delimiter to
+----- the export file.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'BOOKS','extinout/books.dat',
+ null, null, null) ;
+Statement executed.
+ij> delete from imp_books;
+3 rows inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_BOOKS' , 'extinout/books.dat' ,
+ null, null, null, 0) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- import only some columns;
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_BOOKS' ,
+ 'ID, ABSTRACT, PIC', '1,3,4',
+ 'extinout/books.dat', null, null, null, 1) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |NULL |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |NULL |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |NULL |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export using a query.
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from books where id > 2',
+ 'extinout/books.dat' , null, null, null) ;
+Statement executed.
+ij> delete from imp_books where id > 2;
+1 row inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE(null, 'IMP_BOOKS' , 'extinout/books.dat' ,
+ null, null, null, 0) ;
+Statement executed.
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+-----
+1 |NULL |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |NULL |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> --drop table imp_books;
+-----drop table books;
+-----END DERBY-378 , Add support to import/export of lobs
;
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/importExport.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/importExport.out?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/importExport.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk16/importExport.out Mon Feb 26 18:06:37 2007
@@ -14,9 +14,6 @@
testing IE with delimiters(%, &) and UTF-16
testing IE with delimiters(^, #) and WRONG ENCODEINGH
An IOException occurred while writing data to the file.
-testing datatypes that does not have Export Supprt
-Column 'C1' in the table is of type BLOB, it is not supported by the import/export feature.
-Column 'C1' in the table is of type BLOB, it is not supported by the import/export feature.
The exception 'java.sql.SQLException: Read endOfFile at unexpected place on line 2.' was thrown while evaluating an expression.
PASS: finished testing import and export
PASS: teardown complete
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/iepnegativetests.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/iepnegativetests.out?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/iepnegativetests.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/iepnegativetests.out Mon Feb 26 18:06:37 2007
@@ -291,25 +291,6 @@
';', '^', 'utf-16', 1) ;
ERROR 38000: The exception 'java.sql.SQLException: 'SYS.SYSTABLES' is a system table. Users are not allowed to modify the contents of this table.' was thrown while evaluating an expression.
ERROR 42Y25: 'SYS.SYSTABLES' is a system table. Users are not allowed to modify the contents of this table.
-ij> ---not supported by db2 cloudscape import/export
-create table ntype(a int , ct CLOB(1024));
-0 rows inserted/updated/deleted
-ij> create table ntype1(bt BLOB(1024) , a int);
-0 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'NTYPE' , 'extinout/ntype.dat' ,
- null, null, null) ;
-ERROR XIE0B: Column 'CT' in the table is of type CLOB, it is not supported by the import/export feature.
-ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from iep.ntype1',
- 'extinout/ntype.dat' ,
- null, null, null) ;
-ERROR XIE0B: Column 'BT' in the table is of type BLOB, it is not supported by the import/export feature.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'NTYPE' , 'extinout/ntype.dat' ,
- null, null, null, 0) ;
-ERROR XIE0B: Column 'CT' in the table is of type CLOB, it is not supported by the import/export feature.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'NTYPE1' , null , null,
- 'extinout/ntype.dat' ,
- null, null, null, 0) ;
-ERROR XIE0B: Column 'BT' in the table is of type BLOB, it is not supported by the import/export feature.
ij> --import should aquire a lock on the table
create table parent(a int not null primary key);
0 rows inserted/updated/deleted
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/iepnegativetests_ES.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/iepnegativetests_ES.out?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/iepnegativetests_ES.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/iepnegativetests_ES.out Mon Feb 26 18:06:37 2007
@@ -316,25 +316,6 @@
';', '^', 'utf-16', 1) ;
ERROR 38000: Se he generado la excepci EnC:>243< n 'java.sql.SQLException: 'SYS.SYSTABLES' es una tabla del sistema. Los usuarios no tienen permitido modificar el contenido de esta tabla.' al evaluar una expresi EnC:>243< n.
ERROR 42Y25: 'SYS.SYSTABLES' es una tabla del sistema. Los usuarios no tienen permitido modificar el contenido de esta tabla.
-ij> ---not supported by db2 cloudscape import/export
-create table ntype(a int , ct CLOB(1024));
-0 rows inserted/updated/deleted
-ij> create table ntype1(bt BLOB(1024) , a int);
-0 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'NTYPE' , 'extinout/ntype.dat' ,
- null, null, null) ;
-ERROR XIE0B: La columna CT de la tabla de tipo CLOB, no est EnC:>225< soportada por la funci EnC:>243< n import/export.
-ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from iep.ntype1',
- EnC:>9< EnC:>9< EnC:>9< EnC:>9< 'extinout/ntype.dat' ,
- null, null, null) ;
-ERROR XIE0B: La columna BT de la tabla de tipo BLOB, no est EnC:>225< soportada por la funci EnC:>243< n import/export.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'NTYPE' , 'extinout/ntype.dat' ,
- null, null, null, 0) ;
-ERROR XIE0B: La columna CT de la tabla de tipo CLOB, no est EnC:>225< soportada por la funci EnC:>243< n import/export.
-ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'NTYPE1' , null , null,
- 'extinout/ntype.dat' ,
- null, null, null, 0) ;
-ERROR XIE0B: La columna BT de la tabla de tipo BLOB, no est EnC:>225< soportada por la funci EnC:>243< n import/export.
ij> --import should aquire a lock on the table
create table parent(a int not null primary key);
0 rows inserted/updated/deleted
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ieptests.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ieptests.out?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ieptests.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ieptests.out Mon Feb 26 18:06:37 2007
@@ -939,5 +939,127 @@
ij> drop table inventory.orderTable;
0 rows inserted/updated/deleted
ij> --end derby-390 related test cases.
+---DERBY-378 , Add support to import/export of lobs
+create table books(id int , name varchar(30) , abstract clob(90), pic blob(100));
+0 rows inserted/updated/deleted
+ij> create table imp_books(id int , name varchar(30), abstract clob(90), pic blob(100));
+0 rows inserted/updated/deleted
+ij> insert into books values(1, 'effective java' ,
+ 'describes how to write " quality java " code',
+ cast (X'124594322143423214ab35f2e34c' as blob));
+1 row inserted/updated/deleted
+ij> insert into books values(2, 'Apache Derby ' ,
+ 'This books covers Apache Derby Database Engine',
+ cast (X'134594322143423214ab35f2e44d' as blob));
+1 row inserted/updated/deleted
+ij> insert into books values(3, 'Transaction Processing' ,
+ 'This books covers "Transaction" "processing" concepts',
+ cast (X'144594322143423214ab35f2e54e' as blob));
+1 row inserted/updated/deleted
+ij> select * from books;
+ID |NAME |ABSTRACT |PIC
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export lobs to an external file.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_IN_EXTFILE (null, 'BOOKS','extinout/books.dat',
+ null, null, null, 'extinout/books_lobs.dat') ;
+0 rows inserted/updated/deleted
+ij> -- import lobs stored in an external file.
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE(null, 'IMP_BOOKS' ,
+ 'extinout/books.dat', null, null, null, 0) ;
+0 rows inserted/updated/deleted
+ij> select * from imp_books ;
+ID |NAME |ABSTRACT |PIC
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- import only some columns;
+call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE(null,
+ 'IMP_BOOKS' ,'ID, ABSTRACT, PIC', '1,3,4',
+ 'extinout/books.dat', null, null, null, 1) ;
+0 rows inserted/updated/deleted
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+1 |NULL |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |NULL |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |NULL |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- import columns in different order, this will test reading lob data
+-- from the file in a random order.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE(null,
+ 'IMP_BOOKS' ,'PIC, ID, ABSTRACT, NAME', '4,1,3,2',
+ 'extinout/books.dat', null, null, null, 1) ;
+0 rows inserted/updated/deleted
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export using a query.
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_IN_EXTFILE('select * from books where id > 2',
+ 'extinout/books.dat' , null, null, null, 'extinout/books_lobs.dat') ;
+0 rows inserted/updated/deleted
+ij> delete from imp_books where id > 2;
+1 row inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE(null, 'IMP_BOOKS' ,
+ 'extinout/books.dat' ,
+ null, null, null, 0) ;
+0 rows inserted/updated/deleted
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export lob data also to given export file. This should write blob
+-- data in hex format and clob data in double-delimiter to
+-- the export file.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'BOOKS','extinout/books.dat',
+ null, null, null) ;
+0 rows inserted/updated/deleted
+ij> delete from imp_books;
+3 rows inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_BOOKS' , 'extinout/books.dat' ,
+ null, null, null, 0) ;
+0 rows inserted/updated/deleted
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+1 |effective java |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |Apache Derby |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- import only some columns;
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_BOOKS' ,
+ 'ID, ABSTRACT, PIC', '1,3,4',
+ 'extinout/books.dat', null, null, null, 1) ;
+0 rows inserted/updated/deleted
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+1 |NULL |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |NULL |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |NULL |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> -- export using a query.
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from books where id > 2',
+ 'extinout/books.dat' , null, null, null) ;
+0 rows inserted/updated/deleted
+ij> delete from imp_books where id > 2;
+1 row inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE(null, 'IMP_BOOKS' , 'extinout/books.dat' ,
+ null, null, null, 0) ;
+0 rows inserted/updated/deleted
+ij> select * from imp_books;
+ID |NAME |ABSTRACT |PIC
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+1 |NULL |describes how to write " quality java " code |124594322143423214ab35f2e34c
+2 |NULL |This books covers Apache Derby Database Engine |134594322143423214ab35f2e44d
+3 |Transaction Processing |This books covers "Transaction" "processing" concepts |144594322143423214ab35f2e54e
+ij> --drop table imp_books;
+--drop table books;
+---END DERBY-378 , Add support to import/export of lobs
;
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/importExport.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/importExport.out?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/importExport.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/importExport.out Mon Feb 26 18:06:37 2007
@@ -14,9 +14,6 @@
testing IE with delimiters(%, &) and UTF-16
testing IE with delimiters(^, #) and WRONG ENCODEINGH
An IOException occurred while writing data to the file.
-testing datatypes that does not have Export Supprt
-Column 'C1' in the table is of type BLOB, it is not supported by the import/export feature.
-Column 'C1' in the table is of type BLOB, it is not supported by the import/export feature.
Read endOfFile at unexpected place on line 2.
PASS: finished testing import and export
PASS: teardown complete
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XMLTypeAndOpsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XMLTypeAndOpsTest.java?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XMLTypeAndOpsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XMLTypeAndOpsTest.java Mon Feb 26 18:06:37 2007
@@ -363,12 +363,6 @@
assertStatementError(JDBC.vmSupportsJDBC4() ? "38000" : "42Z71", cSt);
cSt = prepareCall(
- " CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY ("
- + " 'select xmlserialize(x as clob) from t1',"
- + " 'xmlexport.del', null, null, null)");
- assertStatementError("XIE0B", cSt);
-
- cSt = prepareCall(
" CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ("
+ " null, 'T1', 'shouldntmatter.del', null, null, null, 0)");
assertStatementError("XIE0B", cSt);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/iepnegativetests.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/iepnegativetests.sql?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/iepnegativetests.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/iepnegativetests.sql Mon Feb 26 18:06:37 2007
@@ -204,23 +204,6 @@
call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('SYS', 'SYSTABLES' , 'extinout/t3.dat' ,
';', '^', 'utf-16', 1) ;
-
----not supported by db2 cloudscape import/export
-create table ntype(a int , ct CLOB(1024));
-create table ntype1(bt BLOB(1024) , a int);
-
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'NTYPE' , 'extinout/ntype.dat' ,
- null, null, null) ;
-call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from iep.ntype1',
- 'extinout/ntype.dat' ,
- null, null, null) ;
-
-call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'NTYPE' , 'extinout/ntype.dat' ,
- null, null, null, 0) ;
-call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'NTYPE1' , null , null,
- 'extinout/ntype.dat' ,
- null, null, null, 0) ;
-
--import should aquire a lock on the table
create table parent(a int not null primary key);
insert into parent values (1) , (2) , (3) , (4) ;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql Mon Feb 26 18:06:37 2007
@@ -530,3 +530,93 @@
drop table inventory.orderTable;
--end derby-390 related test cases.
+
+---DERBY-378 , Add support to import/export of lobs
+
+create table books(id int , name varchar(30) , abstract clob(90), pic blob(100));
+
+create table imp_books(id int , name varchar(30), abstract clob(90), pic blob(100));
+
+insert into books values(1, 'effective java' ,
+ 'describes how to write " quality java " code',
+ cast (X'124594322143423214ab35f2e34c' as blob));
+insert into books values(2, 'Apache Derby ' ,
+ 'This books covers Apache Derby Database Engine',
+ cast (X'134594322143423214ab35f2e44d' as blob));
+insert into books values(3, 'Transaction Processing' ,
+ 'This books covers "Transaction" "processing" concepts',
+ cast (X'144594322143423214ab35f2e54e' as blob));
+
+select * from books;
+
+-- export lobs to an external file.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_IN_EXTFILE (null, 'BOOKS','extinout/books.dat',
+ null, null, null, 'extinout/books_lobs.dat') ;
+
+-- import lobs stored in an external file.
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE(null, 'IMP_BOOKS' ,
+ 'extinout/books.dat', null, null, null, 0) ;
+
+select * from imp_books ;
+
+-- import only some columns;
+call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE(null,
+ 'IMP_BOOKS' ,'ID, ABSTRACT, PIC', '1,3,4',
+ 'extinout/books.dat', null, null, null, 1) ;
+select * from imp_books;
+
+-- import columns in different order, this will test reading lob data
+-- from the file in a random order.
+call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE(null,
+ 'IMP_BOOKS' ,'PIC, ID, ABSTRACT, NAME', '4,1,3,2',
+ 'extinout/books.dat', null, null, null, 1) ;
+
+select * from imp_books;
+
+-- export using a query.
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_IN_EXTFILE('select * from books where id > 2',
+ 'extinout/books.dat' , null, null, null, 'extinout/books_lobs.dat') ;
+
+delete from imp_books where id > 2;
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE(null, 'IMP_BOOKS' ,
+ 'extinout/books.dat' ,
+ null, null, null, 0) ;
+select * from imp_books;
+
+
+-- export lob data also to given export file. This should write blob
+-- data in hex format and clob data in double-delimiter to
+-- the export file.
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'BOOKS','extinout/books.dat',
+ null, null, null) ;
+
+delete from imp_books;
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IMP_BOOKS' , 'extinout/books.dat' ,
+ null, null, null, 0) ;
+
+select * from imp_books;
+
+-- import only some columns;
+call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_BOOKS' ,
+ 'ID, ABSTRACT, PIC', '1,3,4',
+ 'extinout/books.dat', null, null, null, 1) ;
+
+select * from imp_books;
+
+-- export using a query.
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from books where id > 2',
+ 'extinout/books.dat' , null, null, null) ;
+delete from imp_books where id > 2;
+call SYSCS_UTIL.SYSCS_IMPORT_TABLE(null, 'IMP_BOOKS' , 'extinout/books.dat' ,
+ null, null, null, 0) ;
+select * from imp_books;
+
+--drop table imp_books;
+--drop table books;
+
+---END DERBY-378 , Add support to import/export of lobs
+
+
+
+
+
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExport.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExport.java?view=diff&rev=512109&r1=512108&r2=512109
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExport.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExport.java Mon Feb 26 18:06:37 2007
@@ -132,20 +132,6 @@
printExceptionMessage(ex) ;
}
-
- System.out.println("testing datatypes that does not have Export Supprt");
- try {
- doExport("T3", null, null , null);
- } catch (Exception ex) {
- printExceptionMessage(ex);
- }
-
- try {
- doImport("T1" , "T3" , null , null , null, 0);
- } catch (Exception ex) {
- printExceptionMessage(ex);
- }
-
//test less data case on the seconds line of input that
//should throw end of file exception.
try{
@@ -350,8 +336,6 @@
"COLUMN7 DOUBLE PRECISION , COLUMN8 INT , COLUMN9 BIGINT , COLUMN10 NUMERIC , " +
"COLUMN11 REAL , COLUMN12 SMALLINT , COLUMN13 TIME , COLUMN14 TIMESTAMP , "+
"COLUMN15 SMALLINT , COLUMN16 VARCHAR(1))"), 0);
- verifyCount(
- stmt.executeUpdate("CREATE TABLE T3 (C1 BLOB)"), 0);
verifyCount(
stmt.executeUpdate("create table T4 ( Account int, Fname char(30),"+
"Lname char(30), Company varchar(35), Address varchar(40), City varchar(20),"+