/**
 *  Derby - Class org.apache.derbyTesting.functionTests.tests.lang.TimestampArithTest
 *  
 * 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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.JDBC;

/**
 * Test the JDBC TIMESTAMPADD and TIMESTAMPDIFF escape functions.
 *
 * Things to test:
 *   + Test each interval type with timestamp, date, and time inputs.
 *   + Test diff with all 9 combinations of datetime input types (timestamp - timestamp, timestamp - date, etc).
 *   + Test PreparedStatements with parameters, '?', in each argument, and Statements. (Statements are prepared
 *     internally so we do not also have to test PrepardStatements without parameters).
 *   + Test with null inputs.
 *   + Test with input string that is convertible to timestamp.
 *   + Test with invalid interval type.
 *   + Test with invalid arguments in the date time arguments.
 *   + Test TIMESTAMPADD with an invalid type in the count argument.
 *   + Test overflow cases.
 */
public class TimestampArithTest extends BaseJDBCTestCase {
	
	/** Abstract class that factors out all the common code for the timestamps tests. */
	private static abstract class OneTest {
		final int interval; // FRAC_SECOND_INTERVAL, SECOND_INTERVAL, ... or
		final String expectedSQLState; // Null if no SQLException is expected
		final String expectedMsg; // Null if no SQLException is expected
		String sql;

		OneTest(int interval, String expectedSQLState, String expectedMsg) {
			this.interval = interval;
			this.expectedSQLState = expectedSQLState;
			this.expectedMsg = expectedMsg;
		}

		void runTest() throws SQLException {
			ResultSet rs = null;
			
			sql = composeSQL();		
			try {
				rs = stmt.executeQuery(sql);
				checkResultSet(rs, sql);
				if (expectedSQLState != null) {
					fail("Statement '" + sql + "' did not generate an exception");
				}				
			} catch (SQLException sqle) {
				if (expectedSQLState == null) {
                    fail("Unexpected exception from statement '" + sql + "'",
                         sqle);
				}
				assertSQLState("Incorrect SQLState from statement '" + sql + "'", expectedSQLState, sqle);
			} 
			if (rs != null) {
			        rs.close();
				rs = null;
			}

			try {
				rs = executePS();
				checkResultSet(rs, sql);
				if (expectedSQLState != null) {
					fail("Prepared Statement '" + sql + "' did not generate an exception");
				}
			} catch (SQLException sqle) {
				if (expectedSQLState == null) {
                    fail("Unexpected exception from prepared statement '" +
                         sql + "'", sqle);
				}
				assertSQLState("Incorrect SQLState from prepared statement '" + sql + "'", expectedSQLState, sqle);
			} 
			if (rs != null) {
                            rs.close();
                            rs = null;
			}
		} 

		private void checkResultSet(ResultSet rs, String sql) throws SQLException {
			assertTrue("'" + sql + "' did not return any rows.", rs.next());
			checkResultRow(rs, sql);
			assertFalse("'" + sql + "' returned more than one row.", rs.next());
		}

		abstract String composeSQL();
		abstract void checkResultRow(ResultSet rs, String sql) throws SQLException;
		abstract ResultSet executePS() throws SQLException;
	}
	
	private static class OneDiffTest extends OneTest {
		private final java.util.Date ts1;
		private final java.util.Date ts2;
		private final int expectedDiff;
		protected boolean expectNull;
		
		OneDiffTest(int interval, java.util.Date ts1, java.util.Date ts2,
				int expectedDiff, String expectedSQLState, String expectedMsg) {
			super(interval, expectedSQLState, expectedMsg);
			this.ts1 = ts1;
			this.ts2 = ts2;
			this.expectedDiff = expectedDiff;
			expectNull = (ts1 == null) || (ts2 == null);
		}

		void checkResultRow(ResultSet rs, String sql) throws SQLException {
			int actualDiff = rs.getInt(1);
			assertFalse("Unexpected null result from '" + sql + "'.", rs.wasNull() && !expectNull);
			assertFalse("Expected null result from '" + sql + "'.", !rs.wasNull() && expectNull);
			assertEquals("Unexpected result from '" + sql + "'.", expectedDiff, actualDiff);			
		}

