.png "Employee"){kind=icon}
Applies to:
Any Incorta version On-Prem/Cloud
Question:
- How to configure an ODBC connection?
- Unable to connect to Incorta using ODBC
Overview:
Any program that uses Open Database Connectivity (ODBC) to connect to a remote database needs a way to identify the type of database and the computer to which it is connecting. This is accomplished through the use of an ODBC data source, which associates a name with database connection information. As Incorta interfacing Postgres as the hidden layer to show the data stored on Incorta side so the article will describe how to connect to Postgres DB.
Installation:
1)Download Postgres ODBC driver
- You can download the ODBC driver for PostgreSQL from the official download website of PostgreSQL. You can view the various versions of the drivers from the below link
https://www.postgresql.org/ftp/odbc/versions
- Can use any source dll or the ms ( Please use the latest version)
DDL --> a library that contains code and data that can be used by more than one program at the same time, and it's like JAR (once extracted zip file copy to the following location based on OS arch)
| Directory | Installed driver |
| C:\WINDOWS\syswow64 | 32-bit ODBC |
| C:\WINDOWS\system32 | 64-bit ODBC |
MSI --> a Windows package that contains installation information for a particular installer (using GUI for installing DLL files)
2) Configure and locate an ODBC data source
- Open the ODBC Data Source Administrator dialog box, go to the Start menu under Windows Administrative Tools, and choose ODBC Data Sources:
Or In the Control Panel under Administrative Tools, choose appropriate ODBC Data Sources:
Or simpler, in the Windows search box, type the ODBC Data Sources word:
Note: starting windows 10 only x64-bit
- For the purpose of this article, the PostgreSQL Server ODBC data source will be created under the System DSN tab. To start creating the connection, press the Add button:
A dialog box Create a new data source opens. Select PostgreSQL Unicode(x64) driver and click on Finish.
Another dialog box PostgreSQL Unicode ODBC Driver (pSQLODBC) Setup opens. In the dialog box, you must specify the following parameters:
- Data Source: Specify the desired Data Source name. The name will be used to identify the DSN. I have given pgadmin13 as DSN (it will just for list DS)
- Description: Provide the details of the data source
- Database: Specify the database that you want to use. In our configuration, we are using DemoDatabase, so the database name is DemoDatabase
- Server: Specify the Server name/hostname on which the PostgreSQL is installed. We have installed PostgreSQL on localhost so the server name is localhost
- Port: Specify the port number on which the PostgreSQL services are running. PostgreSQL service is running on 5432 port so the value of the port number is 5432
- SSL Mode: If you are using SSL to connect to PostgreSQL, then specify the SSL Mode. We are not using SSL to connect to the PostgreSQL, so I have chosen to disabled
- Username and Password: Specify the appropriate username and password to connect to PostgreSQL. We are using the Postgres user to connect (uid and pwd for the tenant)
Once all parameters are configured, the configuration setup looks like the following image:
Click on Test to verify the connectivity.
As you can see, the connection has been established successfully. Click on Save to create the system DSN. Back to the System DSN screen, you can see the pgadmin13 DSN has been created.
Now on the client application if you list ODBC connection you should find the DS that we was created on DS list like the below example for EXCEL
1-Open Excel (blank worksheet) and navigate to the Data tab, in the Get External Data section select From Other Sources > From Data Connection Wizard. If you do not see From Data Connection Wizard in the list of options you can enable it from Excel > Options > Data.
2- Select ODBC DSN from the Data Connection Wizard and click Next.
3-Select the name of the data source you created and click Next.
Troubleshooting:
1- Check connection between ODBC client and Incorta using telnet command
2- Verify that you can connect using JDBC instead of ODBC by using any of DB clients like DBvis or DBeaver, or you can use the below article for testing the command line
Testing Incorta Postgres connection using driver jar file and command line
3- If it possible, create a new tenant and test the connection, and if successful, we can export the tenant and reimport
