ïż

reference:array [www.ibpp.org]










[[reference:array]]

http://www.ibpp.org/












 


You are here: ibpp  reference  array


Table of Contents



IBPP::Array


Overview

Working with
arrays

Some
other informational methods

SQL data
types supported

Varia
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.
Overview

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.
Working with
arrays

Allocating
an Array object

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.
Describing the
Array

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.
Writing data
to the Array

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.

Writing the Array reference
to your row/column

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.
Reading
data from an Array

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));

Reading
or Writing a "slice" of the array (and not the complete array)

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.
Some other informational methods

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.
SQL data types
supported

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...
A word on the
bool[]

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).
Varia

IBPP::Database
DatabasePtr() and IBPP::Transaction TransactionPtr()

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




reference\array.txt · Last modified: 2007/01/23 18:03