Search in sources :

Example 36 with JDBCResultSet

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet in project dbeaver by serge-rider.

the class OracleUtils method getObjectStatus.

public static boolean getObjectStatus(DBRProgressMonitor monitor, OracleStatefulObject object, OracleObjectType objectType) throws DBCException {
    try (JDBCSession session = DBUtils.openMetaSession(monitor, object.getDataSource(), "Refresh state of " + objectType.getTypeName() + " '" + object.getName() + "'")) {
        try (JDBCPreparedStatement dbStat = session.prepareStatement("SELECT STATUS FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE=? AND OWNER=? AND OBJECT_NAME=?")) {
            dbStat.setString(1, objectType.getTypeName());
            dbStat.setString(2, object.getSchema().getName());
            dbStat.setString(3, DBObjectNameCaseTransformer.transformObjectName(object, object.getName()));
            try (JDBCResultSet dbResult = dbStat.executeQuery()) {
                if (dbResult.next()) {
                    return "VALID".equals(dbResult.getString("STATUS"));
                } else {
                    log.warn(objectType.getTypeName() + " '" + object.getName() + "' not found in system dictionary");
                    return false;
                }
            }
        }
    } catch (SQLException e) {
        throw new DBCException(e, object.getDataSource());
    }
}
Also used : JDBCPreparedStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCPreparedStatement) JDBCSession(org.jkiss.dbeaver.model.exec.jdbc.JDBCSession) SQLException(java.sql.SQLException) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet) DBCException(org.jkiss.dbeaver.model.exec.DBCException)

Example 37 with JDBCResultSet

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet in project dbeaver by serge-rider.

the class OraclePlanAnalyser method explain.

public void explain(JDBCSession session) throws DBCException {
    String planStmtId = SecurityUtils.generateUniqueId();
    try {
        // Detect plan table
        String planTableName = dataSource.getPlanTableName(session);
        if (planTableName == null) {
            throw new DBCException("Plan table not found - query can't be explained");
        }
        // Delete previous statement rows
        // (actually there should be no statement with this id -
        // but let's do it, just in case)
        JDBCPreparedStatement dbStat = session.prepareStatement("DELETE FROM " + planTableName + " WHERE STATEMENT_ID=? ");
        try {
            dbStat.setString(1, planStmtId);
            dbStat.execute();
        } finally {
            dbStat.close();
        }
        // Explain plan
        StringBuilder explainSQL = new StringBuilder();
        explainSQL.append("EXPLAIN PLAN ").append("\n").append("SET STATEMENT_ID = '").append(planStmtId).append("'\n").append("INTO ").append(planTableName).append("\n").append("FOR ").append(query);
        dbStat = session.prepareStatement(explainSQL.toString());
        try {
            dbStat.execute();
        } finally {
            dbStat.close();
        }
        // Read explained plan
        dbStat = session.prepareStatement("SELECT * FROM " + planTableName + " WHERE STATEMENT_ID=? ORDER BY ID");
        try {
            dbStat.setString(1, planStmtId);
            try (JDBCResultSet dbResult = dbStat.executeQuery()) {
                rootNodes = new ArrayList<>();
                IntKeyMap<OraclePlanNode> allNodes = new IntKeyMap<>();
                while (dbResult.next()) {
                    OraclePlanNode node = new OraclePlanNode(dataSource, allNodes, dbResult);
                    allNodes.put(node.getId(), node);
                    if (node.getParent() == null) {
                        rootNodes.add(node);
                    }
                }
            }
        } finally {
            dbStat.close();
        }
    } catch (SQLException e) {
        throw new DBCException(e, session.getDataSource());
    }
}
Also used : JDBCPreparedStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCPreparedStatement) SQLException(java.sql.SQLException) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet) IntKeyMap(org.jkiss.utils.IntKeyMap) DBCException(org.jkiss.dbeaver.model.exec.DBCException)

Example 38 with JDBCResultSet

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet in project dbeaver by serge-rider.

the class OracleTablePhysical method getPartitionInfo.

@PropertyGroup
@LazyProperty(cacheValidator = PartitionInfoValidator.class)
public PartitionInfo getPartitionInfo(DBRProgressMonitor monitor) throws DBException {
    if (partitionInfo == null && partitioned) {
        try (final JDBCSession session = DBUtils.openMetaSession(monitor, getDataSource(), "Load partitioning info")) {
            try (JDBCPreparedStatement dbStat = session.prepareStatement("SELECT * FROM ALL_PART_TABLES WHERE OWNER=? AND TABLE_NAME=?")) {
                dbStat.setString(1, getContainer().getName());
                dbStat.setString(2, getName());
                try (JDBCResultSet dbResult = dbStat.executeQuery()) {
                    if (dbResult.next()) {
                        partitionInfo = new PartitionInfo(monitor, this.getDataSource(), dbResult);
                    }
                }
            }
        } catch (SQLException e) {
            throw new DBException(e, getDataSource());
        }
    }
    return partitionInfo;
}
Also used : JDBCPreparedStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCPreparedStatement) JDBCSession(org.jkiss.dbeaver.model.exec.jdbc.JDBCSession) DBException(org.jkiss.dbeaver.DBException) SQLException(java.sql.SQLException) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet)

