ALTER TABLE

[AlastairP]

I have made a form that I wish to use to alter tables in an existing database.
All is working OK except for 1 bit.
I used the example in faq184-4104
The only changes being that I used variables for the table name and field names.

The function:

 ALTER TABLE (lcTable) ADD COLUMN (lcFname)

failed with the variable lcFname. The function is looking for
the correct syntax

here is the entire code:

LOCAL lcFname,lcTable,lcTYpe,lcname

lcTable=thisform.text1.Value
lcname=thisform.text2.Value
lcTYpe=thisform.text3.Value

lcTable=ALLTRIM(lcTable)
lcname=ALLTRIM(lcname)
lcTYpe=ALLTRIM(lcTYpe)

lcFname=lcname + " " + lcTYpe


 SELECT 0
    USE (lcTable)
    AFIELDS(laFields)
    IF ASCAN(laFields,lcname) > 0
        * the field already exists
        * close the table, and go on our merry way
        USE
    ELSE
        * reopen it - this time exclusively so we can modify the structure
        USE (lcTable) EXCL
        
        * The USE command closes the table (if necessary) and then tries to reopen it.  
        * Therefore, the table will not be open if the Exclusive-Use failed, even if
        * it were open prior to it.
        
        IF USED(lcTable)
            * opened exclusively okay
            ALTER TABLE (lcTable) ADD COLUMN (lcFname)

            * now close it
            USE

            MESSAGEBOX("Update Successful!  -  The new CalcMethod field has been " +;
                "added...","MyClientName")

        ELSE
            * Houston, we have a problem.
            MESSAGEBOX("Update Failed!  -  Unable to add the new CalcMethod field " +;
                "because the joblist table cannot be opened exclusively at this time.  "+;
                "Have everyone exit the System and try it again.","MyClientName")

            RETURN

        ENDIF

    ENDIF

Any help would be appreciated

 

[Dan Freeman]

You'll need a &macro for this:

ALTER TABLE (lcTable) ADD COLUMN &lcFname

You'll probably also need to accommodate for width (and decimals) unless you're always adding logical, date, or integer.

 

[Olaf Doschke]

A name expression as (lcFname) only works for names, but you're adding a field type to it, so you don't just have the field name in the variable, and thus can't use a name expression with it.

If you'd keep field name and type in seperate variables, then [ALTER TABLE (lcTable) ADD COLUMN (lcFName) &lcType] might work, but with lcTypes you need macro substitution anyway, if it is something like Char(10) name expression will not work anyway.

So simply use dans solution to use &lcFName

Bye, Olaf.

 

[AlastairP]

Thanks,
Macro substitution is one thing I have managed to avoid learning/implementing so far ... now is the time start.

 

[Olaf Doschke]

You're avoiding something good, there. Though it is surely slower than name expressions or Evaluate(), it is still easier to do and less cost intensive than Execscript().

Especially in your case of an ALTER TABLE, depending on the size of the table, the main time consumption is creating the new column, not the compilation by macro substitution.

And substitution is really simple, it's even a rather stupid mechanism. What does the word substitution mean? It's simply a replacement. &variablename is simply replaced by the variable value, which extends the current line of code, which then is compiled and executed.

Macro substitution can be placed anyhwere, you could even use it to substitute commands partly:

lcMacro = "Box('He"
Message&lcMacro.llo')

Take the line [Message&lcMacro.llo')], replace &lcMacro. with the value of lcMacro, which is Box('He, and you get the line [MessageBox("Hello")], which then is what is compiled and executed, as if that line had been there initially during the build of the exe.

This substitution doesn't make much sense, but it shows how far you can go with it on the one side, and how stupid that mechanism is. It's not more than the Strtran() function does, followed by compilation.

I already gave you a hint on a catch, as this special case needs the otherwise optional dot as end delimiter of macro substitution. In general you'll find VFP developers writing &Variable only. The dot is needed, if the next part of the code otherwise could also be part of the variable name. In this case, if I would have omitted the dot the code before substitution would be [Message&lcMacrollo')] and VFP would search a variable lcMacrollo instead of the intended lcMacro. So, that means you can omit the dot, when the char following the macro substitution name cannot be part of the name, eg when a space, a bracket, a single or double quote or anything like that follows, this makes the macro substitution end delimiter optional.

Another nonoptional case, where beginners often fail is, if a dot follows, eg if you want to dynamically put together an expression like aliasname.fieldname via two variables lcAlias and lcField and write this as &lcAlias.&lcField, that dot is not taken as the separator anymore, but as end delimiter of the first macro substitution.

But those are already the hardest to understand scenarios. If you understand a macro substitution is really jsut what the name suggests, a substitution of the variable name with it's value, then what happens at runtime just follows. You can make many mistakes in having a non string type value or a value not matching the part of the code, which is not substituted, but the mechanism is really exactly that simple as is usage of a constant.

If you #Define CRLF Chr(13)+Chr(10) for example, the usage of CRLF in code like lcLine = "Text"+CRLF also simply substitutes the constant with it's value. The big difference is, this is only doine during compilation and not at runtime. Therefore constants are constant. They don't exist at runtime, what is compiled is not lcLine = "Text"+CRLF, but before compilation CRLF is substituted with Chr(13)+Chr(10) and the compiled code really just is lcLine = "Text"+Chr(13)+Chr(10)

So if you understand constants, you already have undertood macro substitution. Besides being done at runtime, the only other difference is, that the positions of constants in expressions is not as free as for macro substitution, eg you can not comoile the equivalent example to my initial macro substitution example:

#Define MACRO Box('He

MessageMACROllo')

The reason is, there is neither a begin nor end delimiter of constant names. Constants like MACRO need to be put into other code, where you also could use a variable, but not in any other way. So wht you can do with constants is more limited as what macro substitution can do.

Some very common usages of macro subtition is in sql, eg.
SELECT &lcFieldlist FROM Table WHERE &lcWhereclause, which enables you to let the user dynamically choose both the fields of the query result and what to filter.

On the other side, as initially said, macro substitution is slower than name expressions and can become a performance hit, if it's used, where it's unnecessary. Eg &lcAlias..&lcField better is done as Evaluate(lcAlias+"."+lcField) and Select &lcAlias can be avoided by Select (lcAlias), which avoids the cost of compilation at runtime.

Bye, Olaf.