KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > tools > importExport


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.tools.importExport
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */

21
22 package org.apache.derbyTesting.functionTests.tests.tools;
23
24 import java.sql.Connection JavaDoc;
25 import java.sql.DriverManager JavaDoc;
26 import java.sql.Statement JavaDoc;
27 import java.sql.ResultSet JavaDoc;
28 import java.sql.SQLException JavaDoc;
29 import java.sql.PreparedStatement JavaDoc;
30 import java.sql.ResultSetMetaData JavaDoc;
31 import java.io.File JavaDoc;
32 import java.io.FileOutputStream JavaDoc;
33 import java.io.InputStream JavaDoc;
34 import java.io.BufferedInputStream JavaDoc;
35 import java.io.FileInputStream JavaDoc;
36 import java.util.Properties JavaDoc;
37 import org.apache.derby.tools.ij;
38 import org.apache.derby.tools.JDBCDisplayUtil;
39
40 /**
41   This tests import and export utilties. It first creates
42   a temp table T1 and inserts data into it. Then it calls
43   export to export data out from it into a temp file. Then
44   it calls import to read data from the temp file just
45   created. The program goes through the resultset of import
46   and inserts one row at a time into another temp table T2
47   which has same number of columns as T1. Then it compares
48   number of rows in T1 and T2. If the number of rows are same
49   as in T1 then part of the test succedded.
50
51   The second part imports data out from T2 into second temp
52   file and then we compare both the temp files to see if the
53   2 files exactly match
54
55   @author Mamta, Suresht
56  */

