AfterAjaxCallbackComplete event never fires on update that takes more than two minutes

[richardurban]

I have defined the following xbasic function, called by a button on a UX:

function updateprices as c (e as p)
	dim number_updated as n
	Dim cn as sql::connection
flag = cn.open("::Name::urbangrocery")

dim sqlCommand as c

sqlCommand = <<%str%
update hgdelivery.products pr 
join hgdelivery.daily_data dd on substring(pr.upc,1,11) = substring(dd.upc,3,11)
set pr.nprice = dd.price,
pr.sale_price = dd.sale_price,
pr.price_updated = CURDATE(),
pr.sale_begin_date = dd.start_sale,
pr.sale_end_date = dd.end_sale;

%str%
flag = cn.execute(sqlCommand)

sqlCommand = "select ROW_COUNT()" 
flag = cn.execute(sqlCommand)
dim rs2 as sql::ResultSet
rs2 = cn.ResultSet
number_updated = rs2.data(1)

if flag =.t.	
updateprices = "alert('Number of records updated: " + js_escape(number_updated) + "');"

else
updateprices = "alert('Operation failed to complete');"
end if

cn.Close()
end function

In the OnAjaxCallback Client side event I have the following code:

A5.msgBox.show('Updating Product Data','<div style=\'padding: 20px;\'><img src=\'CSS/A5System/Images/wait.gif\' />Please Wait - Updating Records...</div>','none',function() {});

In the AfterAjaxCallbackComplete Client Side event I have this code:

A5.msgBox.hide();

This works fine when I restrict the number of records in the update. However, when I attempt to include all of the records in the update (about 12,000), the MYSQL query completes, as shown by using the Query in MYSQL:

select * from information_schema.processlist

but the "Updating Product Data" message box never closes, and the Alert message never displays.

Does anyone know what the problem may be?

[richardurban]

Re: AfterAjaxCallbackComplete event never fires on update that takes more than two minutes

I just decided to split the records to be updated in half. This is better anyway, because the more records updated the longer it takes, with the time to update increasing in a steep curve, not in a linear fashion. The update works fine now, completing in about 100 seconds for an update with 11,300 records updated.

function updateprices as c (e as p)
	dim number_updated as n
	dim number_updated2 as n
	dim total_updated as n
	Dim cn as sql::connection
flag = cn.open("::Name::urbangrocery")

dim sqlCommand as c

sqlCommand = <<%str%
update hgdelivery.products pr 
join hgdelivery.daily_data dd on substring(pr.upc,1,11) = substring(dd.upc,3,11)
set pr.nprice = dd.price,
pr.sale_price = dd.sale_price,
pr.price_updated = CURDATE(),
pr.sale_begin_date = dd.start_sale,
pr.sale_end_date = dd.end_sale
where pr.new_code between 100 and 56000;
%str%
flag = cn.execute(sqlCommand)

sqlCommand = "select ROW_COUNT()" 
flag = cn.execute(sqlCommand)
dim rs2 as sql::ResultSet
rs2 = cn.ResultSet
number_updated = rs2.data(1)

sqlCommand = <<%str%
update hgdelivery.products pr 
join hgdelivery.daily_data dd on substring(pr.upc,1,11) = substring(dd.upc,3,11)
set pr.nprice = dd.price,
pr.sale_price = dd.sale_price,
pr.price_updated = CURDATE(),
pr.sale_begin_date = dd.start_sale,
pr.sale_end_date = dd.end_sale
where pr.new_code between 56001 and 99999;
%str%
flag = cn.execute(sqlCommand)

sqlCommand = "select ROW_COUNT()" 
flag = cn.execute(sqlCommand)
dim rs4 as sql::ResultSet
rs4 = cn.ResultSet
number_updated2 = rs4.data(1)
total_updated = number_updated + number_updated2

sqlCommand = "SELECT COUNT(*) FROM hgdelivery.daily_data;" 
flag = cn.execute(sqlCommand)
dim rs3 as sql::ResultSet
rs3 = cn.ResultSet
daily_dat_count = rs3.data(1)

if flag =.t.	
updateprices = "alert('Number of records updated: " + js_escape(total_updated) +" of: " + js_escape(daily_dat_count) + "');"

else
updateprices = "alert('Operation failed to complete');"
end if

cn.Close()

[GGandhi]

Re: AfterAjaxCallbackComplete event never fires on update that takes more than two minutes

I am just curious

update hgdelivery.products pr 
join hgdelivery.daily_data dd on substring(pr.upc,1,11) = substring(dd.upc,3,11)
set pr.nprice = dd.price,
pr.sale_price = dd.sale_price,
pr.price_updated = CURDATE(),
pr.sale_begin_date = dd.start_sale,
pr.sale_end_date = dd.end_sale;

this is from your first post, if you set this in a stored procedure or a function and call that from alpha, does that take same amount of time say 100 secs?
while this will not answer your question phrased in the title, I am just wondering the server might do it faster than the 100 secs. your customer who is updating will appreciate the speed. (may be it is just you updating)

[GGandhi]

Re: AfterAjaxCallbackComplete event never fires on update that takes more than two minutes

removed by Gandhi, error in statement.