Connection Frequently Asked Questions

How can I work with Schemas with several connections defined?

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

private void DataAbstractService_ValidateDatatableAccess(DataAbstractServiceValidateDBObjectAccessEventArgs aEA)
{
  this.AcquireCorrectConnection(aEA.Schema.FindDataset(aEA.DBObjectName));
}

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

private void DataAbstractService_BeforeUpdateData(DataAbstractService aSender, DataAbstractServiceUpdateDataEventArgs aEA)
{
  if (aEA.Deltas.Count > 0)
    this.AcquireCorrectConnection(aEA.Deltas[0].Schema);
}

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

  String lRequiredConnection = schemaElement.Statements[0].Connection;
  if (String.IsNullOrEmpty(lRequiredConnection))
    lRequiredConnection = RemObjects.DataAbstract.Server.Engine.ConnectionManager.DefaultConnectionName;
  if (String.Equals(this.Connection.Name, lRequiredConnection, StringComparison.OrdinalIgnoreCase))
    return;

  // Release default connection
  IAbstractConnection lConnection = this.Connection;
  Boolean lHandleTransaction = lConnection.InTransaction;
  if (lHandleTransaction)
    this.CommitTransaction(lConnection);
  RemObjects.DataAbstract.Server.Engine.ConnectionManager.ReleaseConnection(ref lConnection);

  // Acquire new connection
  lConnection = RemObjects.DataAbstract.Server.Engine.ConnectionManager.AcquireConnection(lRequiredConnection, true);
  if (lHandleTransaction)
    this.BeginTransaction(ref lConnection);
  this.Connection = lConnection;
}

or

procedure TDataAbstract_Service.DataAbstractServiceValidateDatasetAccess(
  Sender: TObject; const aConnection: IDAConnection;
  const aDatasetName: String; const aParamNames: array of String;
  const aParamValues: array of Variant; aSchema: TDASchema;
  var Allowed: Boolean);
var
  i: integer;
  ConName: string;
begin
  ConName := '';

  for i := 0 to aSchema.Datasets.Count - 1 do
    if AnsiSameText(aSchema.Datasets[i].Name, aDatasetName) then begin
      ConName := aSchema.Datasets[i].Statements[0].Connection;
      break;
    end;

  if not AnsiSameText(ConName, aConnection.Name) then begin
    Connection := aSchema.ConnectionManager.NewConnection(ConName);

    for i := 0 to aSchema.ConnectionManager.Connections.Count - 1 do
      aSchema.ConnectionManager.Connections[i].Default :=
          AnsiSameText(aSchema.ConnectionManager.Connections[i].Name, ConName);
  end;
end;

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

How does DA locate statements for a connection?

Each table (or stored procedure) in DA can have multiple statements. As such, when you open it, DA will need to find the correct statement for the connection you are currently using:

  • If there's a statement that matches the currently opened connection, that statement will be used.
  • Else, DA will look for the connection marked as default, check if there's a statement for that connection, and use that.

If neither of these are successful, you get an error. This could happen if:

  • You open connection A, connection B is marked as default, but the table only provides statements for connections C and D.
  • You open connection B, but the table only has a statement for A.

In each of these cases (and their variations), DA doesn't know what statement to use, as none of them match the current connection, or are default.

Note: We do have an issue logged on how to find "default" statements more easily in the future.

Where can I find a comprehensive list of database connection strings?

Some examples of Data Abstract connection strings for the following drivers are provided:

  • ODBC Source
Driver Connection string
ADO ADO?AuxDriver=MSDASQL.1;Server=ODBC_SOURCE;UserID=user;Password=password
FireDAC FireDAC?AuxDriver=ODBC;DataSource=MySQLServer
  • MS SQL Server
Driver Connection string
ADO with SQL Server Authentication ADO?AuxDriver=SQLOLEDB.1;Server=localhost;Database=Northwind;UserID=sa;Password=;
ADO with Windows Authentication ADO?AuxDriver=SQLOLEDB.1;Server=localhost;Database=Northwind;IntegratedSecurity=SSPI;
SDAC with SQL Server Authentication SDAC?Server=localhost;Database=Northwind;UserID=sa;Password=;
SDAC with Windows Authentication SDAC?Server=localhost;Database=Northwind;Integrated Security=SSPI
DBX with SQL Server Authentication DBX?AuxDriver=MSSQL;Server=localhost;Database=Northwind;UserID=sa;Password=sa;
DBX with Windows Authentication DBX?AuxDriver=MSSQL;Server=localhost;Database=Northwind;UserID=x;Password=x;OS Authentication=True;
  • MS Access
Driver Connection string
Microsoft Jet OLEDB provider ADO?AuxDriver=Microsoft.Jet.OLEDB.4.0;Server=D:\DB\db1.mdb;UserID=Admin;Password=;
Using ODBC provider ADO?AuxDriver=MSDASQL.1;Server=MS AccessDatabase;Database=D:\DB\db1.mdb;
  • Interbase
Driver Connection string
Interbase Express (IBX) IBX?Server=localhost;Database=D:\DB\employee.gdb;UserID=sysdba;Password=masterkey;Dialect=3
Interbase Objects (IBO) IBO?Server=localhost;Database=D:\DB\employee.gdb;UserID=sysdba;Password=masterkey;Dialect=3
DBX to local server DBX?AuxDriver=Interbase;Database=D:\DB\employee.gdb;UserID=sysdba;Password=masterkey;SQLDialect=3;
DBX to remote server (TCP/IP) DBX?AuxDriver=Interbase;Database=SUZY:D:\DB\employee.gdb;UserID=sysdba;Password=masterkey;SQLDialect=3;
DBX to remote server (NetBEUI) DBX?AuxDriver=Interbase;Database=\SUZY\D:\DB\employee.gdb;UserID=sysdba;Password=masterkey;SQLDialect=3;
  • MySQL
Driver Connection string
MySQL Direct Access Components MyDAC?Server=localhost;Database=test;UserID=root;Password=;
DBX DBX?AuxDriver=MYSQL;Server=localhost;Database=test;UserID=root;Password=;
  • NexusDB
Driver Connection string
Standard connection NexusDB?Server=ipv4://NexusDB@127.0.0.1;Database=alias://myAlias
Embedded server NexusDB?Server=embedded://nxServerEngine_embedded;Database=path://myPath;
  • DBISAM 4
Driver Connection string
Standard connection DBISAM4?Server=localhost;Database=MiniWH;UserID=Admin;Password=DBAdmin;
Local tables DBISAM4?Database=c:\RemObjects\MiniWH\Databases\DBISAM 4;
  • VisualFoxPro
Driver Connection string
Microsoft Visual FoxPro OLEDB provider ADO?AuxDriver=VFPOLEDB.1;Data Source=C:\Projects\Test\test.dbc

A customer had problems using the connection string above, but succeeded by using the following steps:

  • Control Panel -> Administrative Tools -> Data Sources (ODBC) -> System DNS -> Add.. -> Microsoft Visual FoxPro Driver (you may be prompted to download and install VFPODBC.msi from the msdn-site).
  • Configure the new DSN by specifying its Path (say d:\Databases\VFPDataFolder).
  • Use the ConnectionString below in the SchemaModuler to test the connection. 
ADO?AuxDriver=MSDASQL.1;Server=localhost;
  Persist Security Info=False;Data Source=Visual FoxPro Database;
  Extended Properties="DSN=Visual FoxPro Database;
  UID=;SourceDB=d:\Databases\VFPDataFolder;
  SourceType=DBF;Exclusive=No;BackgroundFetch=Y