Search in sources :

Example 1 with DatasourceConnection

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

the class Query method executeDatasoure.

private QueryResult executeDatasoure(SQL sql, boolean createUpdateData, TimeZone tz) throws PageException {
    DatasourceManagerImpl manager = (DatasourceManagerImpl) pageContext.getDataSourceManager();
    DatasourceConnection dc = manager.getConnection(pageContext, datasource, username, password);
    try {
        if (lazy && !createUpdateData && cachedWithin == null && cachedAfter == null && result == null) {
            if (returntype != RETURN_TYPE_QUERY)
                throw new DatabaseException("only return type query is allowed when lazy is set to true", null, sql, dc);
            return new SimpleQuery(pageContext, dc, sql, maxrows, blockfactor, timeout, getName(), getPageSource().getDisplayPath(), tz);
        }
        if (returntype == RETURN_TYPE_ARRAY)
            return QueryImpl.toArray(pageContext, dc, sql, maxrows, blockfactor, timeout, getName(), getPageSource().getDisplayPath(), createUpdateData, true);
        if (returntype == RETURN_TYPE_STRUCT) {
            if (columnName == null)
                throw new ApplicationException("attribute columnKey is required when return type is set to struct");
            return QueryImpl.toStruct(pageContext, dc, sql, columnName, maxrows, blockfactor, timeout, getName(), getPageSource().getDisplayPath(), createUpdateData, true);
        }
        return new QueryImpl(pageContext, dc, sql, maxrows, blockfactor, timeout, getName(), getPageSource().getDisplayPath(), createUpdateData, true);
    } finally {
        manager.releaseConnection(pageContext, dc);
    }
}
Also used : QueryImpl(lucee.runtime.type.QueryImpl) ApplicationException(lucee.runtime.exp.ApplicationException) DatasourceConnection(lucee.runtime.db.DatasourceConnection) SimpleQuery(lucee.runtime.type.query.SimpleQuery) DatasourceManagerImpl(lucee.runtime.db.DatasourceManagerImpl) DatabaseException(lucee.runtime.exp.DatabaseException)

Example 2 with DatasourceConnection

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

the class StoredProc method returnValue.

