[DB-SIG] db paramstyle for arrays

[David Rushby]

I haven't been following this discussion, so excuse me if this post
serves no purpose.  I'm just trying to present the perspective of
another RDBMS and its Python binding on arrays.

--- Anthony Tuininga <anthony at computronix.com> wrote:
> On Wed, 2004-04-07 at 03:11, M.-A. Lemburg wrote:
> > The DB API supports doing bulk inserts via .executemany(),
> > but I'm not sure whether the same thing as inserting ARRAYs
> > which seem to be a postgresql special data type from your
> > description.

Arrays and executemany are definitely not the same thing in
Interbase/Firebird.  Arrays are a separate datatype.

> Oracle supports arrays as well as named types -- neither of which are
> covered by the DB API. I've had to break new ground here with
> cx_Oracle but it would be nice to get the opinions of other 
> database module authors.

In Interbase/Firebird, arrays are of fixed size, with a predeclared
number of dimensions (up to 16) and number of elements per dimension.
Irritatingly, individual array elements cannot be set to NULL.  The
native Interbase/Firebird database API treats arrays differently from
all other data types, and there's no SQL literal representation of
arrays, no convenient way to manipulate them in stored procedures or
triggers.  Frankly, arrays in IB/FB feel like a half-supported feature,
a partial-birth abortion.

> Binding arrays is done using standard Python sequences -- nothing
> controversial there, I would guess. I've had to "guess" the data type
> from the type of data in the array which of course means that
> problems occur when there are no elements in the array or when 
> the elements in the array consist only of nulls. That leads to 
> the next step, which is telling cx_Oracle what kind of array you 
> would like to bind.

In IB/FB, whose type systems are not extensible like PostgreSQL's or
even Oracle's, the native API provides a set of type codes against
which a bound array field can be matched.  IB/FB arrays are homogenous,
disallow null elements, and can never have zero length, so the
difficulties mentioned above do not arise for kinterbasdb.  kinterbasdb
is therefore able to transparently convert arrays from (potentially
nested) Python sequences on input; to (potentially nested) Python lists
on output.

Here's the kinterbasdb docs section on arrays:
http://kinterbasdb.sourceforge.net/dist_docs/usage.html#adv_param_conv_database_arrays

__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway 
http://promotions.yahoo.com/design_giveaway/