一、背景
在一次验证通过ODBC驱动连接达梦数据库的工作过程中，发现通过ISQL可以正常连接达梦数据库的，但是使用的C++代码生成的程序去连接时会提示失败，代码编译正常，其中配置的连接信息也都正确。
二、环境信息
1、操作系统及g++版本
[root@oracle odbc_connect]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@oracle odbc_connect]# g++ --version
g++ (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36)
Copyright (C) 2015 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
2、ODBC信息
（1） ODBC版本：
[root@oracle odbc_connect]# odbc_config --version
2.3.12
（2）.ODBC.INI配置信息：
[ODBC Data Sources]
DM8 = DM8 ODBC Driver
[DM8]
Description = DM ODBC DSN
Driver = DM8 ODBC DRIVER
SERVER = localhost
UID = SYSDBA
PWD = SYSDBA123
TCP_PORT = 5236
OPTION = 4
（3）ODBCINST.INI配置信息：
[DM8 ODBC DRIVER]
Description=ODBC DRIVER FOR DM
Driver=/home/dmdba/dmdbms/bin/libdodbc.so
Trace=Yes
TraceFile=/var/log/odbc.log
3、 C++程序源码
（1）源代码
#include <iostream>
#include <sql.h>
#include <sqlext.h>
#include <cstring> // for strcpy
#include <cstdio> // for fprintf, printf
#define CHARS 1024
class Conn {
public:
void connect_to_db();
private:
void extract_error(const char* fn, SQLHANDLE handle, SQLSMALLINT type);
void print_connection_info(SQLHDBC hDbc);
void print_current_date(SQLHDBC hDbc);
};
void Conn::extract_error(const char* fn, SQLHANDLE handle, SQLSMALLINT type) {
SQLINTEGER i = 0;
SQLINTEGER native;
SQLCHAR state[7];
SQLCHAR text[256];
SQLSMALLINT len;
SQLRETURN ret;
fprintf(stderr, "The driver reported the following diagnostics whilst running %s\n", fn);
do {
ret = SQLGetDiagRec(type, handle, ++i, state, &native, text, sizeof(text), &len);
if (SQL_SUCCEEDED(ret)) {
printf("%s:%ld:%ld:%s\n", state, (long)i, (long)native, text);
}
} while (ret == SQL_SUCCESS);
}
void Conn::print_connection_info(SQLHDBC hDbc) {
SQLCHAR dsn[CHARS], user[CHARS], server[CHARS], port[CHARS];
SQLSMALLINT len;
SQLGetInfo(hDbc, SQL_SERVER_NAME, server, sizeof(server), &len);
SQLGetInfo(hDbc, SQL_USER_NAME, user, sizeof(user), &len);
SQLGetInfo(hDbc, SQL_DATA_SOURCE_NAME, dsn, sizeof(dsn), &len);
// Assume default port for demonstration; typically port info might be stored or retrieved differently.
strcpy((char*)port, "5236");
std::cout << "Connected to database with DSN: " << dsn << ", User: " << user << ", IP: " << server << ", Port: " << port << std::endl;
}
void Conn::print_current_date(SQLHDBC hDbc) {
SQLHSTMT hStmt = NULL;
SQLRETURN retcode;
SQLCHAR date[CHARS];
SQLLEN len;
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
printf("Error allocating statement handle\n");
extract_error("SQLAllocHandle", hDbc, SQL_HANDLE_DBC);
return;
}
SQLCHAR* query = (SQLCHAR*)"SELECT SYSDATE FROM DUAL";
retcode = SQLExecDirect(hStmt, query, SQL_NTS);
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
printf("Error executing SQL statement\n");
extract_error("SQLExecDirect", hStmt, SQL_HANDLE_STMT);
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
return;
}
retcode = SQLFetch(hStmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLGetData(hStmt, 1, SQL_C_CHAR, date, sizeof(date), &len);
if (SQL_SUCCEEDED(retcode)) {
std::cout << "Current database date: " << date << std::endl;
}
} else {
printf("Error fetching data\n");
extract_error("SQLFetch", hStmt, SQL_HANDLE_STMT);
}
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}
void Conn::connect_to_db() {
SQLHENV hEnv = NULL;
SQLHDBC hDbc = NULL;
SQLRETURN retcode;
// 1. Allocate environment handle
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
// 2. Set environment attributes
SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
// 3. Allocate connection handle
SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
// 4. Connect to the database
retcode = SQLConnect(hDbc, (SQLCHAR*)"DM8", SQL_NTS, (SQLCHAR*)"SYSDBA", SQL_NTS, (SQLCHAR*)"SYSDBA123", SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
std::cout << "Successfully connected to the database." << std::endl;
// Print connection information
print_connection_info(hDbc);
// Print current date from database
print_current_date(hDbc);
} else {
std::cout << "Error connecting to database." << std::endl;
extract_error("SQLConnect", hDbc, SQL_HANDLE_DBC);
}
// Disconnect and free handles
SQLDisconnect(hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
}
int main() {
Conn conn;
conn.connect_to_db();
return 0;
}
（2）代码编译
[root@oracle odbc_connect] ll
total 8
-rw-r--r-- 1 root root 4143 Jun 18 19:02 odbc_connect.cpp
[root@oracle odbc_connect] g++ -o odbc_connect odbc_connect.cpp -lodbc ##编译源码
[root@oracle odbc_connect] ll
total 24
-rwxr-xr-x 1 root root 14104 Jun 18 19:03 odbc_connect
-rw-r--r-- 1 root root 4143 Jun 18 19:02 odbc_connect.cpp
三、问题排查及解决
1、问题复现
（1）使用ISQL登录数据库
[root@oracle ~]# isql -v dm8
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| echo [string] |
| quit |
| |
+---------------------------------------+
SQL>
[root@oracle ~]# isql DM8 SYSDBA SYSDBA123
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| echo [string] |
| quit |
| |
+---------------------------------------+
SQL>
可以看到，无论是那种登录方式，均可以正常连接到数据库，也表名ODBC的配置没有问题。
（2）使用ODBC_CONNECT程序连接数据库
[root@oracle odbc_connect]# ll
total 24
-rwxr-xr-x 1 root root 14104 Jun 18 19:03 odbc_connect
-rw-r--r-- 1 root root 4143 Jun 18 19:02 odbc_connect.cpp
[root@oracle odbc_connect]# ./odbc_connect
Error connecting to database.
The driver reported the following diagnostics whilst running SQLConnect
IM002:1:0:[unixODBC][Driver Manager]Data source name not found, and no default driver specified
[root@oracle odbc_connect]# isql -v dm8
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| echo [string] |
| quit |
| |
+---------------------------------------+
SQL>
[root@oracle odbc_connect]# isql DM8 SYSDBA SYSDBA123
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| echo [string] |
| quit |
| |
+---------------------------------------+
SQL>
可以看到，此时程序去连接数据库时，提示失败。失败原因为DSN和驱动都要没有找到。
2、问题排查
首先可以确定，ODBC的配置没有问题，否则ISQL是连接不上的。现在就需要排查为什么程序这边会连不上。
（1）查看odbc.ini，显示正常：
[root@oracle odbc_connect]# cat /usr/local/etc/odbc.ini
[ODBC Data Sources]
DM8 = DM8 ODBC Driver
[DM8]
Description = DM ODBC DSN
Driver = DM8 ODBC DRIVER
SERVER = localhost
UID = SYSDBA
PWD = SYSDBA123
TCP_PORT = 5236
OPTION = 4
（2）查看odbcinst.ini，显示正常
[root@oracle odbc_connect]# cat /usr/local/etc/odbcinst.ini
[DM8 ODBC DRIVER]
Description=ODBC DRIVER FOR DM
Driver=/home/dmdba/dmdbms/bin/libdodbc.so
Trace=Yes
TraceFile=/var/log/odbc.log
（3）查看指定的驱动文件，依赖完整
[root@oracle odbc_connect]# ll /home/dmdba/dmdbms/bin/libdodbc.so
-rwxr-xr-x 1 dmdba dinstall 210099 Mar 22 08:59 /home/dmdba/dmdbms/bin/libdodbc.so
[root@oracle odbc_connect]# ldd /home/dmdba/dmdbms/bin/libdodbc.so
linux-vdso.so.1 => (0x00007ffc28d9f000)
libdmdpi.so => /home/dmdba/dmdbms/bin/libdmdpi.so (0x00007f025a7ed000)
libdmfldr.so => /home/dmdba/dmdbms/bin/libdmfldr.so (0x00007f025a4ba000)
librt.so.1 => /lib64/librt.so.1 (0x00007f025a2b2000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f025a096000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f0259e92000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f0259b8b000)
libm.so.6 => /lib64/libm.so.6 (0x00007f0259889000)
libc.so.6 => /lib64/libc.so.6 (0x00007f02594bc000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f02592a6000)
/lib64/ld-linux-x86-64.so.2 (0x00007f025b8d1000)
（4）查看ODBC安装路径
[root@oracle odbc_connect]# odbcinst -j
unixODBC 2.3.12
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
在其中可以看到，路径中出现了一个USER DATA SOURCES的项，其指向文件路径的为/root/.odbc.ini。
（5）查看/root/.odbc.ini的配置信息
[root@oracle odbc_connect]# cat /root/.odbc.ini
[root@oracle odbc_connect]#
可以看到在该隐藏文件中并无任何内容。
（6）尝试配置/root/.odbc.ini文件为准确的值
[root@oracle odbc_connect]# cat /root/.odbc.ini
[ODBC Data Sources]
DM8 = DM8 ODBC Driver
[DM8]
Description = DM ODBC DSN
Driver = DM8 ODBC DRIVER
SERVER = localhost
UID = SYSDBA
PWD = SYSDBA123
TCP_PORT = 5236
OPTION = 4
（7）检查程序和ISQL的连接情况
[root@oracle odbc_connect]# ./odbc_connect
Error connecting to database.
The driver reported the following diagnostics whilst running SQLConnect
IM002:1:0:[unixODBC][Driver Manager]Data source name not found, and no default driver specified
[root@oracle odbc_connect]# isql DM8 SYSDBA SYSDBA123
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| echo [string] |
| quit |
| |
+---------------------------------------+
SQL>
验证发现，还是和之前报了相同的错误原因。
（8）尝试修改/root/.odbcinst.ini文件为准确的值
[root@oracle odbc_connect]# cat /root/.odbcinst.ini
[DM8 ODBC DRIVER]
Description=ODBC DRIVER FOR DM
Driver=/home/dmdba/dmdbms/bin/libdodbc.so
Trace=Yes
TraceFile=/var/log/odbc.log
（9）再次检查程序和ISQL的连接情况
[root@oracle odbc_connect]# ll
total 24
-rwxr-xr-x 1 root root 14104 Jun 18 19:03 odbc_connect
-rw-r--r-- 1 root root 4143 Jun 18 19:02 odbc_connect.cpp
[root@oracle odbc_connect]# ./odbc_connect
Successfully connected to the database.
Connected to database with DSN: DM8, User: SYSDBA, IP: localhost, Port: 5236
Current database date: 2024-06-18 19:33:06
[root@oracle odbc_connect]# isql DM8 SYSDBA SYSDBA123
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| echo [string] |
| quit |
| |
+---------------------------------------+
SQL> SELECT SYSDATE FROM DUAL;
+--------------------+
| SYSDATE |
+--------------------+
| 2024-06-18 19:33:20|
+--------------------+
SQLRowCount returns 1
1 rows fetched
SQL>
至此，程序已经成功连接上数据库，执行并返回了查询数据库SYSDATE的值，ISQL正常连接数据库，查询也都正常，该问题得到解决。
四、总结
ODBC的配置要配二级，除了在ODBC的安装路径下需要正确配置，在用户的家下也需要配置。
4544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