private void returnValue(DatasourceConnection dc) throws PageException {
    Connection conn = dc.getConnection();
    if (SQLUtil.isOracle(conn)) {
        String name = this.procedure.toUpperCase().trim();
        // split procedure definition
        String catalog = null, scheme = null;
        {
            int index = name.lastIndexOf('.');
            if (index != -1) {
                catalog = name.substring(0, index).trim();
                name = name.substring(index + 1).trim();
                index = catalog.lastIndexOf('.');
                if (index != -1) {
                    scheme = catalog.substring(0, index).trim();
                    catalog = catalog.substring(index + 1).trim();
                // scheme=catalog.substring(index+1);
                // catalog=catalog.substring(0,index);
                }
            }
            if (StringUtil.isEmpty(scheme))
                scheme = null;
            if (StringUtil.isEmpty(catalog))
                catalog = null;
        }
        try {
            // if(procedureColumnCache==null)procedureColumnCache=new ReferenceMap();
            // ProcMetaCollection coll=procedureColumnCache.get(procedure);
            DataSourceSupport d = ((DataSourceSupport) dc.getDatasource());
            long cacheTimeout = d.getMetaCacheTimeout();
            Map<String, ProcMetaCollection> procedureColumnCache = d.getProcedureColumnCache();
            String id = procedure.toLowerCase();
            ProcMetaCollection coll = procedureColumnCache.get(id);
            if (coll == null || (cacheTimeout >= 0 && (coll.created + cacheTimeout) < System.currentTimeMillis())) {
                DatabaseMetaData md = conn.getMetaData();
                String _catalog = null, _scheme = null, _name = null;
                boolean available = false;
                /*
					 * print.e("pro:"+procedure); print.e("cat:"+catalog); print.e("sch:"+scheme); print.e("nam:"+name);
					 */
                ResultSet proc = md.getProcedures(null, null, name);
                try {
                    while (proc.next()) {
                        _catalog = proc.getString(1);
                        _scheme = proc.getString(2);
                        _name = proc.getString(3);
                        if (_name.equalsIgnoreCase(name) && // second option is very unlikely to ever been the case, but does not hurt to test
                        (catalog == null || _catalog == null || catalog.equalsIgnoreCase(_catalog)) && // second option is very unlikely to ever been the case, but does not hurt to test
                        (scheme == null || _scheme == null || scheme.equalsIgnoreCase(_scheme))) {
                            available = true;
                            break;
                        } else {
                            Log log = pageContext.getConfig().getLog("datasource");
                            if (// log entry added to troubleshoot LDEV-1147
                            log.getLogLevel() >= Log.LEVEL_DEBUG)
                                log.debug("LDEV1147", String.format("name=[%s] scheme=[%s] catalog=[%s] _name=[%s] _scheme=[%s] _catalog=[%s]", name, scheme, catalog, _name, _scheme, _catalog));
                        }
                    }
                } finally {
                    IOUtil.closeEL(proc);
                }
                if (available) {
                    /*
						 * print.e("---------------"); print.e("_pro:"+procedure); print.e("_cat:"+_catalog); print.e("_sch:"+_scheme); print.e("_nam:"+_name);
						 */
                    ResultSet res = md.getProcedureColumns(_catalog, _scheme, _name, "%");
                    coll = createProcMetaCollection(res);
                    procedureColumnCache.put(id, coll);
                }
            }
            int index = -1;
            int ct;
            if (coll != null) {
                Iterator<ProcMeta> it = coll.metas.iterator();
                ProcMeta pm;
                while (it.hasNext()) {
                    index++;
                    pm = it.next();
                    ct = pm.columnType;
                    // Return
                    if (ct == DatabaseMetaData.procedureColumnReturn) {
                        index--;
                        ProcResultBean result = getFirstResult();
                        ProcParamBean param = new ProcParamBean();
                        param.setType(pm.dataType);
                        param.setDirection(ProcParamBean.DIRECTION_OUT);
                        if (result != null)
                            param.setVariable(result.getName());
                        returnValue = param;
                    } else if (ct == DatabaseMetaData.procedureColumnOut || ct == DatabaseMetaData.procedureColumnInOut) {
                        // review of the code: seems to add an addional column in this case
                        if (pm.dataType == CFTypes.CURSOR) {
                            ProcResultBean result = getFirstResult();
                            ProcParamBean param = new ProcParamBean();
                            param.setType(pm.dataType);
                            param.setDirection(ProcParamBean.DIRECTION_OUT);
                            if (result != null)
                                param.setVariable(result.getName());
                            if (params.size() < index)
                                throw new DatabaseException("you have only defined [" + params.size() + "] procparam tags, but the procedure/function called is expecting more", null, null, dc);
                            else if (params.size() == index)
                                params.add(param);
                            else
                                params.add(index, param);
                        } else {
                            ProcParamBean param = null;
                            if (params.size() > index)
                                param = params.get(index);
                            if (param != null && pm.dataType != Types.OTHER && pm.dataType != param.getType()) {
                                param.setType(pm.dataType);
                            }
                        }
                    } else if (ct == DatabaseMetaData.procedureColumnIn) {
                        ProcParamBean param = get(params, index);
                        if (param != null && pm.dataType != Types.OTHER && pm.dataType != param.getType()) {
                            param.setType(pm.dataType);
                        }
                    }
                }
            }
            contractTo(params, index + 1);
        // if(res!=null)print.out(new QueryImpl(res,"columns").toString());
        } catch (SQLException e) {
            throw new DatabaseException(e, dc);
        }
    }
    // return code
    if (returncode) {
        returnValue = STATUS_CODE;
    }
}
Also used : DataSourceSupport(lucee.runtime.db.DataSourceSupport) Log(lucee.commons.io.log.Log) SQLException(java.sql.SQLException) Connection(java.sql.Connection) DatasourceConnection(lucee.runtime.db.DatasourceConnection) DatabaseMetaData(java.sql.DatabaseMetaData) ProcMetaCollection(lucee.runtime.db.ProcMetaCollection) ResultSet(java.sql.ResultSet) DatabaseException(lucee.runtime.exp.DatabaseException) ProcMeta(lucee.runtime.db.ProcMeta)