Example 39 with JDBCResultSet

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet in project dbeaver by serge-rider.

the class ExasolStructureAssistant method searchColumns.

private void searchColumns(JDBCSession session, ExasolSchema schema, String searchObjectNameMask, List<ExasolObjectType> objectTypes, int maxResults, List<DBSObjectReference> objects, int nbResults) throws SQLException, DBException {
    String sql;
    if (schema != null) {
        sql = String.format(SQL_COLS_SCHEMA, ExasolUtils.quoteString(schema.getName()), ExasolUtils.quoteString(searchObjectNameMask));
    } else {
        sql = String.format(SQL_COLS_ALL, ExasolUtils.quoteString(searchObjectNameMask));
    }
    try (JDBCStatement dbStat = session.createStatement()) {
        dbStat.setFetchSize(DBConstants.METADATA_FETCH_SIZE);
        String tableSchemaName;
        String tableOrViewName;
        String columnName;
        ExasolSchema exasolSchema;
        ExasolTable exasolTable;
        try (JDBCResultSet dbResult = dbStat.executeQuery(sql)) {
            while (dbResult.next()) {
                if (session.getProgressMonitor().isCanceled()) {
                    break;
                }
                if (nbResults++ >= maxResults) {
                    return;
                }
                tableSchemaName = JDBCUtils.safeGetStringTrimmed(dbResult, "TABLE_SCHEM");
                tableOrViewName = JDBCUtils.safeGetString(dbResult, "TABLE_NAME");
                columnName = JDBCUtils.safeGetString(dbResult, "COLUMN_NAME");
                exasolSchema = dataSource.getSchema(session.getProgressMonitor(), tableSchemaName);
                if (exasolSchema == null) {
                    LOG.debug("Schema '" + tableSchemaName + "' not found. Probably was filtered");
                    continue;
                }
                // Try with table, then view
                exasolTable = exasolSchema.getTable(session.getProgressMonitor(), tableOrViewName);
                if (exasolTable != null) {
                    objects.add(new ExasolObjectReference(columnName, exasolTable, ExasolObjectType.COLUMN));
                }
            }
        }
    }
}
Also used : JDBCStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet)

Example 40 with JDBCResultSet

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet in project dbeaver by serge-rider.

the class ExasolUtils method generateDDLforTable.

