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