forbitdata.java

derby database source code.good for you.

				okI2 = false;
				if ("22001".equals(sqle.getSQLState())) {
					System.out.println("22001 truncation error");
				} else
					showSQLE(sqle);
			}

			if (okI1 != okI2)
				System.out.println("FAIL - mismatched failures");
		}

		System.out.println("  >> VARCHAR FOR BIT DATA");
		try {
		psI.setInt(1, id+2);
		psI.setBytes(2, null);
		psI.setBytes(3, original);
		psI.setBytes(4, null);
		psI.setBytes(5, null);
		psI.executeUpdate();
		okI1 = true;

		} catch (SQLException sqle) {
			okI1 = false;
			if ("22001".equals(sqle.getSQLState()))
				System.out.println("22001 truncation error");
			else
				showSQLE(sqle);
		}
		if (streamAsWell) {
			try {
			psI.setInt(1, id+3);
			psI.setBinaryStream(3, original == null ? null : new ByteArrayInputStream(original), ol);
			psI.executeUpdate();
			okI2 = true;

			} catch (SQLException sqle) {
				okI2 = false;
				if ("22001".equals(sqle.getSQLState()))
					System.out.println("22001 truncation error");
				else
					showSQLE(sqle);
			}
			if (okI1 != okI2)
				System.out.println("FAIL - mismatched failures");
		}

		System.out.println("  >> LONG VARCHAR FOR BIT DATA");
		try {
		psI.setInt(1, id+4);
		psI.setBytes(2, null);
		psI.setBytes(3, null);
		psI.setBytes(4, original);
		psI.setBytes(5, null);
		psI.executeUpdate();
		okI1 = true;
		} catch (SQLException sqle) {
			okI1 = false;
			if ("22001".equals(sqle.getSQLState()))
				System.out.println("22001 truncation error");
			else
				showSQLE(sqle);
		}

		if (streamAsWell) {
			try {
			psI.setInt(1, id+5);
			psI.setBinaryStream(4, original == null ? null : new ByteArrayInputStream(original), ol);
			psI.executeUpdate();
			okI2 = true;
			} catch (SQLException sqle) {
				okI2 = false;
				if ("22001".equals(sqle.getSQLState()))
					System.out.println("22001 truncation error");
				else
					showSQLE(sqle);
			}
			if (okI1 != okI2)
				System.out.println("FAIL - mismatched failures");
		}

			System.out.println("  >> BLOB");
		try {
		psI.setInt(1, id+6);
		psI.setBytes(2, null);
		psI.setBytes(3, null);
		psI.setBytes(4, null);
		psI.setBytes(5, original);
		okI1 = true;
		psI.executeUpdate();
		} catch (SQLException sqle) {
			okI1 = false;
			if ("22001".equals(sqle.getSQLState()))
				System.out.println("22001 truncation error");
			else
				showSQLE(sqle);
		}
		if (streamAsWell) {
			try {
			psI.setInt(1, id+7);
			psI.setBinaryStream(5, original == null ? null : new ByteArrayInputStream(original), ol);
			psI.executeUpdate();
			okI2 = true;
			} catch (SQLException sqle) {
				okI2 = false;
				if ("22001".equals(sqle.getSQLState()))
					System.out.println("22001 truncation error");
				else
					showSQLE(sqle);
			}
			if (okI1 != okI2)
				System.out.println("FAIL - mismatched failures");
		}
	}



	
	public static void testCompare(Connection conn) throws SQLException {

		System.out.println("START testCompare");

		Statement s = conn.createStatement();
		executeDrop(s, "DROP TABLE FBDVAL.T001");
		executeDrop(s, "DROP TABLE FBDVAL.T002");
		s.execute("CREATE TABLE FBDVAL.T001(ID INT NOT NULL PRIMARY KEY, C1 CHAR(10) FOR BIT DATA, C2 VARCHAR(10) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(10))");
		s.execute("CREATE TABLE FBDVAL.T002(ID INT NOT NULL PRIMARY KEY, C1 CHAR(10) FOR BIT DATA, C2 VARCHAR(10) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(10))");
		PreparedStatement psI = conn.prepareStatement("INSERT INTO FBDVAL.T001 VALUES(?, ?, ?, ?, ?)");
		PreparedStatement psI2 = conn.prepareStatement("INSERT INTO FBDVAL.T002 VALUES(?, ?, ?, ?, ?)");

		insertData(psI, 0, null, 10, false);
		insertData(psI2, 0, null, 10, false);

		byte[] four = new byte[4];
		four[0] = (byte) 0x04;
		four[1] = (byte) 0x23;
		four[2] = (byte) 0xA2;
		four[3] = (byte) 0xFD;

		insertData(psI, 30, four, 10, false);
		insertData(psI2, 30, four, 10, false);
		four[2] = (byte) 0xA1;
		insertData(psI, 40, four, 10, false);
		insertData(psI2, 40, four, 10, false);
		four[2] = (byte) 0xA2;
		four[3] = (byte) 0xFF;
		insertData(psI, 50, four, 10, false);
		insertData(psI2, 50, four, 10, false);

		byte[] four_plus_space = new byte[5];
		four_plus_space[0] = (byte) 0x04;
		four_plus_space[1] = (byte) 0x23;
		four_plus_space[2] = (byte) 0xA2;
		four_plus_space[3] = (byte) 0xFD;
		four_plus_space[4] = (byte) 0x20;
		insertData(psI, 60, four_plus_space, 10, false);
		insertData(psI2, 60, four_plus_space, 10, false);

		byte[] ten = new byte[10];
		ten[0] = (byte) 0x0B;
		ten[1] = (byte) 0x27;
		ten[2] = (byte) 0xA2;
		ten[3] = (byte) 0xFD;
		ten[4] = (byte) 0x01;
		ten[5] = (byte) 0x6D;
		ten[6] = (byte) 0xE2;
		ten[7] = (byte) 0x35;
		ten[8] = (byte) 0x66;
		ten[9] = (byte) 0x90;

		insertData(psI, 70, ten, 10, false);
		insertData(psI2, 70, ten, 10, false);

		String[] COLS = {"C1", "C2", "C3", "C4"};
		String[] OPS = {"=", "<>", "<", "<=", ">", ">="};

		for (int t = 0; t < COLS.length; t++) {
			for (int o = 0; o < COLS.length; o++) {
				for (int a = 0; a < OPS.length; a++) {

					String sql = "SELECT T.ID, T." + COLS[t] + ", O.ID, O." + COLS[o] +
						" FROM FBDVAL.T001 O, FBDVAL.T002 T WHERE T." + COLS[t] + " " + OPS[a] + " O." + COLS[o] + " ORDER BY 1,3";

					System.out.println(sql);
					try {
						PreparedStatement psS = conn.prepareStatement(sql);
						showCompareData(psS);
					} catch (SQLException sqle) {
						if ("42818".equals(sqle.getSQLState()))
							System.out.println("42818 types not comparable " + COLS[t] + " ... " + COLS[o]);
						else
							showSQLE(sqle);
					}
					conn.commit();
				}
			}
		}
		System.out.println("END testCompare");
	}

	/**
		The length of a binary type is encoded when stored, this
		test makes sure all the code paths are tested.
		The encoded length is hidden from the JDBC client.
	*/
	public static void testEncodedLengths(Connection conn) throws SQLException, IOException {

		System.out.println("START testEncodedLengths");

		Statement s = conn.createStatement();
		executeDrop(s, "DROP TABLE FBDVAL.TEL");
		s.execute("CREATE TABLE FBDVAL.TEL(C2 VARCHAR(32672) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(128k))");

		PreparedStatement psi = conn.prepareStatement("INSERT INTO FBDVAL.TEL VALUES(?, ?, ?)");
		PreparedStatement pss = conn.prepareStatement("SELECT * FROM FBDVAL.TEL");
		PreparedStatement psd = conn.prepareStatement("DELETE FROM FBDVAL.TEL");

		//insertEL(psi, pss, psd, 0);
		insertEL(psi, pss, psd,  10);
		insertEL(psi, pss, psd,  30);
		insertEL(psi, pss, psd,  31);
		insertEL(psi, pss, psd,  32); // switch to 2 byte length
		insertEL(psi, pss, psd,  1345);
		insertEL(psi, pss, psd,  23456);
		insertEL(psi, pss, psd,  32672);
		insertEL(psi, pss, psd,  32700);
		insertEL(psi, pss, psd,  (32*1024) - 1);
		insertEL(psi, pss, psd,  (32*1024));
		insertEL(psi, pss, psd,  (32*1024) + 1);
		insertEL(psi, pss, psd,  (64*1024) - 1);
		insertEL(psi, pss, psd,  (64*1024)); // switch to 4 byte length
		insertEL(psi, pss, psd,  (64*1024) + 1);
		insertEL(psi, pss, psd,  (110*1024) + 3242);





		psi.close();
		pss.close();
		psd.close();
		executeDrop(s, "DROP TABLE FBDVAL.TEL");
		s.close();
		System.out.println("END testEncodedLengths");

	}

	private static void insertEL(PreparedStatement psi, PreparedStatement pss, PreparedStatement psd, int length) throws SQLException, IOException {

		Connection conn = psi.getConnection();
		byte[] data = new byte[length];

		// random simple value check
		int off = (int)  (System.currentTimeMillis() % ((long) length));
		data[off] = 0x23;

		psi.setBytes(1, (length <= 32672) ? data : null);
		psi.setBytes(2, (length <= 32700) ? data : null);
		psi.setBinaryStream(3, new java.io.ByteArrayInputStream(data), length); // BLOB column
		psi.executeUpdate();
		conn.commit();

		selectData(pss,data,off,length);

		conn.commit();

		psd.executeUpdate();
		conn.commit();


        // Set values using stream and then verify that select is successful
		psi.setBinaryStream(1, (length <= 32672) ? new java.io.ByteArrayInputStream(data) : null, length);
		psi.setBinaryStream(2, (length <= 32700) ? new java.io.ByteArrayInputStream(data) : null, length);
		psi.setBinaryStream(3, new java.io.ByteArrayInputStream(data), length); // BLOB column
		psi.executeUpdate();
		conn.commit();

		selectData(pss,data,off,length);

        conn.commit();


		psd.executeUpdate();
		conn.commit();


	}

    private static void selectData(PreparedStatement pss,byte[] data,int off,int length)
        throws SQLException,IOException
    {
        
        ResultSet rs = pss.executeQuery();
        while (rs.next())
        {
            System.out.print(" EL byte[] " + length);
            byte[] v = rs.getBytes(1);
            if (v != null) {
                System.out.print(" C1 " + ((v.length == length) ? "OK" : ("FAIL <" + v.length + ">")));
                System.out.print(" DATA " + ((v[off] == 0x23) ? "OK" : ("FAIL " + off)));
            }
            else
                System.out.print(" C1 NULL");

            v = rs.getBytes(2);
            if (v != null) {
                System.out.print(" C2 " + ((v.length == length) ? "OK" : ("FAIL <" + v.length + ">")));
                System.out.print(" DATA " + ((v[off] == 0x23) ? "OK" : ("FAIL " + off)));
            }
            else
                System.out.print(" C2 NULL");
            InputStream c3 = rs.getBinaryStream(3);
            checkEncodedLengthValue("C3", c3, length, off);

            System.out.println("");
        }
        rs.close();

        rs = pss.executeQuery();
        while (rs.next())
        {
            System.out.print(" EL stream " + length);

            checkEncodedLengthValue("C1", rs.getBinaryStream(1), length, off);
            checkEncodedLengthValue("C2", rs.getBinaryStream(2), length, off);
            checkEncodedLengthValue("C3", rs.getBinaryStream(3), length, off);

            System.out.println("");
        }
        rs.close();

    }
	private static void checkEncodedLengthValue(String col, InputStream is, int length, int off) throws IOException {

		if (is == null) {
			System.out.print(" " + col + " NULL");
			return;
		}
		byte[] buf = new byte[3213];
		boolean dataOK = false;
		int sl = 0;
		for (;;) {
			int r = is.read(buf);
			if (r < 0)
				break;

			if ((off >= sl) && (off < (sl + r))) {
				if (buf[off - sl] == 0x23)
					dataOK = true;
			}
			sl += r;
		}
		System.out.print(" " + col + " " + ((sl == length) ? "OK" : ("FAIL <" + sl + ">")));
		System.out.print(" DATA " + (dataOK ? "OK" : ("FAIL " + off)));
	}

	private static void showData(PreparedStatement psS, int id, byte[] original) throws SQLException {
		psS.setInt(1, id);
		psS.setInt(2, id + 10);
		ResultSet rs = psS.executeQuery();
		while (rs.next()) {

			System.out.print("  ORG ");
				System.out.print(showData(original));
			System.out.print("CHR ");
				System.out.print(showData(rs.getBytes(1)));
			System.out.print("VAR ");
				System.out.print(showData(rs.getBytes(2)));
			System.out.print("LVC ");
				System.out.print(showData(rs.getBytes(3)));
			System.out.print("BLOB ");
				System.out.print(showData(rs.getBytes(4)));

			System.out.println("");
		}
		rs.close();

	}

	private static void showCompareData(PreparedStatement psS) throws SQLException {
		ResultSet rs = psS.executeQuery();
		while (rs.next()) {
			System.out.print("  " + rs.getInt(1) + " ");
			System.out.print(showData(rs.getBytes(2)));
			System.out.print("  " + rs.getInt(3) + " ");
			System.out.println(showData(rs.getBytes(4)));
		}
		rs.close();
		psS.close();
	}

	private static String showData(byte[] data) {
		if (data == null)
			return "<NULL> ";

		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < data.length; i++) {
			String s = Integer.toHexString(data[i] & 0xff);
			if (s.length() == 1)
				sb.append('0');
			sb.append(s);
		}

		sb.append(' ');
		sb.append('(');
		sb.append(data.length);
		sb.append(')');
		sb.append(' ');

		return sb.toString();
	}






	private static void showSQLE(SQLException sqle) {
		do {
			System.out.println(sqle.getSQLState() + ": " + sqle.getMessage());
			//sqle.printStackTrace(System.out);
			sqle = sqle.getNextException();
		} while (sqle != null);
	}
	private static void executeDrop(Statement s, String sql) {
		try {
			s.execute(sql);
		} catch (SQLException sqle) {
		}
	}
	private static void executeOK(Statement s, String sql) {
		System.out.println(sql);
		try {
			s.execute(sql);
		} catch (SQLException sqle) {
			System.out.println("FAIL ");
			showSQLE(sqle);
		}
	}
	private static void statementExceptionExpected(Statement s, String sql) {
		System.out.println(sql);
		try {
			s.execute(sql);
			System.out.println("FAIL - SQL expected to throw exception");
		} catch (SQLException sqle) {
			expectedException(sqle);
		}
	}
	private static void expectedException(SQLException sqle) {
		String sqlState = sqle.getSQLState();
		if (sqlState == null) {
			sqlState = "<NULL>";
		}
		System.out.println("EXPECTED SQL Exception: (" + sqlState + ") " + sqle.getMessage());
	}

}