Browse closing Cursor 4

[David Higgs]

In the following code..

	SQLExec(WABCONN,'SELECT * FROM &TBL_WAB_Books WHERE callsign LIKE ?CALL_TO_SEARCH and admininfo NOT LIKE "(NLV-R)" ORDER BY B_Number','cur_book_search')

	BROWSE Font 'Verdana',12 NOEDIT 

if used('cur_book_search')
	USE IN cur_book_search	&& Close Cursor 
	MESSAGEBOX ("Cursor now Closed")
	ELSE
	MESSAGEBOX ("File not Open")
endif

I get a "File not Open" when I use it in my application. When I use the same Code in My Development Program I get "Cursor now Closed". The only difference between the two programs is that the code in my application is in a "Command Button" and My Development was carried out in a PRG File.

My question is, why after a "Browse" would the Cursor Close in my application? If I remove the BROWSE Statement I get the "Cursor now Closed"



Regards,

David.

Recreational user of VFP.

 

[Mike Lewis]

David, if your aim is simply to close the cursor (regardless of whether or not it was already open), then you can replace the entire block of code with:

[tt]USE IN SELECT('cur_book_search')[/tt]

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

But if your aim is to understand why a command button is behaving differently from a PRG, then that would depend on what else the command button is doing. The command button is presumably a member of a form, and the form is presumably called from another form or a PRG. Is there any code anywhere in that command chain that could explain the difference in behaviour?

If your aim is simply to test whether the SQLEXEC() worked, you can do that by testing its returned value. A negative value indicates an error, which would result in no cursor being created. But if a cursor with that name already existed, it would continue to exist after an erroneous SQLEXEC().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Hello Mike,

Nice to hear from you. Thank you for your single line code which I have used within my application.

But if your aim is to understand why a command button is behaving differently from a PRG, then that would depend on what else the command button is doing.

That's basically it, just interested to know as to why the command button (if indeed that is a factor) is behaving differently from the PRG.

It maybe something to do with the BROWSE command as the Cursor remains active when the BROWSE Command is removed. I have tried using a different Cursor name, that made no difference. I'll have to try using a Grid to see if that makes a difference.

No big issue, as either way the Cursor is closed.

Regards,

David.

Recreational user of VFP.

 

[mjcmkrsr]

Hi,

You might want to select the right workarea before browsing and check if there is a difference

SQLExec(WABCONN,'SELECT * FROM &TBL_WAB_Books WHERE callsign LIKE ?CALL_TO_SEARCH and admininfo NOT LIKE "(NLV-R)" ORDER BY B_Number','cur_book_search')

