concatetests.java

derby database source code.good for you.

			//operands VARCHAR(A) VARCHAR(B) and A+B>4000 then result is LONG VARCHAR
      columnLVC1value = columnVC2value+columnVC3value;
			dumpSomeMetaDataInfo(s.executeQuery("values(select vc2 || vc3 from t1)"), concatenatedSQLTypesNames[2]);
			verifyStringData(s.executeQuery("values(select vc2 || vc3 from t1)"), columnLVC1value);
			s.executeUpdate("update t1 set lvc1 = vc2 || vc3");
			verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);

			System.out.println("Test1j - VARCHAR and VARCHAR concatenations will give result type of LONG VARCHAR when concatenated string > 4000");
			//operands VARCHAR(A) VARCHAR(B) and A+B>4000 then result is LONG VARCHAR
      columnLVC1value = columnVC1value+columnVC2value;
			dumpSomeMetaDataInfo(s.executeQuery("values(select vc1 || vc2 from t1)"), concatenatedSQLTypesNames[2]);
			verifyStringData(s.executeQuery("values(select vc1 || vc2 from t1)"), columnLVC1value);
			s.executeUpdate("update t1 set lvc1 = vc1 || vc2");
			verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);

			System.out.println("Test1k - LONG VARCHAR and LONG VARCHAR concatenations will give result type of LONG VARCHAR");
			//operands LONG VARCHAR, LONG VARCHAR then result is LONG VARCHAR
      columnLVC1value = columnLVC1value+columnLVC1value;
			dumpSomeMetaDataInfo(s.executeQuery("values(select lvc1 || lvc1 from t1)"), concatenatedSQLTypesNames[2]);
			verifyStringData(s.executeQuery("values(select lvc1 || lvc1 from t1)"), columnLVC1value);
			s.executeUpdate("update t1 set lvc1 = lvc1 || lvc1");
			verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);

			//operands CHAR(A)/VARCHAR(A)/LONGVARCHAR, LONGVARCHAR and "concatenated string length">32700 does not cause automatic escalation
			//to LOB for compatibility with previous releases. Any such cases would result in an error at runtime
			System.out.println("Test1l - CHAR and LONGVARCHAR concatenation resulting in concatenated string > 32700 will give error");
			try {
				dumpSomeMetaDataInfo(s.executeQuery("values(select c2 || lvc2 from t1)"), concatenatedSQLTypesNames[2]);
				System.out.println("FAIL - should have gotten overflow error for values");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54006"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			try {
				s.executeUpdate("update t1 set lvc2 = c2 || lvc2");
				System.out.println("FAIL - should have gotten overflow error for insert");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54006"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("Test1m - VARCHAR and LONGVARCHAR concatenation resulting in concatenated string > 32700 will give error");
			try {
				dumpSomeMetaDataInfo(s.executeQuery("values(select vc1 || lvc2 from t1)"), concatenatedSQLTypesNames[2]);
				System.out.println("FAIL - should have gotten overflow error for values");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54006"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			try {
				s.executeUpdate("update t1 set lvc2 = vc1 || lvc2");
				System.out.println("FAIL - should have gotten overflow error for insert");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54006"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("Test1n - LONGVARCHAR and LONGVARCHAR concatenation resulting in concatenated string > 32700 will give error");
			try {
				dumpSomeMetaDataInfo(s.executeQuery("values(select lvc1 || lvc2 from t1)"), concatenatedSQLTypesNames[2]);
				System.out.println("FAIL - should have gotten overflow error for values");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54006"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			try {
				s.executeUpdate("update t1 set lvc2 = lvc1 || lvc2");
				System.out.println("FAIL - should have gotten overflow error for insert");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54006"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			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 testCLOB_MAIN");
			} catch(Exception ex) {}
			s.executeUpdate("create table testCLOB_MAIN (c1 char(10), vc1 varchar(100), lvc1 long varchar, clob1 CLOB(2G), clob2 CLOB(256), clob3 CLOB(1M))");
			ps = conn.prepareStatement("insert into testCLOB_MAIN values(?,?,?,?,?,?)");
			columnC1value = "1234567890";
			ps.setString(1, columnC1value);
			columnVC1value = "this is varchar";
			ps.setString(2, columnVC1value);
			columnLVC1value = "this is long varchar";
			ps.setString(3, columnLVC1value);
			String columnCLOB1value = "this is 2G clob";
			ps.setString(4, columnCLOB1value);
			String columnCLOB2value = "this is 256 characters clob";
			ps.setString(5, columnCLOB2value);
			String columnCLOB3value = "this is 1M clob";
			ps.setString(6, columnCLOB3value);
			ps.executeUpdate();

			System.out.println("Test1o - CHAR(A) and CLOB(B) concatenations will give result type of CLOB(A+B) when A+B<2G");
      columnCLOB2value = columnC1value+"this is 256 characters clob";
			dumpSomeMetaDataInfo(s.executeQuery("values(select c1 || clob2 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
			verifyStringData(s.executeQuery("values(select c1 || clob2 from testCLOB_MAIN)"), columnCLOB2value);
			s.executeUpdate("update testCLOB_MAIN set clob2 = c1 || clob2");
			verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);

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

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

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

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

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

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

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

			System.out.println("Test1w - LONG VARCHAR and CLOB(A) concatenations will give result type of CLOB(A+32K) when A+32K<2G");
      columnCLOB2value = columnLVC1value+columnCLOB2value;
			dumpSomeMetaDataInfo(s.executeQuery("values(select lvc1 || clob2 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
			verifyStringData(s.executeQuery("values(select lvc1 || clob2 from testCLOB_MAIN)"), columnCLOB2value);
			s.executeUpdate("update testCLOB_MAIN set clob2 = lvc1 || clob2");
			verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);

			System.out.println("Test1x - CLOB(A) and LONG VARCHAR concatenations will give result type of CLOB(A+32K) when A+32K<2G");
      columnCLOB2value = columnCLOB2value+columnLVC1value;
			dumpSomeMetaDataInfo(s.executeQuery("values(select clob2 || lvc1 from testCLOB_MAIN)"), concatenatedSQLTypesNames[3]);
			verifyStringData(s.executeQuery("values(select clob2 || lvc1 from testCLOB_MAIN)"), columnCLOB2value);
			s.executeUpdate("update testCLOB_MAIN set clob2 = clob2 || lvc1");
			verifyStringData(s.executeQuery("select clob2 from testCLOB_MAIN"), columnCLOB2value);

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

			System.out.println("Test1z - CLOB(A) and LONG VARCHAR concatenations will give result type of CLOB(2G) when A+32K>2G");