Multiple connections in single DA server not working as expected

Hi,

In our current project we are working with 2 databases.
I have both added them in the connections manager and tested their connection, so far so good.
Next I added a table from database A into the DA schema modeler and a stored procedure (as a command) from database B.
These are both showing up correctly, they have both 1 statement with the correct connection filled in in the schema modeler.
In the connections manager you always (?) have a default connection, in my case this was the first database added (‘A’).
When trying out the client, retrieving values from the table in DB A was working as expected.
However, executing the command failed with the message “Could not find stored procedure…” .
When setting the default connection to DB B, the situation is reversed, I cannot get any values from the table in DB A, but can execute the command (the stored procedure) on DB B.
From looking around the table/command should be using the connection defined in the statement, but it seems it is always using the default connection.
Is my thinking correct? Or am I missing something?

I am using Visual Studio 2019 and DA 10.0.0.1463.
Thanks in advance!

Kind regards,
Robin

Hello

To be able to work with Schemas containing objects that use different connections, add the ValidateDatatableAccess, ValidateCommandExecution and BeforeProcessDelta event handlers to your Service with code like:

	private void DataAbstractService_ValidateDataTableAccess(DataAbstractServiceValidateDBObjectAccessEventArgs e)
	{
		this.AcquireCorrectConnection(e.Schema.FindDataset(e.DBObjectName));
		e.Connection = this.Connection;
	}

	private void DataAbstractService_ValidateCommandExecution(DataAbstractServiceValidateDBObjectAccessEventArgs e)
	{
		this.AcquireCorrectConnection(e.Schema.Commands.FindItem(e.DBObjectName));
		e.Connection = this.Connection;
	}

	private void DataAbstractService_BeforeProcessDelta(BusinessProcessor sender, DeltaEventArgs e)
	{
		this.AcquireCorrectConnection(e.Delta.Schema);
		e.Connection = this.Connection;
	}

	private void AcquireCorrectConnection(RemObjects.DataAbstract.Schema.SQLSchemaElement schemaElement)
	{
		if ((schemaElement == null) || (schemaElement.Statements.Count == 0))
		{
			return;
		}

		String requiredConnection = schemaElement.Statements[0].Connection;
		if (String.IsNullOrEmpty(requiredConnection))
		{
			requiredConnection = RemObjects.DataAbstract.Server.Engine.ConnectionManager.DefaultConnectionName;
		}

		if (String.Equals(this.Connection.Name, requiredConnection, StringComparison.OrdinalIgnoreCase))
		{
			return;
		}

		// Release default connection
		IAbstractConnection connection = this.Connection;
		Boolean handleTransaction = connection.InTransaction;
		if (handleTransaction)
		{
			this.CommitTransaction(connection);
		}
		RemObjects.DataAbstract.Server.Engine.ConnectionManager.ReleaseConnection(connection);

		// Acquire new connection
		connection =  RemObjects.DataAbstract.Server.Engine.ConnectionManager.AcquireConnection(requiredConnection, true);
		if (handleTransaction)
		{
			this.BeginTransaction(ref connection);
		}

		this.Connection = connection;
		this.ConnectionName = requiredConnection;
	}

This code will acquire a connection that is needed to open the required DataTable, if this connection differs from the default one.

EDIT: Updated the code snippet

Hi,

Thanks for the code, I can see it retrieves and sets the connection property correctly.
However in my client I get the following exception:

{“An exception occurred on the server: Invalid object name ‘IniFile’.”}

With IniFile being the table name.
In this case IniFile is a table from database B, with database A set as default in the connections manager.
When trying:

dataAdapter.GetTable<IniFile>(). …

I got the above exception, I am using the LinqRemoteDataAdapter.
The initial case is working (retrieving from DB A, executing SP on DB B) but when trying to retrieve something from a table from DB B something still goes wrong.

Thanks again for your help!
Kind regards,
Robin

Hmm, sorry.

Updated the initial answer to a more modern API (and retested it on DA LINQ data access as well)

Logged an issue to put this into docs

Thanks, logged as bugs://84067

Hi,

No problem!
I changed it to the code sample and can also confirm this is working as expected now!
Thanks for the assistance!