TDSQL PostgreSQL版使用 Pro*C 连接 TDSQL PG

一、获取数据库连接信息

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

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

参数说明：

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

·-p 数据库端口。

·-U 数据库用户。

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

二、安装驱动依赖

1. 下载驱动：请单击 TDSQL-PG Pro/C 驱动包下载。

2. 安装

rpm -ivh tdsql_proc-1.1.0-1.x86_64.rpm

软件包会默认安装在 /usr/local/tdsql_proc_pg 目录下，目录中有以下文件：

·预编译程序 /usr/local/tdsql_proc_pg/bin/tdsql_proc

·头文件 /usr/local/tdsql_proc_pg/include

·运行库 /usr/local/tdsql_proc_pg/lib/libtdsqlproc.so 包含依赖库 libssl.so.10和 libcrypto.so.10

·连接配置模板 /usr/local/tdsql_proc_pg/tdsql_proc.cnf

3. 设置环境变量，在操作用户目录添加环境变量，编辑 ~/.bashrc。

export PATH=/usr/local/tdsql_proc_pg/bin:$PATHexport LD_LIBRARY_PATH=/usr/local/tdsql_proc_pg/lib:$LD_LIBRARY_PATH

4. 环境变量生效

source ~/.bashrc

5. 连接配置文件
/usr/local/tdsql_proc_pg/tdsql_proc.cnf 文件中保存了数据库连接信息，输入如下内容：

[db_test]host=10.211.55.30port=11345db=tdsql

·db_testCONNECT 命令中指定的服务名

·hosttdsql 数据库的 ip 地址

·porttdsql 数据库的端口号

·dbtdsql 数据库的数据库名

三、编写 Pro/C 测试程序

示例程序：demo.pc