Example 3 with DatasourceConnection

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

the class StoredProc method doEndTag.

@Override
public int doEndTag() throws PageException {
    long startNS = System.nanoTime();
    Object ds = datasource;
    if (StringUtil.isEmpty(datasource)) {
        ds = pageContext.getApplicationContext().getDefDataSource();
        if (StringUtil.isEmpty(ds)) {
            boolean isCFML = pageContext.getRequestDialect() == CFMLEngine.DIALECT_CFML;
            throw new ApplicationException("attribute [datasource] is required, when no default datasource is defined", "you can define a default datasource as attribute [defaultdatasource] of the tag " + (isCFML ? Constants.CFML_APPLICATION_TAG_NAME : Constants.LUCEE_APPLICATION_TAG_NAME) + " or as data member of the " + (isCFML ? Constants.CFML_APPLICATION_EVENT_HANDLER : Constants.LUCEE_APPLICATION_EVENT_HANDLER) + " (this.defaultdatasource=\"mydatasource\";)");
        }
    }
    Struct res = new StructImpl();
    DataSourceManager manager = pageContext.getDataSourceManager();
    DatasourceConnection dc = ds instanceof DataSource ? manager.getConnection(pageContext, (DataSource) ds, username, password) : manager.getConnection(pageContext, Caster.toString(ds), username, password);
    // create returnValue
    returnValue(dc);
    String sql = createSQL();
    // add returnValue to params
    if (returnValue != null) {
        params.add(0, returnValue);
    }
    Log log = pageContext.getConfig().getLog("datasource");
    SQLImpl _sql = new SQLImpl(sql);
    CallableStatement callStat = null;
    try {
        if (// log entry added to troubleshoot LDEV-1147
        log.getLogLevel() >= Log.LEVEL_DEBUG)
            log.debug("LDEV1147", sql + " [" + params.size() + " params]");
        callStat = dc.getConnection().prepareCall(sql);
        if (blockfactor > 0)
            callStat.setFetchSize(blockfactor);
        if (timeout > 0)
            DataSourceUtil.setQueryTimeoutSilent(callStat, timeout);
        // set IN register OUT
        Iterator<ProcParamBean> it = params.iterator();
        ProcParamBean param;
        int index = 1;
        while (it.hasNext()) {
            param = it.next();
            param.setIndex(index);
            _sql.addItems(new SQLItemImpl(param.getValue()));
            if (param.getDirection() != ProcParamBean.DIRECTION_OUT) {
                SQLCaster.setValue(pageContext, pageContext.getTimeZone(), callStat, index, param);
            }
            if (param.getDirection() != ProcParamBean.DIRECTION_IN) {
                registerOutParameter(callStat, param);
            }
            index++;
        }
        String dsn = (ds instanceof DataSource) ? ((DataSource) ds).getName() : Caster.toString(ds);
        // cache
        boolean isFromCache = false;
        Object cacheValue = null;
        boolean useCache = (cachedWithin != null) || (cachedafter != null);
        String cacheId = null;
        CacheHandler cacheHandler = null;
        if (useCache) {
            cacheId = CacheHandlerCollectionImpl.createId(_sql, dsn, username, password, Query.RETURN_TYPE_STORED_PROC);
            cacheHandler = pageContext.getConfig().getCacheHandlerCollection(Config.CACHE_TYPE_QUERY, null).getInstanceMatchingObject(cachedWithin, null);
            if (cacheHandler instanceof CacheHandlerPro) {
                CacheItem cacheItem = ((CacheHandlerPro) cacheHandler).get(pageContext, cacheId, cachedWithin);
                if (cacheItem != null)
                    cacheValue = ((StoredProcCacheItem) cacheItem).getStruct();
            } else if (cacheHandler != null) {
                // TODO this else block can be removed when all cache handlers implement CacheHandlerPro
                CacheItem cacheItem = cacheHandler.get(pageContext, cacheId);
                if (cacheItem != null)
                    cacheValue = ((StoredProcCacheItem) cacheItem).getStruct();
            // cacheValue = pageContext.getQueryCache().get(pageContext,_sql,dsn,username,password,cachedafter);
            }
        }
        int count = 0;
        long start = System.currentTimeMillis();
        if (cacheValue == null) {
            // execute
            boolean isResult = callStat.execute();
            Struct cacheStruct = useCache ? new StructImpl() : null;
            // resultsets
            ProcResultBean result;
            index = 1;
            do {
                if (isResult) {
                    ResultSet rs = callStat.getResultSet();
                    if (rs != null) {
                        try {
                            result = (ProcResultBean) results.get(index++, null);
                            if (result != null) {
                                lucee.runtime.type.Query q = new QueryImpl(rs, result.getMaxrows(), result.getName(), pageContext.getTimeZone());
                                count += q.getRecordcount();
                                setVariable(result.getName(), q);
                                if (useCache)
                                    cacheStruct.set(KeyImpl.getInstance(result.getName()), q);
                            }
                        } finally {
                            IOUtil.closeEL(rs);
                        }
                    }
                }
            } while ((isResult = callStat.getMoreResults()) || (callStat.getUpdateCount() != -1));
            // params
            it = params.iterator();
            while (it.hasNext()) {
                param = it.next();
                if (param.getDirection() != ProcParamBean.DIRECTION_IN) {
                    Object value = null;
                    if (!StringUtil.isEmpty(param.getVariable())) {
                        try {
                            value = SQLCaster.toCFType(callStat.getObject(param.getIndex()));
                        } catch (Throwable t) {
                            ExceptionUtil.rethrowIfNecessary(t);
                        }
                        value = emptyIfNull(value);
                        if (param == STATUS_CODE)
                            res.set(STATUSCODE, value);
                        else
                            setVariable(param.getVariable(), value);
                        if (useCache)
                            cacheStruct.set(KeyImpl.getInstance(param.getVariable()), value);
                    }
                }
            }
            if (cacheHandler != null) {
                cacheStruct.set(COUNT, Caster.toDouble(count));
                cacheHandler.set(pageContext, cacheId, cachedWithin, new StoredProcCacheItem(cacheStruct, procedure, System.currentTimeMillis() - start));
            // pageContext.getQueryCache().set(pageContext,_sql,dsn,username,password,cache,cachedbefore);
            }
        } else if (cacheValue instanceof Struct) {
            Struct sctCache = (Struct) cacheValue;
            count = Caster.toIntValue(sctCache.removeEL(COUNT), 0);
            Iterator<Entry<Key, Object>> cit = sctCache.entryIterator();
            Entry<Key, Object> ce;
            while (cit.hasNext()) {
                ce = cit.next();
                if (STATUS_CODE.getVariable().equals(ce.getKey().getString()))
                    res.set(KEY_SC, ce.getValue());
                else
                    setVariable(ce.getKey().getString(), ce.getValue());
            }
            isFromCache = true;
        }
        // result
        long exe;
        setVariable(this.result, res);
        res.set(KeyConstants._executionTime, Caster.toDouble(exe = (System.nanoTime() - startNS)));
        res.set(KeyConstants._cached, Caster.toBoolean(isFromCache));
        if (pageContext.getConfig().debug() && debug) {
            boolean logdb = ((ConfigImpl) pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE);
            if (logdb)
                pageContext.getDebugger().addQuery(null, dsn, procedure, _sql, count, pageContext.getCurrentPageSource(), (int) exe);
        }
        // log
        if (log.getLogLevel() >= Log.LEVEL_INFO) {
            log.info(StoredProc.class.getSimpleName(), "executed [" + sql.trim() + "] in " + DecimalFormat.call(pageContext, exe / 1000000D) + " ms");
        }
    } catch (SQLException e) {
        // log
        log.error(StoredProc.class.getSimpleName(), e);
        throw new DatabaseException(e, new SQLImpl(sql), dc);
    } catch (PageException pe) {
        // log
        log.error(StoredProc.class.getSimpleName(), pe);
        throw pe;
    } finally {
        if (callStat != null) {
            try {
                callStat.close();
            } catch (SQLException e) {
            }
        }
        manager.releaseConnection(pageContext, dc);
    }
    return EVAL_PAGE;
}
Also used : SQLImpl(lucee.runtime.db.SQLImpl) DatasourceConnection(lucee.runtime.db.DatasourceConnection) SQLException(java.sql.SQLException) DataSourceManager(lucee.runtime.db.DataSourceManager) Struct(lucee.runtime.type.Struct) StoredProcCacheItem(lucee.runtime.cache.tag.query.StoredProcCacheItem) QueryImpl(lucee.runtime.type.QueryImpl) Entry(java.util.Map.Entry) CallableStatement(java.sql.CallableStatement) CacheHandlerPro(lucee.runtime.cache.tag.CacheHandlerPro) ResultSet(java.sql.ResultSet) Iterator(java.util.Iterator) SQLItemImpl(lucee.runtime.db.SQLItemImpl) PageException(lucee.runtime.exp.PageException) Log(lucee.commons.io.log.Log) DataSource(lucee.runtime.db.DataSource) ApplicationException(lucee.runtime.exp.ApplicationException) StructImpl(lucee.runtime.type.StructImpl) CacheItem(lucee.runtime.cache.tag.CacheItem) StoredProcCacheItem(lucee.runtime.cache.tag.query.StoredProcCacheItem) CacheHandler(lucee.runtime.cache.tag.CacheHandler) DatabaseException(lucee.runtime.exp.DatabaseException) Key(lucee.runtime.type.Collection.Key) ConfigImpl(lucee.runtime.config.ConfigImpl)

