IBPP::Array

Firebird and Interbase do support the storage of arrays of values in a single column of a single row. This feature is not widely available at the Dynamic SQL interface level, and best require some C-API programming to be used. IBPP allows you to read and write those array columns easily.

Writing array data to an array column involves 4 steps.

• Allocate an Array object.
• Describe it (gets its description from the system tables based on the table and column names).
• Copy your C++ array to the database using this Array object methods.
• Write the array reference to the database, through an INSERT or UPDATE statement.

Similarly, reading data from an array column involves 4 steps.

• Allocate an Array object.
• Describe it (gets its description from the system tables based on the table and column names).
• Read the array reference from the database through a SELECT statement.
• Copy the array data from the database to your C++ array using Array object methods.

This is done through the ArrayFactory static method as in :

IBPP::Array ar = IBPP::ArrayFactory(db, tr);

Where db and tr are respectively a connected Database, and a started Transaction.

This step must be well understood. The Array object needs to know the structure of the array it will have to handle. You get that step done in the following way :

ar->Describe("TABLE", "COLUMN");

Knowing the table and column name, Describe() gets the array description from the internal system tables. This includes the SQL type of the array elements, their size (for CHAR and VARCHAR for instance), the number of dimensions (Firebird & Interbase support 16 dimensions), and the bounds of each dimensions. This step is mandatory before you can interact with the Array object.

To write your own data to the array (copy your C++ array to the database), you call on the WriteFrom() method, such as in :

double values[4] = {12.5, 15.78, 17.0, 20.0};
ar->WriteFrom(adDouble, values, sizeof(values)/sizeof(double));

Assuming that your SQL array is compatible with this type of data, it will work and the required type conversions will take place. In this example the SQL array could have been :

A1 DOUBLE PRECISION [4],	/* or */
A2 DOUBLE PRECISION [2:5],	/* still 4 elements, indices from 2 to 5, or */
A3 NUMERIC(9,2) [4],		/* internally stored as a scaled 32 bits integer */

The third parameter of the method WriteFrom is actually the number of elements of your array. The safest way is to declare it in the above manner, by dividing the sizeof() of your array by the sizeof() of the elements. IBPP will check that the number of elements it will handle (based on each dimension bounds) will correspond. If they mismatch, an exception will be raised as continuing might overwrite your C++ array limits. When WriteFrom() is executed, the engine will allocate a new array storage area in the database, copy in the data and return an Array ID which is now embedded in the Array object.

Now, all you need to do is INSERT or UPDATE a row, inserting or updating your array column with the IBPP::Array object as value. The engine will store the new array ID (referencing the stored data of the previous step). The old array ID (in case of an UPDATE) and the old array data will be reclaimed from the database on its next sweep.

You just run a SELECT statement as usual, fetching the array column to a described IBPP::Array object. Then just use ReadTo as in this example :

double values[4];
ar->ReadTo(adDouble, values, sizeof(values)/sizeof(double));

The Firebird/Interbase engine offers you a nice additional facility. You can store or read an array in slices. You are not forced to read or write the whole array each time at once. Here is how to use this capability through IBPP. When you call Describe(), the Array object is initialized to handle the full array. But you can restrict this to some slice by using the method SetBounds() as in the following code fragment :

SQL column is : A2 NUMERIC(9,2) [4]
...
double values[2];
ar->Describe();
ar->SetBounds(0, 2, 3);
ar->ReadTo(adDouble, values, sizeof(values)/sizeof(double)

What does the above code means ? The SQL column is a one-dimensional array of 4 elements. Those elements are indexed from 1 to 4 at the SQL level. The code wants to extract the 2 middle elements of the array, that is the elements with indexes of 2 to 3 in SQL numbering. The SetBounds method restrict the bounds of the dimension 0 of the array (there is a single dimension, and dimensions are counted from 0). It restricts the bounds to the elements going from index 2 to index 3 in SQL numbering. The ReadTo method will then get only 2 elements, and those will be stored in the elements 0 and 1 of the C++ array. When defining a slice, you can only narrow the bounds, not extend them (that would be an error). In addition, and this is an IBPP requirement, once you have already narrowed the bounds, you can't re-extend them, even to their original bounds. If you want to do that, IBPP forces you to call Describe() again. SetBounds() is designed to only allow you to shrink the slice, never expand it. This simplifies some internal details for performance reasons.

When you have called Describe(), you can get some informational figures about your array. Dimensions() will return the number of dimensions. Bounds() will return the lower and upper bounds of a given dimension (the dimensions are counted from 0, remember). ElementType() will return the SQL type of data stored in the array. ElementSize() will return the logical size of the elements : for a VARCHAR(30) it will for instance return 30. ElementScale will return the scaling factor (that number is nul or negative) when the column uses a scaled storage. These are mostly not usefull, except for some automated tool.

Here is the list of the SQL Data Types that are supported by IBPP : SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION, NUMERIC and DECIMAL, CHAR and VARCHAR, DATE, TIME, TIMESTAMP.

For the corresponding C++ array types, you generally use the most implicit corresponding type as in the following table :

• SMALLINT : bool[ ], short[ ], int[ ], int64_t[ ]
• INTEGER : bool[ ], short[ ], int[ ], int64_t[ ]
• FLOAT : float[ ]
• DOUBLE PRECISION : double[ ]
• NUMERIC/DECIMAL when scale is 0 : bool[ ], short[ ], int[ ], int64_t[ ], double[ ]
• NUMERIC/DECIMAL when scale is not 0 : double[ ]
• CHAR and VARCHAR : bool[ ], char[ ]
• DATE : IBPP::Date
• TIME : IBPP::Time
• TIMESTAMP : IBPP::Timestamp

Of course, some combinations can yield underflow. Storing an int in a SMALLINT is accepted by IBPP (and properly converted) as long as the int value does not overflow the SMALLINT bounds. And so on…

Just as with ordinary columns, IBPP has some implicit type conversions for helping storing or reading bool values to/from various SQL columns types. When the column is of an integer type, IBPP stores a true as a 1 and a false as a 0. When reading, it treats a 0 as false and anything else as true. When the column is textual (CHAR or VARCHAR), IBPP stores 'T' for true and 'F' for false. When reading, IBPP treats't', 'T', 'y', 'Y', '1' as true and anything else as false (only the first character counts in this read determination of the bool value). This allows for multiple easy ways to handle bools in databases using for instance a CHAR(1).

Used to get easy access to the Database and Transaction this Array is linked to.