timestamparith.java

derby database source code.good for you.

         "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 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();
        }
        try
        {
            return java.sql.Timestamp.valueOf( s);
        }
        catch( Exception e)
        {
            System.out.println( s + " is not a proper timestamp string.");
            System.out.println( e.getClass().getName() + ": " + e.getMessage());
            e.printStackTrace();
            System.exit(1);
            return null;
        }
    }

    private static java.sql.Date dt( String s)
    {
        return java.sql.Date.valueOf( s);
    }

    private static java.sql.Time tm( String s)
    {
        return java.sql.Time.valueOf( s);
    }

    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();
    }

    public static void main( String[] args)
    {
        System.out.println("Test timestamp arithmetic starting.");
		try
        {
            timestampArith tester = new timestampArith( args);
            tester.doIt();
            if( tester.errorCount == 0)
                System.out.println( "PASSED.");
            else if( tester.errorCount == 1)
                System.out.println( "FAILED. 1 error.");
            else
                System.out.println( "FAILED. " + tester.errorCount + " errors.");
        }
        catch( SQLException sqle)
        {
            reportSQLException( sqle);
            System.exit(1);
        }
        catch( Exception e)
        {
            System.out.println("Unexpected exception: " + e.getMessage());
            e.printStackTrace();
            System.exit(1);
        }
        System.exit(0);
    } // end of main

    String composeSqlStr( String fn, int interval, String parm1, String parm2)
    {
        return "values( {fn TIMESTAMP" + fn + "( " + intervalJdbcNames[interval] +
          ", " + parm1 + "," + parm2 + ")})";
    }
    
    private timestampArith( String[] args) throws Exception
    {
        // make the initial connection.
        ij.getPropertyArg(args);
        conn = ij.startJBMS();

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

    private void doIt() throws SQLException
    {
        for( int i = 0; i < tests.length; i++)
            tests[i].runTest();

        testNullInputs();

        for( int i = 0; i < invalid.length; i++)
        {
            try
            {
                ResultSet rs = stmt.executeQuery( invalid[i][0]);
                rs.next();
                reportFailure( "\"" + invalid[i][0] + "\" did not throw an exception.");
            }
            catch( SQLException sqle)
            {
                checkExpectedException( sqle, invalid[i][1], invalid[i][2], "\"" + invalid[i][0] + "\"");
            }
        }

        testInvalidArgTypes();
    } // end of doIt

    private void testInvalidArgTypes() throws SQLException
    {
        expectException( tsDiffPS[ HOUR_INTERVAL], ts( "2005-05-11 15:26:00"), new Double( 2.0), "XCL12",
                         "An attempt was made to put a data value of type 'double' into a data value of type 'TIMESTAMP'.",
                         "TIMESTAMPDIFF with double ts2");
        expectException( tsDiffPS[ HOUR_INTERVAL], new Double( 2.0), ts( "2005-05-11 15:26:00"), "XCL12",
                         "An attempt was made to put a data value of type 'double' into a data value of type 'TIMESTAMP'.",
                         "TIMESTAMPDIFF with double ts1");

        expectException( tsAddPS[ MINUTE_INTERVAL], new Integer(1), new Integer(-1), "XCL12",
                         "An attempt was made to put a data value of type 'int' into a data value of type 'TIMESTAMP'.",
                         "TIMESTAMPADD with int ts");
        expectException( tsAddPS[ MINUTE_INTERVAL], ts( "2005-05-11 15:26:00"), ts( "2005-05-11 15:26:00"), "XCL12",
                         "An attempt was made to put a data value of type 'java.sql.Timestamp' into a data value of type 'INTEGER'.",
                         "TIMESTAMPADD with timestamp count");
    } // end of testInvalidArgTypes

    private void expectException( PreparedStatement ps, Object arg1, Object arg2,
                                  String expectedSQLState, String expectedMsg, String label)
    {
        try
        {
            ps.setObject( 1, arg1);
            ps.setObject( 2, arg2);
            ResultSet rs = ps.executeQuery();
            rs.next();
            reportFailure( label + " did not throw an exception.");
        }
        catch( SQLException sqle) { checkExpectedException( sqle, expectedSQLState, expectedMsg, label);};
    } // end of expectException

    private void checkExpectedException( SQLException sqle, String expectedSQLState, String expectedMsg, String label)
    {
        if( ! expectedSQLState.equals( sqle.getSQLState()))
            reportFailure( "Unexpected SQLState from \"" + label + "\". expected " +
                           expectedSQLState + " got " + sqle.getSQLState());
        else if( expectedMsg != null && ! expectedMsg.equals( sqle.getMessage()))
            reportFailure( "Unexpected message from \"" + label + "\".\n  expected \"" +
                           expectedMsg + "\"\n  got \"" + sqle.getMessage() + "\"");
    } // end of checkExpectedException

    private void testNullInputs() throws SQLException
    {
        // Null inputs, each position, each type
        tsDiffPS[ HOUR_INTERVAL].setTimestamp( 1, ts( "2005-05-11 15:26:00"));
        tsDiffPS[ HOUR_INTERVAL].setNull( 2, Types.TIMESTAMP);
        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null timestamp in third argument");
        tsDiffPS[ HOUR_INTERVAL].setNull( 2, Types.DATE);
        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null date in third 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 timestamp in second argument");
        tsDiffPS[ HOUR_INTERVAL].setNull( 1, Types.DATE);
        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null date 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 integer in second argument");

        tsAddPS[ MINUTE_INTERVAL].setInt( 1, 1);
        tsAddPS[ MINUTE_INTERVAL].setNull( 2, Types.TIMESTAMP);
        expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null timestamp in third argument");
        tsAddPS[ MINUTE_INTERVAL].setNull( 2, Types.DATE);
        expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null date in third argument");
    } // end of testNullInputs

    private void expectNullResult( PreparedStatement ps, String label)
    {
        try
        {
            ResultSet rs = ps.executeQuery();
            if( ! rs.next())
                reportFailure( label + " returned no rows.");
            else if( rs.getObject( 1) != null)
                reportFailure( label + " did not return null.");
        }
        catch (SQLException sqle)
        {
            reportFailure( "Unexpected exception from " + label);
            reportSQLException( sqle);
        }
    } // end of expectNullResult

    private static void reportSQLException( SQLException sqle)
    {
        System.out.println("Unexpected exception:");
        for(;;)
        {
            System.out.println( "    " + sqle.getMessage());
            if( sqle.getNextException() != null)
                sqle = sqle.getNextException();
            else
                break;
        }
        sqle.printStackTrace();
    } // end of reportSQLException

    private void reportFailure( String msg)
    {
        errorCount++;
        System.out.println( msg);
    }

    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);