MySQL Query fails in loop

[madtowng]

Re: MySQL Query fails in loop

Joe,

If it's just a matter of the connection being closed, when you make the sql call,
why not just check to make sure the connection is open just before you make the call.
I didn't put it in here but I usually put a counter in this to stop it from potential endless looping.

while .not. cn.open(namedconnection)
	cn.open(namedconnection)
' might want to log the need to open this for future reference
end while

[Pizzadude]

Re: MySQL Query fails in loop

Sorry about that.

[Davidk]

Re: MySQL Query fails in loop

I get the same error and loop count as you. Without spending days going over your logic, I'd guess... and it's just a guess... that there's nothing inherently wrong with what Alpha or MySQL is doing.

You're processing 55 Posting rows in the List against 814 Rules records... updating and deleting as you go along... so that you make changes to your main tables and re-select rows within nested loops.

You're doing this to the point of failure with a loop count of over 16,000... 16,000 iterations over a small number of rows... I'm not surprised it's blowing up.

It reminds me of a process I was asked to review and fix. This process was selecting 5000 rows and created a specific subset based on certain criteria. The original code processed the 5000 records into an array... and then 10 functions each re-created that array and processed into a subset... duplicating essentially the same code and rows over and over. It took 20 minutes to complete. Re-writing and understanding how XBasic works brought the execution time down to 8 seconds.

I believe your base logic is unnecessarily complex... that's what the numbers indicate.

[Pizzadude]

Re: MySQL Query fails in loop

So I need to take an entirely different approach. Any suggestions?

[Michael1954]

Re: MySQL Query fails in loop

Hi,

I have the tables.
What are you looking for in the postings tables?
How does it match the Postingrules?
When you find a match what needs updating?

[Pizzadude]

Re: MySQL Query fails in loop

Here is how the fields match up:

postingrules---------posting

BillTo_FK------------BillTo_FK
PUcity---------------originCity
PU_ST---------------originState
PUzip----------------originZip
DestCity-------------destCity
Dest_ST-------------destState
DelZip---------------destZip
Miles-----------------miles
MilesBlankYN--------matches IF miles IS NULL OR miles = ''
truckType------------truckType
TrkTypeBlankYN-----matches IF truckTypeIS NULL OR miles = ''
CommentPE---------comment
Length_1-------------length_1
LengthBlankYN------matches IF truckTypeIS NULL OR miles = ''
Weight---------------weight
WeightBlankYN------matches IF truckTypeIS NULL OR miles = ''

There are some conditions that determine if the record in posting is a match to record in postingrules.

For example, if postingrules.MilesBlankYN = "Y", and posting.miles IS Null or an empty string (blank), that is a match.

Below is the code to construct the WHERE clause for the query against the posting table. The variable names are R_ plus the postingrules field names.

So for example, if postingrules.PUcityPE contains a value, add that value to the WHERE clause.

Once this is constructed, the loop is started to obtain the posting.Posting_ID for each of the posting records. The Posting_ID is appended to the WHERE statement, and the query against the posting table executes.

The query will return one row if there is a match, or no rows it there is not a match.

If there is a match, there is a lot of code that creates an UPDATE statement for the posting record. You can see the code in the Posting UX, in the xbasic function processRulesForSelectedRows. I'll post it in the next post.

