Search in sources :

Example 1 with JDBCStatement

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement 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 2 with JDBCStatement

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement in project dbeaver by dbeaver.

the class JDBCTable method readData.

@NotNull
@Override
public DBCStatistics readData(@NotNull DBCExecutionSource source, @NotNull DBCSession session, @NotNull DBDDataReceiver dataReceiver, @Nullable DBDDataFilter dataFilter, long firstRow, long maxRows, long flags) throws DBCException {
    DBCStatistics statistics = new DBCStatistics();
    boolean hasLimits = firstRow >= 0 && maxRows > 0;
    DBPDataSource dataSource = session.getDataSource();
    DBRProgressMonitor monitor = session.getProgressMonitor();
    try {
        readRequiredMeta(monitor);
    } catch (DBException e) {
        log.warn(e);
    }
    DBDPseudoAttribute rowIdAttribute = (flags & FLAG_READ_PSEUDO) != 0 ? DBUtils.getRowIdAttribute(this) : null;
    // Always use alias if we have criteria or ROWID.
    // Some criteria doesn't work without alias
    // (e.g. structured attributes in Oracle requires table alias)
    String tableAlias = null;
    if ((dataFilter != null && dataFilter.hasConditions()) || rowIdAttribute != null) {
        if (dataSource instanceof SQLDataSource) {
            if (((SQLDataSource) dataSource).getSQLDialect().supportsAliasInSelect()) {
                tableAlias = DEFAULT_TABLE_ALIAS;
            }
        }
    }
    if (rowIdAttribute != null && tableAlias == null) {
        log.warn("Can't query ROWID - table alias not supported");
        rowIdAttribute = null;
    }
    StringBuilder query = new StringBuilder(100);
    query.append("SELECT ");
    appendSelectSource(monitor, query, tableAlias, rowIdAttribute);
    query.append(" FROM ").append(getFullyQualifiedName(DBPEvaluationContext.DML));
    if (tableAlias != null) {
        // $NON-NLS-1$
        query.append(" ").append(tableAlias);
    }
    appendQueryConditions(query, tableAlias, dataFilter);
    appendQueryOrder(query, tableAlias, dataFilter);
    String sqlQuery = query.toString();
    statistics.setQueryText(sqlQuery);
    statistics.addStatementsCount();
    monitor.subTask(ModelMessages.model_jdbc_fetch_table_data);
    try (DBCStatement dbStat = DBUtils.makeStatement(source, session, DBCStatementType.SCRIPT, sqlQuery, firstRow, maxRows)) {
        if (monitor.isCanceled()) {
            return statistics;
        }
        if (dbStat instanceof JDBCStatement && maxRows > 0) {
            boolean useFetchSize = getDataSource().getContainer().getPreferenceStore().getBoolean(ModelPreferences.RESULT_SET_USE_FETCH_SIZE);
            if (useFetchSize) {
                try {
                    ((JDBCStatement) dbStat).setFetchSize(firstRow < 0 || maxRows <= 0 ? DEFAULT_READ_FETCH_SIZE : (int) (firstRow + maxRows));
                } catch (Exception e) {
                    log.warn(e);
                }
            }
        }
        long startTime = System.currentTimeMillis();
        boolean executeResult = dbStat.executeStatement();
        statistics.setExecuteTime(System.currentTimeMillis() - startTime);
        if (executeResult) {
            DBCResultSet dbResult = dbStat.openResultSet();
            if (dbResult != null && !monitor.isCanceled()) {
                try {
                    dataReceiver.fetchStart(session, dbResult, firstRow, maxRows);
                    startTime = System.currentTimeMillis();
                    long rowCount = 0;
                    while (dbResult.nextRow()) {
                        if (monitor.isCanceled() || (hasLimits && rowCount >= maxRows)) {
                            // Fetch not more than max rows
                            break;
                        }
                        dataReceiver.fetchRow(session, dbResult);
                        rowCount++;
                        if (rowCount % 100 == 0) {
                            monitor.subTask(rowCount + ModelMessages.model_jdbc__rows_fetched);
                            monitor.worked(100);
                        }
                    }
                    statistics.setFetchTime(System.currentTimeMillis() - startTime);
                    statistics.setRowsFetched(rowCount);
                } finally {
                    // First - close cursor
                    try {
                        dbResult.close();
                    } catch (Throwable e) {
                        // $NON-NLS-1$
                        log.error("Error closing result set", e);
                    }
                    // Then - signal that fetch was ended
                    try {
                        dataReceiver.fetchEnd(session, dbResult);
                    } catch (Throwable e) {
                        // $NON-NLS-1$
                        log.error("Error while finishing result set fetch", e);
                    }
                }
            }
        }
        return statistics;
    } finally {
        dataReceiver.close();
    }
}
Also used : DBException(org.jkiss.dbeaver.DBException) DBPDataSource(org.jkiss.dbeaver.model.DBPDataSource) SQLDataSource(org.jkiss.dbeaver.model.sql.SQLDataSource) JDBCStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement) DBException(org.jkiss.dbeaver.DBException) JDBCStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement) DBRProgressMonitor(org.jkiss.dbeaver.model.runtime.DBRProgressMonitor) NotNull(org.jkiss.code.NotNull)

