Using ODBC to Connect to a Database

ODBC Constraints

• ODBC does not support read on the standby node.
• ODBC does not support user-defined types and does not support user-defined parameters in stored procedures.
• ODBC does not support DR switchover.
• When the proc_outparam_override parameter is enabled for the database, ODBC cannot properly call the stored procedure that contains the out parameter.

Prerequisites

Procedure in a Linux Server

1. Obtain the source code package of unixODBC by clicking the following link:

   Download address: https://www.unixodbc.org/unixODBC-2.3.7.tar.gz.

   After the download, verify the integrity based on the integrity verification algorithm provided by the community. Download https://www.unixodbc.org/unixODBC-2.3.7.tar.gz.md5, view the MD5 value, and check whether the MD5 value is the same as that in the source code package.

2. Install unixODBC. It does not matter if unixODBC of another version has been installed.
   For example, to install unixODBC-2.3.7, run the commands below.

   tar zxvf unixODBC-2.3.7.tar.gz
   cd unixODBC-2.3.7
   
   ./configure --enable-gui=no # To perform compilation on an Arm server, add the configure parameter --build=aarch64-unknown-linux-gnu.
   make
   # The installation may require root permissions.
   make install

   

   • Currently, unixODBC-2.2.1 is not supported.
   • By default, it is installed in the /usr/local directory. The data source file is generated in the /usr/local/etc directory, and the library file is generated in the /usr/local/lib directory.
   • You can compile unixODBC with the --enable-fastvalidate=yes option to achieve higher performance. However, this option may cause an application that passes an invalid handle to the ODBC API to fail instead of returning an SQL_INVALID_HANDLE error.

3. Replace the GaussDB client driver.

   Decompress GaussDB-Kernel_Database version number_OS version number_64bit_Odbc.tar.gz. After the decompression, the lib and odbc folders are generated. The odbc folder contains another lib folder. Copy all dynamic libraries in the /lib and /odbc/lib folders to the /usr/local/lib directory.

4. Configure the data source.
   1. Configure the ODBC driver file.

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

      [GaussMPP]
      Driver64=/usr/local/lib/gsqlodbcw.so
      setup=/usr/local/lib/gsqlodbcw.so

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

      Table 2 odbcinst.ini configuration parameters

      Parameter	Description	Example
      [DriverName]	Driver name, corresponding to Driver in DSN.	[DRIVER_N]
      Driver64	Path of the dynamic driver library.	Driver64=/usr/local/lib/gsqlodbcw.so
      setup	Driver installation path, which is the same as the dynamic library path in Driver64.	setup=/usr/local/lib/gsqlodbcw.so

   2. Configure the data source file.

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

      [MPPODBC]
      Driver=GaussMPP
      Servername=127.0.0.1 # Database server IP address
      Database=db1  # Database name
      Username=omm  # Database username
      Password=  # Database user password
      Port=8000  # Database listening port
      Sslmode = allow

      Table 3 describes the parameters in the odbc.ini file.

      Table 3 odbc.ini configuration parameters

      Parameter	Description	Example Value
      [DSN]	Data source name.	[MPPODBC]
      Driver	Driver name, corresponding to DriverName in odbcinst.ini.	Driver = DRIVER_N
      Servername	Server IP address. Multiple IP addresses can be configured. Both IPv4 and IPv6 are supported.	Servername=127.0.0.1
      Database	Name of the database to connect.	Database=db1
      Username	Database username.	Username = omm
      Password	Database user password. NOTE: After a user establishes a connection, the ODBC driver automatically clears their password stored in memory. However, if this parameter is configured, unixODBC will cache data source files, which may cause the password to be stored in the memory for a long time. When you connect to an application, you are advised to send your password through an API instead of writing it in a data source configuration file. After the connection has been established, immediately clear the memory segment where your password is stored. CAUTION: The password in the configuration file must comply with the following HTTP rules: Characters must comply with the URL encoding specifications. For example, the exclamation mark (!) must be written as %21, and the percent sign (%) must be written as %25. Therefore, pay attention to the characters. A plus sign (+) will be replaced by a space.	Password=********
      Port	Port number of the server.	Port = 8000
      Sslmode	Specifies whether to enable SSL.	Sslmode = allow

      For values of the Sslmode parameter, see Table 4.

      Table 4 Sslmode options

      Sslmode	Whether SSL Encryption Is Enabled	Description
      disable	No	SSL connection is not enabled.
      allow	Possible	If the database server requires SSL connection, SSL connection can be enabled. However, authenticity of the database server will not be verified.
      prefer	Possible	If the database supports SSL connection, SSL connection is preferred. However, authenticity of the database server will not be verified.
      require	Yes	SSL connection is required and data is encrypted. However, authenticity of the database server will not be verified.
      verify-ca	Yes	SSL connection is required and whether the database has a trusted certificate will be verified.
      verify-full	Yes	SSL connection is required. In addition to the check scope specified by verify-ca, the system checks whether the name of the host where the database resides is the same as that in the certificate. GaussDB does not support this mode.

5. Enable the SSL mode. For details, contact the database administrator.
6. Configure the database server. For details, contact the database administrator.
7. Configure the environment variables on the client.

   vim ~/.bashrc

   Add the following information to the configuration file:

   export LD_LIBRARY_PATH=/usr/local/lib/:$LD_LIBRARY_PATH
   export ODBCSYSINI=/usr/local/etc
   export ODBCINI=/usr/local/etc/odbc.ini

8. Run the following command to validate the addition:

   source ~/.bashrc

9. Connect to the database.

   isql -v GaussODBC

   GaussODBC: data source name.

   • If the following information is displayed, the configuration is correct and the connection succeeds:

     +---------------------------------------+
     | Connected!                            |
     |                                       |
     | sql-statement                         |
     | help [tablename]                      |
     | quit                                  |
     |                                       |
     +---------------------------------------+

   • If error information is displayed, the configuration is incorrect. Check the configuration.

Procedure in a Windows Server

Configure an ODBC data source using the ODBC data source manager preinstalled in the Windows OS.