001    package railo.runtime.tag;
002    
003    import java.sql.CallableStatement;
004    import java.sql.Connection;
005    import java.sql.DatabaseMetaData;
006    import java.sql.ResultSet;
007    import java.sql.SQLException;
008    import java.sql.Types;
009    import java.util.ArrayList;
010    import java.util.Iterator;
011    import java.util.List;
012    import java.util.Map;
013    
014    import javax.servlet.jsp.JspException;
015    
016    import railo.commons.io.IOUtil;
017    import railo.commons.lang.StringUtil;
018    import railo.commons.sql.SQLUtil;
019    import railo.runtime.db.CFTypes;
020    import railo.runtime.db.DataSourceImpl;
021    import railo.runtime.db.DataSourceManager;
022    import railo.runtime.db.DatasourceConnection;
023    import railo.runtime.db.ProcMeta;
024    import railo.runtime.db.ProcMetaCollection;
025    import railo.runtime.db.SQLCaster;
026    import railo.runtime.db.SQLImpl;
027    import railo.runtime.db.SQLItemImpl;
028    import railo.runtime.exp.ApplicationException;
029    import railo.runtime.exp.DatabaseException;
030    import railo.runtime.exp.PageException;
031    import railo.runtime.ext.tag.BodyTagTryCatchFinallySupport;
032    import railo.runtime.listener.ApplicationContextPro;
033    import railo.runtime.op.Caster;
034    import railo.runtime.tag.util.DeprecatedUtil;
035    import railo.runtime.type.Array;
036    import railo.runtime.type.ArrayImpl;
037    import railo.runtime.type.Collection.Key;
038    import railo.runtime.type.KeyImpl;
039    import railo.runtime.type.QueryImpl;
040    import railo.runtime.type.Struct;
041    import railo.runtime.type.StructImpl;
042    import railo.runtime.type.dt.DateTime;
043    import railo.runtime.type.dt.DateTimeImpl;
044    import railo.runtime.type.dt.TimeSpan;
045    
046    
047    
048    
049    
050    
051    public class StoredProc extends BodyTagTryCatchFinallySupport {
052            //private static final int PROCEDURE_CAT=1;        
053            //private static final int PROCEDURE_SCHEM=2;
054            //private static final int PROCEDURE_NAME=3;
055            //private static final int COLUMN_NAME=4;
056            private static final int COLUMN_TYPE=5;
057            private static final int DATA_TYPE=6;
058            private static final int TYPE_NAME=7;
059            //|PRECISION|LENGTH|SCALE|RADIX|NULLABLE|REMARKS|SEQUENCE|OVERLOAD|DEFAULT_VALUE
060            
061    
062            private static final railo.runtime.type.Collection.Key KEY_SC = KeyImpl.intern("StatusCode");
063            
064            private static final railo.runtime.type.Collection.Key COUNT = KeyImpl.intern("count_afsdsfgdfgdsfsdfsgsdgsgsdgsasegfwef");
065            
066            private static final ProcParamBean STATUS_CODE;
067            private static final railo.runtime.type.Collection.Key STATUSCODE = KeyImpl.intern("StatusCode");
068            
069            static{
070                    STATUS_CODE = new ProcParamBean();
071                    STATUS_CODE.setType(Types.INTEGER);
072                    STATUS_CODE.setDirection(ProcParamBean.DIRECTION_OUT);
073                    STATUS_CODE.setVariable("cfstoredproc.statusCode");
074            }
075            
076            
077            private List<ProcParamBean> params=new ArrayList<ProcParamBean>();
078            private Array results=new ArrayImpl();
079    
080            private String procedure;
081            private String datasource=null;
082            private String username;
083            private String password;
084            private int blockfactor=-1;
085            private int timeout=-1;
086            private boolean debug=true;
087            private boolean returncode;
088            private String result="cfstoredproc";
089            
090            private boolean clearCache;
091            private DateTimeImpl cachedbefore;
092            //private String cachename="";
093            private DateTime cachedafter;
094            private ProcParamBean returnValue=null;
095            //private Map<String,ProcMetaCollection> procedureColumnCache;
096            
097            public void release() {
098                    params.clear();
099                    results.clear();
100                    returnValue=null;
101                    procedure=null;
102                    datasource=null;
103                    username=null;
104                    password=null;
105                    blockfactor=-1;
106                    timeout=-1;
107                    debug=true;
108                    returncode=false;
109                    result="cfstoredproc";
110                    
111    
112                    clearCache=false;
113                    cachedbefore=null;
114                    cachedafter=null;
115                    //cachename="";
116                    
117                    super.release();
118            }
119            
120            
121    
122    
123            /** set the value cachedafter
124            *  This is the age of which the query data can be
125            * @param cachedafter value to set
126            **/
127            public void setCachedafter(DateTime cachedafter)        {
128                    //railo.print.ln("cachedafter:"+cachedafter);
129                    this.cachedafter=cachedafter;
130            }
131    
132            /** set the value cachename
133            *  This is specific to JTags, and allows you to give the cache a specific name
134            * @param cachename value to set
135            **/
136            public void setCachename(String cachename)      {
137                    DeprecatedUtil.tagAttribute(pageContext,"StoredProc", "cachename");
138            }
139    
140            /** set the value cachedwithin
141            *  
142            * @param cachedwithin value to set
143            **/
144            public void setCachedwithin(TimeSpan cachedwithin)      {
145                    if(cachedwithin.getMillis()>0)
146                            this.cachedbefore=new DateTimeImpl(pageContext,System.currentTimeMillis()+cachedwithin.getMillis(),false);
147                    else clearCache=true;
148            }
149    
150            /**
151             * @param blockfactor The blockfactor to set.
152             */
153            public void setBlockfactor(double blockfactor) {
154                    this.blockfactor = (int) blockfactor;
155            }
156            
157            /**
158             * @param blockfactor
159             * @deprecated replaced with setBlockfactor(double)
160             */
161            public void setBlockfactor(int blockfactor) {
162                    DeprecatedUtil.tagAttribute(pageContext,"storedproc","blockfactor");
163                    this.blockfactor = blockfactor;
164            }
165    
166            /**
167             * @param datasource The datasource to set.
168             */
169            public void setDatasource(String datasource) {
170                    this.datasource = datasource;
171            }
172    
173            /**
174             * @param username The username to set.
175             */
176            public void setUsername(String username) {
177                    this.username = username;
178            }
179    
180            /**
181             * @param password The password to set.
182             */
183            public void setPassword(String password) {
184                    this.password = password;
185            }
186    
187            /**
188             * @param debug The debug to set.
189             */
190            public void setDebug(boolean debug) {
191                    this.debug = debug;
192            }
193    
194            /**
195             * @param procedure The procedure to set.
196             */
197            public void setProcedure(String procedure) {
198                    this.procedure = procedure;
199            }
200    
201            /**
202             * @param result The result to set.
203             */
204            public void setResult(String result) {
205                    this.result = result;
206            }
207    
208            /**
209             * @param returncode The returncode to set.
210             */
211            public void setReturncode(boolean returncode) {
212                    this.returncode = returncode;
213            }
214    
215            /**
216             * @param dbvarname the dbvarname to set
217             */
218            public void setDbvarname(String dbvarname) {
219                    DeprecatedUtil.tagAttribute(pageContext,"storedproc","dbvarname");
220            }
221            public void setDbtype(String dbtype) {
222                    DeprecatedUtil.tagAttribute(pageContext,"storedproc","dbtype");
223            }
224    
225            public void addProcParam(ProcParamBean param) {
226                    params.add(param);
227            }
228    
229            public void addProcResult(ProcResultBean result) {
230                    results.setEL(result.getResultset(),result);
231            }
232    
233            /**
234             * @see javax.servlet.jsp.tagext.Tag#doStartTag()
235             */
236            public int doStartTag() throws JspException {
237                    
238                    return EVAL_BODY_INCLUDE;
239            }
240    
241            private void returnValue(DatasourceConnection dc) throws PageException {
242                    Connection conn = dc.getConnection();
243                    
244                    
245                    if(SQLUtil.isOracle(conn)){
246                            String name=this.procedure.toUpperCase();
247                            int index=name.lastIndexOf('.');
248                            
249                            String pack=null,scheme=null;
250                            if(index!=-1){
251                                    pack=name.substring(0,index);
252                                    name=name.substring(index+1);
253                                    
254                                    index=pack.lastIndexOf('.');
255                                    if(index!=-1){
256                                            scheme=pack.substring(index+1);
257                                            pack=pack.substring(0,index);
258                                    }
259                                    
260                                    
261                            }
262                            
263                            try {
264                                    DatabaseMetaData md = conn.getMetaData();
265                                    
266                                    //if(procedureColumnCache==null)procedureColumnCache=new ReferenceMap();
267                                    //ProcMetaCollection coll=procedureColumnCache.get(procedure);
268                                    DataSourceImpl d = ((DataSourceImpl)dc.getDatasource());
269                                    long cacheTimeout = d.getMetaCacheTimeout();
270                                    Map<String, ProcMetaCollection> procedureColumnCache = d.getProcedureColumnCache();
271                                    ProcMetaCollection coll=procedureColumnCache.get(procedure);
272                                    
273                                    if(coll==null || (cacheTimeout>=0 && (coll.created+cacheTimeout)<System.currentTimeMillis())) {
274                                            ResultSet res = md.getProcedureColumns(pack, scheme, name, null);
275                                            coll=createProcMetaCollection(res);
276                                            procedureColumnCache.put(procedure,coll);
277                                    }
278                                    
279                                    index=-1;
280                                    for(int i=0;i<coll.metas.length;i++) { 
281                                            index++;
282                                            
283                                            // Return
284                                            if(coll.metas[i].columnType==DatabaseMetaData.procedureColumnReturn) {
285                                                    index--;
286                                                    ProcResultBean result= getFirstResult();
287                                                    ProcParamBean param = new ProcParamBean();
288                                                    
289                                                    param.setType(coll.metas[i].dataType);
290                                                    param.setDirection(ProcParamBean.DIRECTION_OUT);
291                                                    if(result!=null)param.setVariable(result.getName());
292                                                    returnValue=param;
293                                                    
294                                            }       
295                                            else if(coll.metas[i].columnType==DatabaseMetaData.procedureColumnOut || coll.metas[i].columnType==DatabaseMetaData.procedureColumnInOut) {
296                                                    if(coll.metas[i].dataType==CFTypes.CURSOR){
297                                                            ProcResultBean result= getFirstResult();
298                                                            
299                                                            ProcParamBean param = new ProcParamBean();
300                                                            param.setType(coll.metas[i].dataType);
301                                                            param.setDirection(ProcParamBean.DIRECTION_OUT);
302                                                            if(result!=null)param.setVariable(result.getName());
303                                                            params.add(index, param);
304                                                    }
305                                                    else {                                                          
306                                                            ProcParamBean param=(ProcParamBean) params.get(index);
307                                                            if(coll.metas[i].dataType!=Types.OTHER && coll.metas[i].dataType!=param.getType()){
308                                                                    param.setType(coll.metas[i].dataType);
309                                                            }
310                                                    }
311                                            }       
312                                            else if(coll.metas[i].columnType==DatabaseMetaData.procedureColumnIn) { 
313                                                    ProcParamBean param=get(params,index);
314                                                    if(param!=null && coll.metas[i].dataType!=Types.OTHER && coll.metas[i].dataType!=param.getType()){
315                                                            param.setType(coll.metas[i].dataType);
316                                                    }
317                                            }       
318                                    }
319                                    contractTo(params,index+1);
320                                    
321                                    //if(res!=null)print.out(new QueryImpl(res,"columns").toString());
322                            } 
323                            catch (SQLException e) {
324                                throw new DatabaseException(e,dc);
325                            }
326                            
327                            
328                    }
329                    
330                    // return code
331                    if(returncode) {
332                            returnValue=STATUS_CODE;
333                    }
334            }
335    
336            private static ProcParamBean get(List<ProcParamBean> params, int index) {
337                    try{
338                            return params.get(index);
339                    }
340                    catch(Throwable t){
341                            return null;
342                    }
343                    
344            }
345    
346    
347    
348    
349            private void contractTo(List<ProcParamBean> params, int paramCount) {
350                    if(params.size()>paramCount){
351                            for(int i=params.size()-1;i>=paramCount;i--){
352                                    params.remove(i);
353                            }
354                    }
355            }
356    
357    
358    
359    
360            private ProcMetaCollection createProcMetaCollection(ResultSet res) throws SQLException {
361                    /*
362                    try {
363                            print.out(new QueryImpl(res,"qry"));
364                    } catch (PageException e) {}
365                    */
366                    ArrayList<ProcMeta> list=new ArrayList<ProcMeta>();
367                    while(res.next()) {
368                            list.add(new ProcMeta(res.getInt(COLUMN_TYPE),getDataType(res)));
369                    }
370                    return new ProcMetaCollection(list.toArray(new ProcMeta[list.size()]));
371            }
372    
373    
374    
375    
376            private int getDataType(ResultSet res) throws SQLException {
377                    int dataType=res.getInt(DATA_TYPE);     
378                    if(dataType==Types.OTHER) {
379                            String  strDataType= res.getString(TYPE_NAME);
380                            if("REF CURSOR".equalsIgnoreCase(strDataType))dataType=CFTypes.CURSOR;
381                            if("CLOB".equalsIgnoreCase(strDataType))dataType=Types.CLOB;
382                            if("BLOB".equalsIgnoreCase(strDataType))dataType=Types.BLOB;
383                    }
384                    return dataType;
385            }
386    
387    
388    
389    
390            private ProcResultBean getFirstResult() {
391                    Key[] keys = results.keys();
392                    if(keys.length==0) return null;
393                            
394                    return (ProcResultBean) results.removeEL(keys[0]);
395            }
396    
397            /**
398             * @see railo.runtime.ext.tag.TagSupport#doEndTag()
399             */
400            public int doEndTag() throws JspException {
401                    long start=System.currentTimeMillis();
402                    
403                    if(StringUtil.isEmpty(datasource)){
404                            datasource=((ApplicationContextPro)pageContext.getApplicationContext()).getDefaultDataSource();
405                            if(StringUtil.isEmpty(datasource))
406                                    throw new ApplicationException(
407                                                    "attribute [datasource] is required, when no default datasource is defined",
408                                                    "you can define a default datasource as attribute [defaultdatasource] of the tag cfapplication or as data member of the application.cfc (this.defaultdatasource=\"mydatasource\";)");
409                    }
410                    
411                    
412                    
413                Struct res=new StructImpl();
414                    DataSourceManager manager = pageContext.getDataSourceManager();
415                    DatasourceConnection dc = manager.getConnection(pageContext,datasource,username,password);
416                    
417                    // create returnValue 
418                    returnValue(dc);
419                    
420                    // create SQL 
421                    StringBuffer sql=createSQL();
422                    
423    
424                    // add returnValue to params
425                    if(returnValue!=null){
426                            params.add(0,returnValue);
427                    }
428                    
429                    SQLImpl _sql=new SQLImpl(sql.toString());
430                    CallableStatement callStat=null;
431                    try {
432                        callStat = dc.getConnection().prepareCall(sql.toString());
433                                    //ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 
434                                    //ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 
435                        
436                        if(blockfactor>0)callStat.setFetchSize(blockfactor);
437                        if(timeout>0)callStat.setQueryTimeout(timeout);
438                        
439            // set IN register OUT
440                        Iterator<ProcParamBean> it = params.iterator();
441                            ProcParamBean param;
442                            int index=1;
443                        while(it.hasNext()) {
444                            param= it.next();
445                            param.setIndex(index);
446                            _sql.addItems(new SQLItemImpl(param.getValue()));
447                            if(param.getDirection()!=ProcParamBean.DIRECTION_OUT) {
448                                    SQLCaster.setValue(callStat, index, param);
449                            }
450                            if(param.getDirection()!=ProcParamBean.DIRECTION_IN) {
451                                    registerOutParameter(callStat,param);
452                            }
453                            index++;
454                            }
455                        
456            // cache
457                        boolean isFromCache=false;
458                        boolean hasCached=cachedbefore!=null || cachedafter!=null;
459                        Object cacheValue=null;
460                            if(clearCache) {
461                                    hasCached=false;
462                                    pageContext.getQueryCache().remove(_sql,datasource,username,password);
463                            }
464                            else if(hasCached) {
465                                    cacheValue = pageContext.getQueryCache().get(_sql,datasource,username,password,cachedafter);
466                            }
467                            int count=0;
468                            if(cacheValue==null){
469                                    // execute
470                                    boolean isResult=callStat.execute();
471                                    
472                                Struct cache=hasCached?new StructImpl():null;
473            
474                                // resultsets
475                                ProcResultBean result;
476                                
477                                index=1;
478                                    do {
479                                    if(isResult){
480                                            ResultSet rs=callStat.getResultSet();
481                                            if(rs!=null) {
482                                                            try{
483                                                                    result=(ProcResultBean) results.get(index++,null);
484                                                                    if(result!=null) {
485                                                                            railo.runtime.type.Query q = new QueryImpl(rs,result.getMaxrows(),result.getName());    
486                                                                            count+=q.getRecordcount();
487                                                                            setVariable(result.getName(), q);
488                                                                            if(hasCached)cache.set(KeyImpl.getInstance(result.getName()), q);
489                                                                    }
490                                                            }
491                                                            finally{
492                                                                    IOUtil.closeEL(rs);
493                                                            }
494                                                    }
495                                    }
496                                }
497                                while((isResult=callStat.getMoreResults()) || (callStat.getUpdateCount() != -1));
498    
499                                // params
500                                it = params.iterator();
501                                while(it.hasNext()) {
502                                    param=(ProcParamBean) it.next();
503                                    if(param.getDirection()!=ProcParamBean.DIRECTION_IN){
504                                            Object value=null;
505                                            if(!StringUtil.isEmpty(param.getVariable())){
506                                                    try{
507                                                            value=SQLCaster.toCFType(callStat.getObject(param.getIndex()));
508                                                    }
509                                                    catch(Throwable t){}
510                                                    value=emptyIfNull(value);
511                                                    
512                                                    if(param==STATUS_CODE) res.set(STATUSCODE, value);
513                                                    else setVariable(param.getVariable(), value);
514                                                    if(hasCached)cache.set(KeyImpl.getInstance(param.getVariable()), value);
515                                            }
516                                    }
517                                    }
518                                if(hasCached){
519                                    cache.set(COUNT, Caster.toDouble(count));
520                                    pageContext.getQueryCache().set(_sql,datasource,username,password,cache,cachedbefore);
521                                }
522                                
523                            }
524                            else if(cacheValue instanceof Struct) {
525                                    Struct sctCache = (Struct) cacheValue;
526                                    count=Caster.toIntValue(sctCache.removeEL(COUNT),0);
527                                    
528                                    
529                                    Key[] keys = sctCache.keys();
530                                    for(int i=0;i<keys.length;i++){
531                                            if(STATUS_CODE.getVariable().equals(keys[i].getString()))
532                                                    res.set(KEY_SC, sctCache.get(keys[i]));
533                                            else setVariable(keys[i].getString(), sctCache.get(keys[i]));
534                                    }
535                                    isFromCache=true;
536                            }
537                            
538                        // result
539                        long exe;
540                        
541                        setVariable(this.result, res);
542                        res.set(QueryImpl.EXECUTION_TIME,Caster.toDouble(exe=(System.currentTimeMillis()-start)));
543                        res.set(QueryImpl.CACHED,Caster.toBoolean(isFromCache));
544                        
545                        if(pageContext.getConfig().debug() && debug) {
546                            pageContext.getDebugger().addQueryExecutionTime(datasource,procedure,_sql,count,pageContext.getCurrentPageSource(),(int)exe);
547                            }
548                        
549                        
550                    }
551                    catch (SQLException e) {
552                        throw new DatabaseException(e,new SQLImpl(sql.toString()),dc);
553                    }
554                    finally {
555                        if(callStat!=null){
556                                try {
557                                            callStat.close();
558                                    } catch (SQLException e) {}
559                        }
560                        manager.releaseConnection(pageContext,dc);
561                    }
562                    return EVAL_PAGE;
563            }
564    
565            private void setVariable(String name, Object value) throws PageException {
566                    pageContext.setVariable(name, value);
567            }
568    
569    
570    
571    
572            private StringBuffer createSQL() {
573                    StringBuffer sql=new StringBuffer();
574                    if(returnValue!=null)sql.append("{? = call ");
575                    else sql.append("{ call ");
576                    sql.append(procedure);
577                    sql.append('(');
578                    int incount=params.size();
579                    
580                    for(int i=0;i<incount;i++) {
581                            if(i==0)sql.append('?');
582                            else sql.append(",?");
583                    }
584                    sql.append(") }");
585                    return sql;
586                    
587            }
588    
589    
590    
591    
592            private Object emptyIfNull(Object object) {
593                    if(object==null)return "";
594                    return object;
595            }
596    
597            private void registerOutParameter(CallableStatement proc, ProcParamBean param) throws SQLException {
598                    if(param.getScale()==-1)proc.registerOutParameter(param.getIndex(),param.getType());
599                    else proc.registerOutParameter(param.getIndex(),param.getType(),param.getScale());
600            }
601    
602            /**
603             * @param b
604             */
605            public void hasBody(boolean b) {
606                    
607            }
608    
609            /**
610             * @param timeout the timeout to set
611             */
612            public void setTimeout(double timeout) {
613                    this.timeout = (int) timeout;
614            }
615            
616            
617    }
618    
619