001/**
002 * Copyright (c) 2014, the Railo Company Ltd.
003 * Copyright (c) 2015, Lucee Assosication Switzerland
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.io.IOException;
022import java.sql.CallableStatement;
023import java.sql.Connection;
024import java.sql.DatabaseMetaData;
025import java.sql.ResultSet;
026import java.sql.SQLException;
027import java.sql.Types;
028import java.util.ArrayList;
029import java.util.Date;
030import java.util.Iterator;
031import java.util.List;
032import java.util.Map;
033import java.util.Map.Entry;
034
035import javax.servlet.jsp.JspException;
036
037import lucee.commons.io.IOUtil;
038import lucee.commons.io.log.Log;
039import lucee.commons.io.log.LogUtil;
040import lucee.commons.lang.ExceptionUtil;
041import lucee.commons.lang.StringUtil;
042import lucee.commons.sql.SQLUtil;
043import lucee.loader.engine.CFMLEngine;
044import lucee.runtime.PageContextImpl;
045import lucee.runtime.cache.tag.CacheHandler;
046import lucee.runtime.cache.tag.CacheHandlerFactory;
047import lucee.runtime.cache.tag.CacheItem;
048import lucee.runtime.cache.tag.query.StoredProcCacheItem;
049import lucee.runtime.config.Config;
050import lucee.runtime.config.ConfigImpl;
051import lucee.runtime.config.ConfigWeb;
052import lucee.runtime.config.ConfigWebImpl;
053import lucee.runtime.config.ConfigWebUtil;
054import lucee.runtime.config.Constants;
055import lucee.runtime.db.CFTypes;
056import lucee.runtime.db.DataSource;
057import lucee.runtime.db.DataSourceManager;
058import lucee.runtime.db.DataSourceSupport;
059import lucee.runtime.db.DataSourceUtil;
060import lucee.runtime.db.DatasourceConnection;
061import lucee.runtime.db.ProcMeta;
062import lucee.runtime.db.ProcMetaCollection;
063import lucee.runtime.db.SQLCaster;
064import lucee.runtime.db.SQLImpl;
065import lucee.runtime.db.SQLItemImpl;
066import lucee.runtime.engine.ThreadLocalPageContext;
067import lucee.runtime.exp.ApplicationException;
068import lucee.runtime.exp.DatabaseException;
069import lucee.runtime.exp.PageException;
070import lucee.runtime.ext.tag.BodyTagTryCatchFinallySupport;
071import lucee.runtime.functions.displayFormatting.DecimalFormat;
072import lucee.runtime.listener.ApplicationContextPro;
073import lucee.runtime.op.Caster;
074import lucee.runtime.tag.util.DeprecatedUtil;
075import lucee.runtime.type.Array;
076import lucee.runtime.type.ArrayImpl;
077import lucee.runtime.type.Collection.Key;
078import lucee.runtime.type.KeyImpl;
079import lucee.runtime.type.QueryImpl;
080import lucee.runtime.type.Struct;
081import lucee.runtime.type.StructImpl;
082import lucee.runtime.type.dt.DateTime;
083import lucee.runtime.type.dt.TimeSpan;
084import lucee.runtime.type.util.KeyConstants;
085
086
087
088
089
090
091public class StoredProc extends BodyTagTryCatchFinallySupport {
092        //private static final int PROCEDURE_CAT=1;        
093        //private static final int PROCEDURE_SCHEM=2;
094        //private static final int PROCEDURE_NAME=3;
095        //private static final int COLUMN_NAME=4;
096        private static final int COLUMN_TYPE=5;
097        private static final int DATA_TYPE=6;
098        private static final int TYPE_NAME=7;
099        //|PRECISION|LENGTH|SCALE|RADIX|NULLABLE|REMARKS|SEQUENCE|OVERLOAD|DEFAULT_VALUE
100        
101
102        private static final lucee.runtime.type.Collection.Key KEY_SC = KeyImpl.intern("StatusCode");
103        
104        private static final lucee.runtime.type.Collection.Key COUNT = KeyImpl.intern("count_afsdsfgdfgdsfsdfsgsdgsgsdgsasegfwef");
105        
106        private static final ProcParamBean STATUS_CODE;
107        private static final lucee.runtime.type.Collection.Key STATUSCODE = KeyImpl.intern("StatusCode");
108        
109        static{
110                STATUS_CODE = new ProcParamBean();
111                STATUS_CODE.setType(Types.INTEGER);
112                STATUS_CODE.setDirection(ProcParamBean.DIRECTION_OUT);
113                STATUS_CODE.setVariable("cfstoredproc.statusCode");
114        }
115        
116        
117        private List<ProcParamBean> params=new ArrayList<ProcParamBean>();
118        private Array results=new ArrayImpl();
119
120        private String procedure;
121        private String datasource=null;
122        private String username;
123        private String password;
124        private int blockfactor=-1;
125        private int timeout=-1;
126        private boolean debug=true;
127        private boolean returncode;
128        private String result="cfstoredproc";
129        
130        private boolean clearCache;
131        //private DateTimeImpl cachedbefore;
132        //private String cachename="";
133        private DateTime cachedafter;
134        private ProcParamBean returnValue=null;
135        private Object cachedWithin;
136        //private Map<String,ProcMetaCollection> procedureColumnCache;
137        
138        @Override
139        public void release() {
140                params.clear();
141                results.clear();
142                returnValue=null;
143                procedure=null;
144                datasource=null;
145                username=null;
146                password=null;
147                blockfactor=-1;
148                timeout=-1;
149                debug=true;
150                returncode=false;
151                result="cfstoredproc";
152                
153
154                clearCache=false;
155                cachedWithin=null;
156                cachedafter=null;
157                //cachename="";
158                
159                super.release();
160        }
161        
162        
163
164
165        /** set the value cachedafter
166        *  This is the age of which the query data can be
167        * @param cachedafter value to set
168        **/
169        public void setCachedafter(DateTime cachedafter)        {
170                //lucee.print.ln("cachedafter:"+cachedafter);
171                this.cachedafter=cachedafter;
172        }
173
174        /** set the value cachename
175        *  This is specific to JTags, and allows you to give the cache a specific name
176        * @param cachename value to set
177        **/
178        public void setCachename(String cachename)      {
179                DeprecatedUtil.tagAttribute(pageContext,"StoredProc", "cachename");
180        }
181
182        /** set the value cachedwithin
183        *  
184        * @param cachedwithin value to set
185        *
186        public void setCachedwithin(TimeSpan cachedwithin)      {
187                if(cachedwithin.getMillis()>0)
188                        this.cachedbefore=new DateTimeImpl(pageContext,System.currentTimeMillis()+cachedwithin.getMillis(),false);
189                else clearCache=true;
190        }*/
191        
192        /** set the value cachedwithin
193        *  
194        * @param cachedwithin value to set
195        **/
196        public void setCachedwithin(TimeSpan cachedwithin)      {
197                if(cachedwithin.getMillis()>0)
198                        this.cachedWithin=cachedwithin;
199                else clearCache=true;
200        }
201        
202        public void setCachedwithin(Object cachedwithin) throws PageException   {
203                if(cachedwithin instanceof String) {
204                        String str=((String)cachedwithin).trim();
205                        if("request".equalsIgnoreCase(str)) {
206                                this.cachedWithin="request";
207                                return;
208                        }
209                }
210                setCachedwithin(Caster.toTimespan(cachedwithin));
211        }
212        
213        
214        
215
216        /**
217         * @param blockfactor The blockfactor to set.
218         */
219        public void setBlockfactor(double blockfactor) {
220                this.blockfactor = (int) blockfactor;
221        }
222        
223        /**
224         * @param blockfactor
225         * @deprecated replaced with setBlockfactor(double)
226         */
227        @Deprecated
228        public void setBlockfactor(int blockfactor) {
229                DeprecatedUtil.tagAttribute(pageContext,"storedproc","blockfactor");
230                this.blockfactor = blockfactor;
231        }
232
233        /**
234         * @param datasource The datasource to set.
235         */
236        public void setDatasource(String datasource) {
237                this.datasource = datasource;
238        }
239
240        /**
241         * @param username The username to set.
242         */
243        public void setUsername(String username) {
244                this.username = username;
245        }
246
247        /**
248         * @param password The password to set.
249         */
250        public void setPassword(String password) {
251                this.password = password;
252        }
253
254        /**
255         * @param debug The debug to set.
256         */
257        public void setDebug(boolean debug) {
258                this.debug = debug;
259        }
260
261        /**
262         * @param procedure The procedure to set.
263         */
264        public void setProcedure(String procedure) {
265                this.procedure = procedure;
266        }
267
268        /**
269         * @param result The result to set.
270         */
271        public void setResult(String result) {
272                this.result = result;
273        }
274
275        /**
276         * @param returncode The returncode to set.
277         */
278        public void setReturncode(boolean returncode) {
279                this.returncode = returncode;
280        }
281
282        /**
283         * @param dbvarname the dbvarname to set
284         */
285        public void setDbvarname(String dbvarname) {
286                DeprecatedUtil.tagAttribute(pageContext,"storedproc","dbvarname");
287        }
288        public void setDbtype(String dbtype) {
289                DeprecatedUtil.tagAttribute(pageContext,"storedproc","dbtype");
290        }
291
292        public void addProcParam(ProcParamBean param) {
293                params.add(param);
294        }
295
296        public void addProcResult(ProcResultBean result) {
297                results.setEL(result.getResultset(),result);
298        }
299
300        @Override
301        public int doStartTag() throws JspException {
302                
303                return EVAL_BODY_INCLUDE;
304        }
305
306        private void returnValue(DatasourceConnection dc) throws PageException {
307                Connection conn = dc.getConnection();
308                if(SQLUtil.isOracle(conn)){
309                        String name=this.procedure.toUpperCase().trim();
310                        
311                        // split procedure definition 
312                        String catalog=null,scheme=null;
313                        {
314                                int index=name.lastIndexOf('.');
315                        if(index!=-1){
316                                        catalog=name.substring(0,index).trim();
317                                        name=name.substring(index+1).trim();
318                                
319                                        index=catalog.lastIndexOf('.');
320                                if(index!=-1){
321                                scheme=catalog.substring(0,index).trim();
322                                catalog=catalog.substring(index+1).trim();
323                                                //scheme=catalog.substring(index+1);
324                                                //catalog=catalog.substring(0,index);
325                                }
326                        }
327                                if(StringUtil.isEmpty(scheme)) scheme=null;
328                                if(StringUtil.isEmpty(catalog)) catalog=null;
329                        }
330                        
331                        
332                        try {
333                                                                
334                                
335                                //if(procedureColumnCache==null)procedureColumnCache=new ReferenceMap();
336                                //ProcMetaCollection coll=procedureColumnCache.get(procedure);
337                                DataSourceSupport d = ((DataSourceSupport)dc.getDatasource());
338                                long cacheTimeout = d.getMetaCacheTimeout();
339                                Map<String, ProcMetaCollection> procedureColumnCache = d.getProcedureColumnCache();
340                                String id=procedure.toLowerCase();
341                                ProcMetaCollection coll=procedureColumnCache.get(id);
342                                
343                                if(coll==null || (cacheTimeout>=0 && (coll.created+cacheTimeout)<System.currentTimeMillis())) {
344                                        DatabaseMetaData md = conn.getMetaData();
345                                        String _catalog=null,_scheme=null,_name=null;
346                                        boolean available=false;
347                                        /*print.e("pro:"+procedure);
348                                        print.e("cat:"+catalog);
349                                        print.e("sch:"+scheme);
350                                        print.e("nam:"+name);*/
351                                        ResultSet proc = md.getProcedures(null, null, name);
352                                        try {
353                                                while (proc.next()) {
354                                                        _catalog = proc.getString(1);
355                                                        _scheme = proc.getString(2);
356                                                        _name = proc.getString(3);
357                                                        if(
358                                                                        _name.equalsIgnoreCase(name)
359                                                                        &&
360                                                                        (catalog==null || _catalog==null || catalog.equalsIgnoreCase(_catalog)) // second option is very unlikely to ever been the case, but does not hurt to test
361                                                                        &&
362                                                                        (scheme==null || _scheme==null || scheme.equalsIgnoreCase(_scheme)) // second option is very unlikely to ever been the case, but does not hurt to test
363                                                          ) {
364                                                                available=true;
365                                                                break;
366                                                        }
367                                                }
368                                        }
369                                        finally {
370                                                IOUtil.closeEL(proc);
371                                        }
372                                        
373                                        
374                                        
375                                        
376                                        if(available) {         
377                                                /*print.e("---------------");
378                                                print.e("_pro:"+procedure);
379                                                print.e("_cat:"+_catalog);
380                                                print.e("_sch:"+_scheme);
381                                                print.e("_nam:"+_name);*/
382                                                ResultSet res = md.getProcedureColumns(_catalog, _scheme, _name, "%");
383                                        coll=createProcMetaCollection(res);
384                                                procedureColumnCache.put(id,coll);
385                                        }
386                                }
387                                
388                                int index=-1;
389                                int ct;
390                                if(coll!=null) {
391                                        Iterator<ProcMeta> it = coll.metas.iterator();
392                                        ProcMeta pm;
393                                        while(it.hasNext()) { 
394                                        index++;
395                                                pm=it.next();
396                                                ct=pm.columnType;
397                                        
398                                        // Return
399                                                if(ct==DatabaseMetaData.procedureColumnReturn) {
400                                                index--;
401                                                ProcResultBean result= getFirstResult();
402                                                ProcParamBean param = new ProcParamBean();
403                                                
404                                                        param.setType(pm.dataType);
405                                                param.setDirection(ProcParamBean.DIRECTION_OUT);
406                                                if(result!=null)param.setVariable(result.getName());
407                                                returnValue=param;
408                                                
409                                        }       
410                                                else if(ct==DatabaseMetaData.procedureColumnOut || ct==DatabaseMetaData.procedureColumnInOut) {
411                                                        // review of the code: seems to add an addional column in this case
412                                                        if(pm.dataType==CFTypes.CURSOR) {
413                                                        ProcResultBean result= getFirstResult();
414                                                        ProcParamBean param = new ProcParamBean();
415                                                                
416                                                                param.setType(pm.dataType);
417                                                        param.setDirection(ProcParamBean.DIRECTION_OUT);
418                                                        if(result!=null)param.setVariable(result.getName());
419                                                                
420                                                                if(params.size()<index)
421                                                                        throw new DatabaseException("you have only defined ["+params.size()+"] procparam tags, but the procedure/function called is expecting more", null, null, dc);
422                                                                else if(params.size()==index)
423                                                                        params.add(param);
424                                                                else
425                                                        params.add(index, param);
426                                                }
427                                                else {                                                          
428                                                        ProcParamBean param= params.get(index);
429                                                                if(param!=null && pm.dataType!=Types.OTHER && pm.dataType!=param.getType()){
430                                                                        param.setType(pm.dataType);
431                                                        }
432                                                }
433                                        }       
434                                                else if(ct==DatabaseMetaData.procedureColumnIn) {       
435                                                ProcParamBean param=get(params,index);
436                                                        if(param!=null && pm.dataType!=Types.OTHER && pm.dataType!=param.getType()){
437                                                                param.setType(pm.dataType);
438                                                        }
439                                                }
440                                        }       
441                                }
442                                
443                                
444                                contractTo(params,index+1);
445                                
446                                //if(res!=null)print.out(new QueryImpl(res,"columns").toString());
447                        } 
448                        catch (SQLException e) {
449                            throw new DatabaseException(e,dc);
450                        }
451                }
452                
453                // return code
454                if(returncode) {
455                        returnValue=STATUS_CODE;
456                }
457        }
458
459        private static ProcParamBean get(List<ProcParamBean> params, int index) {
460                try{
461                        return params.get(index);
462                }
463                catch(Throwable t){
464                        ExceptionUtil.rethrowIfNecessary(t);
465                        return null;
466                }
467        }
468
469
470
471
472        private void contractTo(List<ProcParamBean> params, int paramCount) {
473                if(params.size()>paramCount){
474                        for(int i=params.size()-1;i>=paramCount;i--){
475                                params.remove(i);
476                        }
477                }
478        }
479
480
481
482
483        private ProcMetaCollection createProcMetaCollection(ResultSet res) throws SQLException {
484                /*
485                try {
486                        print.out(new QueryImpl(res,"qry"));
487                } catch (PageException e) {}
488                */
489                ArrayList<ProcMeta> list=new ArrayList<ProcMeta>();
490                try {
491                while(res.next()) {
492                        list.add(new ProcMeta(res.getInt(COLUMN_TYPE),getDataType(res)));
493                }
494                }
495                finally {
496                        IOUtil.closeEL(res);
497                }
498                return new ProcMetaCollection(list);
499        }
500
501
502
503
504        private int getDataType(ResultSet res) throws SQLException {
505                int dataType=res.getInt(DATA_TYPE);     
506                if(dataType==Types.OTHER) {
507                        String  strDataType= res.getString(TYPE_NAME);
508                        if("REF CURSOR".equalsIgnoreCase(strDataType))dataType=CFTypes.CURSOR;
509                        if("CLOB".equalsIgnoreCase(strDataType))dataType=Types.CLOB;
510                        if("BLOB".equalsIgnoreCase(strDataType))dataType=Types.BLOB;
511                }
512                return dataType;
513        }
514
515
516
517
518        private ProcResultBean getFirstResult() {
519                Iterator<Key> it = results.keyIterator();
520                if(!it.hasNext()) return null;
521                        
522                return (ProcResultBean) results.removeEL(it.next());
523        }
524
525        @Override
526        public int doEndTag() throws PageException  {
527                long startNS=System.nanoTime();
528                
529                Object ds=datasource;
530                if(StringUtil.isEmpty(datasource)){
531                        ds=((ApplicationContextPro)pageContext.getApplicationContext()).getDefDataSource();
532                        if(StringUtil.isEmpty(ds))
533                                throw new ApplicationException(
534                                                "attribute [datasource] is required, when no default datasource is defined",
535                                                "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\";)");
536                }
537                
538                
539                
540            Struct res=new StructImpl();
541                DataSourceManager manager = pageContext.getDataSourceManager();
542                DatasourceConnection dc = ds instanceof DataSource?
543                                manager.getConnection(pageContext,(DataSource)ds,username,password):
544                                manager.getConnection(pageContext,Caster.toString(ds),username,password);
545                
546                // create returnValue 
547                returnValue(dc);
548                
549                // create SQL 
550                StringBuilder sql=createSQL();
551                
552
553                // add returnValue to params
554                if(returnValue!=null){
555                        params.add(0,returnValue);
556                }
557                
558                SQLImpl _sql=new SQLImpl(sql.toString());
559                CallableStatement callStat=null;
560                try {
561                    callStat = dc.getConnection().prepareCall(sql.toString());
562                    if(blockfactor>0)callStat.setFetchSize(blockfactor);
563                    if(timeout>0)DataSourceUtil.setQueryTimeoutSilent(callStat,timeout);
564                    
565        // set IN register OUT
566                    Iterator<ProcParamBean> it = params.iterator();
567                        ProcParamBean param;
568                        int index=1;
569                    while(it.hasNext()) {
570                        param= it.next();
571                        param.setIndex(index);
572                        _sql.addItems(new SQLItemImpl(param.getValue()));
573                        if(param.getDirection()!=ProcParamBean.DIRECTION_OUT) {
574                                SQLCaster.setValue(pageContext.getTimeZone(),callStat, index, param);
575                        }
576                        if(param.getDirection()!=ProcParamBean.DIRECTION_IN) {
577                                registerOutParameter(callStat,param);
578                        }
579                        index++;
580                        }
581                    
582        // cache
583                    boolean isFromCache=false;
584                    boolean hasCached=cachedWithin!=null || cachedafter!=null;
585                    Object cacheValue=null;
586                    String dsn = ds instanceof DataSource?((DataSource)ds).getName():Caster.toString(ds);
587                        if(clearCache) {
588                                hasCached=false;
589                                String id = CacheHandlerFactory.createId(_sql,dsn,username,password);
590                                CacheHandler ch = ConfigWebUtil.getCacheHandlerFactories(pageContext.getConfig()).query.getInstance(pageContext.getConfig(), CacheHandlerFactory.TYPE_TIMESPAN);
591                                ch.remove(pageContext, id);
592                        }
593                        else if(hasCached) {
594                                String id = CacheHandlerFactory.createId(_sql,dsn,username,password);
595                                CacheHandler ch = ConfigWebUtil.getCacheHandlerFactories(pageContext.getConfig()).query.getInstance(pageContext.getConfig(), CacheHandlerFactory.TYPE_TIMESPAN);
596                                
597                                CacheItem ci = ch.get(pageContext, id);
598                                if(ci!=null)cacheValue=((StoredProcCacheItem)ci).getStruct();
599                        }
600                        int count=0;
601                        long start=System.currentTimeMillis();
602                        if(cacheValue==null){
603                                // execute
604                                boolean isResult=callStat.execute();
605                                
606                            Struct cache=hasCached?new StructImpl():null;
607        
608                            // resultsets
609                            ProcResultBean result;
610                            
611                            index=1;
612                                do {
613                                if(isResult){
614                                        ResultSet rs=callStat.getResultSet();
615                                        if(rs!=null) {
616                                                        try{
617                                                                result=(ProcResultBean) results.get(index++,null);
618                                                                if(result!=null) {
619                                                                        lucee.runtime.type.Query q = new QueryImpl(rs,result.getMaxrows(),result.getName(),pageContext.getTimeZone());  
620                                                                        count+=q.getRecordcount();
621                                                                        setVariable(result.getName(), q);
622                                                                        if(hasCached)cache.set(KeyImpl.getInstance(result.getName()), q);
623                                                                }
624                                                        }
625                                                        finally{
626                                                                IOUtil.closeEL(rs);
627                                                        }
628                                                }
629                                }
630                            }
631                            while((isResult=callStat.getMoreResults()) || (callStat.getUpdateCount() != -1));
632
633                            // params
634                            it = params.iterator();
635                            while(it.hasNext()) {
636                                param= it.next();
637                                if(param.getDirection()!=ProcParamBean.DIRECTION_IN){
638                                        Object value=null;
639                                        if(!StringUtil.isEmpty(param.getVariable())){
640                                                try{
641                                                        value=SQLCaster.toCFType(callStat.getObject(param.getIndex()));
642                                                }
643                                                catch(Throwable t){
644                                                        ExceptionUtil.rethrowIfNecessary(t);
645                                                }
646                                                value=emptyIfNull(value);
647                                                
648                                                if(param==STATUS_CODE) res.set(STATUSCODE, value);
649                                                else setVariable(param.getVariable(), value);
650                                                if(hasCached)cache.set(KeyImpl.getInstance(param.getVariable()), value);
651                                        }
652                                }
653                                }
654                            if(hasCached){
655                                cache.set(COUNT, Caster.toDouble(count));
656                                String id = CacheHandlerFactory.createId(_sql,dsn,username,password);
657                                        CacheHandler ch = ConfigWebUtil.getCacheHandlerFactories(pageContext.getConfig()).query.getInstance(pageContext.getConfig(), CacheHandlerFactory.TYPE_TIMESPAN);
658                                        ch.set(pageContext, id, cachedWithin, new StoredProcCacheItem(cache,procedure, System.currentTimeMillis()-start));
659                            }
660                            
661                        }
662                        else if(cacheValue instanceof Struct) {
663                                Struct sctCache = (Struct) cacheValue;
664                                count=Caster.toIntValue(sctCache.removeEL(COUNT),0);
665                                
666                                Iterator<Entry<Key, Object>> cit = sctCache.entryIterator();
667                                Entry<Key, Object> ce;
668                                while(cit.hasNext()){
669                                        ce = cit.next();
670                                        if(STATUS_CODE.getVariable().equals(ce.getKey().getString()))
671                                                res.set(KEY_SC, ce.getValue());
672                                        else setVariable(ce.getKey().getString(), ce.getValue());
673                                }
674                                isFromCache=true;
675                        }
676                    // result
677                    long exe;
678                    
679                    setVariable(this.result, res);
680                    res.set(KeyConstants._executionTime,Caster.toDouble(exe=(System.nanoTime()-startNS)));
681                    res.set(KeyConstants._cached,Caster.toBoolean(isFromCache));
682                    
683                    if(pageContext.getConfig().debug() && debug) {
684                        boolean logdb=((ConfigImpl)pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE);
685                                if(logdb)
686                                        pageContext.getDebugger().addQuery(null,dsn,procedure,_sql,count,pageContext.getCurrentPageSource(),(int)exe);
687                        }
688                    
689                        // log
690                        Log log = ((ConfigWebImpl)pageContext.getConfig()).getLog("datasource", true);
691                        if(log.getLogLevel()>=Log.LEVEL_INFO) {
692                                log.info("storedproc tag", "executed ["+sql.toString().trim()+"] in "+DecimalFormat.call(pageContext, exe/1000000D)+" ms");
693                        }
694                    
695                }
696                catch (SQLException e) {
697                        // log
698                        LogUtil.log(((ConfigWebImpl)pageContext.getConfig()).getLog("datasource", true)
699                                        , Log.LEVEL_ERROR, "storedproc tag", e);
700                                                
701                        throw new DatabaseException(e,new SQLImpl(sql.toString()),dc);
702                }
703                catch (PageException pe) {
704                        // log
705                        LogUtil.log(((ConfigWebImpl)pageContext.getConfig()).getLog("datasource", true)
706                                        , Log.LEVEL_ERROR, "storedproc tag", pe);               
707                        throw pe;
708                }
709                finally {
710                    if(callStat!=null){
711                            try {
712                                        callStat.close();
713                                } catch (SQLException e) {}
714                    }
715                    manager.releaseConnection(pageContext,dc);
716                }
717                
718                
719                
720                
721                return EVAL_PAGE;
722        }
723
724        private void setVariable(String name, Object value) throws PageException {
725                pageContext.setVariable(name, value);
726        }
727
728
729
730
731        private StringBuilder createSQL() {
732                StringBuilder sql=new StringBuilder();
733                if(returnValue!=null)sql.append("{? = call ");
734                else sql.append("{ call ");
735                sql.append(procedure);
736                sql.append('(');
737                int incount=params.size();
738                
739                for(int i=0;i<incount;i++) {
740                        if(i==0)sql.append('?');
741                        else sql.append(",?");
742                }
743                sql.append(") }");
744                return sql;
745                
746        }
747
748
749
750
751        private Object emptyIfNull(Object object) {
752                if(object==null)return "";
753                return object;
754        }
755
756        private void registerOutParameter(CallableStatement proc, ProcParamBean param) throws SQLException {
757                if(param.getScale()==-1)proc.registerOutParameter(param.getIndex(),param.getType());
758                else proc.registerOutParameter(param.getIndex(),param.getType(),param.getScale());
759        }
760
761        /**
762         * @param b
763         */
764        public void hasBody(boolean b) {
765                
766        }
767
768        /**
769         * @param timeout the timeout to set
770         */
771        public void setTimeout(double timeout) {
772                this.timeout = (int) timeout;
773        }
774}