Examples with ResultSetMetaData java.sql.ResultSetMetaData used on opensource projects

Example 96 with ResultSetMetaData

use of java.sql.ResultSetMetaData in project mysql_perf_analyzer by yahoo.

the class MetaDB method checkAndCreateDBCredentialTable.

private void checkAndCreateDBCredentialTable(Connection conn) throws SQLException {
    if (!DBUtils.hasTable(conn, SCHEMA_NAME, CRED_TABLENAME)) {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            logger.info("create table " + CRED_TABLENAME + ".");
            String sql = "create table " + CRED_TABLENAME + "(OWNER VARCHAR(30), DBGROUPNAME VARCHAR(30),USERNAME VARCHAR(60), CREDENTIAL VARCHAR(1024), VERIFIED SMALLINT DEFAULT 0, CREATED TIMESTAMP DEFAULT CURRENT TIMESTAMP)";
            stmt.execute(sql);
            sql = "create unique index UK_" + CRED_TABLENAME + " on " + CRED_TABLENAME + " (OWNER, DBGROUPNAME)";
            stmt.execute(sql);
        } finally {
            DBUtils.close(stmt);
        }
    } else {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            //because of an earlier bug, we forgot to create uniq index/PK on this table
            String ukSQL = "SELECT * FROM " + " SYS.SYSCONGLOMERATES WHERE " + " CONGLOMERATENAME = 'UK_" + CRED_TABLENAME + "'";
            logger.info("Check unique constraints on table " + CRED_TABLENAME + " with " + ukSQL);
            rs = stmt.executeQuery(ukSQL);
            boolean hasIndex = false;
            if (rs != null && rs.next()) {
                logger.info("UK_" + CRED_TABLENAME + " exists, nothing to do.");
                hasIndex = true;
            }
            rs.close();
            rs = null;
            if (!hasIndex) {
                logger.info("UK_" + CRED_TABLENAME + " does not exist, create it.");
                try {
                    boolean ret = stmt.execute("CREATE UNIQUE INDEX UK_" + CRED_TABLENAME + " ON " + CRED_TABLENAME + " (OWNER, DBGROUPNAME)");
                    logger.info("UK_" + CRED_TABLENAME + " created: " + ret);
                } catch (Throwable ex) {
                    logger.log(Level.WARNING, "Exception when create UK_" + CRED_TABLENAME, ex);
                }
            }
            String sql = "select * from " + CRED_TABLENAME + " where 1=0";
            rs = stmt.executeQuery(sql);
            ResultSetMetaData meta = rs.getMetaData();
            int cnt = meta.getColumnCount();
            boolean hasCol = false;
            for (int i = 1; i <= cnt; i++) {
                String col = meta.getColumnName(i);
                if ("CREDENTIAL".equalsIgnoreCase(col) && meta.getColumnDisplaySize(i) < 1024) {
                    hasCol = true;
                    break;
                }
            }
            DBUtils.close(rs);
            rs = null;
            if (hasCol) {
                logger.info("Add new column credential size");
                stmt.execute("ALTER TABLE " + CRED_TABLENAME + " ALTER COLUMN CREDENTIAL SET DATA TYPE VARCHAR(1024)");
            }
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                }
        }
    }
}

Example 97 with ResultSetMetaData

use of java.sql.ResultSetMetaData in project jdbc-shards by wplatform.

the class Expression method getExpressionColumns.

/**
     * Extracts expression columns from the given result set.
     *
     * @param session the session
     * @param rs      the result set
     * @return an array of expression columns
     */
public static Expression[] getExpressionColumns(Session session, ResultSet rs) {
    try {
        ResultSetMetaData meta = rs.getMetaData();
        int columnCount = meta.getColumnCount();
        Expression[] expressions = new Expression[columnCount];
        Database db = session == null ? null : session.getDatabase();
        for (int i = 0; i < columnCount; i++) {
            String name = meta.getColumnLabel(i + 1);
            int type = DataType.getValueTypeFromResultSet(meta, i + 1);
            int precision = meta.getPrecision(i + 1);
            int scale = meta.getScale(i + 1);
            int displaySize = meta.getColumnDisplaySize(i + 1);
            Column col = new Column(name, type, precision, scale, displaySize);
            Expression expr = new ExpressionColumn(db, col);
            expressions[i] = expr;
        }
        return expressions;
    } catch (SQLException e) {
        throw DbException.convert(e);
    }
}

