RE: [INTERFACES] Possible error in libpg++ interface, date[time] fields. - Mailing list pgsql-interfaces

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

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@diku.dk|1999-04-09 17:03:37+02
 2|xerxes@diku.dk|1999-04-09 17:03:38+02
 3|xerxes@diku.dk|1999-04-09 17:03:38+02
 4|xerxes@diku.dk|1999-04-09 17:03:38+02
 6|111111@2222.33|2000-01-01 00:00:00+01
 7|111111@2222.33|1999-12-31 00:00:00+01
 8|111111@2222.33|1999-04-12 00:00:00+02
 9|111111@2222.33|1999-04-13 00:00:00+02
10|xerxes@diku.dk|1999-03-15 00:00:00+01
 5|xerxes@diku.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@diku.dk Deliverytime = 1999-04-09 17:03:37+02
Id =  2 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  3 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  4 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  6 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id =  7 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id =  8 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id =  9 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id = 10 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  5 E-mail = xerxes@diku.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@idp.dk (office), xerxes@diku.dk (home)
Telephone +45 32 83 63 17 (office), +45 26 16 48 10 (mobile)