concatetests.java

derby database source code.good for you.

			System.arraycopy(columnCBD1value, 0, columnBLOB1value, tmpValue.length, columnCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select blob1 || cbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select blob1 || cbd1 from testBLOB_MAIN)"), columnBLOB1value);
			s.executeUpdate("update testBLOB_MAIN set blob1 = cast((blob1 || cbd1) as blob(2G))");
			verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);

			System.out.println("Test2s - VARCHAR(A) FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(A+B) when A+B<2G");
			tmpValue = new byte[columnBLOB2value.length];
			System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
			columnBLOB2value = new byte[columnVCBD1value.length + tmpValue.length];
			System.arraycopy(columnVCBD1value, 0, columnBLOB2value, 0, columnVCBD1value.length);
			System.arraycopy(tmpValue, 0, columnBLOB2value, columnVCBD1value.length, tmpValue.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || blob2 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select vcbd1 || blob2 from testBLOB_MAIN)"), columnBLOB2value);
			s.executeUpdate("update testBLOB_MAIN set blob2 = cast((vcbd1 || blob2) as blob(256))");
			verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);

			System.out.println("Test2t - BLOB(A) and VARCHAR(B) FOR BIT DATA concatenations will give result type of BLOB(A+B) when A+B<2G");
			tmpValue = new byte[columnBLOB2value.length];
			System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
			columnBLOB2value = new byte[tmpValue.length + columnVCBD1value.length];
			System.arraycopy(tmpValue, 0, columnBLOB2value, 0, tmpValue.length);
			System.arraycopy(columnVCBD1value, 0, columnBLOB2value, tmpValue.length, columnVCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select blob2 || vcbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select blob2 || vcbd1 from testBLOB_MAIN)"), columnBLOB2value);
			s.executeUpdate("update testBLOB_MAIN set blob2 = cast((blob2 || vcbd1) as blob(256))");
			verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);

			System.out.println("Test2u - VARCHAR(A) FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(2G) when A+B>2G");
			tmpValue = new byte[columnBLOB1value.length];
			System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
			columnBLOB1value = new byte[columnVCBD1value.length + tmpValue.length];
			System.arraycopy(columnVCBD1value, 0, columnBLOB1value, 0, columnVCBD1value.length);
			System.arraycopy(tmpValue, 0, columnBLOB1value, columnVCBD1value.length, tmpValue.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || blob1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select vcbd1 || blob1 from testBLOB_MAIN)"), columnBLOB1value);
			s.executeUpdate("update testBLOB_MAIN set blob1 = cast((vcbd1 || blob1) as blob(2G))");
			verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);

			System.out.println("Test2v - BLOB(A) and VARCHAR(B) FOR BIT DATA concatenations will give result type of BLOB(2G) when A+B>2G");
			tmpValue = new byte[columnBLOB1value.length];
			System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
			columnBLOB1value = new byte[tmpValue.length + columnVCBD1value.length];
			System.arraycopy(tmpValue, 0, columnBLOB1value, 0, tmpValue.length);
			System.arraycopy(columnVCBD1value, 0, columnBLOB1value, tmpValue.length, columnVCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select blob1 || vcbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select blob1 || vcbd1 from testBLOB_MAIN)"), columnBLOB1value);
			s.executeUpdate("update testBLOB_MAIN set blob1 = cast((blob1 || vcbd1) as blob(2G))");
			verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);

			System.out.println("Test2w - LONG VARCHAR FOR BIT DATA and BLOB(A) concatenations will give result type of BLOB(A+32K) when A+32K<2G");
			tmpValue = new byte[columnBLOB2value.length];
			System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
			columnBLOB2value = new byte[columnLVCBD1value.length + tmpValue.length];
			System.arraycopy(columnLVCBD1value, 0, columnBLOB2value, 0, columnLVCBD1value.length);
			System.arraycopy(tmpValue, 0, columnBLOB2value, columnLVCBD1value.length, tmpValue.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select lvcbd1 || blob2 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select lvcbd1 || blob2 from testBLOB_MAIN)"), columnBLOB2value);
			s.executeUpdate("update testBLOB_MAIN set blob2 = cast((lvcbd1 || blob2) as blob(256))");
			verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);

			System.out.println("Test2x - BLOB(A) and LONG VARCHAR FOR BIT DATA concatenations will give result type of BLOB(A+32K) when A+32K<2G");
			tmpValue = new byte[columnBLOB2value.length];
			System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
			columnBLOB2value = new byte[tmpValue.length + columnLVCBD1value.length];
			System.arraycopy(tmpValue, 0, columnBLOB2value, 0, tmpValue.length);
			System.arraycopy(columnLVCBD1value, 0, columnBLOB2value, tmpValue.length, columnLVCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select blob2 || lvcbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select blob2 || lvcbd1 from testBLOB_MAIN)"), columnBLOB2value);
			s.executeUpdate("update testBLOB_MAIN set blob2 = cast((blob2 || lvcbd1) as blob(256))");
			verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);

			System.out.println("Test2y - LONG VARCHAR FOR BIT DATA and BLOB(B) concatenations will give result type of BLOB(2G) when A+32K>2G");
			tmpValue = new byte[columnBLOB1value.length];
			System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
			columnBLOB1value = new byte[columnLVCBD1value.length + tmpValue.length];
			System.arraycopy(columnLVCBD1value, 0, columnBLOB1value, 0, columnLVCBD1value.length);
			System.arraycopy(tmpValue, 0, columnBLOB1value, columnLVCBD1value.length, tmpValue.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select lvcbd1 || blob1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select lvcbd1 || blob1 from testBLOB_MAIN)"), columnBLOB1value);
			s.executeUpdate("update testBLOB_MAIN set blob1 = cast((lvcbd1 || blob1) as blob(2G))");
			verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);

			System.out.println("Test2z - BLOB(A) and LONG VARCHAR FOR BIT DATA concatenations will give result type of BLOB(2G) when A+32K>2G");
			tmpValue = new byte[columnBLOB1value.length];
			System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
			columnBLOB1value = new byte[tmpValue.length + columnLVCBD1value.length];
			System.arraycopy(tmpValue, 0, columnBLOB1value, 0, tmpValue.length);
			System.arraycopy(columnLVCBD1value, 0, columnBLOB1value, tmpValue.length, columnLVCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select blob1 || lvcbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select blob1 || lvcbd1 from testBLOB_MAIN)"), columnBLOB1value);
			s.executeUpdate("update testBLOB_MAIN set blob1 = cast((blob1 || lvcbd1) as blob(2G))");
			verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);

			System.out.println("Test21a - BLOB(A) and BLOB(B) concatenations will give result type of BLOB(A+B) when A+B<2G");
			tmpValue = new byte[columnBLOB2value.length];
			System.arraycopy(columnBLOB2value, 0, tmpValue, 0, columnBLOB2value.length);
			columnBLOB2value = new byte[tmpValue.length + columnBLOB3value.length];
			System.arraycopy(tmpValue, 0, columnBLOB2value, 0, tmpValue.length);
			System.arraycopy(columnBLOB3value, 0, columnBLOB2value, tmpValue.length, columnBLOB3value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select blob2 || blob3 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select blob2 || blob3 from testBLOB_MAIN)"), columnBLOB2value);
			s.executeUpdate("update testBLOB_MAIN set blob2 = blob2 || blob3");
			verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);

			System.out.println("Test21b - BLOB(A) and BLOB(B) concatenations will give result type of BLOB(2G) when A+B>2G");
			tmpValue = new byte[columnBLOB1value.length];
			System.arraycopy(columnBLOB1value, 0, tmpValue, 0, columnBLOB1value.length);
			columnBLOB1value = new byte[columnBLOB2value.length + tmpValue.length];
			System.arraycopy(columnBLOB2value, 0, columnBLOB1value, 0, columnBLOB2value.length);
			System.arraycopy(tmpValue, 0, columnBLOB1value, columnBLOB2value.length, tmpValue.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select blob2 || blob1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select blob2 || blob1 from testBLOB_MAIN)"), columnBLOB1value);
			s.executeUpdate("update testBLOB_MAIN set blob1 = blob2 || blob1");
			verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);

			System.out.println("Test22 - try 2 empty char for bit data concatenation and verify that length comes back as 0 for the result");
			dumpSomeMetaDataInfo(s.executeQuery("values(X'' || X'')"), concatenatedSQLTypesNames[4]);

			System.out.println("Test23 - Derby-124 try concatenation in a prepared statement with one operand casted to BLOB and other as ? parameter");
			System.out.println("  Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR TO BIT DATA of length 32672 rather than BLOB.");
			System.out.println("  That caused truncation exception when ? parameter was set to > 32672 bytes");
			try {
			s.executeUpdate("drop table bt");
			} catch(Exception ex) {}
			s.executeUpdate("create table bt (b BLOB(100K))");
			byte [] bData = new byte[32700];
			for (int i = 0; i < bData.length; i++)
        bData[i] = (byte)(i % 10);
			byte [] bData1 = new byte[2];
			bData1[0] = (byte) 0x10;
			bData1[1] = (byte) 0x10;
			byte [] bConcatenatedData = new byte[32702];
			System.arraycopy(bData1, 0, bConcatenatedData, 0, bData1.length);
			System.arraycopy(bData, 0, bConcatenatedData, bData1.length, bData.length);
			//Prior to fix for Derby-124, the ? was getting bound to VARCHAR FOR BIT DATA with max length of 32670
			//And when ps.setBytes() set 32700 bytes into the ? parameter, at ps.execute() time, you got following exception
			//ERROR 22001: A truncation error was encountered trying to shrink VARCHAR () FOR BIT DATA 'XX-RESOLVE-XX' to length 32672.
			//As a fix for this, if one of the operands of concatenation is BLOB, then the ? parameter would be bound to BLOB as well      
			System.out.println("  preapre statement with blob||?");
			ps = conn.prepareStatement("insert into bt values (cast (x'1010' as BLOB) || ?)");
			ps.setBytes(1, bData);
			ps.execute();
			verifyByteData(s.executeQuery("select b from bt"), bConcatenatedData);
			s.executeUpdate("delete from bt");
			System.out.println("  Test - preapre statement with blob||cast(? to BLOB)");
			ps = conn.prepareStatement("insert into bt values (cast (x'1010' as BLOB) || cast(? as BLOB))");
			ps.setBytes(1, bData);
			ps.execute();
			verifyByteData(s.executeQuery("select b from bt"), bConcatenatedData);

			s.executeUpdate("drop table testBLOB_MAIN");  
			s.executeUpdate("drop table t2");
			s.executeUpdate("drop table bt");
			System.out.println("Test2 finished - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR FOR BIT DATA and BLOB concatenation tests");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	private static void verifyStringData(ResultSet rs, String expectedValue) throws SQLException
	{
		if (rs == null)
		{
			System.out.println("<NULL>");
			return;
		}

		rs.next();
		if (!(rs.getString(1).equals(expectedValue))) {
			System.out.println("ERROR: expected value does not match actual value");
			System.out.println("expected value is " + expectedValue);
			System.out.println("what we got here is " + rs.getString(1));
		}
		else
		{
			System.out.println("Successful " + rs.getMetaData().getColumnTypeName(1) + " read of " + expectedValue.length() + " characters");
		}
	}

	private static void verifyByteData(ResultSet rs, byte[] expectedValue) throws SQLException
	{
		if (rs == null)