| PostgreSQL 9.4.4 文档 | |||
|---|---|---|---|
| 上一页 | 上一级 | 章 33. ECPG - 在C中嵌入SQL | 下一页 |
一个SQL描述符范围是处理SELECT, FETCH或者 DESCRIBE语句结果的更复杂的方法。 一个SQL描述符范围把一行数据里的数据和元数据项组合到一个数据结构中。 元数据在执行动态SQL语句时特别有用，那里的结果列的属性可能不能提前知道。 PostgreSQL提供了两种使用描述符范围的方法： 命名的SQL描述符范围和C结构SQLDAs。
一个命名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可以是一个字符整数或者一个包含整数的宿主变量。 可能的字段有:
结果集中的行数
实际数据项（因此，这个字段的数据类型依赖于这个查询）
当TYPE是9的时候，那么 DATETIME_INTERVAL_CODE将有DATE的1值， TIME的2值， TIMESTAMP的3值， TIME WITH TIME ZONE的4值或者 TIMESTAMP WITH TIME ZONE的5值。
未实现。
描述符（标识一个空值或者一个截断的值）
未实现
字符中数据长度
字段名称
未实现
字节数据的字符表示的长度
精度（类型numeric）
字符中数据长度
字节数据的字符表示的长度
比例（类型numeric）
字段数据类型的数值代码
在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;
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程序流是：
准备一个查询，并且为它声明一个游标。
为结果行声明SQLDA。
为输入参数声明SQLDA，并且初始化它们(内存分配，参数设置)。
打开具有输入SQLDA的游标
从游标中抓取行，并且将它们存储到输出SQLDA中。
从输出SQLDA中读取值到宿主变量中 (如果有必要使用转换)。
关闭游标。
自由内存区域分配给输入SQLDA。
SQLDA使用三个数据结构类型：sqlda_t，sqlvar_t, 和struct sqlname。
提示: PostgreSQL的SQLDA与IBM DB2通用数据库中的一个有类似的数据结构。 所以DB2的SQLDA上的一些技术信息可以更好的帮助理解PostgreSQL的。
结构类型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;该字段的意思是：
它包含文本字符串"SQLDA "。
它包含字节中分配空间的大小。
它包含一个参数化查询的情况下的输入参数数， 使用USING关键字被传递给OPEN, DECLARE或者 EXECUTE语句。 在这种情况下它被作为SELECT, EXECUTE或者FETCH语句的输出使用。 它的值和sqld语句是一样的。
它包含结果集中的字段数量。
如果查询返回多条记录，那么返回多个链接SQLDA结构， 并且desc_next持有指向列表中下一项的指针。
这是结构集中列数组。
结构类型sqlvar_t持有列值和元数据比如类型和长度。 该类型的定义是：
struct sqlvar_struct
{
short sqltype;
short sqllen;
char *sqldata;
short *sqlind;
struct sqlname sqlname;
};
typedef struct sqlvar_struct sqlvar_t;该字段的含义是：
包含该字段的类型标识符。 对于该值，参阅ecpgtype.h中的enum ECPGttype。
包含该字段的二进制长度。比如4字节的ECPGt_int。
指向该数据。关于数据的格式在第 33.4.4 节 中描述。
指向空指示器。0表示非空，-1表示空。
该字段名称。
struct sqlname结构持有列名。 它作为sqlvar_t结构成员被使用。 该结构定义是：
#define NAMEDATALEN 64
struct sqlname
{
short length;
char data[NAMEDATALEN];
};该字段含义是：
包含该字段名长度。
包含实际字段名。
通过SQLDA检索查询结果集的一般步骤是：
声明sqlda_t结构用来接收结果集。
执行FETCH/EXECUTE/DESCRIBE命令用来处理指定已声明SQLDA的查询。
通过查看sqln检查结果集中的记录数，sqlda_t结构成员。
从sqlvar[0], sqlvar[1]等中获得每列的值，sqlda_t结构成员
通过desc_next指针转到下一行(sqlda_t结构)，sqlda_t结构成员。
你需要重复以上步骤
这是一个通过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;
...
}
使用SQLDA传递输入参数给预备查询的一般步骤是：
创建预备查询(预备语句)
作为输入SQLDA声明sqlda_t结构。
为了输入SQLDA分配内存区域(作为sqlda_t结构)。
在已分配内存中设置（拷贝）输入值。
打开具有声明输入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);
这是一个示例程序，描述了如何获取数据库访问统计，通过输入参数声明，来自系统表。
这个应用程序连接两个系统表，数据库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)