ODBC
Tags: opengauss
Published: 2023-03-10 04:51:41

Open Database Connectivity (ODBC) is a Microsoft API for accessing databases based on the X/OPEN CLI. Applications interact with the database through the APIs provided by ODBC, which enhances their portability, scalability, and maintainability.

Figure 1 shows the system structure of ODBC.

Figure 1 ODBC system structure

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rmsnpn2lsjreytbhc25a.png)

openGauss supports ODBC in the following environments.

Table 1 OSs Supported by ODBC

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yrd76pktdry0szslc6wu.png)

**ODBC Packages for the Linux OS**
Obtain the **openGauss-*.*.0-ODBC.tar.gz** package from the release package. In the Linux OS, unixODBC header files (including **sql.h** and **sqlext.h**) and a library file (**libodbc.so**) are required in application development. The header files and library file can be obtained from the **unixODBC-2.3.0** installation package.

**Configuring the Data Source**

The ODBC driver (**psqlodbcw.so**) provided by openGauss can be used after it is configured in a data source. To configure a data source, you must configure the **odbc.ini** and **odbcinst.ini** files on the server. The two files are generated during the unixODBC compilation and installation, and are saved in the **/usr/local/etc** directory by default.

1. Obtain the **unixODBC-2.3.9** source code package.

Download address: http://www.unixodbc.org/download.html

2. Install unixODBC. If the unixODBC of another version has been installed on the host, overwrite the existing unixODBC.

Currently, unixODBC-2.2.1 is not supported. For example, to install unixODBC-2.3.0, run the commands below. unixODBC is installed in the **/usr/local** directory by default. The data source file is generated in the **/usr/local/etc** directory, and the library file is generated in the **/usr/local/lib** directory.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nncv1usmui2vjbs0wivr.png)

3. Replace the openGauss client driver.

3.1 Decompress openGauss-x.x.x-ODBC.tar.gz to the /usr/local/lib directory. The psqlodbcw.la and psqlodbcw.so files are obtained.
3.2 Copy the library in the lib directory obtained after decompressing openGauss-x.x.x-ODBC.tar.gz to the /usr/local/lib directory.

4. Configure a data source.

4.1 Configure the ODBC driver file.

Add the following content to the **/xxx/odbc/etc/odbcinst.ini** file:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z75phq6zm6wcy8qzvibl.png)

For descriptions of the parameters in the **odbcinst.ini** file, see Table 2.

Table 2 odbcinst.ini configuration parameters

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e8y224frcr35210yvhjo.png)

4.2 Configure the data source file.

Add the following content to the **/usr/local/etc/odbc.ini** file:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ts7kcnl9zseddj9twyxq.png)

For descriptions of the parameters in the **odbc.ini** file, see Table 3.

Table 3 odbc.ini configuration parameters

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cbqoyx4msplq7sv2etcc.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5jzxxal31kraqcj722d7.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wt17or2961m2s5ncy2yp.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yz83tx4jdn5hlav4qz4t.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fxt5yzp9dyxyzv7gecgp.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qvjj81mqta5kwh1g4p5e.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lci4limvrttyhouc7jmr.png)

The valid values of Sslmode are as follows:

Table 4 Sslmode options

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0qopgdbn1lq79175xsxf.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/roea26ef5cbjo0bbmqet.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1v7f771rgj2lwmayfhf4.png)

5. Configure the database server.

5.1 Log in as the OS user omm to the primary database node.

5.2 Run the following command to add NIC IP addresses or host names which are separated by commas (,). The NICs and hosts are used to provide external services. In the following command, NodeName specifies the name of the current node.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rl4s6d3xn6moy9v03bat.png)

If direct routing of LVS is used, add the virtual IP address (10.11.12.13) of LVS to the server listening list.

You can also set listen_addresses to * or 0.0.0.0 to listen on all NICs, but this incurs security risks and is not recommended.

5.3 Run the following command to add an authentication rule to the configuration file of the primary database node. In this example, the IP address (10.11.12.13) of the client is the remote host IP address.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ootweo7wz1n8m62h13oc.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o2u5ijji5k81wbdqd9hy.png)

If the ODBC client and the primary database node to connect are deployed on the same machine, you can use the local trust authentication mode. Run the following command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mcg0imkb4xjr3kzlyk7x.png)

If the ODBC client and the primary database node to connect are deployed on different machines, use the SHA-256 authentication mode. Run the following command:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gyy1schdowwfcivtwn6r.png)

5.4 Restart openGauss.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f9eowa6amumzzmgiod31.png)

6. Configure the environment variables on the client.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/po5j7euwkhskqvkot3pd.png)

Add the following information to the configuration file:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ry2w7cxvc7rxrx23ohaf.png)

7. Run the following command to validate the addition:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i3kry60h12lxz8qlvu1h.png)

**Verifying the Data Source Configuration**

Run the **./isql -v** MPPODBC command (**MPPODBC** is the data source name).

**·** If the following information is displayed, the configuration is correct and the connection succeeds.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/chdy6wyg3ied51xvb51r.png)

**·** If error information is displayed, the configuration is incorrect. Check the configuration.

**Development Process**

Figure 2 ODBC-based application development process

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ws4aebg7tog2y2aqfttz.png)

**Common APIs**
Table 5 API description

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/geszhxezoj9na34rfqdm.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f8f8hgsk8eplh3juyll4.png)