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. -- -- test for import export thru command line interface -- first test basic import functionality -- ascii delimited default format drop table T1; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist. ij> create table T1 ( Account int, Fname char(30), Lname char(30), Company varchar(35), Address varchar(40), City varchar(20), State char(5), Zip char(10), Payment decimal(8,2), Balance decimal(8,2)); 0 rows inserted/updated/deleted ij> create index T1_IndexBalance on T1 (Balance, Account, Company); 0 rows inserted/updated/deleted ij> create index T1_IndexFname on T1 (Fname, Account); 0 rows inserted/updated/deleted ij> create index T1_IndexLname on T1 (Lname, Account); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extin/EndOfFile.txt' , null, null, null, 0) ; ERROR XIE0R: Import error on line 2 of file extin/EndOfFile.txt: Read endOfFile at unexpected place on line 2. ERROR XIE0E: Read endOfFile at unexpected place on line 2. ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extin/Tutor1.asc' , null, null, null, 0) ; 0 rows inserted/updated/deleted ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T1')); 1 ----------- 1 ij> -- ascii Fixed drop table T2; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T2' because it does not exist. ij> autocommit off; ij> create table T2 ( Account int, Fname char(30), Lname char(30), Company varchar(35), Address varchar(40), City varchar(20), State char(5), Zip char(10), Payment decimal(8,2), Balance decimal(8,2)); 0 rows inserted/updated/deleted ij> create index T2_IndexBalance on T2 (Balance, Account, Company); 0 rows inserted/updated/deleted ij> create index T2_IndexFname on T2 (Fname, Account); 0 rows inserted/updated/deleted ij> create index T2_IndexLname on T2 (Lname, Account); 0 rows inserted/updated/deleted ij> commit; ij> --this one should fail becuase this is not the right command to handle fixed formats call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T2' , 'extin/Tutor2.asc' , null, null, null, 0) ; ERROR 38000: The exception 'java.sql.SQLException: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.' was thrown while evaluating an expression. ERROR 42X04: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table. ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T2')); 1 ----------- 1 ij> commit; ij> -- test remapping drop table T3; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T3' because it does not exist. ij> create table T3 ( Lname char(30), Fname char(30), Account int not null primary key, Company varchar(35), Payment decimal(8,2), Balance decimal(8,2)); 0 rows inserted/updated/deleted ij> create index T3_indexBalance on T3 (Balance, Company, Account); 0 rows inserted/updated/deleted ij> create index T3_indexPayment on T3 (Payment, Company, Account); 0 rows inserted/updated/deleted ij> --icorrect mapping of file to table ; should give error and rollback call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T3' , null , '3, 2, 1, 4, 9, 200' , 'extin/Tutor3.asc' , null, null, null, 0) ; ERROR 38000: The exception 'java.sql.SQLException: Column 'COLUMN200' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN200' is not a column in the target table.' was thrown while evaluating an expression. ERROR 42X04: Column 'COLUMN200' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN200' is not a column in the target table. ij> rollback; ij> -- table should not be there select count(*) from T3; ERROR 42X05: Table/View 'T3' does not exist. ij> create table T3 ( Lname char(30), Fname char(30), Account int not null primary key, Company varchar(35), Payment decimal(8,2), Balance decimal(8,2)); 0 rows inserted/updated/deleted ij> create index T3_indexBalance on T3 (Balance, Company, Account); 0 rows inserted/updated/deleted ij> create index T3_indexPayment on T3 (Payment, Company, Account); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T3' , null , '3, 2, 1, 4, 9, 10' , 'extin/Tutor3.asc' , null, null, null, 0) ; 0 rows inserted/updated/deleted ij> commit; ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T3')); 1 ----------- 1 ij> -- now check results select count(*) from T1; 1 ----------- 102 ij> select count(*) from T2; 1 ----------- 0 ij> select count(*) from T3; 1 ----------- 102 ij> select * from T1 where State = 'CA'; ACCOUNT |FNAME |LNAME |COMPANY |ADDRESS |CITY |STATE|ZIP |PAYMENT |BALANCE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 10000 |Bugs |Bunny |Warner Brothers | -- Rabbit Hole -- |Los Angeles |CA |94001 |0.00 |100.00 10129 |Mickey |Mouse |Disneyland |Magic Kingdom |Los Angeles |CA |94000 |0.00 |1.00 ij> select * from T2 where State = 'CA'; ACCOUNT |FNAME |LNAME |COMPANY |ADDRESS |CITY |STATE|ZIP |PAYMENT |BALANCE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ij> select * from T3 where Fname = 'Bugs' or Fname = 'Mickey'; LNAME |FNAME |ACCOUNT |COMPANY |PAYMENT |BALANCE ----------------------------------------------------------------------------------------------------------------------------------- Bunny |Bugs |10000 |Warner Brothers |0.00 |10000.00 Mouse |Mickey |10129 |Disneyland |0.00 |3.00 ij> select Balance, Account, Company from T1 order by Balance; BALANCE |ACCOUNT |COMPANY ---------------------------------------------------------- 1.00 |10129 |Disneyland 100.00 |10000 |Warner Brothers 218.00 |10019 |Market Place 222.00 |10023 |Madson & Huth Communication Co 223.00 |10024 |La Salle Clinic 224.00 |10025 |Town & Country Electric Inc. 225.00 |10026 |Saturn of Appleton 226.00 |10027 |Bemiss Corp. 227.00 |10028 |AAL Member Credit Union 228.00 |10029 |Office Support 229.00 |10030 |EAA 230.00 |10031 |Kurz Electric 231.00 |10032 |Alpha 1 233.00 |10034 |Valley Trust Corporation 234.00 |10035 |Fox Community Credit Union 235.00 |10036 |Valley Lawn Care 236.00 |10037 |Network Health Plan 237.00 |10038 |Kolosso Toyota 238.00 |10039 |AAL 239.00 |10040 |WDFF 240.00 |10041 |AAL 241.00 |10042 |Novus Health Group 242.00 |10043 |AAL 243.00 |10044 |AAL Capital Management 244.00 |10045 |AAL Printing/Distribution 245.00 |10046 |Lawrence University 246.00 |10047 |Ken Gibson Assoc. 247.00 |10048 |Menasha Employees Credit Union 248.00 |10049 |UPIU 249.00 |10050 |AAL 251.00 |10052 |Fox Valley Technical College 252.00 |10053 |Bergstrom Air Force Base 253.00 |10054 |Bank One 254.00 |10055 |Bergstrom Enterprises 255.00 |10056 |Fox Cities Bank 256.00 |10057 |Town of Vandenbroek 257.00 |10058 |Kitz Printing 259.00 |10060 |Prime Control Systems 260.00 |10061 |Van Vreede's 261.00 |10062 |Insurance Brokerage Services 262.00 |10063 |Fox Valley Travel 263.00 |10064 |M & M Advertising 264.00 |10065 |University of Women 265.00 |10066 |YMCA 266.00 |10067 |O'Donnell Publishing 267.00 |10068 |Western Ice 268.00 |10069 |Four D Corporation 269.00 |10070 |Andrews Mautner, Inc. 270.00 |10071 |Laser Typesetting 271.00 |10072 |Wallach & Assoc. 272.00 |10073 |Town of Grand River 273.00 |10074 |Cabelvision 274.00 |10075 |Print & Mail, Inc. 275.00 |10076 |Jewelers Mutual Insurance 276.00 |10077 |Burton Karstedt 278.00 |10079 |Twin City Rod & Gun Club 279.00 |10080 |American Legion 280.00 |10081 |TechLine 281.00 |10082 |Data One Computer Service 282.00 |10083 |Appleton Area School District 283.00 |10084 |Banta Corporate 284.00 |10085 |HRC Inc. 285.00 |10086 |Phopar 286.00 |10087 |Sutherland Electric 288.00 |10088 |Banta Credit Union 289.00 |10089 |Uffenbeck Diamonds 290.00 |10090 |Equitable Reserve Association 291.00 |10091 |Derksen Printing 292.00 |10092 |Custom Printing 293.00 |10093 |Faye's Fine Jewelry 294.00 |10094 |Haugners Inc. 295.00 |10095 |Pyramid Marketing & Advertising 297.00 |10097 |Wisconsin Tissue Credit Union 298.00 |10098 |AAL 299.00 |10099 |Pat Woods Inc 301.00 |10100 |Creative Learning International 302.00 |10101 |United Health 303.00 |10102 |Memorial Florists 304.00 |10103 |Fay Hawkinson & Kruse Inc 305.00 |10104 |Enterprise Motors 306.00 |10105 |Weidert Group Inc 307.00 |10106 |Principal Financial 308.00 |10107 |Woodfield Suites 309.00 |10108 |Master Litho 310.00 |10109 |Rawhide Boys Ranch 311.00 |10110 |Ad Works 312.00 |10111 |Directions Inc 313.00 |10112 |Counseling Resource Center 314.00 |10113 |World Wide Auto Parts 315.00 |10114 |Secura Insurance 316.00 |10115 |Witthuhn Printing 317.00 |10116 |St Mary's Central High School 319.00 |10117 |Valley Periodontics 320.00 |10118 |Neenah Printing 321.00 |10119 |YMCA of Appleton 322.00 |10120 |Appleton Medical Center 323.00 |10121 |Quinlan Dentistry 324.00 |10122 |Klusendorf Chiropractic 325.00 |10123 |Drucks Plumbing 326.00 |10124 |Market Link 327.00 |10125 |Norandex 328.00 |10126 |Appleton Camping ij> select Balance, Account, Company from T2 order by Balance; BALANCE |ACCOUNT |COMPANY ---------------------------------------------------------- ij> select Balance, Account, Company from T3 order by Balance; BALANCE |ACCOUNT |COMPANY ---------------------------------------------------------- 3.00 |10129 |Disneyland 218.00 |10019 |Market Place 222.00 |10023 |Madson & Huth Communication Co 223.00 |10024 |La Salle Clinic 224.00 |10025 |Town & Country Electric Inc. 225.00 |10026 |Saturn of Appleton 226.00 |10027 |Bemiss Corp. 227.00 |10028 |AAL Member Credit Union 228.00 |10029 |Office Support 229.00 |10030 |EAA 230.00 |10031 |Kurz Electric 231.00 |10032 |Alpha 1 233.00 |10034 |Valley Trust Corporation 234.00 |10035 |Fox Community Credit Union 235.00 |10036 |Valley Lawn Care 236.00 |10037 |Network Health Plan 237.00 |10038 |Kolosso Toyota 238.00 |10039 |AAL 239.00 |10040 |WDFF 240.00 |10041 |AAL 241.00 |10042 |Novus Health Group 242.00 |10043 |AAL 243.00 |10044 |AAL Capital Management 244.00 |10045 |AAL Printing/Distribution 245.00 |10046 |Lawrence University 246.00 |10047 |Ken Gibson Assoc. 247.00 |10048 |Menasha Employees Credit Union 248.00 |10049 |UPIU 249.00 |10050 |AAL 251.00 |10052 |Fox Valley Technical College 252.00 |10053 |Bergstrom Air Force Base 253.00 |10054 |Bank One 254.00 |10055 |Bergstrom Enterprises 255.00 |10056 |Fox Cities Bank 256.00 |10057 |Town of Vandenbroek 257.00 |10058 |Kitz Printing 259.00 |10060 |Prime Control Systems 260.00 |10061 |Van Vreede's 261.00 |10062 |Insurance Brokerage Services 262.00 |10063 |Fox Valley Travel 263.00 |10064 |M & M Advertising 264.00 |10065 |University of Women 265.00 |10066 |YMCA 266.00 |10067 |O'Donnell Publishing 267.00 |10068 |Western Ice 268.00 |10069 |Four D Corporation 269.00 |10070 |Andrews Mautner, Inc. 270.00 |10071 |Laser Typesetting 271.00 |10072 |Wallach & Assoc. 272.00 |10073 |Town of Grand River 273.00 |10074 |Cabelvision 274.00 |10075 |Print & Mail, Inc. 275.00 |10076 |Jewelers Mutual Insurance 276.00 |10077 |Burton Karstedt 278.00 |10079 |Twin City Rod & Gun Club 279.00 |10080 |American Legion 280.00 |10081 |TechLine 281.00 |10082 |Data One Computer Service 282.00 |10083 |Appleton Area School District 283.00 |10084 |Banta Corporate 284.00 |10085 |HRC Inc. 285.00 |10086 |Phopar 286.00 |10087 |Sutherland Electric 288.00 |10088 |Banta Credit Union 289.00 |10089 |Uffenbeck Diamonds 290.00 |10090 |Equitable Reserve Association 291.00 |10091 |Derksen Printing 292.00 |10092 |Custom Printing 293.00 |10093 |Faye's Fine Jewelry 294.00 |10094 |Haugners Inc. 295.00 |10095 |Pyramid Marketing & Advertising 297.00 |10097 |Wisconsin Tissue Credit Union 298.00 |10098 |AAL 299.00 |10099 |Pat Woods Inc 301.00 |10100 |Creative Learning International 302.00 |10101 |United Health 303.00 |10102 |Memorial Florists 304.00 |10103 |Fay Hawkinson & Kruse Inc 305.00 |10104 |Enterprise Motors 306.00 |10105 |Weidert Group Inc 307.00 |10106 |Principal Financial 308.00 |10107 |Woodfield Suites 309.00 |10108 |Master Litho 310.00 |10109 |Rawhide Boys Ranch 311.00 |10110 |Ad Works 312.00 |10111 |Directions Inc 313.00 |10112 |Counseling Resource Center 314.00 |10113 |World Wide Auto Parts 315.00 |10114 |Secura Insurance 316.00 |10115 |Witthuhn Printing 317.00 |10116 |St Mary's Central High School 319.00 |10117 |Valley Periodontics 320.00 |10118 |Neenah Printing 321.00 |10119 |YMCA of Appleton 322.00 |10120 |Appleton Medical Center 323.00 |10121 |Quinlan Dentistry 324.00 |10122 |Klusendorf Chiropractic 325.00 |10123 |Drucks Plumbing 326.00 |10124 |Market Link 327.00 |10125 |Norandex 328.00 |10126 |Appleton Camping 10000.00 |10000 |Warner Brothers ij> --- now check other input formats --- this is Tutor1 with RecordSeperator=',', FieldStartDelimiter=(, FieldEndDelimiter=),FieldSeperator=TAB autocommit on; ij> drop table Alt1; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'ALT1' because it does not exist. ij> create table Alt1 ( Account int, Fname char(30), Lname char(30), Company varchar(35), Address varchar(40), City varchar(20), State char(5), Zip char(10), Payment decimal(8,2), Balance decimal(8,2)); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALT1' , 'extin/Alt1.asc' , null, null, null, 0) ; ERROR XIE0R: Import error on line 1 of file extin/Alt1.asc: Invalid character string format for type INTEGER. ERROR 22018: Invalid character string format for type INTEGER. ij> select * from Alt1 where State = 'CA'; ACCOUNT |FNAME |LNAME |COMPANY |ADDRESS |CITY |STATE|ZIP |PAYMENT |BALANCE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ij> select Balance, Account, Company from Alt1 order by Balance; BALANCE |ACCOUNT |COMPANY ---------------------------------------------------------- ij> -- this is Tutor1 with some null fields drop table Alt3; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'ALT3' because it does not exist. ij> create table Alt3 ( Account int, Fname char(30), Lname char(30), Company varchar(35), Address varchar(40), City varchar(20), State char(5), Zip char(10), Payment decimal(8,2), Balance decimal(8,2)); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALT3' , 'extin/Alt3.asc' , null, null, null, 0) ; ERROR XIE0R: Import error on line 1 of file extin/Alt3.asc: Invalid character string format for type INTEGER. ERROR 22018: Invalid character string format for type INTEGER. ij> select * from Alt3 where State = 'CA' or State = 'TX'; ACCOUNT |FNAME |LNAME |COMPANY |ADDRESS |CITY |STATE|ZIP |PAYMENT |BALANCE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ij> select Balance, Account, Company from Alt3 order by Balance; BALANCE |ACCOUNT |COMPANY ---------------------------------------------------------- ij> -- test remapping -- test remapping -- following case is commented because data has different seperator than the default one -- enable this case after rearranging the data (-suresht) --create table tempAlt3(column1 varchar(1000) , column3 varchar(1000) , column9 varchar(1000)); --call SYSCS_UTIL.SYSCS_IMPORT_DATA (null, 'tempAlt3' , -- null , '2,3,9', -- 'extin/Alt3.asc' , -- null, null, null, 0) ; --select * from tempAlt3; --- --- SQL anywhere table --- drop table sqlAnywhere1; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'SQLANYWHERE1' because it does not exist. ij> create table sqlAnywhere1 ( Id int, Name varchar(40), Title varchar(40), Company varchar(50), Address varchar(80), City varchar(30), State varchar(30), Zip varchar(30), Country varchar(30), phone1 varchar(50), phone2 varchar(30), email varchar(80), web varchar(50)); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'SQLANYWHERE1' , null , '1,2,3,4,5,7,8,9,10,11,12,13,14', 'extin/sqlAnywhere1.utf' , '|', '''', 'ASCII', 0) ; 0 rows inserted/updated/deleted ij> select Company, Country from sqlAnywhere1 where country not like 'U%S%A%' and country is not null; COMPANY |COUNTRY --------------------------------------------------------------------------------- Dow Jones Interactive Publishing | Electric Press | Los Alamos National Lab | SoftCom | Kinetoscope | Npulse Software | Penetics | Columbia University | Visionary Information Systems, Inc. | Micro Computer Systems | Gemini Systems | Crossroads Technologies | Silverstream Software, Inc. | Rexton Interactive | Tilden Park | Blue Lobster Software | Novera | Verix Software | Lotus | Background Profiles | Weblogic | Marimba | MECA Software | Pervasive Software, Inc. | Neuron Data | Tandem | Verge Software Corporation | Computer Curriculum Corporation | Omix | Noblestar | Highmark, Inc. | Learncom | Molecular Simulations | Progress Software | Skunk Technologies | Sterling Software | Sun Microsystems | Thought, Inc. | Internet Image | Database Advisor Magazine | Rogue Wave | Creative Concepts Corporation | Visa International | Object Systems, Inc. | Dynavent Software | Telecallaction | Cybered Corp | ADC Kentrox | Sprocket Interactive Solutions | Kenetiks, Inc. | Infospace, Inc. | Open Horizon | WebMethods, Inc. | Sovereign Bank | NetNumina Solutions | Interactive Education | LiveMedia | Sun Microsystems | Grandview DB/DC Systems | Mage Lang Institute, Ltd. | List Foundation | Shafir Inc. | Great Northwest Design | Inovie Software Inc. | LANQUEST | Sprint Paranet | Orbital Technologies | Biss GmbH | GRC International, Inc. | Painted Word, Inc. | Isadra, Inc. | Delphi Solutions |Switzerland FieldWorker Products Ltd. | Fiberlane Communications Canada, Inc. |CANADA Novell | Platinum technology, Inc. ViaTech Development Lab | IBM |Switzerland Manna Network Technologies |Israel Advanced Workstation Solutions, Inc. | Cybotics Technologies Ltd. |Hong Kong Intermedia Communications, Inc. | Bay Networks, Inc. | Charles River Associates, Inc. | Scopus Technologies | Net Dynamics |US Tomday |US Cerulean Technology, Inc. | Pencom Web Works | Check Free | Cybered Corp. | The Weizmann Institute of Science |Israel Tridium, Inc. | Omron Corporation |Japan Nob Systems Corp. | Software Tecnologia Y Servicios S.A. |Colombia IBM | Gunn Software Pty Ltd. |Australia en Vise Corporation |Canada University of Maryland, UMBC |US Temple Games, Inc. |US Food and Agriculture Organization |Italy Adobe Systems |US Trefoil Computers Ltd |UK University of Economics |AT Acriter Systems BV |Netherlands NetBeans, Inc. | Sundayta Ltd |United Kingdom Synthese S.A. | ij> drop table sqlAnywhere2; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'SQLANYWHERE2' because it does not exist. ij> create table sqlAnywhere2 ( Fname varchar(30), Lname varchar(30), email varchar(40), phone varchar(30)); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'SQLANYWHERE2' , null , '3, 4, 7, 5', 'extin/sqlAnywhere2.utf' , '|', '''', 'ASCII', 0) ; 0 rows inserted/updated/deleted ij> select count(*) from sqlAnywhere2; 1 ----------- 204 ij> --- --- MS access text file --- default text format is compatible with ours except their record seperator --- is CR-LF instead of LF -- data does not seem to match above description , only problem I see --- is delimiters inside the data , which gets fixed with double delimters --- check the intended case -suresht drop table HouseHoldItem; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'HOUSEHOLDITEM' because it does not exist. ij> create table HouseHoldItem ( Category int, RoomId int, Description varchar(255), Model varchar(50), ModelId varchar(50), SerialNumber varchar(50), DayPurchase date, PurchasePrice decimal(8,2), Insured smallint, Note varchar(512)); 0 rows inserted/updated/deleted ij> -- import it first with just LF as record seperator, we should be seeing -- ^M's at the end of note field. call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'HOUSEHOLDITEM' , null , '2,3,4,5,6,7,8,11,14', 'extin/Access1.txt' , null, null, null, 0) ; ERROR 38000: The exception 'java.sql.SQLException: The number of values assigned is not the same as the number of specified or implied columns.' was thrown while evaluating an expression. ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. ij> select * from HouseHoldItem; CATEGORY |ROOMID |DESCRIPTION |MODEL |MODELID |SERIALNUMBER |DAYPURCHA&|PURCHASEP&|INSUR&|NOTE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> ---- ---- test export ---- call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.dump' , '|','''', 'ASCII') ; 0 rows inserted/updated/deleted ij> create table imp_temp(column2 varchar(200), column3 varchar(200), column4 varchar(200), column5 varchar(200), column6 varchar(200)); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_TEMP' ,null, '2, 3, 4, 5, 6', 'extinout/t1.dump', '|', '''', 'ASCII', 0) ; 0 rows inserted/updated/deleted ij> select * from imp_temp ; COLUMN2 |COLUMN3 |COLUMN4 |COLUMN5 |COLUMN6 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bugs |Bunny |Warner Brothers | -- Rabbit Hole -- |Los Angeles Bobbi |Arndt |Market Place |1000 S Nicolet Rd |Sametown Bruce |Beecher |Madson & Huth Communication Co |1037 W Wisconsin Ave |Smithville Bruce |Beyer |La Salle Clinic |108 E Wisconsin Ave |Jonestown Butch |Bobbi |Town & Country Electric Inc. |108 Hillock Ct |Smithville Calla |Boshers |Saturn of Appleton |110 Fox River Dr |Smithville Carol |Brauer |Bemiss Corp. |110 W North Water St |Jonestown Carol |Braun |AAL Member Credit Union |1115 E Glendale Ave |Smithville Cheri |Buksyk |Office Support |1122 Milwaukee St |Jonestown Chuck |Buss |EAA |1134 S Franklin St |Overton Chuck |Carpenter |Kurz Electric |115 S Drew St |Smithville Chuck |Carr |Alpha 1 |1151 Valley Fair Mall |Jonestown Colleen |Casperson |Valley Trust Corporation |120 N Morrison St |Smithville Connie |Catterton |Fox Community Credit Union |1200 N Perkins St |Smithville Connie |Clay |Valley Lawn Care |121 N Douglas St # R |Smithville Craig |Collar |Network Health Plan |1216 W Wisconsin Ave |Moretown Dan |Coppenger |Kolosso Toyota |1221 N Lawe St |Smithville Dan |Dag |AAL |1222 N Superior St |Smithville Darlene |Dantzier |WDFF |124 W Wisconsin Ave |Dime Box Darlene |Dellenmann |AAL |130 E Franklin St |Smithville Darren |Dinkl |Novus Health Group |1300 E Calumet St |Smithville David |Dockry |AAL |1302 S Ritger |Smithville Debra |Dorsey |AAL Capital Management |1320 S Lincoln St |Smithville Denise |Richardson |AAL Printing/Distribution |1396 Ridgeway Court |Smithville Diane |Duginski |Lawrence University |144 N Mall Dr |Smithville Dick |Dunbar |Ken Gibson Assoc. |1461 Ashland Ave |Smithville Dinah |Duxbury |Menasha Employees Credit Union |1486 Earl St |Dime Box Dominic |Earl |UPIU |150 W Green Bay Rd |Lonesome Don |Ely |AAL |1620 S Lawe St |Smithville Donna |Erdmann |Fox Valley Technical College |1737 W Reid Dr |Smithville Donna |Esser |Bergstrom Air Force Base |1801 N Richmond St # 12 |Jonestown Duane |Feavel |Bank One |1818 N Meade St |Smithville Emmett |Forseth |Bergstrom Enterprises |1818 N Meade St |Smithville Erik |Francken |Fox Cities Bank |1825 N Bluemound Dr |Jonestown Father |Freitas |Town of Vandenbroek |1825 N Bluemound Dr |Smithville Gary |Garvey |Kitz Printing |1909 W 2nd St |Overton George |Gibson |Prime Control Systems |1965 Oshkosh Ave |Smithville Glenn |Gorman |Van Vreede's |1990 S Van Dyke Rd |Little Gorge Howard |Grennon |Insurance Brokerage Services |200 W College Ave |Smithville Ingrid |Guyette |Fox Valley Travel |2074 American Dr |Overton Jacquie |Hagen |M & M Advertising |21 Waugoo Ave |Smithville James |Hammer |University of Women |212 E College Ave |Overton Jan |Hanks |YMCA |213 Paul Dr |Jonestown Jane |Haugner |O'Donnell Publishing |217 Pacific St |Jonestown Jeff |Henneman |Western Ice |218 E Lawrence St |Smithville Jeff |Holland |Four D Corporation |2181 W Wisconsin Ave |Appleton Jim |Hoppe |Andrews Mautner, Inc. |221 E Atlantic St |Moosetown Jim |Howard |Laser Typesetting |221 W College Ave |Smithville Jim |Ick |Wallach & Assoc. |222 E College Ave |Smithville Jim |Issacson |Town of Grand River |222 E College Ave |Smithville Jo Ann |Jahnke |Cabelvision |225 Main St Box # 8003 |Smithville Joanne |Jensen |Print & Mail, Inc. |2301 W Nordale Dr |Smithville Jodi |Jesse |Jewelers Mutual Insurance |231 E College Ave |Jonestown Joe |Jirtle |Burton Karstedt |2320 S Memorial Dr |Dime Box John |Johnson |Twin City Rod & Gun Club |250 Industrial Drive |Jonestown John |Kamp |American Legion |2600 Stewart Ave # 22 |Smithville John |Karstedt |TechLine |2662 American Dr |Smithville John |Kitz |Data One Computer Service |2820 N Roemer Rd |Smithville John |Kolberg |Appleton Area School District |292 Ohio St |Smithville John |Korth |Banta Corporate |3000 W Wisconsin Ave |Dime Box John |Kotarek |HRC Inc. |3012 Greenview Dr |Jonestown Joyce |Kretsch |Phopar |3036 W Wisconsin Ave |Smithville Judy |Kuehl |Sutherland Electric |3090 Oregon St |Smithville Katie |Kusserow |Banta Credit Union |310 Appleton St |Dime Box Kay |Lambert |Uffenbeck Diamonds |319 Main St |Smithville Ken |Lamb |Equitable Reserve Association |32122 Paseo Adelanto Ste 2B |Jonestown Kevin |Lensby |Derksen Printing |3218 Timothy Ln #6 |Overton Larry |Lesperance |Custom Printing |322 N Commercial St |Smithville Larry |Lindberg |Faye's Fine Jewelry |3232 N Ballard Rd |Jonestown Linda |Linonofski |Haugners Inc. |33 Park Pl |Smithville Linda |Long |Pyramid Marketing & Advertising |3301 W Prospect Ave |Smithville Liz |Lonsway |Wisconsin Tissue Credit Union |333 W College Ave |Jonestown Lon |Lowe |AAL |342 W Wisconsin Ave |Smithville Lori |Ludwig |Pat Woods Inc |3730 W College Ave |Kimbro Luke |Madison |Creative Learning International |375 Byrd Ave |Jonestown Mark |Mancl |United Health |3992 N Richmond St |Smithville Mark |Melissa |Memorial Florists |408 W Wisconsin Ave |Smithville Marlene |Mentink |Fay Hawkinson & Kruse Inc |411 Lincoln St |Overton Marti |Merryfield |Enterprise Motors |419 N Oneida St |Smithville Marty |Meyere |Weidert Group Inc |420 E Longville Dr |Smithville Mary |Micke |Principal Financial |4301 W Wisconsin Ave |Smithville Mary |Mohr |Woodfield Suites |4321 N Ballard Rd |Smithville Mary |Mortensen |Master Litho |4321 N Ballard Rd |Jonestown Mary |Mueller |Rawhide Boys Ranch |4321 N Ballard Rd |New Hope Mary |Mugerauer |Ad Works |4321 N Ballard Rd |Smithville Mary |Novak |Directions Inc |4321 N Ballard Rd |Jonestown Mary Beth |O'Donnell |Counseling Resource Center |4321 N Ballard Rd |Smithville Mary |Pannabaker |World Wide Auto Parts |4321 W College Ave |Smithville Michelle |Pawlowski |Secura Insurance |4406 W Spencer St |Smithville Mike |Peter |Witthuhn Printing |4895 Integrity Way |Smithville Mike |Peters |St Mary's Central High School |502 W Northland Ave |Dime Box Mike |Phillips |Valley Periodontics |517 N Appleton St |Smithville Nancy |Rapp |Neenah Printing |520 E Wisconsin Ave |Jonestown Nancy |Rasmussen |YMCA of Appleton |520 E Wisconsin Ave |Smithville Nancy |Rathman |Appleton Medical Center |525 Enterprise Dr |Smithville Nancy |Reinl |Quinlan Dentistry |525 N Perkins St |Smithville Natalie |Reynolds |Klusendorf Chiropractic |528 2nd St |Smithville Pam |Richeson |Drucks Plumbing |531 N Main St |Dime Box Pat |Robbins |Market Link |557 S Marcella St |Smithville Pat |Roberts |Norandex |610 E Wisconsin Ave |Smithville Peg |Russel |Appleton Camping |675 Brighton Beach Rd |Smithville Mickey |Mouse |Disneyland |Magic Kingdom |Los Angeles ij> drop table imp_temp; 0 rows inserted/updated/deleted ij> -- test case for derby-1854/derby-1641 -- perform import into a table that has same column -- as a primary key and a foreign key (ADMINS table). create table users ( user_id int not null generated by default as identity, user_login varchar(255) not null, primary key (user_id)); 0 rows inserted/updated/deleted ij> create table admins ( user_id int not null, primary key (user_id), constraint admin_uid_fk foreign key (user_id) references users (user_id)); 0 rows inserted/updated/deleted ij> insert into users (user_login) values('test1'); 1 row inserted/updated/deleted ij> insert into users (user_login) values('test2'); 1 row inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select user_id from users' , 'extinout/users_id.dat', null , null , null ) ; 0 rows inserted/updated/deleted ij> call syscs_util.syscs_import_table( null, 'ADMINS', 'extinout/users_id.dat', null, null, null,1); 0 rows inserted/updated/deleted ij> select * from admins; USER_ID ----------- 1 2 ij> select * from users; USER_ID |USER_LOGIN -------------------------------------------------------------------------------------------------------------------------------------------- 1 |test1 2 |test2 ij> -- do consistency check on the table. values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'ADMINS'); 1 ----------- 1 ij> drop table admins; 0 rows inserted/updated/deleted ij> drop table users; 0 rows inserted/updated/deleted ij> -- end derby-1854/derby-1641 test case. -- -- begin test case for derby-2193: -- -- Field comprised of all blank space should become a null -- create table derby_2193_tab ( a varchar( 50 ), b varchar( 50 ) ); 0 rows inserted/updated/deleted ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ( null, 'DERBY_2193_TAB', 'extin/derby-2193.txt', null, null, null, 0 ); 0 rows inserted/updated/deleted ij> select * from derby_2193_tab; A |B ----------------------------------------------------------------------------------------------------- who put the bop |in the bopshebop diddywopdiddy |NULL boopdeedoo |boopdeedoo ij> select b, length(b) from derby_2193_tab; B |2 -------------------------------------------------------------- in the bopshebop |16 NULL |NULL boopdeedoo |10 ij> -- -- Errors should contain identifying line numbers -- create table derby_2193_lineNumber ( a int, b int ); 0 rows inserted/updated/deleted ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ( null, 'DERBY_2193_LINENUMBER', 'extin/derby-2193-linenumber.txt', null, null, null, 0 ); ERROR XIE0R: Import error on line 2 of file extin/derby-2193-linenumber.txt: Invalid character string format for type INTEGER. ERROR 22018: Invalid character string format for type INTEGER. ij> select * from derby_2193_lineNumber; A |B ----------------------- ij> -- -- end test case for derby-2193: -- -- -- begin test case for derby-2925: -- -- Prevent export from overwriting existing files -- create table derby_2925_tab ( a varchar( 50 ), b varchar( 50 ) ); 0 rows inserted/updated/deleted ij> -- -- Testing SYSCS_UTIL.SYSCS_EXPORT_TABLE -- CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ( null, 'DERBY_2925_TAB', 'extout/derby-2925.txt', null, null, null); 0 rows inserted/updated/deleted ij> -- -- Errors should should happen in the second -- call to SYSCS_UTIL.SYSCS_EXPORT_TABLE -- since extout/derby-2925.txt already exists. -- CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ( null, 'DERBY_2925_TAB', 'extout/derby-2925.txt', null, null, null); ERROR XIE0S: The export operation was not performed, because the specified output file (extout/derby-2925.txt) already exists. Export processing will not overwrite an existing file, even if the process has permissions to write to that file, due to security concerns, and to avoid accidental file damage. Please either change the output file name in the export procedure arguments to specify a file which does not exist, or delete the existing file, then retry the export operation. ij> -- -- Testing SYSCS_UTIL.SYSCS_EXPORT_QUERY -- CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY ('select * from DERBY_2925_TAB', 'extout/derby-2925-query.dat', null , null , null ) ; 0 rows inserted/updated/deleted ij> -- -- Errors should should happen in the second -- call to SYSCS_UTIL.SYSCS_EXPORT_QUERY -- since extout/derby-2925-query.dat already exists. -- CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY ('select * from DERBY_2925_TAB', 'extout/derby-2925-query.dat', null , null , null ) ; ERROR XIE0S: The export operation was not performed, because the specified output file (extout/derby-2925-query.dat) already exists. Export processing will not overwrite an existing file, even if the process has permissions to write to that file, due to security concerns, and to avoid accidental file damage. Please either change the output file name in the export procedure arguments to specify a file which does not exist, or delete the existing file, then retry the export operation. ij> -- -- Testing SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE -- create table derby_2925_lob ( id int, name varchar(30), content clob, pic blob ); 0 rows inserted/updated/deleted ij> -- -- Testing SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE -- where data file exists. -- CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE ('SELECT * FROM DERBY_2925_LOB','extout/derby-2925_data.dat', '\t' ,'|','UTF-16','extout/derby-2925_lobs.dat'); 0 rows inserted/updated/deleted ij> -- -- Errors should should happen in the second -- call to SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE -- since extout/derby-2925_data.dat already exists. -- CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE ('SELECT * FROM DERBY_2925_LOB','extout/derby-2925_data.dat', '\t' ,'|','UTF-16','extout/derby-2925_lobs.dat'); ERROR XIE0S: The export operation was not performed, because the specified output file (extout/derby-2925_data.dat) already exists. Export processing will not overwrite an existing file, even if the process has permissions to write to that file, due to security concerns, and to avoid accidental file damage. Please either change the output file name in the export procedure arguments to specify a file which does not exist, or delete the existing file, then retry the export operation. ij> -- -- Testing SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE -- where lob file exists. -- -- Errors should should happen in the -- call to SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE -- since extout/derby-2925_lobs.dat already exists. -- CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE ('SELECT * FROM DERBY_2925_LOB','extout/derby-2925_data1.dat', '\t' ,'|','UTF-16','extout/derby-2925_lobs.dat'); ERROR XIE0T: The export operation was not performed, because the specified large object auxiliary file (extout/derby-2925_lobs.dat) already exists. Export processing will not overwrite an existing file, even if the process has permissions to write to that file, due to security concerns, and to avoid accidental file damage. Please either change the large object auxiliary file name in the export procedure arguments to specify a file which does not exist, or delete the existing file, then retry the export operation. ij> -- -- end test case for derby-2925: ; ij>