coalescetests.java

derby database source code.good for you.

			dumpRS(s.executeQuery("select value(lvc1,vc1) from tB"));

			System.out.println("TestB11a - LONG VARCHAR and LONG VARCHAR operands coalesce(lvc1,lvc2)");
			dumpRS(s.executeQuery("select coalesce(lvc1,lvc2) from tB"));

			System.out.println("TestB11b - LONG VARCHAR and LONG VARCHAR operands value(lvc1,lvc2)");
			dumpRS(s.executeQuery("select value(lvc1,lvc2) from tB"));

			System.out.println("TestB - Focus on CLOB as atleast one of the operands");
			System.out.println("TestB12a - CLOB and CHAR operands coalesce(clob1,c1) with clob1(200) and c1(254)");
			dumpRS(s.executeQuery("select coalesce(clob1,c1) from tB"));

			System.out.println("TestB12b - CLOB and CHAR operands value(clob1,c1) with clob1(200) and c1(254)");
			dumpRS(s.executeQuery("select value(clob1,c1) from tB"));

			System.out.println("TestB13a - CHAR and CLOB operands coalesce(c1,clob2) with c1(254) and clob2(33K)");
			dumpRS(s.executeQuery("select coalesce(c1,clob2) from tB"));

			System.out.println("TestB13b - CHAR and CLOB operands value(c1,clob2) with c1(254) and clob2(33K)");
			dumpRS(s.executeQuery("select value(c1,clob2) from tB"));

			System.out.println("TestB14a - CLOB and VARCHAR operands coalesce(clob1,vc1) with clob1(200) and vc1(253)");
			dumpRS(s.executeQuery("select coalesce(clob1,vc1) from tB"));

			System.out.println("TestB14b - CLOB and VARCHAR operands value(clob1,vc1) with clob1(200) and vc1(253)");
			dumpRS(s.executeQuery("select value(clob1,vc1) from tB"));

			System.out.println("TestB15a - VARCHAR and CLOB operands coalesce(vc2,clob2) with vc2(2000) and clob2(33K)");
			dumpRS(s.executeQuery("select coalesce(vc2,clob2) from tB"));

			System.out.println("TestB15b - VARCHAR and CLOB operands value(vc2,clob2) with vc2(2000) and clob2(33K)");
			dumpRS(s.executeQuery("select value(vc2,clob2) from tB"));

			System.out.println("TestB16a - CLOB and LONG VARCHAR operands coalesce(clob1,lvc1) with clob1(200). The result length will be 32700 (long varchar max length)");
			dumpRS(s.executeQuery("select coalesce(clob1,lvc1) from tB"));

			System.out.println("TestB16b - CLOB and LONG VARCHAR operands value(clob1,lvc1) with clob1(200). The result length will be 32700 (long varchar max length)");
			dumpRS(s.executeQuery("select value(clob1,lvc1) from tB"));

			System.out.println("TestB17a - LONG VARCHAR and CLOB operands coalesce(lvc2,clob2) with clob2(33K). The result length will be 33K since clob length here is > 32700 (long varchar max length)");
			dumpRS(s.executeQuery("select coalesce(lvc2,clob2) from tB"));

			System.out.println("TestB17b - LONG VARCHAR and CLOB operands value(lvc2,clob2) with clob2(33K). The result length will be 33K since clob length here is > 32700 (long varchar max length)");
			dumpRS(s.executeQuery("select value(lvc2,clob2) from tB"));

			System.out.println("TestB18a - CLOB and CLOB operands coalesce(clob1,clob2) with clob1(200) and clob2(33K).");
			dumpRS(s.executeQuery("select coalesce(clob1,clob2) from tB"));

			System.out.println("TestB18b - CLOB and CLOB operands value(clob1,clob2) with clob1(200) and clob2(33K).");
			dumpRS(s.executeQuery("select value(clob1,clob2) from tB"));

			s.executeUpdate("drop table tB");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testCharForBitDataCoalesce( Connection conn) throws Throwable
	{
    try {
			Statement s = conn.createStatement();
			PreparedStatement ps;
    try {
			s.executeUpdate("drop table tC");
    } catch(Exception ex) {}
			s.executeUpdate("create table tC (cbd1 char(254) for bit data, cbd2 char(40) for bit data, vcbd1 varchar(253) for bit data, vcbd2 varchar(2000) for bit data, lvcbd1 long varchar for bit data, lvcbd2 long varchar for bit data, blob1 BLOB(200), blob2 BLOB(33K))");
			ps = conn.prepareStatement("insert into tC values (?,?,?,?,?,?,?,?)");
			ps.setBytes(1, "cbd1 not null".getBytes("US-ASCII"));
			ps.setBytes(2, "cbd2 not null".getBytes("US-ASCII"));
			ps.setBytes(3, "vcbd1 not null".getBytes("US-ASCII"));
			ps.setBytes(4, "vcbd2 not null".getBytes("US-ASCII"));
			ps.setBytes(5, "lvcbd1 not null".getBytes("US-ASCII"));
			ps.setBytes(6, "lvcbd2 not null".getBytes("US-ASCII"));
			ps.setBytes(7, "blob1 not null".getBytes("US-ASCII"));
			ps.setBytes(8, "blob2 not null".getBytes("US-ASCII"));
			ps.executeUpdate();
			ps.setBytes(1, "cbd1 not null but cbd2 is".getBytes("US-ASCII"));
			ps.setBytes(2, null);
			ps.setBytes(3, "vcbd1 not null but vcbd2 is".getBytes("US-ASCII"));
			ps.setBytes(4, null);
			ps.setBytes(5, null);
			ps.setBytes(6, null);
			ps.setBytes(7, null);
			ps.setBytes(8, null);
			ps.executeUpdate();
			ps.setBytes(1, null);
			ps.setBytes(2, "cbd2 not null but cbd1 is".getBytes("US-ASCII"));
			ps.setBytes(3, null);
			ps.setBytes(4, "vcbd2 not null but vcbd1 is".getBytes("US-ASCII"));
			ps.setBytes(5, "lvcbd1 not null again".getBytes("US-ASCII"));
			ps.setBytes(6, "lvcbd2 not null again".getBytes("US-ASCII"));
			ps.setBytes(7, "blob1 not null again".getBytes("US-ASCII"));
			ps.setBytes(8, "blob2 not null again".getBytes("US-ASCII"));
			ps.executeUpdate();
			ps.setBytes(1, null);
			ps.setBytes(2, null);
			ps.setBytes(3, null);
			ps.setBytes(4, null);
			ps.setBytes(5, null);
			ps.setBytes(6, null);
			ps.setBytes(7, null);
			ps.setBytes(8, null);
			ps.executeUpdate();

			System.out.println("TestC - Focus on CHAR FOR BIT DATA as atleast one of the operands");
			System.out.println("TestC1a - 2 CHAR FOR BIT DATA operands coalesce(cbd1,cbd2) with cbd1(254) and cbd2(40)");
			dumpRS(s.executeQuery("select coalesce(cbd1,cbd2) from tC"));

			System.out.println("TestC1b - 2 CHAR FOR BIT DATA operands value(cbd1,cbd2) with cbd1(254) and cbd2(40)");
			dumpRS(s.executeQuery("select value(cbd1,cbd2) from tC"));

			System.out.println("TestC2a - 2 CHAR FOR BIT DATA operands coalesce(cbd2,cbd1) with cbd2(40) and cbd1(254)");
			dumpRS(s.executeQuery("select coalesce(cbd2,cbd1) from tC"));

			System.out.println("TestC2b - 2 CHAR FOR BIT DATA operands value(cbd2,cbd1) with cbd2(40) and cbd1(254)");
			dumpRS(s.executeQuery("select value(cbd2,cbd1) from tC"));

			System.out.println("TestC3a - CHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands coalesce(cbd1,vcbd1) with cbd1(254) and vcbd1(253)");
			dumpRS(s.executeQuery("select coalesce(cbd1,vcbd1) from tC"));

			System.out.println("TestC3b - CHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands value(cbd1,vcbd1) with cbd1(254) and vcbd1(253)");
			dumpRS(s.executeQuery("select value(cbd1,vcbd1) from tC"));

			System.out.println("TestC4a - VARCHAR FOR BIT DATA and CHAR FOR BIT DATA operands coalesce(vcbd1,cbd1) with vcbd1(253) and cbd1(254)");
			dumpRS(s.executeQuery("select coalesce(vcbd1,cbd1) from tC"));

			System.out.println("TestC4b - VARCHAR FOR BIT DATA AND CHAR FOR BIT DATA operands value(vcbd1,cbd1) with vcbd1(253) and cbd1(254)");
			dumpRS(s.executeQuery("select value(vcbd1,cbd1) from tC"));

			System.out.println("TestC - Focus on VARCHAR FOR BIT DATA as atleast one of the operands");
			System.out.println("TestC5a - 2 VARCHAR FOR BIT DATA operands coalesce(vcbd1,vcbd2) with vcbd1(253) and vcbd2(2000)");
			dumpRS(s.executeQuery("select coalesce(vcbd1,vcbd2) from tC"));

			System.out.println("TestC5b - 2 VARCHAR FOR BIT DATA operands value(vcbd1,vcbd2) with vcbd1(253) and vcbd2(2000)");
			dumpRS(s.executeQuery("select value(vcbd1,vcbd2) from tC"));

			System.out.println("TestC6a - 2 VARCHAR FOR BIT DATA operands coalesce(vcbd2,vcbd1) with vcbd2(2000) and vcbd1(253)");
			dumpRS(s.executeQuery("select coalesce(vcbd2,vcbd1) from tC"));

			System.out.println("TestC6b - 2 VARCHAR FOR BIT DATA operands value(vcbd2,vcbd1) with vcbd2(2000) and vcbd1(253)");
			dumpRS(s.executeQuery("select value(vcbd2,vcbd1) from tC"));

			System.out.println("TestC - Focus on LONG VARCHAR FOR BIT DATA as atleast one of the operands");
			System.out.println("TestC7a - CHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands coalesce(cbd1,lvcbd1) with cbd1(254)");
			dumpRS(s.executeQuery("select coalesce(cbd1,lvcbd1) from tC"));

			System.out.println("TestC7b - CHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands value(cbd1,lvcbd1) with cbd1(254)");
			dumpRS(s.executeQuery("select value(cbd1,lvcbd1) from tC"));

			System.out.println("TestC8a - LONG VARCHAR FOR BIT DATA and CHAR FOR BIT DATA operands coalesce(lvcbd1,cbd1) with cbd1(254)");
			dumpRS(s.executeQuery("select coalesce(lvcbd1,cbd1) from tC"));

			System.out.println("TestC8b - LONG VARCHAR FOR BIT DATA and CHAR FOR BIT DATA operands value(lvcbd1,cbd1) with cbd1(254)");
			dumpRS(s.executeQuery("select value(lvcbd1,cbd1) from tC"));

			System.out.println("TestC9a - VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands coalesce(vcbd1,lvcbd1) with vcbd1(253)");
			dumpRS(s.executeQuery("select coalesce(vcbd1,lvcbd1) from tC"));

			System.out.println("TestC9b - VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands value(vcbd1,lvcbd1) with vcbd1(253)");
			dumpRS(s.executeQuery("select value(vcbd1,lvcbd1) from tC"));

			System.out.println("TestC10a - LONG VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands coalesce(lvcbd1,vcbd1) with vcbd1(253)");
			dumpRS(s.executeQuery("select coalesce(lvcbd1,vcbd1) from tC"));

			System.out.println("TestC10b - LONG VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands value(lvcbd1,vcbd1) with vcbd1(253)");
			dumpRS(s.executeQuery("select value(lvcbd1,vcbd1) from tC"));

			System.out.println("TestC11a - LONG VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands coalesce(lvcbd1,lvcbd2)");
			dumpRS(s.executeQuery("select coalesce(lvcbd1,lvcbd2) from tC"));

			System.out.println("TestC11b - LONG VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands value(lvcbd1,lvcbd2)");
			dumpRS(s.executeQuery("select value(lvcbd1,lvcbd2) from tC"));

			System.out.println("TestC - Focus on BLOB as atleast one of the operands");
			try {
				System.out.println("TestC12a - BLOB and CHAR FOR BIT DATA in coalesce(blob1,cbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
				dumpRS(s.executeQuery("select coalesce(blob1,cbd1) from tC"));
				System.out.println("TestC12a - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("42815"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestC12b - BLOB and CHAR FOR BIT DATA in value(blob1,cbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
				dumpRS(s.executeQuery("select value(blob1,cbd1) from tC"));
				System.out.println("TestC12b - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("42815"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestC13a - CHAR FOR BIT DATA and BLOB operands coalesce(cbd1,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
				dumpRS(s.executeQuery("select coalesce(cbd1,blob2) from tC"));
				System.out.println("TestC13a - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("42815"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestC13b - CHAR FOR BIT DATA and BLOB operands value(cbd1,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
				dumpRS(s.executeQuery("select value(cbd1,blob2) from tC"));
				System.out.println("TestC13b - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("42815"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestC14a - BLOB and VARCHAR FOR BIT DATA operands coalesce(blob1,vcbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
				dumpRS(s.executeQuery("select coalesce(blob1,vcbd1) from tC"));
				System.out.println("TestC14a - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("42815"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestC14b - BLOB and VARCHAR FOR BIT DATA operands value(blob1,vcbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
				dumpRS(s.executeQuery("select value(blob1,vcbd1) from tC"));
				System.out.println("TestC14b - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("42815"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestC15a - VARCHAR FOR BIT DATA and BLOB operands coalesce(vcbd2,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
				dumpRS(s.executeQuery("select coalesce(vcbd2,blob2) from tC"));
				System.out.println("TestC15a - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("42815"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestC15b - VARCHAR FOR BIT DATA and BLOB operands value(vcbd2,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
				dumpRS(s.executeQuery("select value(vcbd2,blob2) from tC"));
				System.out.println("TestC15b - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("42815"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestC16a - BLOB and LONG VARCHAR FOR BIT DATA operands coalesce(blob1,lvcbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
				dumpRS(s.executeQuery("select coalesce(blob1,lvcbd1) from tC"));
				System.out.println("TestC16a - should