Search in sources :

Example 51 with JDBCResultSet

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

the class OracleTableBase method loadColumnComments.

void loadColumnComments(DBRProgressMonitor monitor) {
    try {
        try (JDBCSession session = DBUtils.openMetaSession(monitor, getDataSource(), "Load table column comments")) {
            try (JDBCPreparedStatement stat = session.prepareStatement("SELECT COLUMN_NAME,COMMENTS FROM SYS.ALL_COL_COMMENTS cc WHERE CC.OWNER=? AND cc.TABLE_NAME=?")) {
                stat.setString(1, getSchema().getName());
                stat.setString(2, getName());
                try (JDBCResultSet resultSet = stat.executeQuery()) {
                    while (resultSet.next()) {
                        String colName = resultSet.getString(1);
                        String colComment = resultSet.getString(2);
                        OracleTableColumn col = getAttribute(monitor, colName);
                        if (col == null) {
                            log.warn("Column '" + colName + "' not found in table '" + getFullyQualifiedName(DBPEvaluationContext.DDL) + "'");
                        } else {
                            col.setComment(CommonUtils.notEmpty(colComment));
                        }
                    }
                }
            }
        }
        for (OracleTableColumn col : getAttributes(monitor)) {
            col.cacheComment();
        }
    } catch (Exception e) {
        log.warn("Error fetching table '" + getName() + "' column comments", e);
    }
}
Also used : JDBCPreparedStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCPreparedStatement) JDBCSession(org.jkiss.dbeaver.model.exec.jdbc.JDBCSession) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet) SQLException(java.sql.SQLException) DBException(org.jkiss.dbeaver.DBException)

Example 52 with JDBCResultSet

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

the class MySQLStructureAssistant method findConstraintsByMask.

private void findConstraintsByMask(JDBCSession session, @Nullable final MySQLCatalog catalog, String constrNameMask, int maxResults, List<DBSObjectReference> objects) throws SQLException, DBException {
    DBRProgressMonitor monitor = session.getProgressMonitor();
    // Load constraints
    try (JDBCPreparedStatement dbStat = session.prepareStatement("SELECT " + MySQLConstants.COL_TABLE_SCHEMA + "," + MySQLConstants.COL_TABLE_NAME + "," + MySQLConstants.COL_CONSTRAINT_NAME + "," + MySQLConstants.COL_CONSTRAINT_TYPE + " FROM " + MySQLConstants.META_TABLE_TABLE_CONSTRAINTS + " WHERE " + MySQLConstants.COL_CONSTRAINT_NAME + " LIKE ? " + (catalog == null ? "" : " AND " + MySQLConstants.COL_TABLE_SCHEMA + "=?") + " ORDER BY " + MySQLConstants.COL_CONSTRAINT_NAME + " LIMIT " + maxResults)) {
        dbStat.setString(1, constrNameMask.toLowerCase(Locale.ENGLISH));
        if (catalog != null) {
            dbStat.setString(2, catalog.getName());
        }
        try (JDBCResultSet dbResult = dbStat.executeQuery()) {
            int tableNum = maxResults;
            while (dbResult.next() && tableNum-- > 0) {
                if (monitor.isCanceled()) {
                    break;
                }
                final String catalogName = JDBCUtils.safeGetString(dbResult, MySQLConstants.COL_TABLE_SCHEMA);
                final String tableName = JDBCUtils.safeGetString(dbResult, MySQLConstants.COL_TABLE_NAME);
                final String constrName = JDBCUtils.safeGetString(dbResult, MySQLConstants.COL_CONSTRAINT_NAME);
                final String constrType = JDBCUtils.safeGetString(dbResult, MySQLConstants.COL_CONSTRAINT_TYPE);
                final boolean isFK = MySQLConstants.CONSTRAINT_FOREIGN_KEY.equals(constrType);
                objects.add(new AbstractObjectReference(constrName, dataSource.getCatalog(catalogName), null, isFK ? MySQLTableForeignKey.class : MySQLTableConstraint.class, RelationalObjectType.TYPE_CONSTRAINT) {

                    @Override
                    public DBSObject resolveObject(DBRProgressMonitor monitor) throws DBException {
                        MySQLCatalog tableCatalog = catalog != null ? catalog : dataSource.getCatalog(catalogName);
                        if (tableCatalog == null) {
                            throw new DBException("Constraint catalog '" + catalogName + "' not found");
                        }
                        MySQLTable table = tableCatalog.getTable(monitor, tableName);
                        if (table == null) {
                            throw new DBException("Constraint table '" + tableName + "' not found in catalog '" + tableCatalog.getName() + "'");
                        }
                        DBSObject constraint;
                        if (isFK) {
                            constraint = table.getAssociation(monitor, constrName);
                        } else {
                            constraint = table.getConstraint(monitor, constrName);
                        }
                        if (constraint == null) {
                            throw new DBException("Constraint '" + constrName + "' not found in table '" + table.getFullyQualifiedName(DBPEvaluationContext.DDL) + "'");
                        }
                        return constraint;
                    }
                });
            }
        }
    }
}
Also used : JDBCPreparedStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCPreparedStatement) DBException(org.jkiss.dbeaver.DBException) DBSObject(org.jkiss.dbeaver.model.struct.DBSObject) AbstractObjectReference(org.jkiss.dbeaver.model.impl.struct.AbstractObjectReference) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet) DBRProgressMonitor(org.jkiss.dbeaver.model.runtime.DBRProgressMonitor)

