使用描述符范围

PostgreSQL 9.4.4 中文手册
上一页 | 上一级 | 章 33. ECPG - 在C中嵌入SQL | 下一页

# 33.7. 使用描述符范围

一个SQL描述符范围是处理`SELECT`, `FETCH`或者 `DESCRIBE`语句结果的更复杂的方法。 一个SQL描述符范围把一行数据里的数据和元数据项组合到一个数据结构中。 元数据在执行动态SQL语句时特别有用，那里的结果列的属性可能不能提前知道。 PostgreSQL提供了两种使用描述符范围的方法： 命名的SQL描述符范围和C结构SQLDAs。

## 33.7.1. 命名SQL描述符范围

一个命名SQL描述符范围由一个头组成， 包含有关整个描述符的信息，一个或多个项描述符范围， 基本上每个描述结果行中的一个字段。

在你使用SQL描述符范围之前，你需要分配一个：

    EXEC SQL ALLOCATE DESCRIPTOR identifier;

标示符用作描述符范围的"变量名"。 当你不再需要这个描述符，你应该释放它：

    EXEC SQL DEALLOCATE DESCRIPTOR identifier;

要使用一个描述符范围，在一个`INTO`子句的存储目标里声明它， 而不是列出宿主变量：

    EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc;

如果结果集是空，描述符范围将包含来自查询的元数据，即字段名称。

为了尚未执行的预备查询，`DESCRIBE`语句可用于获得结果集的元数据：

    EXEC SQL BEGIN DECLARE SECTION;
    char *sql_stmt = "SELECT * FROM table1";
    EXEC SQL END DECLARE SECTION;

    EXEC SQL PREPARE stmt1 FROM :sql_stmt;
    EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;

PostgreSQL 9.0之前，`SQL`关键字是可选的， 所以使用`DESCRIPTOR`和`SQL DESCRIPTOR` 产生命名SQL描述符范围。 现在，它是强制性的， 省略`SQL`关键词产生SQLDA描述符范围， 参阅第 33.7.2 节。

在`DESCRIBE`和`FETCH`语句中， `INTO`和`USING`关键字使用类似： 它们产生结果集合和描述符范围的元数据。

现在，我们应该如何从描述符范围里获取数据？ 你可以把描述符范围看作是一个有着命名字段的结构。 要从头检索字段数值并且把它存储到一个宿主变量里，使用下面的命令：

    EXEC SQL GET DESCRIPTOR name :hostvar = field;

目前只定义了一个头字段：`COUNT`， 这个字段告诉我们有几个项描述符范围存在（也就是说，在结果里包含多少个字段）。 宿主变量需要是一个整数类型。要从项描述符范围里获取一个字段， 使用下面的命令：

    EXEC SQL GET DESCRIPTOR name VALUE num :hostvar = field;

`num`可以是一个字符整数或者一个包含整数的宿主变量。 可能的字段有:

`CARDINALITY` (integer)

  结果集中的行数

`DATA`

  实际数据项（因此，这个字段的数据类型依赖于这个查询）

`DATETIME_INTERVAL_CODE` (integer)

  当`TYPE`是`9`的时候，那么 `DATETIME_INTERVAL_CODE`将有`DATE`的`1`值， `TIME`的`2`值， `TIMESTAMP`的`3`值， `TIME WITH TIME ZONE`的`4`值或者 `TIMESTAMP WITH TIME ZONE`的`5`值。

`DATETIME_INTERVAL_PRECISION` (integer)

  未实现。

`INDICATOR` (integer)

  描述符（标识一个空值或者一个截断的值）

`KEY_MEMBER` (integer)

  未实现

`LENGTH` (integer)

  字符中数据长度

`NAME` (string)

  字段名称

`NULLABLE` (integer)

  未实现

`OCTET_LENGTH` (integer)

  字节数据的字符表示的长度

`PRECISION` (integer)

  精度（类型`numeric`）

`RETURNED_LENGTH` (integer)

  字符中数据长度

`RETURNED_OCTET_LENGTH` (integer)

  字节数据的字符表示的长度

`SCALE` (integer)

  比例（类型`numeric`）

`TYPE` (integer)

  字段数据类型的数值代码

在`EXECUTE`, `DECLARE`和`OPEN` 语句中，`INTO`和`USING`关键字的作用是不同的。 描述符范围可以手动的编译，为一个查询或者游标提供输入参数， 并且`USING SQL DESCRIPTOR `name``是传递输入参数给一个参数化查询的一种方式。 编译命名SQL描述符范围的语句如下：

    EXEC SQL SET DESCRIPTOR name VALUE num field = :hostvar;

