Section D.2. Data Types

D.2. Data Types

Each column in a table has a particular data type associated with it. SQL-92 defines a fairly broad set of data types . Different RDBMS packages provide extensions to these basic types, for multimedia data, Java objects, and so on. Oracle's NUMBER data type, for instance, allows database designers to specify the exact precision of integer and floating-point data types. The basic SQL-92 types are listed in Table D-1. The names in parentheses are SQL-92-specified alternates for the main type names.

Table D-1. SQL-92 data types

SQL data type	Description
INTEGER (INT)	A signed integer value. The number of bits represented is implementation-dependent.
SMALLINT	A smaller signed integer value, used when storage or memory is at a premium. Generally 8 bits but implementation-dependent.
NUMERIC	A signed fixed-precision decimal. When creating a NUMERIC, you must specify the total length of the number (including the decimal point) and the number of decimal places. NUMERIC(8, 4) allows three digits, a decimal point, and four more digits.
DECIMAL	Defined the same way as a NUMERIC but may store additional precision (more decimal places).
REAL	A single-precision floating-point value. Range and accuracy are implementation-dependent.
DOUBLE PRECISION (DOUBLE)	A double-precision floating-point value. Range and accuracy are implementation-dependent, but are equal to or better than a REAL.
BIT	A fixed number (one or more) of bits. A length specifier (BIT(n)) is optional. The default size is 1 bit.
BIT VARYING	Storage for up to n bits (BIT VARYING (n)). Many databases have alternate implementations of this data type, such as Oracle's VARBINARY.
DATE	A date value (day, month, and year).
TIME	A time value. Precision is implementation-dependent.
TIMESTAMP	A date and time.
CHARACTER (CHAR)	A fixed-length character string. Specified as CHAR(n). Unused characters are padded with blanks. The default size is 1.
CHARACTER VARYING (VARCHAR)	A variable length string, up to size n. Specified as VARCHAR(n).
INTERVAL	A date or time interval. Not directly supported by JDBC.

Note that the data types given here aren't the same as those in Table 8-1 in Chapter 8. When mapping between SQL types and Java types, a JDBC driver matches the physical data type (either SQL-92 or database-specific) and the closest Java type. For example, calling the getType( ) method of ResultSet on a BIT VARYING field generally returns a Types.LONGVARBINARY value. This translation allows most JDBC programs to switch between different databases without losing functionality. Also, many databases implement additional types: the FLOAT type is so common that many people think it is required by the specification.