Example 3 with JDBCStatement

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement in project dbeaver by dbeaver.

the class ExasolStructureAssistant method searchTables.

// --------------
// Helper Classes
// --------------
private void searchTables(JDBCSession session, ExasolSchema schema, String searchObjectNameMask, List<ExasolObjectType> exasolObjectTypes, int maxResults, List<DBSObjectReference> objects, int nbResults) throws SQLException, DBException {
    String sql;
    if (schema != null) {
        sql = String.format(SQL_TABLES_SCHEMA, ExasolUtils.quoteString(schema.getName()), ExasolUtils.quoteString(searchObjectNameMask), buildTableTypes(exasolObjectTypes));
    } else {
        sql = String.format(SQL_TABLES_ALL, ExasolUtils.quoteString(searchObjectNameMask), buildTableTypes(exasolObjectTypes));
    }
    try (JDBCStatement dbStat = session.createStatement()) {
        dbStat.setFetchSize(DBConstants.METADATA_FETCH_SIZE);
        String schemaName;
        String objectName;
        ExasolSchema exasolSchema;
        ExasolObjectType objectType;
        try (JDBCResultSet dbResult = dbStat.executeQuery(sql)) {
            while (dbResult.next()) {
                if (session.getProgressMonitor().isCanceled()) {
                    break;
                }
                if (nbResults++ >= maxResults) {
                    break;
                }
                schemaName = JDBCUtils.safeGetStringTrimmed(dbResult, "TABLE_SCHEM");
                objectName = JDBCUtils.safeGetString(dbResult, "TABLE_NAME");
                exasolSchema = dataSource.getSchema(session.getProgressMonitor(), schemaName);
                if (exasolSchema == null) {
                    LOG.debug("Schema '" + schemaName + "' not found. Probably was filtered");
                    continue;
                }
                objectType = ExasolObjectType.TABLE;
                objects.add(new ExasolObjectReference(objectName, exasolSchema, objectType));
            }
        }
    }
}
Also used : ExasolObjectType(org.jkiss.dbeaver.ext.exasol.editors.ExasolObjectType) JDBCStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet)

Example 4 with JDBCStatement

use of org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement in project dbeaver by dbeaver.

the class TableToolDialog method getScriptListener.

@Override
protected SQLScriptProgressListener<PostgreObject> getScriptListener() {
    return new SQLScriptStatusDialog<PostgreObject>(getTitle() + " progress", null) {

        @Override
        protected void createStatusColumns(Tree objectTree) {
            TreeColumn msgColumn = new TreeColumn(objectTree, SWT.NONE);
            msgColumn.setText("Message");
        }

        @Override
        public void processObjectResults(@NotNull PostgreObject object, @Nullable DBCStatement statement, @Nullable DBCResultSet resultSet) throws DBCException {
            if (statement == null) {
                return;
            }
            TreeItem treeItem = getTreeItem(object);
            if (treeItem != null) {
                try {
                    int warnNum = 0;
                    SQLWarning warning = ((JDBCStatement) statement).getWarnings();
                    while (warning != null) {
                        if (warnNum == 0) {
                            treeItem.setText(1, warning.getMessage());
                        } else {
                            TreeItem warnItem = new TreeItem(treeItem, SWT.NONE);
                            warnItem.setText(0, "");
                            warnItem.setText(1, warning.getMessage());
                        }
                        warnNum++;
                        warning = warning.getNextWarning();
                    }
                    if (warnNum == 0) {
                        treeItem.setText(1, "Done");
                    }
                } catch (SQLException e) {
                // ignore
                }
                treeItem.setExpanded(true);
            }
        }

        @Override
        public void endObjectProcessing(@NotNull PostgreObject object, Exception error) {
            super.endObjectProcessing(object, error);
            if (error != null) {
                TreeItem treeItem = getTreeItem(object);
                if (treeItem != null) {
                    treeItem.setText(1, error.getMessage());
                }
            }
        }
    };
}
Also used : SQLWarning(java.sql.SQLWarning) TreeItem(org.eclipse.swt.widgets.TreeItem) SQLException(java.sql.SQLException) NotNull(org.jkiss.code.NotNull) DBCStatement(org.jkiss.dbeaver.model.exec.DBCStatement) JDBCStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement) SQLScriptStatusDialog(org.jkiss.dbeaver.ui.dialogs.sql.SQLScriptStatusDialog) DBCException(org.jkiss.dbeaver.model.exec.DBCException) SQLException(java.sql.SQLException) JDBCStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement) TreeColumn(org.eclipse.swt.widgets.TreeColumn) Tree(org.eclipse.swt.widgets.Tree) PostgreObject(org.jkiss.dbeaver.ext.postgresql.model.PostgreObject) DBCResultSet(org.jkiss.dbeaver.model.exec.DBCResultSet) Nullable(org.jkiss.code.Nullable)

