Connection Strings

A Connection String is a special string that describes information about a data source (like a Database, but equally it could be a data file, spreadsheet or something else) and also how to connect to said data source. The string is passed to the driver that handles connections, to initiate that connection.

The Connection String is comprised of different attributes, though not all are required. Examples of the attributes are:

  • AuxDriver - the name of the driver to use
  • Server - the name of the server
  • Database - the name of the database to connect to, or the location of said database.
  • UserID - the User ID to use
  • Password - the password required for connecting
  • IntegratedSecurity - the type of security to use for authentication

Examples

Here are some examples of Data Abstract connection strings for the following drivers:

  • ODBC (via ADO)
  • MS SQL Server (ADO, SDAC or DBX)
  • MS Access (ADO)
  • Interbase (IBX, IBO or DBX)
  • MySQL (MyDAC)
  • NexusDB
  • DBISAM (DBISAM 3 and DBISAM 4)
  • VisualFoxPro (ADO)

ODBC Source

  1. ADO:
ADO?AuxDriver=MSDASQL.1;Server=ODBC_SOURCE;UserID=user;Password=password
  1. AnyDAC:
AnyDAC?AuxDriver=ODBC;DataSource=MySQLServer

MS SQL Server

  1. ADO with SQL Server Authentication:
ADO?AuxDriver=SQLOLEDB.1;Server=localhost;Database=Northwind;UserID=sa;Password=;
  1. ADO with Windows Authentication:
ADO?AuxDriver=SQLOLEDB.1;Server=localhost;Database=Northwind;IntegratedSecurity=SSPI;
  1. SDAC with SQL Server Authentication:
SDAC?Server=localhost;Database=Northwind;UserID=sa;Password=;
  1. SDAC with Windows Authentication:
SDAC?Server=localhost;Database=Northwind;Integrated Security=SSPI
  1. DBX with SQL Server Authentication:
DBX?AuxDriver=MSSQL;Server=localhost;Database=Northwind;UserID=sa;Password=sa;

The DBX MSSQL driver doesn't work with blank passwords. The Delphi 7 update (http://bdn.borland.com/article/0,1410,29209,00.html) should resolve this issue.

  1. DBX with Windows Authentication
DBX?AuxDriver=MSSQL;Server=localhost;Database=Northwind;UserID=x;Password=x;OS Authentication=True;

Because of a bug in the default version of dbExpress, a non-empty UserID and Password must be present even if Windows Authentication is used. This should be fixed in the updated MS SQL driver. The above connection string only works with updated uDADBXDriver and uDAEngine units.

  1. CoreLabs DBX driver for SQL Server - not supported.

MS Access

  1. Using Microsoft Jet OLEDB provider:
ADO?AuxDriver=Microsoft.Jet.OLEDB.4.0;Server=D:\DB\db1.mdb;UserID=Admin;Password=;
  1. Using ODBC provider:
ADO?AuxDriver=MSDASQL.1;Server=MS AccessDatabase;Database=D:\DB\db1.mdb;

Interbase

  1. Interbase Express:
IBX?Server=localhost;Database=D:\DB\employee.gdb;UserID=sysdba;Password=masterkey;Dialect=3
  1. Interbase Objects:
IBO?Server=localhost;Database=D:\DB\employee.gdb;UserID=sysdba;Password=masterkey;Dialect=3
  1. DBX to local server:
DBX?AuxDriver=Interbase;Database=D:\DB\employee.gdb;UserID=sysdba;Password=masterkey;SQLDialect=3;
  1. DBX to remote server:

TCP/IP:

DBX?AuxDriver=Interbase;Database=SUZY:D:\DB\employee.gdb;UserID=sysdba;Password=masterkey;SQLDialect=3;

NetBEUI:

DBX?AuxDriver=Interbase;Database=\\SUZY\D:\DB\employee.gdb;UserID=sysdba;Password=masterkey;SQLDialect=3;
  1. ADO OLEDB provider IBProvider - not tested;

MySQL

  1. MySQL Direct Access Components:
MyDAC?Server=localhost;Database=test;UserID=root;Password=;
  1. DBX (only for MySQL 3.x):
DBX?AuxDriver=MYSQL;Server=localhost;Database=test;UserID=root;Password=;
  1. CoreLabs MySQL DBX driver - not supported.

NexusDB

  1. Standard connection:
NexusDB?Server=ipv4://NexusDB@127.0.0.1;Database=alias://myAlias
  1. Embedded server:
NexusDB?Server=embedded://nxServerEngine_embedded;Database=path://myPath;

DBISAM 3

  1. Standard connection:
DBISAM3?Server=localhost;Database=MiniWH;UserID=Admin;Password=DBAdmin;
  1. Local tables:
DBISAM3?Database=c:\RemObjects\MiniWH\Databases\DBISAM 3;

DBISAM 4

  1. Standard connection:
DBISAM4?Server=localhost;Database=MiniWH;UserID=Admin;Password=DBAdmin;
  1. Local tables:
DBISAM4?Database=c:\RemObjects\MiniWH\Databases\DBISAM 4;

Note: The DBISAM 4 driver needs a small fix if you are using DA3 build 301. The DataAbstract_DBISAMDriver_D7 package needs to be rebuilt and re-installed with the following changes:

uDADBISAMDriver.pas: Ensure that DBISAM_V3 is not DEFINED and uncomment line 692 (outpar.DataType := DATypeToVCLType(par.DataType);) Remove db324d7r.dcp from the package and replace it with db408d7r.dcp (or the appropriate version on your machine).

VisualFoxPro

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"