ECPG调研报告-天翼云开发者社区

1 ECPG介绍

ECPG (Embedded SQL in C) 是指嵌入式SQL程序，它由编程语言（C）编写的代码混合特殊标记的SQL命令而成。它可以通过某些特殊的标记直接将SQL语句嵌入到c语言源程序中，使得代码更加简洁。

嵌入式 SQL 程序（*.pgc）的工作原理是通过嵌入式 SQL 预处理器，将源代码（*.pgc）转换成一个普通 c 程序（*.c），而这个c程序中使用了libpq的库函数，因此经过编译链接后生成的可执行程序可以与后端进行通信。

简化了代码
构建时会被检查语法正确性
嵌入式SQL在C中是SQL标准中指定的，有较好的通用性和兼容性。

2 ECPG语法

ECPG代码总是具有以下形式：

EXEC SQL ...;

2.1 管理连接

建立连接

EXEC SQL CONNECT TO target [AS connection-name] [USER user-name];

可以用下列方法指定*target*，也可以指定为 DEFAULT,会以默认用户名发起一个到默认数据库的连接。

dbname[@server][:port]
<tcp|unix>:postgresql://server[:port][/dbname]

可以用下列方法指定*user和 passoword*的方式有3种。

*username/password*
usernameIDENTIFIED BYpassword
usernameUSINGpassword

其中*target，username以及password*可以是一个 SQL 标识符、一个 SQL 字符串或者一个对字符变量的引用。

选择连接

connection-name被用来在一个程序中处理多个连接。如果使用一个连接，它可以被忽略。如果使用多个连接，默认使用最近被打开的连接。如果需要切换连接，可以使用以下语句：

/* 显式地为SQL 语句选择一个连接 */
EXEC SQL AT connection-name SELECT ...;

/* 执行语句来切换当前的连接 */
EXEC SQL SET CONNECTION connection-name;

关闭连接

EXEC SQL DISCONNECT [connection];

可以用下列方法指定*connection*：

*connection-name *
DEFAULT
CURRENT
ALL

如果没有指定连接，当前连接将被关闭。

2.2 运行SQL

增删改语句

增删改语句直接在在EXEC SQL后添加需要执行的内容。如果*autocommit*设置为关闭，注意执行后需要commit，否则不会生效。

/* 创建表 */
EXEC SQL CREATE TABLE foo (number integer, ascii char(16));
EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);
EXEC SQL COMMIT;

/* 插入行 */
EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');
EXEC SQL COMMIT;

/* 更新 */
EXEC SQL UPDATE foo
    SET ascii = 'foobar'
    WHERE number = 9999;
EXEC SQL COMMIT;

如果是执行没有结果集的语句，可以直接使用 EXECUTE IMMEDIATE,例如：

/* 创建表 */
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "CREATE TABLE test1 (...);";
EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE IMMEDIATE :stmt;

查询语句

如果返回结果只有一行，可以直接使用 EXEC SQL执行。

EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';

如果返回结果有多行，则需要使用游标。使用游标的过程为：声明游标、打开游标、从该游标取得一行、重复并且最终关闭它。

/* 声明游标 */
EXEC SQL DECLARE foo_bar CURSOR FOR
    SELECT number, ascii FROM foo
    ORDER BY ascii;
/* 打开游标 */
EXEC SQL OPEN foo_bar;
/* 从游标种取得结果 */
EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;
...
/* 关闭游标 */
EXEC SQL CLOSE foo_bar;
EXEC SQL COMMIT;

事务语句

在默认模式下，只有发出 EXEC SQL COMMIT命令时才会提交命令。嵌入式 SQL 接口也可以通过ecpg的-t命令行选项或者通过EXEC SQL SET AUTOCOMMIT TO ON语句支持事务的自动提交，以下为一些示范的事务管理命令

/* 直接提交一个进行中的事务 */
EXEC SQL COMMIT;

/* 回滚一个进行中的事务 */
EXEC SQL ROLLBACK；

/* 准备两阶段提交的当前事务 */
EXEC SQL PREPARE TRANSACTION transaction_id；

/* 提交处于准备状态的事务 */
EXEC SQL COMMIT PREPARED transaction_id；

预备语句

预备语句可以提前准备一个动态指定的语句用于执行,将`?`作为一个占位符，适用于同一个语句要被使用多次或者时值在编译时未知的场景。它的使用语法如下所示：

/* name: 预备查询的一个标识符*/
/* string: 动态指定的语句，可以是SELECT、INSERT、UPDATE 或者 DELETE 之一*/
EXEC PREPARE name FROM string

预备语句的使用流程为：预备语句，为占位符赋值，释放预备语句。

/*预备语句，使用占位符“?”来代替未知的数值*/
EXEC SQL PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?";
EXEC SQL PREPARE stmt2 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?";

/*语句返回一行可以用SELECT语句配合USING为占位符赋值*/
EXEC SQL EXECUTE stmt1 INTO :dboid, :dbname USING 1;

/*语句返回多行可以用游标配合USING为占位符赋值*/
EXEC SQL DECLARE foo_bar CURSOR FOR stmt2;
...
EXEC SQL OPEN foo_bar USING 100;
...
while (1)
{
    EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;
    ...
}
EXEC SQL CLOSE foo_bar;

/*释放语句*/
EXEC SQL DEALLOCATE PREPARE name;

2.3 使用主变量

声明主变量

ECPG语言中包含SQL语言和C语言，其中C语言为“主语言”，所以C语言中的变量被称为主变量。

主变量的作用是在程序和SQL语句之间交换数据，在使用之前必须在特别标记的 声明部分中被声明，这样嵌入式 SQL 预处理器才会注意它们。

/* 创建声明节 */
EXEC SQL BEGIN DECLARE SECTION;
/* 声明c变量 */
......
EXEC SQL END DECLARE SECTION;

/* 隐式地创建声明节 */
EXEC SQL int i = 4;

SQL与程序之间的值交换

程序到SQL

只有主变量可以在SQL语句中使用。在SQL语句中使用时这些变量必须以冒号 ':' 开头。

/* bar, foo 为主变量，'test' 为常规字符串 */
EXEC SQL INSERT INTO sometable VALUES (:bar, :foo, 'test');

SQL到程序

ECPG提供了 SELECT和 FETCH两种命令，结合 INTO字句，可以指定SQL查询结果存储在哪些主变量中。

SELECT命令用于只返回单一行的查询。

/* INTO在FROM之前,查询的结果数量必须和存放的参数数量相同 */
EXEC SQL SELECT a, b INTO :foo, :bar FROM test;

FETCH命令则用于使用游标返回多行的查询。

/* INTO在FROM之后 */
EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;

 ...

do
{
    ...
    EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
    ...
} while (...);

类型映射

当执行ECPG程序时，值需要在PG数据类型和C语言变量类型之间转换，大多数情况下这种转换可以自动完成，但有一些类型需要通过特殊的库函数进行访问。以此为依据，数据类型可以被分为两种：简单类型和特殊数据类型

简单类型

