How to use alias and exclusive/shared 3

[Mandy_crw]

Hi everyone... i have been using this format in opening table and its working with no errors...

SELECT 0
USE dummy SHARED ALIAS dummi

but i have seen in the internet this format...

SELECT 0
USE dummy ALIAS dummi SHARED

I want to ask which format is correct... Thanks in advanced....

 

[Chris Miller]

Both.

In some commands you can use the so called command options in any order.
Use has so many options, if you would need to give them in one specific order only it would become a pain to have it correctly.

If you see one of them faileing, then there is another reason than the order in which they are written.

Just for sake of completeness: Put together this code will fail:

SELECT 0
USE dummy SHARED ALIAS dummi
SELECT 0
USE dummy ALIAS dummi SHARED

Not because the order of how it's written the second time is wrong. It fails just because the dummy.dbf file already is used. You get Error 3 - File is in use. You don't get a syntax error or similar errors like "unrecognized phrase/keyword" or a compilation error.

VFP is not "forgiving" here, it's a simple concept that command clauses (the parts a command can have) usually have no forced order. Exceptions are obvious. COPY FILE a TO b, for example, where a must be source file and b destination file, can't be swapped. You also can't write COPY TO a FILE b because COPY TO is a different command than COPY FILE, these are two-word commands. So there are cases in which order matters, but it's obvious.

So once more in short: It's okay both ways.

Chriss

 

[Trento777]

Guess it all depends on how best you understand it and how your native tongue works and how your brain processes priorities.

USE has many CLAUSES.

Since my native tongue is spanish, it's easier for me to think:

USE TABLE IN 0 ORDER xFIELD ALIAS cTABLE ....

To me, that's a direct order:

Pick/grab (what?) in (where?) odered by (what?) and, if necessary, call it/name it (what?)


Best regards, Mandy.

You've had Mike, Chriss and many other true proffesionals to help you.

I'm just passing by and also learning from all these great guys.

 

[Chris Miller]

What confused me until someone explained it to me was that USE is both the command to open a table and to close it. Well, the simple way to see it is to realize it does both at the same time.

And there are two cases:
a) you specify no IN clause, which means USE works in the current workarea.
In that case USE closes whatever is in the current workarea and opens the DBF file you specify by file name or just filename (dbf is assumed) or by database!tablename
b) you specify an IN clause, which means USE works in that specified workarea.
Otherwise nothing else changes, you close what is in THAT workarea and open the dbf file you specify.

There are special cases like the simplest - a USE alone, without any further clauses. It means you close what is in the current workarea and don't specify a file to open, thus you only close a DBF file.
The variations USE IN aliasname or USE in number both also don't specify what to open, so they only close what is in that workarea by alias name or workarea number.

Once you know that, USE and all its variations and clauses are less scary. Or not?
The clauses about ORDER, or about SHARED/EXCLUSIVE usage, and some more clauses like AGAIN can go anywhere else, and I agree you best write it the way it makes the most sense to you.

Chriss

PS: You use SELECT 0, Trento777 also mentioned the IN clause with 0. So I know I don't need to explain 0 is a special workarea number which is the first empty workarea. This way you don't need to track which workarea numbers you used and which not. In very early versions of FoxPro you only had a few workareas, now the number is practically unlimited, thus it makes sense to have this special workarea number to otherwise work with alias names. It can be very useful to first SELECT 0 and then USE something instead of doing USE something IN 0 in one command, because in the former case you know the selected workarea is the one the new file is opened in, in the latter case with the IN clause you open a file in an empty workarea, but don't select it. That can be fine in the case you have to open very many tables. Then you can SELECT one of them as last step. Anyway you do it, you do an extra SELECT either before or after USE, so it's just a matter of taste how you do it.

 

[Mandy_crw]

Oh I see.. Thanks Chriss... and Trento777

 

[Mike Lewis]

Mandy, the thing to keep in mind is that, in general, in all VFP commands, you can put the subsidiary clauses in any order. In this case, SHARED and ALIAS are the subsidiary clauses. It doesn't matter what order you put them in. It won't change the functionality of the command. So the best advice would be to do whatever you think seems most natural.

It's interesting that this even applies to SQL commands, where changing the sequence of clauses would cause an error in other SQL databases. In SQL Server, for examples, clauses such as ORDER BY and GROUP BY have to appear in a pre-defined order, but not in VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike,

I didn't realize this about SQL, interesting. I did know that MSSQL, for example, is very sensitive to that and never had these problems in VFP, so that explains it.

I just experimented with it and created a PRG with two differently arranged SQL-Selects:

Select * From sometable Order By 1 Group By 2 Into Cursor abc Having Count(*)=1
Select * From sometable Into Cursor abc Group By 2 Having Count(*)=1 Order By 1

In the FXP created from this PRG code by COMPILE I find these identical sections. I am not at all sure if they really are the right binary sections, but it seems the compiler puts the object code in some specific order, no matter in which order you write the commands:

1 oû	 sometableÇ¿üø
ýÀüCêüø

ýÃüø

ý¼½û abcþ
1 oû	 sometableÇ¿üø
ýÀüCêüø

ýÃüø

ý¼½û abcþ

The compiled FXP object code shows the same series of bytes twice. So the compiler puts the clauses of an SQL query in some specific order while it parses them, at least that's what it looks like at first glance.


Chriss

 

[Mike Lewis]

That's interesting, Chris. And it makes sense.

