In this example, we will show you how to create a data source connection to a MySQL and PostgreSQL database using the ODBC connection type in SmartConnect. The same approach can be applied when connecting to other systems such as Oracle, DB2, Sybase, or any application that supports an ODBC driver.
To verify that the driver is installed correctly, check for it under ODBC Data Sources (64-bit or 32-bit) on the machine, depending on the SmartConnect version.
Option 1: Using ODBC DSN
For option one, please refer to our previous article: Using SmartConnect to connect to ODBC Data Source.
Option 2: Using ODBC Driver – ConnectionString
Go to the Connections >> Create >> choose ODBC and then click the (…) ellipsis to view the available options.
If you receive an error such as “This driver has not been installed” even though the driver is present on the machine, it is likely due to a mismatch between the actual driver name and the default driver name in our configuration table.
Run the query below against the SmartConnect database and update the driver name so that it exactly matches what is shown under ODBC Data Sources.
SELECT * FROM [dbo].[ConnectionStringTemplate]
In my case, I installed the PostgreSQL ANSI (x64) driver andMySQL ODBC 9.4 ANSI driver, so I updated the table values accordingly to match the driver names as shown below.
Note: ensure that you only add or update the name—do not change brackets or modify any other default values. For PostgreSQL, i.e., Server=;Port=; or remove hardcode value of any.
To ensure it matches the name with the ODBC Data Source (64-bit).
Destination:
If you want to update data in a MySQL/PostgreSQL database or any non-Microsoft SQL Server database, it requires a bit more technical setup. SmartConnect does not provide a direct ODBC destination type as it does for SQL connections. However, this does not mean that updating an ODBC-enabled database is not possible.
To achieve this, the ODBC driver must support writing data through .NET, and the update process needs to be handled using a Script Task, as outlined in this article: Using SmartConnect to connect to ODBC Data Source.
Go to the Maintenance >> ODBC Data Source >> Add, then click the (…) ellipsis to view the available options.
If you receive an error such as “This driver has not been installed” even though the driver is present on the machine, it is likely due to a mismatch between the actual driver name and the default driver name in our configuration table.
Run the query below against the SmartConnect database and update the driver name so that it exactly matches what is shown under ODBC Data Sources.
SELECT * FROM [dbo].[ConnectionStringTemplate]
In my case, I installed the PostgreSQL ANSI (x86) driver and MySQL ODBC 8.0 ANSI driver, so I updated the table values accordingly to match the driver names as shown below.
Note: ensure that you only add or update the name—do not change brackets or modify any other default values. For PostgreSQL, i.e., Server=;Port=; or remove hardcode value of any.
To ensure it matches the name with the ODBC Data Source (32-bit).
Destination:
If you want to update data in a MySQL/PostgreSQL database or any non-Microsoft SQL Server database, it requires a bit more technical setup. SmartConnect does not provide a direct ODBC destination type as it does for SQL connections. However, this does not mean that updating an ODBC-enabled database is not possible.
To achieve this, the ODBC driver must support writing data through .NET, and the update process needs to be handled using a Script Task, as outlined below.
Have a question? Please reach out to us at support@eonesolutions.com