/*——————————————————————– * demo.pc is an example Pro*C program*——————————————————————–*/#include <stdio.h>#include <sqlca.h>#include <string.h>#include <setjmp.h>#include <stdlib.h>#include <sqlcpr.h>#include <sqlca.h>#define MAX_VAR_LEN 255#define MAX_NAME_LEN 31void sqlerror();/* 变量声明 */EXEC SQL BEGIN DECLARE SECTION;char *username = "tbase";char *password = "1234abcd";char *service = "db_test";EXEC SQL END DECLARE SECTION;EXEC SQL WHENEVER SQLERROR DO sqlerror(); /* 宿主变量类型 */void variable_test(){ EXEC SQL BEGIN DECLARE SECTION; int a; unsigned int b; char c = 'a'; unsigned char d = 'b'; char *e = "hello"; char f[20] = "world"; long g = 3; unsigned long h = 4; float i = 3.14; double j = 5.4321; varchar k[100]; EXEC SQL END DECLARE SECTION;}/* 创建表 */void create_table_demo_test(){ EXEC SQL BEGIN DECLARE SECTION; char *sql1 = "DROP TABLE IF EXISTS demo_test"; char *sql2 = "CREATE TABLE demo_test (id int, name varchar(100), address varchar(100))"; EXEC SQL END DECLARE SECTION; printf("***********create_table_demo_test start***********\\n"); EXEC SQL PREPARE stat FROM :sql1; EXEC SQL EXECUTE stat; EXEC SQL PREPARE stat FROM :sql2; EXEC SQL EXECUTE stat; EXEC SQL COMMIT; printf("***********create_table_demo_test success***********\\n"); printf ("\\n");}/* SELECT */void select_table_demo_test(){ EXEC SQL BEGIN DECLARE SECTION; long id; int test_id = 2; varchar name[100]; char address[100]; short ind; EXEC SQL END DECLARE SECTION; printf("***********select_table_demo_test start***********\\n"); EXEC SQL SELECT id,name,address INTO :id, :name, :address:ind from demo_test where id = :test_id; name.arr[name.len] = '\\0'; printf("id:%d, name:%s, address:%s\\n", id, name.arr, ind != -1 ? address : "NULL"); printf("***********select_table_demo_test success***********\\n"); printf ("\\n");}/* INSERT */void insert_table_demo_test(){ EXEC SQL BEGIN DECLARE SECTION; int test_id = 2; char name[100] = "FirstMan"; char address[100] = "EARTH"; EXEC SQL END DECLARE SECTION; printf("***********insert_table_demo_test start***********\\n"); EXEC SQL INSERT INTO demo_test values(:test_id, :name, :address); EXEC SQL COMMIT; printf("insert demo_test values(%d,%s,%s)\\n", test_id, name, address); printf("***********insert_table_demo_test success***********\\n"); printf ("\\n");}/* UPDATE */void update_table_demo_test(){ EXEC SQL BEGIN DECLARE SECTION; int test_id = 2; char address[100] = "AAAAA"; short ind = -1; EXEC SQL END DECLARE SECTION; printf("***********update_table_demo_test start***********\\n"); EXEC SQL UPDATE demo_test SET address = :address where id = :test_id; EXEC SQL COMMIT; printf("update demo_test address to %s\\n",address); printf("***********update_table_demo_test success***********\\n"); printf ("\\n");}/* DELETE */void delete_table_demo_test(){ EXEC SQL BEGIN DECLARE SECTION; int test_id = 2; EXEC SQL END DECLARE SECTION; printf("***********delete_table_demo_test start***********\\n"); EXEC SQL DELETE FROM demo_test where id = :test_id; EXEC SQL COMMIT; printf("***********delete_table_demo_test success***********\\n"); printf ("\\n");}/* COMMIT 已经包含在其他用例，ROLLBACK */void rollback_demo_test(){ EXEC SQL BEGIN DECLARE SECTION; int test_id = 2; char name[100] = "ROLLBACK"; char address[100] = "ROLLBACK"; EXEC SQL END DECLARE SECTION; printf("***********rollback_demo_test start***********\\n"); printf("insert demo_test values(%d, %s, %s) rollback\\n",test_id, name, address); EXEC SQL INSERT INTO demo_test values(:test_id, :name, :address); EXEC SQL ROLLBACK; printf("***********rollback_demo_test success***********\\n"); printf ("\\n");}/* 使用游标，将结果集保存在变量中 */void select_table_with_cursor_demo_test(){ EXEC SQL BEGIN DECLARE SECTION; int id; varchar name[50]; char address[50]; EXEC SQL END DECLARE SECTION;printf("***********select_table_with_cursor_demo_test start***********\\n"); EXEC SQL DECLARE cur CURSOR for select id,name,address from demo_test; EXEC SQL OPEN cur; EXEC SQL FETCH cur INTO :id, :name, :address; name.arr[name.len] = '\\0'; printf("id:%d, name:%s, address:%s\\n", id, name.arr, address); EXEC SQL CLOSE cur;printf("***********select_table_with_cursor_demo_test success***********\\n"); printf ("\\n");}/* 简单动态 SQL */void simple_dynamic_SQL_test(){ EXEC SQL BEGIN DECLARE SECTION; char * sql = "insert into simple_dynamic_table(c1,c2) values (1, 2)"; char *sql1 = "DROP TABLE IF EXISTS simple_dynamic_table"; char *sql2 = "CREATE TABLE simple_dynamic_table (c1 int,c2 int)"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE stat FROM :sql1; EXEC SQL EXECUTE stat; EXEC SQL PREPARE stat FROM :sql2; EXEC SQL EXECUTE stat; printf("***********simple_dynamic_SQL_test start***********\\n"); printf("execute sql is %s\\n", sql); EXEC SQL EXECUTE IMMEDIATE :sql; EXEC SQL COMMIT; printf("***********simple_dynamic_SQL_test success***********\\n"); printf ("\\n");}/* ANSI 动态 SQL，使用游标将结果集保存在 DESCRIPTOR 中 */void ANSI_dynamic_SQL_test(){ int i; EXEC SQL BEGIN DECLARE SECTION; char *sql1 = "DROP TABLE IF EXISTS ANSI_dynamic_table"; char *sql2 = "CREATE TABLE ANSI_dynamic_table (c1 int, c2 int, c3 int)"; int c1val = 1; int type = 3; int len = sizeof(int); int input_count, output_count, occurs; char name[31]; int out_data; char out_name[MAX_NAME_LEN] = {0}; short indi; char dyn_statement[1024] = "select c2,c3 from ANSI_dynamic_table where c1=:c1val"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE stat FROM :sql1; EXEC SQL EXECUTE stat; EXEC SQL PREPARE stat FROM :sql2; EXEC SQL EXECUTE stat; printf("***********ANSI_dynamic_SQL_test start***********\\n"); EXEC SQL ALLOCATE DESCRIPTOR 'in_desc'; EXEC SQL ALLOCATE DESCRIPTOR 'out_desc'; EXEC SQL INSERT INTO ANSI_dynamic_table VALUES ( 1, 2, 3); EXEC SQL INSERT INTO ANSI_dynamic_table VALUES ( 1, 2, 2); EXEC SQL INSERT INTO ANSI_dynamic_table VALUES ( 2, 1, 1); EXEC SQL INSERT INTO ANSI_dynamic_table(c1) VALUES (1); EXEC SQL COMMIT; /* 通过 DESCRIPTOR 给 SQL 传入变量（c1val） */ EXEC SQL PREPARE S from :dyn_statement; EXEC SQL DECLARE C CURSOR for S; EXEC SQL DESCRIBE INPUT S USING SQL DESCRIPTOR 'in_desc'; EXEC SQL GET DESCRIPTOR 'in_desc' :input_count = COUNT; for (i=0; i < input_count; i++) { occurs = i +1; /* occurence is 1 based */ EXEC SQL SET DESCRIPTOR 'in_desc' VALUE :occurs TYPE = :type, LENGTH = :len, DATA = :c1val; } EXEC SQL OPEN C USING DESCRIPTOR 'in_desc'; /* 通过 DESCRIPTOR 设定 SQL 输出变量属性 */ EXEC SQL DESCRIBE OUTPUT S USING DESCRIPTOR 'out_desc'; EXEC SQL GET DESCRIPTOR 'out_desc' :output_count = COUNT; for (i = 0; i < output_count; i++) { occurs = i + 1; EXEC SQL GET DESCRIPTOR 'out_desc' VALUE :occurs :out_name = NAME; EXEC SQL SET DESCRIPTOR 'out_desc' VALUE :occurs TYPE = :type, LENGTH = :len; printf("%-*.*s", 9,9, out_name); } printf("\\n"); /* 通过 DESCRIPTOR 获取 SQL 输出变量值 */ EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop; for (;;) { EXEC SQL FETCH C INTO DESCRIPTOR 'out_desc'; for (i=0; i < output_count; i++) { occurs = i + 1; EXEC SQL GET DESCRIPTOR 'out_desc' VALUE :occurs :out_data = DATA, :indi = INDICATOR; if (indi == -1) printf("%-*.*s", 9,9, "NULL"); else printf("%-*d", 9, out_data); } printf ("\\n"); }end_select_loop: EXEC SQL CLOSE C; EXEC SQL DEALLOCATE DESCRIPTOR 'in_desc'; EXEC SQL DEALLOCATE DESCRIPTOR 'out_desc'; printf("***********ANSI_dynamic_SQL_test success***********\\n"); printf ("\\n"); return;}void sqlerror() { printf("Stop Error:\\t%25i, %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; exit(-1);}void main() { /* 建立连接 */ EXEC SQL CONNECT :username identified by :password using :service; printf("connect success\\n"); printf ("\\n"); /* 测试 */ variable_test(); create_table_demo_test(); insert_table_demo_test(); select_table_demo_test(); update_table_demo_test(); select_table_demo_test(); rollback_demo_test(); select_table_with_cursor_demo_test(); delete_table_demo_test(); simple_dynamic_SQL_test(); ANSI_dynamic_SQL_test(); return;}

