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}