dim _whereClause as c	

	_whereClause = "WHERE BillTo_FK = " + "'" + R_BillTo_FK + "'" + " AND "
	
	if R_PUcityPE <> "" then
	_whereClause = _whereClause + "originCity = " + "'" + R_PUcityPE + "'" + " AND "
	end if
	
	if R_PU_ST <> "" then
	_whereClause = _whereClause + "originState = " + "'" + R_PU_ST + "'" + " AND "
	end if
	
	if R_PUzip <> "" then
	_whereClause = _whereClause + "originZip = " + "'" + R_PUzip + "'" + " AND "
	end if
	
	if R_DestCityPE <> "" then
	_whereClause = _whereClause + "destCity = " + "'" + R_DestCityPE + "'" + " AND "
	end if
	
	if R_Dest_ST <> "" then
	_whereClause = _whereClause + "destState = " + "'" + R_Dest_ST + "'" + " AND "
	end if
	
	if R_DelZip <> "" then
	_whereClause = _whereClause + "destZip = " + "'" + R_DelZip + "'" + " AND "
	end if
	
	if R_Miles <> "" then
	_whereClause = _whereClause + "miles = " + "'" + R_Miles + "'" + " AND "
	end if
	
	if R_MilesBlankYN = "Y" then
	_whereClause = _whereClause + "(miles IS NULL OR miles = '')" + " AND "
	end if
	
	if R_truckType <> "" then
	_whereClause = _whereClause + "truckType = " + "'" + R_truckType + "'" + " AND "
	end if
	
	if R_TrkTypeBlankYN = "Y" then
	_whereClause = _whereClause + "(truckType IS NULL OR truckType = '')" + " AND "
	end if
	
	if R_CommentPE <> "" then
	_whereClause = _whereClause + "comment = " + "'" + R_CommentPE + "'" + " AND "
	end if
	
	if R_Length <> "" then
	_whereClause = _whereClause + "length_1 = " + "'" + R_Length + "'" + " AND "
	end if
	
	if R_LengthBlankYN = "Y" then
	_whereClause = _whereClause + "(length_1 IS NULL OR length_1 = '')" + " AND "
	end if
	
	if R_Weight <> "" then
	_whereClause = _whereClause + "weight = " + "'" + R_Weight + "'" + " AND "
	end if
	
	if R_WeightBlankYN = "Y" then
	_whereClause = _whereClause + "(weight IS NULL OR weight = '')" + " AND "
	end if

[Pizzadude]

Re: MySQL Query fails in loop

Here is the code that executes if there is a match:

if R_CkDeletePosting = "Y" then

sql = <<%a%
DELETE
FROM posting
WHERE Posting_ID = :B
%a%

flag = cn.execute(sql,args)
if flag = .f. then
js = "alert('processRulesForSelectedRows cn.execute(sql,args) THREE failed.');"
goto endprocessRulesForSelectedRows
end if

goto skiptothenextrecord
end if

P_RulesApplied = rs2.Data("RulesApplied")
if P_RulesApplied = "" then
R_rulesApplied = _rulePK
else if P_RulesApplied <> "" then
R_rulesApplied = P_RulesApplied + ", " + _rulePK
end if
args.add("RepRulesApplied",R_rulesApplied)

P_PostingStatus = rs2.data("PostingStatus")
P_PUcityPE = rs2.data("originCity")
P_PU_ST = rs2.data("originState")
P_PUzip = rs2.data("originZip")
P_DestCityPE = rs2.data("destCity")
P_DestCityDAT = rs2.data("destCityDAT")
P_Dest_ST = rs2.data("destState")
P_DelZip = rs2.data("destZip")
P_Miles = rs2.data("miles")
P_truckType = rs2.data("truckType")
P_stops = rs2.data("stops")
P_MoreNotes = rs2.data("MoreNotes")

P_Length = rs2.data("length_1")
P_Weight = rs2.data("weight")

P_commentPE = rs2.data("comment") 
P_comment1DAT = rs2.data("Comment1DAT")
P_comment2DAT = rs2.data("Comment2DAT")

dim _repLength as c

if P_Length = "" .and. R_LengthBlankYN = "Y" .and. R_NewLength <> "" then
_repLength = R_NewLength

else if P_Length = "" .and. R_LengthBlankYN = "Y" .and. R_NewLength = "" then
_repLength = P_Length

else if P_Length <> "" .and. R_LengthBlankYN <> "Y" .and. R_NewLength = "" then
_repLength = P_Length

end if
args.add("repLength",_repLength)

dim _repWeight as c

