Examples with SQLImpl lucee.runtime.db.SQLImpl used on opensource projects

Example 6 with SQLImpl

use of lucee.runtime.db.SQLImpl in project Lucee by lucee.

the class Ansi92 method clean.

@Override
public void clean(Config config, DatasourceConnection dc, int type, StorageScopeEngine engine, DatasourceStorageScopeCleaner cleaner, StorageScopeListener listener, Log log) throws PageException {
    String strType = VariableInterpreter.scopeInt2String(type);
    // select
    SQL sqlSelect = new SQLImpl("select cfid,name from " + PREFIX + "_" + strType + "_data where expires<=?", new SQLItem[] { new SQLItemImpl(System.currentTimeMillis(), Types.VARCHAR) });
    Query query;
    try {
        query = new QueryImpl(ThreadLocalPageContext.get(), dc, sqlSelect, -1, -1, null, "query");
    } catch (Throwable t) {
        ExceptionUtil.rethrowIfNecessary(t);
        // possible that the table not exist, if not there is nothing to clean
        return;
    }
    int recordcount = query.getRecordcount();
    String cfid, name;
    for (int row = 1; row <= recordcount; row++) {
        cfid = Caster.toString(query.getAt(KeyConstants._cfid, row, null), null);
        name = Caster.toString(query.getAt(KeyConstants._name, row, null), null);
        if (listener != null)
            listener.doEnd(engine, cleaner, name, cfid);
        ScopeContext.info(log, "remove " + strType + "/" + name + "/" + cfid + " from datasource " + dc.getDatasource().getName());
        engine.remove(type, name, cfid);
        SQLImpl sql = new SQLImpl("delete from " + StorageScopeDatasource.PREFIX + "_" + strType + "_data where cfid=? and name=?", new SQLItem[] { new SQLItemImpl(cfid, Types.VARCHAR), new SQLItemImpl(name, Types.VARCHAR) });
        new QueryImpl(ThreadLocalPageContext.get(), dc, sql, -1, -1, null, "query");
    }
}

Example 7 with SQLImpl

use of lucee.runtime.db.SQLImpl in project Lucee by lucee.

the class ColumnInfo method createSQL.

/**
 * @param meta
 * @return return SQL String for insert
 * @throws PageException
 */
private SQL createSQL(Struct meta) throws PageException {
    String[] fields = null;
    Form form = pageContext.formScope();
    if (formfields != null)
        fields = ListUtil.toStringArray(ListUtil.listToArrayRemoveEmpty(formfields, ','));
    else
        fields = CollectionUtil.keysAsString(pageContext.formScope());
    StringBuffer names = new StringBuffer();
    StringBuffer values = new StringBuffer();
    ArrayList items = new ArrayList();
    String field;
    for (int i = 0; i < fields.length; i++) {
        field = StringUtil.trim(fields[i], null);
        if (StringUtil.startsWithIgnoreCase(field, "form."))
            field = field.substring(5);
        if (!field.equalsIgnoreCase("fieldnames")) {
            if (names.length() > 0) {
                names.append(',');
                values.append(',');
            }
            names.append(field);
            values.append('?');
            ColumnInfo ci = (ColumnInfo) meta.get(field, null);
            if (ci != null)
                items.add(new SQLItemImpl(form.get(field, null), ci.getType()));
            else
                items.add(new SQLItemImpl(form.get(field, null)));
        }
    }
    if (items.size() == 0)
        return null;
    StringBuffer sql = new StringBuffer();
    sql.append("insert into ");
    if (tablequalifier.length() > 0) {
        sql.append(tablequalifier);
        sql.append('.');
    }
    if (tableowner.length() > 0) {
        sql.append(tableowner);
        sql.append('.');
    }
    sql.append(tablename);
    sql.append('(');
    sql.append(names);
    sql.append(")values(");
    sql.append(values);
    sql.append(")");
    return new SQLImpl(sql.toString(), (SQLItem[]) items.toArray(new SQLItem[items.size()]));
}

Example 8 with SQLImpl

use of lucee.runtime.db.SQLImpl in project Lucee by lucee.

the class Ansi92 method select.

@Override
public Query select(Config config, String cfid, String applicationName, DatasourceConnection dc, int type, Log log, boolean createTableIfNotExist) throws PageException {
    String strType = VariableInterpreter.scopeInt2String(type);
    Query query = null;
    SQL sqlSelect = new SQLImpl("select data from " + PREFIX + "_" + strType + "_data where cfid=? and name=? and expires > ?", new SQLItem[] { new SQLItemImpl(cfid, Types.VARCHAR), new SQLItemImpl(applicationName, Types.VARCHAR), new SQLItemImpl(now(config), Types.VARCHAR) });
    PageContext pc = ThreadLocalPageContext.get();
    try {
        query = new QueryImpl(pc, dc, sqlSelect, -1, -1, null, "query");
    } catch (DatabaseException de) {
        if (dc == null || !createTableIfNotExist)
            throw de;
        // table does not exist???
        try {
            SQL sql = createSQL(dc, DataSourceUtil.isMySQL(dc) ? "longtext" : "ntext", strType);
            ScopeContext.info(log, sql.toString());
            new QueryImpl(pc, dc, sql, -1, -1, null, "query");
        } catch (DatabaseException _de) {
            // don't like "ntext", try text
            try {
                SQL sql = createSQL(dc, "text", strType);
                ScopeContext.info(log, sql.toString());
                new QueryImpl(pc, dc, sql, -1, -1, null, "query");
            } catch (DatabaseException __de) {
                // don't like text, try "memo"
                try {
                    SQL sql = createSQL(dc, "memo", strType);
                    ScopeContext.info(log, sql.toString());
                    new QueryImpl(pc, dc, sql, -1, -1, null, "query");
                } catch (DatabaseException ___de) {
                    // don't like "memo", try clob
                    try {
                        SQL sql = createSQL(dc, "clob", strType);
                        ScopeContext.info(log, sql.toString());
                        new QueryImpl(pc, dc, sql, -1, -1, null, "query");
                    } catch (DatabaseException ____de) {
                        ___de.initCause(__de);
                        __de.initCause(_de);
                        _de.initCause(de);
                        // we could not create the table, so there seem to be an other ecception we cannot solve
                        DatabaseException exp = new DatabaseException("Unable to select from your client storage database, and was also unable to create the tables. Here's the exceptions we encountered.", null, null, dc);
                        exp.initCause(de);
                        throw exp;
                    }
                }
            }
        }
        query = new QueryImpl(pc, dc, sqlSelect, -1, -1, null, "query");
    }
    ScopeContext.info(log, sqlSelect.toString());
    return query;
}