We have several identical MSSQL databases, and there is a particular field in a particular table is updated for each database at the end of each day- it's a tinyint that changes from 0 to 1. We need a simple dashboard to show when each database field has switched for the day. I'd prefer not to create a table or save any data for this dashboard, and instead just grab the data from elsewhere as needed.
I've created a list control based on a custom xbasic function: the function queries each server, gets the field and populates to an array, and finally the array is converted to json and sent to the list control.
This works great so far. The issue is that it takes some time to query each server, so it is inefficient to update the entire list once we know a particular database has switched over. The list's custom xbasic template has notes on how to run a filter, but it's not really a filter on a query, it's eliminating a query for a server.
So I guess, the question is: any general design pointers on how to pass the data from the populated list back to the xbasic function, parse it out to just find the databases still needing updated, perform the update on just those servers, and then pass everything back to the list again.
Here's pseudocode for the full query as it stands now:
I've created a list control based on a custom xbasic function: the function queries each server, gets the field and populates to an array, and finally the array is converted to json and sent to the list control.
This works great so far. The issue is that it takes some time to query each server, so it is inefficient to update the entire list once we know a particular database has switched over. The list's custom xbasic template has notes on how to run a filter, but it's not really a filter on a query, it's eliminating a query for a server.
So I guess, the question is: any general design pointers on how to pass the data from the populated list back to the xbasic function, parse it out to just find the databases still needing updated, perform the update on just those servers, and then pass everything back to the list again.
Here's pseudocode for the full query as it stands now:
Code:
dim dToday as d = today() 'array of each of the branch location databases dim arrBranch[0] as p '.. fill the array from another table, array structure like below .. arrBranch[].Branch_Num = 1 arrBranch[..].Branch_Name = "Branch One" arrBranch[..].server_name = "Br1_Name" arrBranch[..].server_Pass = "Encrypted_Password" arrBranch[..].Forwarded = 0 '.. and so on .. for i = 1 to ArrBranch.size() delete sql dim sql as c = "Select BranchID, Forwarded from [Database].[dbo].[Table] where CAST([BusinessDate] as DATE) = '" + dtoc(dToday) + "';" delete cn dim cn as sql::Connection delete cConnStr dim cConnStr as c = "{A5API='SQLServer',Server='"+arrBranch[i].Branch_server+"'}" delete rs dim rs as sql::ResultSet if .not. cn.open(cConnStr,"UserName",a5_decrypt_string(ArrBranch[i].server_pass,"decrypt_key")) then 'error handling goto SkipBranchServer end if if .not. cn.Execute(sql) then 'error handling cn.close() goto SkipBranchServer end if rs = cn.ResultSet cn.close() if rs.RowCount > 0 then arrBranch[i].Forwarded = rs.data("Forwarded") end if SkipBranchServer: next i dim ReturnText ReturnText = vartojson(ArrBranch)
Comment