1 21 22 package org.apache.derbyTesting.functionTests.tests.tools; 23 24 import java.sql.Connection ; 25 import java.sql.DriverManager ; 26 import java.sql.Statement ; 27 import java.sql.ResultSet ; 28 import java.sql.SQLException ; 29 import java.sql.PreparedStatement ; 30 import java.sql.ResultSetMetaData ; 31 import java.io.File ; 32 import java.io.FileOutputStream ; 33 import java.io.InputStream ; 34 import java.io.BufferedInputStream ; 35 import java.io.FileInputStream ; 36 import java.util.Properties ; 37 import org.apache.derby.tools.ij; 38 import org.apache.derby.tools.JDBCDisplayUtil; 39 40 57 58 59 public class importExport { 60 61 62 private static Connection conn; 63 private static String currentVersion; 64 private static boolean passed = false; 65 66 public static void main(String [] args) { 67 System.out.println("Test importExport starting"); 68 69 try { 70 ij.getPropertyArg(args); 73 conn = ij.startJBMS(); 74 75 conn.setAutoCommit(true); 76 setup(true); 77 78 79 try { 81 System.out.println("testing non-existing data file"); 82 doImport("Z" , "T1" , null , null , null, 0); 83 } catch (Exception ex) { 84 printExceptionMessage(ex); 85 } 86 87 try { 89 System.out.println("testing null data file"); 90 doImport(null , "T1" , null , null, null, 0); 91 } catch (Exception 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 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 ex) { 140 printExceptionMessage(ex); 141 } 142 143 try { 144 doImport("T1" , "T3" , null , null , null, 0); 145 } catch (Exception ex) { 146 printExceptionMessage(ex); 147 } 148 149 try{ 152 doImportFromFile("extin/EndOfFile.txt" , "T4" , null , null , null, 0); 153 }catch (Exception 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 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 { 177 Statement stmt = conn.createStatement(); 178 stmt.execute("delete from t2"); 179 stmt.close(); 180 } 181 182 static void doImportAndExport(String fromTable, String colDel , 183 String charDel, 184 String codeset) throws Exception 185 { 186 187 188 doExport(fromTable , colDel , charDel , codeset); 189 doImportAndVerify(fromTable, colDel , charDel, codeset, 0); 190 doImportAndVerify(fromTable, colDel , charDel, codeset, 1); 192 193 } 194 195 private static void doExport(String fromTable, String colDel , 196 String charDel, 197 String codeset) throws Exception 198 { 199 200 String expsql = "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)"; 201 PreparedStatement 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 ps.execute(); 211 ps.close(); 212 213 } 214 215 private static void doImport(String fromTable, String toTable, 216 String colDel, String charDel , 217 String codeset, int replace) throws Exception 218 { 219 220 221 String impsql = "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (? , ? , ? , ?, ? , ?, ?)"; 222 PreparedStatement 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 ps.execute(); 233 ps.close(); 234 235 } 236 237 private static void doImportFromFile(String fileName, String toTable, 238 String colDel, String charDel , 239 String codeset, int replace) throws Exception 240 { 241 242 243 String impsql = "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (? , ? , ? , ?, ? , ?, ?)"; 244 PreparedStatement 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 ps.execute(); 255 ps.close(); 256 257 } 258 259 static void doImportAndVerify(String fromTable, String colDel, 260 String charDel , String codeset, 261 int replace) throws Exception { 262 263 doImport(fromTable , "T2" , colDel , charDel , codeset , replace); 264 265 Statement stmt = conn.createStatement(); 266 ResultSet 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 ("Wrong number of rows returned"); 279 } 280 281 doExport("T2" , colDel , charDel , codeset); 282 283 if (diffTwoFiles( "extinout/"+fromTable + ".dat", "extinout/"+"T2.dat")) { 285 throw new SQLException ("Export from " + fromTable + " and T2 don't match."); 286 287 } 288 289 } 290 291 static boolean diffTwoFiles(String file1, String file2) throws Exception { 292 293 InputStream f1 = new BufferedInputStream (new FileInputStream (file1)); 294 InputStream f2 = new BufferedInputStream (new FileInputStream (file2)); 295 296 int lineNo=1; 297 int o=1; 298 String 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 { 338 Statement 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 { 367 Statement 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 { 390 Statement 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 { 406 if (count!=expect) { 407 System.out.println("FAIL: Expected "+expect+" got "+count+" rows"); 408 throw new SQLException ("Wrong number of rows returned"); 409 } 410 } 411 412 static void verifyBoolean(boolean got, boolean expect) throws SQLException { 413 if (got!=expect) { 414 System.out.println("FAIL: Expected "+expect+" got "+got); 415 throw new SQLException ("Wrong boolean returned"); 416 } 417 } 418 419 static void printExceptionMessage(Exception ex) throws Exception 420 { 421 if (ex instanceof SQLException ) { 422 SQLException ie_ex = ((SQLException )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 |