test2.pgc

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

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

exec sql include header_test;

exec sql type c is char reference;
typedef char* c;

exec sql type ind is union { int integer; short smallint; };
typedef union { int integer; short smallint; } ind;

#define BUFFERSIZ 8
exec sql type str is varchar[BUFFERSIZ];

exec sql declare cur cursor for
       select name, born, age, married, children from meskes;

int
main (void)
{
	exec sql struct birthinfo { long born; short age; };
exec sql begin declare section;
	struct personal_struct	{	str name;
					struct birthinfo birth;
				} personal, *p;
	struct personal_indicator {	int ind_name;
					struct birthinfo ind_birth;
				  } ind_personal, *i;
	ind ind_children;
	char *query="select name, born, age, married, children from meskes where name = :var1";
exec sql end declare section;

	exec sql char *married = NULL;
	exec sql float ind_married;
	exec sql ind children;

	exec sql var ind_married is long;

	char msg[128];
	FILE *dbgs;

	if ((dbgs = fopen("log", "w")) != NULL)
                ECPGdebug(1, dbgs);

	strcpy(msg, "connect");
	exec sql connect to tcp:postgresql://127.0.0.1:5432/mm;

	strcpy(msg, "create");
	exec sql create table meskes(name char(8), born integer, age smallint, married date, children integer);

	strcpy(msg, "insert");
	exec sql insert into meskes(name, married, children) values ('Petra', '19900404', 3);
	exec sql insert into meskes(name, born, age, married, children) values ('Michael', 19660117, 35, '19900404', 3);
	exec sql insert into meskes(name, born, age) values ('Carsten', 19910103,10);
	exec sql insert into meskes(name, born, age) values ('Marc', 19930907, 8);
	exec sql insert into meskes(name, born, age) values ('Chris', 19970923, 4);

	strcpy(msg, "commit");
	exec sql commit;

	strcpy(msg, "open");
	exec sql open cur;

	exec sql whenever not found do break;

	p=&personal;
	i=&ind_personal;
	memset(i, 0, sizeof(ind_personal));
	while (1) {
		strcpy(msg, "fetch");
		exec sql fetch cur into :p:i, :married:ind_married, :children.integer:ind_children.smallint;
		printf("%8.8s", personal.name.arr);
		if (i->ind_birth.born >= 0)
			printf(", born %ld", personal.birth.born);
		if (i->ind_birth.age >= 0)
			printf(", age = %d", personal.birth.age);
		if ((long)ind_married >= 0)
			printf(", married %s", married);
		if (ind_children.smallint >= 0)
			printf(", children = %d", children.integer);
		putchar('\n');

		free(married);
		married = NULL;
	}

	strcpy(msg, "close");
	exec sql close cur;

	/* and now a same query with prepare */
	exec sql prepare MM from :query;
	exec sql declare prep cursor for MM;

	strcpy(msg, "open");
	exec sql open prep using 'Petra';

	exec sql whenever not found do break;

	while (1) {
		strcpy(msg, "fetch");
		exec sql fetch in prep into :personal:ind_personal, :married:ind_married, :children.integer:ind_children.smallint;
		printf("%8.8s", personal.name.arr);
		if (ind_personal.ind_birth.born >= 0)
			printf(", born %ld", personal.birth.born);
		if (ind_personal.ind_birth.age >= 0)
			printf(", age = %d", personal.birth.age);
		if ((long)ind_married >= 0)
			printf(", married %s", married);
		if (ind_children.smallint >= 0)
			printf(", children = %d", children.integer);
		putchar('\n');
	}

	free(married);

	strcpy(msg, "close");
	exec sql close prep;

	strcpy(msg, "drop");
	exec sql drop table meskes;

	strcpy(msg, "commit");
	exec sql commit;

	strcpy(msg, "disconnect"); 
	exec sql disconnect;

	if (dbgs != NULL)
                fclose(dbgs);

	return (0);
}