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