test3.pgc

关系型数据库 Postgresql 6.5.2

exec sql include header_test;

exec sql type str is varchar[10];

int
main ()
{
	typedef struct { long born; short age; } birthinfo;
	exec sql type birthinfo is struct { long born; short age; };
exec sql begin declare section;
	struct personal_struct	{	str name;
					birthinfo birth;
				} personal;
	struct personal_indicator {	int ind_name;
					birthinfo ind_birth;
				  } ind_personal;
	int *ind_married = NULL;
	int children;
	int ind_children;
	str *married = NULL;
	char *wifesname="Petra";
	char *query="select name, born, age, married, children from meskes where name = :var1";
exec sql end declare section;

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

	char msg[128], command[128];
	FILE *dbgs;

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

	strcpy(msg, "connect");
	exec sql connect to unix:postgresql://localhost: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 (:wifesname, '19900404', 3);
	exec sql insert into meskes(name, born, age, married, children) values ('Michael', 19660117, 33, '19900404', 3);
	exec sql insert into meskes(name, born, age) values ('Carsten', 19910103, 8);
	exec sql insert into meskes(name, born, age) values ('Marc', 19930907, 5);
	exec sql insert into meskes(name, born, age) values ('Chris', 19970923, 1);

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

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

	exec sql whenever not found do break;

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

		free(married);
		married = NULL;
	}

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

	/* and now the same query with prepare */
	exec sql prepare MM from "select name, born, age, married, children from meskes where name = ?";
	exec sql declare prep cursor for MM;

	strcpy(msg, "open");
	exec sql open prep using :wifesname;

	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:ind_children;
		printf("%8.8s", personal.name.arr);
		if (ind_personal.ind_birth.born >= 0)
			printf(", born %d", personal.birth.born);
		if (ind_personal.ind_birth.age >= 0)
			printf(", age = %d", personal.birth.age);
		if (ind_married >= 0)
			printf(", married %10.10s", married->arr);
		if (ind_children >= 0)
			printf(", children = %d", children);
		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);
}