Asynchronous script management.

Question

I would like to know how others manage Asynchronous scripts? How do you successfully stop a script because it seems to ignore the AsyncShutdownCmd - Script not responding to AsyncShutdownCmd. 
 When the bootstrap eventually does take action it crashed the Engine which is not ideal: AppEngine_GR was terminated abnormally. Having the script time out is also not ideal because it creates issues of its own. 
 Expected behavior is that the once you activate AsyncShutdownCmd the Garbage Collector comes along and clean it up.

Richard Köhlerstrand · Answer

I understand,

To implement logic that can expect to take long time you need to take another approach if async script is not resolving your issue.

There is a lot to be said about asynchronous scripting and things to tweak to optimize it to avoid performance issues.
But when it comes to connecting to a Database and retrieving data based on a query, then Aveva has some solutions that could be explored.

I have not personally connected to an Oracle database using this, but the method works great when using Microsoft SQL.

The idea is that you connect and execute your query in one script, retrieving enough information to trigger another script, that just wait for the completion to process the result. this will prevent your engine to feel the pain of long running scripts.

The SQLData Script library (aaDBIntegration) is included in any newer version of a galaxy and according to the documentation, it seems to support Oracle too.

/cfs-file/__key/communityserver-discussions-components-files/1658/SQLScript.pdf

The advantage of using this library is that it has a way of executing the query Async.

You should try it out and see if it applies to your problem.

To get you started:

Create the following Attributes

SQLGetStart Boolean

SQLGetProcess Boolean

SQLGet_CommandID String

Add one script, the example was using a SQL Server so you might need to modify your connection string and query to match your Oracle server

I have modified the script to what i expect you to do but since i don't have an oracle database to test with you can consider it as pseudo code

But the documentation can help you here.

dim Connection as aaDBClient.aaDBConnection;
dim Command as aaDBClient.aaDBCommand;
'Create a connection object with the connection string.
LogMessage("Creating connection");
Connection = aaDBAccess.CreateConnection("Provider=MSDAORA;Data Source=myOracleServer;User ID=<name>;Password=<password>",aaDBConnectionType.Oracle);
'Create a command object, with a SQL statement.
LogMessage("Creating a command object");
Command = Connection.CreateCommand("SELECT Id, Value FROM Person.Contact WHERE (ID > :lastName)", aaDBCommandType.SqlStatement, true);

'Everything is ready, let's execute the command async.
LogMessage("Executing command async");
dim ResultCode as integer;
ResultCode = Command.ExecuteAsync();

if ResultCode <> 0 then
	'Failed to start async execution, report the reason.
	LogMessage("Got error " + ResultCode + " executing command async");
else
	'Execution started, identify the command by ID, for use later.
	LogMessage("Command async execution started successfully");
	Me.SQLGet_CommandID = Command.GetID();
	'Allow the Process script to run.
	Me.SQLGetProcess = true;
endif;

'Reset for next time
Me.SQLGetStart = false;

Then create your second script to process the result triggering While true on Me.SQLGetProcess

dim Command as aaDBClient.aaDBCommand;
dim i as integer;
'Retrieve the command object using its ID.
Command = aaDBAccess.GetCommand(Me.SQLGet_CommandID);
if Command <> null then
	'Poll for command complete
	if Command.ExecutionState == aaDBCommandState.Failed then
		Command.ExecuteAsync();
		LogMessage("Retry execution");
	endif;
	if Command.ExecutionState <> aaDBCommandState.Queued then
		LogMessage("Command execution state is " + Command.ExecutionState);

		if Command.ExecutionState == aaDBCommandState.Completed then
			DIM Rows as integer;
			Rows = Command.RowCount;
			LogMessage("Row count returned from command is " + Rows);
			if Rows > 0 then
				Command.SelectRow(0);
				For i=0 To (rows-1)
					Command.SelectRow(i);
					LogMessage("Value from row "+i+": "+Command.GetCurrentRowColumnByName("Value"));
				next;
			endif;
		endif;
		'When done, dispose the command.
		Command.Dispose();
		'Reset for next time
		Me.SQLGetProcess = false;
	endif;
else
	LogMessage("Cannot find command " + Me.SQLGet_CommandID);
	Me.SQLGetProcess = false;
endif;

This was the example i could create on the spot so I hope it get you started and resolves your issue.

A thing i found that could be good to know is that normally (Using MSSQL), you can set a CommandTimeout on the command object, it seems to not apply for Oracle connections