Reference | Help | Introduction | Slide Show | Class Hierarchy | InterClient
PREV | NEXT | FRAMES | NO FRAMES |  |

### InterClient Array Support

InterClient supports array data type as specified by JDBC 2.0 specification. JDBC 2.0 array support is based on SQL3 standard and does not support multidimensional arrays or array of arrays. InterBase, on the other hand, does support multidimensional arrays up to 16 dimensions. Because of these differences InterClient also provides an extension methods to allow a user to work with multidimensional arrays.

#### Base Data Types

InterBase supports arrays of the following data types:

- SMALLINT
- INTEGER
- LONG
- FLOAT
- DOUBLE PRECISION
- DECIMAL
- NUMERIC
- CHAR
- VARCHAR
- DATE

Arrays and Blobs can not be elements of the array.

To write (update or insert) into a column defined as an array of one of the above data types, user should first create and populate a java array, then call PreparedStatement.setObject() method with this array as a parameter. The java array can be an array of objects or an array of primitive data types. Table 1 shows java arrays that could be used to write to an InterBase array of specific data type.

Table 1

Array of | S M A L L
I
N
T
I
N
T
E
G
E
R
F
L
O
A
T
D
O
U
B
L
E
D
E
C
I
M
A
L
N
U
M
E
R
I
C
C
H
A
R
V
A
R
C
H
A
R
D
A
T
E
Byte byte | x | x | x | x | x | x | x | x |
Short short | X | x | x | x | x | x | x | x |
Integer int | x | X | x | x | x | x | x | x |
Long long | x | x | x | x | x | x | x | x |
Float float | x | x | X | x | x | x | x | x |
Double double | x | x | x | X | x | x | x | x |
BigDecimal | x | x | x | x | X | X | x | x |
Boolean boolean | x | x | x | x | x | x | x | x |
String | x | x | x | x | x | x | X | X | x
Date |  |  |  |  |  |  | x | x | X
Timestamp |  |  |  |  |  |  | x | x | X

If, for example, you want to write to a column defined as array of FLOAT, java array of Byte, byte, Short, short, Integer, int, Long, long, Float, float, Double, double, BigDecimal, Boolean, boolean or String could be used. The prefered way though is to use an array of Float or float. Special care should be taken if an array with a data type other than Float/float is used. If you decide to use an array of double (8 bytes) in this case, each element must contain a value that can be stored in float (4 bytes). It is ok though if a user chooses to use an array of Byte, byte, Short, short, Integer, int, Long, long, Boolean, boolean.

It is possible to have an array of objects of different classes. If you want to write to a column defined as an array of DATE, for example, you can define an array of Objects and assign objects of different classes (Date or Timestamp) to the elements of this array.

`Object[] arrayOfDates = new Object[4];`
`arrayOfDates[0] = new Date(93, 2, 5);`
`arrayOfDates[2] = new Date(94, 6, 21);`
`arrayOfDates[1] = new Timestamp(94, 4, 10, 12, 50, 0, 0);`
`arrayOfDates[3] = new Timestamp(96, 2, 20, 14, 50, 0, 0);`
`preparedStatement.setObject (parameterIndex, arrayOfDates);`

If you leave an element of an array of objects unassigned (null), exception is thrown.

When reading an array from InterBase, it is returned to user as an array of objects or primitive data types corresponding to big X in table 1. If big X refers to both array of objects and array of primitive data types, the latter is returned. For example, if a column is defined as array of CHAR, Array.getArray() returns an array of Strings, if a column is defined as array of INTEGER, Array.getArray() returns an array of int.

#### Array Dimensions

InterBase supports multidimensional arrays up to 16 dimensions. Size of each dimension is defined by specifying lower and upper bounds when creating an array column. The bound can be any positive or negative number that can be stored in 2 bytes. Because JDBC 2.0 does not support multidimensional arrays, several extension methods have been added:

ParameterMetaData class
`int[][] getArrayDimensions (int parameterIndex) throws SQLException;`
ResultSetMetaData class
`int[][] getArrayDimensions (int columnIndex) throws SQLException;`
Array class
`Object getArray(int[][] sliceDescriptor) throws SQLException;`

The first two methods return information about number of array dimensions and each dimension bounds. This information is returned in 2-dimensional array. Elements [0][0] and [0][1] give you lower and upper bounds of the first dimension, elements [1][0] and [1][1] contain lower and upper bounds of the second dimension, and so on. The first index cannot be greater than 15 because there are maximum of 16 dimensions in InterBase's arrays.
If, for example, array column is defined in a database as INTEGER[-5:10][3:5], then getArrayDimensions method returns 2 dimensional array with for elements as element[0][0] = -5, [0][1] = 10, [1][0] = 3 and [1][1] = 5.

#### Array Slices

JDBC 2.0 allows you to read a part of array - slice. There is no way to write an array slice. Because JDBC arrays are one dimensional arrays, you just need to specify index of the first element of a slice and a number of element to read.

Array class
`Object getArray(long index, int count) throws SQLException;`

If you want to read a slice of a multidimensional array you should use an extension getArray method mentioned earlier in this section and provide a description on the slice in the form of 2-dimensional array as a parameter to this method. Of course, for each dimension slice's lower bound must not be less than the array's lower bound and slice's upper bound must not be greater than the array's upper bound. If you have an array that was defined as INTERGER[1:3][2:4] with the following values:

IB index | 2 | 3 | 4
1 | 10 | 20 | 30
2 | 40 | 50 | 60
3 | 70 | 80 | 90

then get ArrayDimension returns a two-dimensional array with the following values: element[0][0] = 1, [0][1] = 3, [1][0] = 2, [1][1] = 4. If you want to read a slice defined by lower bound = 2 and upper bound = 3 for both dimensions, you should create new 2D array (or use the existing one if you no longer need it) and assign following values to its elements:

element[0][0] = 2;
element[0][1] = 3;
element[1][0] = 2;
element[1][1] = 3;

Then you call getArray() method and specify this 2D array as input parameter. The result is:

java index | 0 | 1
0 | 40 | 50
1 | 70 | 80

Reference | Help | Introduction | Slide Show | Class Hierarchy | InterClient
PREV | NEXT | FRAMES | NO FRAMES |  |

Send comments or suggestions to interclient@interbase.com