concatetests.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.concateTests

   Copyright 2004 The Apache Software Foundation or its licensors, as applicable.

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyTesting.functionTests.tests.lang;

import java.io.*;
import java.sql.*;
import java.util.Arrays;

import org.apache.derby.tools.ij;
import org.apache.derbyTesting.functionTests.util.Formatters;

import java.io.ByteArrayInputStream; 

/**
  Concatenation tests for various datatypes
 */
public class concateTests
{

	private static String[] concatenatedSQLTypesNames =
	{
		/*0*/ "CHAR",
		/*1*/ "VARCHAR",
		/*2*/ "LONG VARCHAR",
		/*3*/ "CLOB",
		/*4*/ "CHAR () FOR BIT DATA",
		/*5*/ "VARCHAR () FOR BIT DATA",
		/*6*/ "LONG VARCHAR FOR BIT DATA",
		/*7*/ "BLOB",

	};

	public static void main (String[] argv) throws Throwable
	{
		ij.getPropertyArg(argv);
		Connection conn = ij.startJBMS();

		testCharConcatenation(conn);
		testCharForBitDataConcatenation(conn);
	}

	public static void testCharConcatenation( Connection conn) throws Throwable
	{
    try {
			System.out.println("Test1 - CHAR, VARCHAR, LONGVARCHAR and CLOB concatenation tests");

			String columnC1value;
			String columnC2value = Formatters.repeatChar("a",40);
			String columnVC1value;
			String columnVC2value;
			String columnVC3value = "z";
			String columnLVC1value;
			String columnLVC2value = Formatters.repeatChar("b",32698);
			StringBuffer tempStringBuffer = new StringBuffer();

			Statement s = conn.createStatement();
			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 t1");
			} catch(Exception ex) {}
			s.executeUpdate("create table t1 (c1 char(254), c2 char(40), vc1 varchar(264), vc2 varchar(4000), vc3 varchar(1), lvc1 long varchar, lvc2 long varchar)");
			PreparedStatement ps = conn.prepareStatement("insert into t1(c2, vc3) values(?, ?)");
      ps.setString(1,columnC2value);
      ps.setString(2,columnVC3value);
			ps.executeUpdate();
			ps = conn.prepareStatement("update t1 set lvc2 = ?");
      ps.setString(1,columnLVC2value);
			ps.executeUpdate();

			System.out.println("Test1a - CHAR concatenations will give result type of CHAR when concatenated string < 255");
			//operands CHAR(A) CHAR(B) and A+B<255 then result is CHAR(A+B)
			dumpSomeMetaDataInfo(s.executeQuery("values(select c2 || c2 || c2 || c2 || c2 || c2 || '12345678901234' from t1)"), concatenatedSQLTypesNames[0]);
			tempStringBuffer = new StringBuffer(columnC2value);
      tempStringBuffer.append(columnC2value).append(columnC2value).append(columnC2value).append(columnC2value).append(columnC2value);
      tempStringBuffer.append("12345678901234");
      columnC1value = tempStringBuffer.toString();
			verifyStringData(s.executeQuery("values(select c2 || c2 || c2 || c2 || c2 || c2 || '12345678901234' from t1)"), columnC1value);
			s.executeUpdate("update t1 set c1 = c2 || c2 || c2 || c2 || c2 || c2 || '12345678901234'");
			verifyStringData(s.executeQuery("select c1 from t1"), columnC1value);

			System.out.println("Test1b boundary test - CHAR concatenations will give result type of VARCHAR when concatenated string = 255");
			//operands CHAR(A) CHAR(B) and A+B>254 then result is VARCHAR(A+B)
      columnVC1value = columnC1value + "1";
			dumpSomeMetaDataInfo(s.executeQuery("values(select c1 || '1' from t1)"), concatenatedSQLTypesNames[1]);
			verifyStringData(s.executeQuery("values(select c1 || '1' from t1)"), columnVC1value);
			s.executeUpdate("update t1 set vc1 = c1 || '1'");
			verifyStringData(s.executeQuery("select vc1 from t1"), columnVC1value);

			System.out.println("Test1b - CHAR concatenations will give result type of VARCHAR when concatenated string > 254");
			//operands CHAR(A) CHAR(B) and A+B>254 then result is VARCHAR(A+B)
      columnVC1value = columnC1value + "1234567890";
			dumpSomeMetaDataInfo(s.executeQuery("values(select c1 || '1234567890' from t1)"), concatenatedSQLTypesNames[1]);
			verifyStringData(s.executeQuery("values(select c1 || '1234567890' from t1)"), columnVC1value);
			s.executeUpdate("update t1 set vc1 = c1 || '1234567890'");
			verifyStringData(s.executeQuery("select vc1 from t1"), columnVC1value);

			System.out.println("Test1c - CHAR and VARCHAR concatenations will give result type of VARCHAR when concatenated string < 4001");
			//operands CHAR(A) VARCHAR(B) and A+B<4001 then result is VARCHAR(A+B)
			//concatenated string 4000 characters long in following updates
			tempStringBuffer = new StringBuffer(columnC2value);
      tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
      tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
      tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
      columnVC2value = tempStringBuffer.toString();
      columnLVC1value = tempStringBuffer.toString();
			dumpSomeMetaDataInfo(s.executeQuery("values(select c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1 from t1)"), concatenatedSQLTypesNames[1]);
			verifyStringData(s.executeQuery("values(select c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1 from t1)"), columnVC2value);
			s.executeUpdate("update t1 set vc2 = c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1");
			verifyStringData(s.executeQuery("select vc2 from t1"), columnVC2value);
			s.executeUpdate("update t1 set lvc1 = c2||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1");
			verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);

			System.out.println("Test1d - VARCHAR and CHAR concatenations will give result type of VARCHAR when concatenated string < 4001");
			//operands VARCHAR(A) CHAR(B) and A+B<4001 then result is VARCHAR(A+B)
			//concatenated string 4000 characters long in following updates
			tempStringBuffer = new StringBuffer();
      tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
      tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
      tempStringBuffer.append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value).append(columnVC1value);
			tempStringBuffer.append(columnC2value);
      columnVC2value = tempStringBuffer.toString();
      columnLVC1value = tempStringBuffer.toString();
			dumpSomeMetaDataInfo(s.executeQuery("values(select vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2 from t1)"), concatenatedSQLTypesNames[1]);
			verifyStringData(s.executeQuery("values(select vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2 from t1)"), columnVC2value);
			s.executeUpdate("update t1 set vc2 = vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2");
			verifyStringData(s.executeQuery("select vc2 from t1"), columnVC2value);
			s.executeUpdate("update t1 set lvc1 = vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||vc1||c2");
			verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);

			System.out.println("Test1e boundary test - CHAR and VARCHAR concatenations will give result type of LONG VARCHAR when concatenated string = 4001");
			//operands CHAR(A) VARCHAR(B) and A+B>4000 then result is LONG VARCHAR
			//concatenated string is 4001 characters long in following 2 updates
      columnLVC1value = "a"+columnVC2value;
			dumpSomeMetaDataInfo(s.executeQuery("values(select 'a'||vc2 from t1)"), concatenatedSQLTypesNames[2]);
			verifyStringData(s.executeQuery("values(select 'a'||vc2 from t1)"), columnLVC1value);
			s.executeUpdate("update t1 set lvc1 = 'a'||vc2");
			verifyStringData(s.executeQuery("select lvc1 from t1"), columnLVC1value);

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

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

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

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

			System.out.println("Test1j boundary test - VARCHAR and VARCHAR concatenations will give result type of LONG VARCHAR when concatenated string = 4001");