Drop/Detach (get rid of!) a query

[Raymond Lyons]

Below is some code that creates a query. Works fine except for one thing: If the query is null or just as a test if the user says "Yes" to "Drop the Query?", I do not want the query to be there the next time the table is opened. In this case, if you run this and then go to tables in the Control Panel and open the table (or open any form based on the table) this query is not only still there, it is still applied to the browse or the form. How do I get rid of the $%#@! thing? I must be forgetting/missing something horribly simple.

dim tbl as p
dim qry as P
dim nrecs as N
tbl = table.open("funded")
'filter = query_filter_get("Arms change query")
query.filter = "Arm1st_Dt>Date()"
query.order = ""
query.options = ""
query.description = "Test query"
qry=tbl.query_create()

nrecs = qry.records_get()
IF (nrecs = 0) then

qry.drop()
ui_msg_box("Null Query","There are no records in the Query")
query.order = ""
query.filter = ""
END

end if
response = ui_msg_box("","Drop Query?",UI_YES_NO)

if UI_NO_SELECTED
end
else
tbl.index_primary_put()
qry.drop()
tbl.query_detach_all()
end if

tbl.close()

[Al Buchholz]

Re: Drop/Detach (get rid of!) a query

Ray

Check out the query.options documentation.

[Mike Wilson]

Re: Drop/Detach (get rid of!) a query

Raymond,

Your code always runs NO as the button pushed for your message box. Try this for the end part of your code. Then it will work for you.

qdrop_result=ui_msg_box("QDROP","DROP QUERY?",UI_YES_NO+ UI_FIRST_BUTTON_DEFAULT+ UI_INFORMATION_SYMBOL)
'Test to see which button on the message box was pressed....
SELECT
	CASE qdrop_result = UI_NO_SELECTED
		qdrop_NO_Button= .t.
		ui_msg_box("","NO SELECTED")
		end
	CASE qdrop_result = UI_YES_SELECTED
		qdrop_YES_Button= .t.
		ui_msg_box("","YES SELECTED")
		tbl.index_primary_put()
		qry.drop()
		tbl.query_detach_all()
END SELECT

tbl.close()

Mike W

[Mike Wilson]

Re: Drop/Detach (get rid of!) a query

Raymond,
I also think that the code leaves a session of the table open if there are no recs in the query, either send the script to the end just before the table.close() (red in below script) or add a tbl.close() before you end the script at that point.

dim tbl as p
dim qry as P
dim nrecs as N
tbl = table.open("funded")
'filter = query_filter_get("Arms change query")
query.filter = "Arm1st_Dt>Date()"
query.order = ""
query.options = ""
query.description = "Test query"
qry=tbl.query_create()

nrecs = qry.records_get() 
IF (nrecs = 0) then
    qry.drop() 
    ui_msg_box("Null Query","There are no records in the Query")
    query.order = ""
    query.filter = ""
    [COLOR="red"]GOTO ZERORECS[/COLOR]
[COLOR="Blue"]'END [/COLOR]
END IF

qdrop_result=ui_msg_box("QDROP","DROP QUERY?",UI_YES_NO+ UI_FIRST_BUTTON_DEFAULT+ UI_INFORMATION_SYMBOL)
'Test to see which button on the message box was pressed....
SELECT
	CASE qdrop_result = UI_NO_SELECTED
		qdrop_NO_Button= .t.
		ui_msg_box("","NO SELECTED")
		end
	CASE qdrop_result = UI_YES_SELECTED
		qdrop_YES_Button= .t.
		ui_msg_box("","YES SELECTED")
		tbl.index_primary_put()
		qry.drop()
		tbl.query_detach_all()
END SELECT
[COLOR="red"]ZERORECS:[/COLOR]
tbl.close()

I prefer using a5_get_records_in_query() method:

dim tbl as p
dim qry as P
dim nrecs as N

