TDSQL PostgreSQL版使用 ODBC 连接 TDSQL PG

一、获取数据库连接信息

联系数据库管理员获取数据库连接命令：

psql -h 10.211.55.30 -p 11345 -U tbase -d tdsql

参数说明：

·-h 数据库 IP，通常是 CN 的 IP。

·-p 数据库端口。

·-U 数据库用户。

·-d 需要访问的数据库名称。

二、安装驱动依赖

操作系统上使用 ODBC 驱动依赖于 UnixODBC 管理，需要先在系统中安装 UnixODBC。

三、UnixODBC 安装

假设安装路径为 UNIXODBC_PATH。

注意：UnixODBC 版本必须为2.3.7，否则会导致无法连接，可参考源码包安装。

1. 下载 unixODBC

页面地址unixODBC：https://www.unixodbc.org/unixODBC-2.3.7.tar.gz

2. 安装

2.1 将 tar 包上传到服务器目录，例如：/usr/local，进入该目录解压安装包：

tar -xvf unixODBC*.tar.gz

2.2 编译（进入到解压后的目录，以下命令依次执行）

./configure –prefix=$UNIXODBC_PATHmakemake install

2.3 配置环境变量，编辑 ~/.bashrc。

export PATH=$UNIXODBC_PATH/bin:$PATHexport LD_LIBRARY_PATH=$UNIXODBC_PATH/lib:$LD_LIBRARY_PATH

2.4 环境变量生效

source ~/.bashrc

2.5 测试

odbcinst -j unixODBC 2.3.7 DRIVERS…………: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /etc/odbc.ini SQLULEN Size…….: 8 SQLLEN Size……..: 8 SQLSETPOSIROW Size.: 8

四、ODBC 驱动安装和配置

1. 下载驱动：请单击 ODBC 驱动包下载。

2. 安装

2.1 将 tar 包上传到服务器解压：

tar -xvf tdsql_psqlodbc*.tar.gz

解压 TDSQL ODBC 驱动包，假设解压路径为$PG_ODBC_PATH

2.2 配置环境变量，编辑 ~/.bashrc。

export LD_LIBRARY_PATH=$PG_ODBC_PATH:$LD_LIBRARY_PATH

2.3 环境变量生效

source ~/.bashrc

3. 手动配置 ODBC 数据源

3.1 编辑 /etc/odbcinst.ini，输入如下内容：

[TDSQL_PG]Description = ODBC for TDSQL PostgreSQLDriver = $PG_ODBC_PATH/psqlodbcw.soFileUsage = 1

注意：/etc/odbcinst.ini 是通过 odbcinst -j 命令获取的路径，以实际查询配置文件为准。

3.2 编辑 /etc/odbc.ini，输入如下内容：

[PG_CON]Driver=TDSQL_PGServername=10.211.55.30Port=11345Username=tbasePassword=1234abcdDatabase=tdsql

参数说明：

·Driver 驱动名称，在文件 /etc/odbcinst.ini 中声明。

·Servername 服务器名称 /IP。

·Port 数据库端口。

·Username 数据库用户。

·Password 用户密码。

·Database 需要访问的数据库名称。

注意：请按照实际情况进行字段值的修改，驱动动态库路径依据实际路径填写。

校验驱动库已经包含依赖：

ldd $PG_ODBC_PATH/psqlodbcw.so

链接不出现 not found 则为成功。

4. 测试连接

可以使用 unixODBC 自带的 isql 工具进行检测，执行：

五、编写 ODBC 测试程序

示例程序：test_odbc.c