There are some other instances where VFP is more relaxed than SQL Server. One that comes to mind is the HAVING clause. In most SQL implementations, you can only have HAVING if there is a corresponding GROUP BY. But that appears not to be the case with VFP. You can do [tt]SELECT * FROM ATable HAVING <Some condition>[/tt], in which case the condition will be applied to the entire table.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

Why not simply use USE according to its description in the help file

USE [[DatabaseName!] TableName | SQLViewName | ?]

   [IN nWorkArea | cTableAlias] [ONLINE] [ADMIN] [AGAIN]

   [NOREQUERY [nDataSessionNumber]] [NODATA] 

   [INDEX IndexFileList | ? [ORDER [nIndexNumber | IDXFileName 

   | [TAG] TagName [OF CDXFileName] [ASCENDING | DESCENDING]]]]

   [ALIAS cTableAlias] [EXCLUSIVE] [SHARED] [NOUPDATE] 

   [CONNSTRING cConnectionString | nStatementHandle ]

hth

MarK

 

[Chris Miller]

Well, because in itself it does not tell - just to pick out one detail - that the NODATA clause only makes a sense if you USE a view and not if you USE a dbf.
So the syntax section is not selfdescriptive without knowing more.

It makes sense to dig into the features of this syntax description and what the symbols mean, that's true.

| means alternatives, for exxample you can sort the data ASCENDING or DESCENDING, but surely not both at the same time, thus there is a part ASCENDING | DSCENDING.
something within square brackets [ and ] means an optional part. For example everything after USE is optional, as USE without anything further already is a valid command.

Chriss

 

[Mike Lewis]

Aren't we in danger of over-thinking this? Mandy asked as simple question and she got a simple answer. Why not just leave it at that?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Well, you can opt out of a thread whenever you want, I like to follow up discussions. Nobody is oblieged to read everything, but it rounds things up.

Chriss

 

[Mike Lewis]

Yes, I take your point. What worries me is that we risk confusing Mandy. It is possible to have too much information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

And I think that's overcautious. Sure, hlaf knowledge is dangerous knowlegde and so only providing the currently missing piece of informtion is better at brinnging knowledge to the necessary completeness, while telling more creates new half knowledge. But then a thread is not only about the only person asking.

I think someone who has his/her answer also can judge whhether to follow the follow up discussion and perhaps ask about it or opt out. I see your point, but I don't sympathize with it. And as Mandy always points out that she likes to learn from us, I take it for granted it's also in her interet to get more feedback than currently needed. As she also points out her appliction is in a state I'd shortly describe as gamma version (>v1.00) there is no need to only concentrate on the essenttial concrete solution.

And in the bigger context, if you find a pure knowledgebas a forum can become should also have threads concentrating on one concrete question and its answer, then I find a forum without discssion in threads is a dead forum, it's then really just a reference and knoledg base, and for that we have either the VFP help file or books.

Tek-Tips forums have a faq section, and I cold extract just the Q&A about a) the USE into one FAQ and b) the general unforced order of command clauses and the syntax description into another FAQ, and there you'd have that knowledge base section that can also be used to point to it, when a question comes up again. I don't see much pointing to FAQ here, too. So the general stance differs, doesn't it?

Chriss

 

[Steve Meyerson]

Please pardon my i nterruption.

Chris has given much expanded knowledge which I do appreciate (not necessarily understanding it all) and thank him for his sharing.

I don't mean to speak for Chris, but I think there's an impied "To explain further..." in many of his posts.

Steve

 

[Chris Miller]

Yes,

I see you see, Steve, even if you don't want to explicitly endorse it.

That's my intent, also because when I learn a fact about something it's easier for me to learn more about the same context, as I'm already extending my knowledge, so I use that opportunity also the other way around and teach more than asked. And it also shows that it's easy for me to tell more about the context, as I have it memorized this way, effectively. I find it a natural thing to do.

Let me just point out once more, if you're after a Q&A database I think you make too little usage of the FAQ section. So I wonder how you think about that? Don't tell me you fear users don't find or use the FAQ, you can do it for your own use, to point to answers instead of posting a new same answer. Seems I have a point abot the whole reason. That you also like to have an individual discussion rather than pointing to a FAQ.

There also was a certain Valdemort here, that actually wanted to make it a forum policy, yet also only had written 4 or 6 FAQs in all the years he unregularly participated and not even FAQs about very frequently coming up questions. I foundd that also quite telling already.

Chriss

 

[mjcmkrsr]

Hi

Well, because in itself it does not tell - just to pick out one detail - that the NODATA clause only makes a sense if you USE a view and not if you USE a dbf.
So the syntax section is not selfdescriptive without knowing more.

Well, Mandy who claims to be a "Programmer", might (have) want(ed) to read the explanations following the description of the command - which in the case of USE are rather well documented

nfc

MarK

 

[Chris Miller]

Hi MarK,

exactly what ID recommend, still the syntax description alone does not tell that. If you turn the syntax description into a parser that is used to "understnad" code, you would recognize some USE commands as wrong as they are not in the order that's given by this syntax block. Anyway, as others said, we already did go too far in additional information. I still think different about that. For example, nobody can make anything from the binary code I posted in itself, but the fact two USE commands using the same clauses written in different order result in the same binary code can be used to deduct that you can't get a faster or in any other way better command by writing it in a special order. So there's that value in that extra post.

Don't worry, I rest this, the only thing I'd like to point out that I think there once was a general topic about the help itself, which described how to interpret these syntax descriptions, but I don't find that anymore. It's a side topic only, anyway. But one I didn't bring up...

Chriss