PostgreSQL支持检索更多的在一个`FETCH`语句中的记录和存储在宿主变量中的数据， 在这种情况下假设变量是一个数组。例如：

    EXEC SQL BEGIN DECLARE SECTION;
    int id[5];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc;

    EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA;

## 33.7.2. SQLDA描述符范围

SQLDA描述符范围是一个C语言结构， 它过去常常获取结果集和查询的元数据。 一个结构存储来自结果集中的一条记录。

    EXEC SQL include sqlda.h;
    sqlda_t         *mysqlda;

    EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda;

注意省略`SQL`关键字。 第 33.7.1 节中的`INTO`和`USING`关键字的 使用情况的段落有个例外， 也能适用于这里。在`DESCRIBE`语句中，如果使用了`INTO`关键字， 则`DESCRIPTOR`关键字完全省略。

    EXEC SQL DESCRIBE prepared_statement INTO mysqlda;

使用SQLDA程序流是：

1. 准备一个查询，并且为它声明一个游标。

2. 为结果行声明SQLDA。

3. 为输入参数声明SQLDA，并且初始化它们(内存分配，参数设置)。

4. 打开具有输入SQLDA的游标

5. 从游标中抓取行，并且将它们存储到输出SQLDA中。

6. 从输出SQLDA中读取值到宿主变量中 (如果有必要使用转换)。

7. 关闭游标。

8. 自由内存区域分配给输入SQLDA。

### 33.7.2.1. SQLDA数据结构

SQLDA使用三个数据结构类型：`sqlda_t`，`sqlvar_t`, 和`struct sqlname`。

> 提示: PostgreSQL的SQLDA与IBM DB2通用数据库中的一个有类似的数据结构。 所以DB2的SQLDA上的一些技术信息可以更好的帮助理解PostgreSQL的。

#### 33.7.2.1.1. sqlda_t 结构

结构类型`sqlda_t`是实际SQLDA的类型。 它拥有一条记录。并且在链表中使用`desc_next` 字段指针可以连接两个或更多个`sqlda_t`结构， 因此代表行的有序集合。因此，当抓取两个或更多行时， 应用程序通过每个`sqlda_t`节点 随后`desc_next`指针可以读取它们。

`sqlda_t`的定义是：

    struct sqlda_struct
    {
        char            sqldaid[8];
        long            sqldabc;
        short           sqln;
        short           sqld;
        struct sqlda_struct *desc_next;
        struct sqlvar_struct sqlvar[1];
    };

    typedef struct sqlda_struct sqlda_t;

该字段的意思是：

`sqldaid`

  它包含文本字符串`"SQLDA "`。

`sqldabc`

  它包含字节中分配空间的大小。

`sqln`

  它包含一个参数化查询的情况下的输入参数数， 使用`USING`关键字被传递给`OPEN`, `DECLARE`或者 `EXECUTE`语句。 在这种情况下它被作为`SELECT`, `EXECUTE`或者`FETCH`语句的输出使用。 它的值和`sqld`语句是一样的。

`sqld`

  它包含结果集中的字段数量。

`desc_next`

  如果查询返回多条记录，那么返回多个链接SQLDA结构， 并且`desc_next`持有指向列表中下一项的指针。

`sqlvar`

  这是结构集中列数组。

#### 33.7.2.1.2. sqlvar_t结构

结构类型`sqlvar_t`持有列值和元数据比如类型和长度。 该类型的定义是：

    struct sqlvar_struct
    {
        short          sqltype;
        short          sqllen;
        char          *sqldata;
        short         *sqlind;
        struct sqlname sqlname;
    };

    typedef struct sqlvar_struct sqlvar_t;

该字段的含义是：

`sqltype`

  包含该字段的类型标识符。 对于该值，参阅`ecpgtype.h`中的`enum ECPGttype`。

`sqllen`

  包含该字段的二进制长度。比如4字节的`ECPGt_int`。

`sqldata`

  指向该数据。关于数据的格式在第 33.4.4 节 中描述。

`sqlind`

  指向空指示器。0表示非空，-1表示空。

`sqlname`

  该字段名称。

#### 33.7.2.1.3. struct sqlname结构

`struct sqlname`结构持有列名。 它作为`sqlvar_t`结构成员被使用。 该结构定义是：

    #define NAMEDATALEN 64

    struct sqlname
    {
            short           length;
            char            data[NAMEDATALEN];
    };

该字段含义是：

`length`

  包含该字段名长度。

`data`

  包含实际字段名。

### 33.7.2.2. 使用SQLDA检索结果集

通过SQLDA检索查询结果集的一般步骤是：