Example 98 with ResultSetMetaData

use of java.sql.ResultSetMetaData in project jdbc-shards by wplatform.

the class UpdatableResultSetTestCase method testUpdateDataType.

private void testUpdateDataType() throws Exception {
    Connection conn = getConnection();
    Statement stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255), " + "DEC DECIMAL(10,2), BOO BIT, BYE TINYINT, BIN BINARY(100), " + "D DATE, T TIME, TS TIMESTAMP, DB DOUBLE, R REAL, L BIGINT, " + "O_I INT, SH SMALLINT, CL CLOB, BL BLOB)");
    ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
    ResultSetMetaData meta = rs.getMetaData();
    assertEquals("java.lang.Integer", meta.getColumnClassName(1));
    assertEquals("java.lang.String", meta.getColumnClassName(2));
    assertEquals("java.math.BigDecimal", meta.getColumnClassName(3));
    assertEquals("java.lang.Boolean", meta.getColumnClassName(4));
    assertEquals("java.lang.Byte", meta.getColumnClassName(5));
    assertEquals("[B", meta.getColumnClassName(6));
    assertEquals("java.sql.Date", meta.getColumnClassName(7));
    assertEquals("java.sql.Time", meta.getColumnClassName(8));
    assertEquals("java.sql.Timestamp", meta.getColumnClassName(9));
    assertEquals("java.lang.Double", meta.getColumnClassName(10));
    assertEquals("java.lang.Float", meta.getColumnClassName(11));
    assertEquals("java.lang.Long", meta.getColumnClassName(12));
    assertEquals("java.lang.Integer", meta.getColumnClassName(13));
    assertEquals("java.lang.Short", meta.getColumnClassName(14));
    assertEquals("java.sql.Clob", meta.getColumnClassName(15));
    assertEquals("java.sql.Blob", meta.getColumnClassName(16));
    rs.moveToInsertRow();
    rs.updateInt(1, 0);
    rs.updateNull(2);
    rs.updateNull("DEC");
    // 'not set' values are set to null
    assertThrows(ErrorCode.NO_DATA_AVAILABLE, rs).cancelRowUpdates();
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt(1, 1);
    rs.updateString(2, null);
    rs.updateBigDecimal(3, null);
    rs.updateBoolean(4, false);
    rs.updateByte(5, (byte) 0);
    rs.updateBytes(6, null);
    rs.updateDate(7, null);
    rs.updateTime(8, null);
    rs.updateTimestamp(9, null);
    rs.updateDouble(10, 0.0);
    rs.updateFloat(11, (float) 0.0);
    rs.updateLong(12, 0L);
    rs.updateObject(13, null);
    rs.updateShort(14, (short) 0);
    rs.updateCharacterStream(15, new StringReader("test"), 0);
    rs.updateBinaryStream(16, new ByteArrayInputStream(new byte[] { (byte) 0xff, 0x00 }), 0);
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 2);
    rs.updateString("NAME", "+");
    rs.updateBigDecimal("DEC", new BigDecimal("1.2"));
    rs.updateBoolean("BOO", true);
    rs.updateByte("BYE", (byte) 0xff);
    rs.updateBytes("BIN", new byte[] { 0x00, (byte) 0xff });
    rs.updateDate("D", Date.valueOf("2005-09-21"));
    rs.updateTime("T", Time.valueOf("21:46:28"));
    rs.updateTimestamp("TS", Timestamp.valueOf("2005-09-21 21:47:09.567890123"));
    rs.updateDouble("DB", 1.725);
    rs.updateFloat("R", (float) 2.5);
    rs.updateLong("L", Long.MAX_VALUE);
    rs.updateObject("O_I", 10);
    rs.updateShort("SH", Short.MIN_VALUE);
    // auml, ouml, uuml
    rs.updateCharacterStream("CL", new StringReader("ïöü"), 0);
    rs.updateBinaryStream("BL", new ByteArrayInputStream(new byte[] { (byte) 0xab, 0x12 }), 0);
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 3);
    rs.updateCharacterStream("CL", new StringReader("ïöü"));
    rs.updateBinaryStream("BL", new ByteArrayInputStream(new byte[] { (byte) 0xab, 0x12 }));
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 4);
    rs.updateCharacterStream(15, new StringReader("ïöü"));
    rs.updateBinaryStream(16, new ByteArrayInputStream(new byte[] { (byte) 0xab, 0x12 }));
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 5);
    rs.updateClob("CL", new StringReader("ïöü"));
    rs.updateBlob("BL", new ByteArrayInputStream(new byte[] { (byte) 0xab, 0x12 }));
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 6);
    rs.updateClob(15, new StringReader("ïöü"));
    rs.updateBlob(16, new ByteArrayInputStream(new byte[] { (byte) 0xab, 0x12 }));
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 7);
    rs.updateNClob("CL", new StringReader("ïöü"));
    Blob b = conn.createBlob();
    OutputStream out = b.setBinaryStream(1);
    out.write(new byte[] { (byte) 0xab, 0x12 });
    out.close();
    rs.updateBlob("BL", b);
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 8);
    rs.updateNClob(15, new StringReader("ïöü"));
    rs.updateBlob(16, b);
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 9);
    rs.updateNClob("CL", new StringReader("ïöü"), -1);
    rs.updateBlob("BL", b);
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 10);
    rs.updateNClob(15, new StringReader("ïöü"), -1);
    rs.updateBlob(16, b);
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 11);
    rs.updateNCharacterStream("CL", new StringReader("ïöü"), -1);
    rs.updateBlob("BL", b);
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 12);
    rs.updateNCharacterStream(15, new StringReader("ïöü"), -1);
    rs.updateBlob(16, b);
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 13);
    rs.updateNCharacterStream("CL", new StringReader("ïöü"));
    rs.updateBlob("BL", b);
    rs.insertRow();
    rs.moveToInsertRow();
    rs.updateInt("ID", 14);
    rs.updateNCharacterStream(15, new StringReader("ïöü"));
    rs.updateBlob(16, b);
    rs.insertRow();
    rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID NULLS FIRST");
    rs.next();
    assertTrue(rs.getInt(1) == 0);
    assertTrue(rs.getString(2) == null && rs.wasNull());
    assertTrue(rs.getBigDecimal(3) == null && rs.wasNull());
    assertTrue(!rs.getBoolean(4) && rs.wasNull());
    assertTrue(rs.getByte(5) == 0 && rs.wasNull());
    assertTrue(rs.getBytes(6) == null && rs.wasNull());
    assertTrue(rs.getDate(7) == null && rs.wasNull());
    assertTrue(rs.getTime(8) == null && rs.wasNull());
    assertTrue(rs.getTimestamp(9) == null && rs.wasNull());
    assertTrue(rs.getDouble(10) == 0.0 && rs.wasNull());
    assertTrue(rs.getFloat(11) == 0.0 && rs.wasNull());
    assertTrue(rs.getLong(12) == 0 && rs.wasNull());
    assertTrue(rs.getObject(13) == null && rs.wasNull());
    assertTrue(rs.getShort(14) == 0 && rs.wasNull());
    assertTrue(rs.getCharacterStream(15) == null && rs.wasNull());
    assertTrue(rs.getBinaryStream(16) == null && rs.wasNull());
    rs.next();
    assertTrue(rs.getInt(1) == 1);
    assertTrue(rs.getString(2) == null && rs.wasNull());
    assertTrue(rs.getBigDecimal(3) == null && rs.wasNull());
    assertTrue(!rs.getBoolean(4) && !rs.wasNull());
    assertTrue(rs.getByte(5) == 0 && !rs.wasNull());
    assertTrue(rs.getBytes(6) == null && rs.wasNull());
    assertTrue(rs.getDate(7) == null && rs.wasNull());
    assertTrue(rs.getTime(8) == null && rs.wasNull());
    assertTrue(rs.getTimestamp(9) == null && rs.wasNull());
    assertTrue(rs.getDouble(10) == 0.0 && !rs.wasNull());
    assertTrue(rs.getFloat(11) == 0.0 && !rs.wasNull());
    assertTrue(rs.getLong(12) == 0 && !rs.wasNull());
    assertTrue(rs.getObject(13) == null && rs.wasNull());
    assertTrue(rs.getShort(14) == 0 && !rs.wasNull());
    assertEquals("test", rs.getString(15));
    assertEquals(new byte[] { (byte) 0xff, 0x00 }, rs.getBytes(16));
    rs.next();
    assertTrue(rs.getInt(1) == 2);
    assertEquals("+", rs.getString(2));
    assertEquals("1.20", rs.getBigDecimal(3).toString());
    assertTrue(rs.getBoolean(4));
    assertTrue((rs.getByte(5) & 0xff) == 0xff);
    assertEquals(new byte[] { 0x00, (byte) 0xff }, rs.getBytes(6));
    assertEquals("2005-09-21", rs.getDate(7).toString());
    assertEquals("21:46:28", rs.getTime(8).toString());
    assertEquals("2005-09-21 21:47:09.567890123", rs.getTimestamp(9).toString());
    assertTrue(rs.getDouble(10) == 1.725);
    assertTrue(rs.getFloat(11) == (float) 2.5);
    assertTrue(rs.getLong(12) == Long.MAX_VALUE);
    assertEquals(10, ((Integer) rs.getObject(13)).intValue());
    assertTrue(rs.getShort(14) == Short.MIN_VALUE);
    // auml ouml uuml
    assertEquals("ïöü", rs.getString(15));
    assertEquals(new byte[] { (byte) 0xab, 0x12 }, rs.getBytes(16));
    for (int i = 3; i <= 14; i++) {
        rs.next();
        assertEquals(i, rs.getInt(1));
        assertEquals("ïöü", rs.getString(15));
        assertEquals(new byte[] { (byte) 0xab, 0x12 }, rs.getBytes(16));
    }
    assertFalse(rs.next());
    stat.execute("DROP TABLE TEST");
    conn.close();
}