Example 53 with JDBCResultSet

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

the class MySQLStructureAssistant method findProceduresByMask.

private void findProceduresByMask(JDBCSession session, @Nullable final MySQLCatalog catalog, String procNameMask, int maxResults, List<DBSObjectReference> objects) throws SQLException, DBException {
    DBRProgressMonitor monitor = session.getProgressMonitor();
    // Load procedures
    try (JDBCPreparedStatement dbStat = session.prepareStatement("SELECT " + MySQLConstants.COL_ROUTINE_SCHEMA + "," + MySQLConstants.COL_ROUTINE_NAME + " FROM " + MySQLConstants.META_TABLE_ROUTINES + " WHERE " + MySQLConstants.COL_ROUTINE_NAME + " LIKE ? " + (catalog == null ? "" : " AND " + MySQLConstants.COL_ROUTINE_SCHEMA + "=?") + " ORDER BY " + MySQLConstants.COL_ROUTINE_NAME + " LIMIT " + maxResults)) {
        dbStat.setString(1, procNameMask.toLowerCase(Locale.ENGLISH));
        if (catalog != null) {
            dbStat.setString(2, catalog.getName());
        }
        try (JDBCResultSet dbResult = dbStat.executeQuery()) {
            int tableNum = maxResults;
            while (dbResult.next() && tableNum-- > 0) {
                if (monitor.isCanceled()) {
                    break;
                }
                final String catalogName = JDBCUtils.safeGetString(dbResult, MySQLConstants.COL_ROUTINE_SCHEMA);
                final String procName = JDBCUtils.safeGetString(dbResult, MySQLConstants.COL_ROUTINE_NAME);
                objects.add(new AbstractObjectReference(procName, dataSource.getCatalog(catalogName), null, MySQLProcedure.class, RelationalObjectType.TYPE_PROCEDURE) {

                    @Override
                    public DBSObject resolveObject(DBRProgressMonitor monitor) throws DBException {
                        MySQLCatalog procCatalog = catalog != null ? catalog : dataSource.getCatalog(catalogName);
                        if (procCatalog == null) {
                            throw new DBException("Procedure catalog '" + catalogName + "' not found");
                        }
                        MySQLProcedure procedure = procCatalog.getProcedure(monitor, procName);
                        if (procedure == null) {
                            throw new DBException("Procedure '" + procName + "' not found in catalog '" + procCatalog.getName() + "'");
                        }
                        return procedure;
                    }
                });
            }
        }
    }
}
Also used : JDBCPreparedStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCPreparedStatement) DBException(org.jkiss.dbeaver.DBException) DBSObject(org.jkiss.dbeaver.model.struct.DBSObject) AbstractObjectReference(org.jkiss.dbeaver.model.impl.struct.AbstractObjectReference) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet) DBRProgressMonitor(org.jkiss.dbeaver.model.runtime.DBRProgressMonitor)

Example 54 with JDBCResultSet

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

the class MySQLStructureAssistant method findTablesByMask.