1. 声明`sqlda_t`结构用来接收结果集。

2. 执行`FETCH`/`EXECUTE`/`DESCRIBE`命令用来处理指定已声明SQLDA的查询。

3. 通过查看`sqln`检查结果集中的记录数，`sqlda_t`结构成员。

4. 从`sqlvar[0]`, `sqlvar[1]`等中获得每列的值，`sqlda_t`结构成员

5. 通过`desc_next`指针转到下一行(`sqlda_t`结构)，`sqlda_t`结构成员。

6. 你需要重复以上步骤

这是一个通过SQLDA检索结果集的例子。

首先，声明一个`sqlda_t`结构以接收结果集。

    sqlda_t *sqlda1;

接下来，在命令中声明SQLDA。这是`FETCH`命令实例。

    EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;

在链接表后运行循环以检索行。

    sqlda_t *cur_sqlda;

    for (cur_sqlda = sqlda1;
         cur_sqlda != NULL;
         cur_sqlda = cur_sqlda->desc_next)
    {
        ...
    }

在循环中，运行另外一个循环以检索行中的每列数据（`sqlvar_t`结构）。

    for (i = 0; i < cur_sqlda->sqld; i++)
    {
        sqlvar_t v = cur_sqlda->sqlvar[i];
        char *sqldata = v.sqldata;
        short sqllen  = v.sqllen;
        ...
    }

为了得到列值，检查`sqltype`值，`sqlvar_t`结构成员。然后， 切换适当方式，依赖于列类型，从宿主变量`sqlvar`字段拷贝数据。

    char var_buf[1024];

    switch (v.sqltype)
    {
        case ECPGt_char:
            memset(&var_buf, 0, sizeof(var_buf));
            memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf) - 1 : sqllen));
            break;

        case ECPGt_int: /* integer */
            memcpy(&intval, sqldata, sqllen);
            snprintf(var_buf, sizeof(var_buf), "%d", intval);
            break;

        ...
    }

### 33.7.2.3. 使用SQLDA传递查询参数

使用SQLDA传递输入参数给预备查询的一般步骤是：

1. 创建预备查询(预备语句)

2. 作为输入SQLDA声明sqlda_t结构。

3. 为了输入SQLDA分配内存区域(作为sqlda_t结构)。

4. 在已分配内存中设置（拷贝）输入值。

5. 打开具有声明输入SQLDA的游标。

这有个例子。

首先，创建一个预备语句。

    EXEC SQL BEGIN DECLARE SECTION;
    char query[1024] = "SELECT d.oid, * FROM pg_database d, pg_stat_database s WHERE d.oid = s.datid AND (d.datname = ? OR d.oid = ?)";
    EXEC SQL END DECLARE SECTION;

    EXEC SQL PREPARE stmt1 FROM :query;

下一步，为SQLDA分配内存，并且在`sqln`中设置输入参数数， `sqlda_t`结构成员变量。 当预备查询需要两个或更多个输入参数的时候，应用程序必须分配额外内存空间，它 是通过(nr. of params - 1) * sizeof(sqlvar_t)计算的。 这里显示的是为两个输入参数分配内存空间的例子。

    sqlda_t *sqlda2;

    sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
    memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));

    sqlda2->sqln = 2; /* number of input variables */

内存分配后，存储参数值到`sqlvar[]`数组。 （当该SQLDA正在接收结果集时，这是用于检索列值的相同数组。） 在这个例子中，输入参数是有字符串类型的`"postgres"`， 以及有整数类型的`1`。

    sqlda2->sqlvar[0].sqltype = ECPGt_char;
    sqlda2->sqlvar[0].sqldata = "postgres";
    sqlda2->sqlvar[0].sqllen  = 8;

    int intval = 1;
    sqlda2->sqlvar[1].sqltype = ECPGt_int;
    sqlda2->sqlvar[1].sqldata = (char *) &intval;
    sqlda2->sqlvar[1].sqllen  = sizeof(intval);

打开游标并且声明事先准备的SQLDA，将输入参数传递给预备语句。

    EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;

最后，使用输入SQLDA之后，必须显式释放已分配内存空间，不像用于接收查询 结果的SQLDA。

    free(sqlda2);

### 33.7.2.4. 使用SQLDA示例应用程序

这是一个示例程序，描述了如何获取数据库访问统计，通过输入参数声明，来自系统表。

这个应用程序连接两个系统表，数据库OID上的pg_database和 pg_stat_database，并且读取、显示由两个输入参数（`postgres`和OID `1`）检索的数据库统计。

