All Products
Search

This Product

    AnalyticDB:ODBC

    Document Center

    AnalyticDB:ODBC

    Last Updated:Jan 25, 2024

    This topic describes how to use Open Database Connectivity (ODBC) to connect to an AnalyticDB for PostgreSQL instance.

    Prerequisites

    • The internal or public endpoint of an AnalyticDB for PostgreSQL instance is obtained.

      • If your client is deployed on an Elastic Compute Service (ECS) instance that resides in the same region and uses the same network type as the AnalyticDB for PostgreSQL instance, you can use the internal endpoint to connect to the AnalyticDB for PostgreSQL instance. Log on to the AnalyticDB for PostgreSQL console. In the Database Connection Information section of the Basic Information page, view the internal endpoint of the instance.

      • If your client is deployed on an ECS instance that resides in a different region or uses a different network type from your AnalyticDB for PostgreSQL instance or a system outside Alibaba Cloud, you must apply for a public endpoint and then use the public endpoint to connect to the AnalyticDB for PostgreSQL instance. For more information about how to apply for a public endpoint, see Manage public endpoints.

    • The IP address of your client is added to an IP address whitelist of the AnalyticDB for PostgreSQL instance. For more information, see Configure an IP address whitelist.

    Procedure

    The PostgreSQL ODBC driver is an open source tool licensed based on the GNU Lesser General Public License (LGPL) protocol. You can download the driver from the PostgreSQL official website.

    To use the ODBC driver to connect to an AnalyticDB for PostgreSQL instance, perform the following steps:

    1. Run the following commands to install the ODBC driver: 

      yum install -y unixODBC.x86_64  
yum install -y postgresql-odbc.x86_64

    2. Run the following command to configure the data source:

      vim /etc/odbc.ini

      Add connection information. Example: 

      [mygpdb]
Description = Test to gp
Driver = PostgreSQL
Database = <test_pg>
Servername = <gp-bp1k1b8683t****.gpdb.rds.aliyuncs.com>
UserName = <accout>
Password = <Password12!>
Port = 5432
ReadOnly = 0

    3. Test the connectivity. Example:

      echo "select count(*) from pg_class" | isql mygpdb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from pg_class
+---------------------+
| count               |
+---------------------+
| 388                 |
+---------------------+
SQLRowCount returns 1
1 rows fetched

    4. After the ODBC driver is connected to the instance, connect your application to the driver. For more information, see PostgreSQL ODBC driver and psqlODBC HOWTO - C#.

    Additional information

    The Greenplum official website provides a tool package that includes JDBC, ODBC, and libpq. This package is easy to install and use. For more information, see the Greenplum documentation.

    References