RE: [INTERFACES] Possible error in libpg++ interface, date[time] fields.

I have just done an extra experiment.

>From C++ I have:

Number of rows received is 10
Id = 1 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:37+02
Id = 2 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 3 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 4 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 6 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 7 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 8 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 9 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 10 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 5 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-21 13:00:00+02
$

I psql I have, and I do, as follows:

postdb=> select * from t;
id|email |deliverytime
--+--------------+----------------------
1|xerxes(at)diku(dot)dk|1999-04-09 <mailto:1|xerxes(at)diku(dot)dk|1999-04-09>
17:03:37+02
2|xerxes(at)diku(dot)dk|1999-04-09 <mailto:2|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
3|xerxes(at)diku(dot)dk|1999-04-09 <mailto:3|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
4|xerxes(at)diku(dot)dk|1999-04-09 <mailto:4|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
6|111111(at)2222(dot)33|2000-01-01 <mailto:6|111111(at)2222(dot)33|2000-01-01>
00:00:00+01
7|111111(at)2222(dot)33|1999-12-31 <mailto:7|111111(at)2222(dot)33|1999-12-31>
00:00:00+01
8|111111(at)2222(dot)33|1999-04-12 <mailto:8|111111(at)2222(dot)33|1999-04-12>
00:00:00+02
9|111111(at)2222(dot)33|1999-04-13 <mailto:9|111111(at)2222(dot)33|1999-04-13>
00:00:00+02
10|xerxes(at)diku(dot)dk|1999-03-15 <mailto:10|xerxes(at)diku(dot)dk|1999-03-15>
00:00:00+01
5|xerxes(at)diku(dot)dk|1999-04-21 <mailto:5|xerxes(at)diku(dot)dk|1999-04-21>
13:00:00+02
(10 rows)

postdb=> update t set deliverytime = '1999-12-31 00:00:00+01' where id = 7;
UPDATE 0
postdb=> update deferredtransaction set deliverytime = '1999-12-31
00:00:00+01' where id = 7;
UPDATE 1
postdb=> select * from t;
id|email |deliverytime
--+--------------+----------------------
1|xerxes(at)diku(dot)dk|1999-04-09 <mailto:1|xerxes(at)diku(dot)dk|1999-04-09>
17:03:37+02
2|xerxes(at)diku(dot)dk|1999-04-09 <mailto:2|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
3|xerxes(at)diku(dot)dk|1999-04-09 <mailto:3|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
4|xerxes(at)diku(dot)dk|1999-04-09 <mailto:4|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
6|111111(at)2222(dot)33|2000-01-01 <mailto:6|111111(at)2222(dot)33|2000-01-01>
00:00:00+01
8|111111(at)2222(dot)33|1999-04-12 <mailto:8|111111(at)2222(dot)33|1999-04-12>
00:00:00+02
9|111111(at)2222(dot)33|1999-04-13 <mailto:9|111111(at)2222(dot)33|1999-04-13>
00:00:00+02
10|xerxes(at)diku(dot)dk|1999-03-15 <mailto:10|xerxes(at)diku(dot)dk|1999-03-15>
00:00:00+01
5|xerxes(at)diku(dot)dk|1999-04-21 <mailto:5|xerxes(at)diku(dot)dk|1999-04-21>
13:00:00+02
7|111111(at)2222(dot)33|1999-12-31 <mailto:7|111111(at)2222(dot)33|1999-12-31>
00:00:00+01
(10 rows)

postdb=>

- where t is a view [BTW, it _should_ be possible to update through such a
view!].

When having done this update, that is updated the deliverytime value to the
date is was before, the C++ programs returns with this output:

$ per
Number of rows received is 10
Id = 1 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:37+02
Id = 2 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 3 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 4 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 6 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 8 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 9 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 10 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 5 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-21 13:00:00+02
Id = 7 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-21 13:00:00+02

Thus, it seems as if _some_ rows will always return the same deliverytime
value as the delivery time value in the former row in the query.

Consequently I rephrase the C++ query by adding an order clause [order by
id]. The I get this result:

$ per
Number of rows received is 10
Id = 1 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:37+02
Id = 2 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 3 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 4 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 5 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-21 13:00:00+02
Id = 6 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-21 13:00:00+02
Id = 7 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-21 13:00:00+02
Id = 8 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-21 13:00:00+02
Id = 9 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-21 13:00:00+02
Id = 10 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-21 13:00:00+02
$

Rows 6-10 now takes the values of row 5 - the last "right" row.

And if I swap the order I get the following result:

$ per
Number of rows received is 10
Id = 10 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
Id = 9 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
Id = 8 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
Id = 7 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
Id = 6 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
Id = 5 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-21 13:00:00+02
Id = 4 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 3 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 2 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 1 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:37+02
$

I conclude that there is a kind of mark on rows 6-10. In psql the rows are
always treated correctly. I C++, through libpg++, a date or timebased field
will be treated as if it has the same value as does the row before - and if
there is no "before" row in the query, it will return a blank value! Even if
the row is later updated with a new value in this field.

--
Per Erik Rønne
E-mail per(at)idp(dot)dk (office)
Telephone +45 32 83 63 17 (office), +45 26 16 48 10 (mobile)

-----Original Message-----
From: Per Rønne [mailto:per(at)idp(dot)dk]
Sent: 21. april 1999 16:44
To: 'pgsql-interfaces(at)postgresql(dot)org'
Subject: [INTERFACES] Possible error in libpg++ interface, date[time]
fields.

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)