首先，为输入声明SQLDA，以及为输出声明SQLDA。

    EXEC SQL include sqlda.h;

    sqlda_t *sqlda1; /*输出描述符*/
    sqlda_t *sqlda2; /*输入描述符*/

下一步，连接数据库，准备语句，并且为预备语句声明游标。

    int
    main(void)
    {
        EXEC SQL BEGIN DECLARE SECTION;
        char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )";
        EXEC SQL END DECLARE SECTION;

        EXEC SQL CONNECT TO testdb AS con1 USER testuser;

        EXEC SQL PREPARE stmt1 FROM :query;
        EXEC SQL DECLARE cur1 CURSOR FOR stmt1;

接下来，为输入参数将一些值放在输入SQLDA中。为输入SQLDA分配内存，并且设置 输入参数数到`sqln`。存储类型，值以及值长度到`sqltype`，`sqldata`中， 并且将`sqllen`放在`sqlvar`结构中。

    /*为输入参数创建SQLDA结构 */
        sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
        memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));

    sqlda2->sqln = 2; /*输入变量数*/

        sqlda2->sqlvar[0].sqltype = ECPGt_char;
        sqlda2->sqlvar[0].sqldata = "postgres";
        sqlda2->sqlvar[0].sqllen  = 8;

        intval = 1;
        sqlda2->sqlvar[1].sqltype = ECPGt_int;
        sqlda2->sqlvar[1].sqldata = (char *)&intval;
        sqlda2->sqlvar[1].sqllen  = sizeof(intval);

在建立输入SQLDA后，打开具有输入SQLDA的一个游标。

    /*打开具有输入参数的游标。*/

        EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;

从已打开的游标中读取行到输出SQLDA。（一般来说，你必须在循环中反复调用`FETCH`， 为了读取结果集中的所有行。）

        while (1)
        {
            sqlda_t *cur_sqlda;

    /*分配描述符给游标*/

            EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;

接下来，从SQLDA中检索已读取记录，通过下面`sqlda_t`结构中 的连接表。

        for (cur_sqlda = sqlda1 ;
             cur_sqlda != NULL ;
             cur_sqlda = cur_sqlda->desc_next)
        {
            ...

读取第一条记录中的每一列。列数被存储在`sqld`中， 第一列的实际数据被存储在`sqlvar[0]`，`sqlda_t`结构的两个 成员中。

    /* 输出行中每一列*/
            for (i = 0; i < sqlda1->sqld; i++)
            {
                sqlvar_t v = sqlda1->sqlvar[i];
                char *sqldata = v.sqldata;
                short sqllen  = v.sqllen;

                strncpy(name_buf, v.sqlname.data, v.sqlname.length);
                name_buf[v.sqlname.length] = '\0';

目前，该列数据被存储在变量`v`中。拷贝每个数据到宿主变量， 为了列类型查看`v.sqltype`。

                switch (v.sqltype) {
                    int intval;
                    double doubleval;
                    unsigned long long int longlongval;

                    case ECPGt_char:
                        memset(&var_buf, 0, sizeof(var_buf));
                        memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf)-1 : sqllen));
                        break;

                    case ECPGt_int: /* integer */
                        memcpy(&intval, sqldata, sqllen);
                        snprintf(var_buf, sizeof(var_buf), "%d", intval);
                        break;

                    ...

                    default:
                        ...
                }

                printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype);
            }

在处理完所有记录之后关闭游标，并且断开数据库连接。

        EXEC SQL CLOSE cur1;
        EXEC SQL COMMIT;

        EXEC SQL DISCONNECT ALL;

在例 33-1中显示了整个程序。

