Date: Wed, 24 Feb 1999 21:36:40 +0100
From: Michael Meskes <Michael_Meskes@topmail.de>
To: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Newbie Q: How to deal with date/time with libpq or
	ecpg?
Message-ID: <19990224213640.A2636@gmx.net>
Mail-Followup-To: pgsql-interfaces@postgreSQL.org
References: <36D41D02.F4415E1F@insuremarket.com>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="yrj/dFKFPuw6o+aM"
In-Reply-To: <36D41D02.F4415E1F@insuremarket.com>;
	from Michael Zhang on Wed, Feb 24, 1999 at 10:38:42AM -0500


--yrj/dFKFPuw6o+aM
Content-Type: text/plain; charset=us-ascii

On Wed, Feb 24, 1999 at 10:38:42AM -0500, Michael Zhang wrote:
> my web applications use many date/time manipulations. For example, I
> would like to dynamicly display a date of birth column with specified
> format. I could do it on Oracle like this:

I'm not sure about format changing but libpq returns all data as string and
ecpg does put it into a char array without a problem.

I attach an example.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!

--yrj/dFKFPuw6o+aM
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="test2.pgc"

#include <stdio.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 smallinteger; };
typedef union { int integer; short smallinteger; } ind;

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	{	varchar name[8];
					birthinfo birth;
				} personal;
	struct personal_indicator {	int ind_name;
					birthinfo ind_birth;
				  } ind_personal;
	int ind_married;
	ind children;
	ind ind_children;
	char married[11];
	c testname="Petra";
	char *query="select name, born, age, married, children from meskes where name = :var1";
exec sql end declare section;

	exec sql var ind_married is long;

	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 ('Petra', '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.integer:ind_children.smallinteger;
		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 %s", married);
		if (ind_children.smallinteger >= 0)
			printf(", children = %d", children.integer);
		putchar('\n');
	}

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

	/* and now the 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 :testname;

	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.smallinteger;
		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 %s", married);
		if (ind_children.smallinteger >= 0)
			printf(", children = %d", children.integer);
		putchar('\n');
	}

	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);
}

--yrj/dFKFPuw6o+aM--