> When i query a table in psql in the way mentioned below, I get the result
> that is also written below:
>
> postdb=> select id, trim(trailing ' ' from email), deliverytime from
> deferredtransaction;
> id|rtrim |deliverytime
> --+--------------+----------------------
> 1|xerxes(at)diku(dot)dk|1999-04-09 17:03:37+02
> 2|xerxes(at)diku(dot)dk|1999-04-09 17:03:38+02
> 3|xerxes(at)diku(dot)dk|1999-04-09 17:03:38+02
> 4|xerxes(at)diku(dot)dk|1999-04-09 17:03:38+02
> 6|111111(at)2222(dot)33|2000-01-01 00:00:00+01
> 7|111111(at)2222(dot)33|1999-12-31 00:00:00+01
> 8|111111(at)2222(dot)33|1999-04-12 00:00:00+02
> 9|111111(at)2222(dot)33|1999-04-13 00:00:00+02
> 10|xerxes(at)diku(dot)dk|1999-03-15 00:00:00+01
> 5|xerxes(at)diku(dot)dk|1999-04-21 13:00:00+02
> (10 rows)
>
> However, if I query the same table from inside a C++ program, I get the
> following result:
>
> $ per
> Number of rows received is 10
> Id = 1 E-mail = xerxes(at)diku(dot)dk Deliverytime = 1999-04-09 17:03:37+02
> Id = 2 E-mail = xerxes(at)diku(dot)dk Deliverytime = 1999-04-09 17:03:38+02
> Id = 3 E-mail = xerxes(at)diku(dot)dk Deliverytime = 1999-04-09 17:03:38+02
> Id = 4 E-mail = xerxes(at)diku(dot)dk Deliverytime = 1999-04-09 17:03:38+02
> Id = 6 E-mail = 111111(at)2222(dot)33 Deliverytime = 1999-04-09 17:03:38+02
> Id = 7 E-mail = 111111(at)2222(dot)33 Deliverytime = 1999-04-09 17:03:38+02
> Id = 8 E-mail = 111111(at)2222(dot)33 Deliverytime = 1999-04-09 17:03:38+02
> Id = 9 E-mail = 111111(at)2222(dot)33 Deliverytime = 1999-04-09 17:03:38+02
> Id = 10 E-mail = xerxes(at)diku(dot)dk Deliverytime = 1999-04-09 17:03:38+02
> Id = 5 E-mail = xerxes(at)diku(dot)dk Deliverytime = 1999-04-21 13:00:00+02
> $
>
> I'm giving a closer overview over the program and datastructures used, but
> at first I'll describe the problem.
>
> At first I created a table, and filled it with values in the first five
> tuples. In the datetime field "deliverytime" I filled it with now-values,
> and it has gone all right. All five rows (row id 1-5) were given the same
> time, although the first row is one second ahead of the rest :-).
>
> As I had to use the datetimes in my C++ program, I got an 8 byte long
> binary string that was quite difficult to use. In the manual, datetime
> fields are described as going from 4,713 BC to 1,465,001 AD. In
> microseconds. Of course it is rather difficult interprete such numers in
> ordinary dates - with intervals in seconds.
>
> Consequently, I choosed to read the datetimes as "deliverytime::text",
> something that seems to work. But when I inserted extra fields into the
> database table, I discovered that all five fields seemed to contain the
> same (the extra insertions were done to find out the meaning of certain
> values in American standard - they meant "normal time" and "summer time").
> However, I was shocked by finding out that although the new deliverytime
> fields were returned correctly in psql, they were not from the C++
> program. They simply returned the values that were used in the last four
> rows. And in _all_ other fields but date[time] fields, the correct value
> was returned.
>
> And when updating the row with id 5, it returns the new value correctly
> both in psql and the C++ program. The C++ program is so simple (and the
> data returned so "rational") that I don't suspect a programming error
> there. Neither do I suspect an error in interface routines used between
> C++ and PostgreSQL in my firm (International Data Post). And neither do I
> suspect an error in PostgreSQL as such because psql returns the correct
> value. But I do, however, suspect an error in libpg++ interface library.
>
> BTW, why hasn't C++ classes for internal PostgreSQL date types been
> programmed? It should had been quite easy for someone knowing the internal
> date type formats in PostgreSQL. Almost important for the rest of us.
> Classes with the proper methods for manipulationg dates, using dates in
> expressions - and printing them to a text stream and accessing them from a
> text stream [and of course with the proper manipulators :-)].
>
> Without such classes, the date types are unusable in C++ programs - unless
> first transformed into text strings as I do.
>
> =========================
>
> In C++, I'm using following query:
>
> *db << "SET DATESTYLE TO 'ISO';";
>
> *db << "SELECT id,"
> << "email,"
> << "subject,"
> << "responsedir,"
> << "deferreddir,"
> << "timestamp,"
> << "messagefile,"
> << "deliverytime::text "
> << "FROM deferredtransaction ;" ;
>
> The variable db is defined as such:
>
> postDb *db = new postDb("postdb");
>
> - where postDb is our standard class for communicating with PostgreSQL -
> and we are using the newest version (6.4.2).
>
> The output from C++ comes from the following statements:
>
> if(db->iStatus != PGRES_TUPLES_OK)
> cout << "Tuples NOT OK" << endl;
>
> cout << "Number of rows received is "<< db->iRows << endl;
>
> for(i=0; i< db->iRows; i++)
> {
> *db >> td;
> cout << "Id = " << setw(2) << td->id << " "
> << "E-mail = " << td->email << " "
> << "Deliverytime = " << td->deliverytime.s << " "
> << endl;
> }
>
> and td is defined as follows:
>
> struct datetime
> {
> char s[28];
>
> };
>
>
> struct tpHmDeferedTransaction
> {
> unsigned id;
> char email[128];
> char subject[128];
> char responsedir[256];
> char deferreddir[256];
> char timestamp[8];
> char messagefile[128];
> datetime deliverytime;
>
> tpHmDeferedTransaction()
> {
> memset(this, 0, sizeof(*this));
> }
>
> };
>
> main()
> {
> tpHmDeferedTransaction *td;
>
> td = new tpHmDeferedTransaction;
>
> The description of the table queried gives:
>
> postdb-> \d deferredtransaction
>
> Table = deferredtransaction
> +----------------------------------+----------------------------------+---
> ----+
> | Field | Type |
> Length|
> +----------------------------------+----------------------------------+---
> ----+
> | id | int4 not null default nextval('d |
> 4 |
> | email | char() |
> 128 |
> | subject | char() |
> 128 |
> | responsedir | char() |
> 256 |
> | deferreddir | char() |
> 256 |
> | timestamp | datetime |
> 8 |
> | messagefile | char() |
> 128 |
> | deliverytime | datetime |
> 8 |
> +----------------------------------+----------------------------------+---
> ----+
> Index: deferredtransaction_pkey
> postdb->
>
> --
> Per Erik Rønne, MSc <computer science>
> E-mail per(at)idp(dot)dk (office), xerxes(at)diku(dot)dk (home)
> Telephone +45 32 83 63 17 (office), +45 26 16 48 10 (mobile)
>