Java API By Example, From Geeks To Geeks.

1 /* 2 3    Derby - Class org.apache.derbyTesting.functionTests.tests.store.streamingColumn 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.store; 23 24 import java.sql.Connection ; 25 import java.sql.DriverManager ; 26 import java.sql.ResultSetMetaData ; 27 import java.sql.ResultSet ; 28 import java.sql.Statement ; 29 import java.sql.SQLException ; 30 import java.sql.Types ; 31 32 import org.apache.derby.tools.ij; 33 import org.apache.derby.tools.JDBCDisplayUtil; 34 import org.apache.derbyTesting.functionTests.util.Formatters; 35 import org.apache.derbyTesting.functionTests.util.TestUtil; 36 import org.apache.derby.iapi.reference.Limits; 37 import java.io.*; 38 import java.sql.CallableStatement ; 39 import java.sql.PreparedStatement ; 40 import java.util.zip.CRC32 ; 41 import java.util.Properties ; 42 43 /** 44  * Test of JDBC result set Stream calls. 45  * 46  * @author djd 47  */ 48 49 public class streamingColumn { 50 51             // set up a short (fit in one page) inputstream for insert 52 static String [] fileName; 53     static long[] fileLength; 54 55     static 56     { 57         int numFiles = 4; 58         fileName = new String [numFiles]; 59         fileLength = new long[numFiles]; 60 61         fileName[0] = "extin/short.data"; // set up a short (fit in one page) inputstream for insert 62 fileName[1] = "extin/shortbanner"; // set up a long (longer than a page) inputstream for insert 63 fileName[2] = "extin/derby.banner"; // set up a really long (over 300K) inputstream for insert 64 fileName[3] = "extin/empty.data"; // set up a file with nothing in it 65 } 66 67     private static final int LONGVARCHAR = 1; 68     private static final int CLOB = 2; 69     private static final int VARCHAR = 3; 70      71     public static void main(String [] args) { 72 73         System.out.println("Test streamingColumn starting"); 74 75         try { 76             // use the ij utility to read the property file and 77 // make the initial connection. 78 ij.getPropertyArg(args); 79             Connection conn = ij.startJBMS(); 80 81             streamTest1(conn); 82 83             // test column size 1500 bytes 84 streamTest2(conn, 1500); 85             // test column size 5000 butes 86 streamTest2(conn, 5000); 87             streamTest2(conn, 10000); 88 89             streamTest3(conn, 0); 90             streamTest3(conn, 1500); 91             streamTest3(conn, 5000); 92             streamTest3(conn, 10000); 93 94             streamTest4(conn); 95 96             streamTest5(conn, 0); 97             streamTest5(conn, 1500); 98             streamTest5(conn, 5000); 99         // This test fails when running w/ derby.language.logStatementText=true 100 // see DERBY-595 101 //streamTest5(conn, 100000); 102 103             streamTest6(conn, 5000); 104             streamTest7(conn); 105 106             // test 1st column fit, second column doesn't 107 streamTest8(conn, 10, 2500); 108             streamTest9(conn, 10, 2500); 109 110             // test 1st column doesn't fit, second column does 111 streamTest8(conn, 2500, 10); 112             streamTest9(conn, 2500, 10); 113 114             // test compressTable 115 streamTest10(conn); 116 117             // bug 5592 test negativte length for the setXXStream methods. Should fail. 118 streamTest11(conn); 119 120             // bug 5592 test - only non-blank character truncation should give error for varchars 121 streamTest12(conn); 122 123             // bug 5592 test - any character(including blank character) truncation should give error for long varchars 124 streamTest13(conn); 125 126             // Test clob truncation, behavior similar to varchar 127 // trailingspaces are truncated but if there are trailing non-blanks then 128 // exception is thrown 129 // This test is similar to streamTest12. 130 streamTest14(conn); 131              132              133             // Derby500 134 // user supplied stream parameter values are not re-used 135 derby500Test(conn); 136 137             // currently in case of char,varchar,long varchar types 138 // stream paramter value is materialized the first time around 139 // and used for executions. Hence verify that the fix to 140 // DERBY-500 did not change the behavior for char,varchar 141 // and long varchar types when using streams. 142 derby500_verifyVarcharStreams(conn); 143              144             // turn autocommit on because in JCC, java.sql.Connection.close() can not be 145 // requested while a transaction is in progress on the connection. 146 // If autocommit is off in JCC, the transaction remains active, 147 // and the connection cannot be closed. 148 // If autocommit is off in Derby, an invalid transaction state SQL exception is thrown. 149 conn.setAutoCommit(true); 150             conn.close(); 151 152         } catch (SQLException e) { 153             dumpSQLExceptions(e); 154         } catch (Throwable e) { 155             System.out.println("FAIL -- unexpected exception:" + e.toString()); 156         } 157 158         System.out.println("Test streamingColumn finished"); 159     } 160 161     private static void streamTest1(Connection conn) { 162 163         ResultSetMetaData met; 164         ResultSet rs; 165         Statement stmt; 166 167         try { 168             stmt = conn.createStatement(); 169             stmt.execute("create table testLongVarChar (a int, b long varchar)"); 170             // insert a null long varchar 171 stmt.execute("insert into testLongVarChar values(1, '')"); 172             // insert a long varchar with a short text string 173 stmt.execute("insert into testLongVarChar values(2, 'test data: a string column inserted as an object')"); 174 175 176             for (int i = 0; i < fileName.length ; i++) { 177                 // prepare an InputStream from the file 178 File file = new File(fileName[i]); 179                 fileLength[i] = file.length(); 180                 InputStream fileIn = new FileInputStream(file); 181 182                 System.out.println("===> testing " + fileName[i] + " length = " 183                                    + fileLength[i]); 184 185                 // insert a streaming column 186 PreparedStatement ps = conn.prepareStatement("insert into testLongVarChar values(?, ?)"); 187                 ps.setInt(1, 100 + i); 188                 ps.setAsciiStream(2, fileIn, (int)fileLength[i]); 189                 try {//if trying to insert data > 32700, there will be an exception 190 ps.executeUpdate(); 191                     System.out.println("No truncation and hence no error"); 192                 } 193                 catch (SQLException e) { 194                     if (fileLength[i] > Limits.DB2_LONGVARCHAR_MAXWIDTH && e.getSQLState().equals("22001")) //was getting data longer than maxValueAllowed 195 System.out.println("expected exception for data > " + Limits.DB2_LONGVARCHAR_MAXWIDTH + " in length"); 196                     else 197                         dumpSQLExceptions(e); 198                 } 199                 fileIn.close(); 200             } 201 202             rs = stmt.executeQuery("select a, b from testLongVarChar"); 203             met = rs.getMetaData(); 204             byte[] buff = new byte[128]; 205             // fetch all rows back, get the long varchar columns as streams. 206 while (rs.next()) { 207                 // get the first column as an int 208 int a = rs.getInt("a"); 209                 // get the second column as a stream 210 InputStream fin = rs.getAsciiStream(2); 211                 int columnSize = 0; 212                 for (;;) { 213                     int size = fin.read(buff); 214                     if (size == -1) 215                         break; 216                     columnSize += size; 217                 } 218                 verifyLength(a, columnSize, fileLength); 219             } 220 221             rs = stmt.executeQuery("select a, b from testLongVarChar order by a"); 222             met = rs.getMetaData(); 223             // fetch all rows back in order, get the long varchar columns as streams. 224 while (rs.next()) { 225                 // get the first column as an int 226 int a = rs.getInt("a"); 227                 // get the second column as a stream 228 InputStream fin = rs.getAsciiStream(2); 229                 int columnSize = 0; 230                 for (;;) { 231                     int size = fin.read(buff); 232                     if (size == -1) 233                         break; 234                     columnSize += size; 235                 } 236                 verifyLength(a, columnSize, fileLength); 237             } 238 239             rs = stmt.executeQuery("select a, b from testLongVarChar"); 240             // fetch all rows back, get the long varchar columns as Strings. 241 while (rs.next()) 242             { 243                 // JDBC columns use 1-based counting 244 245                 // get the first column as an int 246 int a = rs.getInt("a"); 247 248                 // get the second column as a string 249 String resultString = rs.getString(2); 250                 verifyLength(a, resultString.length(), fileLength); 251             } 252 253             rs = stmt.executeQuery("select a, b from testLongVarChar order by a"); 254             // fetch all rows back in order, get the long varchar columns as Strings. 255 while (rs.next()) 256             { 257                 // JDBC columns use 1-based counting 258 259                 // get the first column as an int 260 int a = rs.getInt("a"); 261 262                 // get the second column as a string 263 String resultString = rs.getString(2); 264                 verifyLength(a, resultString.length(), fileLength); 265             } 266 267             rs = stmt.executeQuery( 268                 "select a, b from testLongVarChar where b like 'test data: a string column inserted as an object'"); 269             // should return one row. 270 while (rs.next()) 271             { 272                 // JDBC columns use 1-based counting 273 274                 // get the first column as an int 275 int a = rs.getInt("a"); 276 277                 // get the second column as a string 278 String resultString = rs.getString(2); 279                 verifyLength(a, resultString.length(), fileLength); 280             } 281 282             stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')"); 283             stmt.executeUpdate("create table foo (a int not null, b long varchar, primary key (a))"); 284             stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)"); 285             insertLongString(conn, 10, "ssssssssss", false); 286 287 288             insertLongString(conn, 0, "", false); 289             insertLongString(conn, 1, "1", false); 290             insertLongString(conn, -1, null, false); 291             insertLongString(conn, 20, "XXXXXXXXXXXXXXXXXXXX", false); 292 293             rs = stmt.executeQuery("select a, b from foo"); 294 295             System.out.println("expect to get null string back"); 296             while(rs.next()) 297             { 298                 int a = rs.getInt("a"); 299                 String resultString = rs.getString(2); 300                 if (resultString == null) 301                 { 302                     System.out.println("a = " + a + " got null string back"); 303                 } 304                 else if (resultString.length() != a) 305                 { 306                     System.out.println("FAIL - failed to get string back, expect "+ 307                                        a + " got " + resultString.length()); 308                 } 309             } 310 311             updateLongString(conn, 1, 3000); 312             updateLongString(conn, 0, 800); 313             updateLongString(conn, 3000, 0); 314             updateLongString(conn, 0, 51); 315             updateLongString(conn, 20, 0); 316             rs = stmt.executeQuery("select a, b from foo"); 317             while(rs.next()) 318             { 319                 int a = rs.getInt("a"); 320                 String resultString = rs.getString(2); 321                 if (resultString == null) 322                 { 323                     System.out.println("a = " + a + " got null string back"); 324                 } 325                 else if (resultString.length() != a) 326                 { 327                     System.out.println("FAIL - failed to get string back, expect "+ 328                                        a + " got " + resultString.length() + 329                                        " " + resultString); 330                 } 331             } 332 333             stmt.executeUpdate("drop table foo"); 334 335             rs.close(); 336             stmt.close(); 337 338         } 339         catch (SQLException e) { 340             dumpSQLExceptions(e); 341         } 342         catch (Throwable e) { 343             System.out.println("FAIL -- unexpected exception:" + e.toString()); 344         } 345     } 346 347     static void streamTest2(Connection conn, long length) throws Exception 348     { 349         Statement sourceStmt = conn.createStatement(); 350 351         sourceStmt.executeUpdate("create table foo (a int not null, b long varchar, primary key (a))"); 352 353         insertLongString(conn, 1, pad("Broadway", length), false); 354         insertLongString(conn, 2, pad("Franklin", length), false); 355         insertLongString(conn, 3, pad("Webster", length), false); 356 357         sourceStmt.executeUpdate("insert into foo select a+100, b from foo"); 358 359         verifyExistence(conn, 1, "Broadway", length); 360         verifyExistence(conn, 2, "Franklin", length); 361         verifyExistence(conn, 3, "Webster", length); 362         verifyExistence(conn, 101, "Broadway", length); 363         verifyExistence(conn, 102, "Franklin", length); 364         verifyExistence(conn, 103, "Webster", length); 365 366         sourceStmt.executeUpdate("drop table foo"); 367     } 368 369     static void streamTest3(Connection conn, long length) throws Exception 370     { 371         Statement sourceStmt = conn.createStatement(); 372         sourceStmt.executeUpdate("create table foo (a int not null constraint pk primary key, b long varchar)"); 373 374         insertLongString(conn, 1, pad("Broadway", length), false); 375         insertLongString(conn, 2, pad("Franklin", length), false); 376         insertLongString(conn, 3, pad("Webster", length), false); 377         PreparedStatement ps = conn.prepareStatement( 378             "update foo set a=a+1000, b=? where a<99 and a in (select a from foo)"); 379 380         File file = new File("extin/short.data"); 381         InputStream fileIn = new FileInputStream(file); 382         ps.setAsciiStream(1, fileIn, (int)(file.length())); 383         ps.executeUpdate(); 384         fileIn.close(); 385 386         ps = conn.prepareStatement( 387             "update foo set a=a+1000, b=? where a<99 and a in (select a from foo)"); 388         file = new File("extin/shortbanner"); 389         fileIn = new FileInputStream(file); 390         ps.setAsciiStream(1, fileIn, (int)(file.length())); 391         ps.executeUpdate(); 392         fileIn.close(); 393 394         sourceStmt.executeUpdate("drop table foo"); 395     } 396 397     private static void streamTest4(Connection conn) { 398 399         ResultSetMetaData met; 400         ResultSet rs; 401         Statement stmt; 402 403         try { 404             stmt = conn.createStatement(); 405             stmt.execute("create table testLongVarBinary (a int, b BLOB(1G))"); 406             // insert an empty string 407 stmt.execute("insert into testLongVarBinary values(1, CAST (" + 408                          TestUtil.stringToHexLiteral("") + "AS BLOB(1G)))"); 409             // insert a short text string 410 stmt.execute("insert into testLongVarBinary values(2,CAST (" + 411                          TestUtil.stringToHexLiteral("test data: a string column inserted as an object") + "AS BLOB(1G)))"); 412 413             for (int i = 0; i < fileName.length; i++) { 414                 // prepare an InputStream from the file 415 File file = new File(fileName[i]); 416                 fileLength[i] = file.length(); 417                 InputStream fileIn = new FileInputStream(file); 418 419                 System.out.println("===> testing " + fileName[i] + " length = " 420                                    + fileLength[i]); 421 422                 // insert a streaming column 423 PreparedStatement ps = conn.prepareStatement("insert into testLongVarBinary values(?, ?)"); 424                 ps.setInt(1, 100 + i); 425                 ps.setBinaryStream(2, fileIn, (int)fileLength[i]); 426                 ps.executeUpdate(); 427                 fileIn.close(); 428             } 429 430             rs = stmt.executeQuery("select a, b from testLongVarBinary"); 431             met = rs.getMetaData(); 432             byte[] buff = new byte[128]; 433             // fetch all rows back, get the long varchar columns as streams. 434 while (rs.next()) { 435                 // get the first column as an int 436 int a = rs.getInt("a"); 437                 // get the second column as a stream 438 InputStream fin = rs.getBinaryStream(2); 439                 int columnSize = 0; 440                 for (;;) { 441                     int size = fin.read(buff, 0, 100); 442                     if (size == -1) 443                         break; 444                     columnSize += size; 445                 } 446             } 447 448             rs = stmt.executeQuery("select a, b from testLongVarBinary order by a"); 449             met = rs.getMetaData(); 450             // fetch all rows back in order, get the long varchar columns as streams. 451 while (rs.next()) { 452                 // get the first column as an int 453 int a = rs.getInt("a"); 454                 // get the second column as a stream 455 InputStream fin = rs.getBinaryStream(2); 456                 int columnSize = 0; 457                 for (;;) { 458                     int size = fin.read(buff); 459                     if (size == -1) 460                         break; 461                     columnSize += size; 462                 } 463             } 464 465             rs = stmt.executeQuery("select a, b from testLongVarBinary"); 466             // fetch all rows back, get the long varchar columns as Strings. 467 while (rs.next()) 468             { 469                 // JDBC columns use 1-based counting 470 471                 // get the first column as an int 472 int a = rs.getInt("a"); 473 474                 // get the second column as a string 475 String resultString = rs.getString(2); 476             } 477 478             rs = stmt.executeQuery("select a, b from testLongVarBinary order by a"); 479             // fetch all rows back in order, get the long varchar columns as Strings. 480 while (rs.next()) 481             { 482                 // JDBC columns use 1-based counting 483 484                 // get the first column as an int 485 int a = rs.getInt("a"); 486 487                 // get the second column as a string 488 String resultString = rs.getString(2); 489             } 490 491             rs.close(); 492             stmt.close(); 493 494         } 495         catch (SQLException e) { 496             dumpSQLExceptions(e); 497         } 498         catch (Throwable e) { 499             System.out.println("FAIL -- unexpected exception:" + e.toString()); 500         } 501     } 502 503     static void streamTest5(Connection conn, long length) throws Exception 504     { 505         Statement sourceStmt = conn.createStatement(); 506         String binaryType = length > 32700 ? "BLOB(1G)" : "long varchar for bit data"; 507         sourceStmt.executeUpdate("create table foo (a int not null constraint pk primary key, b " + binaryType + " )"); 508 509         insertLongString(conn, 1, pad("Broadway", length), true); 510         insertLongString(conn, 2, pad("Franklin", length), true); 511         insertLongString(conn, 3, pad("Webster", length), true); 512         insertLongString(conn, 4, pad("Broadway", length), true); 513         insertLongString(conn, 5, pad("Franklin", length), true); 514         insertLongString(conn, 6, pad("Webster", length), true); 515         PreparedStatement ps = conn.prepareStatement( 516             "update foo set a=a+1000, b=? where a<99 and a in (select a from foo)"); 517         File file = new File("extin/short.data"); 518         InputStream fileIn = new FileInputStream(file); 519         ps.setBinaryStream(1, fileIn, (int)(file.length())); 520         ps.executeUpdate(); 521         fileIn.close(); 522 523         ps = conn.prepareStatement( 524             "update foo set a=a+1000, b=? where a<99 and a in (select a from foo)"); 525         file = new File("extin/shortbanner"); 526         fileIn = new FileInputStream(file); 527         ps.setBinaryStream(1, fileIn, (int)(file.length())); 528         ps.executeUpdate(); 529         ps.close(); 530         fileIn.close(); 531 532         sourceStmt.executeUpdate("drop table foo"); 533     } 534 535     static void streamTest6(Connection conn, long length) throws Exception 536     { 537         Statement sourceStmt = conn.createStatement(); 538         sourceStmt.executeUpdate("create table foo (a int not null constraint pk primary key, b long varchar)"); 539 540         insertLongString(conn, 1, pad("Broadway", length), false); 541         insertLongString(conn, 2, pad("Franklin", length), false); 542         insertLongString(conn, 3, pad("Webster", length), false); 543         PreparedStatement ps = conn.prepareStatement( 544             "update foo set a=a+1000, b=? where a<99 and a in (select a from foo)"); 545 546         streamInLongCol(ps, pad("Grand", length)); 547         ps.close(); 548         sourceStmt.close(); 549     } 550 551     static void streamTest7(Connection conn) throws Exception 552     { 553         conn.setAutoCommit(false); 554 555         System.out.println("streamTest7"); 556 557         Statement s = conn.createStatement(); 558         s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')"); 559         s.execute("create table testlvc (a int, b char(100), lvc long varchar, d char(100))"); 560         s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)"); 561         s.close(); 562         conn.commit(); 563 564         PreparedStatement ps1 = conn.prepareStatement( 565             "insert into testlvc values (?, 'filler for column b on null column', null, 'filler for column d')"); 566 567         PreparedStatement ps2 = conn.prepareStatement( 568             "insert into testlvc values (?, 'filler for column b on empty string column', ?, 'filler2 for column d')"); 569 570 571         for (int i= 0; i < 100; i++) 572         { 573             ps1.setInt(1, i); 574             ps1.executeUpdate(); 575 576             ByteArrayInputStream emptyString = new ByteArrayInputStream(new byte[0]); 577             ps2.setInt(1, i); 578             ps2.setAsciiStream(2, emptyString, 0); 579             ps2.executeUpdate(); 580         } 581         ps1.close(); 582         ps2.close(); 583 584         conn.commit(); 585 586         PreparedStatement ps = conn.prepareStatement("update testlvc set lvc = ? where a = ?"); 587 588         String longString = 589             "this is a relatively long string, hopefully the row will be split or otherwise become long ??? I don't think it will become long but maybe if it rolls back it will become strange"; 590         for (int i = 0; i < 100; i++) 591         { 592             ByteArrayInputStream string1 = new ByteArrayInputStream(longString.getBytes("US-ASCII")); 593             ps.setAsciiStream(1, string1, longString.length()); 594             ps.setInt(2, i); 595             ps.executeUpdate(); 596             if ((i % 2) == 0) 597                 conn.rollback(); 598             else 599                 conn.commit(); 600 601             ByteArrayInputStream emptyString = new ByteArrayInputStream(new byte[0]); 602             ps.setAsciiStream(1, emptyString, 0); 603             ps.executeUpdate(); 604             if ((i%3) == 0) 605                 conn.rollback(); 606             else 607                 conn.commit(); 608         } 609 610         ps.close(); 611     } 612 613     /** 614      * long row test of insert/backout case, using setAsciiStream(). 615      * <p> 616      * The heap tries to make rows all fit on one page if possible. So it 617      * first asks raw store to try inserting without overflowing rows or 618      * columns. If that doesn't work it then asks raw store for a mostly 619      * empty page and tries to insert it there with overflow, If that doesn't 620      * work then an empty page is picked. 621      * <p> 622      * If input parameters are conn,10,2500 - then the second row inserted 623      * will have the 1st column fit, but the second not fit which caused 624      * track #2240. 625      * 626      * @exception StandardException Standard exception policy. 627      **/ 628     static void streamTest8(Connection conn, int stream1_len, int stream2_len) 629     { 630         System.out.println( 631             "Starting streamTest8(conn, " + 632             stream1_len + ", " + stream2_len + ")"); 633 634         ResultSetMetaData met; 635         ResultSet rs; 636         Statement stmt; 637 638         String createsql = 639             new String ( 640                 "create table t8(a int, b long varchar, c long varchar)"); 641 642         String insertsql = new String ("insert into t8 values (?, ?, ?) "); 643 644 645         int numStrings = 10; 646 647         byte[][] stream1_byte_array = new byte[numStrings][]; 648         byte[][] stream2_byte_array = new byte[numStrings][]; 649 650         // make string size match input sizes. 651 for (int i = 0; i < numStrings; i++) 652         { 653             stream1_byte_array[i] = new byte[stream1_len]; 654 655             for (int j = 0; j < stream1_len; j++) 656                 stream1_byte_array[i][j] = (byte)('a'+i); 657 658             stream2_byte_array[i] = new byte[stream2_len]; 659             for (int j = 0; j < stream2_len; j++) 660                 stream2_byte_array[i][j] = (byte)('A'+i); 661         } 662 663         try 664         { 665             conn.setAutoCommit(false); 666             stmt = conn.createStatement(); 667             stmt.execute(createsql); 668             conn.commit(); 669 670             PreparedStatement insert_ps = conn.prepareStatement(insertsql); 671 672 673 674             for (int i = 0; i < numStrings; i++) 675             { 676                 // create the stream and insert it 677 insert_ps.setInt(1, i); 678 679                 // create the stream and insert it 680 insert_ps.setAsciiStream( 681                     2, new ByteArrayInputStream(stream1_byte_array[i]), stream1_len); 682 683                 // create the stream and insert it 684 insert_ps.setAsciiStream( 685                     3, new ByteArrayInputStream(stream2_byte_array[i]), stream2_len); 686 687                 insert_ps.executeUpdate(); 688 689                 // just force a scan of the table, no insert is done. 690 String checkSQL = 691                     "insert into t8 select * from t8 where a = -6363"; 692                 stmt.execute(checkSQL); 693             } 694 695             insert_ps.close(); 696             conn.commit(); 697 698 699             rs = stmt.executeQuery("select a, b, c from t8" ); 700 701             // should return one row. 702 while (rs.next()) 703             { 704                 // JDBC columns use 1-based counting 705 706                 // get the first column as an int 707 int a = rs.getInt("a"); 708 709                 // get the second column as a string 710 String resultString = rs.getString(2); 711 712                 // compare result with expected, using fixed length string from 713 // the streamed byte array 714 String canon = new String (stream1_byte_array[a], "US-ASCII"); 715 716                 if (canon.compareTo(resultString) != 0) 717                 { 718                     System.out.println( 719                         "FAIL -- bad result string:" + 720                         "canon: " + canon + 721                         "resultString: " + resultString); 722                 } 723 724                 // get the second column as a string 725 resultString = rs.getString(3); 726 727                 // compare result with expected, using fixed length string from 728 // the second streamed byte array. 729 canon = new String (stream2_byte_array[a], "US-ASCII"); 730 731                 if (canon.compareTo(resultString) != 0) 732                 { 733                     System.out.println( 734                         "FAIL -- bad result string:" + 735                         "canon: " + canon + 736                         "resultString: " + resultString); 737                 } 738             } 739 740             rs.close(); 741 742 743             stmt.execute("insert into t8 select * from t8"); 744 745             stmt.executeUpdate("drop table t8"); 746 747             stmt.close(); 748             conn.commit(); 749         } 750         catch (SQLException e) { 751             dumpSQLExceptions(e); 752         } 753         catch (Throwable e) { 754             System.out.println("FAIL -- unexpected exception:" + e.toString()); 755         } 756 757         System.out.println( 758             "Finishing streamTest8(conn, " + 759             stream1_len + ", " + stream2_len + ")"); 760     } 761 762     /** 763      * long row test of insert/backout case, using setBinaryStream(). 764      * <p> 765      * The heap tries to make rows all fit on one page if possible. So it 766      * first asks raw store to try inserting without overflowing rows or 767      * columns. If that doesn't work it then asks raw store for a mostly 768      * empty page and tries to insert it there with overflow, If that doesn't 769      * work then an empty page is picked. 770      * <p> 771      * If input parameters are conn,10,2500 - then the second row inserted 772      * will have the 1st column fit, but the second not fit which caused 773      * track #2240. 774      * 775      * @exception StandardException Standard exception policy. 776      **/ 777     static void streamTest9(Connection conn, int stream1_len, int stream2_len) 778     { 779         System.out.println( 780             "Starting streamTest9(conn, " + 781             stream1_len + ", " + stream2_len + ")"); 782 783         ResultSetMetaData met; 784         ResultSet rs; 785         Statement stmt; 786 787         String createsql = 788             new String ( 789                 "create table t9(a int, b long varchar for bit data, c long varchar for bit data)"); 790 791         String insertsql = new String ("insert into t9 values (?, ?, ?) "); 792 793 794         int numStrings = 10; 795 796         byte[][] stream1_byte_array = new byte[numStrings][]; 797         byte[][] stream2_byte_array = new byte[numStrings][]; 798 799         // make string size match input sizes. 800 for (int i = 0; i < numStrings; i++) 801         { 802             stream1_byte_array[i] = new byte[stream1_len]; 803 804             for (int j = 0; j < stream1_len; j++) 805                 stream1_byte_array[i][j] = (byte)('a'+i); 806 807             stream2_byte_array[i] = new byte[stream2_len]; 808             for (int j = 0; j < stream2_len; j++) 809                 stream2_byte_array[i][j] = (byte)('A'+i); 810         } 811 812         try 813         { 814             conn.setAutoCommit(false); 815             stmt = conn.createStatement(); 816             stmt.execute(createsql); 817             conn.commit(); 818 819             PreparedStatement insert_ps = conn.prepareStatement(insertsql); 820 821 822 823             for (int i = 0; i < numStrings; i++) 824             { 825                 // create the stream and insert it 826 insert_ps.setInt(1, i); 827 828                 // create the stream and insert it 829 insert_ps.setBinaryStream( 830                     2, new ByteArrayInputStream(stream1_byte_array[i]), stream1_len); 831 832                 // create the stream and insert it 833 insert_ps.setBinaryStream( 834                     3, new ByteArrayInputStream(stream2_byte_array[i]), stream2_len); 835 836                 insert_ps.executeUpdate(); 837 838                 // just force a scan of the table, no insert is done. 839 String checkSQL = 840                     "insert into t9 select * from t9 where a = -6363"; 841                 stmt.execute(checkSQL); 842             } 843 844             insert_ps.close(); 845             conn.commit(); 846 847 848             rs = stmt.executeQuery("select a, b, c from t9" ); 849 850             // should return one row. 851 while (rs.next()) 852             { 853                 // JDBC columns use 1-based counting 854 855                 // get the first column as an int 856 int a = rs.getInt("a"); 857 858                 // get the second column as a string 859 byte[] resultString = rs.getBytes(2); 860 861                 // compare result with expected 862 byte[] canon = stream1_byte_array[a]; 863 864                 if (!byteArrayEquals( 865                         canon, 0, canon.length, 866                         resultString, 0, resultString.length)) 867                 { 868                     // System.out.println( 869 // "FAIL -- bad result byte array 1:" + 870 // "canon: " + ByteArray.hexDump(canon) + 871 // "resultString: " + ByteArray.hexDump(resultString)); 872 System.out.println( 873                         "FAIL -- bad result byte array 1:" + 874                         "canon: " + canon + 875                         "resultString: " + resultString); 876                 } 877 878                 // get the second column as a string 879 resultString = rs.getBytes(3); 880 881                 // compare result with expected 882 canon = stream2_byte_array[a]; 883 884                 if (!byteArrayEquals( 885                         canon, 0, canon.length, 886                         resultString, 0, resultString.length)) 887                 { 888                     // System.out.println( 889 // "FAIL -- bad result byte array 2:" + 890 // "canon: " + ByteArray.hexDump(canon) + 891 // "resultString: " + ByteArray.hexDump(resultString)); 892 System.out.println( 893                         "FAIL -- bad result byte array 2:" + 894                         "canon: " + canon + 895                         "resultString: " + resultString); 896                 } 897             } 898 899             rs.close(); 900 901             stmt.execute("insert into t9 select * from t9"); 902 903             stmt.executeUpdate("drop table t9"); 904 905             stmt.close(); 906             conn.commit(); 907         } 908         catch (SQLException e) { 909             dumpSQLExceptions(e); 910         } 911         catch (Throwable e) { 912             System.out.println("FAIL -- unexpected exception:" + e.toString()); 913         } 914 915         System.out.println( 916             "Finishing streamTest9(conn, " + 917             stream1_len + ", " + stream2_len + ")"); 918     } 919 920     /** 921      * table with multiple indexes, indexes share columns 922      * table has more than 4 rows, insert stream into table 923      * compress table and verify that each index is valid 924      * @exception StandardException Standard exception policy. 925      **/ 926     private static void streamTest10(Connection conn) { 927 928         ResultSetMetaData met; 929         ResultSet rs; 930         Statement stmt; 931         System.out.println("Testing 10 starts from here"); 932 933         try { 934             stmt = conn.createStatement(); 935             //create the table 936 stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024')"); 937             stmt.execute("create table tab10 (a int, b int, c long varchar)"); 938             stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)"); 939             //create the indexes which shares columns 940 stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')"); 941             stmt.execute("create index i_a on tab10 (a)"); 942             stmt.execute("create index i_ab on tab10 (a, b)"); 943             stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)"); 944 945             // insert a null long varchar 946 stmt.execute("insert into tab10 values(1, 1, '')"); 947             // insert a long varchar with a short text string 948 stmt.execute("insert into tab10 values(2, 2, 'test data: a string column inserted as an object')"); 949 950             //insert stream into table 951 for (int i = 0; i < fileName.length; i++) { 952                 // prepare an InputStream from the file 953 File file = new File(fileName[i]); 954                 fileLength[i] = file.length(); 955                 InputStream fileIn = new FileInputStream(file); 956 957                 System.out.println("===> testing " + fileName[i] + " length = " 958                                    + fileLength[i]); 959 960                 // insert a streaming column 961 PreparedStatement ps = conn.prepareStatement("insert into tab10 values(?, ?, ?)"); 962                 ps.setInt(1, 100 + i); 963                 ps.setInt(2, 100 + i); 964                 ps.setAsciiStream(3, fileIn, (int)fileLength[i]); 965                 try {//if trying to insert data > 32700, there will be an exception 966 ps.executeUpdate(); 967                     System.out.println("No truncation and hence no error"); 968                 } 969                 catch (SQLException e) { 970                     if (fileLength[i] > Limits.DB2_LONGVARCHAR_MAXWIDTH && e.getSQLState().equals("22001")) //was getting data longer than maxValueAllowed 971 System.out.println("expected exception for data > " + Limits.DB2_LONGVARCHAR_MAXWIDTH + " in length"); 972                     else 973                         dumpSQLExceptions(e); 974                 } 975                 fileIn.close(); 976             } 977 978             //execute the compress command 979 CallableStatement cs = conn.prepareCall( 980                 "CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)"); 981             cs.setString(1, "APP"); 982             cs.setString(2, "TESTLONGVARCHAR"); 983             cs.setInt(3, 0); 984             cs.execute(); 985 986             //do consistency checking 987 stmt.execute("CREATE FUNCTION ConsistencyChecker() RETURNS VARCHAR(128) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.runConsistencyChecker' LANGUAGE JAVA PARAMETER STYLE JAVA"); 988             stmt.execute("VALUES ConsistencyChecker()"); 989 990             stmt.close(); 991 992         } 993         catch (SQLException e) { 994             dumpSQLExceptions(e); 995         } 996         catch (Throwable e) { 997             System.out.println("FAIL -- unexpected exception:" + e.toString()); 998         } 999         System.out.println("Testing 10 ends in here"); 1000    } 1001 1002    private static void streamTest11(Connection conn) { 1003 1004        Statement stmt; 1005 1006        System.out.println("Test 11 - Can't pass negative length as the stream length for various setXXXStream methods"); 1007        try { 1008            stmt = conn.createStatement(); 1009            stmt.execute("create table testLongVarCharInvalidStreamLength (a int, b long varchar, c long varchar for bit data)"); 1010            // prepare an InputStream from the file 1011 File file = new File("extin/short.data"); 1012            InputStream fileIn = new FileInputStream(file); 1013 1014            PreparedStatement ps = conn.prepareStatement("insert into testLongVarCharInvalidStreamLength values(?, ?, ?)"); 1015            ps.setInt(1, 100); 1016            try { 1017                System.out.println("===> testing using setAsciiStream with -2 as length"); 1018                ps.setAsciiStream(2, fileIn, -2); //test specifically for bug 4250 1019 System.out.println("FAIL -- should have gotten exception for -2 param value to setAsciiStream"); 1020            } 1021            catch (SQLException e) { 1022                if ("XJ025".equals(e.getSQLState())) 1023                    System.out.println("PASS -- expected exception:" + e.toString()); 1024                else 1025                    dumpSQLExceptions(e); 1026            } 1027 1028            Reader filer = new InputStreamReader(fileIn,"US-ASCII"); 1029            try { 1030                System.out.println("===> testing using setCharacterStream with -1 as length"); 1031                ps.setCharacterStream(2, filer, -1); 1032                System.out.println("FAIL -- should have gotten exception for -1 param value to setCharacterStream"); 1033            } 1034            catch (SQLException e) { 1035                if ("XJ025".equals(e.getSQLState())) 1036                    System.out.println("PASS -- expected exception:" + e.toString()); 1037                else 1038                    dumpSQLExceptions(e); 1039            } 1040 1041            try { 1042                System.out.println("===> testing using setBinaryStream with -1 as length"); 1043                ps.setBinaryStream(3, fileIn, -1); 1044                System.out.println("FAIL -- should have gotten exception for -1 param value to setBinaryStream"); 1045            } 1046            catch (SQLException e) { 1047                if ("XJ025".equals(e.getSQLState())) 1048                    System.out.println("PASS -- expected exception:" + e.toString()); 1049                else 1050                    dumpSQLExceptions(e); 1051            } 1052 1053            fileIn.close(); 1054        } 1055        catch (SQLException e) { 1056                dumpSQLExceptions(e); 1057        } 1058        catch (Throwable e) { 1059            System.out.println("FAIL -- unexpected exception:" + e.toString()); 1060        } 1061        System.out.println("Test 11 - negative stream length tests end in here"); 1062    } 1063 1064    private static void streamTest12(Connection conn) { 1065 1066        ResultSet rs; 1067        Statement stmt; 1068 1069        //The following 2 files are for testing the truncation in varchar. 1070 //only non-blank character truncation will throw an exception for varchars. 1071 //max value allowed in varchars is 32672 characters long 1072 String fileName1 = "extin/char32675trailingblanks.data"; // set up a file 32675 characters long but with last 3 characters as blanks 1073 String fileName2 = "extin/char32675.data"; // set up a file 32675 characters long with 3 extra non-blank characters trailing in the end 1074 1075        System.out.println("Test 12 - varchar truncation tests start from here"); 1076        try { 1077            stmt = conn.createStatement(); 1078            stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')"); 1079            stmt.execute("create table testVarChar (a int, b varchar(32672))"); 1080            //create a table with 4 varchars. This table will be used to try overflow through concatenation 1081 stmt.execute("create table testConcatenation (a varchar(16350), b varchar(16350), c varchar(16336), d varchar(16336))"); 1082            stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)"); 1083            String largeStringA16350 = new String (Formatters.repeatChar("a",16350)); 1084            String largeStringA16336 = new String (Formatters.repeatChar("a",16336)); 1085            PreparedStatement ps = conn.prepareStatement("insert into testConcatenation values (?, ?, ?, ?)"); 1086            ps.setString(1, largeStringA16350); 1087            ps.setString(2, largeStringA16350); 1088            ps.setString(3, largeStringA16336); 1089            ps.setString(4, largeStringA16336); 1090            ps.executeUpdate(); 1091 1092            ps = conn.prepareStatement("insert into testVarChar values(?, ?)"); 1093 1094            // prepare an InputStream from the file which has 3 trailing blanks in the end, so after blank truncation, there won't be any overflow 1095 // try this using setAsciiStream, setCharacterStream, setString and setObject 1096 insertDataUsingAsciiStream(ps, 1, fileName1, Limits.DB2_VARCHAR_MAXWIDTH); 1097            insertDataUsingCharacterStream(ps, 2, fileName1, Limits.DB2_VARCHAR_MAXWIDTH); 1098            insertDataUsingStringOrObject(ps, 3, Limits.DB2_VARCHAR_MAXWIDTH, true, true); 1099            insertDataUsingStringOrObject(ps, 4, Limits.DB2_VARCHAR_MAXWIDTH, true, false); 1100            System.out.println("===> testing trailing blanks using concatenation"); 1101            insertDataUsingConcat(stmt, 5, Limits.DB2_VARCHAR_MAXWIDTH, true, VARCHAR); 1102 1103            // prepare an InputStream from the file which has 3 trailing non-blanks in the end, and hence there would be overflow exception 1104 // try this using setAsciiStream, setCharacterStream, setString and setObject 1105 insertDataUsingAsciiStream(ps, 6, fileName2, Limits.DB2_VARCHAR_MAXWIDTH); 1106            insertDataUsingCharacterStream(ps, 7, fileName2, Limits.DB2_VARCHAR_MAXWIDTH); 1107            insertDataUsingStringOrObject(ps, 8, Limits.DB2_VARCHAR_MAXWIDTH, false, true); 1108            insertDataUsingStringOrObject(ps, 9, Limits.DB2_VARCHAR_MAXWIDTH, false, false); 1109            System.out.println("===> testing trailing non-blank characters using concatenation"); 1110            insertDataUsingConcat(stmt, 10, Limits.DB2_VARCHAR_MAXWIDTH, false, VARCHAR); 1111 1112            rs = stmt.executeQuery("select a, b from testVarChar"); 1113            streamTestDataVerification(rs, Limits.DB2_VARCHAR_MAXWIDTH); 1114    } 1115        catch (SQLException e) { 1116            dumpSQLExceptions(e); 1117        } 1118        catch (Throwable e) { 1119            System.out.println("FAIL -- unexpected exception:" + e.toString()); 1120        } 1121        System.out.println("Test 12 - varchar truncation tests end in here"); 1122    } 1123 1124    private static void streamTest13(Connection conn) { 1125 1126        ResultSet rs; 1127        Statement stmt; 1128 1129        //The following 2 files are for testing the truncation in long varchar. 1130 //any character truncation (including blanks characters) will throw an exception for long varchars. 1131 //max value allowed in long varchars is 32700 characters long 1132 String fileName1 = "extin/char32703trailingblanks.data"; // set up a file 32703 characters long but with last 3 characters as blanks 1133 String fileName2 = "extin/char32703.data"; // set up a file 32703 characters long with 3 extra non-blank characters trailing in the end 1134 1135        System.out.println("Test 13 - long varchar truncation tests start from here"); 1136        try { 1137            stmt = conn.createStatement(); 1138            stmt.execute("create table testLongVarChars (a int, b long varchar)"); 1139            PreparedStatement ps = conn.prepareStatement("insert into testLongVarChars values(?, ?)"); 1140 1141            // prepare an InputStream from the file which has 3 trailing blanks in the end. For long varchar, this would throw a truncation error 1142 // try this using setAsciiStream, setCharacterStream, setString and setObject 1143 insertDataUsingAsciiStream(ps, 1, fileName1, Limits.DB2_LONGVARCHAR_MAXWIDTH); 1144            insertDataUsingCharacterStream(ps, 2, fileName1, Limits.DB2_LONGVARCHAR_MAXWIDTH); 1145            insertDataUsingStringOrObject(ps, 3, Limits.DB2_LONGVARCHAR_MAXWIDTH, true, true); 1146            insertDataUsingStringOrObject(ps, 4, Limits.DB2_LONGVARCHAR_MAXWIDTH, true, false); 1147            //bug 5600- Can't test data overflow in longvarchar using concatenation because longvarchar concatenated string can't be longer than 32700 1148 //System.out.println("===> testing trailing blanks using concatenation"); 1149 //insertDataUsingConcat(stmt, 5, Limits.DB2_LONGVARCHAR_MAXWIDTH, true, true); 1150 1151            // prepare an InputStream from the file which has 3 trailing non-blanks in the end, and hence there would be overflow exception 1152 // try this using setAsciiStream, setCharacterStream, setString and setObject 1153 insertDataUsingAsciiStream(ps, 6, fileName2, Limits.DB2_LONGVARCHAR_MAXWIDTH); 1154            insertDataUsingCharacterStream(ps, 7, fileName2, Limits.DB2_LONGVARCHAR_MAXWIDTH); 1155            insertDataUsingStringOrObject(ps, 7, Limits.DB2_LONGVARCHAR_MAXWIDTH, false, true); 1156            insertDataUsingStringOrObject(ps, 9, Limits.DB2_LONGVARCHAR_MAXWIDTH, false, false); 1157            //bug 5600 - Can't test data overflow in longvarchar using concatenation because longvarchar concatenated string can't be longer than 32700 1158 //System.out.println("===> testing trailing non-blank characters using concatenation"); 1159 //insertDataUsingConcat(stmt, 10, Limits.DB2_LONGVARCHAR_MAXWIDTH, false, true); 1160 1161            rs = stmt.executeQuery("select a, b from testLongVarChars"); 1162            streamTestDataVerification(rs, Limits.DB2_LONGVARCHAR_MAXWIDTH); 1163        } 1164        catch (SQLException e) { 1165            dumpSQLExceptions(e); 1166        } 1167        catch (Throwable e) { 1168            System.out.println("FAIL -- unexpected exception:" + e.toString()); 1169        } 1170        System.out.println("Test 13 - long varchar truncation tests end in here"); 1171    } 1172 1173 1174    /** 1175     * Test truncation behavior for clobs 1176     * Test is similar to streamTest12 except that this test tests for clob column 1177     * @param conn 1178     */ 1179    private static void streamTest14(Connection conn) { 1180 1181        ResultSet rs; 1182        Statement stmt; 1183 1184        //The following 2 files are for testing the truncation in clob 1185 //only non-blank character truncation will throw an exception for clob. 1186 //max value allowed in clob is 2G-1 1187 String fileName1 = "extin/char32675trailingblanks.data"; // set up a file 32675 characters long but with last 3 characters as blanks 1188 String fileName2 = "extin/char32675.data"; // set up a file 32675 characters long with 3 extra non-blank characters trailing in the end 1189 1190        System.out.println("Test 14 - clob truncation tests start from here"); 1191        try { 1192            stmt = conn.createStatement(); 1193            stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')"); 1194            stmt.execute("drop table testConcatenation"); 1195            stmt.execute("create table testClob (a int, b clob(32672))"); 1196            //create a table with 4 varchars. This table will be used to try overflow through concatenation 1197 1198            stmt.execute("create table testConcatenation (a clob(16350), b clob(16350), c clob(16336), d clob(16336))"); 1199            stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)"); 1200            String largeStringA16350 = new String (Formatters.repeatChar("a",16350)); 1201            String largeStringA16336 = new String (Formatters.repeatChar("a",16336)); 1202            PreparedStatement ps = conn.prepareStatement("insert into testConcatenation values (?, ?, ?, ?)"); 1203            ps.setString(1, largeStringA16350); 1204            ps.setString(2, largeStringA16350); 1205            ps.setString(3, largeStringA16336); 1206            ps.setString(4, largeStringA16336); 1207            ps.executeUpdate(); 1208 1209            ps = conn.prepareStatement("insert into testClob values(?, ?)"); 1210 1211            // prepare an InputStream from the file which has 3 trailing blanks in the end, so after blank truncation, there won't be any overflow 1212 // try this using setAsciiStream, setCharacterStream, setString and setObject 1213 insertDataUsingAsciiStream(ps, 1, fileName1, Limits.DB2_VARCHAR_MAXWIDTH); 1214            insertDataUsingCharacterStream(ps, 2, fileName1, Limits.DB2_VARCHAR_MAXWIDTH); 1215            insertDataUsingStringOrObject(ps, 3, Limits.DB2_VARCHAR_MAXWIDTH, true, true); 1216            insertDataUsingStringOrObject(ps, 4, Limits.DB2_VARCHAR_MAXWIDTH, true, false); 1217            System.out.println("===> testing trailing blanks using concatenation"); 1218            insertDataUsingConcat(stmt, 5, Limits.DB2_VARCHAR_MAXWIDTH, true, CLOB); 1219 1220            // prepare an InputStream from the file which has 3 trailing non-blanks in the end, and hence there would be overflow exception 1221 // try this using setAsciiStream, setCharacterStream, setString and setObject 1222 insertDataUsingAsciiStream(ps, 6, fileName2, Limits.DB2_VARCHAR_MAXWIDTH); 1223            insertDataUsingCharacterStream(ps, 7, fileName2, Limits.DB2_VARCHAR_MAXWIDTH); 1224            insertDataUsingStringOrObject(ps, 8, Limits.DB2_VARCHAR_MAXWIDTH, false, true); 1225            insertDataUsingStringOrObject(ps, 9, Limits.DB2_VARCHAR_MAXWIDTH, false, false); 1226            System.out.println("===> testing trailing non-blank characters using concatenation"); 1227            insertDataUsingConcat(stmt, 10, Limits.DB2_VARCHAR_MAXWIDTH, false, CLOB); 1228 1229            rs = stmt.executeQuery("select a, b from testVarChar"); 1230            streamTestDataVerification(rs, Limits.DB2_VARCHAR_MAXWIDTH); 1231    } 1232        catch (SQLException e) { 1233            dumpSQLExceptions(e); 1234        } 1235        catch (Throwable e) { 1236            System.out.println("FAIL -- unexpected exception:" + e.toString()); 1237        } 1238        System.out.println("Test 14 - clob truncation tests end in here"); 1239    } 1240 1241 1242    /** 1243     * Streams are not re-used. This test tests the fix for 1244     * DERBY-500. If an update statement has multiple rows that 1245     * is affected, and one of the parameter values is a stream, 1246     * the update will fail because streams are not re-used. 1247     * @param conn database connection 1248     */ 1249    private static void derby500Test(Connection conn) { 1250 1251        Statement stmt; 1252 1253        System.out.println("======================================"); 1254        System.out.println("START DERBY-500 TEST "); 1255 1256        try { 1257            stmt = conn.createStatement(); 1258            conn.setAutoCommit(false); 1259            stmt.execute("CREATE TABLE t1 (" + "id INTEGER NOT NULL," 1260                    + "mname VARCHAR( 254 ) NOT NULL," + "mvalue INT NOT NULL," 1261                    + "bytedata BLOB NOT NULL," + "chardata CLOB NOT NULL," 1262                    + "PRIMARY KEY ( id ))"); 1263 1264            PreparedStatement ps = conn 1265                    .prepareStatement("insert into t1 values (?,?,?,?,?)"); 1266 1267            // insert 10 rows. 1268 int rowCount = 0; 1269            // use blob and clob values 1270 int len = 10000; 1271            byte buf[] = new byte[len]; 1272            char cbuf[] = new char[len]; 1273            char orig = 'c'; 1274            for (int i = 0; i < len; i++) { 1275                buf[i] = (byte)orig; 1276                cbuf[i] = orig; 1277            } 1278            int randomOffset = 9998; 1279            buf[randomOffset] = (byte) 'e'; 1280            cbuf[randomOffset] = 'e'; 1281            System.out.println("Inserting rows "); 1282            for (int i = 0; i < 10; i++) { 1283                ps.setInt(1, i); 1284                ps.setString(2, "mname" + i); 1285                ps.setInt(3, 0); 1286                ps.setBinaryStream(4, new ByteArrayInputStream(buf), len); 1287                ps.setAsciiStream(5, new ByteArrayInputStream(buf), len); 1288                rowCount += ps.executeUpdate(); 1289            } 1290            conn.commit(); 1291            System.out.println("Rows inserted =" + rowCount); 1292 1293             1294            //conn.commit(); 1295 PreparedStatement pss = conn 1296                    .prepareStatement(" select chardata,bytedata from t1 where id = ?"); 1297            verifyDerby500Test(pss, buf, cbuf,0, 10, true); 1298             1299            // do the update, update must qualify more than 1 row and update will fail 1300 // as currently we dont allow stream values to be re-used 1301 PreparedStatement psu = conn 1302                    .prepareStatement("update t1 set bytedata = ? " 1303                            + ", chardata = ? where mvalue = ? "); 1304 1305            buf[randomOffset + 1] = (byte) 'u'; 1306            cbuf[randomOffset +1] = 'u'; 1307            rowCount = 0; 1308            System.out.println("Update qualifies many rows + streams"); 1309 1310            try { 1311                psu.setBinaryStream(1, new ByteArrayInputStream(buf), len); 1312                psu.setCharacterStream(2, new CharArrayReader(cbuf), len); 1313                psu.setInt(3, 0); 1314                rowCount += psu.executeUpdate(); 1315                System.out.println("DERBY500 #1 Rows updated =" 1316                        + rowCount); 1317 1318            } catch (SQLException sqle) { 1319                System.out 1320                        .println("EXPECTED EXCEPTION - streams cannot be re-used"); 1321                expectedException(sqle); 1322                conn.rollback(); 1323            } 1324             1325            //verify data 1326 //set back buffer value to what was inserted. 1327 buf[randomOffset + 1] = (byte)orig; 1328            cbuf[randomOffset + 1] = orig; 1329             1330            verifyDerby500Test(pss, buf,cbuf, 0, 10,true); 1331 1332            PreparedStatement psu2 = conn 1333                    .prepareStatement("update t1 set bytedata = ? " 1334                            + ", chardata = ? where id = ? "); 1335 1336            buf[randomOffset + 1] = (byte) 'u'; 1337            cbuf[randomOffset + 1] = 'u'; 1338             1339            rowCount = 0; 1340            try { 1341                psu2.setBinaryStream(1, new ByteArrayInputStream(buf), len); 1342                psu2.setAsciiStream(2, new ByteArrayInputStream(buf), len); 1343                psu2.setInt(3, 0); 1344                rowCount += psu2.executeUpdate(); 1345                System.out.println("DERBY500 #2 Rows updated =" 1346                        + rowCount); 1347 1348            } catch (SQLException sqle) { 1349                System.out 1350                        .println("UNEXPECTED EXCEPTION - update should have actually gone through"); 1351                dumpSQLExceptions(sqle); 1352            } 1353            conn.commit(); 1354            verifyDerby500Test(pss, buf,cbuf, 0, 1,true); 1355             1356            // delete 1357 // as currently we dont allow stream values to be re-used 1358 PreparedStatement psd = conn 1359                    .prepareStatement("delete from t1 where mvalue = ?"); 1360 1361            rowCount = 0; 1362            try { 1363                psd.setInt(1, 0); 1364                rowCount += psd.executeUpdate(); 1365                rowCount += psd.executeUpdate(); 1366                System.out.println("DERBY500 #3 Rows deleted =" 1367                        + rowCount); 1368 1369            } catch (SQLException sqle) { 1370                System.out 1371                .println("UNEXPECTED EXCEPTION - delete should have actually gone through"); 1372                dumpSQLExceptions(sqle); 1373            } 1374 1375            conn.commit(); 1376            //verify data 1377 1378            verifyDerby500Test(pss, buf,cbuf, 0, 10, true); 1379 1380            PreparedStatement psd2 = conn 1381                    .prepareStatement("delete from t1 where id = ?"); 1382             1383            rowCount = 0; 1384            try { 1385                psd2.setInt(1, 0); 1386                rowCount += psd2.executeUpdate(); 1387                System.out.println("DERBY500 #4 Rows deleted =" 1388                        + rowCount); 1389 1390            } catch (SQLException sqle) { 1391                System.out 1392                        .println("UNEXPECTED EXCEPTION - delete should have actually gone through"); 1393                dumpSQLExceptions(sqle); 1394            } 1395            conn.commit(); 1396            verifyDerby500Test(pss, buf,cbuf, 1, 2,true); 1397 1398            try 1399            { 1400                ps.setInt(1,11); 1401                rowCount += ps.executeUpdate(); 1402                System.out.println("Rows inserted = "+ rowCount); 1403            } catch (SQLException sqle) { 1404                System.out 1405                        .println("EXPECTED EXCEPTION - streams cannot be re-used"); 1406                expectedException(sqle); 1407                conn.rollback(); 1408            } 1409 1410            stmt.execute("drop table t1"); 1411            conn.commit(); 1412            stmt.close(); 1413            pss.close(); 1414            psu2.close(); 1415            psu.close(); 1416            psd.close(); 1417            psd2.close(); 1418            System.out.println("END DERBY-500 TEST "); 1419            System.out.println("======================================"); 1420 1421        } catch (SQLException sqle) { 1422            dumpSQLExceptions(sqle); 1423        } catch (Exception e) { 1424            System.out.println("DERBY-500 TEST FAILED!"); 1425            e.printStackTrace(); 1426        } 1427 1428    } 1429 1430    /** 1431     * Test that DERBY500 fix did not change the behavior for varchar, 1432     * char, long varchar types when stream api is used. 1433     * Currently, for char,varchar and long varchar - the stream is 1434     * read once and materialized, hence the materialized stream value 1435     * will/can be used for multiple executions of the prepared statement 1436     * @param conn database connection 1437     */ 1438    private static void derby500_verifyVarcharStreams(Connection conn) { 1439 1440        Statement stmt; 1441 1442        System.out.println("======================================"); 1443        System.out.println("START DERBY-500 TEST for varchar "); 1444 1445        try { 1446            stmt = conn.createStatement(); 1447            stmt.execute("CREATE TABLE t1 (" + "id INTEGER NOT NULL," 1448                    + "mname VARCHAR( 254 ) NOT NULL," + "mvalue INT NOT NULL," 1449                    + "vc varchar(32500)," + "lvc long varchar NOT NULL," 1450                    + "PRIMARY KEY ( id ))"); 1451 1452            PreparedStatement ps = conn 1453                    .prepareStatement("insert into t1 values (?,?,?,?,?)"); 1454 1455            // insert 10 rows. 1456 int rowCount = 0; 1457            // use blob and clob values 1458 int len = 10000; 1459            byte buf[] = new byte[len]; 1460            char cbuf[] = new char[len]; 1461            char orig = 'c'; 1462            for (int i = 0; i < len; i++) { 1463                buf[i] = (byte)orig; 1464                cbuf[i] = orig; 1465            } 1466            int randomOffset = 9998; 1467            buf[randomOffset] = (byte)'e'; 1468            cbuf[randomOffset] = 'e'; 1469            for (int i = 0; i < 10; i++) { 1470                ps.setInt(1, i); 1471                ps.setString(2, "mname" + i); 1472                ps.setInt(3, 0); 1473                ps.setCharacterStream(4, new CharArrayReader(cbuf), len); 1474                ps.setAsciiStream(5, new ByteArrayInputStream(buf), len); 1475                rowCount += ps.executeUpdate(); 1476            } 1477            conn.commit(); 1478            System.out.println("Rows inserted =" + rowCount); 1479 1480            try 1481            { 1482                ps.setInt(1,11); 1483                rowCount += ps.executeUpdate(); 1484            } catch (SQLException sqle) { 1485                System.out.println("UNEXPECTED EXCEPTION - streams cannot be "+ 1486                   "re-used but in case of varchar, stream is materialized the"+ 1487                   " first time around. So multiple executions using streams should "+ 1488                   " work fine. "); 1489                dumpSQLExceptions(sqle); 1490            } 1491             1492            PreparedStatement pss = conn 1493                    .prepareStatement(" select lvc,vc from t1 where id = ?"); 1494            verifyDerby500Test(pss, buf, cbuf,0, 10,false); 1495             1496            // do the update, update must qualify more than 1 row and update will 1497 // pass for char,varchar,long varchar columns. 1498 PreparedStatement psu = conn 1499                    .prepareStatement("update t1 set vc = ? " 1500                            + ", lvc = ? where mvalue = ? "); 1501 1502            buf[randomOffset +1] = (byte)'u'; 1503            cbuf[randomOffset +1] = 'u'; 1504            rowCount = 0; 1505            try { 1506                psu.setAsciiStream(1, new ByteArrayInputStream(buf), len); 1507                psu.setCharacterStream(2, new CharArrayReader(cbuf), len); 1508                psu.setInt(3, 0); 1509                rowCount += psu.executeUpdate(); 1510            } catch (SQLException sqle) { 1511                System.out 1512                        .println("EXPECTED EXCEPTION - streams cannot be re-used"); 1513                expectedException(sqle); 1514            } 1515            System.out.println("DERBY500 for varchar #1 Rows updated =" 1516                    + rowCount); 1517 1518            //verify data 1519 verifyDerby500Test(pss, buf,cbuf, 0, 10, false); 1520 1521            PreparedStatement psu2 = conn 1522                    .prepareStatement("update t1 set vc = ? " 1523                            + ", lvc = ? where id = ? "); 1524 1525            buf[randomOffset +1] = (byte)'h'; 1526            cbuf[randomOffset + 1] = 'h'; 1527             1528            rowCount = 0; 1529            try { 1530                psu2.setAsciiStream(1, new ByteArrayInputStream(buf), len); 1531                psu2.setAsciiStream(2, new ByteArrayInputStream(buf), len); 1532                psu2.setInt(3, 0); 1533                rowCount += psu2.executeUpdate(); 1534            } catch (SQLException sqle) { 1535                System.out 1536                        .println("UNEXPECTED EXCEPTION - update should have actually gone through"); 1537                dumpSQLExceptions(sqle); 1538            } 1539            conn.commit(); 1540            System.out.println("DERBY500 for varchar #2 Rows updated =" 1541                    + rowCount); 1542            verifyDerby500Test(pss, buf,cbuf, 0, 1,false); 1543             1544            // delete 1545 // as currently we dont allow stream values to be re-used 1546 PreparedStatement psd = conn 1547                    .prepareStatement("delete from t1 where mvalue = ?"); 1548 1549            rowCount = 0; 1550            try { 1551                psd.setInt(1, 0); 1552                rowCount += psd.executeUpdate(); 1553                rowCount += psd.executeUpdate(); 1554            } catch (SQLException sqle) { 1555                System.out 1556                .println("UNEXPECTED EXCEPTION - delete should have actually gone through"); 1557                dumpSQLExceptions(sqle); 1558            } 1559            System.out.println("DERBY500 for varchar #3 Rows deleted =" 1560                    + rowCount); 1561 1562            //verify data 1563 verifyDerby500Test(pss, buf,cbuf, 0, 10,false); 1564 1565            PreparedStatement psd2 = conn 1566                    .prepareStatement("delete from t1 where id = ?"); 1567             1568            rowCount = 0; 1569            try { 1570                psd2.setInt(1, 0); 1571                rowCount += psd2.executeUpdate(); 1572            } catch (SQLException sqle) { 1573                System.out 1574                        .println("UNEXPECTED EXCEPTION - delete should have actually gone through"); 1575                dumpSQLExceptions(sqle); 1576            } 1577            conn.commit(); 1578            System.out.println("DERBY500 for varchar #4 Rows deleted =" 1579                    + rowCount); 1580            verifyDerby500Test(pss, buf,cbuf, 1, 2,false); 1581 1582            stmt.execute("drop table t1"); 1583            conn.commit(); 1584            stmt.close(); 1585            pss.close(); 1586            psu2.close(); 1587            psu.close(); 1588            psd.close(); 1589            psd2.close(); 1590            System.out.println("END DERBY-500 TEST for varchar"); 1591            System.out.println("======================================"); 1592 1593        } catch (SQLException sqle) { 1594            dumpSQLExceptions(sqle); 1595        } catch (Exception e) { 1596            System.out.println("DERBY-500 TEST for varchar FAILED!"); 1597            e.printStackTrace(); 1598        } 1599 1600    } 1601 1602    /** 1603     * verify the data in the derby500Test 1604     * @param ps select preparedstatement 1605     * @param buf byte array to compare the blob data 1606     * @param cbuf char array to compare the clob data 1607     * @param startId start id of the row to check data for 1608     * @param endId end id of the row to check data for 1609     * @param binaryType flag to indicate if the second column in resultset 1610     * is a binary type or not. true for binary type 1611     * @throws Exception 1612     */ 1613    private static void verifyDerby500Test(PreparedStatement ps, byte[] buf,char[] cbuf, 1614            int startId, int endId,boolean binaryType) throws Exception { 1615        byte[] retrieveData = null; 1616        int rowCount = 0; 1617        ResultSet rs = null; 1618        for (int i = startId; i < endId; i++) { 1619            ps.setInt(1, i); 1620            rs = ps.executeQuery(); 1621            if(rs.next()) 1622            { 1623            compareCharArray(rs.getCharacterStream(1), cbuf,cbuf.length); 1624            if(binaryType) 1625                byteArrayEquals(rs.getBytes(2), 0, buf.length, buf, 0, buf.length); 1626            else 1627                compareCharArray(rs.getCharacterStream(2), cbuf,cbuf.length); 1628                 1629            rowCount++; 1630            } 1631        } 1632        System.out.println("Rows selected =" + rowCount); 1633        rs.close(); 1634    } 1635    /** 1636     * compare char data 1637     * @param stream data from stream to compare 1638     * @param compare base data to compare against 1639     * @param length compare length number of chars. 1640     * @throws Exception 1641     */ 1642    private static void compareCharArray(Reader stream, char[] compare, 1643            int length) throws Exception { 1644        int c1 = 0; 1645        int i = 0; 1646        do { 1647            c1 = stream.read(); 1648            if (c1 != compare[i++]) { 1649                System.out 1650                        .println("FAIL -- MISMATCH in data stored versus data retrieved at " 1651                                + (i - 1)); 1652                break; 1653            } 1654            length--; 1655        } while (c1 != -1 && length > 0); 1656 1657    } 1658     1659    private static void expectedException(SQLException sqle) { 1660 1661        while (sqle != null) { 1662            String sqlState = sqle.getSQLState(); 1663            if (sqlState == null) { 1664                sqlState = "<NULL>"; 1665            } 1666            System.out.println("EXPECTED SQL Exception: (" + sqlState + ") " 1667                    + sqle.getMessage()); 1668 1669            sqle = sqle.getNextException(); 1670        } 1671    } 1672 1673    private static void streamTestDataVerification(ResultSet rs, int maxValueAllowed) 1674    throws Exception { 1675        ResultSetMetaData met; 1676 1677        met = rs.getMetaData(); 1678        byte[] buff = new byte[128]; 1679        // fetch all rows back, get the varchar and/ long varchar columns as streams. 1680 while (rs.next()) { 1681            // get the first column as an int 1682 int a = rs.getInt("a"); 1683            // get the second column as a stream 1684 InputStream fin = rs.getAsciiStream(2); 1685            int columnSize = 0; 1686            for (;;) { 1687                int size = fin.read(buff); 1688                    if (size == -1) 1689                    break; 1690                    columnSize += size; 1691            } 1692            if((a>=1 && a <= 5) && columnSize == maxValueAllowed) 1693                System.out.println("===> verified length " + maxValueAllowed); 1694            else 1695                System.out.println("test failed, columnSize should be " + maxValueAllowed + " but it is" + columnSize); 1696        } 1697    } 1698 1699    //blankPadding 1700 // true means excess trailing blanks 1701 // false means excess trailing non-blank characters 1702 // @param tblType table type, depending on the table type, the corresponding 1703 // table is used. for varchar - testVarChar , for long varchar - testVarChars, 1704 // and for clob - testClob is used 1705 private static void insertDataUsingConcat(Statement stmt, int intValue, int maxValueAllowed, boolean blankPadding, 1706        int tblType) 1707    throws Exception { 1708        String sql; 1709         1710        switch(tblType) 1711        { 1712            case LONGVARCHAR: 1713                sql = "insert into testLongVarChars select " + intValue + ", a||b||"; 1714                break; 1715            case CLOB: 1716                sql = "insert into testClob select "+ intValue + ", c||d||"; 1717                break; 1718            default: 1719                sql = "insert into testVarChar select "+ intValue + ", c||d||"; 1720        } 1721 1722        if (blankPadding) //try overflow with trailing blanks 1723 sql = sql.concat("' ' from testConcatenation"); 1724        else //try overflow with trailing non-blank characters 1725 sql = sql.concat("'123' from testConcatenation"); 1726 1727        //for varchars, trailing blank truncation will not throw an exception. Only non-blank characters will cause truncation error 1728 //for long varchars, any character truncation will throw an exception. 1729 try { 1730            stmt.execute(sql); 1731            System.out.println("No truncation and hence no error."); 1732        } 1733        catch (SQLException e) { 1734            if (e.getSQLState().equals("22001")) //truncation error 1735 System.out.println("expected exception for data > " + maxValueAllowed + " in length"); 1736            else 1737                dumpSQLExceptions(e); 1738        } 1739    } 1740 1741    //blankPadding 1742 // true means excess trailing blanks 1743 // false means excess trailing non-blank characters 1744 //testUsingString 1745 // true means try setString method for overflow 1746 // false means try setObject method for overflow 1747 private static void insertDataUsingStringOrObject(PreparedStatement ps, int intValue, int maxValueAllowed, 1748        boolean blankPadding, boolean testUsingString) 1749    throws Exception { 1750        StringBuffer sb = new StringBuffer (maxValueAllowed); 1751        for (int i = 0; i < maxValueAllowed; i++) 1752            sb.append('q'); 1753 1754        String largeString = new String (sb); 1755        if (blankPadding) { 1756            largeString = largeString.concat(" "); 1757            System.out.print("===> testing trailing blanks(using "); 1758        } else { 1759            largeString = largeString.concat("123"); 1760            System.out.print("===> testing trailing non-blanks(using "); 1761        } 1762 1763        ps.setInt(1, intValue); 1764        if (testUsingString) { 1765            System.out.println("setString) length = " + largeString.length()); 1766            ps.setString(2, largeString); 1767        } else { 1768            System.out.println("setObject) length = " + largeString.length()); 1769            ps.setObject(2, largeString); 1770        } 1771 1772        //for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error 1773 //for long varchars, any character truncation will throw an exception. 1774 try { 1775            ps.executeUpdate(); 1776            System.out.println("No truncation and hence no error"); 1777        } 1778        catch (SQLException e) { 1779            if (largeString.length() > maxValueAllowed && e.getSQLState().equals("22001")) //truncation error 1780 System.out.println("expected exception for data > " + maxValueAllowed + " in length"); 1781            else 1782                dumpSQLExceptions(e); 1783        } 1784    } 1785 1786    private static void insertDataUsingCharacterStream(PreparedStatement ps, int intValue, String fileName, int maxValueAllowed) 1787    throws Exception { 1788        File file = new File(fileName); 1789        InputStream fileIn = new FileInputStream(file); 1790        Reader filer = new InputStreamReader(fileIn, "US-ASCII"); 1791        System.out.println("===> testing(using setCharacterStream) " + fileName + " length = " + file.length()); 1792        ps.setInt(1, intValue); 1793        // insert a streaming column 1794 ps.setCharacterStream(2, filer, (int)file.length()); 1795        //for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error 1796 //for long varchars, any character truncation will throw an exception. 1797 try { 1798            ps.executeUpdate(); 1799            System.out.println("No truncation and hence no error"); 1800        } 1801        catch (SQLException e) { 1802            if (file.length() > maxValueAllowed && e.getSQLState().equals("22001")) //truncation error 1803 System.out.println("expected exception for data > " + maxValueAllowed + " in length"); 1804            else 1805                TestUtil.dumpSQLExceptions(e,true); 1806        } 1807        filer.close(); 1808    } 1809 1810    private static void insertDataUsingAsciiStream(PreparedStatement ps, int intValue, String fileName, int maxValueAllowed) 1811    throws Exception { 1812        File file = new File(fileName); 1813        InputStream fileIn = new FileInputStream(file); 1814        System.out.println("===> testing(using setAsciiStream) " + fileName + " length = " + file.length()); 1815        // insert a streaming column 1816 ps.setInt(1, intValue); 1817        ps.setAsciiStream(2, fileIn, (int)file.length()); 1818        //for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error 1819 //for long varchars, any character truncation will throw an exception. 1820 try { 1821            ps.executeUpdate(); 1822            System.out.println("No truncation and hence no error"); 1823        } 1824        catch (SQLException e) { 1825            if (file.length() > maxValueAllowed && e.getSQLState().equals("22001")) //truncation error 1826 System.out.println("expected exception for data > " + maxValueAllowed + " in length"); 1827            else 1828                TestUtil.dumpSQLExceptions(e,true); 1829        } 1830        fileIn.close(); 1831    } 1832 1833    static void verifyLength(int a, int columnSize, long[] fileLength) 1834    { 1835        for (int i = 0; i < fileLength.length; i++) { 1836            if ((a == (100 + i)) || (a == (10000 + i))) 1837            { 1838                if(columnSize != fileLength[i]) 1839                    System.out.println("test failed, columnSize should be " + fileLength[i] 1840                       + ", but it is " + columnSize + ", i = " + i); 1841                else 1842                    System.out.println("===> verified length " + fileLength[i]); 1843            } 1844        } 1845    } 1846 1847    static void verifyExistence(Connection conn, int key, String base, long length) throws Exception { 1848        if (!pad(base, length).equals(getLongString(conn, key))) 1849            throw new Exception ("failed to find value " + base + "... at key " + key); 1850    } 1851 1852    static String getLongString(Connection conn, int key) throws Exception { 1853        Statement s = conn.createStatement(); 1854        ResultSet rs = s.executeQuery("select b from foo where a = " + key); 1855        if (!rs.next()) 1856            throw new Exception ("there weren't any rows for key = " + key); 1857        String answer = rs.getString(1); 1858        if (rs.next()) 1859            throw new Exception ("there were multiple rows for key = " + key); 1860        rs.close(); 1861        s.close(); 1862        return answer; 1863    } 1864 1865    static String pad(String base, long length) { 1866        StringBuffer b = new StringBuffer (base); 1867        for (long i = 1; b.length() < length; i++) 1868            b.append(" " + i); 1869        return b.toString(); 1870    } 1871 1872    static int insertLongString(Connection conn, int key, String data, boolean binaryColumn) throws Exception { 1873        PreparedStatement ps = conn.prepareStatement("insert into foo values(" + key + ", ?)"); 1874        return streamInStringCol(ps, data, binaryColumn); 1875    } 1876 1877    static int updateLongString(Connection conn, int oldkey, int newkey) 1878         throws Exception 1879    { 1880        PreparedStatement ps = conn.prepareStatement( 1881            "update foo set a = ?, b = ? where a = " + oldkey); 1882 1883        String updateString = pad("", newkey); 1884        ByteArrayInputStream bais = new ByteArrayInputStream(updateString.getBytes("US-ASCII")); 1885        ps.setInt(1, newkey); 1886        ps.setAsciiStream(2, bais, updateString.length()); 1887        int nRows = ps.executeUpdate(); 1888        ps.close(); 1889        return nRows; 1890    } 1891 1892    static int streamInStringCol(PreparedStatement ps, String data, boolean binaryColumn) throws Exception { 1893        int nRows = 0; 1894 1895        if (data == null) 1896        { 1897            ps.setAsciiStream(1, null, 0); 1898            nRows = ps.executeUpdate(); 1899        } 1900        else 1901        { 1902            ByteArrayInputStream bais = new ByteArrayInputStream(data.getBytes("US-ASCII")); 1903            if (binaryColumn) 1904                ps.setBinaryStream(1, bais, data.length()); 1905            else 1906                ps.setAsciiStream(1, bais, data.length()); 1907            nRows = ps.executeUpdate(); 1908            bais.close(); 1909        } 1910        return nRows; 1911    } 1912 1913    public static int streamInLongCol(PreparedStatement ps, Object data) throws Exception { 1914        String s = (String )data; 1915        ByteArrayInputStream bais = new ByteArrayInputStream(s.getBytes("US-ASCII")); 1916        ps.setAsciiStream(1, bais, s.length()); 1917        int nRows = ps.executeUpdate(); 1918        bais.close(); 1919        return nRows; 1920    } 1921 1922    /** 1923        Compare two byte arrays using value equality. 1924        Two byte arrays are equal if their length is 1925        identical and their contents are identical. 1926    */ 1927    private static boolean byteArrayEquals( 1928    byte[] a, 1929    int aOffset, 1930    int aLength, 1931    byte[] b, 1932    int bOffset, 1933    int bLength) 1934    { 1935        if (aLength != bLength) 1936            return false; 1937 1938        for (int i = 0; i < aLength; i++) { 1939            if (a[i + aOffset] != b[i + bOffset]) 1940                return false; 1941        } 1942        return true; 1943    } 1944 1945    static private void dumpSQLExceptions (SQLException se) { 1946        System.out.println("FAIL -- unexpected exception: " + se.toString()); 1947        se.printStackTrace(); 1948        while (se != null) { 1949            System.out.println("SQLSTATE("+se.getSQLState()+"):"+se.getMessage()); 1950            se = se.getNextException(); 1951        } 1952    } 1953     1954     1955} 1956

A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame Popular Tags