Search in sources :

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) {
                }
        }
    }
}
Also used : ResultSetMetaData(java.sql.ResultSetMetaData) PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) ResultSet(java.sql.ResultSet) SQLException(java.sql.SQLException)

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);
    }
}
Also used : ResultSetMetaData(java.sql.ResultSetMetaData) Column(com.wplatform.ddal.dbobject.table.Column) SQLException(java.sql.SQLException) Database(com.wplatform.ddal.engine.Database)

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();
}
Also used : ResultSetMetaData(java.sql.ResultSetMetaData) Blob(java.sql.Blob) ByteArrayInputStream(java.io.ByteArrayInputStream) PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) OutputStream(java.io.OutputStream) Connection(java.sql.Connection) ResultSet(java.sql.ResultSet) StringReader(java.io.StringReader) BigDecimal(java.math.BigDecimal)

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]);
        }
    }
}
Also used : ResultSetMetaData(java.sql.ResultSetMetaData) ResultSet(java.sql.ResultSet)

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");
}
Also used : ResultSetMetaData(java.sql.ResultSetMetaData) ResultSet(java.sql.ResultSet)

Aggregations

ResultSetMetaData (java.sql.ResultSetMetaData)414 ResultSet (java.sql.ResultSet)262 Statement (java.sql.Statement)118 SQLException (java.sql.SQLException)111 Test (org.junit.Test)109 PreparedStatement (java.sql.PreparedStatement)95 Connection (java.sql.Connection)64 ArrayList (java.util.ArrayList)61 DatabaseMetaData (java.sql.DatabaseMetaData)40 HashMap (java.util.HashMap)40 Map (java.util.Map)28 List (java.util.List)17 LinkedHashMap (java.util.LinkedHashMap)15 IOException (java.io.IOException)14 BigDecimal (java.math.BigDecimal)12 HashSet (java.util.HashSet)12 ParameterMetaData (java.sql.ParameterMetaData)10 Properties (java.util.Properties)10 KnownFailure (dalvik.annotation.KnownFailure)9 Test (org.testng.annotations.Test)9