Migration Tips

Migrating other database to Derby and vice versa.

Overview of column types

HSQL

Numerical

INTEGER|INT

as Java type

int | java.lang.Integer

DOUBLE [PRECISION] | FLOAT

as Java type

double | java.lang.Double

DECIMAL

No limit

java.math.BigDecimal

NUMERIC

No limit

java.math.BigDecimal

BOOLEAN | BIT

as Java type

boolean | java.lang.Boolean

TINYINT

as Java type

byte | java.lang.Byte

SMALLINT

as Java type

short | java.lang.Short

BIGINT

as Java type

long | java.lang.Long

REAL

as Java type

double | java.lang.Double[1]

Strings

VARCHAR

as Integer.MAXVALUE

java.lang.String

VARCHAR_IGNORECASE

as Integer.MAXVALUE

java.lang.String

CHAR

CHARACTER as Integer.MAXVALUE

java.lang.String

LONGVARCHAR

as Integer.MAXVALUE

java.lang.String

Date and Time

DATE

as Java type

java.sql.Date

TIME

as Java type

java.sql.Time

TIMESTAMP | DATETIME

as Java type

java.sql.Timestamp

Binary data

BINARY

as Integer.MAXVALUE

byte[]

VARBINARY

as Integer.MAXVALUE

byte[]

LONGVARBINARY

as Integer.MAXVALUE

byte[]

OTHER | OBJECT

as Integer.MAXVALUE

java.lang.Object

Derby

Numerical

Column type

Bytes

Range

Java Type

SMALLINT

2

-32768 java.lang.Short.MIN_VALUE -
32767 java.lang.Short.MAX_VALUE

java.lang.Short

INTEGER

4

-2147483648 java.lang.Integer.MIN_VALUE -
2147483647 java.lang.Integer.MAX_VALUE

java.lang.Integer

BIGINT

8

-9223372036854775808 java.lang.Long.MIN_VALUE -
9223372036854775807 java.lang.Long.MAX_VALUE

java.lang.Long

REAL

4

Smallest REAL value: -3.402E+38
Largest REAL value: 3.402E+38
Smallest positive REAL value: 1.175E-37
Largest negative REAL value: -1.175E-37

java.lang.Float

DOUBLE / DOUBLE PRECISION

8

FLOAT (an alias for DOUBLE PRECISION or REAL )

DECIMAL (storage based on precision)

java.math.BigDecimal

NUMERIC (an alias for DECIMAL )

java.math.BigDecimal

Strings

Column type

Max. size

Java Type

CHAR

2147483647 java.lang.Integer.MAX_VALUE

java.lang.String

VARCHAR

32672

java.lang.String

LONG VARCHAR

32700

java.lang.String

CLOB

2147483647 java.lang.Integer.MAX_VALUE

java.sql.Clob

Date and Time

Column type

Java Type

DATE

java.sql.Date

TIME

java.sql.Time

TIMESTAMP

java.sql.Timestamp

Binary data

Column type

Max. size

Java Type

BLOB

2147483647 java.lang.Integer.MAX_VALUE

java.sql.Blob

CHAR FOR BIT DATA

254

java.lang.String

VARCHAR FOR BIT DATA

32672

java.lang.String

LONG VARCHAR FOR BIT DATA

32700

java.lang.String

MySQL

Numerical

Column type

Bytes

From

To

TINYINT

1

-128

127

SMALLINT

2

-32768

32767

MEDIUMINT

3

-8388608

8388607

INT

4

-2147483648

2147483647

BIGINT

8

-9223372036854775808

9223372036854775807

Strings

Type

Max.size

Bytes

CHAR

2^8-1

255

VARCHAR

2^8-1

255

TINYTEXT

2^8-1

255

TEXT

2^16-1 (64K-1)

65535

MEDIUMTEXT

2^24-1 (16M-1)

16777215

Date and Time

Column type

Zero value

DATETIME

'0000-00-00 00:00:00'

DATE

'0000-00-00'

TIMESTAMP

00000000000000 (length depends on display size)

TIME

'00:00:00'

YEAR

0000

Column type

Display format

TIMESTAMP(14)

YYYYMMDDHHMMSS

TIMESTAMP(12)

YYMMDDHHMMSS

TIMESTAMP(10)

YYMMDDHHMM

TIMESTAMP(8)

YYYYMMDD

TIMESTAMP(6)

YYMMDD

TIMESTAMP(4)

YYMM

TIMESTAMP(2)

YY

Binary data

TINYBLOB

2^8-1

255

BLOB

2^16-1 (64K-1)

65535

MEDIUMBLOB

2^24-1 (16M-1)

16777215

LONGBLOB

2^32-1 (4G-1)

4294967295

Migration_Tips (last edited 2009-09-20 22:12:46 by localhost)