创建表、插入数据并查询

前提条件：需要提前创建testdb数据库。

在数据库连接过程中，若连接语句中指定了ip-port，则必须指定username/password，该规则由GaussDB内核通信认证所决定。若不指定ip-port，则通过本地$PGPORT（UDS协议）进行通信。

以下介绍几种常用连接数据库的方式（详情请参考管理数据库连接，CONNECT）：

/* 连接语句涉及数据库、用户、密码。需提前创建好并有相关操作权限。 */

/* 连接方式：EXEC SQL CONNECT TO [ database_name ][ @host ][ :port ] [ USER connection_user ] */
/* case1: 使用默认的本地连接方式，连接数据库为gaussdb库。 */
EXEC SQL CONNECT TO gaussdb;

/* case2：使用默认的本地连接方式，连接数据库为gaussdb库，连接别名为conn1。 */
EXEC SQL CONNECT TO gaussdb AS conn1;

/* case3：使用ip+port方式（localhost数据库监听的本地地址，$PORT为数据库监听端口），连接数据库为connectdb库，指定数据库别名，指定用户密码。$PORT换成真实的端口号。 */
EXEC SQL CONNECT TO connectdb@localhost:$PORT AS conn2 USER connectuser USING :pw;

/* case4：使用ip+port方式（127.0.0.1数据库监听的本地地址，$PORT为数据库监听端口），连接数据库为connectdb库，指定数据库别名，指定用户密码。 */
EXEC SQL CONNECT TO connectdb@127.0.0.1:$PORT AS conn3 USER connectuser SQLIDENTIFIED BY :pw;

此示例演示如何使用ecpg创建表、插入数据并查询数据。

#include <locale.h>
#include <string.h>
#include <stdlib.h>
#include <stdio.h>

EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL INCLUDE sqlca;

int main(void)
{
    EXEC SQL BEGIN DECLARE SECTION;
    char *temp_str = (char *)malloc(11);
    EXEC SQL END DECLARE SECTION;

    /* 提前创建testdb库 */
    EXEC SQL CONNECT TO testdb;

    /* 打开自动提交，以下执行EXEC SQL时不用手动COMMIT */
    EXEC SQL SET AUTOCOMMIT = ON;
    EXEC SQL DROP TABLE IF EXISTS test_t;

    /* 建表，插入数据 */
    EXEC SQL CREATE TABLE test_t(f FLOAT, i INT, a INT[10], mstr CHAR(10));
    EXEC SQL INSERT INTO test_t(f, i, a, mstr) VALUES(1.01, 1, '{0,1,2,3,4,5,6,7,8,9}', 'China');

    /* 关闭自动提交，以下插入数据的SQL语句需要手动COMMIT才能提交 */
    EXEC SQL SET AUTOCOMMIT = OFF;
    EXEC SQL INSERT INTO test_t(f, i, a, mstr) VALUES(2.01, 2, '{0,1,2,3,4,5,6,7,8,9}', 'USA');
    EXEC SQL COMMIT;

    EXEC SQL INSERT INTO test_t(f, i, a, mstr) VALUES(3.01, 3, '{0,1,2,3,4,5,6,7,8,9}', 'AUS');
    EXEC SQL INSERT INTO test_t(f, i, a, mstr) VALUES(4.01, 4, '{0,1,2,3,4,5,6,7,8,9}', 'JAP');
    EXEC SQL COMMIT;

    EXEC SQL BEGIN DECLARE SECTION;
    int a[10] = {9, 8, 7, 6, 5, 4, 3, 2, 1, 0};
    int id = 6;
    EXEC SQL END DECLARE SECTION;

    /* 从宿主变量取数据插入到表中，宿主变量的类型与表定义的类型一致 */
    strcpy(temp_str, "RUS");
    EXEC SQL INSERT INTO test_t(f, i, a, mstr) VALUES(5.01, 5, :a, :temp_str);
    EXEC SQL COMMIT;

    EXEC SQL SET AUTOCOMMIT = ON;
    EXEC SQL BEGIN;
    EXEC SQL INSERT INTO test_t(f, i, a, mstr) VALUES(6.01, :id, :a, 'SIG');
    EXEC SQL COMMIT;
    EXEC SQL SET AUTOCOMMIT = OFF;

    EXEC SQL BEGIN DECLARE SECTION;
    float ff = 0.0f;
    char tmp_text[25] = "klmnopqrst";
    EXEC SQL END DECLARE SECTION;

    EXEC SQL SET AUTOCOMMIT = ON;
    EXEC SQL BEGIN WORK;

    printf("Found ff=%f tmp_text=%10.10s\n", ff, tmp_text);

    /* 条件查询语句示例 */
    EXEC SQL SELECT f, mstr INTO :ff, :tmp_text FROM test_t WHERE f > (SELECT f FROM test_t WHERE i = 4 OR i < 0) ORDER BY a LIMIT 1;
    printf("Found ff=%f tmp_text=%10.10s\n", ff, tmp_text);

    EXEC SQL SELECT f, mstr INTO :ff, :tmp_text FROM test_t WHERE mstr = 'JAP' ORDER BY i;
    printf("Found ff=%f tmp_text=%10.10s\n", ff, tmp_text);

    EXEC SQL SELECT f, mstr INTO :ff, :tmp_text FROM test_t ORDER BY i DESC LIMIT 1;
    printf("Found ff=%f tmp_text=%10.10s\n", ff, tmp_text);

    EXEC SQL SELECT f, mstr INTO :ff, :tmp_text FROM test_t ORDER BY mstr LIMIT 1;
    printf("Found ff=%f tmp_text=%10.10s\n", ff, tmp_text);

    EXEC SQL SELECT COUNT(f), a INTO :ff, :tmp_text FROM test_t WHERE i > 2 GROUP BY a LIMIT 1;
    printf("Found ff=%f tmp_text=%20.30s\n", ff, tmp_text);

    EXEC SQL SELECT COUNT(f), a INTO :ff, :tmp_text FROM test_t WHERE i > 3 GROUP BY a ORDER BY a LIMIT 1;
    printf("Found ff=%f tmp_text=%20.30s\n", ff, tmp_text);

    EXEC SQL SELECT SUM(f), a INTO :ff, :tmp_text FROM test_t WHERE i > 2 GROUP BY a ORDER BY a LIMIT 1;
    printf("Found ff=%f tmp_text=%20.30s\n", ff, tmp_text);

    EXEC SQL SELECT DISTINCT a INTO :tmp_text FROM test_t ORDER BY a LIMIT 1;

    EXEC SQL DROP TABLE test_t;

    EXEC SQL COMMIT;
    /* 释放连接，释放为宿主变量分配的内存 */
    EXEC SQL DISCONNECT;
    free(temp_str);

    return 0;
}

运行结果如下：

Found ff=0.000000 tmp_text=klmnopqrst
Found ff=5.010000 tmp_text=RUS
Found ff=4.010000 tmp_text=JAP
Found ff=6.010000 tmp_text=SIG
Found ff=3.010000 tmp_text=AUS
Found ff=2.000000 tmp_text={9,8,7,6,5,4,3,2,1,0}
Found ff=1.000000 tmp_text={0,1,2,3,4,5,6,7,8,9}
Found ff=7.020000 tmp_text={0,1,2,3,4,5,6,7,8,9}