if P_Weight = "" .and. R_WeightBlankYN = "Y" .and. R_NewWeight <> "" then
_repWeight = R_NewWeight

else if P_Weight = "" .and. R_WeightBlankYN = "Y" .and. R_NewWeight = "" then
_repWeight = P_Weight

else if P_Weight <> "" .and. R_WeightBlankYN <> "Y" .and. R_NewWeight = "" then
_repWeight = P_Weight

end if
args.add("repWeight",_repWeight)

dim _repMoreNotes as c

if P_MoreNotes = "" then

if P_MoreNotes = "" .and. R_NewMoreNotes <> "" then
_repMoreNotes = "Note added by Rule " + _rulePK + " :" + R_NewMoreNotes
end if

else if P_MoreNotes <> "" .and. R_NewMoreNotes <> "" then

dim searchFlag as l = *any(P_MoreNotes,R_NewMoreNotes)

if searchFlag = .t. then
_repMoreNotes = P_MoreNotes
else if searchFlag = .f. then
_repMoreNotes = P_MoreNotes + crlf(2) + "Note added by Rule " + _rulePK + " :" + R_NewMoreNotes
end if

else
_repMoreNotes = P_MoreNotes

end if

args.add("repMoreNotes",_repMoreNotes)

dim _repPostingStatus as c

if P_PostingStatus = "New Import" then
_repPostingStatus = "Not Posted"
else
_repPostingStatus = P_PostingStatus
end if
args.add("repPostingStatus",_repPostingStatus)

dim _repStopsPE as c

if R_NewStopsPE <> "" then
_repStopsPE = R_NewStopsPE

else if R_NewStopsPE = "" then
_repStopsPE = P_stops

end if
args.add("repStopsPE",_repStopsPE)

dim _repCommentPE as c
dim _repComment1DAT as c

if P_commentPE <> R_NewCommentPE .and. R_NewCommentPE <> ""
_repCommentPE = R_NewCommentPE
_repComment1DAT = R_NewCommentPE

else
_repCommentPE = P_commentPE
_repComment1DAT = P_commentPE

end if
args.add("repcommentPE",_repCommentPE)
args.add("repcomment1DAT",_repComment1DAT)
args.add("repcomment2DAT",R_NewComment2DAT)

dim _repPUcityPE as c
dim _repPUcityDAT as c

if P_PUcityPE = R_PUcityPE .and. R_NewPUcityPE <> "" then

if R_NewPUCityDAT <> "" then
_repPUcityDAT = R_NewPUCityDAT
else if R_NewPUCityDAT = "" then
_repPUcityDAT = R_NewPUcityPE
end if

else if P_PUcityPE = R_PUcityPE .and. R_NewPUcityPE = "" then
_repPUcityPE = P_PUcityPE

if R_NewPUCityDAT <> "" then
_repPUcityDAT = R_NewPUCityDAT
else if R_NewPUCityDAT = "" then
_repPUcityDAT = P_PUcityPE
end if

else
_repPUcityPE = P_PUcityPE
_repPUcityDAT = P_PUcityPE
end if

args.add("repPUcityPE",_repPUcityPE)
args.add("repPUcityDAT",_repPUcityDAT)

dim _repDelCityPE as c
dim _repDelCityDAT as c

if P_DestCityPE = R_DestCityPE .and. R_NewDelCityPE <> "" then
_repDelCityPE = R_NewDelCityPE

if R_NewDelCityDAT <> "" then
_repDelCityDAT = R_NewDelCityDAT
else
_repDelCityDAT = R_NewDelCityPE
end if

else if P_DestCityPE <> R_DestCityPE .and. R_NewDelCityPE = "" then
_repDelCityPE = P_DestCityPE

if R_DestCityPE <> R_NewDelCityDAT .and. R_NewDelCityDAT <> "" then
_repDelCityDAT = R_NewDelCityDAT
else
_repDelCityDAT = P_DestCityDAT
end if