		String composeSQL() {
			return composeSqlStr("DIFF", interval, dateTimeToLiteral(ts1),
					dateTimeToLiteral(ts2));
		}

		ResultSet executePS() throws SQLException {
			setDateTime(tsDiffPS[interval], 1, ts1);
			setDateTime(tsDiffPS[interval], 2, ts2);
			return tsDiffPS[interval].executeQuery();
		}		
	}
	
	private static class OneStringDiffTest extends OneDiffTest {
		private final String ts1;
		private final String ts2;
		
		OneStringDiffTest(int interval, String ts1, String ts2,
				int expectedDiff, String expectedSQLState, String expectedMsg) {
			super(interval, (java.util.Date) null, (java.util.Date) null,
					expectedDiff, expectedSQLState, expectedMsg);
			this.ts1 = ts1;
			this.ts2 = ts2;
			expectNull = (ts1 == null) || (ts2 == null);
		}
		
		String composeSQL() {
			return composeSqlStr("DIFF", interval, dateTimeToLiteral(ts1),
					dateTimeToLiteral(ts2));
		}
		
		ResultSet executePS() throws SQLException {
			tsDiffPS[interval].setString(1, ts1);
			tsDiffPS[interval].setString(2, ts2);
			return tsDiffPS[interval].executeQuery();
		}		
	}
	
	private static class OneAddTest extends OneTest {
		private final java.util.Date ts;
		final int count;
		final java.sql.Timestamp expected;
		
		OneAddTest(int interval, int count, java.util.Date ts,
				java.sql.Timestamp expected, String expectedSQLState,
				String expectedMsg) {
			super(interval, expectedSQLState, expectedMsg);
			this.count = count;
			this.ts = ts;
			this.expected = expected;
		}
		
		String composeSQL() {
			return composeSqlStr("ADD", interval, String.valueOf(count),
					dateTimeToLiteral(ts));
		}

		void checkResultRow(ResultSet rs, String sql) throws SQLException {
			java.sql.Timestamp actual = rs.getTimestamp(1);
			assertFalse("Unexpected null result from '" + sql + "'.", (rs.wasNull() || actual == null) && expected != null);
			assertFalse("Expected null result from '" + sql + "'.", !(rs.wasNull() || actual == null) && expected == null);
			assertEquals("Unexpected result from '" + sql + "'.", expected, actual);
		}

		ResultSet executePS() throws SQLException {
			tsAddPS[interval].setInt(1, count);
			setDateTime(tsAddPS[interval], 2, ts);
			return tsAddPS[interval].executeQuery();
		}		
	}
	
	private static class OneStringAddTest extends OneAddTest {
		private final String ts;

		OneStringAddTest(int interval, int count, String ts,
				java.sql.Timestamp expected, String expectedSQLState,
				String expectedMsg) {
			super(interval, count, (java.util.Date) null, expected,
					expectedSQLState, expectedMsg);
			this.ts = ts;
		}

		String composeSQL() {
			return composeSqlStr("ADD", interval, String.valueOf(count),
					dateTimeToLiteral(ts));
		}

		ResultSet executePS() throws SQLException {
			tsAddPS[interval].setInt(1, count);
			tsAddPS[interval].setString(2, ts);
			return tsAddPS[interval].executeQuery();
		}		
	}
	
    private static final int FRAC_SECOND_INTERVAL = 0;
    private static final int SECOND_INTERVAL = 1;
    private static final int MINUTE_INTERVAL = 2;
    private static final int HOUR_INTERVAL = 3;
    private static final int DAY_INTERVAL = 4;
    private static final int WEEK_INTERVAL = 5;
    private static final int MONTH_INTERVAL = 6;
    private static final int QUARTER_INTERVAL = 7;
    private static final int YEAR_INTERVAL = 8;

