Re: Windows Ada database support.

["Warren W. Gay VE3WWG" <ve3wwg@NoSPAM.cogeco.ca>]

Dmitry A. Kazakov wrote:
> On Mon, 06 Dec 2004 09:48:09 -0500, Warren W. Gay VE3WWG wrote:
...
>>Keep in mind that you cannot use a relational database without
>>using SQL (at least no examples come to my mind). So if you
>>don't like SQL, then you have to either forget relational,
>>or build a higher level "protocol" that compiles/translates
>>to SQL underneath.
> 
> Yes, this is exactly what I meant. In short, Ada bindings should have
> Create_Table (...) instead of Execute ("CREATE TABLE ..."). Let SQL be
> perfect, firmly standardized, validated etc. Even so to have a language
> within another language is a bad idea.

This problem is virtually unsolvable with the existing products
that people out there use. Some database products (PostgreSQL
for example) promote the use of Object IDs (basically Row IDs).
Still others don't support the concept at all (MySQL). A grey
area exists in a 3rd area (like Informix), which allows you to
use them at your own peril (Row IDs are unique, if you keep
within certain database design restrictions).

Related to this same issue is how to identify rows that lack
a natural primary key. Some databases support an identity
type for the purpose (Sybase), while others use sequences.
Still others like MySQL use some weird idea of an auto
increment integer field (I am too lazy to look up the
specifics for this, but this is documented in the APQ
manual).

Here's another good one: Some databases allow you to declare
a VARCHAR(256). Others are restricted to VARCHAR(255), and you
must switch to a different type (TEXT I think), if you need
longer fields.

Some support boolean types, and others do not. Some support
arrays, others do not. If they both support arrays, they
are guaranteed to work with different rules and syntax.

There seems to be virtually no agreement on how blobs are
handled and managed, between the different products.

The list of incompatibilities and differences are many
more. For example, there are differences in the way the
client libraries work (ability to fetch one row at a time,
randomly or not (PostgreSQL), must fetch them all into
client memory for random access, or use one-at-a-time
sequential access (MySQL), etc.)

The challenges for a "unified Ada access" layer are so
numerous, that I consider it unachievable. I took a stab
at providing a "portable" binding in APQ, but had to
make various compromises along the way (these are documented
in the manual).

I support the concept, but unless dramatic changes in
product lines emerge, you cannot arrive at the "I
don't want to know what is beneath" solution.  APQ
takes a stab at this problem, and does reduce or
eliminate many of the differences. Truly portable
code will still need to test which database product
is being used, and adjust the SQL to suit in some
cases. There is just no way to avoid it.

-- 
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg