SELECT command and table record position

[Akourou]

hello,

i have a question

when i run the following code

select * from table-A
join ...
join ...
where ...
order ...
into cursor result-a

select result-a
.... (browsind the records)


what happens with the record position of table-a?
it stays to the record that it was before the statements?

 

[Mike Lewis]

In general, yes, the record pointer of Table A will be in the same place. That's because it's not actually Table A that gets scanned for records in the result set. It's a second instance of the table, that VFP opens behind the scenes. You can see that by running the SELECT without explicitly opening the table. The SELECT will still work.

But, really, I'm wondering why you posted this question. It would have been the work of a moment to try it for yourself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Akourou]

haha, yes

because i try to understand why it goes to Top on table-A
even when i store recno() in a variable and issue goto varW at the end of the select statement
sometimes it goes TOP

 

[Mike Lewis]

No need to do all that. Just observe the record number on the status bar. You will see the following:

USE Table_A    && Record 1 (because you have just opened the table)
50                && Table A will now be on record 50, and you will see that in the status bar
SELECT * FROM Table_A INTO CURSOR Results  
                  && Now you see 1 because the cursor is the current alias
SELECT Table_A    && You should againsee 50, indicating that the record pointer has not moved

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Are you sure you are talking about table-A when you say it has gone top?

The default position after that code would be to have results-a open in the current workarea wouldn't it?

A quick test shows Mike is right, table-a remains at record 28 if it was at record 28 in the beginning


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

A SQL Select may create a filter cursor, but not when you join other data. To check, see what DBF("result_a") is. If it's the name of one of the involved tables it's a filter cursor. It'll most probably be a tmp file name. (by the way, a minus sign in a name is not allowed).

Your new workarea is neither tableA nor any of the joined tables, you create a new cursor, a temp dbf. It has a separate record pointer and points at the top.

You still should stay on whatever record in tableA, because SQL always opens up its own workareas not only for the query result but also the source tables. Therefore SQL will also not take into account any filters you set on tableA or any other things in effect for the tableA workarea. Since you query from an already opened workarea, to get at the source, the SQL engine will determine DBF("tableA"), reopen that and query

To illustrate the principle, Mike has already given code. This here is self-contained and doesn't depend on your data.

Close Tables All
Clear
Create Cursor crsTest (id int)
Insert into crsTest values (1)
Insert into crsTest values (2)
Insert into crsTest values (3)
Goto 2 in crsTest
Create Cursor crsTest2 (id int)
Insert into crsTest2 values (3)
Insert into crsTest2 values (4)
Insert into crsTest2 values (5)
Select *, Alias() from crsTest inner join crsTest2 on crsTest.id=crsTest2.id into cursor crsResult
? "record ", Recno("crsTest"), "in crsTest, workarea no", Select("crsTest")
? "record ", Recno("crsTest2"), "in crsTest2, workarea no", Select("crsTest2")
? "record ", Recno("crsResult"), "in crsResult, workarea no", Select("crsResult")

after you run this you'll see crsTest and crsTest2 will be using workareas 1 and 2. Both record pointers are where they already were before the SQL. Tha alias name within the crsResult will be "C"m which is the name the SQL engine will give to something it opens in workarea 3. And the crsResult will be in workarea 6, which indicates the SQL engine also used workareas 4 and 5, but the final result is created in workarea 6.

And this is even so, despite the sources not being permanent DBFs, the SQL engine does not interfere with any already used workarea, It'll use its own workareas and put the result into a new workarea, too.

More things that may play a role are tablebuffering, if you only use record buffering and move off a record, that triggers a tableupdate of that left record and within that update/delete triggers run and may do anything.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Just also notice: The active/selected workarea changes to the sql result cursor. If you GOTO a record number after your code, without specifying IN or without first selecting the original tableA, you move within the query result.

If for any reason Recno("tableA") still changes, if you knwo the recno you can nevertheless of course still go back there: [tt]GOTO nRecno in tableA[/tt].