nrecs = a5_get_records_in_query("funded","Arm1st_Dt>Date()")
 IF nrecs=0 then
	ui_msg_box("Null Query","There are no records in the Query")
	end 
 ELSE
	tbl = table.open("funded")
	'filter = query_filter_get("Arms change query")
	query.filter = "Arm1st_Dt>Date()"
	query.order = ""
	query.options = ""
	query.description = "Test query"
	qry=tbl.query_create()

	qdrop_result=ui_msg_box("QDROP","DROP QUERY?",UI_YES_NO+ UI_FIRST_BUTTON_DEFAULT+ UI_INFORMATION_SYMBOL)
	'Test to see which button on the message box was pressed....
	SELECT
		CASE qdrop_result = UI_NO_SELECTED
			qdrop_NO_Button= .t.
			ui_msg_box("","NO SELECTED")
			end
		CASE qdrop_result = UI_YES_SELECTED
			qdrop_YES_Button= .t.
			ui_msg_box("","YES SELECTED")
			tbl.index_primary_put()
			qry.drop()
			tbl.query_detach_all()
	END SELECT
	tbl.close()
END IF

Mike W

[Raymond Lyons]

Re: Drop/Detach (get rid of!) a query

Your code always runs NO as the button pushed for your message box. Try this for the end part of your code. Then it will work for you.

Mike,

Good lord! In my haste in typing a test I left out "response = ".

However, that does not do it, which makes me wonder what qry.drop() query.detach() are for if they do not work in this situation. Hopefully someone can explain.

Al,

Thanks. Putting in the "N" flag as in qry=tbl.query_create("N") does the trick by not saving the query in the registry. I can probably just drop all the other stuff (have not tried that yet though)

Ray

[Raymond Lyons]

Re: Drop/Detach (get rid of!) a query

Thanks again Mike, but see my response above. The spurious END I would have caught and is due to my first test using table.current() where I wanted to leave the table open. In any case, it looks like the only thing that works as I want it to is the "N" flag Al pointed me to.

Ray

[Al Buchholz]

Re: Drop/Detach (get rid of!) a query

Ray

Glad that worked.

I noticed that you also had a line of code:

query.options=""

What I was thinking of is:

query.options="N"
qry=tbl.query_create()

Where the "N" is placed in the dot variable query.options

It's a slight difference that ties the two lines of code together. While you can write the code as:

qry=tbl.query_create("N")

my quess is that the "N" in the query_create will cause the dot variable query.options to be ignored.

Subtle, but might be an issue for someone.

[Raymond Lyons]

Re: Drop/Detach (get rid of!) a query

Ray....
What I was thinking of is:

query.options="N"
qry=tbl.query_create()

Where the "N" is placed in the dot variable query.options

It's a slight difference that ties the two lines of code together. While you can write the code as:

qry=tbl.query_create("N")

my quess is that the "N" in the query_create will cause the dot variable query.options to be ignored.

Subtle, but might be an issue for someone.

Al, I first tried query.options="N" but for whatever reason that did not work. When I put the "N" in qry=tbl.query_create("N"), it worked. Go figure!

Thanks again.

Ray

[Mike Wilson]

Re: Drop/Detach (get rid of!) a query

Raymond,

I got a sequence to detach a query reliably. See if this helps.

Mike W

[Raymond Lyons]

Re: Drop/Detach (get rid of!) a query

Mike,

Thanks. Your code:

'' Reset the query
topparent:tables:BIRTHDAYS.filter_expression = ""
topparent:tables:BIRTHDAYS.order_expression = "LASTNAME+FIRSTNAME"
topparent:tables:BIRTHDAYS.query()
' Get the table object from the form
tbl = topparent:tables:BIRTHDAYS.current()

certainly does what I need, as does Al's "N" flag, except unlike the "N" flag your code leaves the query there until the DB is reopened--which is OK for my purposes.

What I still do not understand is, since qry.drop() and/or query.detach() [or query.detach_all()] seem to do nothing in this context, what context are they for? I know somewhere in the past I have used them and they did what I wanted, but I do not have time to chase that down right now. Maybe someone can enlighten me (us)?

Thanks again.

Ray

[G Gabriel]

Re: Drop/Detach (get rid of!) a query

Ray:
I am a bit confused about your objective:

1-Do you wish to simply detach the query(s)?

I do not want the query to be there the next time the table is opened

or
2-Get rid of the $%#@! thing?

How do I get rid of the $%#@! thing?

3-or both?
Assuming the answer is both, let's start with the $%#@! thing: I am not sure what that thing is, but a safe bet it's the query temp files. This might help (check last entry in the thread)
http://msgboard.alphasoftware.com/al...ble#post395336

Detach all queries does work:
I used Mike's zip file and added one more button: "Detach query from Gorilla Glue". It works.