PG 数据类型	C语言类型
smallint	short
integer	int
bigint	long long int
character(n), varchar(n), text	char[n+1], VARCHAR[n+1]
name	char[NAMEDATALEN]
real	float
double precision	double
smallserial	short
serial	int
bigserial	long long int
oid	unsigned int
bytea	char*
boolean	bool

字符串处理

SQL中字符串类型（如varchar， text）转换为C语言的类型有两种方式。

char[]：仅包括字符串的内容。
VARCHAR类型：ECPG提供了一种特殊类型，包含字符串长度和字符串内容。

/* 声明长度为180的VARCHAR类型*/
VARCHAR var[180];

/* 会被转变成varchar_var，其中len不包含终止零字节
 * 用做一个查询的输入时，如果strlen(arr)和len不同，
 * 将使用短的那一个
 */
struct varchar_var { int len; char arr[180]; } var;

特殊类型

特殊类型需要额外通过特殊的库函数访问，因此必须包括pgtypes库。

PG 数据类型	C语言类型
decimal	decimal
numeric	numeric
timestamp	timestamp
interval	interval
date	date

以timestamp类型为例分析转换的流程：

/* 必须包含该类型的头文件 */
#include <pgtypes_timestamp.h>

/* 声明一个timestamp的主变量 */
EXEC SQL BEGIN DECLARE SECTION;
timestamp ts;
EXEC SQL END DECLARE SECTION;

/* 使用库函数对SQL变量进行处理， 
 * PGTYPEStimestamp_to_asc将
 * timestamp转换为ASCII码
 */
EXEC SQL SELECT now()::timestamp INTO :ts;
printf("ts = %s\n", PGTYPEStimestamp_to_asc(ts));

numeric, decimal，interval类型需要使用 PGTYEPxxx_NEW()提前分配内存空间。

复杂类型

数组类型

SQL中的数组类型

ECPG 中不直接支持 SQL 级别的多维数组，也不可以直接将一个数组类型列直接映射到一个数组主变量。

/*假设有以下的表*/
CREATE TABLE t3 (
    ii integer[]
);
 /* 声明主变量 */
EXEC SQL BEGIN DECLARE SECTION;
int ii_a[8];
EXEC SQL END DECLARE SECTION;
...
 /* 获取 ii integer[] */
while (1)
{
    /* 错误 */
    EXEC SQL FETCH FROM cur1 INTO :ii_a;
    ...
}

while (1)
{    /* 正确 */
    EXEC SQL FETCH FROM cur1 INTO :ii_a[0], :ii_a[1], :ii_a[2], :ii_a[3];
    ...
}

C语言中的数组类型

C语言中的数组类型可以用于接收多个行组成的查询结果，但是要确保长度足够，否则会发生缓冲区溢出

/* 声明主变量 */
 EXEC SQL BEGIN DECLARE SECTION;
    int dbid[8];
    char dbname[8][16];
EXEC SQL END DECLARE SECTION;
...
 /* 一次检索多行到数组中。 */
  EXEC SQL SELECT oid,datname INTO :dbid, :dbname FROM pg_database;

结构体类型

SQL中的结构体类型

ECPG 中并不直接支持组合类型,只能单独访问每一个属性或者转换成字符串。

/*假设有以下的类型和表*/
CREATE TYPE comp_t AS (intval integer, textval varchar(32));
CREATE TABLE t4 (compval comp_t);

/*分别对每一个属性声明主变量*/
EXEC SQL BEGIN DECLARE SECTION;
int intval;
varchar textval[33];
EXEC SQL END DECLARE SECTION;
....
while (1)
{
    /* 将组合类型列的每一个元素取到主变量中。 */
    EXEC SQL FETCH FROM cur1 INTO :intval, :textval;

    printf("intval=%d, textval=%s\n", intval, textval.arr);
}

C语言中的结构体类型

C语言中可以定义结构体主变量，一次取到多列，将每一列的结果直接存放到结构体中。

EXEC SQL BEGIN DECLARE SECTION;
    typedef struct
    {
       int oid;
       char datname[65];
       long long int size;
    } dbinfo_t;

    dbinfo_t dbval;
EXEC SQL END DECLARE SECTION;
/* 查询的列顺序和dbinfo_t对应 */
EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database;
...
    while (1)
    {
        /* 将多列取到一个结构中。*/
        EXEC SQL FETCH FROM cur1 INTO :dbval;
    }

    EXEC SQL CLOSE cur1;

指示符

在从数据库中取值时，可以对每一个包含数据的主变量追加一个次要主变量，来对空值进行处理，这个次要主变量被称为指示符并且包含一个说明数据是否为空的标志，如果值不为空，指示符变量将为零；否则它将为负值。

他的使用语法如下所示：

/* 声明主变量和空值指示符*/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR val;
int val_ind;
EXEC SQL END DECLARE SECTION:

/* 如果值不为空，val_ind将为零*/
EXEC SQL SELECT b INTO :val :val_ind FROM test1;

2.4 pgtyes库

除了[特殊类型主变量]的映射函数，pgtype还包含了C 中对这些类型进行基本计算的函数。在下文中列举出了一些常用函数。

numeric类型

由于numeric所以适用于任意精度，需要能够动态地扩展。因此在使用前需要 PGTYPESnumeric_new和 PGTYPESnumeric_free函数在堆上创建变量。

/*新分配一个numeric变量的指针*/
numeric *PGTYPESnumeric_new(void);

/*释放一个numeric的内存*/
void PGTYPESnumeric_free(numeric *var);

/*将字符串转换为numeric类型， 支持的字符串格式为可用的格式是： -2、 .794、 +3.44、 592.49E07或者 -32.84e-4*/
numeric *PGTYPESnumeric_from_asc(char *str, char **endptr);

/*numeric类型转换为字符串*/
char *PGTYPESnumeric_to_asc(numeric *num, int dscale);

/*两个numeric变量相加并且把结果返回第三个numeric变量中。执行成功返回 0，出错返回 -1*/
int PGTYPESnumeric_add(numeric *var1, numeric *var2, numeric *result);

/*比较两个numeric变量，var1大于var2则返回 1，var1小于var2则返回 -1，var1和var2相等则返回 0*/
int PGTYPESnumeric_cmp(numeric *var1, numeric *var2)

date类型

/* 从一个timestamp中抽取日期部分 */
date PGTYPESdate_from_timestamp(timestamp dt);

/* 将一个字符串转换为date类型， 字符串的顺序必须是月份，日期，年份 */
date PGTYPESdate_from_asc(char *str, char **endptr);

/* 将date类型转换为指定格式的字符串，成功时，返回 0；如果发生错误，则返回一个负值 */
int PGTYPESdate_fmt_asc(date dDate, char *fmtstring, char *outbuf);

timestamp类型

/* 获取当前时间戳 */
void PGTYPEStimestamp_current(timestamp *ts);

/* 获取两个时间戳之间的间隔，成功时，该函数返回 0；发生错误时则返回一个负值*/
int PGTYPEStimestamp_sub(timestamp *ts1, timestamp *ts2, interval *iv);

