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

Example 1 with SQLItem

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

the class Query method doEndTag.

@Override
public int doEndTag() throws PageException {
    if (hasChangedPSQ)
        pageContext.setPsq(orgPSQ);
    String strSQL = bodyContent.getString().trim();
    if (strSQL.isEmpty())
        throw new DatabaseException("no sql string defined, inside query tag", null, null, null);
    try {
        // cannot use attribute params and queryparam tag
        if (!items.isEmpty() && params != null)
            throw new DatabaseException("you cannot use the attribute params and sub tags queryparam at the same time", null, null, null);
        // create SQL
        SQL sql;
        if (params != null) {
            if (params instanceof Argument)
                sql = QueryParamConverter.convert(strSQL, (Argument) params);
            else if (Decision.isArray(params))
                sql = QueryParamConverter.convert(strSQL, Caster.toArray(params));
            else if (Decision.isStruct(params))
                sql = QueryParamConverter.convert(strSQL, Caster.toStruct(params));
            else
                throw new DatabaseException("value of the attribute [params] has to be a struct or a array", null, null, null);
        } else {
            sql = items.isEmpty() ? new SQLImpl(strSQL) : new SQLImpl(strSQL, items.toArray(new SQLItem[items.size()]));
        }
        // lucee.runtime.type.Query query=null;
        QueryResult queryResult = null;
        String cacheHandlerId = null;
        String cacheId = null;
        long exe = 0;
        boolean useCache = (cachedWithin != null) || (cachedAfter != null);
        CacheHandler cacheHandler = null;
        if (useCache) {
            cacheId = CacheHandlerCollectionImpl.createId(sql, datasource != null ? datasource.getName() : null, username, password, returntype);
            CacheHandlerCollectionImpl coll = (CacheHandlerCollectionImpl) pageContext.getConfig().getCacheHandlerCollection(Config.CACHE_TYPE_QUERY, null);
            cacheHandler = coll.getInstanceMatchingObject(cachedWithin, null);
            if (cacheHandler == null && cachedAfter != null)
                cacheHandler = coll.getTimespanInstance(null);
            if (cacheHandler != null) {
                // cacheHandlerId specifies to queryResult the cacheType and therefore whether the query is cached or
                cacheHandlerId = cacheHandler.id();
                if (cacheHandler instanceof CacheHandlerPro) {
                    CacheItem cacheItem = ((CacheHandlerPro) cacheHandler).get(pageContext, cacheId, (cachedWithin != null) ? cachedWithin : cachedAfter);
                    if (cacheItem instanceof QueryResultCacheItem)
                        queryResult = ((QueryResultCacheItem) cacheItem).getQueryResult();
                } else {
                    // FUTURE this else block can be removed when all cache handlers implement CacheHandlerPro
                    CacheItem cacheItem = cacheHandler.get(pageContext, cacheId);
                    if (cacheItem instanceof QueryResultCacheItem) {
                        QueryResultCacheItem queryCachedItem = (QueryResultCacheItem) cacheItem;
                        Date cacheLimit = cachedAfter;
                        if (cacheLimit == null || queryCachedItem.isCachedAfter(cacheLimit))
                            queryResult = queryCachedItem.getQueryResult();
                    }
                }
            } else {
                List<String> patterns = pageContext.getConfig().getCacheHandlerCollection(Config.CACHE_TYPE_QUERY, null).getPatterns();
                throw new ApplicationException("cachedwithin value [" + cachedWithin + "] is invalid, valid values are for example [" + ListUtil.listToList(patterns, ", ") + "]");
            }
        // query=pageContext.getQueryCache().getQuery(pageContext,sql,datasource!=null?datasource.getName():null,username,password,cachedafter);
        }
        // cache not found, process and cache result if needed
        if (queryResult == null) {
            // QoQ
            if ("query".equals(dbtype)) {
                lucee.runtime.type.Query q = executeQoQ(sql);
                if (returntype == RETURN_TYPE_ARRAY)
                    // TODO this should be done in queryExecute itself so we not have to convert afterwards
                    queryResult = QueryArray.toQueryArray(q);
                else if (returntype == RETURN_TYPE_STRUCT) {
                    if (columnName == null)
                        throw new ApplicationException("attribute columnKey is required when return type is set to struct");
                    // TODO this should be done in queryExecute itself so we not have to convert
                    queryResult = QueryStruct.toQueryStruct(q, columnName);
                // afterwards
                } else
                    queryResult = (QueryResult) q;
            } else // ORM and Datasource
            {
                long start = System.nanoTime();
                Object obj;
                if ("orm".equals(dbtype) || "hql".equals(dbtype))
                    obj = executeORM(sql, returntype, ormoptions);
                else
                    obj = executeDatasoure(sql, result != null, pageContext.getTimeZone());
                if (obj instanceof QueryResult) {
                    queryResult = (QueryResult) obj;
                } else {
                    if (setReturnVariable) {
                        rtn = obj;
                    } else if (!StringUtil.isEmpty(name)) {
                        pageContext.setVariable(name, obj);
                    }
                    if (result != null) {
                        Struct sct = new StructImpl();
                        sct.setEL(KeyConstants._cached, Boolean.FALSE);
                        long time = System.nanoTime() - start;
                        sct.setEL(KeyConstants._executionTime, Caster.toDouble(time / 1000000));
                        sct.setEL(KeyConstants._executionTimeNano, Caster.toDouble(time));
                        sct.setEL(KeyConstants._SQL, sql.getSQLString());
                        if (Decision.isArray(obj)) {
                        } else
                            sct.setEL(KeyConstants._RECORDCOUNT, Caster.toDouble(1));
                        pageContext.setVariable(result, sct);
                    } else
                        setExecutionTime((System.nanoTime() - start) / 1000000);
                    return EVAL_PAGE;
                }
            }
            if (cachedWithin != null) {
                CacheItem cacheItem = QueryResultCacheItem.newInstance(queryResult, tags, datasource, null);
                if (cacheItem != null)
                    cacheHandler.set(pageContext, cacheId, cachedWithin, cacheItem);
            }
            exe = queryResult.getExecutionTime();
        } else {
            queryResult.setCacheType(cacheHandlerId);
        }
        if (pageContext.getConfig().debug() && debug) {
            boolean logdb = ((ConfigImpl) pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE);
            if (logdb) {
                boolean debugUsage = DebuggerImpl.debugQueryUsage(pageContext, queryResult);
                DebuggerImpl di = (DebuggerImpl) pageContext.getDebugger();
                di.addQuery(debugUsage ? queryResult : null, datasource != null ? datasource.getName() : null, name, sql, queryResult.getRecordcount(), getPageSource(), exe);
            }
        }
        if (setReturnVariable) {
            rtn = queryResult;
        } else if ((queryResult.getColumncount() + queryResult.getRecordcount()) > 0 && !StringUtil.isEmpty(name)) {
            pageContext.setVariable(name, queryResult);
        }
        // Result
        if (result != null) {
            Struct sct = new StructImpl();
            sct.setEL(KeyConstants._cached, Caster.toBoolean(queryResult.isCached()));
            if ((queryResult.getColumncount() + queryResult.getRecordcount()) > 0) {
                String list = ListUtil.arrayToList(queryResult instanceof lucee.runtime.type.Query ? ((lucee.runtime.type.Query) queryResult).getColumnNamesAsString() : CollectionUtil.toString(queryResult.getColumnNames(), false), ",");
                sct.setEL(KeyConstants._COLUMNLIST, list);
            }
            int rc = queryResult.getRecordcount();
            if (rc == 0)
                rc = queryResult.getUpdateCount();
            sct.setEL(KeyConstants._RECORDCOUNT, Caster.toDouble(rc));
            sct.setEL(KeyConstants._executionTime, Caster.toDouble(queryResult.getExecutionTime() / 1000000));
            sct.setEL(KeyConstants._executionTimeNano, Caster.toDouble(queryResult.getExecutionTime()));
            sct.setEL(KeyConstants._SQL, sql.getSQLString());
            // GENERATED KEYS
            lucee.runtime.type.Query qi = Caster.toQuery(queryResult, null);
            if (qi != null) {
                lucee.runtime.type.Query qryKeys = qi.getGeneratedKeys();
                if (qryKeys != null) {
                    StringBuilder generatedKey = new StringBuilder(), sb;
                    Collection.Key[] columnNames = qryKeys.getColumnNames();
                    QueryColumn column;
                    for (int c = 0; c < columnNames.length; c++) {
                        column = qryKeys.getColumn(columnNames[c]);
                        sb = new StringBuilder();
                        int size = column.size();
                        for (int row = 1; row <= size; row++) {
                            if (row > 1)
                                sb.append(',');
                            sb.append(Caster.toString(column.get(row, null)));
                        }
                        if (sb.length() > 0) {
                            sct.setEL(columnNames[c], sb.toString());
                            if (generatedKey.length() > 0)
                                generatedKey.append(',');
                            generatedKey.append(sb);
                        }
                    }
                    if (generatedKey.length() > 0)
                        sct.setEL(GENERATEDKEY, generatedKey.toString());
                }
            }
            // sqlparameters
            SQLItem[] params = sql.getItems();
            if (params != null && params.length > 0) {
                Array arr = new ArrayImpl();
                sct.setEL(SQL_PARAMETERS, arr);
                for (int i = 0; i < params.length; i++) {
                    arr.append(params[i].getValue());
                }
            }
            pageContext.setVariable(result, sct);
        } else // cfquery.executiontime
        {
            setExecutionTime(exe / 1000000);
        }
        // listener
        ((ConfigWebImpl) pageContext.getConfig()).getActionMonitorCollector().log(pageContext, "query", "Query", exe, queryResult);
        // log
        Log log = pageContext.getConfig().getLog("datasource");
        if (log.getLogLevel() >= Log.LEVEL_INFO) {
            log.info("query tag", "executed [" + sql.toString().trim() + "] in " + DecimalFormat.call(pageContext, exe / 1000000D) + " ms");
        }
    } catch (PageException pe) {
        // log
        pageContext.getConfig().getLog("datasource").error("query tag", pe);
        throw pe;
    } finally {
        ((PageContextImpl) pageContext).setTimestampWithTSOffset(previousLiteralTimestampWithTSOffset);
        if (tmpTZ != null) {
            pageContext.setTimeZone(tmpTZ);
        }
    }
    return EVAL_PAGE;
}

