Generic coding SQL

John RF Staples (6/5/14 7:10PM)
Joshua Fletcher (6/5/14 8:49PM)
John RF Staples (6/6/14 3:07PM)

John RF Staples (6/5/14 7:10 PM)

Hello all
I am interested in the two SQL commands ALTER TABLE (sql_name) and
ADD(column_definition); 4D v14.1.
I am trying to use data to define a table structure via generic code.
My question; how to replace the literal name for a table or field with
a generic variable returning either the table / field name?

I have read the SQL reference manual and tried both wrapping a
variable in <<< >>> or passing a function with FN; both were rejected
by ALTER TABLE.
( Never got as far as ADD ...! )

With anticipatory thanks
John Staples. UK.

Joshua Fletcher (6/5/14 8:49 PM)

Hi John,

Short answer: build the statement dynamically in a text variable and
then execute it with EXECUTE IMMEDIATE:

http://kb.4d.com/assetid=47916
http://kb.4d.com/assetid=76179

Long answer:

A table name or column definition cannot be dynamic values. These are
parts of the structure of the statement, rather than the inputs to the
statement. Ź=A0Inputs can accept any arbitrary value, but the
structure
cannot be dynamic. Ź=A0I.e. a "sql_name" isn't a variable. The grammar
can help you understand this...

The Syntax Rules section of the documentation can be used to determine
the possible values for each part of a SQL statement in 4D.

http://doc.4d.com/4Dv14/help/Title/en/page345.html

I will use your question as an example. Starting from ALTER TABLE:

http://doc.4d.com/4Dv14/help/Command/en/page18448.html

You can see that a "sql_name" is expected after ALTER TABLE. However
"sql_name" does not accept dynamic values; it's not a variable:

http://doc.4d.com/4Dv14/help/Command/en/page18480.html

Whereas an "arithmetic_expression" accepts dynamic values:

http://doc.4d.com/4Dv14/help/Command/en/page18482.html

So you can only use a dynamic value when you see
"arithmetic_expression" supported by the grammar.

4D gets around this with EXECUTE IMMEDIATE.

Kind regards,

Josh

--
Josh Fletcher
Technical Account Manager
4D, Inc

color><param>00000,0000,DDEE/param>
/color>

-----Original Message-----
color><param>00000,0000,DDEE/param>HHello all
I am interested in the two SQL commands ALTER TABLE (sql_name) and
ADD(column_definition); 4D v14.1.
I am trying to use data to define a table structure via generic code.
My question; how to replace the literal name for a table or field with a
generic variable returning either the table / field name?

I have read the SQL reference manual and tried both wrapping a
variable in
<<< >>> or passing a function with FN; both were rejected by ALTER
TABLE.
( Never got as far as ADD ...! )

With anticipatory thanks
John Staples. UK.
/color>

John RF Staples (6/6/14 3:07 PM)

Hi Josh

Many thanks for your knowledgable and well explained answer to my
problem; I shall give it a go.
Cheers, John.

Sent from my iPad

Reply to this message

Summary created 6/6/14 at 5:17PM by Intellex Corporation

Comments welcome at: feedback@intellexcorp.com