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