Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 92302 invoked from network); 14 Mar 2007 17:29:04 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 14 Mar 2007 17:29:04 -0000 Received: (qmail 63694 invoked by uid 500); 14 Mar 2007 17:29:13 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 63662 invoked by uid 500); 14 Mar 2007 17:29:13 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 63632 invoked by uid 99); 14 Mar 2007 17:29:12 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 14 Mar 2007 10:29:12 -0700 X-ASF-Spam-Status: No, hits=-99.5 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 14 Mar 2007 10:29:02 -0700 Received: by eris.apache.org (Postfix, from userid 65534) id 75D911A983E; Wed, 14 Mar 2007 10:28:42 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r518228 [2/2] - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: suites/ tests/jdbcapi/ tests/lang/ Date: Wed, 14 Mar 2007 17:28:42 -0000 To: derby-commits@db.apache.org From: kmarsden@apache.org X-Mailer: svnmailer-1.1.0 Message-Id: <20070314172842.75D911A983E@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java?view=auto&rev=518228 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java Wed Mar 14 10:28:41 2007 @@ -0,0 +1,384 @@ +/** + * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.NullIfTest + * + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.derbyTesting.functionTests.tests.lang; + +import java.sql.Connection; +import java.sql.Date; +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.ResultSet; +import java.sql.Time; +import java.sql.Timestamp; + +import junit.framework.Test; +import junit.framework.TestSuite; + +import org.apache.derbyTesting.junit.BaseJDBCTestCase; +import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; +import org.apache.derbyTesting.junit.TestConfiguration; + +import org.apache.derbyTesting.functionTests.tests.lang.CastingTest; + +public class NullIfTest extends BaseJDBCTestCase { + + private static String VALID_DATE_STRING = "'2000-01-01'"; + private static String VALID_TIME_STRING = "'15:30:20'"; + private static String VALID_TIMESTAMP_STRING = "'2000-01-01 15:30:20'"; + private static String NULL_VALUE="NULL"; + + private static String[] ColumnNames = + { + "SMALLINTCOL", + "INTEGERCOL", + "BIGINTCOL", + "DECIMALCOL", + "REALCOL", + "DOUBLECOL", + "CHARCOL", + "VARCHARCOL", + "LONGVARCHARCOL", + "CHARFORBITCOL", + "VARCHARFORBITCOL", + "LVARCHARFORBITCOL", + "CLOBCOL", + "DATECOL", + "TIMECOL", + "TIMESTAMPCOL", + "BLOBCOL", + + }; + + private static String[][]SQLData = + { + {NULL_VALUE, "0","1","2"}, // SMALLINT + {NULL_VALUE,"0","1","21"}, // INTEGER + {NULL_VALUE,"0","1","22"}, // BIGINT + {NULL_VALUE,"0.0","1.0","23.0"}, // DECIMAL(10,5) + {NULL_VALUE,"0.0","1.0","24.0"}, // REAL, + {NULL_VALUE,"0.0","1.0","25.0"}, // DOUBLE + {NULL_VALUE,"'0'","'aa'","'2.0'"}, // CHAR(60) + {NULL_VALUE,"'0'","'aa'",VALID_TIME_STRING}, //VARCHAR(60)", + {NULL_VALUE,"'0'","'aa'",VALID_TIMESTAMP_STRING}, // LONG VARCHAR + {NULL_VALUE,"X'10aa'",NULL_VALUE,"X'10aaaa'"}, // CHAR(60) FOR BIT DATA + {NULL_VALUE,"X'10aa'",NULL_VALUE,"X'10aaba'"}, // VARCHAR(60) FOR BIT DATA + {NULL_VALUE,"X'10aa'",NULL_VALUE,"X'10aaca'"}, //LONG VARCHAR FOR BIT DATA + {NULL_VALUE,"'13'","'14'",NULL_VALUE}, //CLOB(1k) + {NULL_VALUE,VALID_DATE_STRING,VALID_DATE_STRING,NULL_VALUE}, // DATE + {NULL_VALUE,VALID_TIME_STRING,VALID_TIME_STRING,VALID_TIME_STRING}, // TIME + {NULL_VALUE,VALID_TIMESTAMP_STRING,VALID_TIMESTAMP_STRING,VALID_TIMESTAMP_STRING}, // TIMESTAMP + {NULL_VALUE,NULL_VALUE,NULL_VALUE,NULL_VALUE} // BLOB + }; + + private static String[][][] nullIfResults ={ + /*SMALLINT*/ {/*SMALLINT*/ {null,null,null,null},/*INTEGER*/ {null,null,null,"2"},/*BIGINT*/ {null,null,null,"2"},/*DECIMAL(10,5)*/ {null,null,null,"2"},/*REAL*/ {null,null,null,"2"},/*DOUBLE*/ {null,null,null,"2"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*INTEGER*/ {/*SMALLINT*/ {null,null,null,"21"},/*INTEGER*/ {null,null,null,null},/*BIGINT*/ {null,null,null,"21"},/*DECIMAL(10,5)*/ {null,null,null,"21"},/*REAL*/ {null,null,null,"21"},/*DOUBLE*/ {null,null,null,"21"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*BIGINT*/ {/*SMALLINT*/ {null,null,null,"22"},/*INTEGER*/ {null,null,null,"22"},/*BIGINT*/ {null,null,null,null},/*DECIMAL(10,5)*/ {null,null,null,"22"},/*REAL*/ {null,null,null,"22"},/*DOUBLE*/ {null,null,null,"22"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*DECIMAL(10,5)*/ {/*SMALLINT*/ {null,null,null,"23.00000"},/*INTEGER*/ {null,null,null,"23.00000"},/*BIGINT*/ {null,null,null,"23.00000"},/*DECIMAL(10,5)*/ {null,null,null,null},/*REAL*/ {null,null,null,"23.00000"},/*DOUBLE*/ {null,null,null,"23.00000"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception", "Exception"}}, + /*REAL*/ {/*SMALLINT*/ {null,null,null,"24.0"},/*INTEGER*/ {null,null,null,"24.0"},/*BIGINT*/ {null,null,null,"24.0"},/*DECIMAL(10,5)*/ {null,null,null,"24.0"},/*REAL*/ {null,null,null,null},/*DOUBLE*/ {null,null,null,"24.0"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*DOUBLE*/ {/*SMALLINT*/ {null,null,null,"25.0"},/*INTEGER*/ {null,null,null,"25.0"},/*BIGINT*/ {null,null,null,"25.0"},/*DECIMAL(10,5)*/ {null,null,null,"25.0"},/*REAL*/ {null,null,null,"25.0"},/*DOUBLE*/ {null,null,null,null},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*CHAR(60)*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {null,null,null,null},/*VARCHAR(60)*/ {null,null,null,"2.0 "},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {null,"Exception","Exception","Exception"},/*TIME*/ {null,"Exception","Exception","Excep tion"},/*TIMESTAMP*/ {null,"Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*VARCHAR(60)*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {null,null,null,"15:30:20"},/*VARCHAR(60)*/ {null,null,null,null},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {null,"Exception","Exception","Exception"},/*TIME*/ {null,"Exception","Exception","Exception"},/*TIMESTAMP*/ {null,"Exception","Exception ","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*LONG VARCHAR*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","E xception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*CHAR(60) FOR BIT DATA*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {null,null,null,null},/*VARCHAR(60) FOR BIT DATA*/ {null,null,null,"10aaaa202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","E xception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*VARCHAR(60) FOR BIT DATA*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {null,null,null,"10aaba"},/*VARCHAR(60) FOR BIT DATA*/ {null,null,null,null},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"E xception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*LONG VARCHAR FOR BIT DATA*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception"," Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*CLOB(1k)*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Excep tion","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*DATE*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {null,"Exception","Exception","Exception"},/*VARCHAR(60)*/ {null,"Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {null,null,null,null},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception", "Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*TIME*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {null,"Exception","Exception","Exception"},/*VARCHAR(60)*/ {null,"Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {null,null,null,null},/*TIMESTAMP*/ {"Exception", "Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*TIMESTAMP*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {null,"Exception","Exception","Exception"},/*VARCHAR(60)*/ {null,"Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Except ion"},/*TIMESTAMP*/ {null,null,null,null},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*BLOB(1k)*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Excep tion","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/{"Exception","Exception","Exception","Exception"} }, + }; + + // Client will error on the first row if any if DATETIME conversion exception occurs + private static String[][][] nullIfResultsClient ={ + /*SMALLINT*/ {/*SMALLINT*/ {null,null,null,null},/*INTEGER*/ {null,null,null,"2"},/*BIGINT*/ {null,null,null,"2"},/*DECIMAL(10,5)*/ {null,null,null,"2"},/*REAL*/ {null,null,null,"2"},/*DOUBLE*/ {null,null,null,"2"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*INTEGER*/ {/*SMALLINT*/ {null,null,null,"21"},/*INTEGER*/ {null,null,null,null},/*BIGINT*/ {null,null,null,"21"},/*DECIMAL(10,5)*/ {null,null,null,"21"},/*REAL*/ {null,null,null,"21"},/*DOUBLE*/ {null,null,null,"21"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*BIGINT*/ {/*SMALLINT*/ {null,null,null,"22"},/*INTEGER*/ {null,null,null,"22"},/*BIGINT*/ {null,null,null,null},/*DECIMAL(10,5)*/ {null,null,null,"22"},/*REAL*/ {null,null,null,"22"},/*DOUBLE*/ {null,null,null,"22"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*DECIMAL(10,5)*/ {/*SMALLINT*/ {null,null,null,"23.00000"},/*INTEGER*/ {null,null,null,"23.00000"},/*BIGINT*/ {null,null,null,"23.00000"},/*DECIMAL(10,5)*/ {null,null,null,null},/*REAL*/ {null,null,null,"23.00000"},/*DOUBLE*/ {null,null,null,"23.00000"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Excepti on","Exception"}}, + /*REAL*/ {/*SMALLINT*/ {null,null,null,"24.0"},/*INTEGER*/ {null,null,null,"24.0"},/*BIGINT*/ {null,null,null,"24.0"},/*DECIMAL(10,5)*/ {null,null,null,"24.0"},/*REAL*/ {null,null,null,null},/*DOUBLE*/ {null,null,null,"24.0"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*DOUBLE*/ {/*SMALLINT*/ {null,null,null,"25.0"},/*INTEGER*/ {null,null,null,"25.0"},/*BIGINT*/ {null,null,null,"25.0"},/*DECIMAL(10,5)*/ {null,null,null,"25.0"},/*REAL*/ {null,null,null,"25.0"},/*DOUBLE*/ {null,null,null,null},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*CHAR(60)*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {null,null,null,null},/*VARCHAR(60)*/ {null,null,null,"2.0 "},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception", "Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*VARCHAR(60)*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {null,null,null,"15:30:20"},/*VARCHAR(60)*/ {null,null,null,null},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exceptio n","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*LONG VARCHAR*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception ","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*CHAR(60) FOR BIT DATA*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {null,null,null,null},/*VARCHAR(60) FOR BIT DATA*/ {null,null,null,"10aaaa202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception ","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*VARCHAR(60) FOR BIT DATA*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {null,null,null,"10aaba"},/*VARCHAR(60) FOR BIT DATA*/ {null,null,null,null},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*LONG VARCHAR FOR BIT DATA*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exceptio n","Exception","Exception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*CLOB(1k)*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","E xception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*DATE*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {null,null,null,null},/*TIME*/ {"Exception","Exception","Exception","Exception"},/*TIMESTA MP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*TIME*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {null,null,null,null},/*TIMESTA MP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*TIMESTAMP*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception"," Exception","Exception"},/*TIMESTAMP*/ {null,null,null,null},/*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"}}, + /*BLOB(1k)*/ {/*SMALLINT*/ {"Exception","Exception","Exception","Exception"},/*INTEGER*/ {"Exception","Exception","Exception","Exception"},/*BIGINT*/ {"Exception","Exception","Exception","Exception"},/*DECIMAL(10,5)*/ {"Exception","Exception","Exception","Exception"},/*REAL*/ {"Exception","Exception","Exception","Exception"},/*DOUBLE*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60)*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"},/*CHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*VARCHAR(60) FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"},/*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"},/*DATE*/ {"Exception","Exception","Exception","Exception"},/*TIME*/ {"Exception","Exception","E xception","Exception"},/*TIMESTAMP*/ {"Exception","Exception","Exception","Exception"},/*BLOB(1k)*/{"Exception","Exception","Exception","Exception"} }, + }; + + + private static String[][] paramResults = { + /*SMALLINT*/ {"1","1",null,"1"}, + /*INTEGER*/ {"1","1",null,"1"}, + /*BIGINT*/ {"1","1",null,"1"}, + /*DECIMAL(10,5)*/ {"1","1",null,"1"}, + /*REAL*/ {"1.0","1.0",null,"1.0"}, + /*DOUBLE*/ {"1.0","1.0",null,"1.0"}, + /*CHAR(60)*/ {"1","1","1","1"}, + /*VARCHAR(60)*/ {"1","1","1","1"}, + /*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"}, + /*CHAR(60) FOR BIT DATA*/ {null,null,null,null}, + /*VARCHAR(60) FOR BIT DATA*/ {null,null,null,null}, + /*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"}, + /*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"}, + /*DATE*/ {"2000-01-01",null,null,"2000-01-01"}, + /*TIME*/ {"15:30:20",null,null,null}, + /*TIMESTAMP*/ {"2000-01-01 15:30:20.0",null,null,null}, + /*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"} + }; + + // The client prints "1.00000" for DECIMAL(10,5) embedded just prints "1" + private static String[][] paramResultsClient = { + /*SMALLINT*/ {"1","1",null,"1"}, + /*INTEGER*/ {"1","1",null,"1"}, + /*BIGINT*/ {"1","1",null,"1"}, + /*DECIMAL(10,5)*/ {"1.00000","1.00000",null,"1.00000"}, + /*REAL*/ {"1.0","1.0",null,"1.0"}, + /*DOUBLE*/ {"1.0","1.0",null,"1.0"}, + /*CHAR(60)*/ {"1","1","1","1"}, + /*VARCHAR(60)*/ {"1","1","1","1"}, + /*LONG VARCHAR*/ {"Exception","Exception","Exception","Exception"}, + /*CHAR(60) FOR BIT DATA*/ {null,null,null,null}, + /*VARCHAR(60) FOR BIT DATA*/ {null,null,null,null}, + /*LONG VARCHAR FOR BIT DATA*/ {"Exception","Exception","Exception","Exception"}, + /*CLOB(1k)*/ {"Exception","Exception","Exception","Exception"}, + /*DATE*/ {"2000-01-01",null,null,"2000-01-01"}, + /*TIME*/ {"15:30:20",null,null,null}, + /*TIMESTAMP*/ {"2000-01-01 15:30:20.0",null,null,null}, + /*BLOB(1k)*/ {"Exception","Exception","Exception","Exception"} + }; + + public NullIfTest(String name) { + super(name); + // TODO Auto-generated constructor stub + } + + + /** + * Test NULLIF combinations on all datatypes + * + * @throws SQLException + */ + public void testAllDatatypesCombinations() throws SQLException { + Connection conn = getConnection(); + + Statement s = conn.createStatement(); + for (int firstColumnType = 0; firstColumnType < CastingTest.SQLTypes.length; firstColumnType++) { + + StringBuffer nullIfString = new StringBuffer("SELECT NULLIF(" + + ColumnNames[firstColumnType]); + for (int secondColumnType = 0; secondColumnType < CastingTest.SQLTypes.length; secondColumnType++) { + + int row = 0; + try { + StringBuffer completeNullIfString = new StringBuffer( + nullIfString.toString() + "," + + ColumnNames[secondColumnType]); + ResultSet rs = s.executeQuery(completeNullIfString + + ") from AllDataTypesTable"); + while (rs.next()) { + String val = rs.getString(1); + if (usingDerbyNetClient()) + assertEquals( + nullIfResultsClient[firstColumnType][secondColumnType][row], + val); + else + assertEquals( + nullIfResults[firstColumnType][secondColumnType][row], + val); + row++; + } + assertTrue(CastingTest.T_147b[firstColumnType][secondColumnType]); + + } catch (SQLException e) { + for (int r = row; r < 4; r++) { + if (usingDerbyNetClient()) + assertEquals( + nullIfResultsClient[firstColumnType][secondColumnType][row], + "Exception"); + else + assertEquals( + nullIfResults[firstColumnType][secondColumnType][row], + "Exception"); + + } + if (e.getSQLState().equals("42818")) + assertFalse(CastingTest.T_147b[firstColumnType][secondColumnType]); + else + assertEquals("22007", e.getSQLState()); + } + } + + } + } + + /** + * Test NULLIF with parameter as first operand + * + * @throws SQLException + */ + public void testParameterForFirstOperandToNullIf() throws SQLException { + Connection conn = getConnection(); + + PreparedStatement ps; + for (int secondColumnType = 0; secondColumnType < CastingTest.SQLTypes.length; secondColumnType++) { + + String nullIfString = new String("SELECT NULLIF(?," + + ColumnNames[secondColumnType] + + ") from AllDataTypesTable"); + int row = 0; + try { + ps = conn.prepareStatement(nullIfString); + switch (secondColumnType) { + case 0: + case 1: + case 2: + case 3: + case 4: + case 5: + case 6: + case 7: + ps.setBoolean(1, true); + break; + case 8: // 'LONG VARCHAR' + case 11: // 'LONG VARCHAR FOR BIT DATA' + case 12: // 'CLOB' + case 16: // 'BLOB' + // Take specific case of LONG VARCHAR. Prepare of + // nullif(?,long varchar) + // fails early on because at bind time, Derby tries to set ? + // to + // long varchar. But comparison between 2 long varchars is + // not + // supported and hence bind code in + // BinaryComparisonOperatorNode fails + // Similar thing happens for CLOB, BLOB and LONG VARCHAR FOR + // BIT DATA + case 9: + case 10: + ps.setBinaryStream(1, (java.io.InputStream) null, 1); + break; + case 13:// DATE + + ps.setDate(1, Date.valueOf("2000-01-01")); + break; + case 14:// TIME + + ps.setTime(1, Time.valueOf("15:30:20")); + break; + case 15:// TIMESTAMP + + ps + .setTimestamp(1, Timestamp + .valueOf("2000-01-01 15:30:20")); + break; + default: + break; + } + ResultSet rs = ps.executeQuery(); + while (rs.next()) { + String val = rs.getString(1); + + if (usingDerbyNetClient()) + assertEquals(paramResultsClient[secondColumnType][row], + val); + else + assertEquals(paramResults[secondColumnType][row], val); + row++; + } + rs.close(); + + } catch (SQLException e) { + for (int r = row; r < 4; r++) { + if (usingDerbyNetClient()) + assertEquals(paramResultsClient[secondColumnType][row], + "Exception"); + else + assertEquals(paramResults[secondColumnType][row], + "Exception"); + } + } + } + } + + /** + * Runs the test fixtures in embedded and client. + * + * @return test suite + */ + public static Test suite() { + TestSuite suite = new TestSuite("NullIfTest"); + + suite.addTest(baseSuite("NullIfTest:embedded")); + + suite.addTest(TestConfiguration + .clientServerDecorator(baseSuite("NullIfTest:client"))); + return suite; + } + + public static Test baseSuite(String name) { + TestSuite suite = new TestSuite(name); + suite.addTestSuite(NullIfTest.class); + + return new CleanDatabaseTestSetup(suite) { + /** + * Creates the table used in the test cases. + * + */ + protected void decorateSQL(Statement s) throws SQLException { + try { + s.executeUpdate("DROP TABLE AllDataTypesTable"); + } catch (SQLException se) { + } + + StringBuffer createSQL = new StringBuffer( + "create table AllDataTypesTable ("); + for (int type = 0; type < CastingTest.SQLTypes.length - 1; type++) { + createSQL.append(ColumnNames[type] + " " + CastingTest.SQLTypes[type] + + ","); + } + createSQL.append(ColumnNames[CastingTest.SQLTypes.length - 1] + " " + + CastingTest.SQLTypes[CastingTest.SQLTypes.length - 1] + ")"); + s.executeUpdate(createSQL.toString()); + + for (int row = 0; row < SQLData[0].length; row++) { + createSQL = new StringBuffer( + "insert into AllDataTypesTable values("); + for (int type = 0; type < CastingTest.SQLTypes.length - 1; type++) { + createSQL.append(SQLData[type][row] + ","); + } + createSQL.append(SQLData[CastingTest.SQLTypes.length - 1][row] + ")"); + + s.executeUpdate(createSQL.toString()); + } + + s.close(); + } + }; + } + +} Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?view=diff&rev=518228&r1=518227&r2=518228 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Wed Mar 14 10:28:41 2007 @@ -80,6 +80,7 @@ suite.addTest(CursorTest.suite()); suite.addTest(CastingTest.suite()); suite.addTest(ScrollCursors2Test.suite()); + suite.addTest(NullIfTest.suite()); // Add the XML tests, which exist as a separate suite // so that users can "run all XML tests" easily.