Examples with ClientResponse org.voltdb.client.ClientResponse used on opensource projects

Example 56 with ClientResponse

use of org.voltdb.client.ClientResponse in project voltdb by VoltDB.

the class TestFunctionsForVoltDBSuite method testTO_TIMESTAMP.

public void testTO_TIMESTAMP() throws NoConnectionsException, IOException, ProcCallException {
    System.out.println("STARTING TO_TIMESTAMP");
    Client client = getClient();
    ClientResponse cr;
    VoltTable result;
    cr = client.callProcedure("P2.insert", 0, new Timestamp(0L));
    cr = client.callProcedure("P2.insert", 1, new Timestamp(1L));
    cr = client.callProcedure("P2.insert", 2, new Timestamp(1000L));
    cr = client.callProcedure("P2.insert", 3, new Timestamp(-1000L));
    // Test AdHoc
    cr = client.callProcedure("@AdHoc", "select to_timestamp(second, 1372640523) from P2 limit 1");
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals(1372640523 * 1000000L, result.getTimestampAsLong(0));
    // Test string input number, expect error
    try {
        cr = client.callProcedure("@AdHoc", "select to_timestamp(second, '1372640523') from P2 limit 1");
        fail();
    } catch (Exception ex) {
        assertTrue(ex.getMessage().contains("PlanningErrorException"));
        assertTrue(ex.getMessage().contains("incompatible data type"));
    }
    String[] procedures = { "FROM_UNIXTIME", "TO_TIMESTAMP_SECOND", "TO_TIMESTAMP_MILLIS", "TO_TIMESTAMP_MILLISECOND", "TO_TIMESTAMP_MICROS", "TO_TIMESTAMP_MICROSECOND" };
    for (int i = 0; i < procedures.length; i++) {
        String proc = procedures[i];
        cr = client.callProcedure(proc, 0L, 0);
        assertEquals(ClientResponse.SUCCESS, cr.getStatus());
        result = cr.getResults()[0];
        assertEquals(1, result.getRowCount());
        assertTrue(result.advanceRow());
        if (proc == "TO_TIMESTAMP_SECOND" || proc == "FROM_UNIXTIME") {
            assertEquals(0L, result.getTimestampAsLong(0));
        } else if (proc == "TO_TIMESTAMP_MILLIS" || proc == "TO_TIMESTAMP_MILLISECOND") {
            assertEquals(0L, result.getTimestampAsLong(0));
        } else if (proc == "TO_TIMESTAMP_MICROS" || proc == "TO_TIMESTAMP_MICROSECOND") {
            assertEquals(0L, result.getTimestampAsLong(0));
        } else {
            fail();
        }
        cr = client.callProcedure(proc, 1L, 1);
        assertEquals(ClientResponse.SUCCESS, cr.getStatus());
        result = cr.getResults()[0];
        assertEquals(1, result.getRowCount());
        assertTrue(result.advanceRow());
        if (proc == "TO_TIMESTAMP_SECOND" || proc == "FROM_UNIXTIME") {
            assertEquals(1000000L, result.getTimestampAsLong(0));
        } else if (proc == "TO_TIMESTAMP_MILLIS" || proc == "TO_TIMESTAMP_MILLISECOND") {
            assertEquals(1000L, result.getTimestampAsLong(0));
        } else if (proc == "TO_TIMESTAMP_MICROS" || proc == "TO_TIMESTAMP_MICROSECOND") {
            assertEquals(1L, result.getTimestampAsLong(0));
        } else {
            fail();
        }
        cr = client.callProcedure(proc, 1000L, 1);
        assertEquals(ClientResponse.SUCCESS, cr.getStatus());
        result = cr.getResults()[0];
        assertEquals(1, result.getRowCount());
        assertTrue(result.advanceRow());
        if (proc == "TO_TIMESTAMP_SECOND" || proc == "FROM_UNIXTIME") {
            assertEquals(1000000000L, result.getTimestampAsLong(0));
        } else if (proc == "TO_TIMESTAMP_MILLIS" || proc == "TO_TIMESTAMP_MILLISECOND") {
            assertEquals(1000000L, result.getTimestampAsLong(0));
        } else if (proc == "TO_TIMESTAMP_MICROS" || proc == "TO_TIMESTAMP_MICROSECOND") {
            assertEquals(1000L, result.getTimestampAsLong(0));
        } else {
            fail();
        }
        cr = client.callProcedure(proc, -1000, 1);
        assertEquals(ClientResponse.SUCCESS, cr.getStatus());
        result = cr.getResults()[0];
        assertEquals(1, result.getRowCount());
        assertTrue(result.advanceRow());
        if (proc == "TO_TIMESTAMP_SECOND" || proc == "FROM_UNIXTIME") {
            assertEquals(-1000000000L, result.getTimestampAsLong(0));
        } else if (proc == "TO_TIMESTAMP_MILLIS" || proc == "TO_TIMESTAMP_MILLISECOND") {
            assertEquals(-1000000L, result.getTimestampAsLong(0));
        } else if (proc == "TO_TIMESTAMP_MICROS" || proc == "TO_TIMESTAMP_MICROSECOND") {
            assertEquals(-1000L, result.getTimestampAsLong(0));
        } else {
            fail();
        }
        final long maxSec = GREGORIAN_EPOCH / 1000000;
        cr = client.callProcedure(proc, maxSec, 1);
        assertEquals(ClientResponse.SUCCESS, cr.getStatus());
        result = cr.getResults()[0];
        assertEquals(1, result.getRowCount());
        assertTrue(result.advanceRow());
        if (proc == "TO_TIMESTAMP_SECOND" || proc == "FROM_UNIXTIME") {
            assertEquals(maxSec * 1000000, result.getTimestampAsLong(0));
        } else if (proc == "TO_TIMESTAMP_MILLIS" || proc == "TO_TIMESTAMP_MILLISECOND") {
            assertEquals(maxSec * 1000, result.getTimestampAsLong(0));
        } else if (proc == "TO_TIMESTAMP_MICROS" || proc == "TO_TIMESTAMP_MICROSECOND") {
            assertEquals(maxSec, result.getTimestampAsLong(0));
        } else {
            fail();
        }
    }
}

