001/**
002 *
003 * Copyright (c) 2014, the Railo Company Ltd. All rights reserved.
004 *
005 * This library is free software; you can redistribute it and/or
006 * modify it under the terms of the GNU Lesser General Public
007 * License as published by the Free Software Foundation; either 
008 * version 2.1 of the License, or (at your option) any later version.
009 * 
010 * This library is distributed in the hope that it will be useful,
011 * but WITHOUT ANY WARRANTY; without even the implied warranty of
012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
013 * Lesser General Public License for more details.
014 * 
015 * You should have received a copy of the GNU Lesser General Public 
016 * License along with this library.  If not, see <http://www.gnu.org/licenses/>.
017 * 
018 **/
019package lucee.runtime.tag;
020
021import java.util.ArrayList;
022import java.util.List;
023import java.util.TimeZone;
024
025import lucee.commons.date.TimeZoneUtil;
026import lucee.commons.io.log.Log;
027import lucee.commons.io.log.LogUtil;
028import lucee.commons.lang.ClassException;
029import lucee.commons.lang.StringUtil;
030import lucee.runtime.PageContext;
031import lucee.runtime.PageContextImpl;
032import lucee.runtime.PageSource;
033import lucee.runtime.cache.tag.CacheHandler;
034import lucee.runtime.cache.tag.CacheHandlerFactory;
035import lucee.runtime.cache.tag.CacheItem;
036import lucee.runtime.cache.tag.query.QueryCacheItem;
037import lucee.runtime.config.ConfigImpl;
038import lucee.runtime.config.ConfigWebImpl;
039import lucee.runtime.config.ConfigWebUtil;
040import lucee.runtime.config.Constants;
041import lucee.runtime.db.DataSource;
042import lucee.runtime.db.DataSourceImpl;
043import lucee.runtime.db.DatasourceConnection;
044import lucee.runtime.db.DatasourceManagerImpl;
045import lucee.runtime.db.HSQLDBHandler;
046import lucee.runtime.db.SQL;
047import lucee.runtime.db.SQLImpl;
048import lucee.runtime.db.SQLItem;
049import lucee.runtime.debug.DebuggerPro;
050import lucee.runtime.debug.DebuggerUtil;
051import lucee.runtime.exp.ApplicationException;
052import lucee.runtime.exp.DatabaseException;
053import lucee.runtime.exp.ExpressionException;
054import lucee.runtime.exp.PageException;
055import lucee.runtime.ext.tag.BodyTagTryCatchFinallyImpl;
056import lucee.runtime.functions.displayFormatting.DecimalFormat;
057import lucee.runtime.listener.AppListenerUtil;
058import lucee.runtime.listener.ApplicationContextPro;
059import lucee.runtime.op.Caster;
060import lucee.runtime.op.Decision;
061import lucee.runtime.orm.ORMSession;
062import lucee.runtime.orm.ORMUtil;
063import lucee.runtime.tag.util.DeprecatedUtil;
064import lucee.runtime.tag.util.QueryParamConverter;
065import lucee.runtime.type.Array;
066import lucee.runtime.type.ArrayImpl;
067import lucee.runtime.type.Collection;
068import lucee.runtime.type.KeyImpl;
069import lucee.runtime.type.QueryColumn;
070import lucee.runtime.type.QueryImpl;
071import lucee.runtime.type.Struct;
072import lucee.runtime.type.StructImpl;
073import lucee.runtime.type.dt.DateTime;
074import lucee.runtime.type.dt.DateTimeImpl;
075import lucee.runtime.type.dt.TimeSpan;
076import lucee.runtime.type.dt.TimeSpanImpl;
077import lucee.runtime.type.query.SimpleQuery;
078import lucee.runtime.type.scope.Argument;
079import lucee.runtime.type.util.KeyConstants;
080import lucee.runtime.type.util.ListUtil;
081import lucee.runtime.util.PageContextUtil;
082
083
084
085/**
086* Passes SQL statements to a data source. Not limited to queries.
087**/
088public final class Query extends BodyTagTryCatchFinallyImpl {
089
090        private static final Collection.Key SQL_PARAMETERS = KeyImpl.intern("sqlparameters");
091        private static final Collection.Key CFQUERY = KeyImpl.intern("cfquery");
092        private static final Collection.Key GENERATEDKEY = KeyImpl.intern("generatedKey");
093        private static final Collection.Key MAX_RESULTS = KeyImpl.intern("maxResults");
094        private static final Collection.Key TIMEOUT = KeyConstants._timeout;
095        
096        private static final int RETURN_TYPE_QUERY = 1;
097        private static final int RETURN_TYPE_ARRAY_OF_ENTITY = 2;
098
099        
100        /** If specified, password overrides the password value specified in the data source setup. */
101        private String password;
102
103        /** The name of the data source from which this query should retrieve data. */
104        private DataSource datasource=null;
105
106        /** The maximum number of milliseconds for the query to execute before returning an error 
107        **              indicating that the query has timed-out. This attribute is not supported by most ODBC drivers. 
108        **              timeout is supported by the SQL Server 6.x or above driver. The minimum and maximum allowable values 
109        **              vary, depending on the driver. */
110        private TimeSpan timeout=null;
111
112        /** This is the age of which the query data can be */
113        private Object cachedWithin;
114        
115        /** Specifies the maximum number of rows to fetch at a time from the server. The range is 1, 
116        **              default to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. 
117        **              Certain ODBC drivers may dynamically reduce the block factor at runtime. */
118        private int blockfactor=-1;
119
120        /** The database driver type. */
121        private String dbtype;
122
123        /** Used for debugging queries. Specifying this attribute causes the SQL statement submitted to the 
124        **              data source and the number of records returned from the query to be returned. */
125        private boolean debug=true;
126
127        /* This is specific to JTags, and allows you to give the cache a specific name */
128        //private String cachename;
129
130        /** Specifies the maximum number of rows to return in the record set. */
131        private int maxrows=-1;
132
133        /** If specified, username overrides the username value specified in the data source setup. */
134        private String username;
135
136        /**  */
137        private DateTime cachedAfter;
138
139        /** The name query. Must begin with a letter and may consist of letters, numbers, and the underscore 
140        **              character, spaces are not allowed. The query name is used later in the page to reference the query's 
141        **              record set. */
142        private String name;
143        
144        private String result=null;
145
146        //private static HSQLDBHandler hsql=new HSQLDBHandler();
147        
148        private boolean orgPSQ;
149        private boolean hasChangedPSQ;
150        
151        ArrayList<SQLItem> items=new ArrayList<SQLItem>();
152        
153        private boolean clearCache;
154        private boolean unique;
155        private Struct ormoptions;
156        private int returntype=RETURN_TYPE_ARRAY_OF_ENTITY;
157        private TimeZone timezone;
158        private TimeZone tmpTZ;
159        private boolean lazy;
160        private Object params;
161        private int nestingLevel=0;
162        private boolean setReturnVariable=false;
163        private Object rtn;
164        
165
166        private boolean literalTimestampWithTSOffset;
167        private boolean previousLiteralTimestampWithTSOffset;
168        
169        @Override
170        public void release()   {
171                super.release();
172                items.clear();
173                password=null;
174                datasource=null;
175                timeout=null;
176                clearCache=false;
177                cachedWithin=null;
178                cachedAfter=null;
179                //cachename="";
180                blockfactor=-1;
181                dbtype=null;
182                debug=true;
183                maxrows=-1;
184                username=null;
185                name="";
186                result=null;
187                rtn=null;
188
189                orgPSQ=false;
190                hasChangedPSQ=false;
191                unique=false;
192                
193                ormoptions=null;
194                returntype=RETURN_TYPE_ARRAY_OF_ENTITY;
195                timezone=null;
196                tmpTZ=null;
197                lazy=false;
198                params=null;
199                nestingLevel=0;
200                setReturnVariable=false;
201                literalTimestampWithTSOffset=false;
202                previousLiteralTimestampWithTSOffset=false;
203        }
204        
205        
206        public void setOrmoptions(Struct ormoptions) {
207                this.ormoptions = ormoptions;
208        }
209
210
211        public void setReturntype(String strReturntype) throws ApplicationException {
212                if(StringUtil.isEmpty(strReturntype)) return;
213                strReturntype=strReturntype.toLowerCase().trim();
214                
215                if(strReturntype.equals("query"))
216                        returntype=RETURN_TYPE_QUERY;
217                    //mail.setType(lucee.runtime.mail.Mail.TYPE_TEXT);
218                else if(strReturntype.equals("array_of_entity") || strReturntype.equals("array-of-entity") || 
219                                strReturntype.equals("array_of_entities") || strReturntype.equals("array-of-entities") || 
220                                strReturntype.equals("arrayofentities") || strReturntype.equals("arrayofentities"))
221                        returntype=RETURN_TYPE_ARRAY_OF_ENTITY;
222                    //mail.setType(lucee.runtime.mail.Mail.TYPE_TEXT);
223                else
224                        throw new ApplicationException("attribute returntype of tag query has an invalid value","valid values are [query,array-of-entity] but value is now ["+strReturntype+"]");
225        }
226
227
228        public void setUnique(boolean unique) {
229                this.unique = unique;
230        }
231        /**
232         * @param result the result to set
233         */
234        public void setResult(String result) {
235                this.result = result;
236        }
237
238        /**
239         * @param psq set preserver single quote
240         */
241        public void setPsq(boolean psq) {
242                orgPSQ=pageContext.getPsq();
243        if(orgPSQ!=psq){
244                pageContext.setPsq(psq);
245                hasChangedPSQ=true;
246        }
247        }
248        
249        /** set the value password
250        *  If specified, password overrides the password value specified in the data source setup.
251        * @param password value to set
252        **/
253        public void setPassword(String password)        {
254                this.password=password;
255        }
256
257        /** set the value datasource
258        *  The name of the data source from which this query should retrieve data.
259        * @param datasource value to set
260         * @throws ClassException 
261        **/
262
263        public void setDatasource(Object datasource) throws PageException, ClassException       {
264                if (Decision.isStruct(datasource)) {
265                        this.datasource=AppListenerUtil.toDataSource("__temp__", Caster.toStruct(datasource));
266                } 
267                else if (Decision.isString(datasource)) {
268                        this.datasource=((PageContextImpl)pageContext).getDataSource(Caster.toString(datasource));
269                } 
270                else {
271                        throw new ApplicationException("attribute [datasource] must be datasource name or a datasource definition(struct)");
272                        
273                }
274        }
275
276        /** set the value timeout
277        *  The maximum number of milliseconds for the query to execute before returning an error 
278        *               indicating that the query has timed-out. This attribute is not supported by most ODBC drivers. 
279        *               timeout is supported by the SQL Server 6.x or above driver. The minimum and maximum allowable values 
280        *               vary, depending on the driver.
281        * @param timeout value to set
282         * @throws PageException 
283        **/
284        public void setTimeout(Object timeout) throws PageException     {
285                if(timeout instanceof TimeSpan)
286                        this.timeout=(TimeSpan) timeout;
287                // seconds
288                else {
289                        int i = Caster.toIntValue(timeout);
290                        if(i<0)
291                                throw new ApplicationException("invalid value ["+i+"] for attribute timeout, value must be a positive integer greater or equal than 0");
292                        
293                        this.timeout=new TimeSpanImpl(0, 0, 0, i);
294                }
295        }
296
297        /** set the value cachedafter
298        *  This is the age of which the query data can be
299        * @param cachedafter value to set
300        **/
301        public void setCachedafter(DateTime cachedafter)        {
302                //lucee.print.ln("cachedafter:"+cachedafter);
303                this.cachedAfter=cachedafter;
304        }
305
306        /** set the value cachename
307        *  This is specific to JTags, and allows you to give the cache a specific name
308        * @param cachename value to set
309        **/
310        public void setCachename(String cachename)      {
311                DeprecatedUtil.tagAttribute(pageContext,"query", "cachename");
312                //this.cachename=cachename;
313        }
314
315        /** set the value cachedwithin
316        *  
317        * @param cachedwithin value to set
318        **/
319        public void setCachedwithin(TimeSpan cachedwithin)      {
320                if(cachedwithin.getMillis()>0)
321                        this.cachedWithin=cachedwithin;
322                else clearCache=true;
323        }
324        
325        public void setCachedwithin(Object cachedwithin) throws PageException   {
326                if(cachedwithin instanceof String) {
327                        String str=((String)cachedwithin).trim();
328                        if("request".equalsIgnoreCase(str)) {
329                                this.cachedWithin="request";
330                                return;
331                        }
332                }
333                setCachedwithin(Caster.toTimespan(cachedwithin));
334        }
335        
336        public void setLazy(boolean lazy)       {
337                this.lazy=lazy;
338        }
339
340        /** set the value providerdsn
341        *  Data source name for the COM provider, OLE-DB only.
342        * @param providerdsn value to set
343         * @throws ApplicationException
344        **/
345        public void setProviderdsn(String providerdsn) throws ApplicationException      {
346                DeprecatedUtil.tagAttribute(pageContext,"Query", "providerdsn");
347        }
348
349        /** set the value connectstring
350        * @param connectstring value to set
351         * @throws ApplicationException
352        **/
353        public void setConnectstring(String connectstring) throws ApplicationException  {
354                DeprecatedUtil.tagAttribute(pageContext,"Query", "connectstring");
355        }
356        
357
358        public void setTimezone(String timezone) throws ExpressionException     {
359            this.timezone=TimeZoneUtil.toTimeZone(timezone);
360        }
361
362        /** set the value blockfactor
363        *  Specifies the maximum number of rows to fetch at a time from the server. The range is 1, 
364        *               default to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. 
365        *               Certain ODBC drivers may dynamically reduce the block factor at runtime.
366        * @param blockfactor value to set
367        **/
368        public void setBlockfactor(double blockfactor)  {
369                this.blockfactor=(int) blockfactor;
370        }
371
372        /** set the value dbtype
373        *  The database driver type.
374        * @param dbtype value to set
375        **/
376        public void setDbtype(String dbtype)    {
377                this.dbtype=dbtype.toLowerCase();
378        }
379
380        /** set the value debug
381        *  Used for debugging queries. Specifying this attribute causes the SQL statement submitted to the 
382        *               data source and the number of records returned from the query to be returned.
383        * @param debug value to set
384        **/
385        public void setDebug(boolean debug)     {
386                this.debug=debug;
387        }
388
389        /** set the value dbname
390        *  The database name, Sybase System 11 driver and SQLOLEDB provider only. If specified, dbName 
391        *               overrides the default database specified in the data source.
392        * @param dbname value to set
393         * @throws ApplicationException
394        **/
395        public void setDbname(String dbname) {
396                DeprecatedUtil.tagAttribute(pageContext,"Query", "dbname");
397        }
398
399        /** set the value maxrows
400        *  Specifies the maximum number of rows to return in the record set.
401        * @param maxrows value to set
402        **/
403        public void setMaxrows(double maxrows)  {
404                this.maxrows=(int) maxrows;
405        }
406
407        /** set the value username
408        *  If specified, username overrides the username value specified in the data source setup.
409        * @param username value to set
410        **/
411        public void setUsername(String username)        {
412                if(!StringUtil.isEmpty(username))
413                        this.username=username;
414        }
415
416        /** set the value provider
417        *  COM provider, OLE-DB only.
418        * @param provider value to set
419         * @throws ApplicationException
420        **/
421        public void setProvider(String provider) {
422                DeprecatedUtil.tagAttribute(pageContext,"Query", "provider");
423        }
424
425        /** set the value dbserver
426        *  For native database drivers and the SQLOLEDB provider, specifies the name of the database server 
427        *               computer. If specified, dbServer overrides the server specified in the data source.
428        * @param dbserver value to set
429         * @throws ApplicationException
430        **/
431        public void setDbserver(String dbserver) {
432                DeprecatedUtil.tagAttribute(pageContext,"Query", "dbserver");
433        }
434
435        /** set the value name
436        *  The name query. Must begin with a letter and may consist of letters, numbers, and the underscore 
437        *               character, spaces are not allowed. The query name is used later in the page to reference the query's 
438        *               record set.
439        * @param name value to set
440        **/
441        public void setName(String name)        {
442                this.name=name;
443        }
444        
445        public String getName() {
446                return name==null? "query":name;
447        }
448        
449        
450        
451
452
453    /**
454     * @param item
455     */
456    public void setParam(SQLItem item) {
457        items.add(item);
458    }
459    
460    public void setParams(Object params) {
461        this.params=params;
462    }
463    
464    public void setNestinglevel(double nestingLevel) {
465        this.nestingLevel=(int)nestingLevel;
466    }
467    
468    
469    
470
471        @Override
472        public int doStartTag() throws PageException    {
473
474                //timeout not defined
475                if(timeout==null || ((int)timeout.getSeconds())<=0) { // not set
476                        this.timeout=PageContextUtil.remainingTime(pageContext,true);
477                }
478                // timeout bigger than remaining time
479                else {
480                        TimeSpan remaining = PageContextUtil.remainingTime(pageContext,true);
481                        if(timeout.getSeconds()>remaining.getSeconds())
482                                timeout=remaining;
483                }
484                
485                
486                
487                // default datasource
488                if(datasource==null && (dbtype==null || !dbtype.equals("query"))){
489                        Object obj = ((ApplicationContextPro)pageContext.getApplicationContext()).getDefDataSource();
490                        if(StringUtil.isEmpty(obj))
491                                throw new ApplicationException(
492                                                "attribute [datasource] is required, when attribute [dbtype] has not value [query] and no default datasource is defined",
493                                                "you can define a default datasource as attribute [defaultdatasource] of the tag "+Constants.CFAPP_NAME+" or as data member of the "+Constants.APP_CFC+" (this.defaultdatasource=\"mydatasource\";)");
494                        
495                        datasource=obj instanceof DataSource?(DataSource)obj:((PageContextImpl)pageContext).getDataSource(Caster.toString(obj));
496                }
497                
498                PageContextImpl pci = ((PageContextImpl)pageContext);
499                
500                // timezone
501                if(timezone!=null || (datasource!=null && (timezone=datasource.getTimeZone())!=null)) {
502                        tmpTZ=pageContext.getTimeZone();
503                        pageContext.setTimeZone(timezone);
504                }
505                
506                // literal timestamp with TSOffset
507                if(datasource instanceof DataSourceImpl) 
508                        literalTimestampWithTSOffset=((DataSourceImpl)datasource).getLiteralTimestampWithTSOffset();
509                else 
510                        literalTimestampWithTSOffset=false;
511                
512                previousLiteralTimestampWithTSOffset=pci.getTimestampWithTSOffset();
513                pci.setTimestampWithTSOffset(literalTimestampWithTSOffset);
514                                
515                return EVAL_BODY_BUFFERED;
516        }
517
518        @Override
519
520        
521        public int doEndTag() throws PageException      {
522                
523                
524                if(hasChangedPSQ)pageContext.setPsq(orgPSQ);
525                String strSQL=bodyContent.getString();
526                try {
527                        
528                        // no SQL String defined
529                        if(strSQL.length()==0) 
530                                throw new DatabaseException("no sql string defined, inside query tag",null,null,null);
531                        // cannot use attribute params and queryparam tag
532                        if(items.size()>0 && params!=null)
533                                throw new DatabaseException("you cannot use the attribute params and sub tags queryparam at the same time",null,null,null);
534                        // create SQL
535                        SQL sql;
536                        if(params!=null) {
537                                if(params instanceof Argument)
538                                        sql=QueryParamConverter.convert(strSQL, (Argument) params);
539                                else if(Decision.isArray(params))
540                                        sql=QueryParamConverter.convert(strSQL, Caster.toArray(params));
541                                else if(Decision.isStruct(params))
542                                        sql=QueryParamConverter.convert(strSQL, Caster.toStruct(params));
543                                else
544                                        throw new DatabaseException("value of the attribute [params] has to be a struct or a array",null,null,null);
545                        }
546                        else sql=items.size()>0?new SQLImpl(strSQL,items.toArray(new SQLItem[items.size()])):new SQLImpl(strSQL);
547                        
548                        lucee.runtime.type.Query query=null;
549                        long exe=0;
550                        boolean hasCached=cachedWithin!=null || cachedAfter!=null;
551                        String cacheType=null;
552                        
553                        if(clearCache) {
554                                hasCached=false;
555                                String id = CacheHandlerFactory.createId(sql,datasource!=null?datasource.getName():null,username,password);
556                                CacheHandler ch = ConfigWebUtil.getCacheHandlerFactories(pageContext.getConfig()).query.getInstance(pageContext.getConfig(), CacheHandlerFactory.TYPE_TIMESPAN);
557                                ch.remove(pageContext, id);
558                        }
559                        else if(hasCached) {
560                                String id = CacheHandlerFactory.createId(sql,datasource!=null?datasource.getName():null,username,password);
561                                CacheHandler ch = ConfigWebUtil.getCacheHandlerFactories(pageContext.getConfig()).query.getInstance(pageContext.getConfig(), cachedWithin);
562                                if(ch!=null) {
563                                        cacheType=ch.label();
564                                        CacheItem ci = ch.get(pageContext, id);
565                                        
566                                        if(ci instanceof QueryCacheItem) {
567                                                QueryCacheItem ce = (QueryCacheItem) ci;
568                                                if(ce.isCachedAfter(cachedAfter))
569                                                        query= (lucee.runtime.type.Query) ce.query.duplicate(true);
570                                        }
571                                }
572                        }
573                        
574                        
575                        if(query==null) {
576                                if("query".equals(dbtype))              query=executeQoQ(sql);
577                                else if("orm".equals(dbtype) || "hql".equals(dbtype))   {
578                                        long start=System.nanoTime();
579                                        Object obj = executeORM(sql,returntype,ormoptions);
580                                        
581                                        if(obj instanceof lucee.runtime.type.Query){
582                                                query=(lucee.runtime.type.Query) obj;
583                                        }
584                                        else {
585                                                if(setReturnVariable){
586                                                        rtn=obj;
587                                                }
588                                                else if(!StringUtil.isEmpty(name)) {
589                                                        pageContext.setVariable(name,obj);
590                                                }
591                                                if(result!=null){
592                                                        Struct sct=new StructImpl();
593                                                        sct.setEL(KeyConstants._cached, Boolean.FALSE);
594                                                        long time=System.nanoTime()-start;
595                                                        sct.setEL(KeyConstants._executionTime, Caster.toDouble(time/1000000));
596                                                        sct.setEL(KeyConstants._executionTimeNano, Caster.toDouble(time));
597                                                        sct.setEL(KeyConstants._SQL, sql.getSQLString());
598                                                        if(Decision.isArray(obj)){
599                                                                
600                                                        }
601                                                        else sct.setEL(KeyConstants._RECORDCOUNT, Caster.toDouble(1));
602                                                                
603                                                        pageContext.setVariable(result, sct);
604                                                }
605                                                else
606                                                        setExecutionTime((System.nanoTime()-start)/1000000);
607                                                return EVAL_PAGE;
608                                        }
609                                }
610                                else query=executeDatasoure(sql,result!=null,pageContext.getTimeZone());
611                                //query=(dbtype!=null && dbtype.equals("query"))?executeQoQ(sql):executeDatasoure(sql,result!=null);
612                                
613                                if(cachedWithin!=null) {
614                                        DateTimeImpl cachedBefore = null;
615                                        //if(cachedWithin!=null)
616                                        String id = CacheHandlerFactory.createId(sql,datasource!=null?datasource.getName():null,username,password);
617                                        CacheHandler ch = ConfigWebUtil.getCacheHandlerFactories(pageContext.getConfig()).query.getInstance(pageContext.getConfig(), cachedWithin);
618                                        ch.set(pageContext, id,cachedWithin,new QueryCacheItem((lucee.runtime.type.Query)query.duplicate(true)));
619                                }
620                                exe=query.getExecutionTime();
621                        }
622                else {
623                        if(query instanceof QueryImpl) ((QueryImpl)query).setCacheType(cacheType); // FUTURE add method to interface
624                        else query.setCached(hasCached);
625                }
626                        
627                        if(pageContext.getConfig().debug() && debug) {
628                                boolean logdb=((ConfigImpl)pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE);
629                                if(logdb){
630                                        boolean debugUsage=DebuggerUtil.debugQueryUsage(pageContext,query);
631                                        
632                                        ((DebuggerPro)pageContext.getDebugger())
633                                        .addQuery(debugUsage?query:null,datasource!=null?datasource.getName():null,name,sql,query.getRecordcount(),getSource(),exe);
634                                }
635                        }
636                        
637                        if(setReturnVariable){
638                                rtn=query;
639                        }
640                        else if(!query.isEmpty() && !StringUtil.isEmpty(name)) {
641                                pageContext.setVariable(name,query);
642                        }
643                        
644                        // Result
645                        if(result!=null) {
646                                
647                                Struct sct=new StructImpl();
648                                sct.setEL(KeyConstants._cached, Caster.toBoolean(query.isCached()));
649                                if(!query.isEmpty())sct.setEL(KeyConstants._COLUMNLIST, ListUtil.arrayToList(query.getColumnNamesAsString(),","));
650                                int rc=query.getRecordcount();
651                                if(rc==0)rc=query.getUpdateCount();
652                                sct.setEL(KeyConstants._RECORDCOUNT, Caster.toDouble(rc));
653                                sct.setEL(KeyConstants._executionTime, Caster.toDouble(query.getExecutionTime()/1000000));
654                                sct.setEL(KeyConstants._executionTimeNano, Caster.toDouble(query.getExecutionTime()));
655                                
656                                sct.setEL(KeyConstants._SQL, sql.getSQLString());
657                                
658                                // GENERATED KEYS
659                                lucee.runtime.type.Query qi = Caster.toQuery(query,null);
660                                if(qi !=null){
661                                        lucee.runtime.type.Query qryKeys = qi.getGeneratedKeys();
662                                        if(qryKeys!=null){
663                                                StringBuilder generatedKey=new StringBuilder(),sb;
664                                                Collection.Key[] columnNames = qryKeys.getColumnNames();
665                                                QueryColumn column;
666                                                for(int c=0;c<columnNames.length;c++){
667                                                        column = qryKeys.getColumn(columnNames[c]);
668                                                        sb=new StringBuilder();
669                                                        int size=column.size();
670                                                        for(int row=1;row<=size;row++) {
671                                                                if(row>1)sb.append(',');
672                                                                sb.append(Caster.toString(column.get(row,null)));
673                                                        }
674                                                        if(sb.length()>0){
675                                                                sct.setEL(columnNames[c], sb.toString());
676                                                                if(generatedKey.length()>0)generatedKey.append(',');
677                                                                generatedKey.append(sb);
678                                                        }
679                                                }
680                                                if(generatedKey.length()>0)
681                                                        sct.setEL(GENERATEDKEY, generatedKey.toString());
682                                        }
683                                }
684                                
685                                // sqlparameters
686                                SQLItem[] params = sql.getItems();
687                                if(params!=null && params.length>0) {
688                                        Array arr=new ArrayImpl();
689                                        sct.setEL(SQL_PARAMETERS, arr); 
690                                        for(int i=0;i<params.length;i++) {
691                                                arr.append(params[i].getValue());
692                                                
693                                        }
694                                }
695                                pageContext.setVariable(result, sct);
696                        }
697                        // cfquery.executiontime
698                        else {
699                                setExecutionTime(exe/1000000);
700                                
701                        }
702                        
703                        
704                        // listener
705                        ((ConfigWebImpl)pageContext.getConfig()).getActionMonitorCollector()
706                                .log(pageContext, "query", "Query", exe, query);
707                        
708                        // log
709                        Log log = ((ConfigWebImpl)pageContext.getConfig()).getLog("datasource", true);
710                        if(log.getLogLevel()>=Log.LEVEL_INFO) {
711                                log.info("query tag", "executed ["+sql.toString().trim()+"] in "+DecimalFormat.call(pageContext, exe/1000000D)+" ms");
712                        }
713                
714                }
715                catch (PageException pe) {
716                        // log
717                        LogUtil.log(((ConfigWebImpl)pageContext.getConfig()).getLog("datasource", true)
718                                        , Log.LEVEL_ERROR, "query tag", pe);
719                        
720                        throw pe;
721                }
722                finally {
723                        ((PageContextImpl)pageContext).setTimestampWithTSOffset(previousLiteralTimestampWithTSOffset);
724                        if(tmpTZ!=null) {
725                                pageContext.setTimeZone(tmpTZ);
726                        }
727                }
728                
729                return EVAL_PAGE;
730        }
731
732
733        private PageSource getSource() {
734                PageSource source=null;
735                if(nestingLevel>0) {
736                        PageContextImpl pci=(PageContextImpl) pageContext;
737                        List<PageSource> list = pci.getPageSourceList();
738                        int index=list.size()-1-nestingLevel;
739                        if(index>=0) source=list.get(index);
740                }
741                if(source==null) source=pageContext.getCurrentPageSource();
742                return source;
743        }
744
745
746        private void setExecutionTime(long exe) {
747                Struct sct=new StructImpl();
748                sct.setEL(KeyConstants._executionTime,new Double(exe));
749                pageContext.undefinedScope().setEL(CFQUERY,sct);
750        }
751
752
753        private Object executeORM(SQL sql, int returnType, Struct ormoptions) throws PageException {
754                ORMSession session=ORMUtil.getSession(pageContext);
755                
756                if(ormoptions==null) ormoptions=new StructImpl();
757                String dsn = null;
758                if (ormoptions!=null) dsn =     Caster.toString(ormoptions.get(KeyConstants._datasource,null),null);
759                if(StringUtil.isEmpty(dsn,true)) dsn=ORMUtil.getDefaultDataSource(pageContext).getName();
760                
761                // params
762                SQLItem[] _items = sql.getItems();
763                Array params=new ArrayImpl();
764                for(int i=0;i<_items.length;i++){
765                        params.appendEL(_items[i]);
766                }
767                
768                // query options
769                if(maxrows!=-1 && !ormoptions.containsKey(MAX_RESULTS)) ormoptions.setEL(MAX_RESULTS, new Double(maxrows));
770                if(timeout!=null && ((int)timeout.getSeconds())>0 && !ormoptions.containsKey(TIMEOUT)) ormoptions.setEL(TIMEOUT, new Double(timeout.getSeconds()));
771                /* MUST
772offset: Specifies the start index of the resultset from where it has to start the retrieval.
773cacheable: Whether the result of this query is to be cached in the secondary cache. Default is false.
774cachename: Name of the cache in secondary cache.
775                 */
776                Object res = session.executeQuery(pageContext,dsn,sql.getSQLString(),params,unique,ormoptions);
777                if(returnType==RETURN_TYPE_ARRAY_OF_ENTITY) return res;
778                return session.toQuery(pageContext, res, null);
779                
780        }
781        
782        public static Object _call(PageContext pc,String hql, Object params, boolean unique, Struct queryOptions) throws PageException {
783                ORMSession session=ORMUtil.getSession(pc);
784                String dsn = Caster.toString(queryOptions.get(KeyConstants._datasource,null),null);
785                if(StringUtil.isEmpty(dsn,true)) dsn=ORMUtil.getDefaultDataSource(pc).getName();
786                
787                
788                if(Decision.isCastableToArray(params))
789                        return session.executeQuery(pc,dsn,hql,Caster.toArray(params),unique,queryOptions);
790                else if(Decision.isCastableToStruct(params))
791                        return session.executeQuery(pc,dsn,hql,Caster.toStruct(params),unique,queryOptions);
792                else
793                        return session.executeQuery(pc,dsn,hql,(Array)params,unique,queryOptions);
794        }
795        
796
797        private lucee.runtime.type.Query executeQoQ(SQL sql) throws PageException {
798                try {
799                        return new HSQLDBHandler().execute(pageContext,sql,maxrows,blockfactor,timeout);
800                } 
801                catch (Exception e) {
802                        throw Caster.toPageException(e);
803                } 
804        }
805        
806        private lucee.runtime.type.Query executeDatasoure(SQL sql,boolean createUpdateData,TimeZone tz) throws PageException {
807                DatasourceManagerImpl manager = (DatasourceManagerImpl) pageContext.getDataSourceManager();
808                DatasourceConnection dc=manager.getConnection(pageContext,datasource, username, password);
809                
810                try {
811                        if(lazy && !createUpdateData && cachedWithin==null && cachedAfter==null && result==null)
812                                return new SimpleQuery(pageContext,dc,sql,maxrows,blockfactor,timeout,getName(),getSource().getDisplayPath(),tz);
813                        
814                        
815                        return new QueryImpl(pageContext,dc,sql,maxrows,blockfactor,timeout,getName(),getSource().getDisplayPath(),createUpdateData,true);
816                }
817                finally {
818                        manager.releaseConnection(pageContext,dc);
819                }
820        }
821        
822
823        @Override
824        public void doInitBody()        {
825                
826        }
827
828        @Override
829        public int doAfterBody()        {
830                return SKIP_BODY;
831        }
832
833
834        public void setReturnVariable(boolean setReturnVariable) {
835                this.setReturnVariable=setReturnVariable;
836                
837        }
838        public Object getReturnVariable() {
839                return rtn;
840                
841        }
842}