0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.coalesceTests
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.lang;
0023:
0024: import java.io.*;
0025: import java.sql.*;
0026:
0027: import org.apache.derby.tools.ij;
0028:
0029: /**
0030: * Coalesce/Value tests for various datatypes
0031: * coalesce/value function takes arguments and returns the first argument that is not null.
0032: * The arguments are evaluated in the order in which they are specified, and the result of the
0033: * function is the first argument that is not null. The result can be null only if all the arguments
0034: * can be null. The selected argument is converted, if necessary, to the attributes of the result.
0035: */
0036: public class coalesceTests {
0037:
0038: private static String VALID_DATE_STRING = "'2000-01-01'";
0039: private static String VALID_TIME_STRING = "'15:30:20'";
0040: private static String VALID_TIMESTAMP_STRING = "'2000-01-01 15:30:20'";
0041: private static String NULL_VALUE = "NULL";
0042:
0043: private static String[] SQLTypes = { "SMALLINT", "INTEGER",
0044: "BIGINT", "DECIMAL(10,5)", "REAL", "DOUBLE", "CHAR(60)",
0045: "VARCHAR(60)", "LONG VARCHAR", "CHAR(60) FOR BIT DATA",
0046: "VARCHAR(60) FOR BIT DATA", "LONG VARCHAR FOR BIT DATA",
0047: "CLOB(1k)", "DATE", "TIME", "TIMESTAMP", "BLOB(1k)",
0048:
0049: };
0050:
0051: private static String[] ColumnNames = { "SMALLINTCOL",
0052: "INTEGERCOL", "BIGINTCOL", "DECIMALCOL", "REALCOL",
0053: "DOUBLECOL", "CHARCOL", "VARCHARCOL", "LONGVARCHARCOL",
0054: "CHARFORBITCOL", "VARCHARFORBITCOL", "LVARCHARFORBITCOL",
0055: "CLOBCOL", "DATECOL", "TIMECOL", "TIMESTAMPCOL", "BLOBCOL",
0056:
0057: };
0058:
0059: private static String[][] SQLData = {
0060: { NULL_VALUE, "0", "1", "2" }, // SMALLINT
0061: { NULL_VALUE, "11", "111", NULL_VALUE }, // INTEGER
0062: { NULL_VALUE, "22", "222", "3333" }, // BIGINT
0063: { NULL_VALUE, "3.3", "3.33", NULL_VALUE }, // DECIMAL(10,5)
0064: { NULL_VALUE, "4.4", "4.44", "4.444" }, // REAL,
0065: { NULL_VALUE, "5.5", "5.55", NULL_VALUE }, // DOUBLE
0066: { NULL_VALUE, "'1992-01-06'", "'1992-01-16'", NULL_VALUE }, // CHAR(60)
0067: { NULL_VALUE, "'1992-01-07'", "'1992-01-17'",
0068: VALID_TIME_STRING }, //VARCHAR(60)",
0069: { NULL_VALUE, "'1992-01-08'", "'1992-01-18'",
0070: VALID_TIMESTAMP_STRING }, // LONG VARCHAR
0071: { NULL_VALUE, "X'10aa'", NULL_VALUE, "X'10aaaa'" }, // CHAR(60) FOR BIT DATA
0072: { NULL_VALUE, "X'10bb'", NULL_VALUE, "X'10bbbb'" }, // VARCHAR(60) FOR BIT DATA
0073: { NULL_VALUE, "X'10cc'", NULL_VALUE, "X'10cccc'" }, //LONG VARCHAR FOR BIT DATA
0074: { NULL_VALUE, "'13'", "'14'", NULL_VALUE }, //CLOB(1k)
0075: { NULL_VALUE, VALID_DATE_STRING, VALID_DATE_STRING,
0076: NULL_VALUE }, // DATE
0077: { NULL_VALUE, VALID_TIME_STRING, VALID_TIME_STRING,
0078: NULL_VALUE }, // TIME
0079: { NULL_VALUE, VALID_TIMESTAMP_STRING,
0080: VALID_TIMESTAMP_STRING, NULL_VALUE }, // TIMESTAMP
0081: { NULL_VALUE, NULL_VALUE, NULL_VALUE, NULL_VALUE } // BLOB
0082: };
0083:
0084: /**
0085: SQL Reference Guide for DB2 has section titled "Rules for result data types" at the following url
0086: http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0008480.htm
0087:
0088: I have constructed following table based on various tables and information under "Rules for result data types"
0089: This table has FOR BIT DATA TYPES broken out into separate columns for clarity and testing
0090: **/
0091:
0092: public static final String[][] resultDataTypeRulesTable = {
0093:
0094: // Types. S I B D R D C V L C V L C D T T B
0095: // M N I E E O H A O H A O L A I I L
0096: // A T G C A U A R N A R N O T M M O
0097: // L E I I L B R C G R C G B E E E B
0098: // L G N M L H V . H V S
0099: // I E T A E A A B A A T
0100: // N R L R R I R R A
0101: // T C T . . M
0102: // H B B P
0103: // A I I
0104: // R T T
0105: /* 0 SMALLINT */{ "SMALLINT", "INTEGER", "BIGINT",
0106: "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR",
0107: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0108: "ERROR", "ERROR", "ERROR", "ERROR" },
0109: /* 1 INTEGER */{ "INTEGER", "INTEGER", "BIGINT",
0110: "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR",
0111: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0112: "ERROR", "ERROR", "ERROR", "ERROR" },
0113: /* 2 BIGINT */{ "BIGINT", "BIGINT", "BIGINT", "DECIMAL",
0114: "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR",
0115: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0116: "ERROR", "ERROR", "ERROR" },
0117: /* 3 DECIMAL */{ "DECIMAL", "DECIMAL", "DECIMAL",
0118: "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR",
0119: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0120: "ERROR", "ERROR", "ERROR", "ERROR" },
0121: /* 4 REAL */{ "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE",
0122: "REAL", "DOUBLE", "ERROR", "ERROR", "ERROR",
0123: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0124: "ERROR", "ERROR", "ERROR" },
0125: /* 5 DOUBLE */{ "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE",
0126: "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR",
0127: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0128: "ERROR", "ERROR", "ERROR" },
0129: /* 6 CHAR */{ "ERROR", "ERROR", "ERROR", "ERROR",
0130: "ERROR", "ERROR", "CHAR", "VARCHAR",
0131: "LONG VARCHAR", "ERROR", "ERROR", "ERROR", "CLOB",
0132: "DATE", "TIME", "TIMESTAMP", "ERROR" },
0133: /* 7 VARCHAR */{ "ERROR", "ERROR", "ERROR", "ERROR",
0134: "ERROR", "ERROR", "VARCHAR", "VARCHAR",
0135: "LONG VARCHAR", "ERROR", "ERROR", "ERROR", "CLOB",
0136: "DATE", "TIME", "TIMESTAMP", "ERROR" },
0137: /* 8 LONGVARCHAR */{ "ERROR", "ERROR", "ERROR", "ERROR",
0138: "ERROR", "ERROR", "LONG VARCHAR", "LONG VARCHAR",
0139: "LONG VARCHAR", "ERROR", "ERROR", "ERROR", "CLOB",
0140: "ERROR", "ERROR", "ERROR", "ERROR" },
0141: /* 9 CHAR FOR BIT */{ "ERROR", "ERROR", "ERROR", "ERROR",
0142: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0143: "CHAR () FOR BIT DATA", "VARCHAR () FOR BIT DATA",
0144: "LONG VARCHAR FOR BIT DATA", "ERROR", "ERROR",
0145: "ERROR", "ERROR", "ERROR" },
0146: /* 10 VARCH. BIT */{ "ERROR", "ERROR", "ERROR", "ERROR",
0147: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0148: "VARCHAR () FOR BIT DATA",
0149: "VARCHAR () FOR BIT DATA",
0150: "LONG VARCHAR FOR BIT DATA", "ERROR", "ERROR",
0151: "ERROR", "ERROR", "ERROR" },
0152: /* 11 LONGVAR. BIT */{ "ERROR", "ERROR", "ERROR", "ERROR",
0153: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0154: "LONG VARCHAR FOR BIT DATA",
0155: "LONG VARCHAR FOR BIT DATA",
0156: "LONG VARCHAR FOR BIT DATA", "ERROR", "ERROR",
0157: "ERROR", "ERROR", "ERROR" },
0158: /* 12 CLOB */{ "ERROR", "ERROR", "ERROR", "ERROR",
0159: "ERROR", "ERROR", "CLOB", "CLOB", "CLOB", "ERROR",
0160: "ERROR", "ERROR", "CLOB", "ERROR", "ERROR",
0161: "ERROR", "ERROR" },
0162: /* 13 DATE */{ "ERROR", "ERROR", "ERROR", "ERROR",
0163: "ERROR", "ERROR", "DATE", "DATE", "ERROR", "ERROR",
0164: "ERROR", "ERROR", "ERROR", "DATE", "ERROR",
0165: "ERROR", "ERROR" },
0166: /* 14 TIME */{ "ERROR", "ERROR", "ERROR", "ERROR",
0167: "ERROR", "ERROR", "TIME", "TIME", "ERROR", "ERROR",
0168: "ERROR", "ERROR", "ERROR", "ERROR", "TIME",
0169: "ERROR", "ERROR" },
0170: /* 15 TIMESTAMP */{ "ERROR", "ERROR", "ERROR", "ERROR",
0171: "ERROR", "ERROR", "TIMESTAMP", "TIMESTAMP",
0172: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0173: "ERROR", "ERROR", "TIMESTAMP", "ERROR" },
0174: /* 16 BLOB */{ "ERROR", "ERROR", "ERROR", "ERROR",
0175: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0176: "ERROR", "ERROR", "ERROR", "ERROR", "ERROR",
0177: "ERROR", "ERROR", "BLOB" },
0178:
0179: };
0180:
0181: public static void main(String[] argv) throws Throwable {
0182: ij.getPropertyArg(argv);
0183: Connection conn = ij.startJBMS();
0184:
0185: testCoalesceSyntax(conn);
0186:
0187: tablesForTestingAllDatatypesCombinations(conn);
0188: testCompatibleDatatypesCombinations(conn);
0189: testAllDatatypesCombinations(conn);
0190:
0191: testDateCoalesce(conn);
0192: testTimeCoalesce(conn);
0193: testTimeStampCoalesce(conn);
0194: testNumericCoalesce(conn);
0195: testMiscellaneousCoalesce(conn);
0196: testCharCoalesce(conn);
0197: testCharForBitDataCoalesce(conn);
0198: }
0199:
0200: public static void testCoalesceSyntax(Connection conn)
0201: throws Throwable {
0202: try {
0203: System.out
0204: .println("TestA - some syntax testing for Coalesce/Value function");
0205:
0206: PreparedStatement ps;
0207: Statement s = conn.createStatement();
0208: try {
0209: s.executeUpdate("drop table tA");
0210: } catch (Exception ex) {
0211: }
0212: s.executeUpdate("create table tA (c1 int, c2 char(254))");
0213: s.executeUpdate("insert into tA (c1) values(1)");
0214:
0215: System.out
0216: .println("TestAla - select coalesce from tA will give error because no arguments were supplied to the function");
0217: try {
0218: s.executeQuery("select coalesce from tA");
0219: System.out
0220: .println("FAIL - should have gotten error for incorrect syntax");
0221: } catch (SQLException e) {
0222: if (e.getSQLState().equals("42X04"))
0223: System.out.println("expected exception "
0224: + e.getMessage());
0225: else
0226: dumpSQLExceptions(e);
0227: }
0228:
0229: System.out
0230: .println("TestAlb - select value from tA will give error because no arguments were supplied to the function");
0231: try {
0232: s.executeQuery("select value from tA");
0233: System.out
0234: .println("FAIL - should have gotten error for incorrect syntax");
0235: } catch (SQLException e) {
0236: if (e.getSQLState().equals("42X04"))
0237: System.out.println("expected exception "
0238: + e.getMessage());
0239: else
0240: dumpSQLExceptions(e);
0241: }
0242:
0243: System.out
0244: .println("TestA2a - select coalesce from tA will give error because no arguments were supplied inside the parentheses");
0245: try {
0246: s.executeQuery("select coalesce() from tA");
0247: System.out
0248: .println("FAIL - should have gotten error for incorrect syntax");
0249: } catch (SQLException e) {
0250: if (e.getSQLState().equals("42X01"))
0251: System.out.println("expected exception "
0252: + e.getMessage());
0253: else
0254: dumpSQLExceptions(e);
0255: }
0256:
0257: System.out
0258: .println("TestA2b - select value from tA will give error because no arguments were supplied inside the parentheses");
0259: try {
0260: s.executeQuery("select value() from tA");
0261: System.out
0262: .println("FAIL - should have gotten error for incorrect syntax");
0263: } catch (SQLException e) {
0264: if (e.getSQLState().equals("42X01"))
0265: System.out.println("expected exception "
0266: + e.getMessage());
0267: else
0268: dumpSQLExceptions(e);
0269: }
0270:
0271: System.out
0272: .println("TestA3a - select coalesce from tA with only one argument will give error");
0273: try {
0274: s.executeQuery("select coalesce(c1) from tA");
0275: System.out
0276: .println("FAIL - should have gotten error for incorrect syntax");
0277: } catch (SQLException e) {
0278: if (e.getSQLState().equals("42605"))
0279: System.out.println("expected exception "
0280: + e.getMessage());
0281: else
0282: dumpSQLExceptions(e);
0283: }
0284:
0285: System.out
0286: .println("TestA3b - select value from tA with only one argument will give error");
0287: try {
0288: s.executeQuery("select value(c1) from tA");
0289: System.out
0290: .println("FAIL - should have gotten error for incorrect syntax");
0291: } catch (SQLException e) {
0292: if (e.getSQLState().equals("42605"))
0293: System.out.println("expected exception "
0294: + e.getMessage());
0295: else
0296: dumpSQLExceptions(e);
0297: }
0298:
0299: System.out
0300: .println("TestA4a - select coalesce from tA with incorrect column name will give error");
0301: try {
0302: s.executeQuery("select coalesce(c111) from tA");
0303: System.out
0304: .println("FAIL - should have gotten error for incorrect syntax");
0305: } catch (SQLException e) {
0306: if (e.getSQLState().equals("42X04"))
0307: System.out.println("expected exception "
0308: + e.getMessage());
0309: else
0310: dumpSQLExceptions(e);
0311: }
0312:
0313: System.out
0314: .println("TestA4b - select value from tA with incorrect column name will give error");
0315: try {
0316: s.executeQuery("select value(c111) from tA");
0317: System.out
0318: .println("FAIL - should have gotten error for incorrect syntax");
0319: } catch (SQLException e) {
0320: if (e.getSQLState().equals("42X04"))
0321: System.out.println("expected exception "
0322: + e.getMessage());
0323: else
0324: dumpSQLExceptions(e);
0325: }
0326:
0327: System.out
0328: .println("TestA5a - create table with table name as coalesce and column name as coalesce will pass because coalesce is not a reserved-word");
0329: s
0330: .executeUpdate("create table coalesce (coalesce int, c12 int)");
0331: s
0332: .executeUpdate("insert into coalesce(coalesce) values(null)");
0333: s.executeUpdate("insert into coalesce values(null,1)");
0334: dumpRS(s
0335: .executeQuery("select coalesce(coalesce,c12) from coalesce"));
0336: s.executeUpdate("drop table coalesce");
0337:
0338: System.out
0339: .println("TestA5b - create table with table name as value and column name as value will pass because value is not a reserved-word");
0340: s.executeUpdate("create table value (value int, c12 int)");
0341: s.executeUpdate("insert into value(value) values(null)");
0342: s.executeUpdate("insert into value values(null,1)");
0343: dumpRS(s
0344: .executeQuery("select coalesce(value,c12) from value"));
0345: s.executeUpdate("drop table value");
0346:
0347: System.out
0348: .println("TestA6a - All arguments to coalesce function passed as parameters is an error");
0349: try {
0350: ps = conn
0351: .prepareStatement("select coalesce(?,?) from tA");
0352: System.out
0353: .println("FAIL - should have gotten error for using parameters for all the arguments");
0354: } catch (SQLException e) {
0355: if (e.getSQLState().equals("42610"))
0356: System.out.println("expected exception "
0357: + e.getMessage());
0358: else
0359: dumpSQLExceptions(e);
0360: }
0361:
0362: System.out
0363: .println("TestA6b - All arguments to value function passed as parameters is an error");
0364: try {
0365: ps = conn.prepareStatement("select value(?,?) from tA");
0366: System.out
0367: .println("FAIL - should have gotten error for using parameters for all the arguments");
0368: } catch (SQLException e) {
0369: if (e.getSQLState().equals("42610"))
0370: System.out.println("expected exception "
0371: + e.getMessage());
0372: else
0373: dumpSQLExceptions(e);
0374: }
0375:
0376: s.executeUpdate("drop table tA");
0377: } catch (SQLException sqle) {
0378: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
0379: System.out, sqle);
0380: sqle.printStackTrace(System.out);
0381: }
0382: }
0383:
0384: public static void tablesForTestingAllDatatypesCombinations(
0385: Connection conn) throws Throwable {
0386: System.out
0387: .println("Set up by creating table for testing all datatypes combinations");
0388:
0389: Statement s = conn.createStatement();
0390:
0391: try {
0392: s.executeUpdate("DROP TABLE AllDataTypesTable");
0393: } catch (SQLException se) {
0394: }
0395:
0396: StringBuffer createSQL = new StringBuffer(
0397: "create table AllDataTypesTable (");
0398: for (int type = 0; type < SQLTypes.length - 1; type++) {
0399: createSQL.append(ColumnNames[type] + " " + SQLTypes[type]
0400: + ",");
0401: }
0402: createSQL.append(ColumnNames[SQLTypes.length - 1] + " "
0403: + SQLTypes[SQLTypes.length - 1] + ")");
0404: System.out.println(createSQL);
0405: s.executeUpdate(createSQL.toString());
0406:
0407: for (int row = 0; row < SQLData[0].length; row++) {
0408: createSQL = new StringBuffer(
0409: "insert into AllDataTypesTable values(");
0410: for (int type = 0; type < SQLTypes.length - 1; type++) {
0411: createSQL.append(SQLData[type][row] + ",");
0412: }
0413: createSQL.append(SQLData[SQLTypes.length - 1][row] + ")");
0414: System.out.println(createSQL);
0415: s.executeUpdate(createSQL.toString());
0416: }
0417:
0418: s.close();
0419: conn.commit();
0420: }
0421:
0422: public static void testAllDatatypesCombinations(Connection conn)
0423: throws Throwable {
0424: System.out
0425: .println("Start testing all datatypes combinations in COALESCE/VALUE function");
0426:
0427: Statement s = conn.createStatement();
0428:
0429: // Try COALESCE with 2 datatype combinations at a time
0430: for (int firstColumnType = 0; firstColumnType < SQLTypes.length; firstColumnType++) {
0431: for (int secondColumnType = 0; secondColumnType < SQLTypes.length; secondColumnType++) {
0432: try {
0433: String coalesceString = "SELECT COALESCE("
0434: + ColumnNames[firstColumnType] + ","
0435: + ColumnNames[secondColumnType]
0436: + ") from AllDataTypesTable";
0437: System.out.println(coalesceString);
0438: printExpectedResultDataType(firstColumnType,
0439: secondColumnType);
0440: dumpRS(s.executeQuery(coalesceString));
0441: isSupportedCoalesce(firstColumnType,
0442: secondColumnType, true);
0443: } catch (SQLException e) {
0444: if (e.getSQLState().equals("22007"))
0445: System.out
0446: .println("expected exception because char value does not match a time/timestamp format "
0447: + e.getMessage());
0448: else if (!isSupportedCoalesce(firstColumnType,
0449: secondColumnType, false)
0450: && e.getSQLState().equals("42815"))
0451: System.out.println("expected exception "
0452: + e.getMessage());
0453: else
0454: dumpSQLExceptions(e);
0455: }
0456: try {
0457: String valueString = "SELECT VALUE("
0458: + ColumnNames[firstColumnType] + ","
0459: + ColumnNames[secondColumnType]
0460: + ") from AllDataTypesTable";
0461: System.out.println(valueString);
0462: printExpectedResultDataType(firstColumnType,
0463: secondColumnType);
0464: dumpRS(s.executeQuery(valueString));
0465: isSupportedCoalesce(firstColumnType,
0466: secondColumnType, true);
0467: } catch (SQLException e) {
0468: if (e.getSQLState().equals("22007"))
0469: System.out
0470: .println("expected exception because char value does not match a time/timestamp format "
0471: + e.getMessage());
0472: else if (!isSupportedCoalesce(firstColumnType,
0473: secondColumnType, false)
0474: && e.getSQLState().equals("42815"))
0475: System.out.println("expected exception "
0476: + e.getMessage());
0477: else
0478: dumpSQLExceptions(e);
0479: }
0480: }
0481: }
0482: }
0483:
0484: public static void testCompatibleDatatypesCombinations(
0485: Connection conn) throws Throwable {
0486: System.out
0487: .println("Start testing all compatible datatypes combinations in COALESCE/VALUE function");
0488:
0489: Statement s = conn.createStatement();
0490:
0491: for (int firstColumnType = 0; firstColumnType < SQLTypes.length; firstColumnType++) {
0492: StringBuffer coalesceString = new StringBuffer(
0493: "SELECT COALESCE(" + ColumnNames[firstColumnType]);
0494: for (int secondColumnType = 0; secondColumnType < SQLTypes.length; secondColumnType++) {
0495: try {
0496: if (resultDataTypeRulesTable[firstColumnType][secondColumnType]
0497: .equals("ERROR"))
0498: continue; //the datatypes are incompatible, don't try them in COALESCE/VALUE
0499: coalesceString.append(","
0500: + ColumnNames[secondColumnType]);
0501: System.out.println(coalesceString
0502: + ") from AllDataTypesTable");
0503: dumpRS(s.executeQuery(coalesceString
0504: + ") from AllDataTypesTable"));
0505: } catch (SQLException e) {
0506: if (e.getSQLState().equals("22007"))
0507: System.out
0508: .println("expected exception because char value does not match a time/timestamp format "
0509: + e.getMessage());
0510: else if (isClobWithCharAndDateTypeArguments(coalesceString
0511: .toString())
0512: && e.getSQLState().equals("42815"))
0513: System.out
0514: .println("expected exception because mixing CLOB and DATA/TIME/TIMESTAMP arugments "
0515: + e.getMessage());
0516: else if (!isSupportedCoalesce(firstColumnType,
0517: secondColumnType, false)
0518: && e.getSQLState().equals("42815"))
0519: System.out.println("expected exception "
0520: + e.getMessage());
0521: else
0522: dumpSQLExceptions(e);
0523: }
0524: }
0525: }
0526: }
0527:
0528: private static void printExpectedResultDataType(int oneType,
0529: int anotherType) {
0530: String coalesceDescription;
0531: if (resultDataTypeRulesTable[oneType][anotherType]
0532: .equals("ERROR")) {
0533: coalesceDescription = " Operands " + SQLTypes[oneType]
0534: + " , " + SQLTypes[anotherType]
0535: + " are incompatible for Coalesce/Value function";
0536: } else {
0537: coalesceDescription = " Coalesc/Value with operands "
0538: + SQLTypes[oneType] + " , " + SQLTypes[anotherType]
0539: + " will have result data type of "
0540: + resultDataTypeRulesTable[oneType][anotherType];
0541: }
0542: System.out.println(coalesceDescription);
0543: }
0544:
0545: public static boolean isClobWithCharAndDateTypeArguments(
0546: String coalesceString) throws Throwable {
0547: if (coalesceString.indexOf("CLOB") != -1) {
0548: if (coalesceString.indexOf("CHAR") != -1
0549: && (coalesceString.indexOf("DATE") != -1 || coalesceString
0550: .indexOf("TIME") != -1))
0551: return true;
0552: }
0553: return false;
0554: }
0555:
0556: private static boolean isSupportedCoalesce(int oneType,
0557: int anotherType, boolean throwError) {
0558: String coalesceDescription = " Coalesc/Value with operands "
0559: + SQLTypes[oneType] + " , " + SQLTypes[anotherType];
0560:
0561: if (throwError
0562: && resultDataTypeRulesTable[oneType][anotherType]
0563: .equals("ERROR"))
0564: System.out.println("FAIL:" + coalesceDescription
0565: + " should not be supported");
0566:
0567: return (!(resultDataTypeRulesTable[oneType][anotherType]
0568: .equals("ERROR")));
0569: }
0570:
0571: public static void testMiscellaneousCoalesce(Connection conn)
0572: throws Throwable {
0573: try {
0574: Statement s = conn.createStatement();
0575: PreparedStatement ps;
0576: try {
0577: s.executeUpdate("drop table tD");
0578: } catch (Exception ex) {
0579: }
0580: s.executeUpdate("create table tD (c1 int, c2 char(254))");
0581: s
0582: .executeUpdate("insert into tD (c1,c2) values(1,'abcdefgh')");
0583: s.executeUpdate("insert into tD (c1) values(2)");
0584:
0585: System.out
0586: .println("TestD - some miscellaneous testing for Coalesce/Value function");
0587:
0588: System.out
0589: .println("TestD1a - test coalesce function in values clause");
0590: dumpRS(s
0591: .executeQuery("values coalesce(cast('asdfghj' as char(30)),cast('asdf' as char(50)))"));
0592:
0593: System.out
0594: .println("TestD1b - test value function in values clause");
0595: dumpRS(s
0596: .executeQuery("values value(cast('asdfghj' as char(30)),cast('asdf' as char(50)))"));
0597:
0598: System.out
0599: .println("TestD2a - First argument to coalesce function passed as parameter with non-null value");
0600: ps = conn.prepareStatement("select coalesce(?,c2) from tD");
0601: ps.setString(1, "first argument to coalesce");
0602: dumpRS(ps.executeQuery());
0603:
0604: System.out
0605: .println("TestD2b - First argument to value function passed as parameter with non-null value");
0606: ps = conn.prepareStatement("select value(?,c2) from tD");
0607: ps.setString(1, "first argument to value");
0608: dumpRS(ps.executeQuery());
0609:
0610: System.out
0611: .println("TestD3a - First argument to coalesce function passed as parameter with null value");
0612: ps = conn.prepareStatement("select coalesce(?,c2) from tD");
0613: ps.setNull(1, Types.CHAR);
0614: dumpRS(ps.executeQuery());
0615:
0616: System.out
0617: .println("TestD3b - First argument to value function passed as parameter with null value");
0618: ps = conn.prepareStatement("select value(?,c2) from tD");
0619: ps.setNull(1, Types.BIGINT);
0620: dumpRS(ps.executeQuery());
0621:
0622: System.out
0623: .println("TestD4a - Pass incompatible value for parameter to coalesce function");
0624: ps = conn.prepareStatement("select coalesce(c1,?) from tD");
0625: try {
0626: ps.setString(1, "abc");
0627: dumpRS(ps.executeQuery());
0628: System.out
0629: .println("FAIL - should have gotten error because result type is int and we are trying to pass a parameter of type char");
0630: } catch (SQLException e) {
0631: if (e.getSQLState().equals("22018"))
0632: System.out.println("expected exception "
0633: + e.getMessage());
0634: else
0635: dumpSQLExceptions(e);
0636: }
0637:
0638: s.executeUpdate("drop table tD");
0639: } catch (SQLException sqle) {
0640: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
0641: System.out, sqle);
0642: sqle.printStackTrace(System.out);
0643: }
0644: }
0645:
0646: public static void testDateCoalesce(Connection conn)
0647: throws Throwable {
0648: try {
0649: Statement s = conn.createStatement();
0650: PreparedStatement ps;
0651: try {
0652: s.executeUpdate("drop table tF");
0653: } catch (Exception ex) {
0654: }
0655: s
0656: .executeUpdate("create table tF (dateCol date, charCol char(10), varcharCol varchar(50))");
0657: s.executeUpdate("insert into tF values(null, null, null)");
0658: s
0659: .executeUpdate("insert into tF values(date('1992-01-02'), '1992-01-03', '1992-01-04')");
0660:
0661: System.out.println("TestF - focus on date datatypes");
0662: System.out.println("TestF1a - coalesce(dateCol,dateCol)");
0663: dumpRSwithScale(s
0664: .executeQuery("select coalesce(dateCol,dateCol) from tF"));
0665:
0666: System.out.println("TestF1b - value(dateCol,dateCol)");
0667: dumpRSwithScale(s
0668: .executeQuery("select value(dateCol,dateCol) from tF"));
0669:
0670: System.out.println("TestF2a - coalesce(dateCol,charCol)");
0671: dumpRSwithScale(s
0672: .executeQuery("select coalesce(dateCol,charCol) from tF"));
0673:
0674: System.out.println("TestF2b - value(dateCol,charCol)");
0675: dumpRSwithScale(s
0676: .executeQuery("select value(dateCol,charCol) from tF"));
0677:
0678: System.out.println("TestF3a - coalesce(charCol,dateCol)");
0679: dumpRSwithScale(s
0680: .executeQuery("select coalesce(charCol,dateCol) from tF"));
0681:
0682: System.out.println("TestF3b - value(charCol,dateCol)");
0683: dumpRSwithScale(s
0684: .executeQuery("select value(charCol,dateCol) from tF"));
0685:
0686: System.out
0687: .println("TestF4a - coalesce(dateCol,varcharCol)");
0688: dumpRSwithScale(s
0689: .executeQuery("select coalesce(dateCol,charCol) from tF"));
0690:
0691: System.out.println("TestF4b - value(dateCol,varcharCol)");
0692: dumpRSwithScale(s
0693: .executeQuery("select value(dateCol,charCol) from tF"));
0694:
0695: System.out
0696: .println("TestF5a - coalesce(varcharCol,dateCol)");
0697: dumpRSwithScale(s
0698: .executeQuery("select coalesce(charCol,dateCol) from tF"));
0699:
0700: System.out.println("TestF5b - value(varcharCol,dateCol)");
0701: dumpRSwithScale(s
0702: .executeQuery("select value(charCol,dateCol) from tF"));
0703:
0704: System.out
0705: .println("TestF - Try invalid string representation of date into chars and varchars and then use them in coalesce function with date datatype");
0706: s
0707: .executeUpdate("insert into tF values(date('1992-01-01'), 'I am char', 'I am varchar')");
0708:
0709: try {
0710: System.out
0711: .println("TestF6a - coalesce(charCol,dateCol) will fail because one row has invalid string representation of date in the char column");
0712: dumpRSwithScale(s
0713: .executeQuery("select coalesce(charCol,dateCol) from tF"));
0714: System.out.println("TestF6a - should have failed");
0715: } catch (SQLException e) {
0716: if (e.getSQLState().equals("22007"))
0717: System.out.println("expected exception "
0718: + e.getMessage());
0719: else
0720: dumpSQLExceptions(e);
0721: }
0722:
0723: try {
0724: System.out
0725: .println("TestF6b - value(charCol,dateCol) will fail because one row has invalid string representation of date in the char column");
0726: dumpRSwithScale(s
0727: .executeQuery("select value(charCol,dateCol) from tF"));
0728: System.out.println("TestF6b - should have failed");
0729: } catch (SQLException e) {
0730: if (e.getSQLState().equals("22007"))
0731: System.out.println("expected exception "
0732: + e.getMessage());
0733: else
0734: dumpSQLExceptions(e);
0735: }
0736:
0737: try {
0738: System.out
0739: .println("TestF7a - coalesce(varcharCol,dateCol) will fail because one row has invalid string representation of date in the varchar column");
0740: dumpRSwithScale(s
0741: .executeQuery("select coalesce(charCol,dateCol) from tF"));
0742: System.out.println("TestF7a - should have failed");
0743: } catch (SQLException e) {
0744: if (e.getSQLState().equals("22007"))
0745: System.out.println("expected exception "
0746: + e.getMessage());
0747: else
0748: dumpSQLExceptions(e);
0749: }
0750:
0751: try {
0752: System.out
0753: .println("TestF7b - value(varcharCol,dateCol) will fail because one row has invalid string representation of date in the varchar column");
0754: dumpRSwithScale(s
0755: .executeQuery("select value(charCol,dateCol) from tF"));
0756: System.out.println("TestF7b - should have failed");
0757: } catch (SQLException e) {
0758: if (e.getSQLState().equals("22007"))
0759: System.out.println("expected exception "
0760: + e.getMessage());
0761: else
0762: dumpSQLExceptions(e);
0763: }
0764:
0765: s.executeUpdate("drop table tF");
0766: } catch (SQLException sqle) {
0767: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
0768: System.out, sqle);
0769: sqle.printStackTrace(System.out);
0770: }
0771: }
0772:
0773: public static void testTimeStampCoalesce(Connection conn)
0774: throws Throwable {
0775: try {
0776: Statement s = conn.createStatement();
0777: PreparedStatement ps;
0778: try {
0779: s.executeUpdate("drop table tH");
0780: } catch (Exception ex) {
0781: }
0782: s
0783: .executeUpdate("create table tH (timestampCol timestamp, charCol char(19), varcharCol varchar(50))");
0784: s.executeUpdate("insert into tH values(null, null, null)");
0785: s
0786: .executeUpdate("insert into tH values(timestamp('1992-01-01 12:30:30'), '1992-01-01 12:30:31', '1992-01-01 12:30:32')");
0787:
0788: System.out.println("TestH - focus on timestamp datatypes");
0789: System.out
0790: .println("TestH1a - coalesce(timestampCol,timestampCol)");
0791: dumpRSwithScale(s
0792: .executeQuery("select coalesce(timestampCol,timestampCol) from tH"));
0793:
0794: System.out
0795: .println("TestH1b - value(timestampCol,timestampCol)");
0796: dumpRSwithScale(s
0797: .executeQuery("select value(timestampCol,timestampCol) from tH"));
0798:
0799: System.out
0800: .println("TestH2a - coalesce(timestampCol,charCol)");
0801: dumpRSwithScale(s
0802: .executeQuery("select coalesce(timestampCol,charCol) from tH"));
0803:
0804: System.out.println("TestH2b - value(timestampCol,charCol)");
0805: dumpRSwithScale(s
0806: .executeQuery("select value(timestampCol,charCol) from tH"));
0807:
0808: System.out
0809: .println("TestH3a - coalesce(charCol,timestampCol)");
0810: dumpRSwithScale(s
0811: .executeQuery("select coalesce(charCol,timestampCol) from tH"));
0812:
0813: System.out.println("TestH3b - value(charCol,timestampCol)");
0814: dumpRSwithScale(s
0815: .executeQuery("select value(charCol,timestampCol) from tH"));
0816:
0817: System.out
0818: .println("TestH4a - coalesce(timestampCol,varcharCol)");
0819: dumpRSwithScale(s
0820: .executeQuery("select coalesce(timestampCol,charCol) from tH"));
0821:
0822: System.out
0823: .println("TestH4b - value(timestampCol,varcharCol)");
0824: dumpRSwithScale(s
0825: .executeQuery("select value(timestampCol,charCol) from tH"));
0826:
0827: System.out
0828: .println("TestH5a - coalesce(varcharCol,timestampCol)");
0829: dumpRSwithScale(s
0830: .executeQuery("select coalesce(charCol,timestampCol) from tH"));
0831:
0832: System.out
0833: .println("TestH5b - value(varcharCol,timestampCol)");
0834: dumpRSwithScale(s
0835: .executeQuery("select value(charCol,timestampCol) from tH"));
0836:
0837: System.out
0838: .println("TestH - Try invalid string representation of timestamp into chars and varchars and then use them in coalesce function with timestamp datatype");
0839: s
0840: .executeUpdate("insert into tH values(timestamp('1992-01-01 12:30:33'), 'I am char', 'I am varchar')");
0841:
0842: try {
0843: System.out
0844: .println("TestH6a - coalesce(charCol,timestampCol) will fail because one row has invalid string representation of timestamp in the char column");
0845: dumpRSwithScale(s
0846: .executeQuery("select coalesce(charCol,timestampCol) from tH"));
0847: System.out.println("TestH6a - should have failed");
0848: } catch (SQLException e) {
0849: if (e.getSQLState().equals("22007"))
0850: System.out.println("expected exception "
0851: + e.getMessage());
0852: else
0853: dumpSQLExceptions(e);
0854: }
0855:
0856: try {
0857: System.out
0858: .println("TestH6b - value(charCol,timestampCol) will fail because one row has invalid string representation of timestamp in the char column");
0859: dumpRSwithScale(s
0860: .executeQuery("select value(charCol,timestampCol) from tH"));
0861: System.out.println("TestH6b - should have failed");
0862: } catch (SQLException e) {
0863: if (e.getSQLState().equals("22007"))
0864: System.out.println("expected exception "
0865: + e.getMessage());
0866: else
0867: dumpSQLExceptions(e);
0868: }
0869:
0870: try {
0871: System.out
0872: .println("TestH7a - coalesce(varcharCol,timestampCol) will fail because one row has invalid string representation of timestamp in the varchar column");
0873: dumpRSwithScale(s
0874: .executeQuery("select coalesce(charCol,timestampCol) from tH"));
0875: System.out.println("TestH7a - should have failed");
0876: } catch (SQLException e) {
0877: if (e.getSQLState().equals("22007"))
0878: System.out.println("expected exception "
0879: + e.getMessage());
0880: else
0881: dumpSQLExceptions(e);
0882: }
0883:
0884: try {
0885: System.out
0886: .println("TestH7b - value(varcharCol,timestampCol) will fail because one row has invalid string representation of timestamp in the varchar column");
0887: dumpRSwithScale(s
0888: .executeQuery("select value(charCol,timestampCol) from tH"));
0889: System.out.println("TestH7b - should have failed");
0890: } catch (SQLException e) {
0891: if (e.getSQLState().equals("22007"))
0892: System.out.println("expected exception "
0893: + e.getMessage());
0894: else
0895: dumpSQLExceptions(e);
0896: }
0897:
0898: s.executeUpdate("drop table tH");
0899: } catch (SQLException sqle) {
0900: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
0901: System.out, sqle);
0902: sqle.printStackTrace(System.out);
0903: }
0904: }
0905:
0906: public static void testTimeCoalesce(Connection conn)
0907: throws Throwable {
0908: try {
0909: Statement s = conn.createStatement();
0910: PreparedStatement ps;
0911: try {
0912: s.executeUpdate("drop table tG");
0913: } catch (Exception ex) {
0914: }
0915: s
0916: .executeUpdate("create table tG (timeCol time, charCol char(10), varcharCol varchar(50))");
0917: s.executeUpdate("insert into tG values(null, null, null)");
0918: s
0919: .executeUpdate("insert into tG values(time('12:30:30'), '12:30:31', '12:30:32')");
0920:
0921: System.out.println("TestG - focus on time datatypes");
0922: System.out.println("TestG1a - coalesce(timeCol,timeCol)");
0923: dumpRSwithScale(s
0924: .executeQuery("select coalesce(timeCol,timeCol) from tG"));
0925:
0926: System.out.println("TestG1b - value(timeCol,timeCol)");
0927: dumpRSwithScale(s
0928: .executeQuery("select value(timeCol,timeCol) from tG"));
0929:
0930: System.out.println("TestG2a - coalesce(timeCol,charCol)");
0931: dumpRSwithScale(s
0932: .executeQuery("select coalesce(timeCol,charCol) from tG"));
0933:
0934: System.out.println("TestG2b - value(timeCol,charCol)");
0935: dumpRSwithScale(s
0936: .executeQuery("select value(timeCol,charCol) from tG"));
0937:
0938: System.out.println("TestG3a - coalesce(charCol,timeCol)");
0939: dumpRSwithScale(s
0940: .executeQuery("select coalesce(charCol,timeCol) from tG"));
0941:
0942: System.out.println("TestG3b - value(charCol,timeCol)");
0943: dumpRSwithScale(s
0944: .executeQuery("select value(charCol,timeCol) from tG"));
0945:
0946: System.out
0947: .println("TestG4a - coalesce(timeCol,varcharCol)");
0948: dumpRSwithScale(s
0949: .executeQuery("select coalesce(timeCol,charCol) from tG"));
0950:
0951: System.out.println("TestG4b - value(timeCol,varcharCol)");
0952: dumpRSwithScale(s
0953: .executeQuery("select value(timeCol,charCol) from tG"));
0954:
0955: System.out
0956: .println("TestG5a - coalesce(varcharCol,timeCol)");
0957: dumpRSwithScale(s
0958: .executeQuery("select coalesce(charCol,timeCol) from tG"));
0959:
0960: System.out.println("TestG5b - value(varcharCol,timeCol)");
0961: dumpRSwithScale(s
0962: .executeQuery("select value(charCol,timeCol) from tG"));
0963:
0964: System.out
0965: .println("TestG - Try invalid string representation of time into chars and varchars and then use them in coalesce function with time datatype");
0966: s
0967: .executeUpdate("insert into tG values(time('12:30:33'), 'I am char', 'I am varchar')");
0968:
0969: try {
0970: System.out
0971: .println("TestG6a - coalesce(charCol,timeCol) will fail because one row has invalid string representation of time in the char column");
0972: dumpRSwithScale(s
0973: .executeQuery("select coalesce(charCol,timeCol) from tG"));
0974: System.out.println("TestG6a - should have failed");
0975: } catch (SQLException e) {
0976: if (e.getSQLState().equals("22007"))
0977: System.out.println("expected exception "
0978: + e.getMessage());
0979: else
0980: dumpSQLExceptions(e);
0981: }
0982:
0983: try {
0984: System.out
0985: .println("TestG6b - value(charCol,timeCol) will fail because one row has invalid string representation of time in the char column");
0986: dumpRSwithScale(s
0987: .executeQuery("select value(charCol,timeCol) from tG"));
0988: System.out.println("TestG6b - should have failed");
0989: } catch (SQLException e) {
0990: if (e.getSQLState().equals("22007"))
0991: System.out.println("expected exception "
0992: + e.getMessage());
0993: else
0994: dumpSQLExceptions(e);
0995: }
0996:
0997: try {
0998: System.out
0999: .println("TestG7a - coalesce(varcharCol,timeCol) will fail because one row has invalid string representation of time in the varchar column");
1000: dumpRSwithScale(s
1001: .executeQuery("select coalesce(charCol,timeCol) from tG"));
1002: System.out.println("TestG7a - should have failed");
1003: } catch (SQLException e) {
1004: if (e.getSQLState().equals("22007"))
1005: System.out.println("expected exception "
1006: + e.getMessage());
1007: else
1008: dumpSQLExceptions(e);
1009: }
1010:
1011: try {
1012: System.out
1013: .println("TestG7b - value(varcharCol,timeCol) will fail because one row has invalid string representation of time in the varchar column");
1014: dumpRSwithScale(s
1015: .executeQuery("select value(charCol,timeCol) from tG"));
1016: System.out.println("TestG7b - should have failed");
1017: } catch (SQLException e) {
1018: if (e.getSQLState().equals("22007"))
1019: System.out.println("expected exception "
1020: + e.getMessage());
1021: else
1022: dumpSQLExceptions(e);
1023: }
1024:
1025: System.out
1026: .println("TestG - Following will work fine with invalid string representation of time because timeCol is not null and hence we don't look at invalid time string in char/varchar columns");
1027: System.out
1028: .println("TestG8a - coalesce(timeCol,charCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at charCol's invalid time string");
1029: dumpRSwithScale(s
1030: .executeQuery("select coalesce(timeCol,charCol) from tG"));
1031:
1032: System.out
1033: .println("TestG8b - value(timeCol,charCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at charCol's invalid time string");
1034: dumpRSwithScale(s
1035: .executeQuery("select coalesce(timeCol,charCol) from tG"));
1036:
1037: System.out
1038: .println("TestG9a - coalesce(timeCol,varcharCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at varcharCol's invalid time string");
1039: dumpRSwithScale(s
1040: .executeQuery("select coalesce(timeCol,varcharCol) from tG"));
1041:
1042: System.out
1043: .println("TestG9b - value(timeCol,varcharCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at varcharCol's invalid time string");
1044: dumpRSwithScale(s
1045: .executeQuery("select coalesce(timeCol,varcharCol) from tG"));
1046:
1047: s.executeUpdate("drop table tG");
1048: } catch (SQLException sqle) {
1049: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
1050: System.out, sqle);
1051: sqle.printStackTrace(System.out);
1052: }
1053: }
1054:
1055: public static void testNumericCoalesce(Connection conn)
1056: throws Throwable {
1057: try {
1058: Statement s = conn.createStatement();
1059: PreparedStatement ps;
1060: try {
1061: s.executeUpdate("drop table tE");
1062: } catch (Exception ex) {
1063: }
1064: s
1065: .executeUpdate("create table tE (smallintCol smallint, intCol integer, bigintCol bigint, decimalCol1 decimal(22,2), decimalCol2 decimal(8,6), decimalCol3 decimal(31,28), realCol real, doubleCol double)");
1066: s
1067: .executeUpdate("insert into tE values(1, 2, 3, 4, 5.5, 6.6, 7.7, 3.4028235E38)");
1068: s
1069: .executeUpdate("insert into tE values(null,null,null,null,null,null,null,null)");
1070:
1071: System.out.println("TestE - focus on smallint datatypes");
1072: System.out
1073: .println("TestE1 - coalesce(smallintCol,smallintCol)");
1074: dumpRSwithScale(s
1075: .executeQuery("select coalesce(smallintCol,smallintCol) from tE"));
1076:
1077: System.out
1078: .println("TestE1a - coalesce(smallintCol,intCol)");
1079: dumpRSwithScale(s
1080: .executeQuery("select coalesce(smallintCol,intCol) from tE"));
1081:
1082: System.out
1083: .println("TestE1b - coalesce(smallintCol,bigintCol)");
1084: dumpRSwithScale(s
1085: .executeQuery("select coalesce(smallintCol,bigintCol) from tE"));
1086:
1087: System.out
1088: .println("TestE1c - coalesce(SMALLINT,DECIMAL) with decimal(w,x) will give result decimal(p,x) where p=x+max(w-x,5) and if that gives p>31, then p is set to 31");
1089: System.out
1090: .println("TestE1c1 - coalesce(smallintCol,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
1091: dumpRSwithScale(s
1092: .executeQuery("select coalesce(smallintCol,decimalCol1) from tE"));
1093:
1094: System.out
1095: .println("TestE1c2 - coalesce(smallintCol,decimalCol2) with decimal(8,6) will give result decimal(11,6)");
1096: dumpRSwithScale(s
1097: .executeQuery("select coalesce(smallintCol,decimalCol2) from tE"));
1098:
1099: System.out
1100: .println("TestE1c3 - coalesce(smallintCol,decimalCol3) with decimal(31,28) will give result decimal(31,28) rather than giving error for precision > 31");
1101: dumpRSwithScale(s
1102: .executeQuery("select coalesce(smallintCol,decimalCol3) from tE"));
1103:
1104: System.out
1105: .println("TestE1d - coalesce(smallintCol,realCol)");
1106: dumpRSwithScale(s
1107: .executeQuery("select coalesce(smallintCol,realCol) from tE"));
1108:
1109: System.out
1110: .println("TestE1e - coalesce(smallintCol,doubleCol)");
1111: dumpRSwithScale(s
1112: .executeQuery("select coalesce(smallintCol,doubleCol) from tE"));
1113:
1114: System.out.println("TestE - focus on int datatypes");
1115: System.out.println("TestE1 - coalesce(intCol,intCol)");
1116: dumpRSwithScale(s
1117: .executeQuery("select coalesce(intCol,intCol) from tE"));
1118:
1119: System.out
1120: .println("TestE1f - coalesce(intCol,smallintCol)");
1121: dumpRSwithScale(s
1122: .executeQuery("select coalesce(intCol,smallintCol) from tE"));
1123:
1124: System.out.println("TestE1g - coalesce(intCol,bigintCol)");
1125: dumpRSwithScale(s
1126: .executeQuery("select coalesce(intCol,bigintCol) from tE"));
1127:
1128: System.out
1129: .println("TestE1h - coalesce(INT,DECIMAL) with decimal(w,x) will give result decimal(p,x) where p=x+max(w-x,11) and if that gives p>31, then p is set to 31");
1130: System.out
1131: .println("TestE1h1 - coalesce(intCol,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
1132: dumpRSwithScale(s
1133: .executeQuery("select coalesce(intCol,decimalCol1) from tE"));
1134:
1135: System.out
1136: .println("TestE1h2 - coalesce(intCol,decimalCol2) with decimal(8,6) will give result decimal(17,6)");
1137: dumpRSwithScale(s
1138: .executeQuery("select coalesce(intCol,decimalCol2) from tE"));
1139:
1140: System.out
1141: .println("TestE1h3 - coalesce(intCol,decimalCol3) with decimal(31,28) will give result decimal(31,28) rather than giving error for precision > 31");
1142: dumpRSwithScale(s
1143: .executeQuery("select coalesce(intCol,decimalCol3) from tE"));
1144:
1145: System.out.println("TestE1i - coalesce(intCol,realCol)");
1146: dumpRSwithScale(s
1147: .executeQuery("select coalesce(intCol,realCol) from tE"));
1148:
1149: System.out.println("TestE1j - coalesce(intCol,doubleCol)");
1150: dumpRSwithScale(s
1151: .executeQuery("select coalesce(intCol,doubleCol) from tE"));
1152:
1153: System.out.println("TestE - focus on bigint datatypes");
1154: System.out
1155: .println("TestE1 - coalesce(bigintCol,bigintCol)");
1156: dumpRSwithScale(s
1157: .executeQuery("select coalesce(bigintCol,bigintCol) from tE"));
1158:
1159: System.out
1160: .println("TestE1k - coalesce(bigintCol,smallintCol)");
1161: dumpRSwithScale(s
1162: .executeQuery("select coalesce(bigintCol,smallintCol) from tE"));
1163:
1164: System.out.println("TestE1l - coalesce(bigintCol,intCol)");
1165: dumpRSwithScale(s
1166: .executeQuery("select coalesce(bigintCol,intCol) from tE"));
1167:
1168: System.out
1169: .println("TestE1m - coalesce(BIGINT,DECIMAL) with decimal(w,x) will give result decimal(p,x) where p=x+max(w-x,19) and if that gives p>31, then p is set to 31");
1170: System.out
1171: .println("TestE1m1 - coalesce(bigintCol,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
1172: dumpRSwithScale(s
1173: .executeQuery("select coalesce(bigintCol,decimalCol1) from tE"));
1174:
1175: System.out
1176: .println("TestE1m2 - coalesce(bigintCol,decimalCol2) with decimal(8,6) will give result decimal(21,6)");
1177: dumpRSwithScale(s
1178: .executeQuery("select coalesce(bigintCol,decimalCol2) from tE"));
1179:
1180: System.out
1181: .println("TestE1m3 - coalesce(bigintCol,decimalCol3) with decimal(31,28) will give result decimal(31,28) rather than giving error for precision > 31");
1182: dumpRSwithScale(s
1183: .executeQuery("select coalesce(bigintCol,decimalCol3) from tE"));
1184:
1185: System.out.println("TestE1n - coalesce(bigintCol,realCol)");
1186: dumpRSwithScale(s
1187: .executeQuery("select coalesce(bigintCol,realCol) from tE"));
1188:
1189: System.out
1190: .println("TestE1o - coalesce(bigintCol,doubleCol)");
1191: dumpRSwithScale(s
1192: .executeQuery("select coalesce(bigintCol,doubleCol) from tE"));
1193:
1194: System.out.println("TestE - focus on decimal datatypes");
1195:
1196: System.out
1197: .println("TestE1 - coalesce(DECIMAL,DECIMAL) with decimal(w,x), decimal(y,z) will give result decimal(p,s)");
1198: System.out
1199: .println(" where p=max(x,z)+max(w-x,y-z), s=max(x,z) and if that gives p>31, then p is set to 31");
1200: System.out
1201: .println("TestE11 - coalesce(decimalCol1,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
1202: dumpRSwithScale(s
1203: .executeQuery("select coalesce(decimalCol1,decimalCol1) from tE"));
1204:
1205: System.out
1206: .println("TestE12 - coalesce(decimalCol1,decimalCol2) with decimal(22,2) and decimal(8,6) will give result decimal(26,6)");
1207: dumpRSwithScale(s
1208: .executeQuery("select coalesce(decimalCol1,decimalCol2) from tE"));
1209:
1210: System.out
1211: .println("TestE13 - coalesce(decimalCol1,decimalCol3) with decimal(22,2) and decimal(31,28) will give result decimal(31,28) rather than giving error for precision > 31");
1212: dumpRSwithScale(s
1213: .executeQuery("select coalesce(decimalCol1,decimalCol3) from tE"));
1214:
1215: System.out
1216: .println("TestE1p - coalesce(decimalCol1,smallintCol)");
1217: dumpRSwithScale(s
1218: .executeQuery("select coalesce(decimalCol1,smallintCol) from tE"));
1219:
1220: System.out
1221: .println("TestE1q - coalesce(decimalCol1,intCol)");
1222: dumpRSwithScale(s
1223: .executeQuery("select coalesce(decimalCol1,intCol) from tE"));
1224:
1225: System.out
1226: .println("TestE1r - coalesce(decimalCol1,bigintCol)");
1227: dumpRSwithScale(s
1228: .executeQuery("select coalesce(decimalCol1,bigintCol) from tE"));
1229:
1230: System.out
1231: .println("TestE1s - coalesce(decimalCol1,realCol)");
1232: dumpRSwithScale(s
1233: .executeQuery("select coalesce(decimalCol1,realCol) from tE"));
1234:
1235: System.out
1236: .println("TestE1t - coalesce(decimalCol1,doubleCol)");
1237: dumpRSwithScale(s
1238: .executeQuery("select coalesce(decimalCol1,doubleCol) from tE"));
1239:
1240: System.out.println("TestE - focus on real datatypes");
1241: System.out.println("TestE1 - coalesce(realCol,realCol)");
1242: dumpRSwithScale(s
1243: .executeQuery("select coalesce(realCol,realCol) from tE"));
1244:
1245: System.out
1246: .println("TestE1u - coalesce(realCol,smallintCol)");
1247: dumpRSwithScale(s
1248: .executeQuery("select coalesce(realCol,smallintCol) from tE"));
1249:
1250: System.out.println("TestE1v - coalesce(realCol,intCol)");
1251: dumpRSwithScale(s
1252: .executeQuery("select coalesce(realCol,intCol) from tE"));
1253:
1254: System.out.println("TestE1w - coalesce(realCol,bigintCol)");
1255: dumpRSwithScale(s
1256: .executeQuery("select coalesce(realCol,bigintCol) from tE"));
1257:
1258: System.out
1259: .println("TestE1x - coalesce(realCol,decimalCol1)");
1260: dumpRSwithScale(s
1261: .executeQuery("select coalesce(realCol,decimalCol1) from tE"));
1262:
1263: System.out.println("TestE1y - coalesce(realCol,doubleCol)");
1264: dumpRSwithScale(s
1265: .executeQuery("select coalesce(realCol,doubleCol) from tE"));
1266:
1267: System.out.println("TestE - focus on double datatypes");
1268: System.out
1269: .println("TestE1 - coalesce(doubleCol,doubleCol)");
1270: dumpRSwithScale(s
1271: .executeQuery("select coalesce(doubleCol,doubleCol) from tE"));
1272:
1273: System.out
1274: .println("TestE1z - coalesce(doubleCol,smallintCol)");
1275: dumpRSwithScale(s
1276: .executeQuery("select coalesce(doubleCol,smallintCol) from tE"));
1277:
1278: System.out.println("TestE2a - coalesce(doubleCol,intCol)");
1279: dumpRSwithScale(s
1280: .executeQuery("select coalesce(doubleCol,intCol) from tE"));
1281:
1282: System.out
1283: .println("TestE2b - coalesce(doubleCol,bigintCol)");
1284: dumpRSwithScale(s
1285: .executeQuery("select coalesce(doubleCol,bigintCol) from tE"));
1286:
1287: System.out
1288: .println("TestE2c - coalesce(doubleCol,decimalCol1)");
1289: dumpRSwithScale(s
1290: .executeQuery("select coalesce(doubleCol,decimalCol1) from tE"));
1291:
1292: System.out.println("TestE2d - coalesce(doubleCol,realCol)");
1293: dumpRSwithScale(s
1294: .executeQuery("select coalesce(doubleCol,realCol) from tE"));
1295:
1296: s.executeUpdate("drop table tE");
1297: } catch (SQLException sqle) {
1298: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
1299: System.out, sqle);
1300: sqle.printStackTrace(System.out);
1301: }
1302: }
1303:
1304: public static void testCharCoalesce(Connection conn)
1305: throws Throwable {
1306: try {
1307: Statement s = conn.createStatement();
1308: PreparedStatement ps;
1309: try {
1310: s.executeUpdate("drop table tB");
1311: } catch (Exception ex) {
1312: }
1313: s
1314: .executeUpdate("create table tB (c1 char(254), c2 char(40), vc1 varchar(253), vc2 varchar(2000), lvc1 long varchar, lvc2 long varchar, clob1 CLOB(200), clob2 CLOB(33K))");
1315: s
1316: .executeUpdate("insert into tB values('c1 not null', 'c2 not null', 'vc1 not null', 'vc2 not null', 'lvc1 not null', 'lvc2 not null', 'clob1 not null', 'clob2 not null')");
1317: s
1318: .executeUpdate("insert into tB values('c1 not null but c2 is', null, 'vc1 is not null but vc2 is', null, null, null,null,null)");
1319: s
1320: .executeUpdate("insert into tB values(null,'c2 not null but c1 is', null, 'vc2 is not null but vc1 is', 'lvc1 not null again', 'lvc2 not null again', 'clob1 not null again', 'clob2 not null again')");
1321: s
1322: .executeUpdate("insert into tB values(null,null, null, null, null, null, null, null)");
1323:
1324: System.out
1325: .println("TestB - Focus on CHAR as atleast one of the operands");
1326: System.out
1327: .println("TestB1a - 2 CHAR operands coalesce(c1,c2) with c1(254) and c2(40)");
1328: dumpRS(s.executeQuery("select coalesce(c1,c2) from tB"));
1329:
1330: System.out
1331: .println("TestB1b - 2 CHAR operands value(c1,c2) with c1(254) and c2(40)");
1332: dumpRS(s.executeQuery("select value(c1,c2) from tB"));
1333:
1334: System.out
1335: .println("TestB2a - 2 CHAR operands coalesce(c2,c1) with c2(40) and c1(254)");
1336: dumpRS(s.executeQuery("select coalesce(c2,c1) from tB"));
1337:
1338: System.out
1339: .println("TestB2b - 2 CHAR operands value(c2,c1) with c2(40) and c1(254)");
1340: dumpRS(s.executeQuery("select value(c2,c1) from tB"));
1341:
1342: System.out
1343: .println("TestB3a - CHAR and VARCHAR operands coalesce(c1,vc1) with c1(254) and vc1(253)");
1344: dumpRS(s.executeQuery("select coalesce(c1,vc1) from tB"));
1345:
1346: System.out
1347: .println("TestB3b - CHAR and VARCHAR operands value(c1,vc1) with c1(254) and vc1(253)");
1348: dumpRS(s.executeQuery("select value(c1,vc1) from tB"));
1349:
1350: System.out
1351: .println("TestB4a - VARCHAR and CHAR operands coalesce(vc1,c1) with vc1(253) and c1(254)");
1352: dumpRS(s.executeQuery("select coalesce(vc1,c1) from tB"));
1353:
1354: System.out
1355: .println("TestB4b - VARCHAR AND CHAR operands value(vc1,c1) with vc1(253) and c1(254)");
1356: dumpRS(s.executeQuery("select value(vc1,c1) from tB"));
1357:
1358: System.out
1359: .println("TestB - Focus on VARCHAR as atleast one of the operands");
1360: System.out
1361: .println("TestB5a - 2 VARCHAR operands coalesce(vc1,vc2) with vc1(253) and vc2(2000)");
1362: dumpRS(s.executeQuery("select coalesce(vc1,vc2) from tB"));
1363:
1364: System.out
1365: .println("TestB5b - 2 VARCHAR operands value(vc1,vc2) with vc1(253) and vc2(2000)");
1366: dumpRS(s.executeQuery("select value(vc1,vc2) from tB"));
1367:
1368: System.out
1369: .println("TestB6a - 2 VARCHAR operands coalesce(vc2,vc1) with vc2(2000) and vc1(253)");
1370: dumpRS(s.executeQuery("select coalesce(vc2,vc1) from tB"));
1371:
1372: System.out
1373: .println("TestB6b - 2 VARCHAR operands value(vc2,vc1) with vc2(2000) and vc1(253)");
1374: dumpRS(s.executeQuery("select value(vc2,vc1) from tB"));
1375:
1376: System.out
1377: .println("TestB - Focus on LONG VARCHAR as atleast one of the operands");
1378: System.out
1379: .println("TestB7a - CHAR and LONG VARCHAR operands coalesce(c1,lvc1) with c1(254)");
1380: dumpRS(s.executeQuery("select coalesce(c1,lvc1) from tB"));
1381:
1382: System.out
1383: .println("TestB7b - CHAR and LONG VARCHAR operands value(c1,lvc1) with c1(254)");
1384: dumpRS(s.executeQuery("select value(c1,lvc1) from tB"));
1385:
1386: System.out
1387: .println("TestB8a - LONG VARCHAR and CHAR operands coalesce(lvc1,c1) with c1(254)");
1388: dumpRS(s.executeQuery("select coalesce(lvc1,c1) from tB"));
1389:
1390: System.out
1391: .println("TestB8b - LONG VARCHAR and CHAR operands value(lvc1,c1) with c1(254)");
1392: dumpRS(s.executeQuery("select value(lvc1,c1) from tB"));
1393:
1394: System.out
1395: .println("TestB9a - VARCHAR and LONG VARCHAR operands coalesce(vc1,lvc1) with vc1(253)");
1396: dumpRS(s.executeQuery("select coalesce(vc1,lvc1) from tB"));
1397:
1398: System.out
1399: .println("TestB9b - VARCHAR and LONG VARCHAR operands value(vc1,lvc1) with vc1(253)");
1400: dumpRS(s.executeQuery("select value(vc1,lvc1) from tB"));
1401:
1402: System.out
1403: .println("TestB10a - LONG VARCHAR and VARCHAR operands coalesce(lvc1,vc1) with vc1(253)");
1404: dumpRS(s.executeQuery("select coalesce(lvc1,vc1) from tB"));
1405:
1406: System.out
1407: .println("TestB10b - LONG VARCHAR and VARCHAR operands value(lvc1,vc1) with vc1(253)");
1408: dumpRS(s.executeQuery("select value(lvc1,vc1) from tB"));
1409:
1410: System.out
1411: .println("TestB11a - LONG VARCHAR and LONG VARCHAR operands coalesce(lvc1,lvc2)");
1412: dumpRS(s.executeQuery("select coalesce(lvc1,lvc2) from tB"));
1413:
1414: System.out
1415: .println("TestB11b - LONG VARCHAR and LONG VARCHAR operands value(lvc1,lvc2)");
1416: dumpRS(s.executeQuery("select value(lvc1,lvc2) from tB"));
1417:
1418: System.out
1419: .println("TestB - Focus on CLOB as atleast one of the operands");
1420: System.out
1421: .println("TestB12a - CLOB and CHAR operands coalesce(clob1,c1) with clob1(200) and c1(254)");
1422: dumpRS(s.executeQuery("select coalesce(clob1,c1) from tB"));
1423:
1424: System.out
1425: .println("TestB12b - CLOB and CHAR operands value(clob1,c1) with clob1(200) and c1(254)");
1426: dumpRS(s.executeQuery("select value(clob1,c1) from tB"));
1427:
1428: System.out
1429: .println("TestB13a - CHAR and CLOB operands coalesce(c1,clob2) with c1(254) and clob2(33K)");
1430: dumpRS(s.executeQuery("select coalesce(c1,clob2) from tB"));
1431:
1432: System.out
1433: .println("TestB13b - CHAR and CLOB operands value(c1,clob2) with c1(254) and clob2(33K)");
1434: dumpRS(s.executeQuery("select value(c1,clob2) from tB"));
1435:
1436: System.out
1437: .println("TestB14a - CLOB and VARCHAR operands coalesce(clob1,vc1) with clob1(200) and vc1(253)");
1438: dumpRS(s.executeQuery("select coalesce(clob1,vc1) from tB"));
1439:
1440: System.out
1441: .println("TestB14b - CLOB and VARCHAR operands value(clob1,vc1) with clob1(200) and vc1(253)");
1442: dumpRS(s.executeQuery("select value(clob1,vc1) from tB"));
1443:
1444: System.out
1445: .println("TestB15a - VARCHAR and CLOB operands coalesce(vc2,clob2) with vc2(2000) and clob2(33K)");
1446: dumpRS(s.executeQuery("select coalesce(vc2,clob2) from tB"));
1447:
1448: System.out
1449: .println("TestB15b - VARCHAR and CLOB operands value(vc2,clob2) with vc2(2000) and clob2(33K)");
1450: dumpRS(s.executeQuery("select value(vc2,clob2) from tB"));
1451:
1452: System.out
1453: .println("TestB16a - CLOB and LONG VARCHAR operands coalesce(clob1,lvc1) with clob1(200). The result length will be 32700 (long varchar max length)");
1454: dumpRS(s
1455: .executeQuery("select coalesce(clob1,lvc1) from tB"));
1456:
1457: System.out
1458: .println("TestB16b - CLOB and LONG VARCHAR operands value(clob1,lvc1) with clob1(200). The result length will be 32700 (long varchar max length)");
1459: dumpRS(s.executeQuery("select value(clob1,lvc1) from tB"));
1460:
1461: System.out
1462: .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)");
1463: dumpRS(s
1464: .executeQuery("select coalesce(lvc2,clob2) from tB"));
1465:
1466: System.out
1467: .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)");
1468: dumpRS(s.executeQuery("select value(lvc2,clob2) from tB"));
1469:
1470: System.out
1471: .println("TestB18a - CLOB and CLOB operands coalesce(clob1,clob2) with clob1(200) and clob2(33K).");
1472: dumpRS(s
1473: .executeQuery("select coalesce(clob1,clob2) from tB"));
1474:
1475: System.out
1476: .println("TestB18b - CLOB and CLOB operands value(clob1,clob2) with clob1(200) and clob2(33K).");
1477: dumpRS(s.executeQuery("select value(clob1,clob2) from tB"));
1478:
1479: s.executeUpdate("drop table tB");
1480: } catch (SQLException sqle) {
1481: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
1482: System.out, sqle);
1483: sqle.printStackTrace(System.out);
1484: }
1485: }
1486:
1487: public static void testCharForBitDataCoalesce(Connection conn)
1488: throws Throwable {
1489: try {
1490: Statement s = conn.createStatement();
1491: PreparedStatement ps;
1492: try {
1493: s.executeUpdate("drop table tC");
1494: } catch (Exception ex) {
1495: }
1496: s
1497: .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))");
1498: ps = conn
1499: .prepareStatement("insert into tC values (?,?,?,?,?,?,?,?)");
1500: ps.setBytes(1, "cbd1 not null".getBytes("US-ASCII"));
1501: ps.setBytes(2, "cbd2 not null".getBytes("US-ASCII"));
1502: ps.setBytes(3, "vcbd1 not null".getBytes("US-ASCII"));
1503: ps.setBytes(4, "vcbd2 not null".getBytes("US-ASCII"));
1504: ps.setBytes(5, "lvcbd1 not null".getBytes("US-ASCII"));
1505: ps.setBytes(6, "lvcbd2 not null".getBytes("US-ASCII"));
1506: ps.setBytes(7, "blob1 not null".getBytes("US-ASCII"));
1507: ps.setBytes(8, "blob2 not null".getBytes("US-ASCII"));
1508: ps.executeUpdate();
1509: ps.setBytes(1, "cbd1 not null but cbd2 is"
1510: .getBytes("US-ASCII"));
1511: ps.setBytes(2, null);
1512: ps.setBytes(3, "vcbd1 not null but vcbd2 is"
1513: .getBytes("US-ASCII"));
1514: ps.setBytes(4, null);
1515: ps.setBytes(5, null);
1516: ps.setBytes(6, null);
1517: ps.setBytes(7, null);
1518: ps.setBytes(8, null);
1519: ps.executeUpdate();
1520: ps.setBytes(1, null);
1521: ps.setBytes(2, "cbd2 not null but cbd1 is"
1522: .getBytes("US-ASCII"));
1523: ps.setBytes(3, null);
1524: ps.setBytes(4, "vcbd2 not null but vcbd1 is"
1525: .getBytes("US-ASCII"));
1526: ps
1527: .setBytes(5, "lvcbd1 not null again"
1528: .getBytes("US-ASCII"));
1529: ps
1530: .setBytes(6, "lvcbd2 not null again"
1531: .getBytes("US-ASCII"));
1532: ps.setBytes(7, "blob1 not null again".getBytes("US-ASCII"));
1533: ps.setBytes(8, "blob2 not null again".getBytes("US-ASCII"));
1534: ps.executeUpdate();
1535: ps.setBytes(1, null);
1536: ps.setBytes(2, null);
1537: ps.setBytes(3, null);
1538: ps.setBytes(4, null);
1539: ps.setBytes(5, null);
1540: ps.setBytes(6, null);
1541: ps.setBytes(7, null);
1542: ps.setBytes(8, null);
1543: ps.executeUpdate();
1544:
1545: System.out
1546: .println("TestC - Focus on CHAR FOR BIT DATA as atleast one of the operands");
1547: System.out
1548: .println("TestC1a - 2 CHAR FOR BIT DATA operands coalesce(cbd1,cbd2) with cbd1(254) and cbd2(40)");
1549: dumpRS(s.executeQuery("select coalesce(cbd1,cbd2) from tC"));
1550:
1551: System.out
1552: .println("TestC1b - 2 CHAR FOR BIT DATA operands value(cbd1,cbd2) with cbd1(254) and cbd2(40)");
1553: dumpRS(s.executeQuery("select value(cbd1,cbd2) from tC"));
1554:
1555: System.out
1556: .println("TestC2a - 2 CHAR FOR BIT DATA operands coalesce(cbd2,cbd1) with cbd2(40) and cbd1(254)");
1557: dumpRS(s.executeQuery("select coalesce(cbd2,cbd1) from tC"));
1558:
1559: System.out
1560: .println("TestC2b - 2 CHAR FOR BIT DATA operands value(cbd2,cbd1) with cbd2(40) and cbd1(254)");
1561: dumpRS(s.executeQuery("select value(cbd2,cbd1) from tC"));
1562:
1563: System.out
1564: .println("TestC3a - CHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands coalesce(cbd1,vcbd1) with cbd1(254) and vcbd1(253)");
1565: dumpRS(s
1566: .executeQuery("select coalesce(cbd1,vcbd1) from tC"));
1567:
1568: System.out
1569: .println("TestC3b - CHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands value(cbd1,vcbd1) with cbd1(254) and vcbd1(253)");
1570: dumpRS(s.executeQuery("select value(cbd1,vcbd1) from tC"));
1571:
1572: System.out
1573: .println("TestC4a - VARCHAR FOR BIT DATA and CHAR FOR BIT DATA operands coalesce(vcbd1,cbd1) with vcbd1(253) and cbd1(254)");
1574: dumpRS(s
1575: .executeQuery("select coalesce(vcbd1,cbd1) from tC"));
1576:
1577: System.out
1578: .println("TestC4b - VARCHAR FOR BIT DATA AND CHAR FOR BIT DATA operands value(vcbd1,cbd1) with vcbd1(253) and cbd1(254)");
1579: dumpRS(s.executeQuery("select value(vcbd1,cbd1) from tC"));
1580:
1581: System.out
1582: .println("TestC - Focus on VARCHAR FOR BIT DATA as atleast one of the operands");
1583: System.out
1584: .println("TestC5a - 2 VARCHAR FOR BIT DATA operands coalesce(vcbd1,vcbd2) with vcbd1(253) and vcbd2(2000)");
1585: dumpRS(s
1586: .executeQuery("select coalesce(vcbd1,vcbd2) from tC"));
1587:
1588: System.out
1589: .println("TestC5b - 2 VARCHAR FOR BIT DATA operands value(vcbd1,vcbd2) with vcbd1(253) and vcbd2(2000)");
1590: dumpRS(s.executeQuery("select value(vcbd1,vcbd2) from tC"));
1591:
1592: System.out
1593: .println("TestC6a - 2 VARCHAR FOR BIT DATA operands coalesce(vcbd2,vcbd1) with vcbd2(2000) and vcbd1(253)");
1594: dumpRS(s
1595: .executeQuery("select coalesce(vcbd2,vcbd1) from tC"));
1596:
1597: System.out
1598: .println("TestC6b - 2 VARCHAR FOR BIT DATA operands value(vcbd2,vcbd1) with vcbd2(2000) and vcbd1(253)");
1599: dumpRS(s.executeQuery("select value(vcbd2,vcbd1) from tC"));
1600:
1601: System.out
1602: .println("TestC - Focus on LONG VARCHAR FOR BIT DATA as atleast one of the operands");
1603: System.out
1604: .println("TestC7a - CHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands coalesce(cbd1,lvcbd1) with cbd1(254)");
1605: dumpRS(s
1606: .executeQuery("select coalesce(cbd1,lvcbd1) from tC"));
1607:
1608: System.out
1609: .println("TestC7b - CHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands value(cbd1,lvcbd1) with cbd1(254)");
1610: dumpRS(s.executeQuery("select value(cbd1,lvcbd1) from tC"));
1611:
1612: System.out
1613: .println("TestC8a - LONG VARCHAR FOR BIT DATA and CHAR FOR BIT DATA operands coalesce(lvcbd1,cbd1) with cbd1(254)");
1614: dumpRS(s
1615: .executeQuery("select coalesce(lvcbd1,cbd1) from tC"));
1616:
1617: System.out
1618: .println("TestC8b - LONG VARCHAR FOR BIT DATA and CHAR FOR BIT DATA operands value(lvcbd1,cbd1) with cbd1(254)");
1619: dumpRS(s.executeQuery("select value(lvcbd1,cbd1) from tC"));
1620:
1621: System.out
1622: .println("TestC9a - VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands coalesce(vcbd1,lvcbd1) with vcbd1(253)");
1623: dumpRS(s
1624: .executeQuery("select coalesce(vcbd1,lvcbd1) from tC"));
1625:
1626: System.out
1627: .println("TestC9b - VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands value(vcbd1,lvcbd1) with vcbd1(253)");
1628: dumpRS(s.executeQuery("select value(vcbd1,lvcbd1) from tC"));
1629:
1630: System.out
1631: .println("TestC10a - LONG VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands coalesce(lvcbd1,vcbd1) with vcbd1(253)");
1632: dumpRS(s
1633: .executeQuery("select coalesce(lvcbd1,vcbd1) from tC"));
1634:
1635: System.out
1636: .println("TestC10b - LONG VARCHAR FOR BIT DATA and VARCHAR FOR BIT DATA operands value(lvcbd1,vcbd1) with vcbd1(253)");
1637: dumpRS(s.executeQuery("select value(lvcbd1,vcbd1) from tC"));
1638:
1639: System.out
1640: .println("TestC11a - LONG VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands coalesce(lvcbd1,lvcbd2)");
1641: dumpRS(s
1642: .executeQuery("select coalesce(lvcbd1,lvcbd2) from tC"));
1643:
1644: System.out
1645: .println("TestC11b - LONG VARCHAR FOR BIT DATA and LONG VARCHAR FOR BIT DATA operands value(lvcbd1,lvcbd2)");
1646: dumpRS(s
1647: .executeQuery("select value(lvcbd1,lvcbd2) from tC"));
1648:
1649: System.out
1650: .println("TestC - Focus on BLOB as atleast one of the operands");
1651: try {
1652: System.out
1653: .println("TestC12a - BLOB and CHAR FOR BIT DATA in coalesce(blob1,cbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1654: dumpRS(s
1655: .executeQuery("select coalesce(blob1,cbd1) from tC"));
1656: System.out.println("TestC12a - should have failed");
1657: } catch (SQLException e) {
1658: if (e.getSQLState().equals("42815"))
1659: System.out.println("expected exception "
1660: + e.getMessage());
1661: else
1662: dumpSQLExceptions(e);
1663: }
1664:
1665: try {
1666: System.out
1667: .println("TestC12b - BLOB and CHAR FOR BIT DATA in value(blob1,cbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1668: dumpRS(s
1669: .executeQuery("select value(blob1,cbd1) from tC"));
1670: System.out.println("TestC12b - should have failed");
1671: } catch (SQLException e) {
1672: if (e.getSQLState().equals("42815"))
1673: System.out.println("expected exception "
1674: + e.getMessage());
1675: else
1676: dumpSQLExceptions(e);
1677: }
1678:
1679: try {
1680: System.out
1681: .println("TestC13a - CHAR FOR BIT DATA and BLOB operands coalesce(cbd1,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1682: dumpRS(s
1683: .executeQuery("select coalesce(cbd1,blob2) from tC"));
1684: System.out.println("TestC13a - should have failed");
1685: } catch (SQLException e) {
1686: if (e.getSQLState().equals("42815"))
1687: System.out.println("expected exception "
1688: + e.getMessage());
1689: else
1690: dumpSQLExceptions(e);
1691: }
1692:
1693: try {
1694: System.out
1695: .println("TestC13b - CHAR FOR BIT DATA and BLOB operands value(cbd1,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1696: dumpRS(s
1697: .executeQuery("select value(cbd1,blob2) from tC"));
1698: System.out.println("TestC13b - should have failed");
1699: } catch (SQLException e) {
1700: if (e.getSQLState().equals("42815"))
1701: System.out.println("expected exception "
1702: + e.getMessage());
1703: else
1704: dumpSQLExceptions(e);
1705: }
1706:
1707: try {
1708: System.out
1709: .println("TestC14a - BLOB and VARCHAR FOR BIT DATA operands coalesce(blob1,vcbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1710: dumpRS(s
1711: .executeQuery("select coalesce(blob1,vcbd1) from tC"));
1712: System.out.println("TestC14a - should have failed");
1713: } catch (SQLException e) {
1714: if (e.getSQLState().equals("42815"))
1715: System.out.println("expected exception "
1716: + e.getMessage());
1717: else
1718: dumpSQLExceptions(e);
1719: }
1720:
1721: try {
1722: System.out
1723: .println("TestC14b - BLOB and VARCHAR FOR BIT DATA operands value(blob1,vcbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1724: dumpRS(s
1725: .executeQuery("select value(blob1,vcbd1) from tC"));
1726: System.out.println("TestC14b - should have failed");
1727: } catch (SQLException e) {
1728: if (e.getSQLState().equals("42815"))
1729: System.out.println("expected exception "
1730: + e.getMessage());
1731: else
1732: dumpSQLExceptions(e);
1733: }
1734:
1735: try {
1736: System.out
1737: .println("TestC15a - VARCHAR FOR BIT DATA and BLOB operands coalesce(vcbd2,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1738: dumpRS(s
1739: .executeQuery("select coalesce(vcbd2,blob2) from tC"));
1740: System.out.println("TestC15a - should have failed");
1741: } catch (SQLException e) {
1742: if (e.getSQLState().equals("42815"))
1743: System.out.println("expected exception "
1744: + e.getMessage());
1745: else
1746: dumpSQLExceptions(e);
1747: }
1748:
1749: try {
1750: System.out
1751: .println("TestC15b - VARCHAR FOR BIT DATA and BLOB operands value(vcbd2,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1752: dumpRS(s
1753: .executeQuery("select value(vcbd2,blob2) from tC"));
1754: System.out.println("TestC15b - should have failed");
1755: } catch (SQLException e) {
1756: if (e.getSQLState().equals("42815"))
1757: System.out.println("expected exception "
1758: + e.getMessage());
1759: else
1760: dumpSQLExceptions(e);
1761: }
1762:
1763: try {
1764: System.out
1765: .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");
1766: dumpRS(s
1767: .executeQuery("select coalesce(blob1,lvcbd1) from tC"));
1768: System.out.println("TestC16a - should have failed");
1769: } catch (SQLException e) {
1770: if (e.getSQLState().equals("42815"))
1771: System.out.println("expected exception "
1772: + e.getMessage());
1773: else
1774: dumpSQLExceptions(e);
1775: }
1776:
1777: try {
1778: System.out
1779: .println("TestC16b - BLOB and LONG VARCHAR FOR BIT DATA operands coalesce(blob1,lvcbd1) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1780: dumpRS(s
1781: .executeQuery("select value(blob1,lvcbd1) from tC"));
1782: System.out.println("TestC16b - should have failed");
1783: } catch (SQLException e) {
1784: if (e.getSQLState().equals("42815"))
1785: System.out.println("expected exception "
1786: + e.getMessage());
1787: else
1788: dumpSQLExceptions(e);
1789: }
1790:
1791: try {
1792: System.out
1793: .println("TestC17a - LONG VARCHAR FOR BIT DATA and BLOB operands coalesce(lvcbd2,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1794: dumpRS(s
1795: .executeQuery("select coalesce(lvcbd2,blob2) from tC"));
1796: System.out.println("TestC17a - should have failed");
1797: } catch (SQLException e) {
1798: if (e.getSQLState().equals("42815"))
1799: System.out.println("expected exception "
1800: + e.getMessage());
1801: else
1802: dumpSQLExceptions(e);
1803: }
1804:
1805: try {
1806: System.out
1807: .println("TestC17b - LONG VARCHAR FOR BIT DATA and BLOB operands value(lvcbd2,blob2) will fail because BLOB is not compatible with FOR BIT DATA datatypes");
1808: dumpRS(s
1809: .executeQuery("select value(lvcbd2,blob2) from tC"));
1810: System.out.println("TestC17b - should have failed");
1811: } catch (SQLException e) {
1812: if (e.getSQLState().equals("42815"))
1813: System.out.println("expected exception "
1814: + e.getMessage());
1815: else
1816: dumpSQLExceptions(e);
1817: }
1818:
1819: System.out
1820: .println("TestC18a - BLOB and BLOB operands coalesce(blob1,blob2) with blob1(200) and blob2(33K).");
1821: dumpRS(s
1822: .executeQuery("select coalesce(blob1,blob2) from tC"));
1823:
1824: System.out
1825: .println("TestC18b - BLOB and BLOB operands value(blob1,blob2) with blob1(200) and blob2(33K).");
1826: dumpRS(s.executeQuery("select value(blob1,blob2) from tC"));
1827:
1828: s.executeUpdate("drop table tC");
1829: } catch (SQLException sqle) {
1830: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
1831: System.out, sqle);
1832: sqle.printStackTrace(System.out);
1833: }
1834: }
1835:
1836: static private void dumpSQLExceptions(SQLException se) {
1837: System.out.println("FAIL -- unexpected exception: "
1838: + se.toString());
1839: while (se != null) {
1840: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
1841: se = se.getNextException();
1842: }
1843: }
1844:
1845: // lifted from the metadata test
1846: public static void dumpRS(ResultSet s) throws SQLException {
1847: if (s == null) {
1848: System.out.println("<NULL>");
1849: return;
1850: }
1851:
1852: ResultSetMetaData rsmd = s.getMetaData();
1853:
1854: // Get the number of columns in the result set
1855: int numCols = rsmd.getColumnCount();
1856:
1857: if (numCols <= 0) {
1858: System.out.println("(no columns!)");
1859: return;
1860: }
1861:
1862: StringBuffer heading = new StringBuffer("\t ");
1863: StringBuffer underline = new StringBuffer("\t ");
1864:
1865: int len;
1866: // Display column headings
1867: for (int i = 1; i <= numCols; i++) {
1868: if (i > 1) {
1869: heading.append(",");
1870: underline.append(" ");
1871: }
1872: len = heading.length();
1873: heading.append("COL" + i);
1874: heading.append("(datatype : " + rsmd.getColumnTypeName(i));
1875: heading.append(", precision : " + rsmd.getPrecision(i));
1876: heading.append(", scale : " + rsmd.getScale(i) + ")");
1877: len = heading.length() - len;
1878: for (int j = len; j > 0; j--) {
1879: underline.append("-");
1880: }
1881: }
1882: System.out.println(heading.toString());
1883: System.out.println(underline.toString());
1884:
1885: StringBuffer row = new StringBuffer();
1886: // Display data, fetching until end of the result set
1887: while (s.next()) {
1888: row.append("\t{");
1889: // Loop through each column, getting the
1890: // column data and displaying
1891: for (int i = 1; i <= numCols; i++) {
1892: if (i > 1)
1893: row.append(",");
1894: try {
1895: row.append(s.getString(i));
1896: } catch (SQLException ex) {
1897: if (ex.getSQLState().equals("22005"))
1898: row.append("Invalid Conversion Error\n");
1899: else
1900: throw ex;
1901: }
1902: }
1903: row.append("}\n");
1904: }
1905: System.out.println(row.toString());
1906: s.close();
1907: }
1908:
1909: // lifted from the metadata test
1910: public static void dumpRSwithScale(ResultSet s) throws SQLException {
1911: if (s == null) {
1912: System.out.println("<NULL>");
1913: return;
1914: }
1915:
1916: ResultSetMetaData rsmd = s.getMetaData();
1917:
1918: // Get the number of columns in the result set
1919: int numCols = rsmd.getColumnCount();
1920:
1921: if (numCols <= 0) {
1922: System.out.println("(no columns!)");
1923: return;
1924: }
1925:
1926: StringBuffer heading = new StringBuffer("\t ");
1927: StringBuffer underline = new StringBuffer("\t ");
1928:
1929: int len;
1930: // Display column headings
1931: for (int i = 1; i <= numCols; i++) {
1932: if (i > 1) {
1933: heading.append(",");
1934: underline.append(" ");
1935: }
1936: len = heading.length();
1937: heading.append("COL" + i);
1938: heading.append("(datatype : " + rsmd.getColumnTypeName(i));
1939: heading.append(", precision : " + rsmd.getPrecision(i));
1940: heading.append(", scale : " + rsmd.getScale(i) + ")");
1941: len = heading.length() - len;
1942: for (int j = len; j > 0; j--) {
1943: underline.append("-");
1944: }
1945: }
1946: System.out.println(heading.toString());
1947: System.out.println(underline.toString());
1948:
1949: StringBuffer row = new StringBuffer();
1950: // Display data, fetching until end of the result set
1951: while (s.next()) {
1952: row.append("\t{");
1953: // Loop through each column, getting the
1954: // column data and displaying
1955: for (int i = 1; i <= numCols; i++) {
1956: if (i > 1)
1957: row.append(",");
1958: row.append(s.getString(i));
1959: }
1960: row.append("}\n");
1961: }
1962: System.out.println(row.toString());
1963: s.close();
1964: }
1965:
1966: }
|