Examples with QueryColumnValue com.servoy.j2db.query.QueryColumnValue used on opensource projects

Example 1 with QueryColumnValue

use of com.servoy.j2db.query.QueryColumnValue in project servoy-client by Servoy.

the class Messages method fillLocaleMessages.

private static void fillLocaleMessages(String clientId, IDataServer dataServer, Table table, String serverName, Column filterColumn, Object columnValueFilter, String searchKey, String searchText, Locale language, Properties properties, int loadingType, IFoundSetManagerInternal fm) throws ServoyException, RemoteException {
    QueryTable messagesTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
    QuerySelect sql = new QuerySelect(messagesTable);
    // $NON-NLS-1$
    QueryColumn msgKey = new QueryColumn(messagesTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
    // $NON-NLS-1$
    QueryColumn msgVal = new QueryColumn(messagesTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
    // $NON-NLS-1$
    QueryColumn msgLang = new QueryColumn(messagesTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
    sql.addColumn(msgKey);
    sql.addColumn(msgVal);
    // $NON-NLS-1$
    String condMessages = "MESSAGES";
    String langValue = (loadingType == SPECIFIED_LOCALE) ? localeToString(language) : language.getLanguage();
    // default
    sql.addCondition(condMessages, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgLang, new QueryColumnValue(langValue, null)));
    if (filterColumn != null) {
        CompareCondition cc = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, filterColumn.queryColumn(messagesTable), new QueryColumnValue(columnValueFilter, null));
        sql.addCondition(condMessages, cc);
    }
    // Filter to only include records with the default (null) value for columns flagged as Tenant column
    for (Column column : table.getTenantColumns()) {
        CompareCondition cc = new CompareCondition(IBaseSQLCondition.ISNULL_OPERATOR, column.queryColumn(messagesTable), null);
        sql.addCondition("_svy_tenant_id_filter_" + column.getName(), cc);
    }
    if (searchKey != null || searchText != null) {
        QueryTable subselectTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
        QuerySelect subselect = new QuerySelect(subselectTable);
        // $NON-NLS-1$
        QueryColumn msgKeySub = new QueryColumn(subselectTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
        // $NON-NLS-1$
        QueryColumn msgValueSub = new QueryColumn(subselectTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
        // $NON-NLS-1$
        QueryColumn msgLangSub = new QueryColumn(subselectTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
        subselect.addColumn(msgKeySub);
        // $NON-NLS-1$
        String condSearch = "SEARCH";
        if (searchKey != null) {
            subselect.addCondition(condSearch, new CompareCondition(IBaseSQLCondition.LIKE_OPERATOR, msgKeySub, new QueryColumnValue('%' + searchKey + '%', null)));
        }
        if (searchText != null) {
            subselect.addConditionOr(condSearch, new CompareCondition(IBaseSQLCondition.LIKE_OPERATOR, msgValueSub, new QueryColumnValue('%' + searchText + '%', null)));
        }
        sql.addCondition(condMessages, new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, new QueryColumn[] { msgKey }, subselect, true));
    }
    // $NON-NLS-1$
    if (Debug.tracing())
        Debug.trace("Loading messages from DB: SQL: " + sql);
    IDataSet set = dataServer.performQuery(clientId, serverName, null, sql, null, fm != null ? fm.getTableFilterParams(serverName, sql) : null, false, 0, Integer.MAX_VALUE, IDataServer.MESSAGES_QUERY);
    for (int i = 0; i < set.getRowCount(); i++) {
        Object[] row = set.getRow(i);
        if (// $NON-NLS-1$
        row[1] != null && !"".equals(row[1])) {
            properties.setProperty((String) row[0], (String) row[1]);
        }
    }
}

Example 2 with QueryColumnValue

use of com.servoy.j2db.query.QueryColumnValue in project servoy-client by Servoy.

the class QBSelect method createOperand.

IQuerySelectValue createOperand(Object value, BaseColumnType columnType, int flags) {
    if (value instanceof QBColumn) {
        return ((QBColumn) value).getQuerySelectValue();
    }
    Object val = value;
    if (value instanceof QBParameter) {
        TablePlaceholderKey key = ((QBParameter) value).getPlaceholderKey();
        Placeholder placeholder = null;
        if (query != null) {
            placeholder = query.getPlaceholder(key);
        }
        val = placeholder == null ? new Placeholder(key) : placeholder;
    } else if (columnType == null) {
        if (value instanceof Date && !(value instanceof Timestamp) && !(value instanceof Time)) {
            // make sure a date is a timestamp
            val = new Timestamp(((Date) value).getTime());
        }
    } else if (!(value instanceof IQuerySelectValue)) {
        // convert the value (especially UUID) to the type of the column
        val = getAsRightType(value, columnType, flags);
    }
    if (val instanceof IQuerySelectValue) {
        return (IQuerySelectValue) val;
    }
    return new QueryColumnValue(val, null);
}

Example 3 with QueryColumnValue

use of com.servoy.j2db.query.QueryColumnValue in project servoy-client by Servoy.

the class SQLGenerator method makeQueryColumns.

private static ArrayList<IQuerySelectValue> makeQueryColumns(Iterator<Column> it, QueryTable queryTable, QueryInsert insert) {
    ArrayList<IQuerySelectValue> queryColumns = new ArrayList<IQuerySelectValue>();
    List<QueryColumn> insertColumns = new ArrayList<QueryColumn>();
    while (it.hasNext()) {
        Column column = it.next();
        ColumnInfo ci = column.getColumnInfo();
        if (ci != null && ci.isExcluded()) {
            continue;
        }
        QueryColumn queryColumn = column.queryColumn(queryTable);
        if (isBlobColumn(column)) {
            String alias = column.getDataProviderID().substring(0, Math.min(Column.MAX_SQL_OBJECT_NAME_LENGTH - (IDataServer.BLOB_MARKER_COLUMN_ALIAS.length() + 1), column.getDataProviderID().length())) + '_' + IDataServer.BLOB_MARKER_COLUMN_ALIAS;
            // make sure the alias is unique (2 media columns starting with the same name may clash here)
            char c = 'a';
            for (int i = 0; i < queryColumns.size(); i++) {
                IQuerySelectValue sv = queryColumns.get(i);
                if (alias.equals(sv.getAlias())) {
                    // alias not unique, replace first char to make it unique
                    alias = (c++) + alias.substring(1);
                    // search again
                    i = 0;
                }
            }
            queryColumns.add(new QueryColumnValue(Integer.valueOf(1), alias, true));
        } else {
            queryColumns.add(queryColumn);
        }
        if (insert != null && (ci == null || !ci.isDBManaged())) {
            insertColumns.add(queryColumn);
        }
    }
    if (insert != null) {
        insert.setColumnValues(insertColumns.toArray(new QueryColumn[insertColumns.size()]), new Placeholder(new TablePlaceholderKey(queryTable, PLACEHOLDER_INSERT_KEY)));
    }
    return queryColumns;
}

Example 4 with QueryColumnValue

use of com.servoy.j2db.query.QueryColumnValue in project servoy-client by Servoy.

the class FoundSetManager method getTableCount.

public int getTableCount(ITable table) {
    if (table != null) {
        try {
            long time = System.currentTimeMillis();
            IDataServer ds = application.getDataServer();
            String transaction_id = getTransactionID(table.getServerName());
            QuerySelect countSelect = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
            // $NON-NLS-1$
            countSelect.addColumn(new QueryAggregate(QueryAggregate.COUNT, new QueryColumnValue(Integer.valueOf(1), "n", true), null));
            IDataSet set = ds.performQuery(application.getClientID(), table.getServerName(), transaction_id, countSelect, null, getTableFilterParams(table.getServerName(), countSelect), false, 0, 10, IDataServer.FOUNDSET_LOAD_QUERY);
            if (Debug.tracing()) {
                Debug.trace(// $NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
                "Table count time: " + (System.currentTimeMillis() - time) + " thread: " + Thread.currentThread().getName() + ", SQL: " + countSelect.toString());
            }
            if (set.getRowCount() > 0) {
                Object[] row = set.getRow(0);
                if (row.length > 0) {
                    return Utils.getAsInteger(row[0]);
                }
            }
        } catch (Exception e) {
            Debug.error(e);
        }
    }
    return -1;
}

Example 5 with QueryColumnValue

use of com.servoy.j2db.query.QueryColumnValue in project servoy-client by Servoy.

the class SQLGenerator method createExistsCondition.

public static ISQLCondition createExistsCondition(IDataProviderHandler flattenedSolution, QuerySelect sqlSelect, ISQLCondition condition, List<IRelation> relations, BaseQueryTable columnTable, IGlobalValueEntry provider, BaseQueryColumn[] pkQueryColumns) throws RepositoryException {
    // exists (select 1 from innermain join related1 ... join relatedn where innermain.pk = main.pk having aggregate(relatedn))
    if (relations.size() == 0) {
        // searching for aggregate in main table, does no make sense.. ignore in search
        return null;
    }
    QuerySelect existsSelect = new QuerySelect(new QueryTable(sqlSelect.getTable().getName(), sqlSelect.getTable().getDataSource(), sqlSelect.getTable().getCatalogName(), sqlSelect.getTable().getSchemaName()));
    existsSelect.addColumn(new QueryColumnValue(Integer.valueOf(1), null, true));
    // innermain.pk = main.pk
    QueryColumn[] innerPkColumns = new QueryColumn[pkQueryColumns.length];
    for (int p = 0; p < pkQueryColumns.length; p++) {
        BaseQueryColumn pk = pkQueryColumns[p];
        innerPkColumns[p] = new QueryColumn(existsSelect.getTable(), pk.getId(), pk.getName(), pk.getColumnType().getSqlType(), pk.getColumnType().getLength(), pk.getColumnType().getScale(), pk.getNativeTypename(), pk.getFlags(), pk.isIdentity());
        // group by on the inner pk, some dbs (hxtt dbf) require that
        existsSelect.addGroupBy(innerPkColumns[p]);
    }
    existsSelect.addCondition("AGGREGATE-SEARCH", new // $NON-NLS-1$
    SetCondition(// $NON-NLS-1$
    new int[] { IBaseSQLCondition.EQUALS_OPERATOR }, // $NON-NLS-1$
    innerPkColumns, pkQueryColumns, true));
    // add the joins
    BaseQueryTable prevTable = existsSelect.getTable();
    for (IRelation relation : relations) {
        ITable foreignTable = flattenedSolution.getTable(relation.getForeignDataSource());
        QueryTable foreignQtable = new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema());
        existsSelect.addJoin(createJoin(flattenedSolution, relation, prevTable, foreignQtable, true, provider));
        prevTable = foreignQtable;
    }
    existsSelect.addHaving(AbstractBaseQuery.relinkTable(columnTable, prevTable, condition));
    return new ExistsCondition(existsSelect, true);
}