> From: Jim Starkey [mailto:jas@...]

Hi,

[DDL in SP]

> Superficially, it seems simple. In practice, it is a potential
> can of worms. Figuring out what is legal
> and what is not and finding ways to catch everything that is not
> is probably doable, but by no means simple.

I have mainly seen DDL used in SP's on 'other' databases mainly to do stuff
that is not possible, but is on IB. For instance, in SQLServer selectable
SP's are not possible (or they weren't when I last looked), so you used to
have to create a table, insert the records, do the select and then delete it
again. I very rarely come across a situation that needs DDL in a SP, or in
a 'client' app for that matter. I would be interested in what other people
might use it for.

I could use it if it was there, two good examples are creating an external
table for logs, audits etc. (e.g. CREATE EXTERNAL TABLE 'MyAudit' ||
TimeStamp || '.dat'), and creating Generators for use as 'invoice numbers'
for new 'customers', rather than keeping the next number on a 'lookup
table', and all the possible 'multi user' problems associated with that.

> Dynamic SQL is also doable

This to me is a must have. I am always doing the following for every field
in a table for audit/logging purposes.

IF OLD.FIELD1 <> NEW.FIELD1 THEN
EXECUTE PROCEDURE SomeProcedure;

IF OLD.FIELD2 <> NEW.FIELD2 THEN
EXECUTE PROCEDURE SomeProcedure;

......etc.


If I could...

FOR
SELECT RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = 'MyTable'
INTO :FIELDNAME
DO
BEGIN
IF (OLD.:FIELDNAME <> NEW.:FIELDNAME) THEN
EXECUTE PROCEDURE SomeProcedure;
END

...I would be a very happy man.

Cheers

Phil Shrimpton
------------------------------
Project JEDI DCOM Team Captain
Project JEDI Library Team
<www.delphi-jedi.org>
Registered Linux User #155621