Mike:
How do you recon that the bottom button on your zip file detach the queries? see the image! Pretty nice otherwise!

[Mike Wilson]

Re: Drop/Detach (get rid of!) a query

Gabriel,

No Go on detaching from gorilla glue with your button.

1. Push top query button. Answer dialog NO, and the table browse will display the table filtered.
2. Close browse window. Push your new 'detach from Gorilla Glue' button and then push the OPEN Browse button. The table is still filtered. Your script isn't doing it.

And the whole thing:

'' Reset the query
   topparent:tables:BIRTHDAYS.filter_expression = ""
   topparent:tables:BIRTHDAYS.order_expression = "LASTNAME+FIRSTNAME"
   topparent:tables:BIRTHDAYS.query()
' Get the table object from the form
   tbl = topparent:tables:BIRTHDAYS.current()

Could really be replaced with:

'Turn off query in  in current form.
topparent.show_all()

Mike W

ps: Gabe, later, I'm going to post an example of the same scenerio that gave the error with your 'metals' example which has a child table level query filter =.t. and is responsible for generating an error.

[G Gabriel]

Re: Drop/Detach (get rid of!) a query

Mike:
First, calm yourself down then you will see things clear!

1. Push top query button. Answer dialog NO, and the table browse will display the table filtered.
2. Close browse window. Push your new 'detach from Gorilla Glue' button and then push the OPEN Browse button. The table is still filtered. Your script isn't doing it.

What does that have to do with detaching the queries?!

You say:

The table is still filtered

OFFCOURSE IT WILL HAVE THE SAME FILTER IT HAD BEFORE, until you change that to a different index/query or show all! You must not be quite clear on what query detach is supposed to do and must be confusing it with changing the index/query to show all. That is not what it is supposed to do, so you need to take a deep breath and try to understand. I thought the image I attached would explain what I meant, evidently you did not see what I mean, so let me spell it out:

a-query_detach_all DETACHS the query(s) from the table.
b-To verify whether or not the query(s) was detached, and this is the part that you are missing, press alpha's "Index" button from your tollbar, that will show you all indices/queries attached to the table. That is the image I sent. It was taken after pressing the bottom button and as you can see from the image, the query was still there.

Now, do whatever you want to do, then press the button I added, then press the index button, now you will see that the only thing there is recno. Any and every other query(s) you might have used, is gone. Get it?

BTW, I am not sure what is the problem with metals! I use it, I like it, never had any problem with, evidently Peter Wayne does too!

[Raymond Lyons]

Re: Drop/Detach (get rid of!) a query

Mike:
First, calm yourself down then you will see things clear! ...

Gabe,

Maybe you are the one who needs to calm down? As far as I am concerned your button is totally ineffective if it is supposed to do what I originally made pretty clear what I wanted--which is that when you close the form and next open the table the query is not applied, whether in another form or in the default browse from the Control Panel. I really did not care if it hung around until re-opening the DB, I just did not want it re-invoked unless the user chose to do so. Better yet, as the the "N" flag does, get rid of it entirely, right now, end of story.

When you press the index button on the toolbar the query indeed does not show up, which presumably means that if you re-open the Db it won't still be hanging around. But even if you Show All, the test query will still be there. Worse yet for what I wanted, it will be applied to whatever you open.

Ray

Edit: "which presumably means that if you re-open the Db it won't still be hanging around..." Actually, I am not at all sure that is what detach_all() is supposed to mean. I am no longer clear about what it is supposed to accomplish--or .drop() either.

[G Gabriel]

Re: Drop/Detach (get rid of!) a query

This might help clarify things a bit better:
The docs suggest that the most number of queries you could use on a row is 16, or something like that. That myth was busted. Read my past thread: "Me and you and a dog named Lou".

Alpha's solution to resolve this problem, i.e. if you anticipate getting stuck by using the allotted slots of queries, is to detach the queries so you would start from a clean slate, hence, queries_detach_all().

I used it as a matter of routine when running repeated queries, since in the past, if I did not, the system will hang at some point after running several queries.

With more recent builds of alpha, I came to realize that you do not even need it. Have not have an occasion where the system hang. But, it is not a bad idea to use it if you are running many complex queries on a row. It DOES not, clear the filter to show all. That's a horse of a different color!