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