Subscribe to this thread
Home - General / All posts - ALTER statement can't reference target component by a stored VALUE
tjhb

8,687 post(s)
#11-May-19 02:44

I wonder if the limitation in point 2 is intended (or necessary).

1. We can alter a component like this.

--SQL9

ALTER TABLE [Table] (ADD PROPERTY 'Test' 'test');

ALTER DRAWING [Drawing] (ADD PROPERTY 'Test' 'test');

ALTER IMAGE [Image] (ADD PROPERTY 'Test' 'test');

2. But not like this.

--SQL9

VALUE @t TABLE = [Table];

VALUE @d TABLE = [Drawing];

VALUE @i TABLE = [Image];

ALTER TABLE @t (ADD PROPERTY 'Test' 'test');

ALTER DRAWING @d (ADD PROPERTY 'Test' 'test');

ALTER IMAGE @i (ADD PROPERTY 'Test' 'test');

In each of those cases, the ALTER... statement gives "Cannot parse query".

3. Well, we can do it like this.

--SQL9

VALUE @name NVARCHAR = 'Table';

UPDATE [mfd_meta]

SET [Value] = 'test'

WHERE [Name] = @name

AND [Property] = 'Test'

;

I understand why we can't CREATE a component using a stored value (of type TABLE): because no such component yet exists. But I don't understand why we can't use a stored value for ALTER.

(9.0.169.0.)

tjhb

8,687 post(s)
#11-May-19 03:31

Further re 3:

That doesn't help with e.g.

--SQL9

ALTER TABLE @t

    (

    DROP INDEX ...

    );

It would be really nice to be able to do these things with stored values.

adamw


8,479 post(s)
#11-May-19 12:45

The problem with ALTER TABLE @v (ADD PROPERTY ...) is that @v can be something like VALUES (...), not corresponding to any physical component. In that case, there is just no MFD_META associated with the table which could store its properties. This indirectly affects fields, indexes and constraints, too, because when they are added, properties are sometimes used to pass additional data.

We agree though that extending ALTER to work on @ values is useful (no need to extend CREATE / DROP), so we might allow it, we have this on the wishlist.

tjhb

8,687 post(s)
#11-May-19 17:39

Ah, thanks, I didn't think of tables created by VALUES ... or tables created by an inline SELECT or TABLE CALL statement or EXECUTE [WITH] or the result of a query function ... and likewise assigned to an @ value. A lot of things I didn't think of.

If ALTER could be made to use @ values (which includes parameters I suppose, not only VALUE assignments) only where the target is an existing component (and throw an error otherwise), well, that would be a clear rule, and nice to have. (And in that case why not for DROP as well as ALTER, by the same rule. Not CREATE, ditto.)

But I doubt it would have high priority. Although writers of built-in transform templates might sometimes like it too. :)

[Added.] Why nice to have? To make code more readable, and to facilitate code reuse.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.