例 33-1. SQLDA程序示例

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

    EXEC SQL include sqlda.h;

    sqlda_t *sqlda1; /*输出描述符*/
    sqlda_t *sqlda2; /*输入描述符*/

    EXEC SQL WHENEVER NOT FOUND DO BREAK;
    EXEC SQL WHENEVER SQLERROR STOP;

    int
    main(void)
    {
        EXEC SQL BEGIN DECLARE SECTION;
        char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )";

        int intval;
        unsigned long long int longlongval;
        EXEC SQL END DECLARE SECTION;

        EXEC SQL CONNECT TO uptimedb AS con1 USER uptime;

        EXEC SQL PREPARE stmt1 FROM :query;
        EXEC SQL DECLARE cur1 CURSOR FOR stmt1;

    /*为输入参数创建SQLDA结构*/

        sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
        memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));
        sqlda2->sqln = 2; /* a number of input variables */

        sqlda2->sqlvar[0].sqltype = ECPGt_char;
        sqlda2->sqlvar[0].sqldata = "postgres";
        sqlda2->sqlvar[0].sqllen  = 8;

        intval = 1;
        sqlda2->sqlvar[1].sqltype = ECPGt_int;
        sqlda2->sqlvar[1].sqldata = (char *) &intval;
        sqlda2->sqlvar[1].sqllen  = sizeof(intval);

    /*打开具有输入参数的游标*/

        EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;

        while (1)
        {
            sqlda_t *cur_sqlda;

    /*分配描述符给游标*/
            EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;

            for (cur_sqlda = sqlda1 ;
                 cur_sqlda != NULL ;
                 cur_sqlda = cur_sqlda->desc_next)
            {
                int i;
                char name_buf[1024];
                char var_buf[1024];

    /*输出行中每一列*/
                for (i=0 ; i<cur_sqlda->sqld ; i++)
                {
                    sqlvar_t v = cur_sqlda->sqlvar[i];
                    char *sqldata = v.sqldata;
                    short sqllen  = v.sqllen;

                    strncpy(name_buf, v.sqlname.data, v.sqlname.length);
                    name_buf[v.sqlname.length] = '\0';

                    switch (v.sqltype)
                    {
                        case ECPGt_char:
                            memset(&var_buf, 0, sizeof(var_buf));
                            memcpy(&var_buf, sqldata, (sizeof(var_buf)<=sqllen ? sizeof(var_buf)-1 : sqllen) );
                            break;

                        case ECPGt_int: /* integer */
                            memcpy(&intval, sqldata, sqllen);
                            snprintf(var_buf, sizeof(var_buf), "%d", intval);
                            break;

                        case ECPGt_long_long: /* bigint */
                            memcpy(&longlongval, sqldata, sqllen);
                            snprintf(var_buf, sizeof(var_buf), "%lld", longlongval);
                            break;

                        default:
                        {
                            int i;
                            memset(var_buf, 0, sizeof(var_buf));
                            for (i = 0; i < sqllen; i++)
                            {
                                char tmpbuf[16];
                                snprintf(tmpbuf, sizeof(tmpbuf), "%02x ", (unsigned char) sqldata[i]);
                                strncat(var_buf, tmpbuf, sizeof(var_buf));
                            }
                        }
                            break;
                    }

                    printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype);
                }

                printf("\n");
            }
        }

        EXEC SQL CLOSE cur1;
        EXEC SQL COMMIT;

        EXEC SQL DISCONNECT ALL;

        return 0;
    }

该例子输出应该看起来像下面这样（一些数字有所不同）。

    oid = 1 (type: 1)
    datname = template1 (type: 1)
    datdba = 10 (type: 1)
    encoding = 0 (type: 5)
    datistemplate = t (type: 1)
    datallowconn = t (type: 1)
    datconnlimit = -1 (type: 5)
    datlastsysoid = 11510 (type: 1)
    datfrozenxid = 379 (type: 1)
    dattablespace = 1663 (type: 1)
    datconfig =  (type: 1)
    datacl = {=c/uptime,uptime=CTc/uptime} (type: 1)
    datid = 1 (type: 1)
    datname = template1 (type: 1)
    numbackends = 0 (type: 5)
    xact_commit = 113606 (type: 9)
    xact_rollback = 0 (type: 9)
    blks_read = 130 (type: 9)
    blks_hit = 7341714 (type: 9)
    tup_returned = 38262679 (type: 9)
    tup_fetched = 1836281 (type: 9)
    tup_inserted = 0 (type: 9)
    tup_updated = 0 (type: 9)
    tup_deleted = 0 (type: 9)

    oid = 11511 (type: 1)
    datname = postgres (type: 1)
    datdba = 10 (type: 1)
    encoding = 0 (type: 5)
    datistemplate = f (type: 1)
    datallowconn = t (type: 1)
    datconnlimit = -1 (type: 5)
    datlastsysoid = 11510 (type: 1)
    datfrozenxid = 379 (type: 1)
    dattablespace = 1663 (type: 1)
    datconfig =  (type: 1)
    datacl =  (type: 1)
    datid = 11511 (type: 1)
    datname = postgres (type: 1)
    numbackends = 0 (type: 5)
    xact_commit = 221069 (type: 9)
    xact_rollback = 18 (type: 9)
    blks_read = 1176 (type: 9)
    blks_hit = 13943750 (type: 9)
    tup_returned = 77410091 (type: 9)
    tup_fetched = 3253694 (type: 9)
    tup_inserted = 0 (type: 9)
    tup_updated = 0 (type: 9)
    tup_deleted = 0 (type: 9)

上一页 | 起始页 | 下一页
pgtypes 库 | 上一级 | 错误处理