应用接入指南 - 2、C程序开发 - 《TBase 使用手册》

2、C程序开发

2、C程序开发

2.1、连接数据库

#include <stdio.h>  
#include <stdlib.h>  
#include "libpq-fe.h"     
int
main(int argc, char **argv){
    const char *conninfo;
    PGconn     *conn;      
    if (argc > 1){
        conninfo = argv[1];
    }else{
        conninfo = "dbname = postgres";  
    }            
    conn = PQconnectdb(conninfo);
    if (PQstatus(conn) != CONNECTION_OK){
        fprintf(stderr, "连接数据库失败: %s",PQerrorMessage(conn));              
    }else{
        printf("连接数据库成功！\n");
    }
    PQfinish(conn);
    return 0;
}

编译

gcc -c -I /usr/local/install/tbase_pgxz/include/ conn.c  
gcc -o conn conn.o -L /usr/local/install/tbase_pgxz/lib/ -lpq

运行

./conn "host=172.16.0.3 dbname=postgres port=11000"  
连接数据库成功！

./conn "host=172.16.0.3 dbname=postgres port=15432 user=tbase"   
连接数据库成功！

2.2、建立数据表

#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"   
int
main(int argc, char **argv){
    const char *conninfo;
    PGconn     *conn;      
    PGresult   *res;
    const char *sql = "create table tbase(id int,nickname text) distribute by shard(id) to group  default_group";
    if (argc > 1){
        conninfo = argv[1];
    }else{
        conninfo = "dbname = postgres";           
    }        
    conn = PQconnectdb(conninfo);
    if (PQstatus(conn) != CONNECTION_OK){
        fprintf(stderr, "连接数据库失败: %s",PQerrorMessage(conn));              
    }else{
        printf("连接数据库成功！\n");
    }
    res = PQexec(conn,sql);
    if(PQresultStatus(res) != PGRES_COMMAND_OK){
        fprintf(stderr, "建立数据表失败: %s",PQresultErrorMessage(res)); 
    }else{
        printf("建立数据表成功！\n");
    }
    PQclear(res);
    PQfinish(conn);
    return 0;
}

编译

gcc -c -I /usr/local/install/tbase_pgxz/include/ createtable.c  
gcc -o createtable createtable.o -L /usr/local/install/tbase_pgxz/lib/ -lpq

运行

./createtable "port=11000 dbname=postgres"
连接数据库成功！  
建立数据表成功！

2.3、插入数据

#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"   
int
main(int argc, char **argv){
    const char *conninfo;
    PGconn     *conn;      
    PGresult   *res;
    const char *sql = "INSERT INTO tbase (id,nickname) values(1,'tbase'),(2,'pgxz')";
    if (argc > 1){
        conninfo = argv[1];
    }else{
        conninfo = "dbname = postgres";           
    }        
    conn = PQconnectdb(conninfo);
    if (PQstatus(conn) != CONNECTION_OK){
        fprintf(stderr, "连接数据库失败: %s",PQerrorMessage(conn));              
    }else{
        printf("连接数据库成功！\n");
    }
    res = PQexec(conn,sql);
    if(PQresultStatus(res) != PGRES_COMMAND_OK){
        fprintf(stderr, "插入数据失败: %s",PQresultErrorMessage(res)); 
    }else{
        printf("插入数据成功！\n");
    }
    PQclear(res);
    PQfinish(conn);
    return 0;
}

编译

gcc -c -I /usr/local/install/tbase_pgxz/include/ insert.c
gcc -o insert insert.o -L /usr/local/install/tbase_pgxz/lib/ -lpq

运行

./insert "dbname=postgres port=15432"

2.4、查询数据