[highlight #FCE94F]SELECT CUR_BOOK_SEARCH [/highlight]

BROWSE Font 'Verdana',12 NOEDIT 

if used('cur_book_search')
	USE IN cur_book_search	&& Close Cursor 
	MESSAGEBOX ("Cursor now Closed")
	ELSE
	MESSAGEBOX ("File not Open")
endif

hth
Mark

 

[Mike Lewis]

Mark has a point. If SQLEXEC() failed to return a cursor, the previously-selected cursor will still be selected. That won't directly explain the behaviour you are seeing, but it could be something to keep in mind.

As for why the command button behaves differently from the PRG, my point was that a command button doesn't exist in isolation. It must be part of a containership hierarchy (and perhaps of a class hierarchy as well), and might therefore be affected by a lot of code that doesn't affect the PRG (variables in scope, SET commands, search path, connection strings,etc.).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Where is which code? You might think it doesn't matter, but if you call a PRG or a procedure of main.prg out of a form with private datasession, the code in main.prg does switch to the initial datasession id 1.

All code working on some workarea must be in the same datasession object, thus don't work with functions/procedures. Make them methods of your form or a class you can put on any form, topic specific, like here an sqlhandler.

To have them available in any form make them base form methods and inherit them or put things you want to be able to ggenerally use as object of your base form. In case it is about datasession/data access it better be part of the form class inheritance tree than a subobject of goApp, as goApp will also very likely live in datasesion id1, being no form and being instanciated from main.prg

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

Thank you all for your replies.

After further investigation I found that I had two Cursors with the same name. Once I renamed one of the Cursors all appears to be working as expected.



Regards,

David.

Recreational user of VFP.

 

[Koen Piller]

Hi,
That is funny, two cursors with the same name????
I suppose you mean two tables since when you create a cursor with a name and try to create an other one with the same name, the first will be overwritten and you will result with only one.
Koen

 

[David Higgs]

That is funny, two cursors with the same name????
I suppose you mean two tables since when you create a cursor with a name and try to create an other one with the same name, the first will be overwritten and you will result with only one.

No, I did have two Cursors with the same name, looking at the same MySQL Database. One cursor was in my "Open Files" PRG and the other was in a Command Button on my "Main Form". When I open either of the two cursors I gather the relevant information and then close the Cursor. When I changed the name of one the cursors the Error: ( 'Cur_Book_Search' is not found ) no longer occurred and when I changed the name back the problem came back.

The reason why the duplication came about was that I originally transferred my Data Files from DBF to a MySQL File and used to open the MySQL File and copy the contents to a Cursor to replicate an open DBF File. I was in the process of changing my routines so that I only queried the MySQL Table as and when required, rather than having a cursor open with the full contents of the MySQL File waiting to be queried. Now that I have achieved that the "Open Files" Cursor is no longer required and so the problem no longer exists.

I still don't know why the issue of two Cursors with the same name would have caused the issue, but it did!!

Regards,

David.

Recreational user of VFP.

 

[Mike Lewis]

The only way you can have two cursors with the same name (open at the same time) is if they exist in different data sessions. That might be the case if one of them was created in code in a command button, since the command button presumably lives on a form, and a form might well have a private data session.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Using SQLEXEC with the same cursor name is closing the previous one and puts the data of that query into the new cursor with the same old name. But you can close only one cursor now, as you haven'T done two.

You should realize that the last part of the code closing the workarea only runs after you close the browse window. The BROWSE is just like doing a modal form, code execution stops, the button click still is running while you brows. If you click on two buttons creating browse windows you'd really come into a quiurks mode, because using the same alias name will mean you force the browse window to close, as its cursor vanishes before it is recreated. Your sqlexec is not refreshing the content of the already exisitng cursor and browse winodw, you close and create a new one. The code closing the cursor then runs and doesn't find it, as the SQLEXEC already closed it.

Just the smae when you do a SELECT...INTO CURSOR with an already exisintg alias name. It doesn't error, but it closes the cursor and creates a new one.

Just like the same when you USE any dbf not in an empty workare, but in an already used workarea, you close the previously used dbf and open the new one, USE is always a combination of close AND open, just USE means you don't open something new, so it only closes whatever is open in the current workarea.

You can do like Mike said only USE when the alias name is found, first checking used like you do or simpler USE IN SELECT('aliasname') because of the special nature of SELECT('unusedalias'): It returns 0, 0 is always referring to any empty workarea, so you actually close an empty workaresa, which does nothing.

But you also should care about using specific names, because you want to have data open in parallel, typically.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

What remains for you I guess is the question how can USED('cur_book_search') even ever be false because right beforehand you selected that wrokarea and browsed it.

Yes, but Browse causes a modal state for this code, the part after browse only runs after you close the browse window, it's like DO FORM of a modal form. With a twist though, because a modal form hinders to use anything else but you still can click the other button, while a browse window is open.

Closing the browse window does not close the workarea it displays. But when you click another button also recreating the same workarea alias name, that causes the workarea to be closed, the browse window then is indirectly caused to close and the code after it goes into the else branch, so clicking the second button causes the first button click code to finish in the state the workarea is closed. That already happens before SQLEXEC even finishes. Afterward, a new cursor with the same name is created and you browse it.

Visually this all just appears to be a refresh of the already open browse window. But do a messagebox("sqlexec cursor (re)created") right after SQLEXEC (restored to reuse the same alias name) and clicking on the second button while the browse window of the first is still open you'll see the browse window close, the messagebox saying "file not open" and then the messagebox after the sqlexec.

Maybe also get a few things straight. USED() is not checking the file open state, it's checking the workarea occupied state. It's intentional USE of some.dbf causes a workarea to also have the same aliasname as the file name is (as long as that works), but still the workarea name isn't the file name, it's also not just the file name without DBF extension, its the workarea name (And used could also check a workarea number). Workareas always maintain a DBF, but that doesn't make them the DBF itself, it's the maintenance and access object of a DBF. workareaname.fieldname is the most used way of access to the dbf file fields, but DBF() actually is giving the specific dbf file maintained by the workarea, USED() gives the usage/occupation status, CURSORGETPROP() give more properties of this workarea object. It's not all just available with the normal object.propertyname syntax of any other objects, so only field names are addressable this way and the cursor or workarea properties don't overlap or get in conflict with any field names. Famous problem case when variables have the same name as fields, but also a wanted thing when using SCATTER the legacy way without its modern NAME clause creating a real separate record object.

Indeed cursor and workarea are synonyms, much more would be clearer, if the INTO clause of SQL would be called INTO WORKAREA aliasname.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Hi David,
I believe Mike has hit the nail. Two cursors with the same name can only exist if you have two datasessions.
In the same datasession it is impossible to have two cursors with identical name.
Please open your Datasession window and investigate.
( Datasession window: VFP main menu -> Window -> Datasession ) The window has a combobox with all the available datasessions.
Please bear in mind you will not see the actual name of your cursor, you will see the alias. The cursor has a name random unique name created by VFP like e.g. BD2EEA088.TMP and not myCursor.dbf There will be no file myCursor.dbf on your HD.

Now the question is, where why did you change datasession.
Regards,
Koen

 

[David Higgs]

Thank you to all that replied, much appreciated.

Olaf,

Thank you for your comprehensive replies, there is a lot to take in, so I will take my time reading and learning from your advice.

Now the question is, where why did you change datasession.

The reason why the duplication came about was that my application was using VFP DBF Files. As I wanted to access my Datafiles from two different PC’s on my Home Network I migrated the Data Files to MySQL on my Home Server. To make the change with a minimum of fuss I used to open the MySQL Files and copy the Data Files into VFP Cursors to replicate open DBF Files. Hence the application needed a minimum of alteration to the code.

I started to look into ways of making more efficient use of my Data Files by only querying the MySQL Table as and when required, rather than having a cursor open with the full contents of the MySQL Data File waiting to be queried. In the course of doing so, I inadvertently named two Cursors with the same name. Now that I have identified the issue, I have removed the original Cursor and all is well.

Please open your Datasession window and investigate.
( Datasession window: VFP main menu -> Window -> Datasession ) The window has a combobox with all the available datasessions.

I will follow your advice and take a look at the Datasession Window.


With the help of Tek-Tips Members I have now got a better understanding of what went wrong and why it went wrong. I will follow all advice given and hopefully take it all in and improve my VFP/MySQL knowledge.




Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

David,

you don't seem to read anymore, can't blame you, you have your solution.

I can recap what I said in whort and demo it: What I observe when I put two buttons on a form that both have your code, is that SQLEXEC reusing the same cursor name closes an already existing cursor. So clicking the second button after the first click still waits for the BROWSE to close triggers the cursor and the BROWSE window to close and the code in the click of the first button now finishes and branches into the ELSE message box, that happens before SQLEXEC of the second button even finishes recreating the cursor with the other query returning other data.

I know your situation differs with a button click and PRG. Doesn't matter, though. The point is this is the same thing as we already know in many other ways if you query reusing a cursor name, that cursor is closed, grids bound to it go blank and browse windows close.

If you're interested I have attached said demo form that runs with some prerequisites (MySQL Server and ODBC driver etc.) that also runs smoothly without using two alias names. Look into the forms Load() event to adjust making the database connection and look into the buttons click events.

The way I programmed them without your aftermath code simply makes use of the automatic effects of SQLEXEC and it also starts the BROWSE with NOWAIT option, so you don't have that unfortunate state the click of a button doesn't finish until you close the BROWSE window or the BROWSE windows is triggered to close. A click event finishes and leaves an open BROWSE window repositioned +(50,50) from the form position.

You can click on a button, click on the other button and have no error, no message, just the other data in the newly created BROWSE, you can add further such buttons. At any time you only use one workarea.

If you want to see your described behavior, just put in your click code. Of course, you use BROWSE without NOWAIT, because you want your aftermath tidy up code only to run after the BROWSE window is closed, but that way you cause the quirky effect the buttons code waits for BROWSE to close, and that does not only happen, when you manually close it, also when you click your button and it recreates the same cursor, because in the first step it's closed.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

This overlapped with your answer. Anyway, you can still use my attachment to use its simpler way of providing a form with multiple query buttons that may make MySQL administration simpler to you.

You'll want to look into the Load event first and likely need to adapt the driver used, maybe connect to the database you want and add your password, so the login dialog doesn't come up at all.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

you don't seem to read anymore, can't blame you, you have your solution.

No, I always like to read your input, it is very welcome. My problem is it takes a while to read the information and for it to sink into my aged memory! In any case I always return here and look at my previous postings and replies.

Although I have found a solution I will still continue reading the replies.

If you're interested I have attached said demo form that runs with some prerequisites (MySQL Server and ODBC driver etc.) that also runs smoothly without using two alias names. Look into the forms Load() event to adjust making the database connection and look into the buttons click events.

Thank you for your further simplified update and your "demo" file. I am pleased you could create the problem and explain why it happens. I will endeavour to take a look and take on board previous comments.

Regards,

David.

Recreational user of VFP.