streamingcolumn.java

derby database source code.good for you.

			insertDataUsingStringOrObject(ps, 8, Limits.DB2_VARCHAR_MAXWIDTH, false, true);
			insertDataUsingStringOrObject(ps, 9, Limits.DB2_VARCHAR_MAXWIDTH, false, false);
			System.out.println("===> testing trailing non-blank characters using concatenation");
			insertDataUsingConcat(stmt, 10, Limits.DB2_VARCHAR_MAXWIDTH, false, false);

			rs = stmt.executeQuery("select a, b from testVarChar");
			streamTestDataVerification(rs, Limits.DB2_VARCHAR_MAXWIDTH);
    }
		catch (SQLException e) {
			dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
		}
	 	System.out.println("Test 12 - varchar truncation tests end in here");
	}

	private static void streamTest13(Connection conn) {

		ResultSet rs;
		Statement stmt;

		//The following 2 files are for testing the truncation in long varchar.
		//any character truncation (including blanks characters) will throw an exception for long varchars.
		//max value allowed in long varchars is 32700 characters long
		String fileName1 = "extin/char32703trailingblanks.data"; // set up a file 32703 characters long but with last 3 characters as blanks
		String fileName2 = "extin/char32703.data"; // set up a file 32703 characters long with 3 extra non-blank characters trailing in the end

		System.out.println("Test 13 - long varchar truncation tests start from here");
		try {
			stmt = conn.createStatement();
			stmt.execute("create table testLongVarChars (a int, b long varchar)");
			PreparedStatement ps = conn.prepareStatement("insert into testLongVarChars values(?, ?)");

			// prepare an InputStream from the file which has 3 trailing blanks in the end. For long varchar, this would throw a truncation error
			// try this using setAsciiStream, setCharacterStream, setString and setObject
			insertDataUsingAsciiStream(ps, 1, fileName1, Limits.DB2_LONGVARCHAR_MAXWIDTH);
			insertDataUsingCharacterStream(ps, 2, fileName1, Limits.DB2_LONGVARCHAR_MAXWIDTH);
			insertDataUsingStringOrObject(ps, 3, Limits.DB2_LONGVARCHAR_MAXWIDTH, true, true);
			insertDataUsingStringOrObject(ps, 4, Limits.DB2_LONGVARCHAR_MAXWIDTH, true, false);
			//bug 5600- Can't test data overflow in longvarchar using concatenation because longvarchar concatenated string can't be longer than 32700
			//System.out.println("===> testing trailing blanks using concatenation");
			//insertDataUsingConcat(stmt, 5, Limits.DB2_LONGVARCHAR_MAXWIDTH, true, true);

			// prepare an InputStream from the file which has 3 trailing non-blanks in the end, and hence there would be overflow exception
			// try this using setAsciiStream, setCharacterStream, setString and setObject
			insertDataUsingAsciiStream(ps, 6, fileName2, Limits.DB2_LONGVARCHAR_MAXWIDTH);
			insertDataUsingCharacterStream(ps, 7, fileName2, Limits.DB2_LONGVARCHAR_MAXWIDTH);
			insertDataUsingStringOrObject(ps, 7, Limits.DB2_LONGVARCHAR_MAXWIDTH, false, true);
			insertDataUsingStringOrObject(ps, 9, Limits.DB2_LONGVARCHAR_MAXWIDTH, false, false);
			//bug 5600 - Can't test data overflow in longvarchar using concatenation because longvarchar concatenated string can't be longer than 32700
			//System.out.println("===> testing trailing non-blank characters using concatenation");
			//insertDataUsingConcat(stmt, 10, Limits.DB2_LONGVARCHAR_MAXWIDTH, false, true);

			rs = stmt.executeQuery("select a, b from testLongVarChars");
			streamTestDataVerification(rs, Limits.DB2_LONGVARCHAR_MAXWIDTH);
		}
		catch (SQLException e) {
			dumpSQLExceptions(e);
		}
		catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception:" + e.toString());
		}
	 	System.out.println("Test 13 - long varchar truncation tests end in here");
	}



    /**
     * Streams are not re-used. This test tests the fix for 
     * DERBY-500. If an update statement has multiple rows that
     * is affected, and one of the parameter values is a stream,
     * the update will fail because streams are not re-used.
     * @param conn database connection
     */
    private static void derby500Test(Connection conn) {

        Statement stmt;

        System.out.println("======================================");
        System.out.println("START  DERBY-500 TEST ");

        try {
            stmt = conn.createStatement();
            conn.setAutoCommit(false);
            stmt.execute("CREATE TABLE t1 (" + "id INTEGER NOT NULL,"
                    + "mname VARCHAR( 254 ) NOT NULL," + "mvalue INT NOT NULL,"
                    + "bytedata BLOB NOT NULL," + "chardata CLOB NOT NULL,"
                    + "PRIMARY KEY ( id ))");

            PreparedStatement ps = conn
                    .prepareStatement("insert into t1 values (?,?,?,?,?)");

            // insert 10 rows.
            int rowCount = 0;
            // use blob and clob values
            int len = 10000;
            byte buf[] = new byte[len];
            char cbuf[] = new char[len];
            char orig =  'c';
            for (int i = 0; i < len; i++) {
                buf[i] = (byte)orig;
                cbuf[i] = orig;
            }
            int randomOffset = 9998;
            buf[randomOffset] = (byte) 'e';
            cbuf[randomOffset] = 'e';
            System.out.println("Inserting rows ");
            for (int i = 0; i < 10; i++) {
                ps.setInt(1, i);
                ps.setString(2, "mname" + i);
                ps.setInt(3, 0);
                ps.setBinaryStream(4, new ByteArrayInputStream(buf), len);
                ps.setAsciiStream(5, new ByteArrayInputStream(buf), len);
                rowCount += ps.executeUpdate();
            }
            conn.commit();
            System.out.println("Rows inserted =" + rowCount);

            
            //conn.commit();
            PreparedStatement pss = conn
                    .prepareStatement(" select chardata,bytedata from t1 where id = ?");
            verifyDerby500Test(pss, buf, cbuf,0, 10, true);
            
            // do the update, update must qualify more than 1 row and update will fail
            // as currently we dont allow stream values to be re-used
            PreparedStatement psu = conn
                    .prepareStatement("update t1 set bytedata = ? "
                            + ", chardata = ? where mvalue = ?  ");

            buf[randomOffset + 1] = (byte) 'u';
            cbuf[randomOffset +1] = 'u';
            rowCount = 0;
            System.out.println("Update qualifies many rows + streams");

            try {
                psu.setBinaryStream(1, new ByteArrayInputStream(buf), len);
                psu.setCharacterStream(2, new CharArrayReader(cbuf), len);
                psu.setInt(3, 0);
                rowCount += psu.executeUpdate();
                System.out.println("DERBY500 #1 Rows updated  ="
                        + rowCount);

            } catch (SQLException sqle) {
                System.out
                        .println("EXPECTED EXCEPTION - streams cannot be re-used");
                expectedException(sqle);
                conn.rollback();
            }
            
            //verify data
            //set back buffer value to what was inserted.
            buf[randomOffset + 1] = (byte)orig;
            cbuf[randomOffset + 1] = orig;
            
            verifyDerby500Test(pss, buf,cbuf, 0, 10,true);

            PreparedStatement psu2 = conn
                    .prepareStatement("update t1 set bytedata = ? "
                            + ", chardata = ? where id = ?  ");

            buf[randomOffset + 1] = (byte) 'u';
            cbuf[randomOffset + 1] = 'u';
            
            rowCount = 0;
            try {
                psu2.setBinaryStream(1, new ByteArrayInputStream(buf), len);
                psu2.setAsciiStream(2, new ByteArrayInputStream(buf), len);
                psu2.setInt(3, 0);
                rowCount += psu2.executeUpdate();
                System.out.println("DERBY500 #2 Rows updated  ="
                        + rowCount);

            } catch (SQLException sqle) {
                System.out
                        .println("UNEXPECTED EXCEPTION - update should have actually gone through");
                dumpSQLExceptions(sqle);
            }
            conn.commit();
            verifyDerby500Test(pss, buf,cbuf, 0, 1,true);
            
            // delete
            // as currently we dont allow stream values to be re-used
            PreparedStatement psd = conn
                    .prepareStatement("delete from t1 where mvalue = ?");

            rowCount = 0;
            try {
                psd.setInt(1, 0);
                rowCount += psd.executeUpdate();
                rowCount += psd.executeUpdate();
                System.out.println("DERBY500 #3 Rows deleted ="
                        + rowCount);

            } catch (SQLException sqle) {
                System.out
                .println("UNEXPECTED EXCEPTION - delete should have actually gone through");
                dumpSQLExceptions(sqle);
            }

            conn.commit();
            //verify data
           
            verifyDerby500Test(pss, buf,cbuf, 0, 10, true);

            PreparedStatement psd2 = conn
                    .prepareStatement("delete from t1 where id = ?");
            
            rowCount = 0;
            try {
                psd2.setInt(1, 0);
                rowCount += psd2.executeUpdate();
                System.out.println("DERBY500 #4 Rows deleted  ="
                        + rowCount);

            } catch (SQLException sqle) {
                System.out
                        .println("UNEXPECTED EXCEPTION - delete should have actually gone through");
                dumpSQLExceptions(sqle);
            }
            conn.commit();
            verifyDerby500Test(pss, buf,cbuf, 1, 2,true);

            try
            {
                ps.setInt(1,11);
                rowCount += ps.executeUpdate();
                System.out.println("Rows inserted = "+ rowCount);
            } catch (SQLException sqle) {
                System.out
                        .println("EXPECTED EXCEPTION - streams cannot be re-used");
                expectedException(sqle);
                conn.rollback();
            }

            stmt.execute("drop table t1");
            conn.commit();
            stmt.close();
            pss.close();
            psu2.close();
            psu.close();
            psd.close();
            psd2.close();
            System.out.println("END  DERBY-500 TEST ");
            System.out.println("======================================");

        } catch (SQLException sqle) {
            dumpSQLExceptions(sqle);
        } catch (Exception e) {
            System.out.println("DERBY-500 TEST FAILED!");
            e.printStackTrace();
        }

    }

    /**
     * Test that DERBY500 fix did not change the behavior for varchar,
     * char, long varchar types when stream api is used. 
     * Currently, for char,varchar and long varchar - the stream is 
     * read once and materialized, hence the materialized stream value
     * will/can be used for multiple executions of the prepared statement  
     * @param conn database connection
     */
    private static void derby500_verifyVarcharStreams(Connection conn) {

        Statement stmt;

        System.out.println("======================================");
        System.out.println("START  DERBY-500 TEST for varchar ");

        try {
            stmt = conn.createStatement();
            stmt.execute("CREATE TABLE t1 (" + "id INTEGER NOT NULL,"
                    + "mname VARCHAR( 254 ) NOT NULL," + "mvalue INT NOT NULL,"
                    + "vc varchar(32500)," + "lvc long varchar NOT NULL,"
                    + "PRIMARY KEY ( id ))");

            PreparedStatement ps = conn
                    .prepareStatement("insert into t1 values (?,?,?,?,?)");

            // insert 10 rows.
            int rowCount = 0;
            // use blob and clob values
            int len = 10000;
            byte buf[] = new byte[len];
            char cbuf[] = new char[len];
            char orig =  'c';
            for (int i = 0; i < len; i++) {
                buf[i] = (byte)orig;
                cbuf[i] = orig;
            }
            int randomOffset = 9998;
            buf[randomOffset] = (byte)'e';
            cbuf[randomOffset] = 'e';
            for (int i = 0; i < 10; i++) {
                ps.setInt(1, i);
                ps.setString(2, "mname" + i);
                ps.setInt(3, 0);
                ps.setCharacterStream(4, new CharArrayReader(cbuf), len);
                ps.setAsciiStream(5, new ByteArrayInputStream(buf), len);
                rowCount += ps.executeUpdate();
            }
            conn.commit();
            System.out.println("Rows inserted =" + rowCount);

            try
            {
                ps.setInt(1,11);
                rowCount += ps.executeUpdate();
            } catch (SQLException sqle) {
                System.out.println("UNEXPECTED EXCEPTION - streams cannot be "+
                   "re-used but in case of varchar, stream is materialized the"+
                   " first time around. So multiple executions using streams should "+
                   " work fine. ");
                dumpSQLExceptions(sqle);
            }
            
            PreparedStatement pss = conn
                    .prepareStatement(" select lvc,vc from t1 where id = ?");
            verifyDerby500Test(pss, buf, cbuf,0, 10,false);
            
            // do the update, update must qualify more than 1 row and update will
            // pass for char,varchar,long varchar columns.
            PreparedStatement psu = conn
                    .prepareStatement("update t1 set vc = ? "
                            + ", lvc = ? where mvalue = ?  ");

            buf[randomOffset +1] = (byte)'u';
            cbuf[randomOffset +1] = 'u';
            rowCount = 0;
            try {
                psu.setAsciiStream(1, new ByteArrayInputStream(buf), len);
                psu.setCharacterStream(2, new CharArrayReader(cbuf), len);
                psu.setInt(3, 0);
                rowCount += psu.executeUpdate();
            } catch (SQLException sqle) {
                System.out
                        .println("EXPECTED EXCEPTION - streams cannot be re-used");
                expectedException(sqle);
            }
            System.out.println("DERBY500 for varchar #1 Rows updated  ="
                    + rowCount);

            //verify data
            verifyDerby500Test(pss, buf,cbuf, 0, 10, false);

            PreparedStatement psu2 = conn
                    .prepareStatement("update t1 set vc = ? "
                            + ", lvc = ? where id = ?  ");

            buf[randomOffset +1] = (byte)'h';
            cbuf[randomOffset + 1] = 'h';
            
            rowCount = 0;
            try {
                psu2.setAsciiStream(1, new ByteArrayInputStream(buf), len);
                psu2.setAsciiStream(2, new ByteArrayInputStream(buf), len);
                psu2.setInt(3, 0);
                rowCount += psu2.executeUpdate();
            } catch (SQLException sqle) {
                System.out
                        .println("UNEXPECTED EXCEPTION - update should have actually gone through");
                dumpSQLExceptions(sqle);
            }