Skip site navigation (1) Skip section navigation (2)

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

From: Per Rønne <per(at)idp(dot)dk>
To: "'pgsql-interfaces(at)postgresql(dot)org'" <pgsql-interfaces(at)postgresql(dot)org>
Subject: Possible error in libpg++ interface, date[time] fields.
Date: 1999-04-21 14:44:09
Message-ID: B15FE41977A9D21195F00000836A4275119CC5@mars.idp.dk (view raw or flat)
Thread:
Lists: pgsql-interfaces
> 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)
> 

Responses

pgsql-interfaces by date

Next:From: Gene Selkov Jr.Date: 1999-04-21 15:02:08
Subject: Re: [SQL] perl and postgres. . .
Previous:From: Per RønneDate: 1999-04-21 13:12:55
Subject:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group