concatetests.java

derby database source code.good for you.

			s.executeUpdate("update t2 set vcbd2 = vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2");
			verifyByteData(s.executeQuery("select vcbd2 from t2"), columnVCBD2value);
			s.executeUpdate("update t2 set lvcbd1 = vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2");
			verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);

			System.out.println("Test2e boundary test - CHAR FOR BIT DATA and VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA when concatenated string > 4000");
			//operands CHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
			//concatenated string is > 4000 characters long in following 2 updates
			columnLVCBD1value = new byte[4001];
			System.arraycopy(columnCBD0value, 0, columnLVCBD1value, 0, columnCBD0value.length);
			System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, 1, columnVCBD2value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select cbd0||vcbd2 from t2)"), concatenatedSQLTypesNames[6]);
			verifyByteData(s.executeQuery("values(select cbd0||vcbd2 from t2)"), columnLVCBD1value);
			s.executeUpdate("update t2 set lvcbd1 = cbd0||vcbd2 ");
			verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);

			System.out.println("Test2f boundary test - VARCHAR FOR BIT DATA and CHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA when concatenated string > 4000");
			//operands VARCHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
			columnLVCBD1value = new byte[4001];
			System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, 0, columnVCBD2value.length);
			System.arraycopy(columnCBD0value, 0, columnLVCBD1value, 4000, columnCBD0value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd2 || cbd0 from t2)"), concatenatedSQLTypesNames[6]);
			verifyByteData(s.executeQuery("values(select vcbd2 || cbd0 from t2)"), columnLVCBD1value);
			s.executeUpdate("update t2 set lvcbd1 = vcbd2 || cbd0");
			verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);

			System.out.println("Test2g - CHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA");
			//operands CHAR(A) FOR BIT DATA, LONG VARCHAR FOR BIT DATA then result is LONG VARCHAR FOR BIT DATA
			byte[] tmpColumnLVCBD1value = new byte[4001];
			System.arraycopy(columnLVCBD1value, 0, tmpColumnLVCBD1value, 0, columnLVCBD1value.length);
			columnLVCBD1value = new byte[4002];
			System.arraycopy(columnCBD0value, 0, columnLVCBD1value, 0, columnCBD0value.length);
			System.arraycopy(tmpColumnLVCBD1value, 0, columnLVCBD1value, 1, tmpColumnLVCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select cbd0 || lvcbd1 from t2)"), concatenatedSQLTypesNames[6]);
			verifyByteData(s.executeQuery("values(select cbd0 || lvcbd1 from t2)"), columnLVCBD1value);
			s.executeUpdate("update t2 set lvcbd1 = cbd0 || lvcbd1");
			verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);

			System.out.println("Test2h - VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA");
			//operands VARCHAR(A) FOR BIT DATA, LONG VARCHAR FOR BIT DATA then result is LONG VARCHAR FOR BIT DATA
			tmpColumnLVCBD1value = new byte[4002];
			System.arraycopy(columnLVCBD1value, 0, tmpColumnLVCBD1value, 0, columnLVCBD1value.length);
			columnLVCBD1value = new byte[4266];
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0, columnVCBD1value.length);
			System.arraycopy(tmpColumnLVCBD1value, 0, columnLVCBD1value, 264, tmpColumnLVCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || lvcbd1 from t2)"), concatenatedSQLTypesNames[6]);
			verifyByteData(s.executeQuery("values(select vcbd1 || lvcbd1 from t2)"), columnLVCBD1value);
			s.executeUpdate("update t2 set lvcbd1 = vcbd1 || lvcbd1");
			verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);

			System.out.println("Test2i - VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string < 4001");
			//operands VARCHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B<4001 then result is VARCHAR(A+B) FOR BIT DATA
			columnLVCBD1value = new byte[528];
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 264, columnVCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || vcbd1 from t2)"), concatenatedSQLTypesNames[5]);
			verifyByteData(s.executeQuery("values(select vcbd1 || vcbd1 from t2)"), columnLVCBD1value);
			s.executeUpdate("update t2 set lvcbd1 = vcbd1 || vcbd1");
			verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);

			System.out.println("Test2j boundary test - VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA when concatenated string = 4001");
			//operands VARCHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
			columnLVCBD1value = new byte[4001];
			System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, 0, columnVCBD2value.length);
			System.arraycopy(columnVCBD3value, 0, columnLVCBD1value, 4000, columnVCBD3value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd2 || vcbd3 from t2)"), concatenatedSQLTypesNames[6]);
			verifyByteData(s.executeQuery("values(select vcbd2 || vcbd3 from t2)"), columnLVCBD1value);
			s.executeUpdate("update t2 set lvcbd1 = vcbd2 || vcbd3");
			verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);

			System.out.println("Test2j - VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA when concatenated string > 4000");
			//operands VARCHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B>4000 then result is LONG VARCHAR FOR BIT DATA
			columnLVCBD1value = new byte[columnVCBD1value.length + columnVCBD2value.length];
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0, columnVCBD1value.length);
			System.arraycopy(columnVCBD2value, 0, columnLVCBD1value, columnVCBD1value.length, columnVCBD2value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || vcbd2 from t2)"), concatenatedSQLTypesNames[6]);
			verifyByteData(s.executeQuery("values(select vcbd1 || vcbd2 from t2)"), columnLVCBD1value);
			s.executeUpdate("update t2 set lvcbd1 = vcbd1 || vcbd2");
			verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);

			System.out.println("Test2k - LONG VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA concatenations will give result type of LONG VARCHAR FOR BIT DATA");
			//operands LONG VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA then result is LONG VARCHAR FOR BIT DATA
			tmpColumnLVCBD1value = new byte[columnLVCBD1value.length];
			System.arraycopy(columnLVCBD1value, 0, tmpColumnLVCBD1value, 0, columnLVCBD1value.length);
			columnLVCBD1value = new byte[tmpColumnLVCBD1value.length + tmpColumnLVCBD1value.length];
			System.arraycopy(tmpColumnLVCBD1value, 0, columnLVCBD1value, 0, tmpColumnLVCBD1value.length);
			System.arraycopy(tmpColumnLVCBD1value, 0, columnLVCBD1value, tmpColumnLVCBD1value.length, tmpColumnLVCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select lvcbd1 || lvcbd1 from t2)"), concatenatedSQLTypesNames[6]);
			verifyByteData(s.executeQuery("values(select lvcbd1 || lvcbd1 from t2)"), columnLVCBD1value);
			s.executeUpdate("update t2 set lvcbd1 = lvcbd1 || lvcbd1");
			verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);

			System.out.println("Test2l - CHAR FOR BIT DATA and LONGVARCHAR FOR BIT DATA concatenation resulting in concatenated string > 32700");
			byte[] tmpColumnLVCBD2value = new byte[columnLVCBD2value.length];
			System.arraycopy(columnLVCBD2value, 0, tmpColumnLVCBD2value, 0, columnLVCBD2value.length);
			columnLVCBD2value = new byte[columnCBD2value.length + tmpColumnLVCBD2value.length];
			System.arraycopy(columnCBD2value, 0, columnLVCBD2value, 0, columnCBD2value.length);
			System.arraycopy(tmpColumnLVCBD2value, 0, columnLVCBD2value, columnCBD2value.length, tmpColumnLVCBD2value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select cbd2 || lvcbd2 from t2)"), concatenatedSQLTypesNames[6]);
			verifyByteData(s.executeQuery("values(select cbd2 || lvcbd2 from t2)"), columnLVCBD2value);
			s.executeUpdate("update t2 set lvcbd2 = cbd2 || lvcbd2");
			verifyByteData(s.executeQuery("select lvcbd2 from t2"), columnLVCBD2value);

			System.out.println("Test2m - VARCHAR FOR BIT DATA and LONGVARCHAR FOR BIT DATA concatenation resulting in concatenated string > 32700");
			tmpColumnLVCBD2value = new byte[columnLVCBD2value.length];
			System.arraycopy(columnLVCBD2value, 0, tmpColumnLVCBD2value, 0, columnLVCBD2value.length);
			columnLVCBD2value = new byte[columnVCBD1value.length + tmpColumnLVCBD2value.length];
			System.arraycopy(columnVCBD1value, 0, columnLVCBD2value, 0, columnVCBD1value.length);
			System.arraycopy(tmpColumnLVCBD2value, 0, columnLVCBD2value, columnVCBD1value.length, tmpColumnLVCBD2value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1 || lvcbd2 from t2)"), concatenatedSQLTypesNames[6]);
			verifyByteData(s.executeQuery("values(select vcbd1 || lvcbd2 from t2)"), columnLVCBD2value);
			s.executeUpdate("update t2 set lvcbd2 = vcbd1 || lvcbd2");
			verifyByteData(s.executeQuery("select lvcbd2 from t2"), columnLVCBD2value);

			System.out.println("Test2n - LONGVARCHAR FOR BIT DATA and LONGVARCHAR FOR BIT DATA concatenation resulting in concatenated string > 32700");
			tmpColumnLVCBD2value = new byte[columnLVCBD2value.length];
			System.arraycopy(columnLVCBD2value, 0, tmpColumnLVCBD2value, 0, columnLVCBD2value.length);
			columnLVCBD2value = new byte[columnLVCBD1value.length + tmpColumnLVCBD2value.length];
			System.arraycopy(columnLVCBD1value, 0, columnLVCBD2value, 0, columnLVCBD1value.length);
			System.arraycopy(tmpColumnLVCBD2value, 0, columnLVCBD2value, columnLVCBD1value.length, tmpColumnLVCBD2value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select lvcbd1 || lvcbd2 from t2)"), concatenatedSQLTypesNames[6]);
			verifyByteData(s.executeQuery("values(select lvcbd1 || lvcbd2 from t2)"), columnLVCBD2value);
			s.executeUpdate("update t2 set lvcbd2 = lvcbd1 || lvcbd2");
			verifyByteData(s.executeQuery("select lvcbd2 from t2"), columnLVCBD2value);

			try { //this is if we ever run the test against DB2, we want to make sure table doesn't already exist in DB2
			s.executeUpdate("drop table testBLOB_MAIN");
			} catch(Exception ex) {}
			s.executeUpdate("create table testBLOB_MAIN (cbd1 CHAR(10) FOR BIT DATA, vcbd1 VARCHAR(100) FOR BIT DATA, lvcbd1 LONG VARCHAR FOR BIT DATA, blob1 BLOB(2G), blob2 BLOB(256), blob3 BLOB(1M))");
			ps = conn.prepareStatement("insert into testBLOB_MAIN values(?,?,?,?,?,?)");
			columnCBD1value = new byte[10];
			java.util.Arrays.fill(columnCBD1value, (byte)'a');
			ps.setBytes(1, columnCBD1value);
			columnVCBD1value = new byte[10];
			java.util.Arrays.fill(columnVCBD1value, (byte)'b');
			ps.setBytes(2, columnVCBD1value);
			columnLVCBD1value = new byte[10];
			java.util.Arrays.fill(columnLVCBD1value, (byte)'c');
			ps.setBytes(3, columnLVCBD1value);
			byte[] columnBLOB1value = new byte[10];
			java.util.Arrays.fill(columnBLOB1value, (byte)'d');
			ps.setBytes(4, columnBLOB1value);
			byte[] columnBLOB2value = new byte[10];
			java.util.Arrays.fill(columnBLOB2value, (byte)'e');
			ps.setBytes(5, columnBLOB2value);
			byte[] columnBLOB3value = new byte[10];
			java.util.Arrays.fill(columnBLOB3value, (byte)'f');
			ps.setBytes(6, columnBLOB3value);
			ps.executeUpdate();

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

			System.out.println("Test2p - BLOB(A) and CHAR(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 + columnCBD1value.length];
			System.arraycopy(tmpValue, 0, columnBLOB2value, 0, tmpValue.length);
			System.arraycopy(columnCBD1value, 0, columnBLOB2value, tmpValue.length, columnCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select blob2 || cbd1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select blob2 || cbd1 from testBLOB_MAIN)"), columnBLOB2value);
			s.executeUpdate("update testBLOB_MAIN set blob2 =  cast((blob2 || cbd1) as blob(256))");
			verifyByteData(s.executeQuery("select blob2 from testBLOB_MAIN"), columnBLOB2value);

			System.out.println("Test2q - CHAR(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[columnCBD1value.length + tmpValue.length];
			System.arraycopy(columnCBD1value, 0, columnBLOB1value, 0, columnCBD1value.length);
			System.arraycopy(tmpValue, 0, columnBLOB1value, columnCBD1value.length, tmpValue.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select cbd1 || blob1 from testBLOB_MAIN)"), concatenatedSQLTypesNames[7]);
			verifyByteData(s.executeQuery("values(select cbd1 || blob1 from testBLOB_MAIN)"), columnBLOB1value);
			s.executeUpdate("update testBLOB_MAIN set blob1 = cast((cbd1 || blob1) as blob(2G))");
			verifyByteData(s.executeQuery("select blob1 from testBLOB_MAIN"), columnBLOB1value);

			System.out.println("Test2r - BLOB(A) and CHAR(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 + columnCBD1value.length];
			System.arraycopy(tmpValue, 0, columnBLOB1value, 0, tmpValue.length);