Problem with queries

[Graham Wickens]

Although I can use queries quite happily, I seem to get to a "Tide-mark" on how many values I can query in any one query.

I can have anything from one value to find, to sometimes over 1000 values to find, My Table "Logging" has 516,000 records.
I can use query filters like (reg="G-XWBB".or.reg="HB-JCT") for a smallish number of search values, but beyond a certain point the filter string is truncated by A5 and the query fails. I then tried reading the values into a table and access the results in a Set (the entered values as master and "Logging" as child), again this worked of a fashion, but becomes very slow and cumbersome as the total number of records grows.

Is there a way of build filters that will cater for any amount of "Reg=".....".or." ?

thanks

[Ted Giles]

The query string truncation is a nuisance, but I suspect it is necessary within Xbasic.
Can you explain a bit more about the volume please?
If you're just looking for Reg numbers - and say you want a list of 1000, then you may need to sub-caregorise the reg numbers into groups, or use something different like fuel type, airfield, seats etc.
If you really need 1000 then you could separate into blocks and upload to a transaction table for processing.

What do you intend to do with the list of 1000 records?

[madtowng]

Hi Graham.

How are you generating the list of 1000 records (do you have that many columns you are checking) ?

[MoGrace]

In your post yesterday you showed us an image with a radio button that had 21 selections. Are these referring to indexed fields in the table? If they are, a drop down list might work better as you can set up the variable with display|value so what the user selects returns the field to sort by and then another text box (or two if a range is needed in a conditional object) can be displayed for the user to enter the search criteria. That should help the query you build be more concise based on just those variables using a select statement to build the filter for the query.

[Graham Wickens]

in this particular instance I am logging when seen, where seen and seen as against a particular airframe.
Im trying to make it so I have the least amount of typing to do.
the input file is a straight text file.