else
_repDelCityPE = P_DestCityPE
_repDelCityDAT = P_DestCityPE

end if
args.add("repDelCityPE",_repDelCityPE)
args.add("repDelCityDAT",_repDelCityDAT)

dim _repPU_ST as c

if R_NewPU_ST <> "" then
_repPU_ST = R_NewPU_ST

else
_repPU_ST = P_PU_ST

end if
args.add("repPU_ST",_repPU_ST)

dim _repDelST as c

if R_NewDel_ST <> "" then
_repDelST = R_NewDel_ST

else
_repDelST = P_Dest_ST

end if
args.add("repDelST",_repDelST)

dim _repPUZIP as c

if R_NewPUzip <> "" then
_repPUZIP = R_NewPUzip

else
_repPUZIP = P_PUzip

end if
args.add("repPUZIP",_repPUZIP)

dim _repDelZip as c

if R_NewDelZip <> "" then
_repDelZip = R_NewDelZip

else
_repDelZip = P_DelZip

end if
args.add("repDelZip",_repDelZip)

dim _repMiles as c

if R_NewMiles <> "" .and. _repStopsPE = "0" then
_repMiles = R_NewMiles
else if R_NewMiles <> "" .and. _repStopsPE = "1" then
_repMiles = R_NewMiles
else if R_NewMiles <> "" .and. _repStopsPE = "2" then
_repMiles = R_NewMiles
else if R_NewMiles <> "" .and. _repStopsPE = "3" then
_repMiles = R_NewMiles
else if R_NewMiles <> "" .and. _repStopsPE = "4" then
_repMiles = R_NewMiles
else if R_NewMiles <> "" .and. _repStopsPE = "5" then
_repMiles = R_NewMiles
else if R_NewMiles <> "" .and. _repStopsPE = "6" then
_repMiles = R_NewMiles
else if R_NewMiles <> "" .and. _repStopsPE = "7" then
_repMiles = R_NewMiles
else if R_NewMiles <> "" .and. _repStopsPE = "8" then
_repMiles = R_NewMiles
else if R_NewMiles <> "" .and. _repStopsPE = "9" then
_repMiles = R_NewMiles
else if R_NewMiles <> "" .and. _repStopsPE = "10" then
_repMiles = R_NewMiles
else
_repMiles = P_Miles
end if
args.add("repMiles",_repMiles)

dim _repTrkTypePE as c
dim _repTrkTypeDAT as c
dim _DATcode as c

dim _repNewTruckTypePE as c

if P_truckType = "" .and. R_NewTruckTypePE <> "" .and. R_NewTruckTypeDAT <> "" .and. R_TrkTypeBlankYN = "Y" then
args.add("repTrkTypePE",R_NewTruckTypePE)
args.add("repTrkTypeDAT",R_NewTruckTypeDAT)
args.add("repNewTruckTypePE",R_NewTruckTypePE)	' 3-1-17 NEW

else if P_truckType <> "" .and. R_NewTruckTypePE <> "" .and. R_NewTruckTypeDAT <> "" .and. R_TrkTypeBlankYN <> "Y" then

args.add("repTrkTypePE",R_NewTruckTypePE)
args.add("repTrkTypeDAT",R_NewTruckTypeDAT)
args.add("repNewTruckTypePE",R_NewTruckTypePE)	' 3-1-17 NEW

else
args.add("repTrkTypePE",P_truckType)
args.add("repNewTruckTypePE","")
args.add("whatTrkType",P_truckType)

sql = <<%a%
SELECT TruckTypePE_PK, TrailerType, DATcode
FROM trucktype_pe
WHERE TrailerType = :whatTrkType
%a%

flag = cn.execute(sql,args)
if flag = .f. then
js = "alert('processRulesForSelectedRows cn.execute(sql,args) FOUR failed.');"
goto endprocessRulesForSelectedRows
end if

rs3 = cn.ResultSet

flag = rs3.nextRow()

