Examples with JDBCResultSet org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet used on opensource projects

Example 1 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));
                }
            }
        }
    }
}

Example 2 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();
    }
}

Example 3 with JDBCResultSet

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

the class ExasolTable method read.

private void read(DBRProgressMonitor monitor) throws DBCException {
    JDBCSession session = DBUtils.openMetaSession(monitor, getDataSource(), "Read Table Details");
    try (JDBCStatement stmt = session.createStatement()) {
        String sql = String.format(readAdditionalInfo, ExasolUtils.quoteString(this.getSchema().getName()), ExasolUtils.quoteString(this.getName()), ExasolUtils.quoteString(this.getSchema().getName()), ExasolUtils.quoteString(this.getName()), ExasolUtils.quoteString(this.getSchema().getName()), ExasolUtils.quoteString(this.getName()));
        try (JDBCResultSet dbResult = stmt.executeQuery(sql)) {
            dbResult.next();
            this.hasDistKey = JDBCUtils.safeGetBoolean(dbResult, "TABLE_HAS_DISTRIBUTION_KEY");
            this.lastCommit = JDBCUtils.safeGetTimestamp(dbResult, "LAST_COMMIT");
            this.sizeRaw = JDBCUtils.safeGetLong(dbResult, "RAW_OBJECT_SIZE");
            this.sizeCompressed = JDBCUtils.safeGetLong(dbResult, "MEM_OBJECT_SIZE");
            this.deletePercentage = JDBCUtils.safeGetFloat(dbResult, "DELETE_PERCENTAGE");
            this.createTime = JDBCUtils.safeGetTimestamp(dbResult, "CREATED");
            this.hasRead = true;
        }
    } catch (SQLException e) {
        throw new DBCException(e, getDataSource());
    }
}

Example 4 with JDBCResultSet

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

the class ExasolPlanAnalyser method explain.

public void explain(DBCSession session) throws DBCException {
    rootNodes = new ArrayList<>();
    JDBCSession connection = (JDBCSession) session;
    boolean oldAutoCommit = false;
    try {
        oldAutoCommit = connection.getAutoCommit();
        if (oldAutoCommit)
            connection.setAutoCommit(false);
        //alter session
        JDBCUtils.executeSQL(connection, "ALTER SESSION SET PROFILE = 'ON'");
        //execute query
        JDBCUtils.executeSQL(connection, query);
        //alter session
        JDBCUtils.executeSQL(connection, "ALTER SESSION SET PROFILE = 'OFF'");
        //rollback in case of DML
        connection.rollback();
        //alter session
        JDBCUtils.executeSQL(connection, "FLUSH STATISTICS");
        connection.commit();
        //retrieve execute info
        try (JDBCPreparedStatement stmt = connection.prepareStatement("SELECT * FROM EXA_USER_PROFILE_LAST_DAY WHERE SESSION_ID = CURRENT_SESSION AND STMT_ID = (select max(stmt_id) from EXA_USER_PROFILE_LAST_DAY where sql_text = ?)")) {
            stmt.setString(1, query);
            try (JDBCResultSet dbResult = stmt.executeQuery()) {
                while (dbResult.next()) {
                    ExasolPlanNode node = new ExasolPlanNode(null, dbResult);
                    rootNodes.add(node);
                }
            }
        }
    } catch (SQLException e) {
        throw new DBCException(e, session.getDataSource());
    } finally {
        //rollback changes because profile actually executes query and it could be INSERT/UPDATE
        try {
            connection.rollback();
            if (oldAutoCommit)
                connection.setAutoCommit(true);
        } catch (SQLException e) {
            LOG.error("Error closing plan analyser", e);
        }
    }
}

Example 5 with JDBCResultSet

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

the class GenericProcedure method loadProcedureColumns.

