Package java.sql

Examples of java.sql.ResultSet


    }

    private void testMathFunctions() throws SQLException {
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("CALL SINH(50)");
        assertTrue(rs.next());
        assertEquals(Math.sinh(50), rs.getDouble(1));
        rs = stat.executeQuery("CALL COSH(50)");
        assertTrue(rs.next());
        assertEquals(Math.cosh(50), rs.getDouble(1));
        rs = stat.executeQuery("CALL TANH(50)");
        assertTrue(rs.next());
        assertEquals(Math.tanh(50), rs.getDouble(1));
        conn.close();
    }
View Full Code Here


//## Java 1.5 begin ##
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        stat.execute("CREATE ALIAS mean FOR \"" +
                getClass().getName() + ".mean\"");
        ResultSet rs = stat.executeQuery(
                "select mean(), mean(10), mean(10, 20), mean(10, 20, 30)");
        rs.next();
        assertEquals(1.0, rs.getDouble(1));
        assertEquals(10.0, rs.getDouble(2));
        assertEquals(15.0, rs.getDouble(3));
        assertEquals(20.0, rs.getDouble(4));

        stat.execute("CREATE ALIAS mean2 FOR \"" +
                getClass().getName() + ".mean2\"");
        rs = stat.executeQuery(
                "select mean2(), mean2(10), mean2(10, 20)");
        rs.next();
        assertEquals(Double.NaN, rs.getDouble(1));
        assertEquals(10.0, rs.getDouble(2));
        assertEquals(15.0, rs.getDouble(3));

        DatabaseMetaData meta = conn.getMetaData();
        rs = meta.getProcedureColumns(null, null, "MEAN2", null);
        assertTrue(rs.next());
        assertEquals("FUNCTIONS", rs.getString("PROCEDURE_CAT"));
        assertEquals("PUBLIC", rs.getString("PROCEDURE_SCHEM"));
        assertEquals("MEAN2", rs.getString("PROCEDURE_NAME"));
        assertEquals("P2", rs.getString("COLUMN_NAME"));
        assertEquals(DatabaseMetaData.procedureColumnIn, rs.getInt("COLUMN_TYPE"));
        assertEquals("OTHER", rs.getString("TYPE_NAME"));
        assertEquals(Integer.MAX_VALUE, rs.getInt("PRECISION"));
        assertEquals(Integer.MAX_VALUE, rs.getInt("LENGTH"));
        assertEquals(0, rs.getInt("SCALE"));
        assertEquals(DatabaseMetaData.columnNullable, rs.getInt("NULLABLE"));
        assertEquals("", rs.getString("REMARKS"));
        assertEquals(null, rs.getString("COLUMN_DEF"));
        assertEquals(0, rs.getInt("SQL_DATA_TYPE"));
        assertEquals(0, rs.getInt("SQL_DATETIME_SUB"));
        assertEquals(0, rs.getInt("CHAR_OCTET_LENGTH"));
        assertEquals(1, rs.getInt("ORDINAL_POSITION"));
        assertEquals("YES", rs.getString("IS_NULLABLE"));
        assertEquals("MEAN2", rs.getString("SPECIFIC_NAME"));
        assertFalse(rs.next());

        stat.execute("CREATE ALIAS printMean FOR \"" +
                getClass().getName() + ".printMean\"");
        rs = stat.executeQuery(
                "select printMean('A'), printMean('A', 10), " +
                "printMean('BB', 10, 20), printMean ('CCC', 10, 20, 30)");
        rs.next();
        assertEquals("A: 0", rs.getString(1));
        assertEquals("A: 10", rs.getString(2));
        assertEquals("BB: 15", rs.getString(3));
        assertEquals("CCC: 20", rs.getString(4));
        conn.close();
//## Java 1.5 end ##
    }
