Connecting to different types of databases

The Database Adapter functionality of CADMATIC eShare has native support for Microsoft SQL Server and Oracle databases. It also supports OLE DB and ODBC that enable connecting to other database types such as Microsoft Access, MySQL, and PostgreSQL. To use OLE DB or ODBC, the appropriate data provider supplied by the database vendor or a third party must be installed on the CADMATIC eShare server computer. CADMATIC eShare only supports 32-bit database providers.

Microsoft SQL Server

CADMATIC eShare provides native support for Microsoft SQL Server databases, and you just need a suitable connection string to establish connection. Connection must be authenticated using mixed mode authentication; Windows authentication is not supported.

To access a Microsoft SQL Server database, a project administrator must define a database adapter that has database type set to Microsoft SQL Server, and the connection string must be using this format:

data source=<DB server>\<instance name>;initial catalog=<DB name>;User ID=<username>;Password=<password>;MultipleActiveResultSets=True

Note: The database user defined in the connection string only needs to have read access to the database.

Example connection string

This connection string specifies that Microsoft SQL Server database is on the local computer, the instance name is ‘MSSQLSERVER’, the database name is ‘StatusDatabase’, and the database is accessed with the username ‘eshare’ and password ‘esharepass’:

data source=localhost\MSSQLSERVER;initial catalog=StatusDatabase;User ID=eshare;Password=esharepass;MultipleActiveResultSets=True

Note: Add Encrypt=True to connection string in SQL database adapters if you want to use encrypted connection.

Microsoft Access

CADMATIC eShare can connect to Microsoft Access databases (accdb and mdb files) by using Microsoft Office Access Connectivity Engine (referred to as ACE below) or its predecessor Microsoft JetEngine, both of which use an OLE DB connection.

Note: We do not recommend using Microsoft Access databases directly with ACE because its performance is very limited and too slow for most enterprise scenarios, and unexpected behavior can occur when using this type of connection in a server application. A better alternative is to transfer the data from Microsoft Access to a proper database server such as Microsoft SQL Server or Oracle Database.

For CADMATIC eShare to access a Microsoft Access database, the system needs to be prepared as follows:

The database files are placed in a location that CADMATIC eShare can access.
CADMATIC eShare has read access to the database files. The simplest way to do this is to grant read access for the IIS_IUSRS group.
Either Microsoft Access or Microsoft Access Runtime is installed on the CADMATIC eShare server computer. Microsoft Access 2010 Runtime is available for free at http://www.microsoft.com/en-us/download/details.aspx?id=10910.

Then, a project administrator must define a database adapter that has database type set to OLE DB, and the connection string must be using this format:

Provider=<data provider>;Data Source=<database file>

Example connection string

This connection string specifies that Microsoft Access database is located in C:\Database.accdb and it does not use password protection:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database.accdb;

To retrieve data from the database, define the required SQL queries in the data source configuration of the database adapter. For example, if the database has a table named ‘Table1’ with the columns ‘Key1’ and ‘Value1’, you can use this kind of query:

SELECT Value1 FROM Table1 WHERE Key1 = @Key

See Creating an object attributes data source for more query examples.

MySQL

CADMATIC eShare can connect to MySQL databases by using ODBC.

To access a MySQL database, the system needs to be prepared as follows:

32-bit version of ‘MySQL Connector/ODBC’ is installed on the CADMATIC eShare server computer. Version 5.2 or later is recommended, but also earlier versions should work.
MySQL database provides a user account with permission to access the database; having SELECT privileges should be sufficient.

Then, a project administrator must define a database adapter that has database type set to ODBC, and the connection string must be using this format:

Provider={MySQL ODBC 5.2 Unicode Driver};Server=<DB server>;Database=<database name>;User=<username>;Password=<password>;Option=3;

Note the curly braces {} in {MySQL ODBC 5.2 Unicode Driver}. Change this provider name if your site uses a different version of MySQL Connector/ODBC.

Example connection string

This connection string specifies that MySQL database is on the server ‘mysql’, the database name is ‘crmdb’, and the database is accessed with the username ‘eshare’ and password ‘esharepass’:

Provider={MySQL ODBC 5.2 Unicode Driver};Server=mysql;Database=crmdb;User=eshare;Password=esharepass;Option=3;

PostgreSQL

CADMATIC eShare can connect to PostgreSQL databases using ODBC connection.

To access a PostgreSQL database, the system needs to be prepared as follows:

32-bit version of PostgreSQL ODBC Driver (psqlODBC) is installed on the CADMATIC eShare server.
PostgreSQL database provides a user account with sufficient permissions; at least CONNECT to the database and SELECT to the tables are needed.

Then, a project administrator must define a database adapter that has database type set to ODBC, and the connection string must be using this format:

Driver={PostgreSQL Unicode};Server=<DB server>;Port=<server port>;Database=<database name>;Uid=<username>;Pwd=<password>;

Note the curly braces {} in {PostgreSQL Unicode}. The TCP server port number is typically 5432.

Example connection string

This connection string specifies that PostgreSQL database is on the server ‘pgsql’ and uses the default port 5432, the database name is ‘crmdb’, and the database is accessed with the username ‘eshare’ and password ‘esharepass’:

Driver={PostgreSQL Unicode};Server=pgsql;Port=5432;Database=crmdb;Uid=eshare;Pwd=esharepass;