At 08:58 2000-06-05 -0700, you wrote:

>re: Dynamic SQL from within an SP.
>
>I don't think that is asking for a "bit much" -- Oracle supports this. My
>head has been churning the last
>few weeks about how this could be added within IB.
>
>Along the lines of "defining the problem" -- Louis, can you define some DDL
>operations that
>you've wanted to do in a SP, but haven't been able to?

I will ( longwindedly :-) )explain in the context of what we do.

We have developed a generic 'database application engine' (for want of a
better description). It is based on IB and we use this in all our
applications. What this engine does is being able to change the schema at
run-time. Our applications include the necessary tools for users to define
and customise the application to the Nth degree. This includes creating and
manipulating tables on a 'business relationship' level. Maybe not great
from a vendor point of view, but infinitely flexible without the need for
any programmers ('We work to make ourselves obsolete!'). Our front-end is
also built at run-time, based on the schema.

When the user creates a table, the database application engine then
automatically creates all the SPs to wrap the particular table with ADD,
SET, UPDATE and VIEW SPs. As the user edits the table, the SPs change. We
have also defined a number of other 'datatypes'. The user can define a
column type to be a lookup (similar to MS Access) to a column in another
table. In the database this implemented as a FK, but the VIEW SP returns
the FK value as well as the looked up column value. It essestially
'flattens' the two tables with a join. Tables may contain N lookup column
to a depth of M. We have also created datatypes that automatically maintain
history in a separate table, 'compound' datatypes, etc. It is important to
note that all of these 'extra' datatypes are implemented under the hood.
The user only needs to selects a column type as e.g. lookup into column X
of table T, define the column as a history column, and Bob's Your Uncle...!
All the tables to implement the lookup, history, etc. are created and the
SP updated to present the table as you defined it.

This may sound like a lot of 'unnecessary' work, and believe me, it has
involved a lot of intricate programming. The needs of the customers that we
have been servicing change frequently due to the nature of their business
and we have found it worthwhile to develop this generic backup-end and
front-end.

This brings me to my request. At the moment, all these DDL manipulation is
implemented in a client software module. It would be great to be able to
write SPs to :

CREATE_TABLE(TableName,PKColumnName,PKColumnType);
ADD_COLUMN(TableName,ColumnName,ColumnType);
etc...

This will create an 'API' within the database itself. You could do this in
a middle tier, but I believe it would be better in the DB. From here the
requirement for DDL in SPs

I realise that what we are doing may be very elaborate and that very few
people have the need for this, but even MS SQL Server supports this. I
considered moving to SQL Server, but all the people I talk to still
maintain IB is better. IB is also my 1st choice, having used it for the
last 4 years.

I can but dream....

Thanks for your time...

L.J. van Alphen




L.J. van Alphen
Managing Director
Basix Automation

E-mail: lja@...
Tel: (+27) (0)21-683 5490
Fax: (+27) (0)21-683 5435

P.O. Box 171
Rondebosch
7701
South Africa