001/** 002 * 003 * Copyright (c) 2014, the Railo Company Ltd. All rights reserved. 004 * 005 * This library is free software; you can redistribute it and/or 006 * modify it under the terms of the GNU Lesser General Public 007 * License as published by the Free Software Foundation; either 008 * version 2.1 of the License, or (at your option) any later version. 009 * 010 * This library is distributed in the hope that it will be useful, 011 * but WITHOUT ANY WARRANTY; without even the implied warranty of 012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 013 * Lesser General Public License for more details. 014 * 015 * You should have received a copy of the GNU Lesser General Public 016 * License along with this library. If not, see <http://www.gnu.org/licenses/>. 017 * 018 **/ 019package lucee.runtime.db; 020 021import java.math.BigDecimal; 022import java.sql.Array; 023import java.sql.Blob; 024import java.sql.Clob; 025import java.sql.Date; 026import java.sql.PreparedStatement; 027import java.sql.ResultSet; 028import java.sql.SQLException; 029import java.sql.Time; 030import java.sql.Timestamp; 031import java.sql.Types; 032import java.util.TimeZone; 033 034import lucee.commons.date.JREDateTimeUtil; 035import lucee.commons.io.IOUtil; 036import lucee.commons.lang.ExceptionUtil; 037import lucee.commons.lang.StringUtil; 038import lucee.commons.sql.SQLUtil; 039import lucee.runtime.config.NullSupportHelper; 040import lucee.runtime.exp.DatabaseException; 041import lucee.runtime.exp.PageException; 042import lucee.runtime.op.Caster; 043import lucee.runtime.op.date.DateCaster; 044import lucee.runtime.type.QueryImpl; 045import lucee.runtime.type.dt.DateImpl; 046import lucee.runtime.type.dt.DateTime; 047import lucee.runtime.type.dt.TimeImpl; 048import lucee.runtime.type.sql.BlobImpl; 049import lucee.runtime.type.sql.ClobImpl; 050 051/** 052 * SQL Caster 053 */ 054public final class SQLCaster { 055 056 private SQLCaster() {} 057 058 /** 059 060 * sets a Value to a PreparedStatement 061 * @param stat 062 * @param parameterIndex 063 * @param item 064 * @throws SQLException 065 * @throws PageException 066 * @throws DatabaseException 067 */ 068 069 public static Object toSqlType(SQLItem item) throws PageException, DatabaseException { 070 Object value=item.getValue(); 071 072 try { 073 if(item.isNulls() || value==null) { 074 return null; 075 } 076 int type=item.getType(); 077 switch(type) { 078 case Types.BIGINT: 079 return Caster.toLong(value); 080 case Types.BIT: 081 return Caster.toBoolean(value); 082 case Types.BLOB: 083 return BlobImpl.toBlob(value); 084 case Types.CHAR: 085 return Caster.toString(value); 086 case Types.CLOB: 087 case Types.NCLOB: 088 return ClobImpl.toClob(value); 089 case Types.DATE: 090 return new Date(Caster.toDate(value,null).getTime()); 091 case Types.NUMERIC: 092 case Types.DECIMAL: 093 return new BigDecimal(Caster.toDouble(value).toString()); 094 case Types.DOUBLE: 095 return Caster.toDouble(value); 096 case Types.FLOAT: 097 return Caster.toFloat(value); 098 case Types.VARBINARY: 099 case Types.LONGVARBINARY: 100 case Types.BINARY: 101 return Caster.toBinary(value); 102 case Types.REAL: 103 return Caster.toFloat(value); 104 case Types.TINYINT: 105 return Caster.toByte(value); 106 case Types.SMALLINT: 107 return Caster.toShort(value); 108 case Types.INTEGER: 109 return Caster.toInteger(value); 110 case Types.VARCHAR: 111 case Types.LONGVARCHAR: 112 case CFTypes.VARCHAR2: 113 case Types.NVARCHAR: 114 return Caster.toString(value); 115 case Types.TIME: 116 return new Time(Caster.toDate(value,null).getTime()); 117 case Types.TIMESTAMP: 118 return new Timestamp(Caster.toDate(value,null).getTime()); 119 case Types.OTHER: 120 default: 121 if(value instanceof DateTime) return new Date(Caster.toDate(value,null).getTime()); 122 if(value instanceof lucee.runtime.type.Array) return Caster.toList(value); 123 if(value instanceof lucee.runtime.type.Struct) return Caster.toMap(value); 124 125 return value;//toSQLObject(value); TODO alle lucee spezifischen typen sollten in sql typen uebersetzt werden 126 } 127 } 128 catch(PageException pe) { 129 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 130 return null; 131 throw pe; 132 } 133 } 134 135 public static void setValue(TimeZone tz,PreparedStatement stat, int parameterIndex, SQLItem item) throws PageException, SQLException, DatabaseException { 136 Object value=item.getValue(); 137 if(item.isNulls() || value==null) { 138 stat.setNull(parameterIndex,item.getType()); 139 return; 140 } 141 int type=item.getType(); 142 switch(type) { 143 /*case Types.ARRAY: 144 stat.setArray(parameterIndex,toArray(item.getValue())); 145 return;*/ 146 case Types.BIGINT: 147 try { 148 stat.setLong(parameterIndex,Caster.toLongValue(value)); 149 } 150 catch(PageException pe) { 151 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 152 stat.setNull(parameterIndex,item.getType()); 153 else throw pe; 154 } 155 return; 156 case Types.BIT: 157 try { 158 stat.setBoolean(parameterIndex,Caster.toBooleanValue(value)); 159 } 160 catch(PageException pe) { 161 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 162 stat.setNull(parameterIndex,item.getType()); 163 else throw pe; 164 } 165 return; 166 case Types.BLOB: 167 try { 168 stat.setBlob(parameterIndex,SQLUtil.toBlob(stat.getConnection(),value)); 169 } 170 catch(PageException pe) { 171 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 172 stat.setNull(parameterIndex,item.getType()); 173 else throw pe; 174 } 175 return; 176 case Types.CLOB: 177 try { 178 stat.setClob(parameterIndex,SQLUtil.toClob(stat.getConnection(),value)); 179 /*if(value instanceof String) { 180 try{ 181 stat.setString(parameterIndex,Caster.toString(value)); 182 } 183 catch(Throwable t){ 184 ExceptionUtil.rethrowIfNecessary(t); 185 stat.setClob(parameterIndex,SQLUtil.toClob(stat.getConnection(),value)); 186 } 187 188 } 189 else stat.setClob(parameterIndex,SQLUtil.toClob(stat.getConnection(),value));*/ 190 } 191 catch(PageException pe) { 192 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 193 stat.setNull(parameterIndex,item.getType()); 194 else throw pe; 195 } 196 return; 197 case Types.CHAR: 198 String str = Caster.toString(value); 199 //if(str!=null && str.length()==0) str=null; 200 stat.setObject(parameterIndex, str, type); 201 ////stat.setString(parameterIndex,str); 202 return; 203 case Types.DECIMAL: 204 case Types.NUMERIC: 205 try { 206 stat.setDouble(parameterIndex, (Caster.toDoubleValue(value))); 207 } 208 catch(PageException pe) { 209 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 210 stat.setNull(parameterIndex,item.getType()); 211 else throw pe; 212 } 213 return; 214 215 case Types.DOUBLE: 216 case Types.FLOAT: 217 try { 218 if(type==Types.FLOAT)stat.setFloat(parameterIndex, Caster.toFloatValue(value)); 219 else if(type==Types.DOUBLE)stat.setDouble(parameterIndex, Caster.toDoubleValue(value)); 220 else stat.setObject(parameterIndex, Caster.toDouble(value), type); 221 } 222 catch(PageException pe) { 223 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 224 stat.setNull(parameterIndex,item.getType()); 225 else throw pe; 226 } 227 return; 228 case Types.VARBINARY: 229 case Types.LONGVARBINARY: 230 case Types.BINARY: 231 try { 232 stat.setObject(parameterIndex, Caster.toBinary(value), type); 233 ////stat.setBytes(parameterIndex,Caster.toBinary(value)); 234 } 235 catch(PageException pe) { 236 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 237 stat.setNull(parameterIndex,item.getType()); 238 else throw pe; 239 } 240 return; 241 case Types.REAL: 242 try { 243 stat.setObject(parameterIndex, Caster.toFloat(value), type); 244 ////stat.setFloat(parameterIndex,Caster.toFloatValue(value)); 245 } 246 catch(PageException pe) { 247 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 248 stat.setNull(parameterIndex,item.getType()); 249 else throw pe; 250 } 251 return; 252 case Types.TINYINT: 253 try { 254 stat.setObject(parameterIndex, Caster.toByte(value), type); 255 ////stat.setByte(parameterIndex,Caster.toByteValue(value)); 256 } 257 catch(PageException pe) { 258 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 259 stat.setNull(parameterIndex,item.getType()); 260 else throw pe; 261 } 262 return; 263 case Types.SMALLINT: 264 try { 265 stat.setObject(parameterIndex, Caster.toShort(value), type); 266 ////stat.setShort(parameterIndex,Caster.toShortValue(value)); 267 } 268 catch(PageException pe) { 269 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 270 stat.setNull(parameterIndex,item.getType()); 271 else throw pe; 272 } 273 return; 274 case Types.INTEGER: 275 try { 276 stat.setObject(parameterIndex, Caster.toInteger(value), type); 277 ////stat.setInt(parameterIndex,Caster.toIntValue(value)); 278 } 279 catch(PageException pe) { 280 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 281 stat.setNull(parameterIndex,item.getType()); 282 else throw pe; 283 } 284 return; 285 case Types.VARCHAR: 286 case Types.LONGVARCHAR: 287 case CFTypes.VARCHAR2: 288 stat.setObject(parameterIndex, Caster.toString(value), type); 289 ////stat.setString(parameterIndex,Caster.toString(value)); 290 return; 291 case Types.DATE: 292 try { 293 stat.setDate( 294 parameterIndex, 295 new Date(Caster.toDate(value,tz).getTime()), 296 JREDateTimeUtil.getThreadCalendar(tz)); 297 298 //stat.setDate(parameterIndex,new Date((Caster.toDate(value,null).getTime()))); 299 } 300 catch(PageException pe) { 301 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 302 stat.setNull(parameterIndex,item.getType()); 303 else throw pe; 304 } 305 return; 306 case Types.TIME: 307 try { 308 309 //stat.setObject(parameterIndex, new Time((Caster.toDate(value,null).getTime())), type); 310 stat.setTime( 311 parameterIndex, 312 new Time(Caster.toDate(value,tz).getTime()), 313 JREDateTimeUtil.getThreadCalendar(tz)); 314 } 315 catch(PageException pe) { 316 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 317 stat.setNull(parameterIndex,item.getType()); 318 else throw pe; 319 } 320 return; 321 case Types.TIMESTAMP: 322 try { 323 //stat.setObject(parameterIndex, new Timestamp((Caster.toDate(value,null).getTime())), type); 324 //stat.setObject(parameterIndex, value, type); 325 stat.setTimestamp( 326 parameterIndex, 327 new Timestamp(Caster.toDate(value,tz).getTime()), 328 JREDateTimeUtil.getThreadCalendar(tz)); 329 } 330 catch(PageException pe) { 331 if(!NullSupportHelper.full() && value instanceof String && StringUtil.isEmpty((String)value)) 332 stat.setNull(parameterIndex,item.getType()); 333 else throw pe; 334 } 335 336 return; 337 case Types.OTHER: 338 stat.setObject(parameterIndex,value,Types.OTHER); 339 return; 340 /*case CF_SQL_STRUCT: 341 case CF_SQL_REFCURSOR: 342 case CF_SQL_NULL: 343 case CF_SQL_ARRAY: 344 case CF_SQL_DISTINCT: */ 345 default: 346 stat.setObject(parameterIndex, value,type); 347 //throw new DatabaseException(toStringType(item.getType())+" is not a supported Type",null,null); 348 349 } 350 } 351 352 /** 353 * Cast a SQL Item to a String (Display) Value 354 * @param item 355 * @return String Value 356 */ 357 public static String toString(SQLItem item) { 358 try { 359 return _toString(item); 360 } catch (PageException e) { 361 try { 362 return "["+toStringType(item.getType())+"]"; 363 } catch (DatabaseException e1) { 364 return ""; 365 } 366 } 367 } 368 private static String _toString(SQLItem item) throws PageException { 369 int type=item.getType(); 370 371 // string types 372 if(type==Types.VARCHAR ||type==Types.LONGVARCHAR || type==Types.CHAR || type==Types.CLOB || type==Types.NVARCHAR) { 373 return (matchString(item)); 374 } 375 // int types 376 else if(type==Types.BIGINT || type==Types.INTEGER || type==Types.SMALLINT || type==Types.BIGINT || type==Types.TINYINT) { 377 return Caster.toString(Caster.toIntValue(item.getValue())); 378 } 379 // numeric types 380 else if(type==Types.DECIMAL || type==Types.NUMERIC || type==Types.DOUBLE || type==Types.FLOAT) { 381 return (Caster.toString(Caster.toDoubleValue(item.getValue()))); 382 } 383 // time types 384 else if(type==Types.TIME) { 385 return new TimeImpl(DateCaster.toDateAdvanced(item.getValue(),null)).castToString(); 386 } 387 // date types 388 else if(type==Types.DATE) { 389 return new DateImpl(DateCaster.toDateAdvanced(item.getValue(),null)).castToString(); 390 } 391 // time types 392 else if(type==Types.TIMESTAMP) { 393 return DateCaster.toDateAdvanced(item.getValue(),null).castToString(); 394 } 395 // invalid type 396 else { 397 return Caster.toString(item.getValue()); 398 } 399 } 400 401 /*private static String toString(Clob clob) throws SQLException, IOException { 402 Reader in = clob.getCharacterStream(); 403 StringBuilder buf = new StringBuilder(); 404 for(int c=in.read();c != -1;c = in.read()) { 405 buf.append((char)c); 406 } 407 return buf.toString(); 408 }*/ 409 410 411 /** 412 * cast a Value to a correspondance CF Type 413 * @param item 414 * @return cf type 415 * @throws PageException 416 */ 417 418 public static Object toCFTypex(SQLItem item) throws PageException { 419 try { 420 return _toCFTypex(item); 421 } catch (PageException e) { 422 if(item.isNulls())return item.getValue(); 423 throw e; 424 } 425 } 426 427 public static Object toCFTypeEL(SQLItem item) { 428 try { 429 return _toCFTypex(item); 430 } catch (PageException e) { 431 return item.getValue(); 432 } 433 } 434 private static Object _toCFTypex(SQLItem item) throws PageException { 435 436 int type=item.getType(); 437 // char varchar 438 if(type==Types.VARCHAR || type==Types.LONGVARCHAR || type==CFTypes.VARCHAR2 || type==Types.NVARCHAR) { 439 return Caster.toString(item.getValue()); 440 } 441 // char types 442 else if(type==Types.CHAR || type==Types.NCHAR) { 443 return Caster.toString(item.getValue()); 444 } 445 // int types 446 else if(type==Types.BIGINT || type==Types.INTEGER || type==Types.SMALLINT || type==Types.BIGINT || type==Types.TINYINT) { 447 return Caster.toInteger(item.getValue()); 448 } 449 // numeric types 450 else if(type==Types.DOUBLE || type==Types.FLOAT || type==Types.NUMERIC || type==Types.DECIMAL ) { 451 return Caster.toDouble(item.getValue()); 452 } 453 // time types 454 else if(type==Types.TIME) { 455 return new TimeImpl(DateCaster.toDateAdvanced(item.getValue(),null)).castToString(); 456 } 457 // date types 458 else if(type==Types.DATE) { 459 return new DateImpl(DateCaster.toDateAdvanced(item.getValue(),null)).castToString(); 460 } 461 // time types 462 else if(type==Types.TIMESTAMP) { 463 return DateCaster.toDateAdvanced(item.getValue(),null).castToString(); 464 } 465 // invalid type 466 else { 467 return item.getValue(); 468 } 469 } 470 471 public static Object toCFType(Object value, Object defaultValue) { 472 try { 473 if(value instanceof Clob) { 474 return IOUtil.toString(((Clob)value).getCharacterStream()); 475 } 476 else if(value instanceof Blob) { 477 return IOUtil.toBytes(((Blob)value).getBinaryStream()); 478 } 479 else if(value instanceof Array) { 480 return ((java.sql.Array)value).getArray(); 481 } 482 else 483 return value; 484 } 485 catch(Exception e) { 486 return defaultValue; 487 } 488 } 489 490 public static Object toCFType(Object value) throws PageException { 491 try { 492 if(value instanceof Clob) { 493 return IOUtil.toString(((Clob)value).getCharacterStream()); 494 } 495 else if(value instanceof Blob) { 496 return IOUtil.toBytes(((Blob)value).getBinaryStream()); 497 } 498 else if(value instanceof Array) { 499 return ((java.sql.Array)value).getArray(); 500 } 501 else if(value instanceof ResultSet) { 502 return new QueryImpl((ResultSet)value,"query",null); 503 } 504 else 505 return value; 506 } 507 catch(SQLException e) { 508 throw new DatabaseException(e,null); 509 } 510 catch(Throwable t) { 511 throw Caster.toPageException(t); 512 } 513 } 514 515 public static Object toCFType(Object value, int type) throws PageException { 516 // char varchar 517 if(type==Types.VARCHAR || type==Types.LONGVARCHAR || type==CFTypes.VARCHAR2 || type==Types.NVARCHAR) { 518 return Caster.toString(value); 519 } 520 // char types 521 else if(type==Types.CHAR || type==Types.NCHAR) { 522 return Caster.toString(value); 523 } 524 // int types 525 else if(type==Types.BIGINT || type==Types.INTEGER || type==Types.SMALLINT || type==Types.BIGINT || type==Types.TINYINT) { 526 return Caster.toInteger(value); 527 } 528 // numeric types 529 else if(type==Types.DOUBLE || type==Types.FLOAT || type==Types.NUMERIC || type==Types.DECIMAL ) { 530 return Caster.toDouble(value); 531 } 532 // time types 533 else if(type==Types.TIME) { 534 return new TimeImpl(DateCaster.toDateAdvanced(value,null)).castToString(); 535 } 536 // time types 537 else if(type==Types.DATE) { 538 return new DateImpl(DateCaster.toDateAdvanced(value,null)).castToString(); 539 } 540 // time types 541 else if(type==Types.TIMESTAMP) { 542 return DateCaster.toDateAdvanced(value,null).castToString(); 543 } 544 // invalid type 545 else { 546 return value; 547 } 548 } 549 550 551 552 private static String matchString(SQLItem item) throws PageException { 553 String str = StringUtil.replace(Caster.toString(item.getValue()),"'","''",false); 554 return "'"+str+"'"; 555 } 556 557 /** 558 * returns CF SQL Type as String 559 * @param type 560 * @return SQL Type as String 561 * @throws DatabaseException 562 */ 563 public static String toStringType(int type) throws DatabaseException { 564 switch(type) { 565 case Types.ARRAY: return "CF_SQL_ARRAY"; 566 case Types.BIGINT: return "CF_SQL_BIGINT"; 567 case Types.BINARY: return "CF_SQL_BINARY"; 568 case Types.BIT: return "CF_SQL_BIT"; 569 case Types.BOOLEAN: return "CF_SQL_BOOLEAN"; 570 case Types.BLOB: return "CF_SQL_BLOB"; 571 case Types.CHAR: return "CF_SQL_CHAR"; 572 case Types.CLOB: return "CF_SQL_CLOB"; 573 case Types.DATALINK: return "CF_SQL_DATALINK"; 574 case Types.DATE: return "CF_SQL_DATE"; 575 case Types.DISTINCT: return "CF_SQL_DISTINCT"; 576 case Types.NUMERIC: return "CF_SQL_NUMERIC"; 577 case Types.DECIMAL: return "CF_SQL_DECIMAL"; 578 case Types.DOUBLE: return "CF_SQL_DOUBLE"; 579 case Types.REAL: return "CF_SQL_REAL"; 580 case Types.FLOAT: return "CF_SQL_FLOAT"; 581 case Types.TINYINT: return "CF_SQL_TINYINT"; 582 case Types.SMALLINT: return "CF_SQL_SMALLINT"; 583 case Types.STRUCT: return "CF_SQL_STRUCT"; 584 case Types.INTEGER: return "CF_SQL_INTEGER"; 585 case Types.VARCHAR: return "CF_SQL_VARCHAR"; 586 case Types.NVARCHAR: return "CF_SQL_NVARCHAR"; 587 case CFTypes.VARCHAR2: return "CF_SQL_VARCHAR2"; 588 case Types.LONGVARBINARY:return "CF_SQL_LONGVARBINARY"; 589 case Types.VARBINARY: return "CF_SQL_VARBINARY"; 590 case Types.LONGVARCHAR: return "CF_SQL_LONGVARCHAR"; 591 case Types.TIME: return "CF_SQL_TIME"; 592 case Types.TIMESTAMP: return "CF_SQL_TIMESTAMP"; 593 case Types.REF: return "CF_SQL_REF"; 594 case CFTypes.CURSOR: return "CF_SQL_REFCURSOR"; 595 case Types.OTHER: return "CF_SQL_OTHER"; 596 case Types.NULL: return "CF_SQL_NULL"; 597 598 default: throw new DatabaseException("invalid CF SQL Type",null,null,null); 599 } 600 } 601 602 /* * 603 * cast a String SQL Type to int Type 604 * @param strType 605 * @return SQL Type as int 606 * @throws DatabaseException 607 */ 608 /*public static int cfSQLTypeToIntType(String strType) throws DatabaseException { 609 strType=strType.toUpperCase().trim(); 610 611 if(strType.equals("CF_SQL_ARRAY")) return Types.ARRAY; 612 else if(strType.equals("CF_SQL_BIGINT")) return Types.BIGINT; 613 else if(strType.equals("CF_SQL_BINARY")) return Types.BINARY; 614 else if(strType.equals("CF_SQL_BIT")) return Types.BIT; 615 else if(strType.equals("CF_SQL_BLOB")) return Types.BLOB; 616 else if(strType.equals("CF_SQL_BOOLEAN")) return Types.BOOLEAN; 617 else if(strType.equals("CF_SQL_CHAR")) return Types.CHAR; 618 else if(strType.equals("CF_SQL_CLOB")) return Types.CLOB; 619 else if(strType.equals("CF_SQL_DATALINK")) return Types.DATALINK; 620 else if(strType.equals("CF_SQL_DATE")) return Types.DATE; 621 else if(strType.equals("CF_SQL_DISTINCT")) return Types.DISTINCT; 622 else if(strType.equals("CF_SQL_DECIMAL")) return Types.DECIMAL; 623 else if(strType.equals("CF_SQL_DOUBLE")) return Types.DOUBLE; 624 else if(strType.equals("CF_SQL_FLOAT")) return Types.FLOAT; 625 else if(strType.equals("CF_SQL_IDSTAMP")) return CFTypes.IDSTAMP; 626 else if(strType.equals("CF_SQL_INTEGER")) return Types.INTEGER; 627 else if(strType.equals("CF_SQL_INT")) return Types.INTEGER; 628 else if(strType.equals("CF_SQL_LONGVARBINARY"))return Types.LONGVARBINARY; 629 else if(strType.equals("CF_SQL_LONGVARCHAR"))return Types.LONGVARCHAR; 630 else if(strType.equals("CF_SQL_MONEY")) return Types.DOUBLE; 631 else if(strType.equals("CF_SQL_MONEY4")) return Types.DOUBLE; 632 else if(strType.equals("CF_SQL_NUMERIC")) return Types.NUMERIC; 633 else if(strType.equals("CF_SQL_NULL")) return Types.NULL; 634 else if(strType.equals("CF_SQL_REAL")) return Types.REAL; 635 else if(strType.equals("CF_SQL_REF")) return Types.REF; 636 else if(strType.equals("CF_SQL_REFCURSOR")) return CFTypes.CURSOR; 637 else if(strType.equals("CF_SQL_OTHER")) return Types.OTHER; 638 else if(strType.equals("CF_SQL_SMALLINT")) return Types.SMALLINT; 639 else if(strType.equals("CF_SQL_STRUCT")) return Types.STRUCT; 640 else if(strType.equals("CF_SQL_TIME")) return Types.TIME; 641 else if(strType.equals("CF_SQL_TIMESTAMP")) return Types.TIMESTAMP; 642 else if(strType.equals("CF_SQL_TINYINT")) return Types.TINYINT; 643 else if(strType.equals("CF_SQL_VARBINARY")) return Types.VARBINARY; 644 else if(strType.equals("CF_SQL_VARCHAR")) return Types.VARCHAR; 645 else if(strType.equals("CF_SQL_NVARCHAR")) return Types.NVARCHAR; 646 else if(strType.equals("CF_SQL_VARCHAR2")) return CFTypes.VARCHAR2; 647 648 649 else throw new DatabaseException("invalid CF SQL Type ["+strType+"]",null,null,null); 650 }*/ 651 652 653 /** 654 * cast a String SQL Type to int Type 655 * @param strType 656 * @return SQL Type as int 657 * @throws DatabaseException 658 */ 659 public static int toSQLType(String strType) throws DatabaseException { 660 strType=strType.toUpperCase().trim(); 661 if(strType.startsWith("CF_SQL_")) strType=strType.substring(7); 662 if(strType.startsWith("SQL_")) strType=strType.substring(4); 663 664 665 if(strType.length()>2) { 666 char first=strType.charAt(0); 667 if(first=='A') { 668 if(strType.equals("ARRAY")) return Types.ARRAY; 669 } 670 else if(first=='B') { 671 if(strType.equals("BIGINT")) return Types.BIGINT; 672 else if(strType.equals("BINARY")) return Types.BINARY; 673 else if(strType.equals("BIT")) return Types.BIT; 674 else if(strType.equals("BLOB")) return Types.BLOB; 675 else if(strType.equals("BOOLEAN")) return Types.BOOLEAN; 676 else if(strType.equals("BOOL")) return Types.BOOLEAN; 677 } 678 else if(first=='C') { 679 if(strType.equals("CLOB")) return Types.CLOB; 680 else if(strType.equals("CHAR")) return Types.CHAR; 681 else if(strType.equals("CLOB")) return Types.CLOB; 682 else if(strType.equals("CURSOR")) return CFTypes.CURSOR; 683 } 684 else if(first=='D') { 685 if(strType.equals("DATALINK")) return Types.DATALINK; 686 else if(strType.equals("DATE")) return Types.DATE; 687 else if(strType.equals("DISTINCT")) return Types.DISTINCT; 688 else if(strType.equals("DECIMAL")) return Types.DECIMAL; 689 else if(strType.equals("DOUBLE")) return Types.DOUBLE; 690 } 691 else if(first=='F') { 692 if(strType.equals("FLOAT")) return Types.FLOAT; 693 } 694 else if(first=='I') { 695 if(strType.equals("IDSTAMP")) return CFTypes.IDSTAMP; 696 else if(strType.equals("INTEGER")) return Types.INTEGER; 697 else if(strType.equals("INT")) return Types.INTEGER; 698 } 699 else if(first=='L') { 700 //if(strType.equals("LONG"))return Types.INTEGER; 701 if(strType.equals("LONGVARBINARY"))return Types.LONGVARBINARY; 702 else if(strType.equals("LONGVARCHAR"))return Types.LONGVARCHAR; 703 } 704 else if(first=='M') { 705 if(strType.equals("MONEY")) return Types.DOUBLE; 706 else if(strType.equals("MONEY4")) return Types.DOUBLE; 707 } 708 else if(first=='N') { 709 if(strType.equals("NUMERIC")) return Types.NUMERIC; 710 else if(strType.equals("NUMBER")) return Types.NUMERIC; 711 else if(strType.equals("NULL")) return Types.NULL; 712 else if(strType.equals("NCHAR")) return Types.NCHAR; 713 else if(strType.equals("NCLOB")) return Types.NCLOB; 714 else if(strType.equals("NVARCHAR")) return Types.NVARCHAR; 715 716 717 718 } 719 else if(first=='O') { 720 if(strType.equals("OTHER")) return Types.OTHER; 721 else if("OBJECT".equals(strType)) return Types.OTHER; 722 } 723 else if(first=='R') { 724 if(strType.equals("REAL")) return Types.REAL; 725 else if(strType.equals("REF")) return Types.REF; 726 else if(strType.equals("REFCURSOR"))return CFTypes.CURSOR; 727 } 728 else if(first=='S') { 729 if(strType.equals("SMALLINT")) return Types.SMALLINT; 730 else if(strType.equals("STRUCT")) return Types.STRUCT; 731 else if(strType.equals("STRING")) return Types.VARCHAR; 732 else if(strType.equals("SQLXML")) return Types.SQLXML; 733 } 734 else if(first=='T') { 735 if(strType.equals("TIME")) return Types.TIME; 736 else if(strType.equals("TIMESTAMP"))return Types.TIMESTAMP; 737 else if(strType.equals("TINYINT")) return Types.TINYINT; 738 } 739 else if(first=='V') { 740 if(strType.equals("VARBINARY"))return Types.VARBINARY; 741 else if(strType.equals("VARCHAR")) return Types.VARCHAR; 742 else if(strType.equals("VARCHAR2")) return CFTypes.VARCHAR2; 743 } 744 } 745 746 throw new DatabaseException("invalid CF SQL Type ["+strType+"]",null,null,null); 747 } 748 749 public static short toCFType(int sqlType, short defaultValue) { 750 switch(sqlType) { 751 case Types.ARRAY: return lucee.commons.lang.CFTypes.TYPE_ARRAY; 752 case Types.BIGINT: return lucee.commons.lang.CFTypes.TYPE_NUMERIC; 753 case Types.LONGVARBINARY: 754 case Types.VARBINARY: 755 case Types.BLOB: 756 case Types.BINARY: return lucee.commons.lang.CFTypes.TYPE_BINARY; 757 case Types.BOOLEAN: 758 case Types.BIT: return lucee.commons.lang.CFTypes.TYPE_BOOLEAN; 759 case Types.LONGVARCHAR: 760 case Types.NVARCHAR: 761 case CFTypes.VARCHAR2: 762 case Types.VARCHAR: 763 case Types.CLOB: 764 case Types.CHAR: return lucee.commons.lang.CFTypes.TYPE_STRING; 765 766 case Types.TIME: 767 case Types.TIMESTAMP: 768 case Types.DATE: return lucee.commons.lang.CFTypes.TYPE_DATETIME; 769 case Types.INTEGER: 770 case Types.SMALLINT: 771 case Types.TINYINT: 772 case Types.FLOAT: 773 case Types.REAL: 774 case Types.DOUBLE: 775 case Types.DECIMAL: 776 case Types.NUMERIC: return lucee.commons.lang.CFTypes.TYPE_NUMERIC; 777 778 779 default: 780 return defaultValue; 781 } 782 783 } 784}