Example 57 with ClientResponse

use of org.voltdb.client.ClientResponse in project voltdb by VoltDB.

the class TestFunctionsForVoltDBSuite method testDateadd.

public void testDateadd() throws NoConnectionsException, IOException, ProcCallException {
    System.out.println("STARTING test DATEADD function tests");
    /*
         *      "CREATE TABLE P2 ( " +
                "ID INTEGER DEFAULT '0' NOT NULL, " +
                "TM TIMESTAMP DEFAULT NULL, " +
                "PRIMARY KEY (ID) ); " +
                "PARTITION TABLE P2 ON COLUMN ID;\n" +
         */
    Client client = getClient();
    ClientResponse cr = null;
    VoltTable vt = null;
    cr = client.callProcedure("@AdHoc", "INSERT INTO P2 (ID, TM) VALUES (10000, '2000-01-01 01:00:00.000000');");
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(year, 1, TM) FROM P2 WHERE ID = 10000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2001-01-01 01:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(quarter, 1, TM) FROM P2 WHERE ID = 10000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-04-01 01:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(month, 1, TM) FROM P2 WHERE ID = 10000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-02-01 01:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(day, 1, TM) FROM P2 WHERE ID = 10000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(vt.getTimestampAsSqlTimestamp(0), Timestamp.valueOf("2000-01-02 01:00:00.000000"));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(hour, 1, TM) FROM P2 WHERE ID = 10000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-01-01 02:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(minute, 1, TM) FROM P2 WHERE ID = 10000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-01-01 01:01:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(second, 1, TM) FROM P2 WHERE ID = 10000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-01-01 01:00:01.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(millisecond, 1, TM) FROM P2 WHERE ID = 10000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-01-01 01:00:00.001000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(millis, 1, TM) FROM P2 WHERE ID = 10000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-01-01 01:00:00.001000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(microsecond, 1, TM) FROM P2 WHERE ID = 10000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-01-01 01:00:00.000001"), vt.getTimestampAsSqlTimestamp(0));
    cr = client.callProcedure("@AdHoc", "INSERT INTO P2 (ID, TM) VALUES (20000, '2007-01-01 13:10:10.111111');");
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(millisecond, 1, TM) FROM P2 WHERE ID = 20000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2007-01-01 13:10:10.112111"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(millisecond, 2, TM) FROM P2 WHERE ID = 20000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2007-01-01 13:10:10.113111"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(microsecond, 1, TM) FROM P2 WHERE ID = 20000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2007-01-01 13:10:10.111112"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(microsecond, 2, TM) FROM P2 WHERE ID = 20000").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2007-01-01 13:10:10.111113"), vt.getTimestampAsSqlTimestamp(0));
    cr = client.callProcedure("@AdHoc", "INSERT INTO P2 (ID, TM) VALUES (20001, '2007-01-01 01:01:01.111111');");
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(quarter, 4, TM) FROM P2 WHERE ID = 20001").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2008-01-01 01:01:01.111111"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(month, 13, TM) FROM P2 WHERE ID = 20001").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2008-02-01 01:01:01.111111"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(day, 365, TM) FROM P2 WHERE ID = 20001").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2008-01-01 01:01:01.111111"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(hour, 23, TM) FROM P2 WHERE ID = 20001").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2007-01-02 00:01:01.111111"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(minute, 59, TM) FROM P2 WHERE ID = 20001").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2007-01-01 02:00:01.111111"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(second, 59, TM) FROM P2 WHERE ID = 20001").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2007-01-01 01:02:00.111111"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(millisecond, 59, TM) FROM P2 WHERE ID = 20001").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2007-01-01 01:01:01.170111"), vt.getTimestampAsSqlTimestamp(0));
    cr = client.callProcedure("@AdHoc", "INSERT INTO P2 (ID, TM) VALUES (20002, '2000-01-01 00:00:00.000000');");
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(year, -1, TM) FROM P2 WHERE ID = 20002").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("1999-01-01 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(quarter, -1, TM) FROM P2 WHERE ID = 20002").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("1999-10-01 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(month, -1, TM) FROM P2 WHERE ID = 20002").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("1999-12-01 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(day, -1, TM) FROM P2 WHERE ID = 20002").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("1999-12-31 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(hour, -1, TM) FROM P2 WHERE ID = 20002").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("1999-12-31 23:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(minute, -1, TM) FROM P2 WHERE ID = 20002").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("1999-12-31 23:59:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(second, -1, TM) FROM P2 WHERE ID = 20002").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("1999-12-31 23:59:59.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(millisecond, -1, TM) FROM P2 WHERE ID = 20002").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("1999-12-31 23:59:59.999000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(microsecond, -1, TM) FROM P2 WHERE ID = 20002").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("1999-12-31 23:59:59.999999"), vt.getTimestampAsSqlTimestamp(0));
    //leap year test case
    cr = client.callProcedure("@AdHoc", "INSERT INTO P2 (ID, TM) VALUES (20003, '2000-02-29 00:00:00.000000');");
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(year, 1, TM) FROM P2 WHERE ID = 20003").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2001-02-28 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    cr = client.callProcedure("@AdHoc", "INSERT INTO P2 (ID, TM) VALUES (20004, '2000-01-31 00:00:00.000000');");
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(month, 1, TM) FROM P2 WHERE ID = 20004").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-02-29 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(day, 31, TM) FROM P2 WHERE ID = 20004").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-03-02 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    cr = client.callProcedure("@AdHoc", "INSERT INTO P2 (ID, TM) VALUES (20005, '1999-12-31 00:00:00.000000');");
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(year, 1, TM) FROM P2 WHERE ID = 20005").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-12-31 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(quarter, 2, TM) FROM P2 WHERE ID = 20005").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-06-30 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(month, 2, TM) FROM P2 WHERE ID = 20005").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-02-29 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(day, 60, TM) FROM P2 WHERE ID = 20005").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-02-29 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(hour, 1440, TM) FROM P2 WHERE ID = 20005").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-02-29 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(minute, 86400, TM) FROM P2 WHERE ID = 20005").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-02-29 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(second, 5184000, TM) FROM P2 WHERE ID = 20005").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-02-29 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(millisecond, 5184000000, TM) FROM P2 WHERE ID = 20005").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-02-29 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(microsecond, 5184000000000, TM) FROM P2 WHERE ID = 20005").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(Timestamp.valueOf("2000-02-29 00:00:00.000000"), vt.getTimestampAsSqlTimestamp(0));
    // Test null interval
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(YEAR, NULL, TM), DATEADD(QUARTER, NULL,TM), DATEADD(MONTH, NULL,TM), " + "DATEADD(DAY, NULL,TM), DATEADD(HOUR, NULL,TM), DATEADD(MINUTE, NULL,TM), " + "DATEADD(SECOND, NULL,TM), DATEADD(MILLISECOND, NULL,TM), " + "DATEADD(MICROSECOND, NULL,TM) FROM P2 WHERE ID = 20005;").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(null, vt.getTimestampAsTimestamp(0));
    assertEquals(null, vt.getTimestampAsTimestamp(1));
    assertEquals(null, vt.getTimestampAsTimestamp(2));
    assertEquals(null, vt.getTimestampAsTimestamp(3));
    assertEquals(null, vt.getTimestampAsTimestamp(4));
    assertEquals(null, vt.getTimestampAsTimestamp(5));
    assertEquals(null, vt.getTimestampAsTimestamp(6));
    assertEquals(null, vt.getTimestampAsTimestamp(7));
    assertEquals(null, vt.getTimestampAsTimestamp(8));
    // Test null timestamp
    cr = client.callProcedure("@AdHoc", "INSERT INTO P2 (ID, TM) VALUES (20006, null)");
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    vt = client.callProcedure("@AdHoc", "SELECT DATEADD(YEAR, 1, TM), DATEADD(QUARTER, 1, TM), DATEADD(MONTH, 1, TM), " + "DATEADD(DAY, 1, TM), DATEADD(HOUR, 1, TM), DATEADD(MINUTE, 1, TM), " + "DATEADD(SECOND, 1, TM), DATEADD(MILLISECOND, 1, TM), " + "DATEADD(MICROSECOND, 1, TM) FROM P2 WHERE ID = 20006;").getResults()[0];
    assertTrue(vt.advanceRow());
    assertEquals(null, vt.getTimestampAsTimestamp(0));
    assertEquals(null, vt.getTimestampAsTimestamp(1));
    assertEquals(null, vt.getTimestampAsTimestamp(2));
    assertEquals(null, vt.getTimestampAsTimestamp(3));
    assertEquals(null, vt.getTimestampAsTimestamp(4));
    assertEquals(null, vt.getTimestampAsTimestamp(5));
    assertEquals(null, vt.getTimestampAsTimestamp(6));
    assertEquals(null, vt.getTimestampAsTimestamp(7));
    assertEquals(null, vt.getTimestampAsTimestamp(8));
    // Test null or illegal datepart
    boolean throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(NULL, 1, TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("Unexpected Ad Hoc Planning Error"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(WEEK, 1, TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("Unexpected Ad Hoc Planning Error"));
        throwed = true;
    }
    assertTrue(throwed);
    // Test large intervals caused exceptions
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(YEAR, " + ((long) Integer.MAX_VALUE + 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(YEAR, " + ((long) Integer.MIN_VALUE - 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(QUARTER, " + ((long) Integer.MAX_VALUE + 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(QUARTER, " + ((long) Integer.MIN_VALUE - 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(MONTH, " + ((long) Integer.MAX_VALUE + 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(MONTH, " + ((long) Integer.MIN_VALUE - 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(DAY, " + Long.MAX_VALUE + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(DAY, " + (Long.MIN_VALUE + 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(HOUR, " + Long.MAX_VALUE + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(HOUR, " + (Long.MIN_VALUE + 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(MINUTE, " + Long.MAX_VALUE + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(MINUTE, " + (Long.MIN_VALUE + 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(SECOND, " + Long.MAX_VALUE + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(SECOND, " + (Long.MIN_VALUE + 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(MILLISECOND, " + Long.MAX_VALUE + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(MILLISECOND, " + (Long.MIN_VALUE + 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(MICROSECOND, " + Long.MAX_VALUE + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
    throwed = false;
    try {
        client.callProcedure("@AdHoc", "SELECT DATEADD(MICROSECOND, " + (Long.MIN_VALUE + 1) + ", TM) FROM P2 WHERE ID = 20005;");
    } catch (ProcCallException e) {
        assertEquals(ClientResponse.GRACEFUL_FAILURE, e.getClientResponse().getStatus());
        assertTrue(e.getClientResponse().getStatusString().contains("interval is too large for DATEADD function"));
        throwed = true;
    }
    assertTrue(throwed);
}

Example 58 with ClientResponse

use of org.voltdb.client.ClientResponse in project voltdb by VoltDB.

the class TestFunctionsForVoltDBSuite method testBadParamTypeForTimeStampField.

public void testBadParamTypeForTimeStampField() throws IOException, ProcCallException {
    Client client = getClient();
    // seed dummy data into table
    ClientResponse cr;
    cr = client.callProcedure("@AdHoc", "INSERT INTO P2 (ID, TM) VALUES (10000, '2000-01-01 01:00:00.000000');");
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    final int numberOfProcsToTest = BadParamTypesForTimestamp.procs.length;
    final int numberOfValuesToTest = BadParamTypesForTimestamp.values.length;
    for (int procEntry = 0; procEntry < numberOfProcsToTest; procEntry++) {
        for (int valueIndexToTestWith = 0; valueIndexToTestWith < numberOfValuesToTest; valueIndexToTestWith++) {
            verifyProcFails(client, "VOLTDB ERROR: SQL ERROR\n .* can't be cast as TIMESTAMP", "BadParamTypesForTimestamp", procEntry, valueIndexToTestWith);
        }
    }
}

Example 59 with ClientResponse

use of org.voltdb.client.ClientResponse in project voltdb by VoltDB.

the class TestFunctionsForVoltDBSuite method testConcat.

public void testConcat() throws NoConnectionsException, IOException, ProcCallException {
    System.out.println("STARTING test Concat and its Operator");
    Client client = getClient();
    ClientResponse cr;
    VoltTable result;
    cr = client.callProcedure("P1.insert", 1, "Xin", 1, 1.0);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    cr = client.callProcedure("CONCAT2", "", 1);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("Xin", result.getString(1));
    cr = client.callProcedure("CONCAT2", "@VoltDB", 1);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("Xin@VoltDB", result.getString(1));
    cr = client.callProcedure("ConcatOpt", "", 1);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("Xin", result.getString(1));
    cr = client.callProcedure("ConcatOpt", "@VoltDB", 1);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("Xin@VoltDB", result.getString(1));
}

Example 60 with ClientResponse

use of org.voltdb.client.ClientResponse in project voltdb by VoltDB.

the class TestFunctionsForVoltDBSuite method subtestDECODE.

private void subtestDECODE() throws NoConnectionsException, IOException, ProcCallException {
    System.out.println("STARTING DECODE");
    Client client = getClient();
    ClientResponse cr;
    VoltTable result;
    cr = client.callProcedure("@AdHoc", "Delete from P1;");
    cr = client.callProcedure("P1.insert", 1, "IBM", 10, 1.1);
    cr = client.callProcedure("P1.insert", 2, "Microsoft", 10, 1.1);
    cr = client.callProcedure("P1.insert", 3, "Hewlett Packard", 10, 1.1);
    cr = client.callProcedure("P1.insert", 4, "Gateway", 10, 1.1);
    cr = client.callProcedure("P1.insert", 5, null, 10, 1.1);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    // match 1st condition
    cr = client.callProcedure("DECODE", 1);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("zheng", result.getString(1));
    // match 2nd condition
    cr = client.callProcedure("DECODE", 2);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("li", result.getString(1));
    // match 3rd condition
    cr = client.callProcedure("DECODE", 3);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("at", result.getString(1));
    // match 4th condition
    cr = client.callProcedure("DECODE", 4);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("VoltDB", result.getString(1));
    // null case
    cr = client.callProcedure("DECODE", 5);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("where", result.getString(1));
    // param cases
    // For project.addStmtProcedure("DECODE_PARAM_INFER_STRING", "select desc,  DECODE (desc,?,?,desc) from P1 where id = ?");
    cr = client.callProcedure("DECODE_PARAM_INFER_STRING", "Gateway", "You got it!", 4);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("You got it!", result.getString(1));
    // For project.addStmtProcedure("DECODE_PARAM_INFER_INT", "select desc,  DECODE (id,?,?,id) from P1 where id = ?");
    cr = client.callProcedure("DECODE_PARAM_INFER_INT", 4, -4, 4);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals(-4, result.getLong(1));
    // For project.addStmtProcedure("DECODE_PARAM_INFER_DEFAULT", "select desc,  DECODE (?,?,?,?) from P1 where id = ?");
    cr = client.callProcedure("DECODE_PARAM_INFER_DEFAULT", "Gateway", "Gateway", "You got it!", "You ain't got it!", 4);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("You got it!", result.getString(1));
    // For project.addStmtProcedure("DECODE_PARAM_INFER_CONFLICTING", "select desc,  DECODE (id,1,?,2,99,'99') from P1 where id = ?");
    cr = client.callProcedure("DECODE_PARAM_INFER_CONFLICTING", "贾鑫?贾鑫!", 1);
    assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    result = cr.getResults()[0];
    assertEquals(1, result.getRowCount());
    assertTrue(result.advanceRow());
    assertEquals("贾鑫?贾鑫!", result.getString(1));
    // For project.addStmtProcedure("DECODE_PARAM_INFER_CONFLICTING", "select desc,  DECODE (id,1,?,2,99,'99') from P1 where id = ?");
    try {
        cr = client.callProcedure("DECODE_PARAM_INFER_CONFLICTING", 1000, 1);
        assertEquals(ClientResponse.SUCCESS, cr.getStatus());
    } catch (ProcCallException pce) {
        fail("Should have thrown unfortunate type error.");
    }
}