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