interval类型

interval类型需要在使用之前提前分配内存空间。

/*新分配一个interval类型的指针*/
interval *PGTYPESinterval_new(void);

/*释放先前分配的区间变量的内存。*/
void PGTYPESinterval_new(interval *intvl);

/*复制一个interval类型*/
int PGTYPESinterval_copy(interval *intvlsrc, interval *intvldest);

decimal类型

decimal类型和numeric类型相似。不过，它被限制为最大精度是 30 个有效位。decimal类型既可以在栈上也可以在堆上创建。

/*新分配一个decimal变量的指针*/
decimal *PGTYPESdecimal_new(void);

/*释放一个decimal类型的内存*/
void PGTYPESdecimal_free(decimal *var);

2.5 错误处理

ECPG的错误处理有2种工具

使用WHENEVER命令设置回调来处理警告和错误情情况。
从sqlca变量中获得错误或警告的详细信息。

设置回调

设置回调函数的语法如下所示：

EXEC SQL WHENEVER condition action;

*condition*可以为以下值：

SQLERROR：SQL执行出错
SQLWARNING：SQL执行产生warning
NOT FOUND：SQL 语句检索或影响零行

*action*可以为以下值：

CONTINUE
GOTO label
SQLPRINT
STOP
DO BREAK
CALL name (*args*)

sqlca

ECPG提供了一种名为 sqlca（SQL 通讯区域）的全局变量，覆盖率error和warning。如果有多个error和warning，sqlca只保存最后一个。sqlca的结构如下所示

struct
{
    char sqlcaid[8];
    long sqlabc;
    long sqlcode;/*为0表示没有发生错误， 为负数表示warning或error，为正数表示无害情况*/
    struct
    {
        int sqlerrml; /*错误消息长度*/
        char sqlerrmc[SQLERRMC_LEN];/*错误消息字符串*/
    } sqlerrm;
    char sqlerrp[8];
    long sqlerrd[6];/*执行成功时，sqlca.sqlerrd[1]包含被处理行的 OID，sqlca.sqlerrd[2]包含被处理或被返回的行数*/
    char sqlwarn[8];/* sqlca.sqlwarn[2]为W：产生了warning
                     * sqlca.sqlwarn[1]为W：值存到主变量里时被截断了
                     * sqlca.sqlwarn[0]为W：sqlca.sqlwarn中任意一个被设置为W
                     */
    char sqlstate[5];/*与sqlcode相似，是另一种标准的错误代码，具体含义可以通过pg错误代码查到*/
} sqlca;

2.6 描述符

描述符是一种高级方法，用于处理SELECT、FETCH或者DESCRIBE语句的结果，包括查询获取到的一行数据以及元数据。PG提供了两种方法使用描述符：命名 SQL 描述符区域和 C 结构 SQLDA。

命名 SQL 描述符区域

一个命名 SQL 描述符由一个头部以及一个或多个行描述符区域构成，头部包含与整个描述符相关的信息，而条目描述符区域则描述结果行中的每一列。他的使用流程为：分配SQL 描述符区域，使用描述符区域，释放描述符区域。

/*分配SQL描述符区域，mydesc是描述符的变量名 */
EXEC SQL ALLOCATE DESCRIPTOR mydesc;

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


/*使用描述符区域*/
/*对于还没有执行的预备查询，DESCRIBE可以被用来得到其结果集的元数据*/
EXEC SQL PREPARE stmt1 FROM :sql_stmt;
EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;

/*执行查询语句，将结果存储到描述符中*/
EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc;

/*从描述符中获取数据*/
/*获取描述符头部描述符数据*/
EXEC SQL GET DESCRIPTOR name :hostvar = field;

/*获取条目描述符数据*/
EXEC SQL GET DESCRIPTOR name VALUE num :hostvar = field;

/*释放描述符区域*/
EXEC SQL DEALLOCATE DESCRIPTOR mydesc;

头部描述符支持一种属性：

COUNT，表示查询结果有多少列，及条目描述符有多少个。

条目描述符支持多种属性*：*

CARDINALITY: 结果集中的行数
DATA: 实际的数据项
TYPE: 列的数据类型
DATETIME_INTERVAL_CODE: 当TYPE是9时，1 表示 DATE， 2 表示 TIME， 3 表示 TIMESTAMP， 4 表示 TIME WITH TIME ZONE， 5 表示 TIMESTAMP WITH TIME ZONE。
INDICATOR: 指示符
LENGTH：以字符计的数据长度
NAME：列名
OCTET_LENGTH：以字节计的数据长度
PRECISION：精度
SCALE：numeric类型小数的位数

SQLDA 描述符

SQLDA 描述符区域是一个 C 语言结构，也可以用于获取结果和元数据。除了获取数据，它也可以和 USING结合为预备语句赋值。SQLDA 使用三种数据结构类型：sqlda_t、sqlvar_t以及 struct sqlname。

sqlda_t是实际 SQLDA 的类型。

struct sqlda_struct
{
    char            sqldaid[8]; /* 包含一个字符串"SQLDA"*/
    long            sqldabc; /* 已分配空间的大小（字节）*/
    short           sqln; /* 当它被传递给使用USING关键词，它表示输入参数的数目。
                           * 在它被用作查询语句的输出时，它的值和sqld一样*/
    short           sqld; /*一个结果集中的属性的数量*/
    struct sqlda_struct *desc_next; /*如果查询返回不止一个记录,指向下一个sqlda_t*/
    struct sqlvar_struct sqlvar[1]; /*查询结果里的列数组*/
};

typedef struct sqlda_struct sqlda_t;

sqlvar_t保存一个列值和元数据

struct sqlvar_struct
{
    short          sqltype; /* 属性的类性标识符 */
    short          sqllen; /* 属性的二进制长度 */
    char          *sqldata; /* 查询得到的数据 */
    short         *sqlind; /* 空标识符， 表示非空，-1 表示空 */
    struct sqlname sqlname;/*属性的名称 */
};

typedef struct sqlvar_struct sqlvar_t;

struct sqlname sqlname保存属性的名称

#define NAMEDATALEN 64

struct sqlname
{
        short           length; /*属性长度*/
        char            data[NAMEDATALEN]; /*属性名称*/
};

SQLDA 使用示例

EXEC SQL include sqlda.h;

sqlda_t *sqlda1; /* 用于输出的描述符 */
sqlda_t *sqlda2; /* 用于输入的描述符 */
...
/*准备一个预备语句*/
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;
EXEC SQL DECLARE cur1 CURSOR FOR stmt1;

/*为输入描述符（sqlda2）赋值*/
sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));
sqlda2->sqln = 2; /* 输入变量的数量 */
/*d.datname=postgres*/
sqlda2->sqlvar[0].sqltype = ECPGt_char;
sqlda2->sqlvar[0].sqldata = "postgres";
sqlda2->sqlvar[0].sqllen  = 8;
/*d.oid=1*/
intval = 1;
sqlda2->sqlvar[1].sqltype = ECPGt_int;
sqlda2->sqlvar[1].sqldata = (char *)&intval;
sqlda2->sqlvar[1].sqllen  = sizeof(intval);

