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);
}
}
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;
}
}
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;
}
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);
}
}
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);
}
}
Aggregations