Just also notice, if you use RELATIONS or FILTERs etc. This might not suffice, once your record pointers are "out of whack", you'd need to reposition in all of them and in the right order, as GOTO may not only cause triggers, it also moves correspondingly in workareas connected via RELATIONS.

Again, another reason the SQL engine does not use any originally addressed workarea, it just knows which cursor/table etc to query from and as Mike also already said, if a source table isn't even open, it'll be opened, either found in the current direcory or within the currently active DBC.

And in that respect, VFP will keep that open, even when it closes other temporarily used workareas. That's done in expectance your next queries will again involve thise tables again. But that's the only aspect in which the SQL engine isn't strictly tidy, when it doesn't see an alias already in use, it'll look for tables and open them and leave these tables open. In the sense of previously used workareas and their record pointer etc. there are no changes from SQL, though. If you have changes there, they come from other places.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Akourou]

Thank you Mike and Olaf.

Very informative.

But i have to search more because my "tableA" goes to top after the sql query.
And worse, it does not do that in all cases. sometimes it stays at the current record.

 

[Mike Lewis]

Can we look at this from the opposite direction?

What do you want to achieve? Do you want to maintain the current record position? Or do you want it to go to the top? Or what?

If you want it to retain the record position, do this:

lnRec = RECNO("Table_A")
SELECT ... etc. INTO CURSOR ...
GO lnRec IN Table_A

If you want it to go the top, just do GO TOP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Such "sometimes" errors are harder to find, but how good are you with the debugger?
You could do a conditional breakpoint on the condition RECNO("TableA") changes, keep it inactive, SUSPEND right before the SQL, turn on the breakpoint and then resume.

And one more thing to do is use coverage logging to log any code happening, this can even be done in an EXE on a PC just having VFP runtimes available. When you're experiencing the go top you could analyze where it happens. It won't happen all alone as behavior unless a RELATION as I already mentioned.

It's tedious to analyze a coverage log, as you need to put it side to side to source code, the coverage profiler will allow to see source code with statistics side to side, but doesn't have a mode to show you the command sequence. You can find essentail starting points in the log, though, when you search for the method doing the SQL and the line number and assume it's not much code running aside from the query, the offending code doing so record movement directly or indirectly should be near that line in the log, even if it happens in the stored procs of a DBC or a control, in inherited code or wherever else, as the coverage log simply logs any code running in sequence.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Akourou]

is it possible that it has do with combobox in the form, bounded with a table?

does a combobox bounded to a table move the table pointer?

 

[Olaf Doschke]

Yes, a combo moves the pointer to the select value.

Also a grid cell selection causes record pointer movement.

All controls with a list bound to a table directly will move in it, so also a listbox. That's by design, that way you can concentrate on the workareas and not control.value

If you don't want that you have to populate controls with independent cursors, or with the items (listbox/combo) created by additem()

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[Akourou]

It is true, the grid has nothing to do with it.
I deleted the grid and still i have the following behavior:

I have guestform that uses guest table to manipulate guest info.
I have bookform that uses booking table to manipulate bookings of guests.
Bookform has a combobox bound to the guest table.
So when i move to other records in booking table, the record pointer of guest table also moves to the specific guest.

If i have both forms open everything seems ok.

If i open only bookings form and go to a specific record, then open guest form, it immediately GOTOP to booking table.

can you explain why is this happening?

 

[Mike Lewis]

Have the two forms got different private data sessions? If so, then that could mean that the guest form is opening a new instance of the table, in which case the record pointer (in that new instance) would of course be at the top.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

One form could only influence another form if both share a data session.

But Mike, of course, is right: If the newly opened form opens its tables in its own private data session, it starts off uninfluenced at the top.

I mentioned RELATIONS already, you didn't react to that. If you put tables into the data environment you may have lines connecting two related tables, That also corresponds to a SET RELATION. They act by moving record pointers from some head table to the first corresponding child record in a detail-table or vice versa, position to the right head record when the record pointer position in detail data changes.

