Query options??

[peterg000]

Maybe someone can shed some light on queries and the options used to set them. I have a script that steps through a table that has had a query set for it ....

query. filter = "filter"
query.order - "order"
(I set no options)

each time the script steps through, it does a Lookup() to get addition info (from same table) ... when it cycles through the seventh record, the query is dropped (used debug to see this). I then explicitly set the query.options to "X" and that seemed to solve problem, but to be quite honest, i don't really understand what the options are doing. Since the query just gets dropped and there is no error that comes up, I'm worried this could be happening in other scripts ..... any help understanding this would be much appreciated.

Thanks in advance ....

Glenn

[Clunes]

Re: Query options??

I had a similar problem in stepping through a set of records, in that the process simply gave me an error when it got a certain record every time. It baffled me until I used <qry>.drop() after each step in the process. I used this in frustration, but it worked. I think it is that queries get stored some how and you end up with an overflow unless you clear them at some point before doing it again

In a past post someone recommended resetting by using query.options = "" and query.order = "" before doing query.filter

[MikeC]

Re: Query options??

Good advice Mark as the options, order, and filter in cases that are used like this persist and therefore could mess up other queries using them later on....so most will set them to "" right before using them to make certain.

[Tom Cone Jr]

Re: Query options??

Check the help file discussion of the "options" property of the "query" object. You'll find it in the discussion of <tbl>.query_create(). There are a finite number of "current" queries that can be tracked (persist) by Alpha for any given table. Using 'T' as the query.options setting tells Alpha that the current query does not need to persist so query slots for that table are not consumed.

[peterg000]

Re: Query options??

Thanks guys ....

Tom ... read through help a number of times. Was looking for real life experiences (sometimes a bit different from documentation).

Ok .... so how about I state what I think and then someone can straighten me out ...

- lookup() seems to use up query slots

- All queries whether using and index to create or no index(record order) use a query slot and is support by an .mpx file.

- Options only help define how the query s built ... whether using an index or not (???)

- There are only 16 slots available (???)

With the above understanding ....
I set a query at the begining of my loop(thats 1 slot) and then I would step through 100 records .... each time executing a lookup() to the same table ( 1 slot each time) ..... I only get through 7 records before the original query is dropped(no message) ..... so Im counting 8 at this point. Doc says there is a total of 16 available ... cant figure out where the other 8 go????? Question is .... why when using the 'X' option, all is good .... shouldnt matter what I use to build query, slots still get used .... right???? So should I be thinking, when index is used to build query, a slot is not used??? This is where I start getting confused.

Thanks again ...


Glenn

[Tbaker]

Re: Query options??

Glenn

Have you tried the "T" option as Tom Cone has suggested?


Tom Baker

[peterg000]

Re: Query options??

Tom .... didnt try the 'T' because I do want the query to persist until I have run through all my records in my loop. Maybe Im missing something?? Doesnt the 'T' option state that if the query slot has to be bumped and used by a more current query, it can? My original query was getting dropped during my loop (until I used the 'x' option) through a table and I think its getting dropped because of the lookups() that are getting executed and using up slots. I still have the question of why using 'x' options works ... doesnt it still use a slot?

Glenn

[Tbaker]

Re: Query options??

Glenn

When using the "X" option, you are telling the query to use an available index, one that has the same definition as the query, EVEN IF A QUERY Exists.

I would say that using the "X" option, there are no additional slots being used for the query and therefore you can not exceed the 16 slot maximim.

Could you post a sample of the script that you are using to do this. I am a little confused with the "when cycling through the records a lookup() is used to get information". Are you using one of the Lookup() functions will which not take up a query slot?

Tom Baker

[peterg000]

Re: Query options??

Here you go .... stripped out a bunch of code, but you should get the jest.

By the way, with the 'X' option in place, after creating the query, I checked its type with query.type_get() ... still says '6' which would indicate a query slot used (wouldnt it??) and I cant get the flags(options) to list of with the query.flags_get() or the mru_query().

' =====> Open Unit allocation table 
tblb = table.open("tsm_unitalloc")
qfilter = "(ua_property_name = var->vg_current_property_name) .and. Ua_Maint_Week <> .t."
if unitselect = "range"
	qfilter = qfilter+" .and. (val(unit) >= val(var->unitstart) .and. val(unit) <= val(var->unitend))" 
end if
if weekselect = "range"
	qfilter = qfilter+" .and. (val(week) >= val(var->weekstart) .and. val(week) <= val(var->weekend))"
end if

query.filter = qfilter
' ---- with no option or option "T" ... doesnt work
' ---- with option 'x' .... it works
query.options = "X"
query.order = "unitweek"

qryb = tblb.query_create()

-------
---
--- stripped out code
---
-------