Example 4 with DatasourceConnection

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

the class Update method doEndTag.

@Override
public int doEndTag() throws PageException {
    Object ds = DBInfo.getDatasource(pageContext, datasource);
    DataSourceManager manager = pageContext.getDataSourceManager();
    DatasourceConnection dc = ds instanceof DataSource ? manager.getConnection(pageContext, (DataSource) ds, username, password) : manager.getConnection(pageContext, Caster.toString(ds), username, password);
    try {
        Struct meta = null;
        try {
            meta = Insert.getMeta(dc, tablequalifier, tableowner, tablename);
        } catch (SQLException se) {
            meta = new StructImpl();
        }
        String[] pKeys = getPrimaryKeys(dc);
        SQL sql = createSQL(dc, pKeys, meta);
        if (sql != null) {
            lucee.runtime.type.Query query = new QueryImpl(pageContext, dc, sql, -1, -1, null, "query");
            if (pageContext.getConfig().debug()) {
                String dsn = ds instanceof DataSource ? ((DataSource) ds).getName() : Caster.toString(ds);
                boolean logdb = ((ConfigImpl) pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE);
                if (logdb) {
                    boolean debugUsage = DebuggerUtil.debugQueryUsage(pageContext, query);
                    pageContext.getDebugger().addQuery(debugUsage ? query : null, dsn, "", sql, query.getRecordcount(), pageContext.getCurrentPageSource(), query.getExecutionTime());
                }
            }
            // log
            Log log = pageContext.getConfig().getLog("datasource");
            if (log.getLogLevel() >= Log.LEVEL_INFO) {
                log.info("update tag", "executed [" + sql.toString().trim() + "] in " + DecimalFormat.call(pageContext, query.getExecutionTime() / 1000000D) + " ms");
            }
        }
        return EVAL_PAGE;
    } catch (PageException pe) {
        pageContext.getConfig().getLog("datasource").error("update tag", pe);
        throw pe;
    } finally {
        manager.releaseConnection(pageContext, dc);
    }
}
Also used : PageException(lucee.runtime.exp.PageException) DatasourceConnection(lucee.runtime.db.DatasourceConnection) SQLException(java.sql.SQLException) Log(lucee.commons.io.log.Log) DataSourceManager(lucee.runtime.db.DataSourceManager) DataSource(lucee.runtime.db.DataSource) Struct(lucee.runtime.type.Struct) SQL(lucee.runtime.db.SQL) QueryImpl(lucee.runtime.type.QueryImpl) StructImpl(lucee.runtime.type.StructImpl) ConfigImpl(lucee.runtime.config.ConfigImpl)

