Kadb中的ecpg编程

#include "stdio.h"

#include "stdlib.h"

#include "string.h"

// 相当于高级程序语言的全局变量定义

EXEC SQL BEGIN DECLARE SECTION; // 主变量声明开始

const char* target1 = "postgres@192.168.85.155:5432"; // 数据库1

const char* user1 = "postgres";

const char* passwd1 = "123456";

const char* target2 = "test@192.168.85.155:5432"; // 数据库2

const char* user2 = "postgres";

const char* passwd2 = "123456";

int rows = 0; // 表行数

char sno[10]; // s表的四个属性（s表是自定义的）

char sname[20];

int status;

char city[20];

EXEC SQL END DECLARE SECTION; // 主变量声明结束

int main() {

         EXEC SQL WHENEVER SQLERROR SQLPRINT; // error 时输出错误信息

         // 连接至数据库1

         EXEC SQL CONNECT TO :target1 USER :user1 USING :passwd1;

         printf("\n已连接%s\n", target1);

        EXEC SQL SET AUTOCOMMIT TO ON;

         EXEC SQL drop database if exists test; // 删除 spj 数据库

         EXEC SQL create database test; // 创建 spj 数据库

         // 断开数据库1

         EXEC SQL DISCONNECT CURRENT; // 这个不写也能运行，不知道有没有风险

         printf("\n已断开%s\n", target1);

         // 连接至数据库2

         EXEC SQL CONNECT TO :target2 USER :user2 USING :passwd2;

         printf("\n已连接%s\n", target2);

         // 下面五条exec sql，建表及插入

         EXEC SQL drop table if exists s;

         EXEC SQL CREATE TABLE IF NOT EXISTS s(sno char(10) primary key, sname char(20) unique, status int, city char(20));

         EXEC SQL insert into s(sno, sname, status, city) values('s1', 'APEX LEGEND', 114514, 'Wuhan');

         EXEC SQL insert into s(sno, sname, status, city) values('s2', 'DEVIL MAY CRY5', 616161, 'Wuhan');

         EXEC SQL insert into s(sno, sname, status, city) values('s3', 'WARFRAME', 123456, 'Wuhan');

         // 写入中文，数据库内是utf8，控制台输出的是gbk，会乱码

         EXEC SQL END; // 结束事务块

         EXEC SQL BEGIN; // BEGIN 事务块，DECLARE只能在事务块中使用

         // 定义游标，相当于每次读表的一行

         EXEC SQL DECLARE iterator CURSOR FOR

                                   select sno, sname, status, city

                                   from s;

         EXEC SQL OPEN iterator; // 打开游标

         EXEC SQL select count(*) into :rows from s; // 获取行数，只有主变量能被sql赋值

         int flag = 0;

         while (rows --) {

                  EXEC SQL fetch iterator into :sno, :sname, :status, :city; // fetch 获取一行元组

                  if (flag ++ == 0) { // 第一次把列名输出

                          printf("\n%-10s %-20s %-10s %-20s \n", "sno", "sname", "status", "city");

                  }

                  printf("%-10s %-20s %-10d %-20s \n", sno, sname, status, city);

         }

         EXEC SQL CLOSE iterator; // 关闭游标

         EXEC SQL END; // 结束事务块

         EXEC SQL DISCONNECT CURRENT; // 断开数据库2

         printf("\n已断开%s\n", target2);

         return 0;

}

/usr/local/pgsql/bin/ecpg proc.pgc

cc -I/usr/local/pgsql/include -c proc.c

cc -o proc proc.o -L/usr/local/pgsql/lib –lecpg

-bash-4.2$ ./proc

已连接postgres@192.168.85.155:5432

已断开postgres@192.168.85.155:5432

已连接test@192.168.85.155:5432

SQL error: there is already a transaction in progress

sno        sname                status     city                

s1         APEX LEGEND          114514     Wuhan               s1        

s2         DEVIL MAY CRY5       616161     Wuhan               s2        

s3         WARFRAME             123456     Wuhan               s3        

已断开test@192.168.85.155:5432