TDSQL PostgreSQL版使用 Pro*C 连接 TD

本文介绍如何使用 Pro * C 示例程序连接及操作数据库。
获取数据库连接信息
联系数据库管理员获取数据库连接命令：
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:$PATH
export 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.30
port=11345
db=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 31
﻿
void 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***********
使用 Pro*C 连接 TDSQL PG

获取数据库连接信息

安装驱动依赖

编写 Pro/C 测试程序
