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 38000: The exception 'java.sql.SQLException: Read endOfFile at unexpected place on line 2.' was thrown while evaluating an expression. 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 38000: The exception 'java.sql.SQLException: Invalid character string format for type INTEGER.' was thrown while evaluating an expression. 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 38000: The exception 'java.sql.SQLException: Invalid character string format for type INTEGER.' was thrown while evaluating an expression. 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. ; ij>