Example 2 with SQLItem

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

the class QueryImpl method execute.

private static void execute(PageContext pc, DatasourceConnection dc, SQL sql, int maxrow, int fetchsize, TimeSpan timeout, boolean createUpdateData, boolean allowToCachePreperadeStatement, QueryImpl qry, QueryResult qr, Collection.Key keyName) throws PageException {
    TimeZone tz = ThreadLocalPageContext.getTimeZone(pc);
    // check if datasource support Generated Keys
    boolean createGeneratedKeys = createUpdateData;
    if (createUpdateData) {
        if (!dc.supportsGetGeneratedKeys())
            createGeneratedKeys = false;
    }
    // check SQL Restrictions
    if (dc.getDatasource().hasSQLRestriction()) {
        QueryUtil.checkSQLRestriction(dc, sql);
    }
    Statement stat = null;
    // Stopwatch stopwatch=new Stopwatch();
    long start = System.nanoTime();
    // stopwatch.start();
    boolean hasResult = false;
    // boolean closeStatement=true;
    try {
        SQLItem[] items = sql.getItems();
        if (items.length == 0) {
            stat = dc.getConnection().createStatement();
            setAttributes(stat, maxrow, fetchsize, timeout);
            // some driver do not support second argument
            // hasResult=createGeneratedKeys?stat.execute(sql.getSQLString(),Statement.RETURN_GENERATED_KEYS):stat.execute(sql.getSQLString());
            hasResult = QueryUtil.execute(pc, stat, createGeneratedKeys, sql);
        } else {
            // some driver do not support second argument
            PreparedStatement preStat = dc.getPreparedStatement(sql, createGeneratedKeys, allowToCachePreperadeStatement);
            // closeStatement=false;
            stat = preStat;
            setAttributes(preStat, maxrow, fetchsize, timeout);
            setItems(pc, ThreadLocalPageContext.getTimeZone(pc), preStat, items);
            hasResult = QueryUtil.execute(pc, preStat);
        }
        int uc;
        // ResultSet res;
        do {
            if (hasResult) {
                // if(fillResult(dc,res, maxrow, true,createGeneratedKeys,tz))break;
                if (fillResult(qry, qr, keyName, dc, stat.getResultSet(), maxrow, true, createGeneratedKeys, tz))
                    break;
            } else if ((uc = setUpdateCount(qry != null ? qry : qr, stat)) != -1) {
                if (uc > 0 && createGeneratedKeys && qry != null)
                    qry.setGeneratedKeys(dc, stat, tz);
            } else
                break;
            try {
                // hasResult=stat.getMoreResults(Statement.CLOSE_CURRENT_RESULT);
                hasResult = stat.getMoreResults();
            } catch (Throwable t) {
                ExceptionUtil.rethrowIfNecessary(t);
                break;
            }
        } while (true);
    } catch (SQLException e) {
        throw new DatabaseException(e, sql, dc);
    } catch (Throwable e) {
        ExceptionUtil.rethrowIfNecessary(e);
        throw Caster.toPageException(e);
    } finally {
        // if(closeStatement)
        DBUtil.closeEL(stat);
    }
    if (qry != null) {
        qry.exeTime = System.nanoTime() - start;
        if (qry.columncount == 0) {
            if (qry.columnNames == null)
                qry.columnNames = new Collection.Key[0];
            if (qry.columns == null)
                qry.columns = new QueryColumnImpl[0];
        }
    } else {
        qr.setExecutionTime(System.nanoTime() - start);
    }
}

