streamingcolumn.java

derby database source code.good for you.

            conn.commit();
            System.out.println("DERBY500 for varchar #2 Rows updated  ="
                    + rowCount);
            verifyDerby500Test(pss, buf,cbuf, 0, 1,false);
            
            // 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();
            } catch (SQLException sqle) {
                System.out
                .println("UNEXPECTED EXCEPTION - delete should have actually gone through");
                dumpSQLExceptions(sqle);
            }
            System.out.println("DERBY500 for varchar #3 Rows deleted ="
                    + rowCount);

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

            PreparedStatement psd2 = conn
                    .prepareStatement("delete from t1 where id = ?");
            
            rowCount = 0;
            try {
                psd2.setInt(1, 0);
                rowCount += psd2.executeUpdate();
            } catch (SQLException sqle) {
                System.out
                        .println("UNEXPECTED EXCEPTION - delete should have actually gone through");
                dumpSQLExceptions(sqle);
            }
            conn.commit();
            System.out.println("DERBY500 for varchar #4 Rows deleted  ="
                    + rowCount);
            verifyDerby500Test(pss, buf,cbuf, 1, 2,false);

            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  for varchar");
            System.out.println("======================================");

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

    }

    /**
     * verify the data in the derby500Test
     * @param ps select preparedstatement
     * @param buf byte array to compare the blob data
     * @param cbuf char array to compare the clob data
     * @param startId start id of the row to check data for 
     * @param endId end id of the row to check data for
     * @param binaryType  flag to indicate if the second column in resultset
     *                  is a binary type or not. true for binary type 
     * @throws Exception
     */
    private static void verifyDerby500Test(PreparedStatement ps, byte[] buf,char[] cbuf,
            int startId, int endId,boolean binaryType) throws Exception {
        byte[] retrieveData = null;
        int rowCount = 0;
        ResultSet rs = null;
        for (int i = startId; i < endId; i++) {
            ps.setInt(1, i);
            rs = ps.executeQuery();
            if(rs.next())
            {
            compareCharArray(rs.getCharacterStream(1), cbuf,cbuf.length);
            if(binaryType)
                byteArrayEquals(rs.getBytes(2), 0, buf.length, buf, 0, buf.length);
            else
                compareCharArray(rs.getCharacterStream(2), cbuf,cbuf.length);
                
            rowCount++;
            }
        }
        System.out.println("Rows selected =" + rowCount);
        rs.close();
    }
    /**
     * compare char data
     * @param stream data from stream to compare 
     * @param compare base data to compare against
     * @param length compare length number of chars.
     * @throws Exception
     */
    private static void compareCharArray(Reader stream, char[] compare,
            int length) throws Exception {
        int c1 = 0;
        int i = 0;
        do {
            c1 = stream.read();
            if (c1 != compare[i++]) {
                System.out
                        .println("FAIL -- MISMATCH in data stored versus data retrieved at "
                                + (i - 1));
                break;
            }
            length--;
        } while (c1 != -1 && length > 0);

    }
    
    private static void expectedException(SQLException sqle) {

        while (sqle != null) {
            String sqlState = sqle.getSQLState();
            if (sqlState == null) {
                sqlState = "<NULL>";
            }
            System.out.println("EXPECTED SQL Exception: (" + sqlState + ") "
                    + sqle.getMessage());

            sqle = sqle.getNextException();
        }
    }

	private static void streamTestDataVerification(ResultSet rs, int maxValueAllowed)
	throws Exception{
		ResultSetMetaData met;

		met = rs.getMetaData();
		byte[] buff = new byte[128];
		// fetch all rows back, get the varchar and/ long varchar columns as streams.
		while (rs.next()) {
			// get the first column as an int
			int a = rs.getInt("a");
			// get the second column as a stream
			InputStream fin = rs.getAsciiStream(2);
			int columnSize = 0;
			for (;;) {
				int size = fin.read(buff);
					if (size == -1)
					break;
					columnSize += size;
			}
			if((a>=1 && a <= 5) && columnSize == maxValueAllowed)
				System.out.println("===> verified length " + maxValueAllowed);
			else
				System.out.println("test failed, columnSize should be " + maxValueAllowed + " but it is" + columnSize);
		}
	}

	//blankPadding
	//  true means excess trailing blanks
	//  false means excess trailing non-blank characters
	//forLongVarChar
	//  true means testing for long varchar truncation and hence use table testLongVarChars
	//  false means testing for varchar truncation and hence use table testVarChar
	private static void insertDataUsingConcat(Statement stmt, int intValue, int maxValueAllowed, boolean blankPadding,
	 	boolean forLongVarChar)
	throws Exception{
		String sql;
		if (forLongVarChar)
			sql = "insert into testLongVarChars select " + intValue + ", a||b||";
		else
			sql = "insert into testVarChar select "+ intValue + ", c||d||";

		if (blankPadding) //try overflow with trailing blanks
			sql = sql.concat("'   ' from testConcatenation");
		else //try overflow with trailing non-blank characters
			sql = sql.concat("'123' from testConcatenation");

		//for varchars, trailing blank truncation will not throw an exception. Only non-blank characters will cause truncation error
		//for long varchars, any character truncation will throw an exception.
		try {
			stmt.execute(sql);
			System.out.println("No truncation and hence no error.");
		}
		catch (SQLException e) {
			if (e.getSQLState().equals("22001")) //truncation error
				System.out.println("expected exception for data > " + maxValueAllowed + " in length");
			else
				dumpSQLExceptions(e);
		}
	}

	//blankPadding
	//  true means excess trailing blanks
	//  false means excess trailing non-blank characters
	//testUsingString
	//  true means try setString method for overflow
	//  false means try setObject method for overflow
	private static void insertDataUsingStringOrObject(PreparedStatement ps, int intValue, int maxValueAllowed,
	 	boolean blankPadding, boolean testUsingString)
	throws Exception{
	 	StringBuffer sb = new StringBuffer(maxValueAllowed);
	 	for (int i = 0; i < maxValueAllowed; i++)
			sb.append('q');

	 	String largeString = new String(sb);
	 	if (blankPadding) {
			largeString = largeString.concat("   ");
			System.out.print("===> testing trailing blanks(using ");
	 	} else {
			largeString = largeString.concat("123");
			System.out.print("===> testing trailing non-blanks(using ");
	 	}

	 	ps.setInt(1, intValue);
	 	if (testUsingString) {
			System.out.println("setString) length = " + largeString.length());
			ps.setString(2, largeString);
	 	} else {
			System.out.println("setObject) length = " + largeString.length());
			ps.setObject(2, largeString);
	 	}

		//for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error
		//for long varchars, any character truncation will throw an exception.
	 	try {
			ps.executeUpdate();
			System.out.println("No truncation and hence no error");
	 	}
	 	catch (SQLException e) {
			if (largeString.length() > maxValueAllowed && e.getSQLState().equals("22001")) //truncation error
				System.out.println("expected exception for data > " + maxValueAllowed + " in length");
			else
				dumpSQLExceptions(e);
	 	}
	}

	private static void insertDataUsingCharacterStream(PreparedStatement ps, int intValue, String fileName, int maxValueAllowed)
	throws Exception{
	 	File file = new File(fileName);
	 	InputStream fileIn = new FileInputStream(file);
	 	Reader filer = new InputStreamReader(fileIn, "US-ASCII");
	 	System.out.println("===> testing(using setCharacterStream) " + fileName + " length = " + file.length());
	 	ps.setInt(1, intValue);
	 	// insert a streaming column
	 	ps.setCharacterStream(2, filer, (int)file.length());
		//for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error
		//for long varchars, any character truncation will throw an exception.
	 	try {
			ps.executeUpdate();
			System.out.println("No truncation and hence no error");
	 	}
	 	catch (SQLException e) {
			if (file.length() > maxValueAllowed && e.getSQLState().equals("22001")) //truncation error
				System.out.println("expected exception for data > " + maxValueAllowed + " in length");
			else
				dumpSQLExceptions(e);
	 	}
	 	filer.close();
	}

	private static void insertDataUsingAsciiStream(PreparedStatement ps, int intValue, String fileName, int maxValueAllowed)
	throws Exception{
	 	File file = new File(fileName);
	 	InputStream fileIn = new FileInputStream(file);
	 	System.out.println("===> testing(using setAsciiStream) " + fileName + " length = " + file.length());
	 	// insert a streaming column
	 	ps.setInt(1, intValue);
	 	ps.setAsciiStream(2, fileIn, (int)file.length());
		//for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error
		//for long varchars, any character truncation will throw an exception.
	 	try {
			ps.executeUpdate();
			System.out.println("No truncation and hence no error");
	 	}
	 	catch (SQLException e) {
			if (file.length() > maxValueAllowed && e.getSQLState().equals("22001")) //truncation error
				System.out.println("expected exception for data > " + maxValueAllowed + " in length");
			else
				dumpSQLExceptions(e);
	 	}
	 	fileIn.close();
	}

	static void verifyLength(int a, int columnSize, long[] fileLength)
	{
		for (int i = 0; i < fileLength.length; i++) {
			if ((a == (100 + i)) || (a == (10000 + i)))
			{
				if(columnSize != fileLength[i])
					System.out.println("test failed, columnSize should be " + fileLength[i]
					   + ", but it is " + columnSize + ", i = " + i);
				else
					System.out.println("===> verified length " + fileLength[i]);
			}
		}
	}

	static void verifyExistence(Connection conn, int key, String base, long length) throws Exception {
		if (!pad(base, length).equals(getLongString(conn, key)))
			throw new Exception("failed to find value " + base + "... at key " + key);
	}

	static String getLongString(Connection conn, int key) throws Exception {
		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery("select b from foo where a = " + key);
		if (!rs.next())
			throw new Exception("there weren't any rows for key = " + key);
		String answer = rs.getString(1);
		if (rs.next())
			throw new Exception("there were multiple rows for key = " + key);
		rs.close();
		s.close();
		return answer;
	}

	static String pad(String base, long length) {
		StringBuffer b = new StringBuffer(base);
		for (long i = 1; b.length() < length; i++)
			b.append(" " + i);
		return b.toString();
	}

	static int insertLongString(Connection conn, int key, String data, boolean binaryColumn) throws Exception {
		PreparedStatement ps = conn.prepareStatement("insert into foo values(" + key + ", ?)");
		return streamInStringCol(ps, data, binaryColumn);
	}

	static int updateLongString(Connection conn, int oldkey, int newkey)
		 throws Exception
	{
		PreparedStatement ps = conn.prepareStatement(
			"update foo set a = ?, b = ? where a = " + oldkey);

		String updateString = pad("", newkey);
		ByteArrayInputStream bais = new ByteArrayInputStream(updateString.getBytes());
		ps.setInt(1, newkey);
		ps.setAsciiStream(2, bais, updateString.length());
		int nRows = ps.executeUpdate();
		ps.close();
		return nRows;
	}

	static int streamInStringCol(PreparedStatement ps, String data, boolean binaryColumn) throws Exception {
		int nRows = 0;

		if (data == null)
		{
			ps.setAsciiStream(1, null, 0);
			nRows = ps.executeUpdate();
		}
		else
		{
			ByteArrayInputStream bais = new ByteArrayInputStream(data.getBytes("US-ASCII"));
			if (binaryColumn)
				ps.setBinaryStream(1, bais, data.length());
			else
				ps.setAsciiStream(1, bais, data.length());
			nRows = ps.executeUpdat