1. 修改示例代码中 username、password、service 三个变量为用户名、密码、在 tdsql_proc.cnf 配置的数据源名称。

2. 预编译

/usr/local/tdsql_proc_pg/bin/tdsql_proc iname=demo.pc oname=demo.c conn_config=/usr/local/tdsql_proc_pg/tdsql_proc.cnf

3. 编译

gcc demo.c -o test -I /usr/local/tdsql_proc_pg/include -L /usr/local/tdsql_proc_pg/lib -ltdsqlproc

4. 执行

./test

5. 结果

connect success***********create_table_demo_test start**********************create_table_demo_test success**********************insert_table_demo_test start***********insert demo_test values(2,FirstMan,EARTH)***********insert_table_demo_test success**********************select_table_demo_test start***********id:2, name:FirstMan, address:EARTH***********select_table_demo_test success**********************update_table_demo_test start***********update demo_test address to AAAAA***********update_table_demo_test success**********************select_table_demo_test start***********id:2, name:FirstMan, address:AAAAA***********select_table_demo_test success**********************rollback_demo_test start***********insert demo_test values(2, ROLLBACK, ROLLBACK) rollback***********rollback_demo_test success**********************select_table_with_cursor_demo_test start***********id:2, name:FirstMan, address:AAAAA***********select_table_with_cursor_demo_test success**********************delete_table_demo_test start**********************delete_table_demo_test success**********************simple_dynamic_SQL_test start***********execute sql is insert into simple_dynamic_table(c1,c2) values (1, 2)***********simple_dynamic_SQL_test success**********************ANSI_dynamic_SQL_test start***********c2 c3 27 37 27 27 NULL NULL ***********ANSI_dynamic_SQL_test success***********

TDSQL PostgreSQL版 使用 Pro*C 连接 TDSQL PG

一、获取数据库连接信息

二、安装驱动依赖

三、编写 Pro/C 测试程序

相关内容：

TDSQL PostgreSQL版使用 Pro*C 连接 TDSQL PG