VFP 9.0 SQL select command on .dbf 3

[Steve Yu]

We have a 30-year old SBT package that was written in dBase III and ported over the years to VFP 3,6, and now 9.0.
It is working great and we don't want to change anything.
I'm looking to try get around the VFP restriction that same .dbf can not be opened in more than one work area.
The need is for a shared subroutine to open a commonly needed .dbf for processing without having to worry if that .dbf is being opened in any of the calling programs.
New to SQL, I thought I could get around that restriction by using SQL select cursors since it seems to me I can create multiple cursors against the same .dbf. To my surprise, the target .dbf is opened in a random work area when I issued the command 'Select * from .dbf into cursor testcursor alias c1'. And upon return to the calling program the target .dbf remains open and will cause an error to occur if the calling program tries to open the same target .dbf in a different work area.
Can someone help with some insight if my description can be understood ?

Much obliged,
Steve the gray haired xBase programmer in Houston, a very experienced one nonetheless.

 

[Nigel Gomm]

Steve,

guessing a bit here but it could be that your select * is not creating a new underlying table.. but instead a filtered view of the original dbf/alias. Add the NOFILTER keyword to the select statement to get around that.
e.g.

SELECT * from (alias()) into cursor sys(2015) NOFILTER READWRITE

But,

If i understand correctly you just want to open a dbf without having to worry if it is already open somewhere else in the same program? Try the AGAIN key word to USE .
e.g.

USE tablename AGAIN ALIAS myalias in 0

hth

n

 

[Mike Lewis]

Steve,

First, welcome to the forum.

Your assumption that the same DBF cannot be opened in multiple work areas is incorrect. All you have to do is to add the keyword AGAIN to your use command. For example:

USE MyTable IN 0
USE MyTable AGAIN IN 0

This will open the same table in two separate work areas.

What you cannot do is to use the same alais for the two different work areas.

Anothre way to open the same table twice is to open them in different data sessions. But since you are adapting an existing ex-dBASE application, that might be more difficult to do retrospectively.

Regarding SQL: When you use SQL to create a cursor from a DBF, VFP opens the DBF in its own work area behind the scenes (it is a specific work area, not a "random" work area). And you are right that it then leaves it open. But this is rarely a problem, as the new instance of the DBF will have a different alias, and its work area will not normally be visible. The only time this is really an issue is if you later try to open the DBF exclusively. That won't work of course, as the DBF is already open.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

By the eay, a very common technique for opening a DBF is like this:

IF NOT USED("MyTable")
  USE MyTable IN 0
ENDIF
SELECT MyTable

This says that, if the table is not already open, go ahead and open it in the next available work area, and then move to that work area. If the table is already open, just move to its work area.

This technique is commonly used in subroutines. Of course, any changes that the subroutine makes to the status of the table (its record pointer, index order, etc), will then be reflected in the table's "other" work area. If that's not what you want, then use the AGAIN option, as per my previous post.

I hope this makes sense.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Hi Steve,

First of all, I am wondering how you're successfully running the SELECT statement with an ALIAS clause in it.

'Select * from .dbf into cursor testcursor alias c1'

I think that should give you an error!

Now, to what others here alrady suggested you, I am just adding what I do usually.

Inside the method/program in which you will be working on the copy of your table...