' =====> Start looping thru Unit Weeks
		tblb.fetch_first()
		while tblb.fetch_eof() = .f.
			'
			continue = .t.
			'
			' =====> check active flags for owners & unitweeks if selection criteria selected
			if vl_oflag = .t.
				oactive =lookup("tsm_owner","O_property_name = "+quote(var->vg_current_property_name)+" .and. "+"Owner = "+quote(tblb.Owner),"O_active_flag")
				if oactive = .f. then
				    goto next_unitweek
				end if
			end if
			'
			if vl_uflag = .t.
				uactive = lookup("tsm_unitalloc","Ua_property_name = "+quote(var->vg_current_property_name)+" .and. "+"Unitweek = "+quote(tblb.Unitweek),"Ua_active_flag")
				if uactive = .f. then
				    goto next_unitweek
				end if
			end if
			'
			--------
			----
			----   stripped out code
			----
			--------
			'	
			next_unitweek:
			tblb.fetch_next()
		end while

[Tbaker]

Re: Query options??

Glenn

I looked at your code. As far as I can see in your code, you are calling the query ONCE -

You then open the queried table and begin to fetch through the records.

As far as I know, the lookup() function does not use a query slot but it does open the queried table every time that the lookup() function is called and closes it each time.

I then created a small database file (my testing file called "Will work") along with another table to be used for the lookup. Will Work has 43 records in the table of which 39 will be used by the query.

I created two buttons of the form (the only form in the app). The first button creates a query using the "T" option and then uses the records in the query to fetch through and create a CRLF() listing of the rates in the job table that were selected by the query in will work and then reports those to you . It works fine.

The Second button creates a query using the "X" option. There is an index in the table that matches the query criteria (Occupied=.t.) . Next the same as above, fetch through the records (39) and created a CRLF() listing of the rates in the job table that were selected by the query in will work - also gives a report of those to you. It also works fine (a little faster than the first button but then again it is based on an index)

You could try using code to get the number of records in the query, which I use in both of the buttons code. This would tell you that the query is reporting the right number of records.

The only other thing that I could suggest is making sure that your filters are correct since you are using conditionals in the making the query filter. Or the conditional that you are using in the fetching, is that filter correct.

I can't find anything in your code that would cause the query to be dropped (unless my old eyes missed something).

Look at the zip file attached - see if it close to what you have done (the filters are very simple). My filters are simlple with no conditions - that is what makes me suggest to look at your filters in debug to see if they are correct.

Sorry I could not help more in this -

Tom Baker

[peterg000]

Re: Query options??

Tom,
Thanks for putting some time in on this, I will try to put together small mock with my tables and see If I can pass over so you can take a look. When I run my script through debug, the query gets dropped at the point the lookup() is executed (on the eight cycle through loop).

a couple of bugs here that I see on yours and mine .....

mru_query is not reporting back query.options .... right?

and on mine, when using option 'X', it still shows a type of '6' even though there is definitely an index that can be used.

stay tuned .... :)

Glenn

[Tbaker]

Re: Query options??

Glen

Just as information, did a little digging and found that if you create a pointer to MRU_query and then run that pointer in the interactive - it only shows

Filter
Order
Flags
Value

No options.

It has been the same since Version 7.

I guess very few use MRU_query.

Tom Baker

[martinwcole]

Re: Query options??

because query.filter/order/options is a system global dot variable, the values will persist

say an hour ago, in some button you said
query.filter="name='smith'"
query.order="lastname"
query.options="U"

and now at a different button you say
query.filter="state='tx'"
query.order="lastname"

the options are still "U" - and can obviously mess up your query

several years ago I wrote a function that I always use before evey query:

FUNCTION QClear AS L ( ) 'V didn't exist then
 query.filter=""
 query.options=""
 query.order=""
END FUNCTION

and now at a different button you say
qclear()
query.filter="state='tx'"
query.order="lastname"

this time it will work as expected

a very good habit for using queries in loops (or anywhere for that matter) is to ALWAYS use drop()

like

[COLOR=red]qclear()[/COLOR]
query.filter="state='tx' "
query.order="lastname"
t1=table.open("customers")
idx1=t1.query_create()
t2=table.open("transactions")
 
dim shared cid as c
[COLOR=red]qclear()[/COLOR]
t1.fetch_first()
while .not. t1.fetch_eof()
     cid=t1.custid
     query.filter="custid=var->cid"
     query.order="tdate"
     idx2=t2.query_create()
     t2.fetch_first()
     while .not. t2.fetch_eof()
          'do something
          t2.fetch_next()
     end while
    [COLOR=red] idx2.drop()[/COLOR]
     t1.fetch_next()
end while
[COLOR=red]idx1.drop()[/COLOR]
t1.close()
t2.close()

[peterg000]

Re: Query options??

All good stuff martin .... thanks for the input.

Glenn

[peterg000]

Re: Query options??

Tom ..... Im posting a really cut down version of the form and script that is involved.

unzip
open database
run form GenCharges
press 1. Generate - Option T
This will come back with 0 transactions
press 1a. Generate - Option X
This will come back with 41 transactions
Each one executes one of 2 scripts ... gencharges & gencharges_xoption

The Table in question it tsm_unitalloc ....

If you debug the script gencharges while running the first button .... when cycling through the loop, make a watch variable for the query .... you will see it drop when the script loops through about 8 times. Hope you can see something I cant.

Thanks again!!

Glenn