/* 用输入描述符（sqlda2）打开一个游标。 */
EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2
while(1)
{
  
    /*将游标fetch到的值存到输出描述符(sqlda1)中*/
    EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;
        ....
    /*遍历输出描述符链表*/
    for (cur_sqlda = sqlda1 ;
         cur_sqlda != NULL ;
         cur_sqlda = cur_sqlda->desc_next)
    {
        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';
    }
}
...

2.7 预处理指令

include

如果某文件包含 SQL 预处理器所需的信息，需要SQL INCLUDE 语句包含此文件，否则使用普通的#include语句即可。

EXEC SQL INCLUDE name；

define 和 undef

ECPG define 和 undefine 语句会被预编译器替换。如果是一个ECPG查询中使用的常量，不能使用#include。

EXEC SQL DEFINE name;
EXEC SQL DEFINE name value;

2.8 编译运行

运行流程

写好pgc文件后，需要使用ecpg程序将pgc编程成C文件来使用，具体的实现流程如下所示：

暂时无法在飞书文档外展示此内容

/*ecpg预编译器处理，完整语句见下一节*/
ecpg program.pgc

/*生成.o文件*/
cc -I/usr/local/pgsql/include -c program.c

/*生成可执行文件*/
cc -o prog1 prog1.o -L/usr/local/pgsql/lib -lecpg

ecpg命令

ecpg安装在bin目录下，它的作用是将嵌入式 SQL 语句的 C 程序转换为普通 C 代码。输出文件可以被任何 C 编译器工具链处理。

ecpg [option...] file...

*option*可以是以下值：

-c：自动从 SQL 代码生成确定的 C 代码
-C：设置兼容模式
-D：用ECPG语言定义符号
-I: 指定额外的include目录
-i: 解析系统的include文件
-r *option*: 选择运行时选项：no_indicator:不使用指示符显示是否为空，prepare:所有语句都提前准备，questionmarks:允许使用问号作为占位符
-t: 打开自动提交
-v:打印额外信息

makefile

编译链接的过程可以写为makefile

ECPG = ecpg
CC = gcc

INCLUDES = -I$(shell pg_config --includedir)
LIBPATH = -L$(shell pg_config --libdir)
CFLAGS += $(INCLUDES)
LDFLAGS += -Wall -g
LDLIBS += $(LIBPATH) -lecpg -lpq -lpgtypes

%.c: %.pgc
    $(ECPG) -t -c $(INCLUDES) -o $@ $<
%: %.o
    $(CC) $(CFLAGS) $(LDFLAGS) $(LDLIBS) -o $@ $<

3.ECPG实践

以下为一个简单的ecpg demo。

#include <stdlib.h>
#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#include <unistd.h>
EXEC SQL include sqlca.h;
EXEC SQL include sqlda.h;

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

EXEC SQL WHENEVER NOT FOUND DO BREAK;
EXEC SQL WHENEVER SQLERROR CALL print_sqlca();

void
print_sqlca()
{
    fprintf(stderr, "==== sqlca ====\n");
    fprintf(stderr, "sqlcode: %ld\n", sqlca.sqlcode);
    fprintf(stderr, "sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml);
    fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    fprintf(stderr, "sqlerrd: %ld %ld %ld %ld %ld %ld\n", sqlca.sqlerrd[0],sqlca.sqlerrd[1],sqlca.sqlerrd[2],
                                                          sqlca.sqlerrd[3],sqlca.sqlerrd[4],sqlca.sqlerrd[5]);
    fprintf(stderr, "sqlwarn: %d %d %d %d %d %d %d %d\n", sqlca.sqlwarn[0], sqlca.sqlwarn[1], sqlca.sqlwarn[2],
                                                          sqlca.sqlwarn[3], sqlca.sqlwarn[4], sqlca.sqlwarn[5],
                                                          sqlca.sqlwarn[6], sqlca.sqlwarn[7]);
    fprintf(stderr, "sqlstate: %5s\n", sqlca.sqlstate);
    fprintf(stderr, "===============\n");
  
}
/*查询出连接对应的cn名*/
void
prinftcnname()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char nodename[1024];
    EXEC SQL END DECLARE SECTION;
    EXEC SQL DECLARE c CURSOR FOR SELECT node_name FROM pgxc_node where node_port::text IN (SELECT setting FROM pg_settings WHERE name = 'port');
    EXEC SQL OPEN c;
    for(;;)
    {
        /* get the next result row */
        EXEC SQL FETCH NEXT FROM c INTO :nodename;
 
        printf("%s\n",nodename
        );
    }
 
    EXEC SQL CLOSE c;
    EXEC SQL COMMIT;

}

void
selectandprint()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char query[1024] = "select * from pg_enum where (enumlabel =? OR enumlabel =?)";
    int intval;
    unsigned long long int longlongval;
    EXEC SQL END DECLARE SECTION;

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

    /* 为一个输入参数创建一个 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 = "GREEN";
    sqlda2->sqlvar[0].sqllen  = 5;

    sqlda2->sqlvar[1].sqltype = ECPGt_char;
    sqlda2->sqlvar[1].sqldata = "BLUE";
    sqlda2->sqlvar[1].sqllen  = 4;

    /* 用输入参数打开一个游标。 */
    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: /* 整数 */
                        memcpy(&intval, sqldata, sqllen);
                        snprintf(var_buf, sizeof(var_buf), "%d", intval);
                        break;

                    case ECPGt_long_long: /* 大整数 */
                        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;
}


int
main(void)
{  
    char *enumname = "postgres";
    int namelen = strlen(enumname);
    EXEC SQL BEGIN DECLARE SECTION;
    char *create_type_sql= "CREATE TYPE color AS ENUM ('RED', 'GREEN', 'BLUE')";
    const char *target1 = "tcp:postgresql://localhost:30025/postgres";
    const char *target2 = "tcp:postgresql://localhost:30026/postgres";
    const char *user = "zhoutianqi";
    const char *passwd = "********";
    EXEC SQL END DECLARE SECTION;

    /* 创建连接 */
    EXEC SQL CONNECT TO :target1 AS cn02 USER :user USING :passwd;
    EXEC SQL CONNECT TO :target2 AS cn01 USER :user USING :passwd;
    EXEC SQL SET CONNECTION cn02;
    /* 创建枚举类型*/
    EXEC SQL EXECUTE IMMEDIATE :create_type_sql;
    EXEC SQL COMMIT;
    /*打印cn02上枚举类对应的oid*/
    selectandprint();

    /*切换连接*/
    EXEC SQL SET CONNECTION cn01;
  
    /*打印cn01上枚举类对应的oid*/
    selectandprint();
  
    /*关闭连接*/
    EXEC SQL DISCONNECT ALL;

    return 0;
}

多次执行后的结果：