	/** timestamp - timestamp */
	private static final OneDiffTest[] diffBetweenTsTests = {
			new OneDiffTest(FRAC_SECOND_INTERVAL, ts("2005-05-10 08:25:00"), ts("2005-05-10 08:25:00.000001"), 1000, null, null),
			new OneDiffTest(SECOND_INTERVAL, ts("2005-05-10 08:25:01"), ts("2005-05-10 08:25:00"), -1, null, null),
			new OneDiffTest(SECOND_INTERVAL, ts("2005-05-10 08:25:00.1"), ts("2005-05-10 08:25:00"), 0, null, null),
			new OneDiffTest(SECOND_INTERVAL, ts("2005-05-10 08:25:00"), ts("2005-05-10 08:26:00"), 60, null, null),
			new OneDiffTest(MINUTE_INTERVAL, ts("2005-05-11 08:25:00"), ts("2005-05-10 08:25:00"), -24 * 60, null, null),
			new OneDiffTest(HOUR_INTERVAL, ts("2005-05-10 08:25:00"), ts("2005-05-11 08:25:00"), 24, null, null),
			new OneDiffTest(DAY_INTERVAL, ts("2005-05-10 08:25:00"), ts("2005-05-11 08:25:00"), 1, null, null),
			new OneDiffTest(DAY_INTERVAL, ts("2005-05-10 08:25:01"), ts("2005-05-11 08:25:00"), 0, null, null),
			new OneDiffTest(WEEK_INTERVAL, ts("2005-02-23 08:25:00"), ts("2005-03-01 08:25:00"), 0, null, null),
			new OneDiffTest(MONTH_INTERVAL, ts("2005-02-23 08:25:00"), ts("2005-03-23 08:25:00"), 1, null, null),
			new OneDiffTest(MONTH_INTERVAL, ts("2005-02-23 08:25:01"), ts("2005-03-23 08:25:00"), 0, null, null),
			new OneDiffTest(QUARTER_INTERVAL, ts("2005-02-23 08:25:00"), ts("2005-05-23 08:25:00"), 1, null, null),
			new OneDiffTest(QUARTER_INTERVAL, ts("2005-02-23 08:25:01"), ts("2005-05-23 08:25:00"), 0, null, null),
			new OneDiffTest(YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts("2005-05-23 08:25:00"), 0, null, null),
			new OneDiffTest(YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts("2006-02-23 08:25:00"), 1, null, null)			
	};
	
	/** timestamp - date */
	private static final OneDiffTest[] diffBetweenTsAndDateTests = {
			new OneDiffTest(FRAC_SECOND_INTERVAL, ts("2004-05-10 00:00:00.123456"), dt("2004-05-10"), -123456000, null, null),
			new OneDiffTest(SECOND_INTERVAL, ts("2004-05-10 08:25:01"), dt("2004-05-10"), -(1 + 60 * (25 + 60 * 8)), null, null),
			new OneDiffTest(MINUTE_INTERVAL, ts("2004-05-11 08:25:00"), dt("2004-05-10"), -(24 * 60 + 8 * 60 + 25), null, null),
			new OneDiffTest(HOUR_INTERVAL, ts("2004-02-28 08:25:00"), dt("2004-03-01"), 39, null, null),
			new OneDiffTest(DAY_INTERVAL, ts("2004-05-10 08:25:00"), dt("2004-05-11"), 0, null, null),
			new OneDiffTest(WEEK_INTERVAL, ts("2004-02-23 00:00:00"), dt("2004-03-01"), 1, null, null),
			new OneDiffTest(MONTH_INTERVAL, ts("2004-02-23 08:25:00"), dt("2004-03-24"), 1, null, null),
			new OneDiffTest(QUARTER_INTERVAL, ts("2004-02-23 08:25:00"), dt("2004-05-24"), 1, null, null),
			new OneDiffTest(YEAR_INTERVAL, ts("2004-02-23 08:25:00"), dt("2004-05-23"), 0, null, null)
	};
	
