'Select area' of a table in Grid

[SitesMasstec]

My application open some tables, and the user can open a Grid (populated with data from another table) just by pressing the F2 key.
Well, some tables that opened by the application have their 'select area' defined (1-ESTOQUE.DBF, 7-FORNECED.DBF, etc).

I would like do define the 'select area' of the table (ITENSEST.DBF) that populates the Grid* to 15, in order that the files in the application do not crash (if the user press F2 VFP automatically defines SELECT AREA 1 for the table ITENSEST.DBF that populates the Grid, and I have pre-defined in the application the 'select area' 1 to another file -ESTOQUE.DBF).

Is it possible?

(*)In the Grid control Properties:
RecordSource: itensest
RecordSourceType: 0-Table

Thank you,
SitesMasstec

 

[Olaf Doschke]

Get rid of that habit, open up all tables IN 0, which opens them in a new unused workarea, then SELECT alias to select this workarea pr do the inverse SELECT 0 to first select a yet unused workarea and USE the table in there. You also set the grid to the ALIAS, not to a workarea number.

Bye, Olaf.

 

[Dan Olsson]

I agree, also you can use:
USE IN SELECT(aliasname) to close an alias regardless if it is open or not

 

[SitesMasstec]

Yes Olaf, but I opted for this:

In the Form, Load:
SELECT 15 (this is for control of all the tables I have in the application)
USE ITENSEST

In the Form, Destroy:
SELECT 15
USE

I used in the Grid control Properties:
RecordSource: itensest
RecordSourceType: 1-Alias

And now it works fine.



Thank you,
SitesMasstec

 

[Olaf Doschke]

No, I really mean 0 as zero and only zero = 0.

USE tablea IN 0
USE tableb IN 0

This way you don't end up with tableb taking the same workarea as tablea and thus being the only open table, 0 always references the next free workarea. Also, you have more than 15, unless you're at legacy FoxPro, but then ask at forum182, please, to make that clear.

In VFP there is no point in having a certain workarea number for a certain table, everything references a workarea with its ALIAS name and not its number, you don't care which workarea number tablea will use, all you care about is not using an alias name twice. Not using a workarea for two tables is automatic and won't happen anyway.

Bye, Olaf.

 

[Mike Lewis]

I completely agree with the others. Never use hard-coded work area numbers. Doing so is a bad habit that goes back beyond Foxpro 2.x.

In your particular case, the best way to handle it would be like this:

In the form's Load:
USE ITENSEST IN 0

In the forms Destroy:
USE IN SELECT("ITENSET")

That way, you are sure of closing exactly the file that you opened. And the closing won't fail if by chance the file was not already open at that point. The main point is that you never have to know the work area number.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

I absolutely agree with Olaf and Mike. Your code should never refer to specific work areas.

I wrote about this topic:

http://www.tomorrowssolutionsllc.com/Articles/Working with Work Areas.pdf

Tamar

 

[Scott24x7]

I know there is some discussion around the Data Environment, but I have been using it since around VFP6. Utilize the DE and none of this is necessary...


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

A failsafe opening of a table without ever getting "Alias name is already in use" or "File is in use" errors is this:

USE table.dbf ALIAS table IN SELECT("table") AGAIN

To understand this, you first have to know the USE command is both opening and closing the file related to a workarea (no matter if dbf file or .tmp files of cursors or views, which sometimes are only virtual). the simplest USE command, only USE, closes what is open in the current workarea, this contributes to the fact of Foxpro always having shorthand notations or parameterizations, if you only want to act on the current workarea, which also makes code shorter and readable, though I prefer more verbose code for sake of being explicit about what to do, the above USE command is almost as explicit, as it can be, I only leave out a few clauses like SHARED/EXCLUSIVE/ADMIN/NODATA/CONNSTRING, so this also is not the extreme maximum possible USE command. If you don't know any one of these clauses or options, please update your knowledge of the USE command and read its help topic, too.

To avoid double alias use, IN SELECT("table") ensures, that if the table.dbf file already is open, this command reopens it in exactly the same workarea it is already opened in. So, in that case, the USE both closes and reopens the table.dbf file, but there will be no "Alias name is already in use" error. What if no workarea has the alias name "table"? Then SELECT("table") is 0, and what does that mean? Bingo, if you paid attention to what I said earlier, this opens the table.dbf in a free yet unused workarea, which you always get with IN 0. Fine isn't it?

Now, why AGAIN? If some other developers code, for example, Microsofts referential integrity triggers, opens a table with another alias name than its automatic alias name, its file name, then you still would be getting "File is in use" error, which you don't want. AGAIN tells VFP you don't want that error in case the file is already open, you use the DBF again in that case and you don't care if other code opened table.dbf with an alternative alias name, which is best practice to do. So just notice: Even if you program with best practices in mind, that should include programming defensive against other people not doing so.

Side note: In the case of triggers, it is forgivable, as necessary to have the same table open twice for being able to move record pointer to other records even in case row buffering is used. As referential integrity is all about checks of correct referencing of data with foreign keys before saving something, it would be dumb to do that in the workarea the developer uses and might only row buffer, because that would "spill the milk".