==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 39
sqlerrm.sqlerrmc: type "color" already exists on line 168 /*type 已经建立*/
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 42710
===============
cn02 /*cn02的查询结果*/
enumtypid = 16472 (type: 1)
enumsortorder = 00 00 00 40  (type: 12)
enumlabel = GREEN (type: 1)

enumtypid = 16472 (type: 1)
enumsortorder = 00 00 40 40  (type: 12)
enumlabel = BLUE (type: 1)

cn01 /*cn01的查询结果*/
enumtypid = 16436 (type: 1)
enumsortorder = 00 00 00 40  (type: 12)
enumlabel = GREEN (type: 1)

enumtypid = 16436 (type: 1)
enumsortorder = 00 00 40 40  (type: 12)
enumlabel = BLUE (type: 1)

1 ECPG介绍

简化了代码
构建时会被检查语法正确性
嵌入式SQL在C中是SQL标准中指定的，有较好的通用性和兼容性。

2 ECPG语法

ECPG代码总是具有以下形式：

EXEC SQL ...;

2.1 管理连接

建立连接

EXEC SQL CONNECT TO target [AS connection-name] [USER user-name];

可以用下列方法指定*target*，也可以指定为 DEFAULT,会以默认用户名发起一个到默认数据库的连接。

dbname[@server][:port]
<tcp|unix>:postgresql://server[:port][/dbname]

可以用下列方法指定*user和 passoword*的方式有3种。

*username/password*
usernameIDENTIFIED BYpassword
usernameUSINGpassword

其中*target，username以及password*可以是一个 SQL 标识符、一个 SQL 字符串或者一个对字符变量的引用。

选择连接

/* 显式地为SQL 语句选择一个连接 */
EXEC SQL AT connection-name SELECT ...;

/* 执行语句来切换当前的连接 */
EXEC SQL SET CONNECTION connection-name;

关闭连接

EXEC SQL DISCONNECT [connection];

可以用下列方法指定*connection*：

*connection-name *
DEFAULT
CURRENT
ALL

如果没有指定连接，当前连接将被关闭。

2.2 运行SQL

增删改语句

增删改语句直接在在EXEC SQL后添加需要执行的内容。如果*autocommit*设置为关闭，注意执行后需要commit，否则不会生效。

/* 创建表 */
EXEC SQL CREATE TABLE foo (number integer, ascii char(16));
EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);
EXEC SQL COMMIT;

/* 插入行 */
EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');
EXEC SQL COMMIT;

/* 更新 */
EXEC SQL UPDATE foo
    SET ascii = 'foobar'
    WHERE number = 9999;
EXEC SQL COMMIT;

如果是执行没有结果集的语句，可以直接使用 EXECUTE IMMEDIATE,例如：

/* 创建表 */
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "CREATE TABLE test1 (...);";
EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE IMMEDIATE :stmt;

查询语句

如果返回结果只有一行，可以直接使用 EXEC SQL执行。

EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';

如果返回结果有多行，则需要使用游标。使用游标的过程为：声明游标、打开游标、从该游标取得一行、重复并且最终关闭它。

/* 声明游标 */
EXEC SQL DECLARE foo_bar CURSOR FOR
    SELECT number, ascii FROM foo
    ORDER BY ascii;
/* 打开游标 */
EXEC SQL OPEN foo_bar;
/* 从游标种取得结果 */
EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;
...
/* 关闭游标 */
EXEC SQL CLOSE foo_bar;
EXEC SQL COMMIT;

事务语句

/* 直接提交一个进行中的事务 */
EXEC SQL COMMIT;

/* 回滚一个进行中的事务 */
EXEC SQL ROLLBACK；

/* 准备两阶段提交的当前事务 */
EXEC SQL PREPARE TRANSACTION transaction_id；

/* 提交处于准备状态的事务 */
EXEC SQL COMMIT PREPARED transaction_id；

预备语句

/* name: 预备查询的一个标识符*/
/* string: 动态指定的语句，可以是SELECT、INSERT、UPDATE 或者 DELETE 之一*/
EXEC PREPARE name FROM string

预备语句的使用流程为：预备语句，为占位符赋值，释放预备语句。

/*预备语句，使用占位符“?”来代替未知的数值*/
EXEC SQL PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?";
EXEC SQL PREPARE stmt2 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?";

/*语句返回一行可以用SELECT语句配合USING为占位符赋值*/
EXEC SQL EXECUTE stmt1 INTO :dboid, :dbname USING 1;

/*语句返回多行可以用游标配合USING为占位符赋值*/
EXEC SQL DECLARE foo_bar CURSOR FOR stmt2;
...
EXEC SQL OPEN foo_bar USING 100;
...
while (1)
{
    EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;
    ...
}
EXEC SQL CLOSE foo_bar;

/*释放语句*/
EXEC SQL DEALLOCATE PREPARE name;

2.3 使用主变量

声明主变量

ECPG语言中包含SQL语言和C语言，其中C语言为“主语言”，所以C语言中的变量被称为主变量。

主变量的作用是在程序和SQL语句之间交换数据，在使用之前必须在特别标记的 声明部分中被声明，这样嵌入式 SQL 预处理器才会注意它们。

/* 创建声明节 */
EXEC SQL BEGIN DECLARE SECTION;
/* 声明c变量 */
......
EXEC SQL END DECLARE SECTION;

/* 隐式地创建声明节 */
EXEC SQL int i = 4;

SQL与程序之间的值交换

程序到SQL

只有主变量可以在SQL语句中使用。在SQL语句中使用时这些变量必须以冒号 ':' 开头。

/* bar, foo 为主变量，'test' 为常规字符串 */
EXEC SQL INSERT INTO sometable VALUES (:bar, :foo, 'test');

SQL到程序

ECPG提供了 SELECT和 FETCH两种命令，结合 INTO字句，可以指定SQL查询结果存储在哪些主变量中。

SELECT命令用于只返回单一行的查询。

/* INTO在FROM之前,查询的结果数量必须和存放的参数数量相同 */
EXEC SQL SELECT a, b INTO :foo, :bar FROM test;

FETCH命令则用于使用游标返回多行的查询。

/* INTO在FROM之后 */
EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;

 ...

do
{
    ...
    EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
    ...
} while (...);

类型映射

简单类型

PG 数据类型	C语言类型
smallint	short
integer	int
bigint	long long int
character(n), varchar(n), text	char[n+1], VARCHAR[n+1]
name	char[NAMEDATALEN]
real	float
double precision	double
smallserial	short
serial	int
bigserial	long long int
oid	unsigned int
bytea	char*
boolean	bool

字符串处理

SQL中字符串类型（如varchar， text）转换为C语言的类型有两种方式。

char[]：仅包括字符串的内容。
VARCHAR类型：ECPG提供了一种特殊类型，包含字符串长度和字符串内容。

/* 声明长度为180的VARCHAR类型*/
VARCHAR var[180];

/* 会被转变成varchar_var，其中len不包含终止零字节
 * 用做一个查询的输入时，如果strlen(arr)和len不同，
 * 将使用短的那一个
 */
struct varchar_var { int len; char arr[180]; } var;

特殊类型