View Full Code Here

        String fileName = getBaseDir() + "/test.txt";
        Properties prop = System.getProperties();
        OutputStream out = IOUtils.openFileOutputStream(fileName, false);
        prop.store(out, "");
        out.close();
        ResultSet rs = stat.executeQuery("SELECT LENGTH(FILE_READ('" + fileName + "')) LEN");
        rs.next();
        assertEquals(IOUtils.length(fileName), rs.getInt(1));
        rs = stat.executeQuery("SELECT FILE_READ('" + fileName + "') PROP");
        rs.next();
        Properties p2 = new Properties();
        p2.load(rs.getBinaryStream(1));
        assertEquals(prop.size(), p2.size());
        rs = stat.executeQuery("SELECT FILE_READ('" + fileName + "', NULL) PROP");
        rs.next();
        String ps = rs.getString(1);
        InputStreamReader r = new InputStreamReader(IOUtils.openFileInputStream(fileName));
        String ps2 = IOUtils.readStringAndClose(r, -1);
        assertEquals(ps, ps2);
        conn.close();
        IOUtils.delete(fileName);
View Full Code Here

        deleteDb("functions");
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        stat.execute("CREATE AGGREGATE MEDIAN FOR \"" + MedianString.class.getName() + "\"");
        stat.execute("CREATE AGGREGATE IF NOT EXISTS MEDIAN FOR \"" + MedianString.class.getName() + "\"");
        ResultSet rs = stat.executeQuery("SELECT MEDIAN(X) FROM SYSTEM_RANGE(1, 9)");
        rs.next();
        assertEquals("5", rs.getString(1));
        conn.close();

        if (config.memory) {
            return;
        }

        conn = getConnection("functions");
        stat = conn.createStatement();
        stat.executeQuery("SELECT MEDIAN(X) FROM SYSTEM_RANGE(1, 9)");
        DatabaseMetaData meta = conn.getMetaData();
        rs = meta.getProcedures(null, null, "MEDIAN");
        assertTrue(rs.next());
        assertFalse(rs.next());
        rs = stat.executeQuery("SCRIPT");
        boolean found = false;
        while (rs.next()) {
            String sql = rs.getString(1);
            if (sql.indexOf("MEDIAN") >= 0) {
                found = true;
            }
        }
        assertTrue(found);