1. Save the ALIAS() to a variable (obviously, we wouldn't know if an ALIAS is actually in the currently selected work area)
2. Collect data from your table (which might have been already opened) to a temp cursor.
3. Do your stuff
4. Close the temp cursor
5. If the ALIAS variable (from 1) is not empty, restore the select area
6. Go back to your calling program

Below is a basic idea

lalias = ALIAS()
SELECT * FROM <yourdbf> INTO CURSOR testcursor NOFILTER READWRITE  && READWRITE is required only if you want to change the cursor content
*/
*/ Do your stuff here
*/
IF USED('testcursor')  && just in case, a precaution
   USE IN testcursor  && close the cursor and erase the temporary file of the cursor data
ENDIF 
IF NOT EMPTY(lalias)
   SELECT (SELECT('lalias))  && reselect the ALIAS which was active, if any, when you entered inside this method
ENDIF 
RETURN && return to your calling program from this method

Rajesh

 

[Chris Miller]

First, Mike is right with AGSIN, but what you observe is also right, so Rajeshs idea to close the workarea SQL uses will solve the problem the calling code doesn't use AGAIN or tries to get exclusive access or whatever causes the error.

So, even when you go SQL you still have to cope with workareas, also workareas that are keeping DBFs open, no matter if you use NOFILTER or READWRITE (there is no need to use both, READWRITE include NOFILTER behavior, just make the result cursor updatable).

There is a thing that would almost work: CREATE VIEW and SET VIEW TO (not to be mistaken as SQL View), that saves the currently open workareas and restores them. It's also not closing a cursor, but there is one bigger problem: Current record numbers are not stored. And even if, this has negative effects on relations set and I think I also gave up using this as it causes grid reconstruction.

Rajesh already sketched a solution, I'd do establish a well known situation, that is open the DBFs you query in the SQL beforehand, so you know these workareas were used from you and are disposable in the end. So for a query SELECT * FROM table.dbf where id=5 into cursor result nofilter to not influence the datasession in any other way than to add the cursor with alias result, you can do

nInitial = Select(0)
cTempAlias = '_sql'+sys(2015)
Select 0
USE table.dbf again alias (cTempAlias)
Select * FROM (cTempAlias) where id=5 into cursor result nofilter
Use In Select (cTempAlias)
Select (nInitial)

Yes, it's a bit ugly to need all this just to not touch the caller datasession state aside of creating a new result cursor. It may also be that the table left open isn't the real problem, but only not going back to the initially selected workarea. Good code should not just open tables when they already are open, and to act on this premise you would even only use the table.dbf when it's not yet opened. But that would overcomplicate things.

It could save a bit time, if you know the table you query is already open. The SQL query would still use its own workareas during the query, it would also leave the table open, but not the additional workareas used during the query, so the SQL will just generate the result cursor and keep the workarea with the table untouched in terms of anything: Recno, Filter, Relations starting from it or ending into it. All these things are actually the reason SQL opens tables again in any case, especially to not get a result influenced by the currently SET('Filter') and to not cause relations to move in other workareas while the SQL engine collects data.

So the only bad habit of SQL is to leave a DBF open when it was not open beforehand. I think the reasoning of the MS VFP team was that an SQL view needs to be able to apply tableupdates to the DBF when you do tableupdate the view alias of an updatable view, but that case is not important for a simple query on a DBF that's not generating an updatable view cursor. I think it also has to do with the convenience to already have the DBF open when you query it multiple times, the caching of results seems to depend on that workarea, too.

In short: It's complicated. I'll think a bit more about this to find a solution taking less steps. I think I do have something in my toolbelt that stores a snapshot of a datasession state and can go back to it (except keeping a new result cursor open, of course). That's taking even more steps, but makes it two calls one before and one after the SQL. I think there is no universally correct way as a generic routine can't know whether the newly opened DBF should be closed, so even with such a helper function you still need to specify what should not be reset and I chose something about that which may not fit your needs. I may also not have all rights on this to publish it.

I'm just reminded on this occasion that someone told me CREATE VIEW is the solution, but it certainly is not.


Chriss

 

[Steve Yu]

I'm totally overwhelmed by you guys' response, let me thank everyone first of all. not expecting there are still so many VFP practitioners out there.
I'll need time to digest all the suggestions; without checking it, I'm aware of the use again clause but wouldn't the record pointers move in sync in both calling and called program ? That is not desirable as the called program is activated randomly via function key F1 at any READ (sorry this is xBase, no data sessions, no remote views, no forms, just @ say read). The called program, inventory lookup, is just that; don't want to affect the pointer in the calling program.

 

[Steve Yu]

Mike,
On use mytable again, problems arise when you have good old .ndx files involved (sorry again, this is 30-year old dBase III structure): the .ndx files are shared by both calling and called routines; if the called routine changes, open or close the .ndx files, the calling routine's .ndx are affected in sync and all pointers will be altered and/or closed.
Chriss,
Thank you for your suggestion of the workaround; this would definitely work for me to get around the 'use mytable again' dilemma, and I don't have to worry about the .ndx mess either.
The extra first step of issuing 'use mytable again' before the SQL Select looks redundant and unnecessary but in reality is un-avoidable if we want to make sure mytable is not left open, by design now that I've learned (still extremely confused even after your kind explanation), after the called routine returns; the calling program could bomb if it tries to open mytable.
The F1 inventory popup has been the delight of all sales staff in the company for quick access from anywhere on the menu. the 'use mytable again' technique is absolutely essential; The SQL Select I'm learning now will replace the old cumbersome way of saving and restoring all work area open tables, indexes, and pointers (a huge and error-prone process)

Thanks again everyone and I really appreciated your taking time to help out.

Steve Yu in Houston

 

[Chris Miller]

Just one comment: Use again opens a table twice, provided another alias name, that has it's own record pointer, set order/index and filter, it's a separate workarea, for short.

I don't know about ndx indexes and how they would be involved, but I guess a separate workarea means no index sort order at first, unless you'd set one, so I'd also assume no influence of the callers workareas.



Chriss

 

[Chris Miller]

I just tried this:

Clear
Open Database c:\Users\Public\vfp9\samples\northwind\northwind.Dbc
Use northwind!orders
Set Filter To Recno()<6
Go 5
Set Order To OrderDate
Browse Nowait
? Alias(), Select(0), Reccount(), Recno(), Set("Order"),Set("Filter")
Suspend && screenshot 1
* Resume && typed in the command window

nInitial = Select(0)
cTempAlias = '_sql'+Sys(2015)
Select 0
Use northwind!orders Again Alias (cTempAlias)
Select * From (cTempAlias) Where Isnull(_vfp.DoCmd('set')) ;
	Into Cursor Result NOFILTER && screenshot2 during query
Use In Select (cTempAlias)
Select (nInitial)
? Alias(), Select(0), Reccount(), Recno(), Set("Order"),Set("Filter")

Before the query (after Orders is opened and recno, filter order are set):

[pre] [/pre]

RESUME in the command window and during the query I could make this screenshot:

You can see the tempalias _sql_66... I open, on top of that (literally) the SQL engine uses 3 workareas during the query: C,D, and Result, the Orders workarea is not touched.
The active workarea C has the same metrics (Records: 830) as Orders, so it is the orders.dbf, no idea what D is for, but likely something for query optimization, and Result is, well, the Result that's generated because of Into Cursor Result.
[pre] [/pre]

after the query ran, the output and datasession window is showing what has been tidied up by the SQL engine:

You see the SQL engine closes C and D, and, of course, keeps Result open

In this case there actually was no need for the tempalias, The already open Orders.dbf wasn't touched by the SQL engine anyway.

But let's look at the case I query another table, like foxcode when I don't have it open before the query, without using the tempalias, something like this:

Clear
Open Database c:\Users\Public\vfp9\samples\northwind\northwind.Dbc
Use northwind!orders
Set Filter To Recno()<6
Go 5
Set Order To OrderDate
Browse Nowait
? Alias(), Select(0), Reccount(), Recno(), Set("Order"),Set("Filter")
nInitial = Select(0)
Select * From foxcode Where Isnull(_vfp.DoCmd('set')) ;
	Into Cursor Result NOFILTER && screenshot2 during query
Use In Select (cTempAlias)
Select (nInitial)
? Alias(), Select(0), Reccount(), Recno(), Set("Order"),Set("Filter")

After that - I spare another screenshot - The foxcode table is open and not closed after Result was generated.
But introducing the tempalias, foxcode is not left open:

nInitial = Select(0)
cTempAlias = '_sql'+Sys(2015)
Select 0
Use foxcode Again Alias (cTempAlias)
Select * From foxcode Where Isnull(_vfp.DoCmd('set')) ;
	Into Cursor Result NOFILTER && screenshot2 during query
Use In Select (cTempAlias)
Select (nInitial)
? Alias(), Select(0), Reccount(), Recno(), Set("Order"),Set("Filter")

[pre] [/pre]
With this code only two workareas remain used: Orders and Result.

While this is quite complicated it tricks VFPs SQL engine to only use the DBF it queries in one of its temporary single letter named workareas it closes at the end of the query. So indeed during the query foxcode is open twice, but the workarea C (or whatever) is closed by the SQL engine and the workarea _sql_... is closed by the Use In Select(cTempAlias), so as net effect Result is the only new workarea.

Code that would open foxcode later without AGAIN and without checking whether USED('foxcode') is true, will therefore not fail. As Mike Lewis said, it's actually bad code that wouldn't check this and simply open a table it assumes isn't open yet. But if you don't like to mend the legacy code to manage workareas better, then this helps.

It's still just covering the simple case of a single table query. You can extend this to make use of several tempalias names for all dbfs you want to keep as they are currently. It's complicated, sometimes unnecessary, especially when the involved tables are already opened, but this overrides VFPs SQL habit to not close DBFs it queries, whether they already were open, or the SL query opened them.

I bet it's still confusing, but at least it's a reference you may understand, eventually, after you played with this yourself.

By the way, I have this from a session of Christof Wollenhaupt (Foxpert), he also used _vfp.DoCmd('Suspend'), but for some reason that doesn't work, as if it's ignored. The command SET simply shows the datasession window and does so for every record queried, which makes the query slow but you can see the temporary workareas SQL uses.

Chriss

 

[Steve Yu]

Chriss,

Thanks again for the elaboration.
I understand now problem only occurs when the target table is not already open when the 'Select * ...' is issued - SQL engine will open the target table in a hidden work area and leave it open after the 'Select * ...' query is finished; not good. No issue if the target table is already open - SQL engine will use whatever it needs from the target table (without affecting any existing pointers on the table) to generate the query and leave the target table as is after the query.
On 'bad coding for not check before use ....' comments: in my case, it is not possible for the calling program to check because the called routine is a popup (ON KEY LABEL F1 DO popup) that can be called from any READ prompt (hundreds of possible entry points on menu). It's the sole responsibility of the popup to save and restore all work areas with relevant pointers, a painstaking process and error-prone. The new 'Select *' query method will help simplify the process, I hope. And we shall see.
Regards,

Steve Yu

 

[Chris Miller]

in my case, it is not possible for the calling program to check because the called routine is a popup

That's not the problem, the problem is any code that just USEs some dbf without first checking whether it already is open. That applies to any code anywhere and would also solve the case you have without needing to close the tables that an SQL query opens and keeps opened.

As the problem only is when code opens a table that's already open, that code wasn't programmed for the most general case where opening a dfb again is a problem. Programmaers aware of that could in the simplest case use AGAIN as precaution.

To show one last case you might doubt is working, assume nothing is open at start, you program with IF USED() and use a workarea with the right alias, then you do actually not need to worry the next SQL disturbs your use of that workarea, too. SQL always does USE DBF('alias') AGAIN for its purpose.

close databases all
close tables all
Select * from foxcode into cursor foxcodecopy nofilter
*... any code might run in between these lines
If Not Used("Foxcode")
   Use foxcode in 0 again
Endif
Select foxcode
Go 5
? recno()
* now another query
Select * from foxcode where recno()<5 into cursor foxfirst4 nofilter
? recno('foxcode') && this will still be 5

Chriss

 

[Mike Lewis]

the called routine is a popup (ON KEY LABEL F1 DO popup) that can be called from any READ prompt (hundreds of possible entry points on menu). It's the sole responsibility of the popup to save and restore all work areas with relevant pointers, a painstaking process and error-prone.

Was it not Mark Twain who said, "When you catch an ON KEY LABEL, kill it"? OK, maybe not. But seriously, ON KEY LABELs can cause all sorts of problems, especially when they do things with tables or aliases or work areas. If you are already using them in your application, it might be difficult to change, but if you get the chance to get rid of them, do so. (Not to mention getting rid of the READ states as well.)

That said, it would be easier now to save and restore the work areas and pointers, as VFP has the AUSED() function which saves all the current aliases and work area numbers into an array, which you can then loop through. Personally, I've never found it necessary to do that. If a subroutine uses a given table, and you don't want to open the table AGAIN. just save its RECNO(), then go back that number when you have finished.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike Lewis,

there are cases of relations, where even just changing the active record causes other record pointers to change, too. On top of that with record buffering (only) you can cause update problems when leaving a record sooner as was intended. So an ON KEY LABEL routoine surely should use its own workareas and not touch anything already open. But that should also be enough, even being aware of the problem of USE without again, it's the responsibility of any code that runs with such ON KEY LABEL intermissions, that it handles the case a table is already open. It's simply a very normal way to use tables, as you suggested it yourself. Once all code does that there also is no problem with tables kept open.

The argument actually plays against itself, if you "fear" the table is used, then it's better for whatever code it's already open. So actually this should be welcomed.

This all goes well with the general rule to name dbfs without spaces, open them with their name, usually. Always using the AGAIN clause also helps you in the cases code uses a special alias like the "_sql"+sys(2015) I used. You can use that same name pattern elsewhere and still have no name collision. For multiuser scenarios GUID would be better, but you still can also check whether USED(tempalias) is .t. before you really use it, if you want to be 100% sure you TRY to USE it AGAIN with a generic alias. Exclusive use always will be a problem, no matter if you keep strict track of tables opened. A table could also be open shared for other reasons than SQL having kept it open, so you usually only work shared on data and exclusive in separate admin tools.

I know Steve, you likely won't want to churn through all existing code that simply uses tables. Some of it might do so at the start of a form with a private datasession, where you can be sure there's no problem. I thinkyou also are stuck a bit in the serial way Fox applications with READ states work, if you go for Winforms thinking about things happening in parallel, even without multithreading, just by ON KEY or other events has to become your norm of thinking. So code has to become strict locally and not depend on other code not interfering on the one side, but code should also not interfere with what doesn't belong to it. With the way VFP works regarding tables kept open, the easier way to cope with it is writing code stable against such conditions. So if a table is used by its normal name, you can be sure it's not closed before you do (if your code has the responsibility to close it), but you can also leave it open for others to use for their purposes and for special use cases you need a table to be yours for a longer lifetime give such workareas special alias names. Combine that with always using AGAIN just in case and you have a stable system.

Closing tables in winforms environments should be the task of a forms datasession and not individual code, unless you have a method that needs a dbf only breifly. But even then, all open tables are closed when a datasession ends. With legacy applications that might be the biggest difference, but even then, the dbfs will close when the EXE exits.

There are more penalties for closing and reopening dbfs then for keeping a long life file handle. To illustrate the case I never had problems with applications that only once did OPEN DATABASE and then used tables of the DBC on and off. And the DBC itself also is a free table.

Chriss

 

[Steve Yu]

Chriss and Mike,

All your comments well noted.
The on key label F1 usage is indeed troublesome and could and does cause work areas to lose pointers.
The flip side is that it provides an invaluable tool for sales staff to look up inventory quickly, whether they are entering an order, needing to answer a customer on the phone, etc. without having to navigate to the item inquiry screen on the menu. So it is worth the save and restore coding headache.
However aside from the popup F1, most of the rest of the package are sequential processing where calling programs and called programs know each other well and conflicts rarely occur. And also remember, when this package was designed back in the days, only very limited number of work areas are available.
As to 'getting rid of the READ state', it's a very valid suggestion. In reality, we've spent thousands of dollars recently trying to find a 'modern' package (forms,remote views,tableupdates,etc) to replace our existing one. And we have not found one yet.
The character mode user interface maybe archaic but it's more efficient from our perspective. And there is nothing antiquated about the capabilities of this system of ours: realtime interface with Amazon,Wayfair,and hosts of other trading partners for automated exchanges of PO,SO,invoing,etc; UPS, FedEx, ACH with banks, you name it and we have an automated realtime interface. One of the past Mondays we processed over 3000 online orders, hectic in the warehouse but we got every order out same day. We love the power VFP generates.
Regards,

Steve Yu

 

[Chris Miller]

Steve Yu,

all well noted, but instead of having the overlap in workarea use ON KEY LABEL F! could also start a form or screen with a private datasession. You can mix VFP9 capabilites with legacy code, i.e. a form could also simply be a window. I guess all that needs to come back is a string that's pasted into the current control in READ state, and for that you don't need to work in the same datasession to return a result cursor, you can easily return a single value or even a list of itmes, just comma separated, you can also return an array you create in a called ON keyboard event code by return @array, that doesn't only work for passing an array in, it's also working for the output you return.

That's surely the easiest way to not interfere.



Chriss

 

[Steve Yu]

Chriss,

From my legacy code, at the READ prompt, how would I activate a form ?
Aside from the mechanics, the form popup may not work for us simply because, once popped up, it would require user to point and click in order to navigate the various options on the form. Again this would deviate from our mostly keyboard oriented interface (with a few exceptions); one we have chosen to follow because it is actually more efficient than GUI in a very fast paced input environment.
However I'm always interested in learning, so I'd like to try the form popup to see how it works; as you mentioned, the new data session should totally eliminate the save and restore mess.
One more note on the return value from the form, there wouldn't be any in our case. Actually we don't want anything coming back into the READ buffer at all - the popup is strictly a information only display, on a given item number. It receives an item number, displays bunch of info and returns to resume whatever the READ was at.
Thanks again.

Steve Yu

 

[Chris Miller]

First of all, ON KEY LABEL can do any code, also code that creates windows or forms. And such "forms" can be as simple as a poupmenu, too. But before anything else, they could have their own datasession.
I'm with Mike, that ON KEY LABEL is better put into menu hotkeys. People tend to forget the right click also is a key on your keyboard. Besides the click being a click on a trackpad or similar, or a space or enter.


Chriss

 

[Steve Yu]

Hi Mike,

Thank you for your suggestions and comments.
Yes we are running in VFP 9.0 SP2, in backward compatible mode nonetheless, with @ say get and no forms in any of the core modules.
However we do have one stand alone form module running on Apple Ipads in our warehouses, with touch screen input, that tracks the movement of containers coming in and out of our facilities, over 200 in a week. Status are updated in real time via RDP connections to the office applications.
Regards,

Steve Yu

 

[Chris Miller]

From my legacy code, at the READ prompt, how would I activate a form ?

Just to give an example:

ON KEY LABEL F10 names()
CLEAR
@ 1,1 say 'name?'
STORE '' TO cName
@ 1,8 get cName size 1,20
READ

PROCEDURE names()
   DEFINE POPUP names FROM 1,15 SHORTCUT
   FOR nline = 1 TO ALINES(aNames,'Chris,Mike,Rajesh,Steve,Tom',',')
       DEFINE BAR nLine OF names PROMPT aNames[nline]
       ON SELECTION BAR nLine OF names Keyboard(prompt())
   ENDFOR
   ACTIVATE POPUP names
ENDPROC

Names couold and should be a prg, it can run a screen or a form. Whatever you want can happen.

Chriss