Now I explained IN SELECT("table") AGAIN, but why ALIAS table? Actually, this is just being verbose, it is the alias name the workarea gets when you open table.dbf, but let's just think about the unusual case your dbf file name has spaces in it or begins with a digit, which both are not allowed for workarea alias names, then you may want to control the alias name instead of letting VFP decide automatically to convert spaces to underlines, you may opt for having aliases without underlines.

And finally notice AGAIN is not harmful, if the file was not already used, VFP doesn't test, if you really use the table AGAIN now and also while the ALIAS name specification may seem unnecessary it ensures the SELECT("table") uses that exactly same alias name. It becomes more apparent when generalizing the code.

So let's put this in a more general form of a function with two parameters DBF and ALIAS, then it becomes:

Procedure OpenDBF()
   Lparameters tcDBF, tcAlias
   tcAlias = Chrtran(Evl(tcAlias,JustStem(tcDBF))," .!,-+","_")
   If Isdigit(tcAlias)
      tcAlias = "_"+tcAlias
   Endif
   Use (tcDBF) Alias (tcAlias) In Select(tcAlias) Again
   SELECT (tcAlias)
Endproc

Now I leave it as open homework, why the first few lines treat the tcAlias value this way. Just notice, if it is passed in, it is used as given by the caller, otherwise it just automatically creates a valid alias name, also if tcDBF is not a (fully qualified with path) dbf file name but just the table name of the currently selected database. You may even try to pass in "dbname!tablename".

I bet now reading all this it confuses you and seems complicated. You think numbers then are still simpler than alias names, but using a number is making a definition you don't ever need when you can be more verbose. When you talk with your customers, do you address them with their name or with their customer number? I know dbfs are not people, but the point is not being polite to your dbf files, the point is being polite with your next developer and with yourself coming back to your own code, maybe daily. Also, numbers don't solve the file or alias in use errors. You gave dbfs a name for knowing what data they contain, VFP now allows you to work with that same name, and you still want to work with numbers instead? Is that sane? Do you also work with RAM addresses instead of variable names?

Finally, I most often write USE commands with the caution of such a procedure in mind, but not really implementing and calling that procedure. It's just an educational definition, over the top used in production. But I often enough write it in the specific form of [tt]USE sometable IN SELECT("sometable") AGAIN[/tt] without the alias clause as the specific sometable name will cause that alias automatically anyway and there is no reason to be more verbose about it. Of course, a procedure definition has to be more pedantic about it to work - sometimes against all odds or developer quirks.

You can use it, for example:

CLOSE TABLES ALL
CLOSE DATABASES ALL
OPEN DATABASE (ADDBS(_samples)+"NORTHWIND\NORTHWIND.DBC")

OpenDBF("northwind!customers")
? ALIAS(), AUSED(laDummy) && telling how many workareas are used
OpenDBF("customers")
? ALIAS(), AUSED(laDummy)
OpenDBF("northwind!customers","customers")
? ALIAS(), AUSED(laDummy)

There are a few minor issues with the procedure in comparison with only opening a dbf when it isn't open already via [tt]IF NOT USED("alias")[/tt]: If the table is already open, it will be closed and opened, which stores buffered changes, moves record pointer back to the top and puts buffering back to a default (normally no buffering), removes all relations starting or ending in the workarea, but on the other side USED("alias") can't test a DBF file being open, it just looks for the already used alias name and so the cases of a table being used with an alternative alias name are not covered by this simple check, which gives the OpenDBF procedure an advantage over the simpler only USE if NOT USED() approach, if your main concern is error free code execution. It doesn't unbind you from knowing what you want anyway. I for example rather avoid relations and combine data with SQL queries, mostly from MSSQL Server anyway, so all techniques specific to DBFs ahve limited use. And in the case of buffered changes existing and coming to a point you (re)open a dbf, that's questionable code, overall.

The last sample case of using the syntax with "!" might make it a good exercise to extend the code to automatically not just remove the "!" but in case tcDBF is passed in with that nomenclature, you just take the part after the "!" as the alias name. I thought it's nice the dbc name then becomes a prefix of the alias name, so you might also take that as is.

Last not least, the opposite CloseDBF() function would be harder to write if you really take DBF serious and want to close all workareas having a certain DBF file open, but taken from the perspective of closing an ALIAS, it's simply USE IN SELECT("alias"), as Mike already said. There is no procedure necessary for that, you might also leave it to the destroy of a form and its date session to automatically close all its open workareas. There is no limit, no memory or file handle limit, not today, that was a thing in the past, but we're in more modern times where RAM is no limit anymore.

Workareas don't cost the dbf size in memory bytes, that cost only emerges with fetching data into the cache memory and also remains when closing DBFs, that's never fully in your hand anyway. But work areas are really not the data in them, they hold their alias name (a few bytes) they have many attributes associated with them, though they are not objects, but all the CURSORSETPROP properties belong to the workarea, also FILTER, KEY, ORDER, the record pointer and maybe some more I forget to mention all belong to workarea and not DBF in it, they just affect the DBF and what you see in it. People often forget they work with the workareas and only indirectly with the DBF files. the record pointer, for example, feels like a file position pointer of low-level file functions, but if the record pointer really would be associated with the dbf file, it would not be possible to have a separate pointer when having the same DBF file open twice.