57
58
59 public class importExport {
60     
61
62     private static Connection JavaDoc conn;
63     private static String JavaDoc currentVersion;
64     private static boolean passed = false;
65
66     public static void main(String JavaDoc[] args) {
67         System.out.println("Test importExport starting");
68
69         try {
70             // use the ij utility to read the property file and
71
// make the initial connection.
72
ij.getPropertyArg(args);
73             conn = ij.startJBMS();
74
75             conn.setAutoCommit(true);
76             setup(true);
77
78
79             //try to import from a file which doesn't exist
80
try {
81                 System.out.println("testing non-existing data file");
82                 doImport("Z" , "T1" , null , null , null, 0);
83             } catch (Exception JavaDoc ex) {
84                 printExceptionMessage(ex);
85             }
86
87             //try to import from a null file
88
try {
89                 System.out.println("testing null data file");
90                 doImport(null , "T1" , null , null, null, 0);
91             } catch (Exception JavaDoc ex) {
92                 printExceptionMessage(ex);
93             }
94
95
96             System.out.println("testing empty table");
97             doImportAndExport("T1",null, null , null);
98
99             System.out.println("testing empty table with Delimited format");
100             doImportAndExport("T1", null, null , "8859_1");
101
102             System.out.println("testing empty table import Field/Char Delimiters");
103             doImportAndExport("T1", "\t", "|" , "8859_1");
104
105             cleanupBeforeNextRun();
106             addDummyRows();
107             System.out.println("testing import/export with default options");
108             doImportAndExport("T1",null, null, null);
109
110             cleanupBeforeNextRun();
111             System.out.println("testing IE with code set 8859_1");
112             doImportAndExport("T1", null, null , "8859_1");
113             
114             cleanupBeforeNextRun();
115             System.out.println("testing IE with delimiter and codeset");
116             doImportAndExport("T1", "\t", "|", "8859_1");
117
118             cleanupBeforeNextRun();
119             System.out.println("testing IE with delimiters(%, &) and Cp1252");
120             doImportAndExport("T1", "%", "&", "Cp1252");
121
122             cleanupBeforeNextRun();
123             System.out.println("testing IE with delimiters(%, &) and UTF-16");
124             doImportAndExport("T1", "%", "&", "UTF-16");
125
126             cleanupBeforeNextRun();
127
128             System.out.println("testing IE with delimiters(^, #) and WRONG ENCODEINGH");
129             try{
130                 doImportAndExport("T1", "^", "#", "INAVALID ENCODING");
131             } catch (Exception JavaDoc ex) {
132                 printExceptionMessage(ex) ;
133             }
134
135
136             System.out.println("testing datatypes that does not have Export Supprt");
137             try {
138                 doExport("T3", null, null , null);
139             } catch (Exception JavaDoc ex) {
140                 printExceptionMessage(ex);
141             }
142
143             try {
144                 doImport("T1" , "T3" , null , null , null, 0);
145             } catch (Exception JavaDoc ex) {
146                 printExceptionMessage(ex);
147             }
148                 
149             //test less data case on the seconds line of input that
150
//should throw end of file exception.
151
try{
152                 doImportFromFile("extin/EndOfFile.txt" , "T4" , null , null , null, 0);
153             }catch (Exception JavaDoc ex) {
154                 printExceptionMessage(ex);
155             }
156
157             System.out.println("PASS: finished testing import and export");
158             teardown();
159             System.out.println("PASS: finished cleaning up the temporary objects from database");
160
161             conn.close();
162
163             passed = true;
164
165         } catch (Throwable JavaDoc e) {
166             System.out.println("FAIL: exception thrown:");
167             passed = false;
168             JDBCDisplayUtil.ShowException(System.out,e);
169         }
170
171         if (passed)
172             System.out.println("PASS");
173         System.out.println("Test importExport finished");
174     }
175
176     static void cleanupBeforeNextRun() throws Exception JavaDoc {
177         Statement JavaDoc stmt = conn.createStatement();
178         stmt.execute("delete from t2");
179         stmt.close();
180     }
181
182     static void doImportAndExport(String JavaDoc fromTable, String JavaDoc colDel ,
183                                   String JavaDoc charDel,
184                                   String JavaDoc codeset) throws Exception JavaDoc
185     {
186
187
188         doExport(fromTable , colDel , charDel , codeset);
189         doImportAndVerify(fromTable, colDel , charDel, codeset, 0);
190         //test with replace
191
doImportAndVerify(fromTable, colDel , charDel, codeset, 1);
192
193     }
194
195     private static void doExport(String JavaDoc fromTable, String JavaDoc colDel ,
196                      String JavaDoc charDel,
197                      String JavaDoc codeset) throws Exception JavaDoc
198     {
199         
200         String JavaDoc expsql = "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)";
201         PreparedStatement JavaDoc ps = conn.prepareStatement(expsql);
202         ps.setString(1 , "APP");
203         ps.setString(2, fromTable);
204         ps.setString(3, (fromTable==null ? fromTable : "extinout/" + fromTable + ".dat" ));
205         ps.setString(4 , colDel);
206         ps.setString(5 , charDel);
207         ps.setString(6 , codeset);
208
209         //perform export
210
ps.execute();
211         ps.close();
212
213     }
214
215     private static void doImport(String JavaDoc fromTable, String JavaDoc toTable,
216                                  String JavaDoc colDel, String JavaDoc charDel ,
217                                  String JavaDoc codeset, int replace) throws Exception JavaDoc
218     {
219
220                 
221         String JavaDoc impsql = "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (? , ? , ? , ?, ? , ?, ?)";
222         PreparedStatement JavaDoc ps = conn.prepareStatement(impsql);
223         ps.setString(1 , "APP");
224         ps.setString(2, toTable);
225         ps.setString(3, (fromTable==null ? fromTable : "extinout/" + fromTable + ".dat" ));
226         ps.setString(4 , colDel);
227         ps.setString(5 , charDel);
228         ps.setString(6 , codeset);
229         ps.setInt(7, replace);
230
231         //perform export
232
ps.execute();
233         ps.close();
234
235     }
236     
237     private static void doImportFromFile(String JavaDoc fileName, String JavaDoc toTable,
238                                  String JavaDoc colDel, String JavaDoc charDel ,
239                                  String JavaDoc codeset, int replace) throws Exception JavaDoc
240     {
241
242                 
243         String JavaDoc impsql = "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (? , ? , ? , ?, ? , ?, ?)";
244         PreparedStatement JavaDoc ps = conn.prepareStatement(impsql);
245         ps.setString(1 , "APP");
246         ps.setString(2, toTable);
247         ps.setString(3, fileName);
248         ps.setString(4 , colDel);
249         ps.setString(5 , charDel);
250         ps.setString(6 , codeset);
251         ps.setInt(7, replace);
252
253         //perform export
254
ps.execute();
255         ps.close();
256
257     }
258     
259     static void doImportAndVerify(String JavaDoc fromTable, String JavaDoc colDel,
260                                   String JavaDoc charDel , String JavaDoc codeset,
261                                   int replace) throws Exception JavaDoc {
262
263         doImport(fromTable , "T2" , colDel , charDel , codeset , replace);
264
265         Statement JavaDoc stmt = conn.createStatement();
266         ResultSet JavaDoc rs = stmt.executeQuery("SELECT COUNT(*) FROM " + fromTable);
267         rs.next();
268         int numberOfRowsInT1 = rs.getInt(1);
269         rs.close();
270         rs = stmt.executeQuery("SELECT COUNT(*) FROM t2");
271         rs.next();
272         int numberOfRowsInT2 = rs.getInt(1);
273         rs.close();
274         stmt.close();
275         if (numberOfRowsInT1 != numberOfRowsInT2)
276         {
277             System.out.println("FAIL: Expected " + numberOfRowsInT1 + " got " + numberOfRowsInT2 + " rows after import");
278             throw new SQLException JavaDoc("Wrong number of rows returned");
279         }
280
281         doExport("T2" , colDel , charDel , codeset);
282
283         //check whether the exported files from T1 and T2 are same now.
284
if (diffTwoFiles( "extinout/"+fromTable + ".dat", "extinout/"+"T2.dat")) {
285             throw new SQLException JavaDoc("Export from " + fromTable + " and T2 don't match.");
286
287         }
288
289     }
290
291     static boolean diffTwoFiles(String JavaDoc file1, String JavaDoc file2) throws Exception JavaDoc {
292
293         InputStream JavaDoc f1 = new BufferedInputStream JavaDoc(new FileInputStream JavaDoc(file1));
294         InputStream JavaDoc f2 = new BufferedInputStream JavaDoc(new FileInputStream JavaDoc(file2));
295
296         int lineNo=1;
297         int o=1;
298         String JavaDoc lineSep = "\n";
299
300         boolean diffed = false;
301         boolean notDone = true;
302
303         int b1, b2;
304
305         while (notDone) {
306             b1 = f1.read();
307             b2 = f2.read();
308             if ((b1 != b2) && (b1 != -1) && (b2 != -1)) {
309                 diffed = true;
310                 System.out.println(file1 + " " + file2 + " differ: byte " + o + ", line " + lineNo);
311                 notDone = false;
312             } else {
313                 if (b1 == b2) {
314                     if (b1 == -1) {
315                         notDone = false;
316                     } else
317                         if (b1 == (int)lineSep.charAt(0)) {
318                             lineNo++;
319                         }
320                 } else
321                     if (b1 == -1) {
322                         diffed = true;
323                         System.out.println(file1 + " " + file2 + " differ: EOF on " + file1);
324                         notDone = false;
325                     } else if (b2 == -1) {
326                         diffed = true;
327                         System.out.println(file1 + " " + file2 + " differ: EOF on " + file2);
328                         notDone = false;
329                     }
330
331             }
332             o++;
333         }
334         return diffed;
335     }
336
337     static void setup(boolean first) throws Exception JavaDoc {
338         Statement JavaDoc stmt = conn.createStatement();
339
340         if (first) {
341             verifyCount(
342                         stmt.executeUpdate("CREATE TABLE T1 (COLUMN1 VARCHAR(5) , COLUMN2 VARCHAR(8) , " +
343                                            "COLUMN3 SMALLINT , COLUMN4 CHAR(11) , COLUMN5 DATE , COLUMN6 DECIMAL(5,1) , " +
344                                            "COLUMN7 DOUBLE PRECISION , COLUMN8 INT , COLUMN9 BIGINT , COLUMN10 NUMERIC , " +
345                                            "COLUMN11 REAL , COLUMN12 SMALLINT , COLUMN13 TIME , COLUMN14 TIMESTAMP , "+
346                                            "COLUMN15 SMALLINT , COLUMN16 VARCHAR(1))"), 0);
347             verifyCount(
348                         stmt.executeUpdate("CREATE TABLE T2 (COLUMN1 VARCHAR(5) , COLUMN2 VARCHAR(8) , " +
349                                            "COLUMN3 SMALLINT, COLUMN4 CHAR(11) , COLUMN5 DATE , COLUMN6 DECIMAL(5,1) , " +
350                                            "COLUMN7 DOUBLE PRECISION , COLUMN8 INT , COLUMN9 BIGINT , COLUMN10 NUMERIC , " +
351                                            "COLUMN11 REAL , COLUMN12 SMALLINT , COLUMN13 TIME , COLUMN14 TIMESTAMP , "+
352                                            "COLUMN15 SMALLINT , COLUMN16 VARCHAR(1))"), 0);
353             verifyCount(
354                         stmt.executeUpdate("CREATE TABLE T3 (C1 BLOB)"), 0);
355             verifyCount(
356                         stmt.executeUpdate("create table T4 ( Account int, Fname char(30),"+
357                         "Lname char(30), Company varchar(35), Address varchar(40), City varchar(20),"+
358                        "State char(5), Zip char(10), Payment decimal(8,2), Balance decimal(8,2))"),0);
359             
360         } else {
361             verifyBoolean( stmt.execute("DELETE FROM t1"), false);
362         }
363         stmt.close();
364     }
365
366     static void addDummyRows() throws Exception JavaDoc {
367         Statement JavaDoc stmt = conn.createStatement();
368
369         verifyCount(
370                     stmt.executeUpdate("INSERT INTO T1 VALUES (null,'aa',1,'a',DATE('1998-06-30'),"+
371                                        "1,1,1,1,1,1,1,TIME('12:00:00'),TIMESTAMP('1998-06-30 12:00:00.0'),1,'a')"),1);
372
373         verifyCount(
374                     stmt.executeUpdate("INSERT INTO T1 VALUES (null,'bb',1,'b',DATE('1998-06-30'),"+
375                                        "2,2,2,2,2,2,2,TIME('12:00:00'),TIMESTAMP('1998-06-30 12:00:00.0'),2,'b')"),1);
376
377         verifyCount(
378                     stmt.executeUpdate("INSERT INTO T1 VALUES (null,'cc',1,'c',DATE('1998-06-30'),"+
379                                        "3,3,3,3,3,3,3,TIME('12:00:00'),TIMESTAMP('1998-06-30 12:00:00.0'),3,'c')"),1);
380
381         verifyCount(
382                     stmt.executeUpdate("INSERT INTO T1 VALUES (null,'dd',1,'d',DATE('1998-06-30'),"+
383                                        "4,4,4,4,4,4,4,TIME('12:00:00'),TIMESTAMP('1998-06-30 12:00:00.0'),4,'d')"),1);
384
385         System.out.println("PASS: setup complete");
386     }
387
388
389     static void teardown() throws SQLException JavaDoc {
390         Statement JavaDoc stmt = conn.createStatement();
391
392         verifyCount(
393                     stmt.executeUpdate("DROP TABLE t1"),
394                     0);
395
396         verifyCount(
397                     stmt.executeUpdate("DROP TABLE t2"),
398                     0);
399
400         stmt.close();
401
402         System.out.println("PASS: teardown complete");
403     }
404
405     static void verifyCount(int count, int expect) throws SQLException JavaDoc {
406         if (count!=expect) {
407             System.out.println("FAIL: Expected "+expect+" got "+count+" rows");
408             throw new SQLException JavaDoc("Wrong number of rows returned");
409         }
410     }
411
412     static void verifyBoolean(boolean got, boolean expect) throws SQLException JavaDoc {
413         if (got!=expect) {
414             System.out.println("FAIL: Expected "+expect+" got "+got);
415             throw new SQLException JavaDoc("Wrong boolean returned");
416         }
417     }
418
419     static void printExceptionMessage(Exception JavaDoc ex) throws Exception JavaDoc
420     {
421         if (ex instanceof SQLException JavaDoc) {
422             SQLException JavaDoc ie_ex = ((SQLException JavaDoc)ex);
423             
424             while(ie_ex.getNextException() != null)
425             {
426                 ie_ex = ie_ex.getNextException();
427             }
428             System.out.println(ie_ex.getMessage());
429         }
430         else
431             throw ex;
432     }
433 }
434
435
436
437
438
439
440
441
Popular Tags