Subject Re: [IB-Architect] SQL Scripts
Author Jim Starkey
At 03:30 PM 12/7/01 +0000, Ian Newby wrote:
>Hi Jim,
> I agree that it is not necessarily and easy or even a recommended
>option in many situations, particularly for performing batch inserts.
>However, the biggest use of this I believe would be for database
>metadata manipulation.

I agree. But this begs the question of what sort of metadata
manipulation requires this type of feature, and is it a better
idea to attack the real problem or build a better bandaid.

In theory, there should never be meta-data twiddling in production
code. If there is, something is probably wrong, and the most likely
candidate is the database system.

The most common use is probably activating/deactivating a batch
of triggers (an interesting collective noun). To finesse this
problem in Netfrastructure, I let triggers be defined as members
of trigger classes. Trigger classes, in turn, can be deactivated/
activation on a per-connection basis. Trigger classes are infinitely
more efficient that batched DDL commands.

The lesson here is that behind almost every request for a feature
is a more basic problem. People tend to ask about work arounds,
usually whatever the last system they used offered, rather than
their actual problems. Let me give another example.

Back in the early days of Datatrieve-11 I found myself cornered
(literally) by a SIG of users intent on holding me captive until
I agreed to implement the COBOL "redefines" DDL clause. Needless
to day, the very idea of "redefines" offended my internal concept of
data. Seeing no hope of rescue, I fought back. Why did they want
such of horrible thing? So they could have a six digit numeric
field that could be sorted as three independent two digit fields?
Give me an example, I demanded. Well, suppose we wanted to store
a date...

They really didn't want "redefines". They wanted a date datatype
but had never heard of one. So I invented the data datatype (this
was 1976 when computers still had lights on the front) and they
were (eventually) happy.

So, when somebody asks for a bandaid, ask about the wound.

> As for the batch insert problem, one option for this would be to
>implement a sort of batch insert, ie
> I don't know if this is possible or even desirable, but it may be a
>nice method of batch loading data.

Interest construct. Better than a series of individual "insert"
statements by a long shot. But a better programmatic solution
is to prepare one "insert" and execute it many times.

Jim Starkey