Example 99 with ResultSetMetaData

use of java.sql.ResultSetMetaData in project jdbc-shards by wplatform.

the class ResultSetTestCase method testColumnLabelColumnName.

private void testColumnLabelColumnName() throws SQLException {
    ResultSet rs = stat.executeQuery("select x as y from dual");
    rs.next();
    rs.getString("x");
    rs.getString("y");
    rs.close();
    rs = conn.getMetaData().getColumns(null, null, null, null);
    ResultSetMetaData meta = rs.getMetaData();
    int columnCount = meta.getColumnCount();
    String[] columnName = new String[columnCount];
    for (int i = 1; i <= columnCount; i++) {
        // columnName[i - 1] = meta.getColumnLabel(i);
        columnName[i - 1] = meta.getColumnName(i);
    }
    while (rs.next()) {
        for (int i = 0; i < columnCount; i++) {
            rs.getObject(columnName[i]);
        }
    }
}

Example 100 with ResultSetMetaData

use of java.sql.ResultSetMetaData in project jdbc-shards by wplatform.

the class ResultSetTestCase method testLimitMaxRows.

private void testLimitMaxRows() throws SQLException {
    trace("Test LimitMaxRows");
    ResultSet rs;
    stat.execute("CREATE TABLE one (C CHARACTER(10))");
    rs = stat.executeQuery("SELECT C || C FROM one;");
    ResultSetMetaData md = rs.getMetaData();
    assertEquals(20, md.getPrecision(1));
    ResultSet rs2 = stat.executeQuery("SELECT UPPER (C)  FROM one;");
    ResultSetMetaData md2 = rs2.getMetaData();
    assertEquals(10, md2.getPrecision(1));
    rs = stat.executeQuery("SELECT UPPER (C), CHAR(10), " + "CONCAT(C,C,C), HEXTORAW(C), RAWTOHEX(C) FROM one");
    ResultSetMetaData meta = rs.getMetaData();
    assertEquals(10, meta.getPrecision(1));
    assertEquals(1, meta.getPrecision(2));
    assertEquals(30, meta.getPrecision(3));
    assertEquals(3, meta.getPrecision(4));
    assertEquals(40, meta.getPrecision(5));
    stat.execute("DROP TABLE one");
}