	/** date - timestamp */
	private static final OneDiffTest[] diffBetweenDateAndTsTests = {
			new OneDiffTest(FRAC_SECOND_INTERVAL, dt("2004-05-10"), ts("2004-05-10 00:00:00.123456"), 123456000, null, null),
			new OneDiffTest(SECOND_INTERVAL, dt("2004-05-10"), ts("2004-05-09 23:59:00"), -60, null, null),
			new OneDiffTest(MINUTE_INTERVAL, dt("2004-05-10"), ts("2004-05-11 08:25:00"), 24 * 60 + 8 * 60 + 25, null, null),
			new OneDiffTest(HOUR_INTERVAL, dt("2005-03-01"), ts("2005-02-28 08:25:00"), -15, null, null),
			new OneDiffTest(DAY_INTERVAL, dt("2004-05-10"), ts("2004-05-11 08:25:00"), 1, null, null),
			new OneDiffTest(WEEK_INTERVAL, dt("2004-03-01"), ts("2004-02-23 00:00:00"), -1, null, null),
			new OneDiffTest(MONTH_INTERVAL, dt("2005-03-24"), ts("2004-02-23 08:25:00"), -13, null, null),
			new OneDiffTest(QUARTER_INTERVAL, dt("2004-05-23"), ts("2004-02-23 08:25:01"), 0, null, null),
			new OneDiffTest(YEAR_INTERVAL, dt("2004-05-23"), ts("2003-02-23 08:25:00"), -1, null, null)
	};
	
