SQL Maestro Group / Resources / Latest resources / AnySQL Maestro Connection Strings

A brief guide to connection strings for AnySQL Maestro

Last modified: Aug 14, 2009

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	MySQL Connector/ODBC 3.51, MySQL Connector/ODBC 5.1
PostgreSQL	8.3, 8.4	psqlODBC 08.03.0400
Microsoft SQL Server	SQL Server 2000, SQL Server 2005, SQL Server 2008	Microsoft OLE DB Provider for SQL Server, SQL Native Client, SQL Server Native Client 10.0
Oracle	9i, 10g, 11g	Oracle Provider for OLE DB, Microsoft OLE DB Provider for Oracle
IBM DB2	8.1, 9.0, 9.5	IBM OLE DB Provider for DB2
MS Access, MS Excel	MS Access 2003, MS Excel 2003	Microsoft Jet OLE DB 4.0
MS Access, MS Excel	MS Access 2007, MS Excel 2007	ACE OLEDB 12.0
Firebird	2.0, 2.1	Firebird ODBC driver
SQLite	3.6	SQLite3 ODBC Driver
Text files	-	Microsoft Jet OLE DB 4.0
Sybase SQL Anywhere	10.0, 11.0	SQL Anywhere OLE DB Provider 10, SQL Anywhere ODBC Driver 10

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	MySQL ODBC 3.51 Driver: Driver={MySQL ODBC 3.51 Driver};Server=myServerAddress;Port=3306; Database=myDataBase;User=myUsername;Password=myPassword; MySQL ODBC 5.1 Driver: Driver={MySQL ODBC 5.1 Driver};Server=myServerAddress;Port=3306; Database=myDataBase;User=myUsername;Password=myPassword; 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)	Standard: Driver={PostgreSQL}; Server=myServerAddress;Port=5432;Database=myDataBase; Uid=myUsername;Pwd=myPassword; ANSI: Driver={PostgreSQL ANSI};Server=myServerAddress;Port=5432;Database=myDataBase; Uid=myUsername;Pwd=myPassword; UNICODE: Driver={PostgreSQL UNICODE};Server=myServerAddress;Port=5432;Database=myDataBase; Uid=myUsername;Pwd=myPassword; Detailed description of the parameters is available at the driver documentation.
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.
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.
Microsoft Jet OLE DB 4.0	MS Access: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb; To view additional connection parameters, click here.
	MS Excel: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcelFile.xls;Extended Properties="Excel 8.0;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.
Firebird ODBC Driver	Driver=Firebird/InterBase(r) driver;Uid=myUsername;Pwd= myPassword; DbName=d:\myfolder\my_firebird_database.fdb; Multilingual documentation of the driver is available for download at the product page.
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.
SQL Anywhere OLE DB Provider 10	Provider=SAOLEDB.10;ENG=myServerName;DBN=myDataBase;CommLinks=clientSideNetworkProtocol; User ID=myUsername;Password=myPassword; Here you can find the full list of the connection string parameters with explanation.
SQL Anywhere ODBC Driver 10	Driver={SQL Anywhere 10};CommLinks=clientSideNetworkProtocol;ENG=myServerName; DBN=myDataBase;User ID=myUsername;Password=myPassword; Here you can find more information about the SQL Anywhere ODBC driver. 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