Example 5 with JDBCStatement

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

the class ExasolUtils method generateDDLforTable.

public static String generateDDLforTable(DBRProgressMonitor monitor, ExasolDataSource dataSource, ExasolTable exasolTable) throws DBException {
    StringBuilder ddlOutput = new StringBuilder();
    ddlOutput.append("CREATE TABLE ").append(exasolTable.getFullyQualifiedName(DBPEvaluationContext.DDL)).append(" (");
    try (JDBCSession session = DBUtils.openMetaSession(monitor, exasolTable, "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<>();
            // distribution key infos
            List<String> distKey = new ArrayList<>();
            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").append(DBUtils.getQuotedIdentifier(dataSource, CommonUtils.notEmpty(rs.getString("COLUMN_NAME")))).append(" ").append(rs.getString("COLUMN_TYPE"));
                // has default value?
                String columnDefault = rs.getString("COLUMN_DEFAULT");
                if (columnDefault != null)
                    columnString.append(" DEFAULT ").append(columnDefault);
                // has identity
                BigDecimal bigDecimal = rs.getBigDecimal("COLUMN_IDENTITY");
                if (bigDecimal != null)
                    columnString.append(" IDENTITY ").append(bigDecimal.toString());
                // has identity
                if (!rs.getBoolean("COLUMN_IS_NULLABLE"))
                    columnString.append(" NOT NULL");
                // comment
                String columnComment = rs.getString("COLUMN_COMMENT");
                if (columnComment != null)
                    // replace ' to double ' -> escape for SQL
                    columnString.append(" COMMENT IS '").append(columnComment.replaceAll("'", "''")).append("'");
                // 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 ").append(CommonUtils.joinStrings(",", distKey));
            }
            ddlOutput.append("\n);\n");
        }
        // partitioning
        ddlOutput.append(getPartitionDdl(exasolTable, monitor));
        // ddlOutput.append(";\n"); //partition expression has ; already
        // 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;
            pk = pks.iterator().next();
            ddlOutput.append("\n").append(getPKDdl(pk, monitor)).append(";\n");
        }
        // foreign key
        Collection<ExasolTableForeignKey> fks = exasolTable.getAssociations(monitor);
        if (fks != null && fks.size() > 0) {
            // look keys
            for (ExasolTableForeignKey fk : fks) {
                ddlOutput.append("\n").append(getFKDdl(fk, monitor)).append(";\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) BigDecimal(java.math.BigDecimal) JDBCStatement(org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement) JDBCResultSet(org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet)

Aggregations

JDBCStatement (org.jkiss.dbeaver.model.exec.jdbc.JDBCStatement)70 JDBCResultSet (org.jkiss.dbeaver.model.exec.jdbc.JDBCResultSet)40 SQLException (java.sql.SQLException)32 JDBCSession (org.jkiss.dbeaver.model.exec.jdbc.JDBCSession)26 DBException (org.jkiss.dbeaver.DBException)24 NotNull (org.jkiss.code.NotNull)18 ArrayList (java.util.ArrayList)16 DBPDataSource (org.jkiss.dbeaver.model.DBPDataSource)12 DBRProgressMonitor (org.jkiss.dbeaver.model.runtime.DBRProgressMonitor)12 DBCException (org.jkiss.dbeaver.model.exec.DBCException)10 SQLWarning (java.sql.SQLWarning)8 AbstractObjectReference (org.jkiss.dbeaver.model.impl.struct.AbstractObjectReference)8 DBSObject (org.jkiss.dbeaver.model.struct.DBSObject)8 JDBCStatementImpl (org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl)6 VoidProgressMonitor (org.jkiss.dbeaver.model.runtime.VoidProgressMonitor)4 BigDecimal (java.math.BigDecimal)2 SQLFeatureNotSupportedException (java.sql.SQLFeatureNotSupportedException)2 SQLXML (java.sql.SQLXML)2 Tree (org.eclipse.swt.widgets.Tree)2 TreeColumn (org.eclipse.swt.widgets.TreeColumn)2