@SuppressWarnings("rawtypes")
public static String generateDDLforTable(DBRProgressMonitor monitor, ExasolDataSource dataSource, ExasolTable exasolTable) throws DBException {
    StringBuilder ddlOutput = new StringBuilder();
    ddlOutput.append("CREATE TABLE \"" + exasolTable.getSchema().getName() + "\".\"" + exasolTable.getName() + "\" (");
    try (JDBCSession session = DBUtils.openMetaSession(monitor, dataSource, "Get Table DDL")) {
        try (JDBCStatement dbStat = session.createStatement()) {
            JDBCResultSet rs = dbStat.executeQuery(String.format(TABLE_QUERY_COLUMNS, quoteString(exasolTable.getSchema().getName()), quoteString(exasolTable.getName())));
            // column infos
            List<String> columns = new ArrayList<String>();
            // distribution key infos
            List<String> distKey = new ArrayList<String>();
            while (rs.next()) {
                StringBuilder columnString = new StringBuilder("");
                // double quotation mark for column as the name could be a
                // reserved word
                columnString.append("\n\t\t\"" + rs.getString("COLUMN_NAME") + "\" " + rs.getString("COLUMN_TYPE") + " ");
                // has default value?
                if (rs.getString("COLUMN_DEFAULT") != null)
                    columnString.append("DEFAULT " + rs.getString("COLUMN_DEFAULT") + " ");
                // has identity
                if (rs.getBigDecimal("COLUMN_IDENTITY") != null)
                    columnString.append("IDENTITY " + rs.getBigDecimal("COLUMN_IDENTITY").toString() + " ");
                // has identity
                if (!rs.getBoolean("COLUMN_IS_NULLABLE"))
                    columnString.append("NOT NULL ");
                // comment
                if (rs.getString("COLUMN_COMMENT") != null)
                    // replace ' to double ' -> escape for SQL
                    columnString.append("COMMENT IS '" + rs.getString("COLUMN_COMMENT").replaceAll("'", "''") + "'");
                // if distkey add column to distkey
                if (rs.getBoolean("COLUMN_IS_DISTRIBUTION_KEY"))
                    distKey.add(rs.getString("COLUMN_NAME"));
                columns.add(columnString.toString());
            }
            ddlOutput.append(CommonUtils.joinStrings(",", columns));
            // do we have a distkey?
            if (distKey.size() > 0) {
                ddlOutput.append(",\n\t\t DISTRIBUTE BY " + CommonUtils.joinStrings(",", distKey));
            }
            ddlOutput.append("\n);\n");
        }
        //primary key
        Collection<ExasolTableUniqueKey> pks = exasolTable.getConstraints(monitor);
        if (pks != null & pks.size() > 0) {
            //get only first as there is only 1 primary key
            ExasolTableUniqueKey pk = null;
            pk = pks.iterator().next();
            ArrayList<String> columns = new ArrayList<String>();
            for (DBSEntityAttributeRef c : pk.getAttributeReferences(monitor)) {
                columns.add("\"" + c.getAttribute().getName() + "\"");
            }
            ddlOutput.append("\nALTER TABLE \"" + exasolTable.getSchema().getName() + "\".\"" + exasolTable.getName() + "\" ADD CONSTRAINT " + pk.getName() + " PRIMARY KEY (" + CommonUtils.joinStrings(",", columns) + ") " + (pk.getEnabled() ? "ENABLE" : "") + " ;\n");
        }
        //foreign key
        Collection<ExasolTableForeignKey> fks = exasolTable.getAssociations(monitor);
        if (fks != null & fks.size() > 0) {
            //look keys
            for (ExasolTableForeignKey fk : fks) {
                ArrayList<String> columns = new ArrayList<String>();
                for (DBSEntityAttributeRef c : fk.getAttributeReferences(monitor)) {
                    columns.add("\"" + c.getAttribute().getName() + "\"");
                }
                ddlOutput.append("\nALTER TABLE \"" + exasolTable.getSchema().getName() + "\".\"" + exasolTable.getName() + "\" ADD CONSTRAINT " + fk.getName() + " FOREIGN KEY (" + CommonUtils.joinStrings(",", columns) + ") REFERENCES \"" + fk.getReferencedTable().getSchema().getName() + "\".\"" + fk.getReferencedTable().getName() + "\" " + (fk.getEnabled() ? "ENABLE" : "") + " ;\n");
            }
        }
        return ddlOutput.toString();
    } catch (SQLException e) {
        throw new DBException(e, dataSource);
    } finally {
        monitor.done();
    }
}
Also used : JDBCSession(org.jkiss.dbeaver.model.exec.jdbc.JDBCSession) DBException(org.jkiss.dbeaver.DBException) SQLException(java.sql.SQLException) ArrayList(java.util.ArrayList) DBSEntityAttributeRef(org.jkiss.dbeaver.model.struct.DBSEntityAttributeRef) JDBCStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet)

Aggregations

JDBCResultSet (org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet)82 JDBCPreparedStatement (org.jkiss.dbeaver.model.exec.jdbc.JDBCPreparedStatement)66 SQLException (java.sql.SQLException)57 JDBCSession (org.jkiss.dbeaver.model.exec.jdbc.JDBCSession)54 DBException (org.jkiss.dbeaver.DBException)53 ArrayList (java.util.ArrayList)20 DBCException (org.jkiss.dbeaver.model.exec.DBCException)15 DBRProgressMonitor (org.jkiss.dbeaver.model.runtime.DBRProgressMonitor)12 JDBCStatement (org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement)10 AbstractObjectReference (org.jkiss.dbeaver.model.impl.struct.AbstractObjectReference)10 DBSObject (org.jkiss.dbeaver.model.struct.DBSObject)8 DBPDataSource (org.jkiss.dbeaver.model.DBPDataSource)5 GenericMetaObject (org.jkiss.dbeaver.ext.generic.model.meta.GenericMetaObject)4 NotNull (org.jkiss.code.NotNull)3 DB2XMLString (org.jkiss.dbeaver.ext.db2.info.DB2XMLString)3 DB2DataSource (org.jkiss.dbeaver.ext.db2.model.DB2DataSource)3 GenericDataSource (org.jkiss.dbeaver.ext.generic.model.GenericDataSource)3 Matcher (java.util.regex.Matcher)2 DB2Parameter (org.jkiss.dbeaver.ext.db2.info.DB2Parameter)2 DB2Schema (org.jkiss.dbeaver.ext.db2.model.DB2Schema)2