Java > Open Source Codes > org > hsqldb > Column


1 /* Copyright (c) 1995-2000, The Hypersonic SQL Group.
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the Hypersonic SQL Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL THE HYPERSONIC SQL GROUP,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  *
30  * This software consists of voluntary contributions made by many individuals
31  * on behalf of the Hypersonic SQL Group.
32  *
33  *
34  * For work added by the HSQL Development Group:
35  *
36  * Copyright (c) 2001-2005, The HSQL Development Group
37  * All rights reserved.
38  *
39  * Redistribution and use in source and binary forms, with or without
40  * modification, are permitted provided that the following conditions are met:
41  *
42  * Redistributions of source code must retain the above copyright notice, this
43  * list of conditions and the following disclaimer.
44  *
45  * Redistributions in binary form must reproduce the above copyright notice,
46  * this list of conditions and the following disclaimer in the documentation
47  * and/or other materials provided with the distribution.
48  *
49  * Neither the name of the HSQL Development Group nor the names of its
50  * contributors may be used to endorse or promote products derived from this
51  * software without specific prior written permission.
52  *
53  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
54  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
55  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
56  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
57  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
58  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
59  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
60  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
61  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
62  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
63  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
64  */
65
66
67 package org.hsqldb;
68
69 import java.io.IOException ;
70 import java.io.Serializable ;
71 import java.math.BigDecimal ;
72 import java.math.BigInteger ;
73 import java.sql.Date ;
74 import java.sql.Time ;
75 import java.sql.Timestamp ;
76
77 import org.hsqldb.HsqlNameManager.HsqlName;
78 import org.hsqldb.lib.StringConverter;
79 import org.hsqldb.store.ValuePool;
80 import org.hsqldb.types.Binary;
81 import org.hsqldb.types.JavaObject;
82 import org.hsqldb.lib.java.JavaSystem;
83
84 // fredt@users 20020130 - patch 491987 by jimbag@users
85 // fredt@users 20020320 - doc 1.7.0 - update
86 // fredt@users 20020401 - patch 442993 by fredt - arithmetic expressions
87 // to allow mixed type arithmetic expressions beginning with a narrower type
88 // changes applied to several lines of code and not marked separately
89 // consists of changes to arithmatic functions to allow promotion of
90 // java.lang.Number values and new functions to choose type for promotion
91 // fredt@users 20020401 - patch 455757 by galena@users (Michiel de Roo)
92 // interpretation of TINYINT as Byte instead of Short
93 // fredt@users 20020130 - patch 491987 by jimbag@users
94 // support for sql standard char and varchar. size is maintained as
95 // defined in the DDL and trimming and padding takes place accordingly
96 // modified by fredt - trimming and padding are turned off by default but
97 // can be applied accross the database by defining sql.enforce_size=true in
98 // database.properties file
99 // fredt@users 20020215 - patch 1.7.0 by fredt - quoted identifiers
100 // applied to different parts to support the sql standard for
101 // naming of columns and tables (use of quoted identifiers as names)
102 // fredt@users 20020328 - patch 1.7.0 by fredt - change REAL to Double
103 // fredt@users 20020402 - patch 1.7.0 by fredt - type conversions
104 // frequently used type conversions are done without creating temporary
105 // Strings to reduce execution time and garbage collection
106 // fredt@users 20021013 - patch 1.7.1 by fredt - type conversions
107 // scripting of Double.Nan and infinity values
108 // fredt@users 20030715 - patch 1.7.2 - type narrowing for numeric values
109 // fredt@users - patch 1.8.0 - enforcement of precision and scale
110
111 /**
112  * Implementation of SQL table columns as defined in DDL statements with
113  * static methods to process their values.<p>
114  *
115  * Enhanced type checking and conversion by fredt@users
116  *
117  * @author Thomas Mueller (Hypersonic SQL Group)
118  * @author fredt@users
119  * @version 1.8.0
120  * @since Hypersonic SQL
121  */
122 public class Column {
123
124 // --------------------------------------------------
125 // DDL name, size, scale, null, identity and default values
126 // most variables are final but not declared so because of a bug in
127 // JDK 1.1.8 compiler
128 public HsqlName columnName;
129     private int colType;
130     private int colSize;
131     private int colScale;
132     private boolean isNullable;
133     private boolean isIdentity;
134     private boolean isPrimaryKey;
135     private Expression defaultExpression;
136     long identityStart;
137     long identityIncrement;
138     static final BigInteger MAX_LONG = BigInteger.valueOf(Long.MAX_VALUE);
139     static final BigInteger MIN_LONG = BigInteger.valueOf(Long.MIN_VALUE);
140     static final BigInteger MAX_INT = BigInteger.valueOf(Integer.MAX_VALUE);
141     static final BigInteger MIN_INT = BigInteger.valueOf(Integer.MIN_VALUE);
142     static final BigDecimal BIG_DECIMAL_0 = new BigDecimal (0.0);
143     static final BigDecimal BIG_DECIMAL_1 = new BigDecimal (1.0);
144
145     /**
146      * Creates a column defined in DDL statement.
147      *
148      * @param name
149      * @param nullable
150      * @param type
151      * @param size
152      * @param scale
153      * @param identity
154      * @param startvalue
155      * @param increment
156      * @param primarykey
157      * @param defstring
158      */
159     Column(HsqlName name, boolean nullable, int type, int size, int scale,
160             boolean primarykey,
161             Expression defexpression) throws HsqlException {
162
163         columnName = name;
164         isNullable = nullable;
165         colType = type;
166         colSize = size;
167         colScale = scale;
168         isPrimaryKey = primarykey;
169         defaultExpression = defexpression;
170     }
171
172     void setIdentity(boolean identity, long startvalue,
173                      long increment) throws HsqlException {
174
175         isIdentity = identity;
176         identityStart = startvalue;
177         identityIncrement = increment;
178
179         if (isIdentity) {
180             if (colType == Types.INTEGER) {
181                 if (identityStart > Integer.MAX_VALUE
182                         || identityIncrement > Integer.MAX_VALUE) {
183                     throw Trace.error(Trace.NUMERIC_VALUE_OUT_OF_RANGE,
184                                       columnName.statementName);
185                 }
186             }
187         }
188     }
189
190     private Column() {}
191
192     /**
193      * Used for primary key changes.
194      */
195     Column duplicate(boolean withIdentity) throws HsqlException {
196
197         Column newCol = new Column();
198
199         newCol.columnName = columnName;
200         newCol.isNullable = isNullable;
201         newCol.colType = colType;
202         newCol.colSize = colSize;
203         newCol.colScale = colScale;
204         newCol.defaultExpression = defaultExpression;
205
206         if (withIdentity) {
207             newCol.setIdentity(isIdentity, identityStart, identityIncrement);
208         }
209
210         return newCol;
211     }
212
213     void setType(Column other) {
214
215         isNullable = other.isNullable;
216         colType = other.colType;
217         colSize = other.colSize;
218         colScale = other.colScale;
219     }
220
221     /**
222      * Is this the identity column in the table.
223      *
224      * @return boolean
225      */
226     boolean isIdentity() {
227         return isIdentity;
228     }
229
230     /**
231      * Is column nullable.
232      *
233      * @return boolean
234      */
235     boolean isNullable() {
236         return isNullable;
237     }
238
239     /**
240      * Set nullable.
241      *
242      */
243     void setNullable(boolean value) {
244         isNullable = value;
245     }
246
247     /**
248      * Is this single column primary key of the table.
249      *
250      * @return boolean
251      */
252     public boolean isPrimaryKey() {
253         return isPrimaryKey;
254     }
255
256     /**
257      * Set primary key.
258      *
259      */
260     void setPrimaryKey(boolean value) {
261         isPrimaryKey = value;
262     }
263
264     /**
265      * Returns default value in the session context.
266      */
267     Object getDefaultValue(Session session) throws HsqlException {
268
269         return defaultExpression == null ? null
270                                          : defaultExpression.getValue(session,
271                                          colType);
272     }
273
274     /**
275      * Returns DDL for default value.
276      */
277     String getDefaultDDL() {
278
279         String ddl = null;
280
281         try {
282             ddl = defaultExpression == null ? null
283                                             : defaultExpression.getDDL();
284         } catch (HsqlException e) {}
285
286         return ddl;
287     }
288
289     /**
290      * Returns default expression for the column.
291      */
292     Expression getDefaultExpression() {
293         return defaultExpression;
294     }
295
296     void setDefaultExpression(Expression expr) {
297         defaultExpression = expr;
298     }
299
300     /**
301      * Type of the column.
302      *
303      * @return java.sql.Types int value for the column
304      */
305     int getType() {
306         return colType;
307     }
308
309     int getDIType() {
310         return colType == Types.VARCHAR_IGNORECASE ? Types.VARCHAR
311                                                    : colType;
312     }
313
314     int getDITypeSub() {
315
316         if (colType == Types.VARCHAR_IGNORECASE) {
317             return Types.TYPE_SUB_IGNORECASE;
318         }
319
320         return Types.TYPE_SUB_DEFAULT;
321     }
322
323     /**
324      * Size of the column in DDL (0 if not defined).
325      *
326      * @return DDL size of column
327      */
328     int getSize() {
329         return colSize;
330     }
331
332     /**
333      * Scale of the column in DDL (0 if not defined).
334      *
335      * @return DDL scale of column
336      */
337     int getScale() {
338         return colScale;
339     }
340
341     /**
342      * Add two object of a given type
343      *
344      * @param a
345      * @param b
346      * @param type
347      * @return result
348      * @throws HsqlException
349      */
350     static Object add(Object a, Object b, int type) throws HsqlException {
351
352         if (a == null || b == null) {
353             return null;
354         }
355
356         switch (type) {
357
358             case Types.NULL :
359                 return null;
360
361             case Types.REAL :
362             case Types.FLOAT :
363             case Types.DOUBLE : {
364                 double ad = ((Number ) a).doubleValue();
365                 double bd = ((Number ) b).doubleValue();
366
367                 return ValuePool.getDouble(Double.doubleToLongBits(ad + bd));
368
369 // return new Double(ad + bd);
370 }
371             case Types.VARCHAR :
372             case Types.CHAR :
373             case Types.LONGVARCHAR :
374             case Types.VARCHAR_IGNORECASE :
375                 return (String ) a + (String ) b;
376
377             case Types.NUMERIC :
378             case Types.DECIMAL : {
379                 BigDecimal abd = (BigDecimal ) a;
380                 BigDecimal bbd = (BigDecimal ) b;
381
382                 return abd.add(bbd);
383             }
384             case Types.TINYINT :
385             case Types.SMALLINT :
386             case Types.INTEGER : {
387                 int ai = ((Number ) a).intValue();
388                 int bi = ((Number ) b).intValue();
389
390                 return ValuePool.getInt(ai + bi);
391             }
392             case Types.BIGINT : {
393                 long longa = ((Number ) a).longValue();
394                 long longb = ((Number ) b).longValue();
395
396                 return ValuePool.getLong(longa + longb);
397             }
398             default :
399                 throw Trace.error(Trace.FUNCTION_NOT_SUPPORTED,
400                                   Types.getTypeString(type));
401         }
402     }
403
404     /**
405      * Concat two objects by turning them into strings first.
406      *
407      * @param a
408      * @param b
409      * @return result
410      * @throws HsqlException
411      */
412     static Object concat(Object a, Object b) throws HsqlException {
413
414         if (a == null || b == null) {
415             return null;
416         }
417
418         return a.toString() + b.toString();
419     }
420
421     /**
422      * Negate a numeric object.
423      *
424      * @param a
425      * @param type
426      * @return result
427      * @throws HsqlException
428      */
429     static Object negate(Object a, int type) throws HsqlException {
430
431         if (a == null) {
432             return null;
433         }
434
435         switch (type) {
436
437             case Types.NULL :
438                 return null;
439
440             case Types.REAL :
441             case Types.FLOAT :
442             case Types.DOUBLE : {
443                 double ad = -((Number ) a).doubleValue();
444
445                 return ValuePool.getDouble(Double.doubleToLongBits(ad));
446             }
447             case Types.NUMERIC :
448             case Types.DECIMAL :
449                 return ((BigDecimal ) a).negate();
450
451             case Types.TINYINT :
452             case Types.SMALLINT :
453             case Types.INTEGER :
454                 return ValuePool.getInt(-((Number ) a).intValue());
455
456             case Types.BIGINT :
457                 return ValuePool.getLong(-((Number ) a).longValue());
458
459             default :
460                 throw Trace.error(Trace.FUNCTION_NOT_SUPPORTED,
461                                   Types.getTypeString(type));
462         }
463     }
464
465     /**
466      * Multiply two numeric objects.
467      *
468      * @param a
469      * @param b
470      * @param type
471      * @return result
472      * @throws HsqlException
473      */
474     static Object multiply(Object a, Object b,
475                            int type) throws HsqlException {
476
477         if (a == null || b == null) {
478             return null;
479         }
480
481 // fredt@users - type conversion - may need to apply to other arithmetic operations too
482 if (!(a instanceof Number && b instanceof Number )) {
483             a = Column.convertObject(a, type);
484             b = Column.convertObject(b, type);
485         }
486
487         switch (type) {
488
489             case Types.NULL :
490                 return null;
491
492             case Types.REAL :
493             case Types.FLOAT :
494             case Types.DOUBLE : {
495                 double ad = ((Number ) a).doubleValue();
496                 double bd = ((Number ) b).doubleValue();
497
498                 return ValuePool.getDouble(Double.doubleToLongBits(ad * bd));
499             }
500             case Types.NUMERIC :
501             case Types.DECIMAL : {
502                 BigDecimal abd = (BigDecimal ) a;
503                 BigDecimal bbd = (BigDecimal ) b;
504
505                 return abd.multiply(bbd);
506             }
507             case Types.TINYINT :
508             case Types.SMALLINT :
509             case Types.INTEGER : {
510                 int ai = ((Number ) a).intValue();
511                 int bi = ((Number ) b).intValue();
512
513                 return ValuePool.getInt(ai * bi);
514             }
515             case Types.BIGINT : {
516                 long longa = ((Number ) a).longValue();
517                 long longb = ((Number ) b).longValue();
518
519                 return ValuePool.getLong(longa * longb);
520             }
521             default :
522                 throw Trace.error(Trace.FUNCTION_NOT_SUPPORTED,
523                                   Types.getTypeString(type));
524         }
525     }
526
527     /**
528      * Divide numeric object a by object b.
529      *
530      * @param a
531      * @param b
532      * @param type
533      * @return result
534      * @throws HsqlException
535      */
536     static Object divide(Object a, Object b, int type) throws HsqlException {
537
538         if (a == null || b == null) {
539             return null;
540         }
541
542         switch (type) {
543
544             case Types.NULL :
545                 return null;
546
547             case Types.REAL :
548             case Types.FLOAT :
549             case Types.DOUBLE : {
550                 double ad = ((Number ) a).doubleValue();
551                 double bd = ((Number ) b).doubleValue();
552
553                 return ValuePool.getDouble(Double.doubleToLongBits(ad / bd));
554             }
555             case Types.NUMERIC :
556             case Types.DECIMAL : {
557                 BigDecimal abd = (BigDecimal ) a;
558                 BigDecimal bbd = (BigDecimal ) b;
559                 int scale = abd.scale() > bbd.scale() ? abd.scale()
560                                                              : bbd.scale();
561
562                 return (bbd.signum() == 0) ? null
563                                            : abd.divide(bbd, scale,
564                                            BigDecimal.ROUND_HALF_DOWN);
565             }
566             case Types.TINYINT :
567             case Types.SMALLINT :
568             case Types.INTEGER : {
569                 int ai = ((Number ) a).intValue();
570                 int bi = ((Number ) b).intValue();
571
572                 if (bi == 0) {
573                     throw Trace.error(Trace.DIVISION_BY_ZERO);
574                 }
575
576                 return ValuePool.getInt(ai / bi);
577             }
578             case Types.BIGINT : {
579                 long longa = ((Number ) a).longValue();
580                 long longb = ((Number ) b).longValue();
581
582                 return (longb == 0) ? null
583                                     : ValuePool.getLong(longa / longb);
584             }
585             default :
586                 throw Trace.error(Trace.FUNCTION_NOT_SUPPORTED,
587                                   Types.getTypeString(type));
588         }
589     }
590
591     /**
592      * Subtract numeric object b from object a.
593      *
594      * @param a
595      * @param b
596      * @param type
597      * @return result
598      * @throws HsqlException
599      */
600     static Object subtract(Object a, Object b,
601                            int type) throws HsqlException {
602
603         if (a == null || b == null) {
604             return null;
605         }
606
607         switch (type) {
608
609             case Types.NULL :
610                 return null;
611
612             case Types.REAL :
613             case Types.FLOAT :
614             case Types.DOUBLE : {
615                 double ad = ((Number ) a).doubleValue();
616                 double bd = ((Number ) b).doubleValue();
617
618                 return ValuePool.getDouble(Double.doubleToLongBits(ad - bd));
619             }
620             case Types.NUMERIC :
621             case Types.DECIMAL : {
622                 BigDecimal abd = (BigDecimal ) a;
623                 BigDecimal bbd = (BigDecimal ) b;
624
625                 return abd.subtract(bbd);
626             }
627             case Types.TINYINT :
628             case Types.SMALLINT :
629             case Types.INTEGER : {
630                 int ai = ((Number ) a).intValue();
631                 int bi = ((Number ) b).intValue();
632
633                 return ValuePool.getInt(ai - bi);
634             }
635             case Types.BIGINT : {
636                 long longa = ((Number ) a).longValue();
637                 long longb = ((Number ) b).longValue();
638
639                 return ValuePool.getLong(longa - longb);
640             }
641             default :
642                 throw Trace.error(Trace.FUNCTION_NOT_SUPPORTED,
643                                   Types.getTypeString(type));
644         }
645     }
646
647 // boucherb@users 2003-09-25
648 // TODO: Maybe use //#ifdef tag or reflective static method attribute
649 // instantiation to take advantage of String.compareToIgnoreCase when
650 // available (JDK 1.2 and greater) during ANT build. That or perhaps
651 // consider using either local character-wise comparison or first converting
652 // to lower case and then to upper case. Sun states that the JDK 1.2 introduced
653 // String.compareToIngnorCase() comparison involves calling
654 // Character.toLowerCase(Character.toUpperCase()) on compared characters,
655 // to correctly handle some caveats concering using only the one operation or
656 // the other outside the ascii character range.
657 // fredt@users 20020130 - patch 418022 by deforest@users
658 // use of rtrim() to mimic SQL92 behaviour
659
660     /**
661      * Compare a with b and return int value as result.
662      *
663      * @param a instance of Java wrapper, depending on type, but always same for a & b (can be null)
664      * @param b instance of Java wrapper, depending on type, but always same for a & b (can be null)
665      * @param type one of the java.sql.Types
666      * @return result 1 if a>b, 0 if a=b, -1 if b>a
667      * @throws HsqlException
668      */
669     static int compare(Collation collation, Object a, Object b, int type) {
670
671         int i = 0;
672
673         if (a == b) {
674             return 0;
675         }
676
677         // Current null handling here: null==null and smaller any value
678 // Note, standard SQL null handling is handled by Expression.test() calling testNull() instead of this!
679 // Attention, this is also used for grouping ('null' is one group)
680 if (a == null) {
681             return -1;
682         }
683
684         if (b == null) {
685             return 1;
686         }
687
688         switch (type) {
689
690             case Types.NULL :
691                 return 0;
692
693             case Types.VARCHAR :
694             case Types.LONGVARCHAR :
695                 return collation.compare((String ) a, (String ) b);
696
697             case Types.CHAR :
698                 return collation.compare(Library.rtrim((String ) a),
699                                          Library.rtrim((String ) b));
700
701             case Types.VARCHAR_IGNORECASE :
702                 return collation.compareIgnoreCase(((String ) a),
703                                                    ((String ) b));
704
705             case Types.TINYINT :
706             case Types.SMALLINT :
707             case Types.INTEGER : {
708                 int ai = ((Number ) a).intValue();
709                 int bi = ((Number ) b).intValue();
710
711                 return (ai > bi) ? 1
712                                  : (bi > ai ? -1
713                                             : 0);
714             }
715             case Types.BIGINT : {
716                 long longa = ((Number ) a).longValue();
717                 long longb = ((Number ) b).longValue();
718
719                 return (longa > longb) ? 1
720                                        : (longb > longa ? -1
721                                                         : 0);
722             }
723             case Types.REAL :
724             case Types.FLOAT :
725             case Types.DOUBLE : {
726                 double ad = ((Number ) a).doubleValue();
727                 double bd = ((Number ) b).doubleValue();
728
729                 return (ad > bd) ? 1
730                                  : (bd > ad ? -1
731                                             : 0);
732             }
733             case Types.NUMERIC :
734             case Types.DECIMAL :
735                 i = ((BigDecimal ) a).compareTo((BigDecimal ) b);
736                 break;
737
738             case Types.DATE :
739                 return HsqlDateTime.compare((Date ) a, (Date ) b);
740
741             case Types.TIME :
742                 return HsqlDateTime.compare((Time ) a, (Time ) b);
743
744             case Types.TIMESTAMP :
745                 return HsqlDateTime.compare((Timestamp ) a, (Timestamp ) b);
746
747             case Types.BOOLEAN : {
748                 boolean boola = ((Boolean ) a).booleanValue();
749                 boolean boolb = ((Boolean ) b).booleanValue();
750
751                 return (boola == boolb) ? 0
752                                         : (boolb ? -1
753                                                  : 1);
754             }
755             case Types.BINARY :
756             case Types.VARBINARY :
757             case Types.LONGVARBINARY :
758                 if (a instanceof Binary && b instanceof Binary) {
759                     i = compareTo(((Binary) a).getBytes(),
760                                   ((Binary) b).getBytes());
761                 }
762                 break;
763
764             case Types.OTHER :
765                 return 0;
766         }
767
768         return (i == 0) ? 0
769                         : (i < 0 ? -1
770                                  : 1);
771     }
772
773     /**
774      * Convert an object into a Java object that represents its SQL type.<p>
775      * All internal type conversion operations start with
776      * this method. If a direct conversion doesn't take place, the object
777      * is converted into a string first and an attempt is made to convert
778      * the string into the target type.<br>
779      *
780      * One objective of this mehod is to ensure the Object can be converted
781      * to the given SQL type. For example, a very large BIGINT
782      * value cannot be narrowed down to an INTEGER or SMALLINT.<br>
783      *
784      * Type conversion performed by this method has gradually evolved in 1.7.2
785      * to allow narrowing of numeric types in all cases according to the SQL
786      * standard.<br>
787      *
788      * Another new objective is to normalize DATETIME values.<br>
789      *
790      * Objects set via JDBC PreparedStatement use this method to convert
791      * the data to the Java type that is required for custom serialization
792      * by the engine. <br>
793      *
794      * @param o
795      * @param type
796      * @return result
797      * @throws HsqlException
798      */
799     public static Object convertObject(Object o,
800                                        int type) throws HsqlException {
801
802         try {
803             if (o == null) {
804                 return null;
805             }
806
807             switch (type) {
808
809                 case Types.NULL :
810                     return null;
811
812                 case Types.TINYINT :
813                     if (o instanceof java.lang.String ) {
814                         o = Library.trim((String ) o, " ", true, true);
815
816                         int val = Integer.parseInt((String ) o);
817
818                         o = ValuePool.getInt(val);
819                     }
820
821                     if (o instanceof java.lang.Integer ) {
822                         int temp = ((Number ) o).intValue();
823
824                         if (Byte.MAX_VALUE < temp || temp < Byte.MIN_VALUE) {
825                             throw Trace.error(
826                                 Trace.NUMERIC_VALUE_OUT_OF_RANGE);
827                         }
828
829                         return o;
830                     }
831
832                     if (o instanceof java.lang.Long ) {
833   &n