delete MyCollection
dim shared vLogFileName as c = filename_decode("[logPath]\*.log")
dim shared vLogText     as c = ""
dim shared vFilter 	as c = ""
vResult = ui_dlg_box("Log Entry File Name",<<%dlg%
{Background=#204+153+255}
{Font=Trebuchet ms,8}
{region}
[%p=ui_get_file("Input Log Filename","Log(*.Log)|Text (*.txt)",vLogFileName,"X");I=$a5_smart_file%.80vLogFilename];
{endregion};
{line=1,0};
{region}
<OK!OK> <Cancel!CANCEL>
{endregion};
%dlg%,<<%code%
if .not. (a_dlg_button = "Cancel" .or. a_dlg_button = "OK" ) then
	a_dlg_button = ""
end if
%code%)
if vResult = "OK" then
	fp = file.open(vLogFileName,FILE_RW_SHARED)
	WHILE .not. fp.eof()
		text = fp.read_line()
		vCountry = ""
		IF left(text,1)="#" THEN
			Seendate  = substr(text,2,10)
			LocateNo  = substr(text,13,2)
			SeenWhere = substr(text,16)
		else
			if ("," $ text) then
				vCountry = ut(substr(rtrim(text),at(",",text)+1))
				text = left(text,at(",",text)-1)
			end if
			if ("/" $ text) then
				vReg = word(text,1,"/",1)
				vCode = word(text,2,"/")
			else
				vReg = text
				vCode = ""
			end if
			vLogText = vLogText+ut(vReg)-"|"-vCode-"|"-vCountry-"|"-Seendate-"|"-LocateNo-"|"-ut(SeenWhere)+crlf()
		END IF
	END WHILE
	fp.close()
	dim global MyCollection as U
	rec_count = *count(vLogText)
	dim MyFilter[rec_count] as p
	MyFilter.initialize_properties("REG|CODE|COUNTRY|SEENDATE|LOCATENO|SEENWHERE",vLogText)
	myCollection.initialize("1|2|3|4|5|6","1","1|2|3|4|5|6",vLogText)
	for x = 1 to rec_count
		vFilter = vFilter + "reg='"-rtrim(MyFilter[x].reg-"'.or.")
	next x	
	vFilter = left(vFilter,len(vFilter)-4)
	vOrder = "REG"
	DIM Shared varP_logging as P
	DIM layout_name as c 
	layout_name = "logging_Detail_log"
	DIM tempP as p
	tempP=obj(":"+object_Name_normalize(word(layout_name,1,"@")))
	if is_object(tempP) then
		if tempP.class() = "form" .or. tempP.class() = "browse" then
			tempP.activate()
		else
			varP_logging = :Browse.viewqueried(layout_name,vFilter,vOrder)
		end if
	else
		varP_logging = :Browse.viewqueried(layout_name,vFilter,vOrder)
	end if
end if

the input data format is:

#12/08/1986|01|South Cerney
06 blue,RUSSIANFEDER
07 black,RUSSIANFEDER
08 red,RUSSIANFEDER
200/XC,FRANCE
WZ755/BGA3481,UK
#04/10/2019|01|{Paganhill}
C-GBKF
D-EEFV
D-EFJN
D-EHDW
D-EHLE

using a global collection so I can pass the relevant when,where,as to the correct record in the Viewd Browse

this has the following code on the onRowdblclick for the loaded form

dim global MyCollection as U
t = table.current()
mylog = MyCollection_get(rtrim(t.reg))
if t.as = "" then
	t.change_begin()
	t.H      = word(myLog,4,"|")
        t,code = word(myLog,2,"|")
	t.Where  = word(MyLog,5,"|")
	t.When   = word(myLog,3,"|")
	t.as     = rtrim(t.Reg)
	t.change_end(.t.)
else
	reply = ui_msg_box("","Confirm Removal of Logging Data for "+this.reg,UI_QUESTION_SYMBOL+UI_YES_NO)
	if reply = UI_YES_SELECTED then
		t.change_begin()
		t.H = ""
		t.where = ""
		t.when = ""
		t.as = ""
		t.change_end(.t.)
	end if
end if
topparent.refresh_layout()

[Ted Giles]

I think I get it -maybe.
Have you thought about using a cascading Tree ( Robin is good at these ), so you can click through the items, each of which will show a drop down list.
You should be able to get all the info if it is available, through mouse clicks only.

[MoGrace]

Actually if using a collection, Tim Kiebert might be the one to ask.

[Graham Wickens]

I calculated that I could have a maximum of 64 iterations in my query.filter so my next attempt is thus:

delete MyCollection
dim shared vLogFileName as c = filename_decode("[logPath]\*.log")
dim shared vLogText     as c = ""
dim shared vFilter 		as c = ""
vResult = ui_dlg_box("Log Entry File Name",<<%dlg%
{Background=#204+153+255}
{Font=Trebuchet ms,8}
{region}
[%p=ui_get_file("Input Log Filename","Log(*.Log)|Text (*.txt)",vLogFileName,"X");I=$a5_smart_file%.80vLogFilename];
{endregion};
{line=1,0};
{region}
<OK!OK> <Cancel!CANCEL>
{endregion};
%dlg%,<<%code%
if .not. (a_dlg_button = "Cancel" .or. a_dlg_button = "OK" ) then
	a_dlg_button = ""
end if
%code%)
if vResult = "OK" then
	fp = file.open(vLogFileName,FILE_RW_SHARED)
	WHILE .not. fp.eof()
		text = fp.read_line()
		vCountry = ""
		IF left(text,1)="#" THEN
			Seendate  = substr(text,2,10)
			LocateNo  = substr(text,13,2)
			SeenWhere = substr(text,16)
		else
			if ("," $ text) then
				vCountry = ut(substr(rtrim(text),at(",",text)+1))
				text = left(text,at(",",text)-1)
			end if
			if ("/" $ text) then
				vReg = word(text,1,"/",1)
				vCode = word(text,2,"/")
			else
				vReg = text
				vCode = ""
			end if
			vLogText = vLogText+ut(vReg)-"|"-vCode-"|"-vCountry-"|"-Seendate-"|"-LocateNo-"|"-ut(SeenWhere)+crlf()
		END IF
	END WHILE
	fp.close()
	dim global MyCollection as U
	myCollection.initialize("1|2|3|4|5","1","1|2|3|4|5|6",vLogText)
	dim entry_count as n = *count(vLogText)
	dim vLogData[entry_count] as p
	vLogData.initialize_properties("reg|Code|Country|SeenDate|SeenOrder|SeenWhere",vLogText)
	dim vFilterTxt[0] as c
	dim index as n
	for block = 0 to int(entry_count/64 + 1)
		vFilter = ""
		for iteration = 1 to 64
			index = block * 64 + iteration
			if index > entry_count then
				exit for
			end if
			vFilter = vFilter + "reg='"-vLogData[index].reg-"'.or."
		next iteration
		vFilterTxt[]=left(vFilter,len(vFilter)-4)
	next Block
	vOrder = "REG"
	for iterate = 1 to Block
		DIM Shared varP_logging as P
		layout_name = "logging_Detail_log"
		DIM tempP as p
		tempP=obj(":"+object_Name_normalize(word(layout_name,1,"@")))
		if is_object(tempP) then
			if tempP.class() = "form" .or. tempP.class() = "browse" then
				tempP.activate()
			else
				varP_logging = :Form.viewqueried(layout_name,vFilterTxt[iterate],vOrder,"dialog")
			end if
		else
			varP_logging = :Form.viewqueried(layout_name,vFilterTxt[iterate],vOrder,"dialog")
		end if
		if is_object(varP_logging) then
			varP_Logging.close()
		end if
	next iterate
end if
end

when I try and iterate the different filter statements/form it only allows me to display the first filter/form then ends.
what do I need to do to make the script loop through the filtered forms?

[Graham Wickens]

I have been teaching myself how to use arrays and collections, so tried this problem using arrays.

delete MyCollection
dim shared vLogFileName as c = filename_decode("[logPath]\*.log")
dim shared vLogText     as c = ""
dim shared vFilter 		as c = ""
vResult = ui_dlg_box("Log Entry File Name",<<%dlg%
{Background=#204+153+255}
{Font=Trebuchet ms,8}
{region}
[%p=ui_get_file("Input Log Filename","Log(*.Log)|Text (*.txt)",vLogFileName,"X");I=$a5_smart_file%.80vLogFilename];
{endregion};
{line=1,0};
{region}
<OK!OK> <Cancel!CANCEL>
{endregion};
%dlg%,<<%code%
if .not. (a_dlg_button = "Cancel" .or. a_dlg_button = "OK" ) then
	a_dlg_button = ""
end if
%code%)
if vResult = "OK" then
	fp = file.open(vLogFileName,FILE_RW_SHARED)
	WHILE .not. fp.eof()
		text = fp.read_line()
		vCountry = ""
		IF left(text,1)="#" THEN
			Seendate  = substr(text,2,10)
			LocateNo  = substr(text,13,2)
			SeenWhere = substr(text,16)
		else
			if ("," $ text) then
				vCountry = ut(substr(rtrim(text),at(",",text)+1))
				text = left(text,at(",",text)-1)
			end if
			if ("/" $ text) then
				vReg = word(text,1,"/",1)
				vCode = word(text,2,"/")
			else
				vReg = text
				vCode = ""
			end if
			vLogText = vLogText+ut(vReg)-"|"-vCode-"|"-vCountry-"|"-Seendate-"|"-LocateNo-"|"-ut(SeenWhere)+crlf()
		END IF
	END WHILE
	fp.close()
	dim global MyCollection as U
	myCollection.initialize("1|2|3|4|5","1","1|2|3|4|5|6",vLogText)
	dim entry_count as n = *count(vLogText)
	dim vLogData[entry_count] as p
	vLogData.initialize_properties("reg|Code|Country|SeenDate|SeenOrder|SeenWhere",vLogText)
	dim vFilterTxt[0] as c
	dim index as n
	' Build filter Text
	for block = 0 to int(entry_count/64 + 1)
		vFilter = ""
		for iteration = 1 to 64
			index = block * 64 + iteration
			if index > entry_count then
				exit for
			end if
			vFilter = vFilter + "reg='"-vLogData[index].reg-"'.or."
		next iteration
		vFilterTxt[]=left(vFilter,len(vFilter)-4)
	next Block
	block = vFilterTxt.first_empty()-1
	vFilterTxt.resize(block)
	vOrder = "REG"
	dim idx as n
	' build Display Data
	dim vDisplayData[9999] as p
	vDisplayData.initialize_from_table("Logging",vFilterTxt[1],vOrder)
	for iterate = 2 to block
		vDisplayData.initialize_from_table("Logging",vFilterTxt[iterate],vOrder,.t.)
	next iterate
	rec_numb = vDisplayData.first_empty()
	vDisplayData.resize(rec_numb-1)
	title_text = "Enter Logging"
	ui_dlg_box("@="+title_text,<<%dlg%
{Background=#153+204+255}
{region1}
{font=Courier New,7}
{endregion2};
{region=3}
[%O==FmtCurrLogg(eval(value))%.60,16idx^#vDisplayData!select];
{endregion=3}
{region}
{line=2,0};
{justify=center}
<OK><CLOSE>
{endregion};
%dlg%,<<%code%
	if a_dlg_button = "select" then
		t = table.open("logging")
		query.filter = "unique="-quote(rtrim(vDisplayData[idx].unique))
		showvar(query.filter)
		query.order = "recno()"
		t.query_create()
		ThisReg = myCollection.get(rtrim(vDisplayData[idx].reg))
		if rtrim(t.as) = "" then
			t.change_begin()
			t.H      = word(ThisReg,5,"|")
			t.Where  = word(ThisReg,6,"|")
			t.When   = word(ThisReg,4"|")
			t.as     = t.reg
			t.change_end(.t.)
		else
			reply = ui_msg_box("","Confirm Removal of Logging Data for "+word(ThisReg,1,"|"),UI_QUESTION_SYMBOL+UI_YES_NO)
			if reply = UI_YES_SELECTED then
				t.change_begin()
				t.h = ""
				t.where = ""
				t.when = ""
				t.as = ""
				t.change_end(.t.)
			end if
		end if
		a_dlg_button = ""
	end if
%code%)	
end if
end
FUNCTION FmtCurrLogg as c (ar as p)
	FmtCurrLogg = padr(rtrim(ar.reg),12," ")+padr(rtrim(ar.Code),10," ")+padr(rtrim(ar.H),3," ")+ar.When+" "+ar.Where
end function

just wondering if there is a quicker method of appending all the queried data into one array?

[Graham Wickens]

found a more efficient method (less searches of table to create array)

	dim shared MyCollection as U
	myCollection.initialize(vCollFormat,"1",vCollFormat,vCollText)
	dim vFilter as c = ""
	KeyVal = mycollection.first()
	for n = 1 to myCollection.size()
		CurrentVal = mycollection.get(KeyVal)
		if word(CurrentVal,4,"|") <> "" then
			vFilter = vFilter-"(reg='"-word(currentVal,1,"|")-"'.and.country='"-word(currentVal,4,"|")-"').or."
		else
			vFilter = vFilter - "reg='"-word(CurrentVal,1,"|")-"'.or."
		end if
		KeyVal = mycollection.next(KeyVal)
	next n
	dim vFilterLen as n = len(vfilter)
	dim vFilterTxt[0] as c
	if vFilterLen > 1024 then
		dim shared vFilterData as c = rtrim(left(vFilter,1024))
		dim shared vFilterLen as n = len(vfilter)
		dim shared vDataLen as n = 1
		dim Pos as n = 1
		while vDatalen > 0
			vDataLen   = len(vFilterData)-len(word(vFilterData,-1,".or."))
			vFilterTxt[] = rtrim(substr(vFilter,Pos,vDatalen-4))
			pos = pos + vDatalen
			vFilterData = rtrim(substr(vFilter,pos,1024))
		end while
		vFilterTxt.resize(vFilterTxt.first_empty()-1)
	else
	vFilterTxt[] = left(vFilter,len(rtrim(vFilter))-4)
	end if
	vOrder = "REG"
	dim shared vDisplayData[9999] as p
	vDisplayData.initialize_from_table("Logging",vFilterTxt[1],vOrder)
	for n = 2 to vFilterTxt.size()
		vDisplayData.initialize_from_table("Logging",vFilterTxt[n],vOrder,.t.)
	next n
	vDisplayData.resize(vDisplayData.first_empty()-1)

I created one long filter string, then chopped it in the maximum number of conditions for each search (i.e. maximum of 1024 characters).