I don't know, what you actually want, you seem not wnat to have a dependency, but then are surprised a record pointer starts ar top and not in a related position.

The record position is not something that's stored in the DBF file, each workarea has a record pointer, and so no matter whether in two forms, same or different data sessions, in two different workareas the record pointer can and usually does point to different records, starting at top.

And on the other side, even when two forms bind to the same table in the same workarea of the same data session, what's displayed isn't always automatically updated.

I don't really know how to help you, as I don't have a good picture of the wanted behavior and the data session and workarea usage.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

And it comes back to my earlier question: What exactly are you trying to achieve?

Put another way, why is it so important to know where the record pointer is? If you don't know where it is, and if you want it to be at a particular record, why not just move it there explicitly?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Akourou]

what i want to achieve is the record pointer in booking table not to move each time i open the guest form.

Mike, i do not know what you mean by private data sessions but i do not open any tables from my forms.
I have a procedure that run at the start of my application that opens all tables, and they only close when i quit the app.

Olaf, the only relation that i find between the two tables is the combobox on booking form, bound with the guest table. That is why when i move to another booking record, the pointer of guest table moves to the specific guest. I understand this behavior and i want it. But not the opposite. I mean moving to other guest record, does not move the pointer of booking table because there is no relation. But when i close and i open again guest form, it moves the booking table pointer to top, and this is what i want to avoid.

 

[Olaf Doschke]

If you work that way - opening all tables and use them in all forms, then all forms have to work in the same datasession, none has a private datasession.

Then you'll always have that side effect when two controls are bound to the same workarea, the least thing to do is to open a table twice with the AGAIN clause and with another alias, so USE tableA AGAIN ALIAS tableA2 - because both opening a table twice without explicitly stating that with AGAIN and reusing the same alias name causes errors. Then bind one control to the tableA workarea and the other to tableA2.

It's not the most recommendable solution though, you typically only want to share the same data session when you actually want this side effect, when the side effect becomes the main effect, ie when moving in some list a satellite detail form also shows the details of that row.

The more controlled solution is to use private data sessions and let each form be its own world of record pointers. It allows reusing the same alias names, as the new datasession is - as the name says - private.

So as you don't seem to know the concept of data sessions, that's what you have to learn now. I even bet your forms do have tables in their DE and would (re)open them, as that also causes top position, which strongly smells like a table is opened with the form and therefore at its initial top position, you are not located on another unexpected record by picking it in a combo.

So in essence: Private data session means no such side effects. You don't want them, then don't allow them. You should also know the same alias problem, it's quite a famous example of two comboboxes bound to the same alias influencing each other, every control should have its own workarea, therefore the concept of OOP programming includes a three tier architecture, where you don't skip the middle tier. VFP is very tempting in skipping that, but when you adhere to it, you'd automatically never have a control bound directly to a table anyway, therefore also no such side effects.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

To see if your form has private data sessions, check the form's DataSession property. If it is set to 2, the form has a private data session. This means that the form has its own self-contained view of the open tables, work areas, record pointers, etc.

So if you open a table in such a form, it will be opening a separate instance of the table (separate from the table that you opened in your main program). That separate instance will have its own record pointer. And since, when you first open a table, its record pointer is normally set to 1, that would explain the behaviour you are seeing.

You say that you are not opening your tables in the form. You might not be opening them explicitly, by they might still be open behind the scenes. That could happen if you run a SELECT command (as per your original post). Or they could be opened in the Data Environment. To check that latter point, open the form in the Form Designer, right-click on the form, and select Data Environment. If you see the Data Environment window with one or more table in it, then those tables are being opened automatically when the form is launched. But that's not necessarily the case if you see the standard File Open dialogue at that point.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Akourou]

Mike,

thank you.

that was it!
i had forgotten tables in the data environment of the form.
I deleted these entries and now it works as it should.

thank you Mike and Olaf