View Full Code Here

        assertCallResult("1", stat, "abs(1)");
        assertCallResult("1", stat, "abs(1)");

        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
        stat.execute("CREATE ALIAS ADD_ROW FOR \"" + getClass().getName() + ".addRow\"");
        ResultSet rs;
        rs = stat.executeQuery("CALL ADD_ROW(1, 'Hello')");
        rs.next();
        assertEquals(1, rs.getInt(1));
        rs = stat.executeQuery("SELECT * FROM TEST");
        rs.next();
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());

        DatabaseMetaData meta = conn.getMetaData();
        rs = meta.getProcedureColumns(null, null, "ADD_ROW", null);
        assertTrue(rs.next());
        assertEquals("FUNCTIONS", rs.getString("PROCEDURE_CAT"));
        assertEquals("PUBLIC", rs.getString("PROCEDURE_SCHEM"));
        assertEquals("ADD_ROW", rs.getString("PROCEDURE_NAME"));
        assertEquals("P2", rs.getString("COLUMN_NAME"));
        assertEquals(DatabaseMetaData.procedureColumnIn, rs.getInt("COLUMN_TYPE"));
        assertEquals("INTEGER", rs.getString("TYPE_NAME"));
        assertEquals(10, rs.getInt("PRECISION"));
        assertEquals(10, rs.getInt("LENGTH"));
        assertEquals(0, rs.getInt("SCALE"));
        assertEquals(DatabaseMetaData.columnNoNulls, rs.getInt("NULLABLE"));
        assertEquals("", rs.getString("REMARKS"));
        assertEquals(null, rs.getString("COLUMN_DEF"));
        assertEquals(0, rs.getInt("SQL_DATA_TYPE"));
        assertEquals(0, rs.getInt("SQL_DATETIME_SUB"));
        assertEquals(0, rs.getInt("CHAR_OCTET_LENGTH"));
        assertEquals(1, rs.getInt("ORDINAL_POSITION"));
        assertEquals("YES", rs.getString("IS_NULLABLE"));
        assertEquals("ADD_ROW", rs.getString("SPECIFIC_NAME"));
        assertTrue(rs.next());
        assertEquals("P3", rs.getString("COLUMN_NAME"));
        assertEquals("VARCHAR", rs.getString("TYPE_NAME"));
        assertFalse(rs.next());

        stat.executeQuery("CALL ADD_ROW(2, 'World')");

        stat.execute("CREATE ALIAS SELECT_F FOR \"" + getClass().getName() + ".select\"");
        rs = stat.executeQuery("CALL SELECT_F('SELECT * FROM TEST ORDER BY ID')");
        assertEquals(2, rs.getMetaData().getColumnCount());
        rs.next();
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        rs.next();
        assertEquals(2, rs.getInt(1));
        assertEquals("World", rs.getString(2));
        assertFalse(rs.next());

        rs = stat.executeQuery("SELECT NAME FROM SELECT_F('SELECT * FROM TEST ORDER BY NAME') ORDER BY NAME DESC");
        assertEquals(1, rs.getMetaData().getColumnCount());
        rs.next();
        assertEquals("World", rs.getString(1));
        rs.next();
        assertEquals("Hello", rs.getString(1));
        assertFalse(rs.next());

        rs = stat.executeQuery("SELECT SELECT_F('SELECT * FROM TEST WHERE ID=' || ID) FROM TEST ORDER BY ID");
        assertEquals(1, rs.getMetaData().getColumnCount());
        rs.next();
        assertEquals("((1, Hello))", rs.getString(1));
        rs.next();
        assertEquals("((2, World))", rs.getString(1));
        assertFalse(rs.next());

        rs = stat.executeQuery("SELECT SELECT_F('SELECT * FROM TEST ORDER BY ID') FROM DUAL");
        assertEquals(1, rs.getMetaData().getColumnCount());
        rs.next();
        assertEquals("((1, Hello), (2, World))", rs.getString(1));
        assertFalse(rs.next());
        assertThrows(ErrorCode.SYNTAX_ERROR_2, stat).
                executeQuery("CALL SELECT_F('ERROR')");
        stat.execute("CREATE ALIAS SIMPLE FOR \"" + getClass().getName() + ".simpleResultSet\"");
        rs = stat.executeQuery("CALL SIMPLE(2, 1, 1, 1, 1, 1, 1, 1)");
        assertEquals(2, rs.getMetaData().getColumnCount());
        rs.next();
        assertEquals(0, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        rs.next();
        assertEquals(1, rs.getInt(1));
        assertEquals("World", rs.getString(2));
        assertFalse(rs.next());

        rs = stat.executeQuery("SELECT * FROM SIMPLE(1, 1, 1, 1, 1, 1, 1, 1)");
        assertEquals(2, rs.getMetaData().getColumnCount());
        rs.next();
        assertEquals(0, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());

        stat.execute("CREATE ALIAS ARRAY FOR \"" + getClass().getName() + ".getArray\"");
        rs = stat.executeQuery("CALL ARRAY()");
        assertEquals(1, rs.getMetaData().getColumnCount());
        rs.next();
        Array a = rs.getArray(1);
        Object[] array = (Object[]) a.getArray();
        assertEquals(2, array.length);
        assertEquals(0, ((Integer) array[0]).intValue());
        assertEquals("Hello", (String) array[1]);
        ResultSet rs2 = a.getResultSet();
        rs2.next();
        assertEquals(1, rs2.getInt(1));
        assertEquals(0, rs2.getInt(2));
        rs2.next();
        assertEquals(2, rs2.getInt(1));
        assertEquals("Hello", rs2.getString(2));
        assertFalse(rs.next());

        stat.execute("CREATE ALIAS ROOT FOR \"" + getClass().getName() + ".root\"");
        rs = stat.executeQuery("CALL ROOT(9)");
        rs.next();
View Full Code Here

        deleteDb("functions");
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        // with white space
        stat.execute("CREATE ALIAS PARSE_INT2 FOR \"java.lang.Integer.parseInt(java.lang.String, int)\"");
        ResultSet rs;
        rs = stat.executeQuery("CALL PARSE_INT2('473', 10)");
        rs.next();
        assertEquals(473, rs.getInt(1));
        stat.execute("DROP ALIAS PARSE_INT2");
        // without white space
        stat.execute("CREATE ALIAS PARSE_INT2 FOR \"java.lang.Integer.parseInt(java.lang.String,int)\"");
        stat.execute("DROP ALIAS PARSE_INT2");
        conn.close();
View Full Code Here

    private void testSchemaSearchPath() throws SQLException {
        deleteDb("functions");
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        ResultSet rs;
        stat.execute("CREATE SCHEMA TEST");
        stat.execute("SET SCHEMA TEST");
        stat.execute("CREATE ALIAS PARSE_INT2 FOR \"java.lang.Integer.parseInt(java.lang.String, int)\";");
        rs = stat.executeQuery("SELECT ALIAS_NAME FROM INFORMATION_SCHEMA.FUNCTION_ALIASES WHERE ALIAS_SCHEMA ='TEST'");
        rs.next();
        assertEquals("PARSE_INT2", rs.getString(1));
        stat.execute("DROP ALIAS PARSE_INT2");

        stat.execute("SET SCHEMA PUBLIC");
        stat.execute("CREATE ALIAS TEST.PARSE_INT2 FOR \"java.lang.Integer.parseInt(java.lang.String, int)\";");
        stat.execute("SET SCHEMA_SEARCH_PATH PUBLIC, TEST");

        rs = stat.executeQuery("CALL PARSE_INT2('-FF', 16)");
        rs.next();
        assertEquals(-255, rs.getInt(1));
        rs = stat.executeQuery("SELECT ALIAS_NAME FROM INFORMATION_SCHEMA.FUNCTION_ALIASES WHERE ALIAS_SCHEMA ='TEST'");
        rs.next();
        assertEquals("PARSE_INT2", rs.getString(1));
        rs = stat.executeQuery("CALL TEST.PARSE_INT2('-2147483648', 10)");
        rs.next();
        assertEquals(-2147483648, rs.getInt(1));
        rs = stat.executeQuery("CALL FUNCTIONS.TEST.PARSE_INT2('-2147483648', 10)");
        rs.next();
        assertEquals(-2147483648, rs.getInt(1));
        conn.close();
    }
View Full Code Here

    private void testArrayParameters() throws SQLException {
        deleteDb("functions");
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        ResultSet rs;
        stat.execute("create alias array_test AS "
                + "$$ Integer[] array_test(Integer[] in_array) "
                + "{ return in_array; } $$;");

        PreparedStatement stmt = conn.prepareStatement("select array_test(?) from dual");
        stmt.setObject(1, new Integer[] { 1, 2 });
        rs = stmt.executeQuery();
        rs.next();
        assertEquals(Integer[].class.getName(), rs.getObject(1).getClass().getName());

        CallableStatement call = conn.prepareCall("{ ? = call array_test(?) }");
        call.setObject(2, new Integer[] { 2, 1 });
        call.registerOutParameter(1, Types.ARRAY);
        call.execute();
View Full Code Here

        conn.close();
    }

    private void assertCallResult(String expected, Statement stat, String sql) throws SQLException {
        ResultSet rs = stat.executeQuery("CALL " + sql);
        rs.next();
        String s = rs.getString(1);
        assertEquals(expected, s);
    }
View Full Code Here

     * @param name the text
     * @return the count
     */
    public static int addRow(Connection conn, int id, String name) throws SQLException {
        conn.createStatement().execute("INSERT INTO TEST VALUES(" + id + ", '" + name + "')");
        ResultSet rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM TEST");
        rs.next();
        int result = rs.getInt(1);
        rs.close();
        return result;
    }
View Full Code Here

TOP

Related Classes of java.sql.ResultSet

Copyright © 2018 www.massapicom. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.