#include <stdio.h>#include <stdlib.h>#include <sql.h>#include <sqlext.h>// 定义连接字符串#define DSN "DSN=PG_CON"voidprint_diag(char *msg, SQLSMALLINT htype, SQLHANDLE handle){ char sqlstate[32]; char message[1000]; SQLINTEGER nativeerror; SQLSMALLINT textlen; SQLRETURN ret; SQLSMALLINT recno = 0; if (msg) printf("%s\\n", msg); do { recno++; ret = SQLGetDiagRec(htype, handle, recno, sqlstate, &nativeerror, message, sizeof(message), &textlen); if (ret == SQL_INVALID_HANDLE) printf("Invalid handle\\n"); else if (SQL_SUCCEEDED(ret)) printf("%s=%s\\n", sqlstate, message); } while (ret == SQL_SUCCESS); if (ret == SQL_NO_DATA && recno == 1) printf("No error information\\n");}// 创建表void create_table(SQLHDBC dbc) { SQLHSTMT stmt; SQLRETURN ret; ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to allocate statement handle\\n"); return; } ret = SQLExecDirect(stmt, (SQLCHAR*)"CREATE TABLE IF NOT EXISTS test_table (id SERIAL PRIMARY KEY, name VARCHAR(50), age INTEGER);", SQL_NTS); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to execute create table statement\\n"); } SQLFreeHandle(SQL_HANDLE_STMT, stmt);}// drop表void drop_table(SQLHDBC dbc) { SQLHSTMT stmt; SQLRETURN ret; ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to allocate statement handle\\n"); return; } ret = SQLExecDirect(stmt, (SQLCHAR*)"DROP TABLE test_table;", SQL_NTS); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to execute drop table statement\\n"); } SQLFreeHandle(SQL_HANDLE_STMT, stmt);}// 插入数据void insert_data(SQLHDBC dbc, const char* name, int age) { SQLHSTMT stmt; SQLRETURN ret; ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to allocate statement handle\\n"); return; } ret = SQLPrepare(stmt, (SQLCHAR*)"INSERT INTO test_table (name, age) VALUES (?, ?);", SQL_NTS); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to prepare insert statement\\n"); SQLFreeHandle(SQL_HANDLE_STMT, stmt); return; } ret = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0, (SQLPOINTER)name, 0, NULL); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to bind parameter 1\\n"); SQLFreeHandle(SQL_HANDLE_STMT, stmt); return; } ret = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)&age, 0, NULL); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to bind parameter 2\\n"); SQLFreeHandle(SQL_HANDLE_STMT, stmt); return; } ret = SQLExecute(stmt); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to execute insert statement\\n"); } SQLFreeHandle(SQL_HANDLE_STMT, stmt);}// 更新数据void update_data(SQLHDBC dbc, int id, const char* name, int age) { SQLHSTMT stmt; SQLRETURN ret; ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to allocate statement handle\\n"); return; } ret = SQLPrepare(stmt, (SQLCHAR*)"UPDATE test_table SET name = ?, age = ? WHERE id = ?;", SQL_NTS); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to prepare update statement\\n"); SQLFreeHandle(SQL_HANDLE_STMT, stmt); return; } ret = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0, (SQLPOINTER)name, 0, NULL); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to bind parameter 1\\n"); SQLFreeHandle(SQL_HANDLE_STMT, stmt); return; } ret = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)&age, 0, NULL); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to bind parameter 2\\n"); SQLFreeHandle(SQL_HANDLE_STMT, stmt); return; } ret = SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)&id, 0, NULL); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to bind parameter 3\\n"); SQLFreeHandle(SQL_HANDLE_STMT, stmt); return; } ret = SQLExecute(stmt); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to execute update statement\\n"); } SQLFreeHandle(SQL_HANDLE_STMT, stmt);}// 删除数据void delete_data(SQLHDBC dbc, int id) { SQLHSTMT stmt; SQLRETURN ret; ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to allocate statement handle\\n"); return; } ret = SQLPrepare(stmt, (SQLCHAR*)"DELETE FROM test_table WHERE id = ?;", SQL_NTS); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to prepare delete statement\\n"); SQLFreeHandle(SQL_HANDLE_STMT, stmt); return; } ret = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)&id, 0, NULL); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to bind parameter\\n"); SQLFreeHandle(SQL_HANDLE_STMT, stmt); return; } ret = SQLExecute(stmt); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to execute delete statement\\n"); } SQLFreeHandle(SQL_HANDLE_STMT, stmt);}// 查询数据void select_data(SQLHDBC dbc) { SQLHSTMT stmt; SQLRETURN ret; ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to allocate statement handle\\n"); return; } ret = SQLExecDirect(stmt, (SQLCHAR*)"SELECT * FROM test_table;", SQL_NTS); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to execute select statement\\n"); SQLFreeHandle(SQL_HANDLE_STMT, stmt); return; } SQLLEN id, age; SQLCHAR name[50]; while (SQLFetch(stmt) == SQL_SUCCESS) { SQLGetData(stmt, 1, SQL_C_LONG, &id, 0, NULL); SQLGetData(stmt, 2, SQL_C_CHAR, name, sizeof(name), NULL); SQLGetData(stmt, 3, SQL_C_LONG, &age, 0, NULL); printf("ID: %ld, Name: %s, Age: %ld\\n", id, name, age); } SQLFreeHandle(SQL_HANDLE_STMT, stmt);}int main() { SQLHENV env; SQLHDBC dbc; SQLRETURN ret; // 初始化环境句柄 ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to allocate environment handle\\n"); return 1; } // 设置环境属性 ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to set environment attribute\\n"); SQLFreeHandle(SQL_HANDLE_ENV, env); return 1; } // 分配连接句柄 ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc); if (ret != SQL_SUCCESS) { fprintf(stderr, "Failed to allocate connection handle\\n"); SQLFreeHandle(SQL_HANDLE_ENV, env); return 1; } // 建立连接 ret = SQLDriverConnect(dbc, NULL, (SQLCHAR*)DSN, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE); if (ret != SQL_SUCCESS) { print_diag("SQLDriverConnect failed.", SQL_HANDLE_DBC, dbc); SQLFreeHandle(SQL_HANDLE_DBC, dbc); SQLFreeHandle(SQL_HANDLE_ENV, env); return 1; } // 创建表 create_table(dbc); // 插入数据 insert_data(dbc, "John", 25); insert_data(dbc, "Alice", 30); // 查询数据 select_data(dbc); // 更新数据 update_data(dbc, 1, "John Doe", 26); // 删除数据 delete_data(dbc, 2); // 查询数据 select_data(dbc); // drop表 drop_table(dbc); // 断开连接 SQLDisconnect(dbc); // 释放连接句柄和环境句柄 SQLFreeHandle(SQL_HANDLE_DBC, dbc); SQLFreeHandle(SQL_HANDLE_ENV, env); return 0;}

编译：

gcc -o test_odbc -g test_odbc.c -I$UNIXODBC_PATH/include -L$UNIXODBC_PATH/lib -lodbc

运行：

./test_odbc

结果：

ID: 1, Name: John, Age: 25ID: 2, Name: Alice, Age: 30ID: 1, Name: John Doe, Age: 8589934618

TDSQL PostgreSQL版 使用 ODBC 连接 TDSQL PG