特殊类型需要额外通过特殊的库函数访问，因此必须包括pgtypes库。

PG 数据类型	C语言类型
decimal	decimal
numeric	numeric
timestamp	timestamp
interval	interval
date	date

以timestamp类型为例分析转换的流程：

/* 必须包含该类型的头文件 */
#include <pgtypes_timestamp.h>

/* 声明一个timestamp的主变量 */
EXEC SQL BEGIN DECLARE SECTION;
timestamp ts;
EXEC SQL END DECLARE SECTION;

/* 使用库函数对SQL变量进行处理， 
 * PGTYPEStimestamp_to_asc将
 * timestamp转换为ASCII码
 */
EXEC SQL SELECT now()::timestamp INTO :ts;
printf("ts = %s\n", PGTYPEStimestamp_to_asc(ts));

numeric, decimal，interval类型需要使用 PGTYEPxxx_NEW()提前分配内存空间。

复杂类型

数组类型

SQL中的数组类型

ECPG 中不直接支持 SQL 级别的多维数组，也不可以直接将一个数组类型列直接映射到一个数组主变量。

/*假设有以下的表*/
CREATE TABLE t3 (
    ii integer[]
);
 /* 声明主变量 */
EXEC SQL BEGIN DECLARE SECTION;
int ii_a[8];
EXEC SQL END DECLARE SECTION;
...
 /* 获取 ii integer[] */
while (1)
{
    /* 错误 */
    EXEC SQL FETCH FROM cur1 INTO :ii_a;
    ...
}

while (1)
{    /* 正确 */
    EXEC SQL FETCH FROM cur1 INTO :ii_a[0], :ii_a[1], :ii_a[2], :ii_a[3];
    ...
}

C语言中的数组类型

C语言中的数组类型可以用于接收多个行组成的查询结果，但是要确保长度足够，否则会发生缓冲区溢出

/* 声明主变量 */
 EXEC SQL BEGIN DECLARE SECTION;
    int dbid[8];
    char dbname[8][16];
EXEC SQL END DECLARE SECTION;
...
 /* 一次检索多行到数组中。 */
  EXEC SQL SELECT oid,datname INTO :dbid, :dbname FROM pg_database;

结构体类型

SQL中的结构体类型

ECPG 中并不直接支持组合类型,只能单独访问每一个属性或者转换成字符串。

/*假设有以下的类型和表*/
CREATE TYPE comp_t AS (intval integer, textval varchar(32));
CREATE TABLE t4 (compval comp_t);

/*分别对每一个属性声明主变量*/
EXEC SQL BEGIN DECLARE SECTION;
int intval;
varchar textval[33];
EXEC SQL END DECLARE SECTION;
....
while (1)
{
    /* 将组合类型列的每一个元素取到主变量中。 */
    EXEC SQL FETCH FROM cur1 INTO :intval, :textval;

    printf("intval=%d, textval=%s\n", intval, textval.arr);
}

C语言中的结构体类型

C语言中可以定义结构体主变量，一次取到多列，将每一列的结果直接存放到结构体中。

EXEC SQL BEGIN DECLARE SECTION;
    typedef struct
    {
       int oid;
       char datname[65];
       long long int size;
    } dbinfo_t;

    dbinfo_t dbval;
EXEC SQL END DECLARE SECTION;
/* 查询的列顺序和dbinfo_t对应 */
EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database;
...
    while (1)
    {
        /* 将多列取到一个结构中。*/
        EXEC SQL FETCH FROM cur1 INTO :dbval;
    }

    EXEC SQL CLOSE cur1;

指示符

他的使用语法如下所示：

/* 声明主变量和空值指示符*/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR val;
int val_ind;
EXEC SQL END DECLARE SECTION:

/* 如果值不为空，val_ind将为零*/
EXEC SQL SELECT b INTO :val :val_ind FROM test1;

2.4 pgtyes库

除了[特殊类型主变量]的映射函数，pgtype还包含了C 中对这些类型进行基本计算的函数。在下文中列举出了一些常用函数。

numeric类型

由于numeric所以适用于任意精度，需要能够动态地扩展。因此在使用前需要 PGTYPESnumeric_new和 PGTYPESnumeric_free函数在堆上创建变量。

/*新分配一个numeric变量的指针*/
numeric *PGTYPESnumeric_new(void);

/*释放一个numeric的内存*/
void PGTYPESnumeric_free(numeric *var);

/*将字符串转换为numeric类型， 支持的字符串格式为可用的格式是： -2、 .794、 +3.44、 592.49E07或者 -32.84e-4*/
numeric *PGTYPESnumeric_from_asc(char *str, char **endptr);

/*numeric类型转换为字符串*/
char *PGTYPESnumeric_to_asc(numeric *num, int dscale);

/*两个numeric变量相加并且把结果返回第三个numeric变量中。执行成功返回 0，出错返回 -1*/
int PGTYPESnumeric_add(numeric *var1, numeric *var2, numeric *result);

/*比较两个numeric变量，var1大于var2则返回 1，var1小于var2则返回 -1，var1和var2相等则返回 0*/
int PGTYPESnumeric_cmp(numeric *var1, numeric *var2)

date类型

/* 从一个timestamp中抽取日期部分 */
date PGTYPESdate_from_timestamp(timestamp dt);

/* 将一个字符串转换为date类型， 字符串的顺序必须是月份，日期，年份 */
date PGTYPESdate_from_asc(char *str, char **endptr);

/* 将date类型转换为指定格式的字符串，成功时，返回 0；如果发生错误，则返回一个负值 */
int PGTYPESdate_fmt_asc(date dDate, char *fmtstring, char *outbuf);

timestamp类型

/* 获取当前时间戳 */
void PGTYPEStimestamp_current(timestamp *ts);

/* 获取两个时间戳之间的间隔，成功时，该函数返回 0；发生错误时则返回一个负值*/
int PGTYPEStimestamp_sub(timestamp *ts1, timestamp *ts2, interval *iv);

interval类型

interval类型需要在使用之前提前分配内存空间。

/*新分配一个interval类型的指针*/
interval *PGTYPESinterval_new(void);

/*释放先前分配的区间变量的内存。*/
void PGTYPESinterval_new(interval *intvl);

/*复制一个interval类型*/
int PGTYPESinterval_copy(interval *intvlsrc, interval *intvldest);

decimal类型

decimal类型和numeric类型相似。不过，它被限制为最大精度是 30 个有效位。decimal类型既可以在栈上也可以在堆上创建。

/*新分配一个decimal变量的指针*/
decimal *PGTYPESdecimal_new(void);

/*释放一个decimal类型的内存*/
void PGTYPESdecimal_free(decimal *var);

2.5 错误处理

ECPG的错误处理有2种工具

使用WHENEVER命令设置回调来处理警告和错误情情况。
从sqlca变量中获得错误或警告的详细信息。

设置回调

设置回调函数的语法如下所示：

EXEC SQL WHENEVER condition action;

*condition*可以为以下值：

SQLERROR：SQL执行出错
SQLWARNING：SQL执行产生warning
NOT FOUND：SQL 语句检索或影响零行

