SQL Maestro Group / Products / DB2 / Resources / AnySQL Maestro Connection Strings

A brief guide to connection strings for AnySQL Maestro (Updated)

Last modified: Feb 7, 2013

The article contains a list of ODBC drivers and OLE DB providers AnySQL Maestro has been examined with. The corresponding connection strings are also included.

1. ODBC Drivers/OLE DB Providers

AnySQL Maestro works via ADO interface. So to work with a database, first of all you need to have the corresponding ODBC driver/OLE DB provider installed on your PC. The table below contains a list of drivers successfully tested with AnySQL Maestro as well as the links to the home page of each driver.

Server	Versions	Tested Drivers/Providers
MySQL	5.0, 5.1, 5.5, 5.6	MySQL Connector/ODBC 3.51, MySQL Connector/ODBC 5.1, MySQL Connector/ODBC 5.2
PostgreSQL	8.3, 8.4, 9.0, 9.1, 9.2	PostgreSQL ODBC driver
Microsoft SQL Server	SQL Server 2000, 2005, SQL Server 2008, 2012	Microsoft OLE DB Provider for SQL Server, SQL Native Client 9.0 (SQL Server 2005), SQL Server Native Client 10.0 (SQL Server 2008 R2), SQL Server Native Client 11.0 (SQL Server 2012)
Oracle	9i, 10g, 11g	Oracle Provider for OLE DB, Microsoft OLE DB Provider for Oracle
IBM DB2	8.x - 10.x	IBM OLE DB Provider for DB2
MS Access, MS Excel	MS Access 2007, MS Excel 2007	2007 Office System Driver
MS Access, MS Excel	MS Access 2010, MS Excel 2010	Microsoft Access Database Engine 2010
Firebird	2.0 - 2.5	IBPhoenix Open Source ODBC Driver
Interbase	7.0 to 11.0	Embarcadero InterBase ODBC Driver IBPhoenix Open Source ODBC Driver
SQLite	3.0 - 3.7	SQLite3 ODBC Driver
Text files	-	Microsoft Jet OLE DB 4.0
Sybase SQL Anywhere	10.0 - 12.0	Sybase SQL Anywhere ODBC Driver

Some of drivers and providers listed above (Microsoft Jet OLE DB 4.0, Microsoft OLE DB Provider for SQL Server) are included into the Windows installation package, some other (SQL Anywhere OLE DB Provider, SQL Anywhere ODBC Driver) come with DBMS installation package or need to be downloaded separately.

2. Connection Strings

To connect to a database with AnySQL Maestro, you need to enter a connection string at the Create Database Profile window. To get a connection string that is ready to use, specify parameters for the corresponding template. For example, if you want to connect to the Sakila database at local MySQL server as user Robin with password Red using MySQL Connector/ODBC 5.1, your connection string could be as follows:

Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Sakila;User=Robin;Password=Red;

You can also provide additional parameters to specify how the driver should work. For example, if you want to enable auto-reconnection functionality for the connection above, use the following connection string:

Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Sakila;User=Robin;Password=Red;Option=4194304

To find a description of such additional parameters, use the links to the driver/provider documentation in the table below.

Driver	Connection string template
MySQL Connector/ODBC	Driver=myDriverName;Server=myServerAddress;Port=3306; Database=myDataBase;User=myUsername;Password=myPassword; As the driver name for MySQL ODBC 3.51 Connector enter Driver={MySQL ODBC 3.51 Driver}, for MySQL ODBC 5.1 Connector - Driver={MySQL ODBC 5.1 Driver}, for MySQL ODBC 5.2 Unicode Connector - Driver={MySQL ODBC 5.2w Driver}, and for MySQL ODBC 5.2 Ansi Connector - Driver={MySQL ODBC 5.2a Driver}. Port value is not necessary to be specified. By default it is equal to 3306. Here you can find the full list of the connection string parameters with explanation.
PostgreSQL ODBC Driver (psqlODBC)	The full distribution installs 2 drivers: PostgreSQL Unicode, a Unicode enabled driver that will work well with character from a huge range of languages, and PostgreSQL ANSI, an ANSI driver which is also able to handle some multibyte character sets such as EUC_JP, BIG5 and Shift-JIS. UNICODE: Driver={PostgreSQL UNICODE};Server=myServerAddress;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword; ANSI: Driver={PostgreSQL ANSI};Server=myServerAddress;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Microsoft OLE DB Provider for SQL Server	Provider=SQLOLEDB;Server=myServerAddress;Database=myDataBase; Uid=myUsername; Pwd=myPassword; The complete list of the driver connection parameters is accessible at MSDN.
SQL Native Client	Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase; Uid=myUsername; Pwd=myPassword; The complete list of the driver connection parameters is accessible at MSDN.
SQL Server Native Client 10.0	Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword; For more information about the connection string options, use the following link.
SQL Server Native Client 11.0	Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword; Find out the description of connection string keywords used with this client.
Microsoft OLE DB Provider for Oracle	Provider=msdaora;Data Source=myDataBase;User Id=myUsername;Password=myPassword;
Oracle Provider for OLE DB	Provider=OraOLEDB.Oracle;Data Source=myDataBase;User Id=myUsername;Password=myPassword; The description of the additional components is available at the Oracle Provider for OLE DB Developer's Guide.
IBM OLE DB Provider for DB2	Provider=IBMDADB2;DSN=myDataBase;Uid=myUsername;Pwd=myPassword; More information about this provider can be found at the product page.
2007 Office System Driver, Microsoft Access Database Engine 2010	MS Access: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydatabase.mdb;
	To connect to MS Excel, add the appropriate Extended Properties of the connection string based on the Excel file type as recommended at Download Center instructions. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyExcelFile.xls;Extended Properties="Excel 12.0 Xml;HDR=Yes" Specify "HDR=Yes" if the first row of the file contains column names (default value) or "HDR=No" otherwise.
	Text files: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Exported_data\TXT\; Extended Properties="text;HDR=Yes;FMT=Delimited"; The format of the text file is determined by using a schema information file Schema.ini.
IBPhoenix Open Source ODBC Driver	Driver=Firebird/InterBase(r) driver;Uid=myUsername;Pwd= myPassword; DbName=d:\myfolder\my_firebird_database.fdb;
Embarcadero InterBase ODBC Driver	Provider=MSDASQL;User ID="myFirebirdUser";Password="myPassword";Extended Properties="Driver={InterBase ODBC driver};DBNAME=d:\myfolder\my_firebird_database.fdb; The complete reference comes with the driver.
SQLite3 ODBC Driver	Driver=SQLite3 ODBC Driver;Database=d:\myfolder\my_sqlite_database.db; More information about the driver can be found at the producer's site.
Sybase SQL Anywhere ODBC Driver	Driver={SQL Anywhere 12};ENG=myEngine;UID=myUser;PWD=myPassword;DBN=myDatabase;LINKS=TCPIP(HOST=myHost); Complete list of connection parameters is also available at SyBooks Online

3. Specialized tools

Though AnySQL Maestro includes all the basic tools for effective database management, we do also offer software products designed especially for concrete DBMS. These products support advanced DBMS features such as stored procedures, triggers, grants and work faster because of using direct connections instead of ODBC drivers.

See the feature matrix to find out the difference between AnySQL Maestro and specialized tools such as SQL Maestro for MySQL, PostgreSQL Maestro, MS SQL Maestro, etc.

Back to resource list