OKAY, and finally this doesn't care about EXCLUSIVE or SHARED, so it still can fail on opening a DBF, which another user has open exclusive, but it covers the nastiest cases of file/alias already used errors.

Bye, Olaf.

 

[SitesMasstec]

Dear collegues:

I agree with you about the use of ALIAS instead of SELECT (number), and as Olaf said, is from old Microsoft FoxPro 1 to 2.6.

I mantain this kind of approach for easier track of file types in my applications:
1.SELECT 1 to 9 for basic (most important) files
2.SELECT 10 to 19 for generated files which can be used in other system outside the application or sub-system in the same application
3.SELECT 20 to 29 for generated temporary files to generate reports/labels in the application

So, when I look at lots of code, it is easier for me to understand the purpose of those lines.

But be sure I appreciate (and also learn a lot from) your expert posts.


Thank you,
SitesMasstec

 

[Olaf Doschke]

You can only have static workarea numbers in the case you are the only developer, just because others work with an alias approach and not care about the workarea number dbfs are opened in or cursors are created in. Think alone about this: SQL-SELECT INTO CURSOR (Select Areas 20-29 in your world) does not allow specifying a workarea number, only alias names.

You should rather use prefixes of alias names to conclude about your types of data scopes.

Bye, Olaf.

 

[Mike Lewis]

If anyone is interested, the use of hard-coded numbers for work areas was deprecated long before even FPD or FPW. Foxbase Plus supported [tt]SELECT 0[/tt] to mean select the next available work area and [tt]USE ... IN 0[/tt] to mean open a file in the next available work area.

The original dBASE II (the ancestor of all Fox / xBase dialects) only supported two work areas, named primary and secondary. dBASE III Plus and Foxbase Plus increased that to ten, which could either be numbered 1 to 10 or designated with letters A to J. Successive versions of Foxpro gradually increased the limit to the present 65,535 (but A to J as work area designations still exist in the VFP, which is why it is inadvisable to use those letters on their own for table names, field names, variables, etc).

I know this doesn't help answer the original question, but it might be of passing interest.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

This seems to be one of those things you just can't convince the user to accept.
It's a terrible practice, there's an easy and obvious solution, if they won't accept it, then don't waste your time.

Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[SitesMasstec]

Dear colleagues:

By the last 3 posts here, I am now convinced to begin to change the way I select tables.

Thank you very much.


Thank you,
SitesMasstec

 

[Mike Lewis]

I am now convinced to begin to change the way I select tables.

Definitely the right decision. Good for you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You're welcome. Just a note about a possible prefixing of alias names:

In the normal case you would not use any prefix and use alias name= file name, but then it comes to other distinctions than you do, you have cursor which you can give a prefix crs or cur, more generally speaking VFP suggests three letter prefixes for classes and objects (see

https://msdn.microsoft.com/en-us/library/2548zzyt(v=vs.80).aspx),

especially for all controls (txt = Textbox, edb = Editbox etc). The prefix cur is suggested for Cursor objects, which very specifically mean the objects within Data Environments, not cursor alias names. I often use both crs and cur as cursor alias prefix, when I write code in Forums, it mixes a little, I should rather stick to crs for alias names and keep cur for the data environment objects, but I don't make use of these. By the way, they also don't allow you to specify which workarea number the corresponding table, view or cursor adapter will become.

Frameworks I used add a prefix v_ for views and keep it that way also for cursor adapter cursor alias names when they came from the time the major data access objects were views, then this may have the distinction of lv_ for a local view and rv_ for a remote view. You can take some ideas from the help topics about names and naming conventions, there also are some about variable names and naming rules in general.

In you distinction I'd perhaps use the general rule to keep it at file names for the most important tables, so alias=file name (of course disregarding .dbf, just the stem name), In regard of generated files for external systems, you may prefix them with ext or any short for the subsystem, that's also more about the file names and perhaps location, from that point of view you can give files these prefixes and then again stay with the alias=file name base rule.

What really stands out from normal dbf files is cursors, no matter if you get them from query INTO CURSOR, from CREATE CURSOR or by using a view or cursor adapter. Any alias, which by DBF("alias") turns out to be a .tmp file can be considered a cursor, some even take cursor as a synonym for workarea based on the fact CURSORSETPROP() actually applies to any workarea, also to tables. So the meaning of terms overlaps here anyway. At least crs is a nice prefix for all the alias names not addressing a permanent dbf file. But as said frameworks use different naming convention, this is really your taste. By the technical rule you can't prefix alias names with numbers, the first character of an alias name could only be an underscore.

Of course, this will introduce a new feeling for cursors in the set window, the data session window normally very useful in debugging to manually pick a workarea. As you don't set the workarea numbers anymore, a dbf or cursor may appear at any position in the list depending on the order of events happening, there is no sorting by alias name option involved, but you can, of course, define some helper functions and forms, AUSED() will create an array of used workareas and their alias name, which could be used as source of a listbox and sorted via ASORT.

Bye, Olaf.