*action*可以为以下值：

CONTINUE
GOTO label
SQLPRINT
STOP
DO BREAK
CALL name (*args*)

sqlca

ECPG提供了一种名为 sqlca（SQL 通讯区域）的全局变量，覆盖率error和warning。如果有多个error和warning，sqlca只保存最后一个。sqlca的结构如下所示

struct
{
    char sqlcaid[8];
    long sqlabc;
    long sqlcode;/*为0表示没有发生错误， 为负数表示warning或error，为正数表示无害情况*/
    struct
    {
        int sqlerrml; /*错误消息长度*/
        char sqlerrmc[SQLERRMC_LEN];/*错误消息字符串*/
    } sqlerrm;
    char sqlerrp[8];
    long sqlerrd[6];/*执行成功时，sqlca.sqlerrd[1]包含被处理行的 OID，sqlca.sqlerrd[2]包含被处理或被返回的行数*/
    char sqlwarn[8];/* sqlca.sqlwarn[2]为W：产生了warning
                     * sqlca.sqlwarn[1]为W：值存到主变量里时被截断了
                     * sqlca.sqlwarn[0]为W：sqlca.sqlwarn中任意一个被设置为W
                     */
    char sqlstate[5];/*与sqlcode相似，是另一种标准的错误代码，具体含义可以通过pg错误代码查到*/
} sqlca;

2.6 描述符

命名 SQL 描述符区域

/*分配SQL描述符区域，mydesc是描述符的变量名 */
EXEC SQL ALLOCATE DESCRIPTOR mydesc;

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


/*使用描述符区域*/
/*对于还没有执行的预备查询，DESCRIBE可以被用来得到其结果集的元数据*/
EXEC SQL PREPARE stmt1 FROM :sql_stmt;
EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;

/*执行查询语句，将结果存储到描述符中*/
EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc;

/*从描述符中获取数据*/
/*获取描述符头部描述符数据*/
EXEC SQL GET DESCRIPTOR name :hostvar = field;

/*获取条目描述符数据*/
EXEC SQL GET DESCRIPTOR name VALUE num :hostvar = field;

/*释放描述符区域*/
EXEC SQL DEALLOCATE DESCRIPTOR mydesc;

头部描述符支持一种属性：

COUNT，表示查询结果有多少列，及条目描述符有多少个。

条目描述符支持多种属性*：*

CARDINALITY: 结果集中的行数
DATA: 实际的数据项
TYPE: 列的数据类型
DATETIME_INTERVAL_CODE: 当TYPE是9时，1 表示 DATE， 2 表示 TIME， 3 表示 TIMESTAMP， 4 表示 TIME WITH TIME ZONE， 5 表示 TIMESTAMP WITH TIME ZONE。
INDICATOR: 指示符
LENGTH：以字符计的数据长度
NAME：列名
OCTET_LENGTH：以字节计的数据长度
PRECISION：精度
SCALE：numeric类型小数的位数

SQLDA 描述符

sqlda_t是实际 SQLDA 的类型。

struct sqlda_struct
{
    char            sqldaid[8]; /* 包含一个字符串"SQLDA"*/
    long            sqldabc; /* 已分配空间的大小（字节）*/
    short           sqln; /* 当它被传递给使用USING关键词，它表示输入参数的数目。
                           * 在它被用作查询语句的输出时，它的值和sqld一样*/
    short           sqld; /*一个结果集中的属性的数量*/
    struct sqlda_struct *desc_next; /*如果查询返回不止一个记录,指向下一个sqlda_t*/
    struct sqlvar_struct sqlvar[1]; /*查询结果里的列数组*/
};

typedef struct sqlda_struct sqlda_t;

sqlvar_t保存一个列值和元数据

struct sqlvar_struct
{
    short          sqltype; /* 属性的类性标识符 */
    short          sqllen; /* 属性的二进制长度 */
    char          *sqldata; /* 查询得到的数据 */
    short         *sqlind; /* 空标识符， 表示非空，-1 表示空 */
    struct sqlname sqlname;/*属性的名称 */
};

typedef struct sqlvar_struct sqlvar_t;

struct sqlname sqlname保存属性的名称

#define NAMEDATALEN 64

struct sqlname
{
        short           length; /*属性长度*/
        char            data[NAMEDATALEN]; /*属性名称*/
};

SQLDA 使用示例

EXEC SQL include sqlda.h;

sqlda_t *sqlda1; /* 用于输出的描述符 */
sqlda_t *sqlda2; /* 用于输入的描述符 */
...
/*准备一个预备语句*/
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;
EXEC SQL DECLARE cur1 CURSOR FOR stmt1;

/*为输入描述符（sqlda2）赋值*/
sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));
sqlda2->sqln = 2; /* 输入变量的数量 */
/*d.datname=postgres*/
sqlda2->sqlvar[0].sqltype = ECPGt_char;
sqlda2->sqlvar[0].sqldata = "postgres";
sqlda2->sqlvar[0].sqllen  = 8;
/*d.oid=1*/
intval = 1;
sqlda2->sqlvar[1].sqltype = ECPGt_int;
sqlda2->sqlvar[1].sqldata = (char *)&intval;
sqlda2->sqlvar[1].sqllen  = sizeof(intval);

/* 用输入描述符（sqlda2）打开一个游标。 */
EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2
while(1)
{
  
    /*将游标fetch到的值存到输出描述符(sqlda1)中*/
    EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;
        ....
    /*遍历输出描述符链表*/
    for (cur_sqlda = sqlda1 ;
         cur_sqlda != NULL ;
         cur_sqlda = cur_sqlda->desc_next)
    {
        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';
    }
}
...

2.7 预处理指令

include

如果某文件包含 SQL 预处理器所需的信息，需要SQL INCLUDE 语句包含此文件，否则使用普通的#include语句即可。

EXEC SQL INCLUDE name；

define 和 undef

ECPG define 和 undefine 语句会被预编译器替换。如果是一个ECPG查询中使用的常量，不能使用#include。

EXEC SQL DEFINE name;
EXEC SQL DEFINE name value;

2.8 编译运行

运行流程

写好pgc文件后，需要使用ecpg程序将pgc编程成C文件来使用，具体的实现流程如下所示：

暂时无法在飞书文档外展示此内容

/*ecpg预编译器处理，完整语句见下一节*/
ecpg program.pgc

/*生成.o文件*/
cc -I/usr/local/pgsql/include -c program.c

/*生成可执行文件*/
cc -o prog1 prog1.o -L/usr/local/pgsql/lib -lecpg

ecpg命令

ecpg安装在bin目录下，它的作用是将嵌入式 SQL 语句的 C 程序转换为普通 C 代码。输出文件可以被任何 C 编译器工具链处理。

ecpg [option...] file...

*option*可以是以下值：

-c：自动从 SQL 代码生成确定的 C 代码
-C：设置兼容模式
-D：用ECPG语言定义符号
-I: 指定额外的include目录
-i: 解析系统的include文件
-r *option*: 选择运行时选项：no_indicator:不使用指示符显示是否为空，prepare:所有语句都提前准备，questionmarks:允许使用问号作为占位符
-t: 打开自动提交
-v:打印额外信息

