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