private void findTablesByMask(JDBCSession session, @Nullable final MySQLCatalog catalog, String tableNameMask, int maxResults, List<DBSObjectReference> objects) throws SQLException, DBException {
    DBRProgressMonitor monitor = session.getProgressMonitor();
    // Load tables
    try (JDBCPreparedStatement dbStat = session.prepareStatement("SELECT " + MySQLConstants.COL_TABLE_SCHEMA + "," + MySQLConstants.COL_TABLE_NAME + " FROM " + MySQLConstants.META_TABLE_TABLES + " WHERE " + MySQLConstants.COL_TABLE_NAME + " LIKE ? " + (catalog == null ? "" : " AND " + MySQLConstants.COL_TABLE_SCHEMA + "=?") + " ORDER BY " + MySQLConstants.COL_TABLE_NAME + " LIMIT " + maxResults)) {
        dbStat.setString(1, tableNameMask.toLowerCase(Locale.ENGLISH));
        if (catalog != null) {
            dbStat.setString(2, catalog.getName());
        }
        try (JDBCResultSet dbResult = dbStat.executeQuery()) {
            int tableNum = maxResults;
            while (dbResult.next() && tableNum-- > 0) {
                if (monitor.isCanceled()) {
                    break;
                }
                final String catalogName = JDBCUtils.safeGetString(dbResult, MySQLConstants.COL_TABLE_SCHEMA);
                final String tableName = JDBCUtils.safeGetString(dbResult, MySQLConstants.COL_TABLE_NAME);
                objects.add(new AbstractObjectReference(tableName, dataSource.getCatalog(catalogName), null, MySQLTableBase.class, RelationalObjectType.TYPE_TABLE) {

                    @Override
                    public DBSObject resolveObject(DBRProgressMonitor monitor) throws DBException {
                        MySQLCatalog tableCatalog = catalog != null ? catalog : dataSource.getCatalog(catalogName);
                        if (tableCatalog == null) {
                            throw new DBException("Table catalog '" + catalogName + "' not found");
                        }
                        MySQLTableBase table = tableCatalog.getTableCache().getObject(monitor, tableCatalog, tableName);
                        if (table == null) {
                            throw new DBException("Table '" + tableName + "' not found in catalog '" + catalogName + "'");
                        }
                        return table;
                    }
                });
            }
        }
    }
}
Also used : JDBCPreparedStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCPreparedStatement) DBException(org.jkiss.dbeaver.DBException) DBSObject(org.jkiss.dbeaver.model.struct.DBSObject) AbstractObjectReference(org.jkiss.dbeaver.model.impl.struct.AbstractObjectReference) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet) DBRProgressMonitor(org.jkiss.dbeaver.model.runtime.DBRProgressMonitor)

Example 55 with JDBCResultSet

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

the class OracleUtils method getDDL.

public static String getDDL(DBRProgressMonitor monitor, String objectType, DBSEntity object, OracleDDLFormat ddlFormat) throws DBException {
    String objectFullName = DBUtils.getObjectFullName(object, DBPEvaluationContext.DDL);
    OracleSchema schema = null;
    if (object instanceof OracleSchemaObject) {
        schema = ((OracleSchemaObject) object).getSchema();
    } else if (object instanceof OracleTableBase) {
        schema = ((OracleTableBase) object).getContainer();
    }
    final OracleDataSource dataSource = (OracleDataSource) object.getDataSource();
    monitor.beginTask("Load sources for " + objectType + " '" + objectFullName + "'...", 1);
    try (final JDBCSession session = DBUtils.openMetaSession(monitor, dataSource, "Load source code for " + objectType + " '" + objectFullName + "'")) {
        if (dataSource.isAtLeastV9()) {
            JDBCUtils.executeProcedure(session, "begin DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE'," + ddlFormat.isShowStorage() + "); end;");
            JDBCUtils.executeProcedure(session, "begin DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE'," + ddlFormat.isShowTablespace() + ");  end;");
            JDBCUtils.executeProcedure(session, "begin DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES'," + ddlFormat.isShowSegments() + ");  end;");
        }
        try (JDBCPreparedStatement dbStat = session.prepareStatement("SELECT DBMS_METADATA.GET_DDL(?,?" + (schema == null ? "" : ",?") + ") TXT " + "FROM DUAL")) {
            dbStat.setString(1, objectType);
            dbStat.setString(2, object.getName());
            if (schema != null) {
                dbStat.setString(3, schema.getName());
            }
            try (JDBCResultSet dbResult = dbStat.executeQuery()) {
                if (dbResult.next()) {
                    return dbResult.getString(1);
                } else {
                    log.warn("No DDL for " + objectType + " '" + objectFullName + "'");
                    return "-- EMPTY DDL";
                }
            }
        } finally {
        /*
                if (curSchema != null) {
                    setCurrentSchema(session, curSchema);
                }
*/
        }
    } catch (SQLException e) {
        if (object instanceof OracleTableBase) {
            log.error("Error generating Oracle DDL. Generate default.", e);
            return JDBCUtils.generateTableDDL(monitor, (OracleTableBase) object, true);
        } else {
            throw new DBException(e, dataSource);
        }
    } finally {
        monitor.done();
    }
}
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)

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