makefile

编译链接的过程可以写为makefile

ECPG = ecpg
CC = gcc

INCLUDES = -I$(shell pg_config --includedir)
LIBPATH = -L$(shell pg_config --libdir)
CFLAGS += $(INCLUDES)
LDFLAGS += -Wall -g
LDLIBS += $(LIBPATH) -lecpg -lpq -lpgtypes

%.c: %.pgc
    $(ECPG) -t -c $(INCLUDES) -o $@ $<
%: %.o
    $(CC) $(CFLAGS) $(LDFLAGS) $(LDLIBS) -o $@ $<

3.ECPG实践

以下为一个简单的ecpg demo。

#include <stdlib.h>
#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#include <unistd.h>
EXEC SQL include sqlca.h;
EXEC SQL include sqlda.h;

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

EXEC SQL WHENEVER NOT FOUND DO BREAK;
EXEC SQL WHENEVER SQLERROR CALL print_sqlca();

void
print_sqlca()
{
    fprintf(stderr, "==== sqlca ====\n");
    fprintf(stderr, "sqlcode: %ld\n", sqlca.sqlcode);
    fprintf(stderr, "sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml);
    fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    fprintf(stderr, "sqlerrd: %ld %ld %ld %ld %ld %ld\n", sqlca.sqlerrd[0],sqlca.sqlerrd[1],sqlca.sqlerrd[2],
                                                          sqlca.sqlerrd[3],sqlca.sqlerrd[4],sqlca.sqlerrd[5]);
    fprintf(stderr, "sqlwarn: %d %d %d %d %d %d %d %d\n", sqlca.sqlwarn[0], sqlca.sqlwarn[1], sqlca.sqlwarn[2],
                                                          sqlca.sqlwarn[3], sqlca.sqlwarn[4], sqlca.sqlwarn[5],
                                                          sqlca.sqlwarn[6], sqlca.sqlwarn[7]);
    fprintf(stderr, "sqlstate: %5s\n", sqlca.sqlstate);
    fprintf(stderr, "===============\n");
  
}
/*查询出连接对应的cn名*/
void
prinftcnname()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char nodename[1024];
    EXEC SQL END DECLARE SECTION;
    EXEC SQL DECLARE c CURSOR FOR SELECT node_name FROM pgxc_node where node_port::text IN (SELECT setting FROM pg_settings WHERE name = 'port');
    EXEC SQL OPEN c;
    for(;;)
    {
        /* get the next result row */
        EXEC SQL FETCH NEXT FROM c INTO :nodename;
 
        printf("%s\n",nodename
        );
    }
 
    EXEC SQL CLOSE c;
    EXEC SQL COMMIT;

}

void
selectandprint()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char query[1024] = "select * from pg_enum where (enumlabel =? OR enumlabel =?)";
    int intval;
    unsigned long long int longlongval;
    EXEC SQL END DECLARE SECTION;

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

    /* 为一个输入参数创建一个 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 = "GREEN";
    sqlda2->sqlvar[0].sqllen  = 5;

    sqlda2->sqlvar[1].sqltype = ECPGt_char;
    sqlda2->sqlvar[1].sqldata = "BLUE";
    sqlda2->sqlvar[1].sqllen  = 4;

    /* 用输入参数打开一个游标。 */
    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: /* 整数 */
                        memcpy(&intval, sqldata, sqllen);
                        snprintf(var_buf, sizeof(var_buf), "%d", intval);
                        break;

                    case ECPGt_long_long: /* 大整数 */
                        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;
}


int
main(void)
{  
    char *enumname = "postgres";
    int namelen = strlen(enumname);
    EXEC SQL BEGIN DECLARE SECTION;
    char *create_type_sql= "CREATE TYPE color AS ENUM ('RED', 'GREEN', 'BLUE')";
    const char *target1 = "tcp:postgresql://localhost:30025/postgres";
    const char *target2 = "tcp:postgresql://localhost:30026/postgres";
    const char *user = "zhoutianqi";
    const char *passwd = "********";
    EXEC SQL END DECLARE SECTION;

    /* 创建连接 */
    EXEC SQL CONNECT TO :target1 AS cn02 USER :user USING :passwd;
    EXEC SQL CONNECT TO :target2 AS cn01 USER :user USING :passwd;
    EXEC SQL SET CONNECTION cn02;
    /* 创建枚举类型*/
    EXEC SQL EXECUTE IMMEDIATE :create_type_sql;
    EXEC SQL COMMIT;
    /*打印cn02上枚举类对应的oid*/
    selectandprint();

    /*切换连接*/
    EXEC SQL SET CONNECTION cn01;
  
    /*打印cn01上枚举类对应的oid*/
    selectandprint();
  
    /*关闭连接*/
    EXEC SQL DISCONNECT ALL;

    return 0;
}

多次执行后的结果：

==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 39
sqlerrm.sqlerrmc: type "color" already exists on line 168 /*type 已经建立*/
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 42710
===============
cn02 /*cn02的查询结果*/
enumtypid = 16472 (type: 1)
enumsortorder = 00 00 00 40  (type: 12)
enumlabel = GREEN (type: 1)

enumtypid = 16472 (type: 1)
enumsortorder = 00 00 40 40  (type: 12)
enumlabel = BLUE (type: 1)

cn01 /*cn01的查询结果*/
enumtypid = 16436 (type: 1)
enumsortorder = 00 00 00 40  (type: 12)
enumlabel = GREEN (type: 1)

enumtypid = 16436 (type: 1)
enumsortorder = 00 00 40 40  (type: 12)
enumlabel = BLUE (type: 1)

活动

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

ECPG调研报告

1 ECPG介绍

2 ECPG语法

2.1 管理连接

建立连接

选择连接

关闭连接

2.2 运行SQL

增删改语句

查询语句

事务语句

预备语句

2.3 使用主变量

声明主变量

SQL与程序之间的值交换

程序到SQL

SQL到程序

类型映射

简单类型

字符串处理

特殊类型

复杂类型

数组类型

结构体类型

指示符

2.4 pgtyes库

numeric类型

date类型

timestamp类型

interval类型

decimal类型

2.5 错误处理

设置回调

sqlca

2.6 描述符

命名 SQL 描述符区域

SQLDA 描述符

SQLDA 使用示例

2.7 预处理指令

include

define 和 undef

2.8 编译运行

运行流程

ecpg命令

makefile

3.ECPG实践

ECPG调研报告

1 ECPG介绍

2 ECPG语法

2.1 管理连接

建立连接

选择连接

关闭连接

2.2 运行SQL

增删改语句

查询语句

事务语句

预备语句

2.3 使用主变量

声明主变量

SQL与程序之间的值交换

程序到SQL

SQL到程序

类型映射

简单类型

字符串处理

特殊类型

复杂类型

数组类型

结构体类型

指示符

2.4 pgtyes库

numeric类型

date类型