private void loadProcedureColumns(DBRProgressMonitor monitor) throws DBException {
    Collection<? extends GenericProcedure> procedures = getContainer().getProcedures(monitor, getName());
    if (procedures == null || !procedures.contains(this)) {
        throw new DBException("Internal error - cannot read columns for procedure '" + getName() + "' because its not found in container");
    }
    Iterator<? extends GenericProcedure> procIter = procedures.iterator();
    GenericProcedure procedure = null;
    final GenericMetaObject pcObject = getDataSource().getMetaObject(GenericConstants.OBJECT_PROCEDURE_COLUMN);
    try (JDBCSession session = DBUtils.openMetaSession(monitor, getDataSource(), "Load procedure columns")) {
        final JDBCResultSet dbResult;
        if (functionResultType == null) {
            dbResult = session.getMetaData().getProcedureColumns(getCatalog() == null ? this.getPackage() == null || !this.getPackage().isNameFromCatalog() ? null : this.getPackage().getName() : getCatalog().getName(), getSchema() == null ? null : getSchema().getName(), getName(), getDataSource().getAllObjectsPattern());
        } else {
            dbResult = session.getMetaData().getFunctionColumns(getCatalog() == null ? null : getCatalog().getName(), getSchema() == null ? null : getSchema().getName(), getName(), getDataSource().getAllObjectsPattern());
        }
        try {
            int previousPosition = -1;
            while (dbResult.next()) {
                String columnName = GenericUtils.safeGetString(pcObject, dbResult, JDBCConstants.COLUMN_NAME);
                int columnTypeNum = GenericUtils.safeGetInt(pcObject, dbResult, JDBCConstants.COLUMN_TYPE);
                int valueType = GenericUtils.safeGetInt(pcObject, dbResult, JDBCConstants.DATA_TYPE);
                String typeName = GenericUtils.safeGetString(pcObject, dbResult, JDBCConstants.TYPE_NAME);
                int columnSize = GenericUtils.safeGetInt(pcObject, dbResult, JDBCConstants.LENGTH);
                boolean notNull = GenericUtils.safeGetInt(pcObject, dbResult, JDBCConstants.NULLABLE) == DatabaseMetaData.procedureNoNulls;
                int scale = GenericUtils.safeGetInt(pcObject, dbResult, JDBCConstants.SCALE);
                int precision = GenericUtils.safeGetInt(pcObject, dbResult, JDBCConstants.PRECISION);
                //int radix = GenericUtils.safeGetInt(dbResult, JDBCConstants.RADIX);
                String remarks = GenericUtils.safeGetString(pcObject, dbResult, JDBCConstants.REMARKS);
                int position = GenericUtils.safeGetInt(pcObject, dbResult, JDBCConstants.ORDINAL_POSITION);
                DBSProcedureParameterKind parameterType;
                if (functionResultType == null) {
                    switch(columnTypeNum) {
                        case DatabaseMetaData.procedureColumnIn:
                            parameterType = DBSProcedureParameterKind.IN;
                            break;
                        case DatabaseMetaData.procedureColumnInOut:
                            parameterType = DBSProcedureParameterKind.INOUT;
                            break;
                        case DatabaseMetaData.procedureColumnOut:
                            parameterType = DBSProcedureParameterKind.OUT;
                            break;
                        case DatabaseMetaData.procedureColumnReturn:
                            parameterType = DBSProcedureParameterKind.RETURN;
                            break;
                        case DatabaseMetaData.procedureColumnResult:
                            parameterType = DBSProcedureParameterKind.RESULTSET;
                            break;
                        default:
                            parameterType = DBSProcedureParameterKind.UNKNOWN;
                            break;
                    }
                } else {
                    switch(columnTypeNum) {
                        case DatabaseMetaData.functionColumnIn:
                            parameterType = DBSProcedureParameterKind.IN;
                            break;
                        case DatabaseMetaData.functionColumnInOut:
                            parameterType = DBSProcedureParameterKind.INOUT;
                            break;
                        case DatabaseMetaData.functionColumnOut:
                            parameterType = DBSProcedureParameterKind.OUT;
                            break;
                        case DatabaseMetaData.functionReturn:
                            parameterType = DBSProcedureParameterKind.RETURN;
                            break;
                        case DatabaseMetaData.functionColumnResult:
                            parameterType = DBSProcedureParameterKind.RESULTSET;
                            break;
                        default:
                            parameterType = DBSProcedureParameterKind.UNKNOWN;
                            break;
                    }
                }
                if (CommonUtils.isEmpty(columnName) && parameterType == DBSProcedureParameterKind.RETURN) {
                    columnName = "RETURN";
                }
                if (position == 0) {
                    // Some drivers do not return ordinal position (PostgreSQL) but
                    // position is contained in column name
                    Matcher numberMatcher = PATTERN_COL_NAME_NUMERIC.matcher(columnName);
                    if (numberMatcher.matches()) {
                        position = Integer.parseInt(numberMatcher.group(1));
                    }
                }
                if (procedure == null || (previousPosition >= 0 && position <= previousPosition && procIter.hasNext())) {
                    procedure = procIter.next();
                }
                GenericProcedureParameter column = new GenericProcedureParameter(procedure, columnName, typeName, valueType, position, columnSize, scale, precision, notNull, remarks, parameterType);
                procedure.addColumn(column);
                previousPosition = position;
            }
        } finally {
            dbResult.close();
        }
    } catch (SQLException e) {
        throw new DBException(e, getDataSource());
    }
}