concatetests.java

derby database source code.good for you.

      columnCLOB1value = columnCLOB1value+columnLVC1value;
			dumpSomeMetaDataInfo(s.executeQuery("values(select clob1 || lvc1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
			verifyStringData(s.executeQuery("values(select clob1 || lvc1 from testCLOB_MAIN)"), columnCLOB1value);
			s.executeUpdate("update testCLOB_MAIN set clob1 = clob1 || lvc1");
			verifyStringData(s.executeQuery("select clob1 from testCLOB_MAIN"), columnCLOB1value);

			System.out.println("Test11a - CLOB(A) and CLOB(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
      columnCLOB2value = columnCLOB2value+columnCLOB3value;
			dumpSomeMetaDataInfo(s.executeQuery("values(select clob2 || clob3 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
			verifyStringData(s.executeQuery("values(select clob2 || clob3 from testCLOB_MAIN)"), columnCLOB2value);
			s.executeUpdate("update testCLOB_MAIN set clob2 = clob2 || clob3");
			verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);

			System.out.println("Test11b - CLOB(A) and CLOB(B) concatenations will give result type of CLOB(2G) when A+B>2G");
      columnCLOB1value = columnCLOB2value+columnCLOB1value;
			dumpSomeMetaDataInfo(s.executeQuery("values(select clob2 || clob1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
			verifyStringData(s.executeQuery("values(select clob2 || clob1 from testCLOB_MAIN)"), columnCLOB1value);
			s.executeUpdate("update testCLOB_MAIN set clob1 = clob2 || clob1");
			verifyStringData(s.executeQuery("select clob1 from testCLOB_MAIN"), columnCLOB1value);

			System.out.println("Test12 - try 2 empty string concatenation and verify that length comes back as 0 for the result");
			dumpSomeMetaDataInfo(s.executeQuery("values('' || '')"), concatenatedSQLTypesNames[0]);
			verifyStringData(s.executeQuery("values('' || '')"), "");

			System.out.println("Test13 - Prepared statement with CLOB(A) and ? concatenations will give result type of CLOB(A+length of ?)");
			System.out.println("  Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR of length 32672 rather than CLOB.");
			try {
			s.executeUpdate("drop table ct");
			} catch(Exception ex) {}
			s.executeUpdate("create table ct (c CLOB(100K))");
			String cData = Formatters.repeatChar("c",32700);
			String cData1 = "aa";
			String cConcatenatedData = cData1 + cData;
			//Prior to fix for Derby-124, the ? was getting bound to VARCHAR with max length of 32670
			//As a fix for this, if one of the operands of concatenation is CLOB, then the ? parameter would be bound to CLOB as well
			System.out.println("  preapre statement with clob||?");
			ps = conn.prepareStatement("insert into ct values (cast ('aa' as CLOB) || ?)");
			ps.setString(1, cData);
			ps.execute();
			verifyStringData(s.executeQuery("select c from ct"), cConcatenatedData);
			s.executeUpdate("delete from ct");
			System.out.println("  Test - preapre statement with clob||cast(? to cLOB)");
			ps = conn.prepareStatement("insert into ct values (cast ('aa' as CLOB) || cast(? as CLOB))");
			ps.setString(1, cData);
			ps.execute();
			verifyStringData(s.executeQuery("select c from ct"), cConcatenatedData);
			s.executeUpdate("delete from ct");

			s.executeUpdate("drop table testCLOB_MAIN");
			s.executeUpdate("drop table t1");
			s.executeUpdate("drop table ct");
			System.out.println("Test1 finished - CHAR, VARCHAR, LONGVARCHAR and CLOB concatenation tests");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testCharForBitDataConcatenation( Connection conn) throws Throwable
	{
    try {
			System.out.println("Test2 - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR FOR BIT DATA and BLOB concatenation tests");
			Statement s = conn.createStatement();
			byte[] columnCBD0value = {(byte)'a'};
			byte[] columnCBD1value;
			byte[] columnCBD2value = new byte[40];
			java.util.Arrays.fill(columnCBD2value, (byte)'a');
			byte[] columnCBD3value = new byte[14];
			java.util.Arrays.fill(columnCBD3value, (byte)'a');
			byte[] columnVCBD1value;
			byte[] columnVCBD2value;
			byte[] columnVCBD3value = {(byte)'a'};
			byte[] columnLVCBD1value;
			byte[] columnLVCBD2value = new byte[32698];
			java.util.Arrays.fill(columnLVCBD2value, (byte)'a');
			byte[] tempStringBuffer;

			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 t2");
			} catch(Exception ex) {}
			s.executeUpdate("create table t2 (cbd0 CHAR(1) FOR BIT DATA, cbd1 CHAR(254) FOR BIT DATA, cbd2 CHAR(40) FOR BIT DATA, cbd3 CHAR(14) FOR BIT DATA, vcbd1 VARCHAR(264) FOR BIT DATA, vcbd2 VARCHAR(4000) FOR BIT DATA, vcbd3 VARCHAR(1) FOR BIT DATA, lvcbd1 LONG VARCHAR FOR BIT DATA, lvcbd2 LONG VARCHAR FOR BIT DATA)");
			PreparedStatement ps = conn.prepareStatement("insert into t2(cbd0, cbd2, cbd3, vcbd3) values (?, ?,?,?)");
			ps.setBytes(1, columnCBD0value);
			ps.setBytes(2, columnCBD2value);
			ps.setBytes(3, columnCBD3value);
			ps.setBytes(4, columnVCBD3value);
			ps.executeUpdate();
			ps = conn.prepareStatement("update t2 set lvcbd2 = ?");
			ps.setBytes(1, columnLVCBD2value);
			ps.executeUpdate();

			System.out.println("Test2a - CHAR FOR BIT DATA concatenations will give result type of CHAR FOR BIT DATA when concatenated string < 255");
			//operands CHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B<255 then result is CHAR(A+B) FOR BIT DATA
			columnCBD1value = new byte[254];
			System.arraycopy(columnCBD2value, 0, columnCBD1value, 0, columnCBD2value.length);
			System.arraycopy(columnCBD2value, 0, columnCBD1value, 40, columnCBD2value.length);
			System.arraycopy(columnCBD2value, 0, columnCBD1value, 80, columnCBD2value.length);
			System.arraycopy(columnCBD2value, 0, columnCBD1value, 120, columnCBD2value.length);
			System.arraycopy(columnCBD2value, 0, columnCBD1value, 160, columnCBD2value.length);
			System.arraycopy(columnCBD2value, 0, columnCBD1value, 200, columnCBD2value.length);
			System.arraycopy(columnCBD3value, 0, columnCBD1value, 240, columnCBD3value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd3 from t2)"), concatenatedSQLTypesNames[4]);
			verifyByteData(s.executeQuery("values(select cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd3 from t2)"), columnCBD1value);
			s.executeUpdate("update t2 set cbd1 = cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd2 || cbd3");
			verifyByteData(s.executeQuery("select cbd1 from t2"), columnCBD1value);

			System.out.println("Test2b boundary test - CHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string = 255");
			//operands CHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B>254 then result is VARCHAR(A+B) FOR BIT DATA
			columnVCBD1value = new byte[255];
			System.arraycopy(columnCBD1value, 0, columnVCBD1value, 0, columnCBD1value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 254, columnCBD0value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select cbd1 || cbd0 from t2)"), concatenatedSQLTypesNames[5]);
			verifyByteData(s.executeQuery("values(select cbd1 || cbd0 from t2)"), columnVCBD1value);
			s.executeUpdate("update t2 set vcbd1 = cbd1 || cbd0");
			verifyByteData(s.executeQuery("select vcbd1 from t2"), columnVCBD1value);

			System.out.println("Test2b - CHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string > 254");
			//operands CHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B>254 then result is VARCHAR(A+B) FOR BIT DATA
			columnVCBD1value = new byte[264];
			System.arraycopy(columnCBD1value, 0, columnVCBD1value, 0, columnCBD1value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 254, columnCBD0value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 255, columnCBD0value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 256, columnCBD0value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 257, columnCBD0value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 258, columnCBD0value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 259, columnCBD0value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 260, columnCBD0value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 261, columnCBD0value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 262, columnCBD0value.length);
			System.arraycopy(columnCBD0value, 0, columnVCBD1value, 263, columnCBD0value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select cbd1 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 from t2)"), concatenatedSQLTypesNames[5]);
			verifyByteData(s.executeQuery("values(select cbd1 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0  from t2)"), columnVCBD1value);
			s.executeUpdate("update t2 set vcbd1 = cbd1 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0 || cbd0");
			verifyByteData(s.executeQuery("select vcbd1 from t2"), columnVCBD1value);

			System.out.println("Test2c - CHAR FOR BIT DATA and VARCHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string < 4001");
			//operands CHAR(A) FOR BIT DATA, VARCHAR(B) FOR BIT DATA and A+B<4001 then result is VARCHAR(A+B) FOR BIT DATA
			//concatenated string 4000 characters long in following updates
			columnLVCBD1value = new byte[4000];
			columnVCBD2value = new byte[4000];
			System.arraycopy(columnCBD2value, 0, columnLVCBD1value, 0, columnCBD2value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 40, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 304, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 568, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 832, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1096, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1360, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1624, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1888, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2152, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2416, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2680, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2944, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3208, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3472, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3736, columnVCBD1value.length);
			System.arraycopy(columnLVCBD1value, 0, columnVCBD2value, 0, columnLVCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1 from t2)"), concatenatedSQLTypesNames[5]);
			verifyByteData(s.executeQuery("values(select cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1 from t2)"), columnVCBD2value);
			s.executeUpdate("update t2 set vcbd2 = cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1");
			verifyByteData(s.executeQuery("select vcbd2 from t2"), columnVCBD2value);
			s.executeUpdate("update t2 set lvcbd1 = cbd2||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1");
			verifyByteData(s.executeQuery("select lvcbd1 from t2"), columnLVCBD1value);

			System.out.println("Test2d - VARCHAR FOR BIT DATA and CHAR FOR BIT DATA concatenations will give result type of VARCHAR FOR BIT DATA when concatenated string < 4001");
			//operands VARCHAR(A) FOR BIT DATA, CHAR(B) FOR BIT DATA and A+B<4001 then result is VARCHAR(A+B) FOR BIT DATA
			//concatenated string 4000 characters long in following updates
			columnLVCBD1value = new byte[4000];
			columnVCBD2value = new byte[4000];
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 0, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 264, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 528, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 792, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1056, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1320, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1584, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 1848, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2112, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2376, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2640, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 2904, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3168, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3432, columnVCBD1value.length);
			System.arraycopy(columnVCBD1value, 0, columnLVCBD1value, 3696, columnVCBD1value.length);
			System.arraycopy(columnCBD2value, 0, columnLVCBD1value, 3960, columnCBD2value.length);
			System.arraycopy(columnLVCBD1value, 0, columnVCBD2value, 0, columnLVCBD1value.length);
			dumpSomeMetaDataInfo(s.executeQuery("values(select vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2 from t2)"), concatenatedSQLTypesNames[5]);
			verifyByteData(s.executeQuery("values(select vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||vcbd1||cbd2 from t2)"), columnLVCBD1value);