cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
miguello
Level VI

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

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:

Screenshot 2021-10-26 153937.png

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, 

M.

 

1 REPLY 1
ih
Super User (Alumni) ih
Super User (Alumni)

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

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:

 

Run queries in background, wait for them to complete, and reference them in the remaining JSL script