	/** timestamp + timestamp */
	private static final OneAddTest[] addBetweenTsTests = {
			new OneAddTest(FRAC_SECOND_INTERVAL, 1000, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:55:00.000001"), null, null),
			new OneAddTest(SECOND_INTERVAL, 60, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:56:00"), null, null),
			new OneAddTest(MINUTE_INTERVAL, -1, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:54:00"), null, null),
			new OneAddTest(HOUR_INTERVAL, 2, ts("2005-05-11 15:55:00"), ts("2005-05-11 17:55:00"), null, null),
			new OneAddTest(DAY_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-12 15:55:00"), null, null),
			new OneAddTest(WEEK_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-18 15:55:00"), null, null),
			new OneAddTest(MONTH_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-06-11 15:55:00"), null, null),
			new OneAddTest(QUARTER_INTERVAL, 1, ts("2005-10-11 15:55:00"), ts("2006-01-11 15:55:00"), null, null),
			new OneAddTest(YEAR_INTERVAL, -10, ts("2005-10-11 15:55:00"), ts("1995-10-11 15:55:00"), null, null)
	}; 
	
	/** date + timestamp */
	private static final OneAddTest[] addBetweenDateAndTsTests = {
			// following gives an error with J2ME j9_foundation 1.1 (DERBY-2225):
			new OneAddTest(FRAC_SECOND_INTERVAL, -1000, dt("2005-05-11"), ts("2005-05-10 23:59:59.999999"), null, null),
			new OneAddTest(SECOND_INTERVAL, 60, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
			new OneAddTest(MINUTE_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
			new OneAddTest(HOUR_INTERVAL, -2, dt("2005-05-11"), ts("2005-05-10 22:00:00"), null, null),
			new OneAddTest(DAY_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-12 00:00:00"), null, null),
			new OneAddTest(WEEK_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-18 00:00:00"), null, null),
			new OneAddTest(MONTH_INTERVAL, -1, dt("2005-03-29"), ts("2005-02-28 00:00:00"), null, null),
			new OneAddTest(QUARTER_INTERVAL, -2, dt("2005-05-05"), ts("2004-11-05 00:00:00"), null, null),
			new OneAddTest(YEAR_INTERVAL, 2, dt("2005-05-05"), ts("2007-05-05 00:00:00"), null, null)			
	};
	
	private static final OneStringDiffTest[] diffBetweenStringTests = {
			new OneStringDiffTest(SECOND_INTERVAL, "2005-05-10 08:25:00", "2005-05-10 08:26:00", 60, null, null)
	};
	
	private static final OneStringAddTest[] addBetweenStringTests = {
			new OneStringAddTest(DAY_INTERVAL, 1, "2005-05-11 15:55:00", ts("2005-05-12 15:55:00"), null, null)		
	};
	
	/** check overflow conditions */
	private static final OneTest[] overflowTests = {
			new OneDiffTest(FRAC_SECOND_INTERVAL, ts("2004-05-10 00:00:00.123456"), ts("2004-05-10 00:00:10.123456"), 0, "22003",
					"The resulting value is outside the range for the data type INTEGER."),
			new OneDiffTest(FRAC_SECOND_INTERVAL, ts("2004-05-10 00:00:00.123456"), ts("2005-05-10 00:00:00.123456"), 0, "22003",
					"The resulting value is outside the range for the data type INTEGER."),
			new OneDiffTest(SECOND_INTERVAL, ts("1904-05-10 00:00:00"),	ts("2205-05-10 00:00:00"), 0, "22003",
					"The resulting value is outside the range for the data type INTEGER."),
			new OneAddTest(YEAR_INTERVAL, 99999, ts("2004-05-10 00:00:00.123456"), null, "22003",
					"The resulting value is outside the range for the data type TIMESTAMP.") 			
	};
	
    private static final String[][] invalid = {
        {"values( {fn TIMESTAMPDIFF( SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
         "Syntax error: Encountered \"SECOND\" at line 1, column 28."},
        {"values( {fn TIMESTAMPDIFF( , CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
         "Syntax error: Encountered \",\" at line 1, column 28."},
        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 5)})", "42X01",
         "Syntax error: Encountered \",\" at line 1, column 80."},
        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, 'x')})", "42X45",
         "CHAR is an invalid type for argument number 3 of TIMESTAMPDIFF."},
        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, 'x', CURRENT_TIMESTAMP)})", "42X45",
         "CHAR is an invalid type for argument number 2 of TIMESTAMPDIFF."},
        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP)})", "42X01",
         "Syntax error: Encountered \")\" at line 1, column 61."},
        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND)})", "42X01",
         "Syntax error: Encountered \")\" at line 1, column 42."},
        {"values( {fn TIMESTAMPADD( x, 1, CURRENT_TIMESTAMP)})", "42X01",
           "Syntax error: Encountered \"x\" at line 1, column 27."},
        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, CURRENT_DATE, CURRENT_TIMESTAMP)})", "42X45",
           "DATE is an invalid type for argument number 2 of TIMESTAMPADD."},
        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 'XX', CURRENT_TIMESTAMP)})", "42X45",
           "CHAR is an invalid type for argument number 2 of TIMESTAMPADD."},
        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1.1, CURRENT_TIMESTAMP)})", "42X45",
           "DECIMAL is an invalid type for argument number 2 of TIMESTAMPADD."},
        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 2.1)})", "42X45",
           "DECIMAL is an invalid type for argument number 3 of TIMESTAMPADD."},
        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 'XX')})", "42X45",
           "CHAR is an invalid type for argument number 3 of TIMESTAMPADD."},
        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1)})", "42X01",
           "Syntax error: Encountered \")\" at line 1, column 44."},
        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND)})", "42X01",
           "Syntax error: Encountered \")\" at line 1, column 41."}
    };

	private static final String[] intervalJdbcNames = { "SQL_TSI_FRAC_SECOND",
		"SQL_TSI_SECOND", "SQL_TSI_MINUTE", "SQL_TSI_HOUR", "SQL_TSI_DAY",
		"SQL_TSI_WEEK", "SQL_TSI_MONTH", "SQL_TSI_QUARTER", "SQL_TSI_YEAR" };
    
    private static Statement stmt;
	private static PreparedStatement[] tsAddPS = new PreparedStatement[intervalJdbcNames.length];
	private static PreparedStatement[] tsDiffPS = new PreparedStatement[intervalJdbcNames.length];

	/**
	 * Basic constructor.
	 */
	public TimestampArithTest(String name) {
		super(name);
	}

	protected void initializeConnection(Connection conn) throws SQLException {
		conn.setAutoCommit(false);		
	}
	
	public static Test suite() {
        return new BaseJDBCTestSetup(
            new BaseTestSuite(
                TimestampArithTest.class, "TimestampArithTest")) {

			protected void setUp() throws Exception {
				super.setUp();

				for (int i = 0; i < intervalJdbcNames.length; i++) {
					tsAddPS[i] = getConnection().prepareStatement(
							composeSqlStr("ADD", i, "?", "?"));
					tsDiffPS[i] = getConnection().prepareStatement(
							composeSqlStr("DIFF", i, "?", "?"));
				}

				stmt = getConnection().createStatement();
			}

            protected void tearDown() throws Exception {
                closeAll(tsAddPS);
                tsAddPS = null;
                closeAll(tsDiffPS);
                tsDiffPS = null;
                stmt.close();
                stmt = null;
                super.tearDown();
            }
		};
	}

    /** Close all statements in an array. */
    private static void closeAll(Statement[] statements) throws SQLException {
        for (int i = 0; i < statements.length; i++) {
            statements[i].close();
        }
    }
	
	public void testDiffBetweenTimestamp() throws SQLException {
		getConnection();
		
		for (int i = 0; i < diffBetweenTsTests.length; i++) {
			diffBetweenTsTests[i].runTest();
		}
	}
	
	public void testDiffBetweenTimestampAndDate() throws SQLException {
		for (int i = 0; i < diffBetweenTsAndDateTests.length; i++) {
			diffBetweenTsAndDateTests[i].runTest();
		}		
	}
	
	public void testDiffBetweenDateAndTimestamp() throws SQLException {
		for (int i = 0; i < diffBetweenDateAndTsTests.length; i++) {
			diffBetweenDateAndTsTests[i].runTest();
		}				
	}
	
	public void testAddBetweenTimestamp() throws SQLException {
		for (int i = 0; i < addBetweenTsTests.length; i++) {
			addBetweenTsTests[i].runTest();
		}						
	}
	
	public void testAddBetweenDateAndTimestamps() throws SQLException {
		for (int i = 0; i < addBetweenDateAndTsTests.length; i++) {
			addBetweenDateAndTsTests[i].runTest();
		}								
	}
	
	public void testDiffBetweenString() throws SQLException {
		for (int i = 0; i < diffBetweenStringTests.length; i++) {
			diffBetweenStringTests[i].runTest();
		}										
	}
	
	public void testAddBetweenString() throws SQLException {
		for (int i = 0; i < addBetweenStringTests.length; i++) {
			addBetweenStringTests[i].runTest();
		}												
	}
	
	public void testOverflow() throws SQLException {
		for (int i = 0; i < overflowTests.length; i++) {
			overflowTests[i].runTest();
		}														
	}

	/**
	 * Tests null inputs, each position, each type.
	 * 
	 * @throws SQLException
	 */
	public void testNullInputs() throws SQLException {		
        tsDiffPS[HOUR_INTERVAL].setTimestamp(1, ts( "2005-05-11 15:26:00"));
        tsDiffPS[HOUR_INTERVAL].setNull(2, Types.TIMESTAMP);

        // TIMESTAMPDIFF with null timestamp in third argument
        expectNullResult(tsDiffPS[HOUR_INTERVAL]);

        // TIMESTAMPDIFF with null date in third argument
        tsDiffPS[HOUR_INTERVAL].setNull(2, Types.DATE);
        expectNullResult(tsDiffPS[HOUR_INTERVAL]);

        // TIMESTAMPDIFF with null timestamp in second argument
        tsDiffPS[HOUR_INTERVAL].setTimestamp(2, ts( "2005-05-11 15:26:00"));
        tsDiffPS[HOUR_INTERVAL].setNull(1, Types.TIMESTAMP);
        expectNullResult(tsDiffPS[HOUR_INTERVAL]);

        // TIMESTAMPDIFF with null date in second argument
        tsDiffPS[HOUR_INTERVAL].setNull(1, Types.DATE);
        expectNullResult(tsDiffPS[HOUR_INTERVAL]);

        // TIMESTAMPADD with null integer in second argument
        tsAddPS[MINUTE_INTERVAL].setTimestamp(2, ts( "2005-05-11 15:26:00"));
        tsAddPS[MINUTE_INTERVAL].setNull(1, Types.INTEGER);
        expectNullResult(tsAddPS[MINUTE_INTERVAL]);

        // TIMESTAMPADD with null timestamp in third argument
        tsAddPS[MINUTE_INTERVAL].setInt(1, 1);
        tsAddPS[MINUTE_INTERVAL].setNull(2, Types.TIMESTAMP);
        expectNullResult(tsAddPS[MINUTE_INTERVAL]);

        // TIMESTAMPADD with null date in third argument
        tsAddPS[MINUTE_INTERVAL].setNull(2, Types.DATE);
        expectNullResult(tsAddPS[MINUTE_INTERVAL]);
	}

	public void testInvalidLengths() throws SQLException {
		ResultSet rs;
		
		for (int i = 0; i < invalid.length; i++) {			
			try {
				rs = stmt.executeQuery(invalid[i][0]);
				rs.next();
				fail(invalid[i][0] + " did not throw an exception.");
			} catch (SQLException sqle) {
				assertSQLState("Unexpected SQLState from " + invalid[i][0], invalid[i][1], sqle);
			}
		}
	}
	
	public void testInvalidArgTypes() throws SQLException {
        expectException( tsDiffPS[ HOUR_INTERVAL], ts( "2005-05-21 15:26:00"), 2.0, "XCL12",
                "TIMESTAMPDIFF with double ts2");
        expectException( tsDiffPS[ HOUR_INTERVAL], 2.0, ts( "2005-05-11 15:26:00"), "XCL12",
                "TIMESTAMPDIFF with double ts1");
        expectException( tsAddPS[ MINUTE_INTERVAL], 1, -1, "XCL12",
                "TIMESTAMPADD with int ts");
        expectException( tsAddPS[ MINUTE_INTERVAL], ts( "2005-05-11 15:26:00"), ts( "2005-05-11 15:26:00"), "XCL12",
                "TIMESTAMPADD with timestamp count");		
	}
	
	private static void expectException(PreparedStatement ps, Object obj1, Object obj2, 
			String expectedSQLState, String label) {
		 ResultSet rs;
		
        try {
        	ps.setObject(1, obj1);
			ps.setObject(2, obj2);
			rs = ps.executeQuery();
			rs.next();
			fail(label + " did not throw an exception.");			
		} catch (SQLException sqle) {
			assertSQLState("Unexpected SQLState from " + label, expectedSQLState, sqle);
		}
	}

    private static void expectNullResult(PreparedStatement ps)
            throws SQLException {
        JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
    }

	private static String dateTimeToLiteral(Object ts) {
		if (ts instanceof java.sql.Timestamp)
			return "{ts '" + ((java.sql.Timestamp) ts).toString() + "'}";
		else if (ts instanceof java.sql.Time)
			return "{t '" + ((java.sql.Time) ts).toString() + "'}";
		else if (ts instanceof java.sql.Date)
			return "{d '" + ((java.sql.Date) ts).toString() + "'}";
		else if (ts instanceof String)
			return "TIMESTAMP( '" + ((String) ts) + "')";
		else
			return ts.toString();
	}
	
	private static String composeSqlStr(String fn, int interval, String parm1, String parm2) {
		return "values( {fn TIMESTAMP" + fn + "( "
				+ intervalJdbcNames[interval] + ", " + parm1 + "," + parm2
				+ ")})";
	}
	
	private static void setDateTime(PreparedStatement ps, int parameterIdx,
			java.util.Date dateTime) throws SQLException {
		if (dateTime instanceof java.sql.Timestamp)
			ps.setTimestamp(parameterIdx, (java.sql.Timestamp) dateTime);
		else if (dateTime instanceof java.sql.Date)
			ps.setDate(parameterIdx, (java.sql.Date) dateTime);
		else if (dateTime instanceof java.sql.Time)
			ps.setTime(parameterIdx, (java.sql.Time) dateTime);
		else
			ps.setTimestamp(parameterIdx, (java.sql.Timestamp) dateTime);
	}
	
	private static java.sql.Timestamp ts(String s) {
		// Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff
		if (s.length() < 29) {
			// Pad out the fraction with zeros
			StringBuffer sb = new StringBuffer(s);
			if (s.length() == 19)
				sb.append('.');
			while (sb.length() < 29)
				sb.append('0');
			s = sb.toString();
		}
        return java.sql.Timestamp.valueOf(s);
	}
	
	private static java.sql.Date dt(String s) {
		return java.sql.Date.valueOf(s);
	}
}