if flag = .f. then
args.add("repTrkTypeDAT","")
else if flag = .t. then

_DATcode = rs3.data("DATcode")
args.add("repTrkTypeDAT",_DATcode)

end if

end if


sql = <<%a%
UPDATE posting
SET originCity = :repPUcityPE, originCityDAT = :repPUcityDAT, originState = :repPU_ST, originZip = :repPUZIP, destCity = :repDelCityPE, destCityDAT = :repDelCityDAT, destState = :repDelST,
PostingStatus = :repPostingStatus, destZip = :repDelZip, truckType = :repTrkTypePE, NewTruckTypePE = :repNewTruckTypePE, NewTruckTypeDAT = :repTrkTypeDAT, miles = :repMiles, Comment = :repcommentPE,
Comment1DAT = :repcommentPE, Comment2DAT = :repcomment2DAT,	stops = :repStopsPE, NewStopsPE = :repStopsPE, RulesApplied = :RepRulesApplied,
MoreNotes = :repMoreNotes, length_1 = :repLength, weight = :repWeight
WHERE Posting_ID= :B
%a%

flag = cn.execute(sql,args)

[Michael1954]

Re: MySQL Query fails in loop

Let me see If I can set a case statement that gets the "True" matches. Then you won't need to do much processing and you can keep most of your logic.

[Michael1954]

Re: MySQL Query fails in loop

I am sorry I still don't understand but here is how I would approach it.


SELECT
posting.Posting_ID,
postingrules.Rule_PK,
postingrules.MilesBlankYN
FROM
posting
INNER JOIN postingrules ON postingrules.Company_IDFK = posting.Comp_IDFK
WHERE
postingrules.WeightBlankYN IS NOT NULL OR
postingrules.TrkTypeBlankYN IS NOT NULL OR
postingrules.MilesBlankYN IS NOT NULL

In the data you sent me this returned no matches. That suggests to me you do not have to test for a match.

I think if you could explain the tests one by one we could find all of the records that should be tested.
We could then count how many passed for each rule process (Where you want to run update code) if the result was zero you would not run that branch.

This way you get your loop much smaller.

[Pizzadude]

Re: MySQL Query fails in loop

I'm not sure you can do what you are trying to do.

But it looks like you are trying to get a smaller list of postingrules to work with, before looping through records. Is that correct?

That is a good idea. For example, the BillTo_FK field does exactly that, it assures that the rules are only "This Customer's" rules, and I have javascript in mt Posting UX that will not allow the processRulesForSelectedRows function to execute if any of the selected rows belong to more than one customer.

But in order for that to work, the BillTo_FK field must have the same value in both tables, for all the records being processed in both tables.

Each rule has 16 fields that are used to construct the WHERE clause. In order for a match, there will be some combination of those fields that are the same in both tables, but it could be one field or all fields.

So I don't know how a join could be constructed.

I very much appreciate your time and efforts.

[Michael1954]

Re: MySQL Query fails in loop

There are 46,464 SQL choices when you join on BillTo_FK David said 16,000 in the loop. I am sure it should be possible to write a case statement to see if the record is worth looking at.

If you get your code working and you make the smallest error debugging will be a nightmare.

Good luck as I said my xbasic is non existent.

[Pizzadude]

Re: MySQL Query fails in loop

In the test case we have been using there are 16000 loops, because it is only looking at one particular value for BillTo_FK in both tables.

But it is still a lot of loops.

I keep thinking that is strange that Alpha and/or MySQL are unable to to continue repeatedly processing the same transaction. I would think it could go on for days, and repeat it millions of times.

[Davidk]

Re: MySQL Query fails in loop

But it's only 16000 because it fails. You don't know the real number. I have a feeling the logic flow isn't what it should be.

[Michael1954]

Re: MySQL Query fails in loop

I agree, remember the error messages may be misleading. It could be falling over on logic.

[Pizzadude]

Re: MySQL Query fails in loop

I'm not sure what you mean by failing due to "logic flow".