Example 5 with DatasourceConnection

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

the class IKHandlerDatasource method loadData.

@Override
public IKStorageValue loadData(PageContext pc, String appName, String name, String strType, int type, Log log) throws PageException {
    ConfigImpl config = (ConfigImpl) ThreadLocalPageContext.getConfig(pc);
    DatasourceConnectionPool pool = config.getDatasourceConnectionPool();
    DatasourceConnection dc = pool.getDatasourceConnection(config, pc.getDataSource(name), null, null);
    SQLExecutor executor = SQLExecutionFactory.getInstance(dc);
    Query query;
    try {
        if (!dc.getDatasource().isStorage())
            throw new ApplicationException("storage usage for this datasource is disabled, you can enable this in the Lucee administrator.");
        query = executor.select(config, pc.getCFID(), pc.getApplicationContext().getName(), dc, type, log, true);
    } catch (SQLException se) {
        throw Caster.toPageException(se);
    } finally {
        if (dc != null)
            pool.releaseDatasourceConnection(dc);
    }
    if (query != null && config.debug()) {
        boolean debugUsage = DebuggerUtil.debugQueryUsage(pc, query);
        pc.getDebugger().addQuery(debugUsage ? query : null, name, "", query.getSql(), query.getRecordcount(), ((PageContextImpl) pc).getCurrentPageSource(null), query.getExecutionTime());
    }
    boolean _isNew = query.getRecordcount() == 0;
    if (_isNew) {
        ScopeContext.info(log, "create new " + strType + " scope for " + pc.getApplicationContext().getName() + "/" + pc.getCFID() + " in datasource [" + name + "]");
        return null;
    }
    String str = Caster.toString(query.getAt(KeyConstants._data, 1));
    if (str.startsWith("struct:"))
        return null;
    try {
        IKStorageValue data = (IKStorageValue) JavaConverter.deserialize(str);
        ScopeContext.info(log, "load existing data from [" + name + "." + PREFIX + "_" + strType + "_data] to create " + strType + " scope for " + pc.getApplicationContext().getName() + "/" + pc.getCFID());
        return data;
    } catch (Exception e) {
        ScopeContext.error(log, e);
        return null;
    // throw Caster.toPageException(e);
    }
}
Also used : ApplicationException(lucee.runtime.exp.ApplicationException) DatasourceConnectionPool(lucee.runtime.db.DatasourceConnectionPool) DatasourceConnection(lucee.runtime.db.DatasourceConnection) Query(lucee.runtime.type.Query) SQLExecutor(lucee.runtime.type.scope.storage.db.SQLExecutor) SQLException(java.sql.SQLException) ConfigImpl(lucee.runtime.config.ConfigImpl) SQLException(java.sql.SQLException) ApplicationException(lucee.runtime.exp.ApplicationException) PageException(lucee.runtime.exp.PageException)

Aggregations

DatasourceConnection (lucee.runtime.db.DatasourceConnection)24 SQLException (java.sql.SQLException)14 ConfigImpl (lucee.runtime.config.ConfigImpl)10 PageException (lucee.runtime.exp.PageException)9 DataSource (lucee.runtime.db.DataSource)8 DatabaseException (lucee.runtime.exp.DatabaseException)8 Log (lucee.commons.io.log.Log)7 DatasourceConnectionPool (lucee.runtime.db.DatasourceConnectionPool)7 SQLExecutor (lucee.runtime.type.scope.storage.db.SQLExecutor)7 ApplicationException (lucee.runtime.exp.ApplicationException)5 DataSourceManager (lucee.runtime.db.DataSourceManager)4 PageRuntimeException (lucee.runtime.exp.PageRuntimeException)4 QueryImpl (lucee.runtime.type.QueryImpl)4 Struct (lucee.runtime.type.Struct)4 IOException (java.io.IOException)3 ResultSet (java.sql.ResultSet)3 StructImpl (lucee.runtime.type.StructImpl)3 Connection (java.sql.Connection)2 Iterator (java.util.Iterator)2 SQL (lucee.runtime.db.SQL)2