#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"   
int
main(int argc, char **argv){
    const char *conninfo;
    PGconn     *conn;      
    PGresult   *res;
    const char *sql = "select * from tbase";
    if (argc > 1){
        conninfo = argv[1];
    }else{
        conninfo = "dbname = postgres";           
    }
    conn = PQconnectdb(conninfo);    
    if (PQstatus(conn) != CONNECTION_OK){
        fprintf(stderr, "连接数据库失败: %s",PQerrorMessage(conn));              
    }else{    
        printf("连接数据库成功！\n");
    }                                
    res = PQexec(conn,sql);
    if(PQresultStatus(res) != PGRES_TUPLES_OK){
        fprintf(stderr, "插入数据失败: %s",PQresultErrorMessage(res)); 
    }else{
        printf("查询数据成功！\n");    
        int rownum = PQntuples(res) ;
        int colnum = PQnfields(res);
        for(int j = 0;j< colnum; ++j){
            printf("%s\t",PQfname(res,j));
        }
        printf("\n");
        for(int i = 0;i< rownum; ++i){
            for(int j = 0;j< colnum; ++j){
                printf("%s\t",PQgetvalue(res,i,j));
            }
            printf("\n");
        }
    }
    PQclear(res);
    PQfinish(conn);
    return 0;
}

编译

gcc -std=c99 -c -I /usr/local/install/tbase_pgxz/include/ select.c  
gcc -o select select.o -L /usr/local/install/tbase_pgxz/lib/ -lpq

运行

./select "dbname=postgres port=15432"
连接数据库成功！  
查询数据成功！    
id      nickname  
1       tbase  
2       pgxz

2.5、流数据COPY入表

#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"   
int 
main(int argc, char **argv){
    const char *conninfo;
    PGconn     *conn;      
    PGresult   *res;
    const char *buffer = "1,tbase\n2,pgxz\n3,Tbase牛";
    if (argc > 1){
        conninfo = argv[1];
    }else{
        conninfo = "dbname = postgres";           
    }
    conn = PQconnectdb(conninfo);
    if (PQstatus(conn) != CONNECTION_OK){
        fprintf(stderr, "连接数据库失败: %s",PQerrorMessage(conn));              
    }else{
        printf("连接数据库成功！\n");
    }
    res=PQexec(conn,"COPY tbase FROM STDIN DELIMITER ',';");
    if(PQresultStatus(res) != PGRES_COPY_IN){
        fprintf(stderr, "copy数据出错1: %s",PQresultErrorMessage(res));
    }else{
        int len = strlen(buffer);
        if(PQputCopyData(conn,buffer,len) == 1){
             if(PQputCopyEnd(conn,NULL) == 1){
                res = PQgetResult(conn);
                if(PQresultStatus(res) == PGRES_COMMAND_OK){
                    printf("copy数据成功！\n");         
                }else{
                    fprintf(stderr, "copy数据出错2: %s",PQerrorMessage(conn));    
                }
             }else{
                fprintf(stderr, "copy数据出错3: %s",PQerrorMessage(conn));   
             }
        }else{
            fprintf(stderr, "copy数据出错4: %s",PQerrorMessage(conn));              
        }
    }
    PQclear(res);
    PQfinish(conn);
    return 0;
}

编译

gcc -c -I /usr/local/install/tbase_pgxz/include/ copy.c
gcc -o copy copy.o -L /usr/local/install/tbase_pgxz/lib/ -lpq

执行

./copy "dbname=postgres port=15432"
连接数据库成功！  
copy数据成功！

3、shell脚本开发

#!/bin/sh
if [ $# -ne 0 ]
then
    echo "usage: $0 exec_sql"
    exit 1
fi
exec_sql=$1
masters=`psql -h 172.16.0.29 -d postgres -p 15432 -t -c "select string_agg(node_host, ' ') from (select * from pgxc_node where node_type = 'D' order by node_name) t"`
port_list=`psql -h 172.16.0.29 -d postgres -p 15432 -t -c "select string_agg(node_port::text, ' ') from (select * from pgxc_node where node_type = 'D' order by node_name) t"`
node_cnt=`psql -h 172.16.0.29 -d postgres -p 15432 -t -c "select count(*) from pgxc_node where node_type = 'D'"`
masters=($masters)
ports=($port_list)
echo $node_cnt
flag=0
for((i=0;i<$node_cnt;i++));
do
    seq=$(($i+1))
    master=${masters[$i]}
    port=${ports[$i]}
    echo $master
    echo $port
    psql -h $master -p $port  postgres -c "$exec_sql"
done