Example 3 with SQLItem

use of lucee.runtime.db.SQLItem 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 4 with SQLItem

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

the class Query method executeORM.

private Object executeORM(SQL sql, int returnType, Struct ormoptions) throws PageException {
    ORMSession session = ORMUtil.getSession(pageContext);
    if (ormoptions == null)
        ormoptions = new StructImpl();
    String dsn = null;
    if (ormoptions != null)
        dsn = Caster.toString(ormoptions.get(KeyConstants._datasource, null), null);
    if (StringUtil.isEmpty(dsn, true))
        dsn = ORMUtil.getDefaultDataSource(pageContext).getName();
    // params
    SQLItem[] _items = sql.getItems();
    Array params = new ArrayImpl();
    for (int i = 0; i < _items.length; i++) {
        params.appendEL(_items[i]);
    }
    // query options
    if (maxrows != -1 && !ormoptions.containsKey(MAX_RESULTS))
        ormoptions.setEL(MAX_RESULTS, new Double(maxrows));
    if (timeout != null && ((int) timeout.getSeconds()) > 0 && !ormoptions.containsKey(TIMEOUT))
        ormoptions.setEL(TIMEOUT, new Double(timeout.getSeconds()));
    /*
		 * MUST offset: Specifies the start index of the resultset from where it has to start the retrieval. cacheable: Whether the result of this query is to
		 * be cached in the secondary cache. Default is false. cachename: Name of the cache in secondary cache.
		 */
    Object res = session.executeQuery(pageContext, dsn, sql.getSQLString(), params, unique, ormoptions);
    if (returnType == RETURN_TYPE_ARRAY || returnType == RETURN_TYPE_UNDEFINED)
        return res;
    return session.toQuery(pageContext, res, null);
}

Example 5 with SQLItem

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

the class Ansi92 method execute.

private static int execute(PageContext pc, Connection conn, SQLImpl sql, TimeZone tz) throws SQLException, PageException {
    PreparedStatement preStat = conn.prepareStatement(sql.getSQLString());
    int count = 0;
    try {
        SQLItem[] items = sql.getItems();
        for (int i = 0; i < items.length; i++) {
            SQLCaster.setValue(pc, tz, preStat, i + 1, items[i]);
        }
        count = preStat.executeUpdate();
    } finally {
        preStat.close();
    }
    return count;
}