use "macro substitution" in database design

[nhatrangkhanhhoa]

Dear all,

Kindly teach me how to use "&" (macro substitution) in Database Design?

Thanks

 

[Mike Lewis]

Macro substitution is not directly related to database design. It's more of a programming construct.

I suggest you look at the help topic: "& command".

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[nhatrangkhanhhoa]

Dear Mike,
Thanks for your help.
You know, i want to design the databse with some tables and relations in my project. But i want to change the name of the table each month. So i think that i have to use "&" for change the name of that table.
Any misunderstand from me or any idea from you? please teach me.
Thanks so much

 

[Mike Lewis]

I'm not sure why you need to change the name of the table each month. It's not something that's normally done. However, I don't know your system, so I assume you have a good reason.

That said, I think what you are really asking is how to reference a table name when you don't know the name of the table in advance.

The answer is to store the table name in a variable, and use parenthesis when referencing the variable. For example, if the variable lcTable contains the name of the table, you can do this:

USE (lcTable) ALIAS MyTable

(note the parenthesis about lcTable).

Once this command has been executed, you can refer to the table by its alias, MyTable.

In fact, you could also use macro substitution for this, but the method I've described is preferable, partly because it's faster and partly becuase it's less likely to give problems if the table name contains embedded spaces or certain other characters.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

OTher than Mikes case of using/opening a table with varying names, you'd change a table name by RENAME FILE, no need for macro substitution here. With DBFs belonging to a database container (DBC) this is a bit more complicated, as the file name is by default the same as the table name stored in the dbc, but changing the one does not change the other automatically.

As Mike, I don't know the reason for your design of changing names. You may make excerpts of data with names corresponding to a month or year, but even when hitting the 2GB limit there is no technical reason to rename a table, you'd rather need to start a new one, leaving the old one as archive.

If you want to construct a table with a prefix name plus a year and month encoded, you may use "prefix"+DTOS() or "prefix"+Left(dtos(),6) in a variable for the name and then USE (lcTablename) or CREATE TABLE (lcTablename), name expressions.

Bye, Olaf.

 

[nhatrangkhanhhoa]

Thanks Mike and Olaf

 

[SkipVought]

Hi,

i want to change the name of the table each month

As already suggested, chnanging table names is not something that is normally done.

It is often a result of poor database design, where something like a month's worth of data is put in a separate table, resulting in a database with a GROWING NUMBER OF TABLES. This is not a best and accepted database design practice. It is klunky and your database becomes extremely difficult to actually use.

Rather you might have a table that looks similar to one of your "growing number of tables" with an additional column for the [Related Date].

BTW the ampersand operator (&) or in some languages it is (||) is use for string concatenation...

MyHandle = "Skip" & "Vought"

MyHandle now contains the string "SkipVought"

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Stella740pl]

May I suggest that it could be similar to what I have to do every month. It's not like the database is ever-growing.

It's just that once a month I get a new set of data, and I have to thoroughly screen, clean, analyze, and massage it every other way BEFORE it's actually ready to be appended to the database (a set of free tables, to be exact - for a number of reasons). Actually, the principal table is also one per year.

Sets of intermediate monthly tables, each marked with a mmyy in the name, are also stored as a backup for some time, to facilitate easier reprocessing of the data. If a problem of any kind is found at a some later time (and it is not that rare, considering the nature of our data), we can return and do necessary steps to recover/reprocess/estimate, etc. the data from the optimal point for that, and for the month in question only, instead of doing it from scratch. Some of those files will be deleted when the data is finalized, and some will be burned to CDs/DVDs for storage. (I don't remember any case when we had to restore and use that stored to CDs/DVDs data, though.)

 

[dbMark]

There are some cases where macrosubstitution is the only simple solution. For example, many SET commands won't work using (name expressions)...

LOCAL lcFullPath
lcFullPath = SET("FULLPATH")  && save current ON/OFF flag value
SET FULLPATH ON
* {do something}
* SET FULLPATH (lcFullPath)  && would generate an error
SET FULLPATH &lcFullPath  && reset prior setting

But if both ways work, it is always recommended as "best practices" to avoid using the slower macrosubstitution, especially within loops and repeated code.

By the way, I've read that VFP is one of the few programming languages where you can insert and run commands on the fly in compiled code. Not that you want to do that excessively, but it's possible.

 

[Stella740pl]

dbMark,

For example, many SET commands won't work using (name expressions)...

But in your example, lcFullPath is NOT a name expression, it's an ON or OFF keyword, part of the command.
Of course, SET FULLPATH (lcFullPath) would generate an error, and SET FULLPATH &lcFullPath would not.
Not only SET commands, any command would not work if you use a (name expression) to substitute a part of the command and not a name.

In essense, it is that one case where macrosubstitution is actually recommended - to replace parts of the commands.

 

[Stella740pl]

Hi Mike,

I do use macrosubstitutions when I cannot avoid them, but your example is not something that I usually do.

As I said above, I use them to build commands - like to add an optional clause in some cases and not others, or like in dbMark's example, or build a string for SQL-Select statement depending on choices/parameters, then macro to execute it.

 

[Mike Lewis]

dbMark,

For example, many SET commands won't work using (name expressions)...

(Oh, I like these new quotes .... I think.)

But that's not relevant here. The questioner was asking about opening a table when the name isn't know in advance. That's why I pointed him towards name expressions. I didn't say they were always available in all cases.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[dbMark]

Wow, I was only trying to explain the differences (pro & con) between macrosubstitution (very powerful in scope) and use of name expressions (faster). That's all, just adding some background.

As for my code example, I didn't intend for you to think FULLPATH was a name expression, the variable within the parentheses was the name expression.

Also, some SET commands allow name expressions to be used. So Mike's suggestion to try regular expressions first is excellent.

lnVal = 2
SET DECIMALS TO (lnVal)