Executing series of data pulls in scheduled scripts when one or more DB servers are down

miguello — Sat, 10 Jun 2023 20:47:26 GMT

All,

I have a script that pulls various databases around the world. This script is scheduled and automated. Meaning that it does what it does, saves all the tables, closes JMP and task is then finished.

However, from time to time one or two DBs are unresponsive. LDAP server is down and doesn't accept my password, or some DB maintenance etc.

In this case I get a pop-up window like that:

Even if I'm there when this happens and click "Cancel", the script exits without completing.

I have the function that pulls the data from DB encapsulated in try\catch block - doesn't seem to help when I manually click the "Cancel" button... But more importantly - how do I run script unsupervised in this case? How do I tell it - if you can't connect to one of the DBs - let it go, pull data from the rest of the DBs and process what you have... Don't hang up the whole script only because one of the DBs is unresponsive... How do I get rid of that pop up window and just consider it failed try, move to catch block and go on with the script?

Here's portion of the script that is responsible for this:

DBPaths = [
	"Server1" => "ODBC:DSN=...PORT=5432;",
	"Server2" => "ODBC:DSN=...PORT=5432;",
	"Server3" => "ODBC:DSN=...PORT=5432;",
	"Server4" => "ODBC:DSN=...PORT=5432;",
	"Server5" => "ODBC:DSN=...PORT=5432;"];


myQuery = Function( {serverDBPath, server}, 

	New SQL Query(
		Version( 130 ),
		Connection( serverDBPath ),
		QueryName( server ),
		Select( ... ),
		From( ... ),
		Where( ... ),
		PostQueryScript( ... )
	) << Run Foreground()
);

myDTs = {};

For Each( {{server, DBPath}, index}, DBPaths,
	Show( server, DBPath, index );
	Try( myDTs[index] = myQuery( DBPath, server ), writeToLog( server || " Query error... Using: " || DBPath ||" Error message: "||exception_msg[1] ) );
);

Thanks,

Re: Executing series of data pulls in scheduled scripts when one or more DB servers are down

ih — Thu, 28 Oct 2021 13:43:59 GMT

I wonder if running the queries in the background would help? I often use a method like the one below with a check that the returned data is valid:

topic Executing series of data pulls in scheduled scripts when one or more DB servers are down in Discussions

Executing series of data pulls in scheduled scripts when one or more DB servers are down

Re: Executing series of data pulls in scheduled scripts when one or more DB servers are down