Using ODBC to Connect to a Database
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.
GaussDB supports ODBC in the following environments.
OS |
Platform |
---|---|
EulerOS V2.0SP5 |
x86_64 |
EulerOS V2.0SP9 |
Arm64 |
EulerOS V2.0SP10 |
x86_64 |
EulerOS V2.0SP10 |
Arm64 |
Windows 7 |
x86_32 |
Windows 7 |
x86_64 |
Windows Server 2008 |
x86_32 |
Windows Server 2008 |
x86_64 |
Kylin V10 |
x86_64 |
Kylin V10 |
Arm64 |
UnionTech V20 |
x86_64 |
UnionTech V20 |
Arm64 |
Huawei Cloud EulerOS 2.0 |
x86_64 |
Huawei Cloud EulerOS 2.0 |
Arm64 |
The ODBC Driver Manager running on Unix or Linux can be unixODBC or iODBC. unixODBC-2.3.7 is used as the component for connecting to the database.
Windows has a native ODBC Driver Manager. You can locate Data Sources (ODBC) by choosing
.The current database ODBC driver is based on an open-source version and may be incompatible with data types tinyint, smalldatetime, nvarchar, and nvarchar2.
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
- The package name for Linux is GaussDB-Kernel_Database version number_OS version number_64bit_Odbc.tar.gz. In the Linux OS, header files (including sql.h and sqlext.h) and the library (libodbc.so) are required in application development. These header files and library can be obtained from the unixODBC-2.3.7 installation package.
- The package name for Windows is GaussDB-Kernel_Database version number_Windows_Odbc_X64.tar.gz (64-bit). In the Windows OS, the required header files and library files are system-resident.
Procedure in a Linux Server
- 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.
- 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.
- 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.
- Configure the data source.
- 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
- 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.
- Configure the ODBC driver file.
- Enable the SSL mode. For details, contact the database administrator.
- Configure the database server. For details, contact the database administrator.
- 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
- Run the following command to validate the addition:
source ~/.bashrc
- 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.
- If the following information is displayed, the configuration is correct and the connection succeeds:
Procedure in a Windows Server
Configure an ODBC data source using the ODBC data source manager preinstalled in the Windows OS.
- Replace the GaussDB client driver.
Decompress the GaussDB-Kernel_Database version number_Windows_X64_Odbc.tar.gz (64-bit) driver package or GaussDB-Kernel_Database version number_Windows_X86_Odbc.tar.gz (32-bit) driver package, and click gsqlodbc.exe to install the driver.
- Open the driver manager.
When configuring the data source, use the ODBC driver manager corresponding to the ODBC version. If the 64-bit ODBC driver is used, the 64-bit ODBC driver manager must be used. Assume that the OS is installed on drive C (if the OS is installed on another drive, change the path accordingly):
- If you want to use 32-bit ODBC driver manager in a 64-bit OS, open C:\Windows\SysWOW64\odbcad32.exe. Do not choose Control Panel > Administrative Tools > Data Sources (ODBC).
WoW64 is short for Windows 32-bit on Windows 64-bit. C:\Windows\SysWOW64\ stores the 32-bit environment on a 64-bit system. C:\Windows\System32\ stores the environment consistent with the current OS. For technical details, see Windows technical documents.
- For a 32-bit OS, open C:\Windows\System32\odbcad32.exe or choose Computer > Control Panel > Administrative Tools > Data Sources (ODBC) to open Driver Manager.
- For a 64-bit OS, choose Control Panel > Administrative Tools > Data Sources (ODBC) to enable driver management.
- If you want to use 32-bit ODBC driver manager in a 64-bit OS, open C:\Windows\SysWOW64\odbcad32.exe. Do not choose Control Panel > Administrative Tools > Data Sources (ODBC).
- Configure the data source.
On the User DSN tab page, click Add and choose GaussDB Unicode for setup.
For details about the parameters, see Procedure in a Linux Server.
You can click Datasource to configure whether to print logs.
The entered username and password will be recorded in the Windows registry and you do not need to enter them again when connecting to the database next time. For security purposes, you are advised to delete sensitive information before clicking Save and enter the required username and password again when using ODBC APIs to connect to the database.
- Configure SSL mode.
Change the value of SSL Mode in 3 to require.
Table 5 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. Currently, Windows ODBC does not support the cert authentication.
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. Currently, Windows ODBC does not support the cert authentication.
- Configure a GaussDB server. For details, contact the administrator.
- Restart the database instance.
gs_om -t stop gs_om -t start
- Click Test to test the connection.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot