Cannot retrieve timestamp filed value from db (PostgreSQLDirect .NET 2.50.16)

Cannot retrieve timestamp filed value from db (PostgreSQLDirect .NET 2.50.16)

Postby Wojtek » Mon 21 Aug 2006 18:14

Dear Colleagues

I have been using PostgreSQLDirect .NET 2.50.16 + VS2005 Prof.
I am experiencing strange problem with getting data from timestamp field.
Initially I have been developing small application using Windows based Postgres 8.1 server (localhost). All was OK.
When I moved db to production Linux machine the problem surfaced.

Sample query to show the problem:
"SELECT id, date FROM orders WHERE id=6900"

Whne I query db directly in PgAdmin I get results:

id ; date
-------------------------------------------
6900 ; 2006-06-05 09:22:44.437441


Then I use following code in C#:

myPgConn = new PgSqlConnection(cnnStr);
myPgConn.Open();
PgSqlCommand cmd = new PgSqlCommand("SELECT id, date FROM orders WHERE id=6900");
cmd.Connection = myPgConn;
PgSqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
textBox1.AppendText( "Fld 1 type :" + rdr.GetDataTypeName(0).ToString());
textBox1.AppendText( "\nFld 1 value:" + rdr.GetValue(0).ToString());
textBox1.AppendText( "\nFld 2 type :" + rdr.GetDataTypeName(1).ToString());
textBox1.AppendText( "\nFld 2 value:" + rdr.GetValue(1).ToString() + "\n");
}
rdr.Close();


This code returns following results:
Fld 1 type :BIGINT
Fld 1 value:6900
Fld 2 type :TIMESTAMP
Fld 2 value:2000-01-01 00:00:00 <-- Not correct !

I use protocol version ProtocolVersion.Ver30 (in PgSqlConnectionStringBuilder - not shown in example)

When I switch to Ver20 problem is eliminated - the code returns values:
Fld 1 type :BIGINT
Fld 1 value:6900
Fld 2 type :TIMESTAMP
Fld 2 value:2006-06-05 09:22:44 <-- Correct !


Db is ver. Postgres 8.1 on Linux (Ubuntu 6) machine.
Entries in postgres.conf:
...
datestyle = 'iso, dmy'
client_encoding = unicode
lc_messages = 'pl_PL.UTF-8'
lc_monetary = 'pl_PL.UTF-8' # locale for monetaryformatting
lc_numeric = 'pl_PL.UTF-8' # locale for number formatting
lc_time = 'pl_PL.UTF-8' # locale for time formatting

Suppose if I do not want to switch to protocol Ver30 how can I get rid of this problem ?

Appreciate your comments.

Best regards,
Wojtek Pawlowski
Wojtek
 
Posts: 14
Joined: Sun 20 Nov 2005 18:51
Location: Warsaw

Postby Alexey » Tue 22 Aug 2006 07:41

We are investigating this problem.
Please perform a bit more testing with different timestamp values (e.g., 1999-18-05 23:59:02, etc.)
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Similar Problem With Postgres Installed on Linux

Postby gabrielnasser » Thu 03 May 2007 15:03

Hello,

I have been using an application I developed with driver version 2.55.23 with various versions of Postgres installed on Windows - no problems.

We just installed Postgres 8.1.8 on i486-pc-linux-gnu (Debian 4.1.1-21). We tried connecting our application (running on Windows) to the same exact database on Linux but we are facing problems with timestamps.

The query is akin to

select commissioned_time::timestamp, location_code, item_guid
from serial_item, item_master
where commissioned_time >= ('2007-05-01')::timestamp
AND commissioned_time < ('2007-05-04')::timestamp
AND item_master.full_code = serial_item.item_full_code

we have records that match 3-May-2007 but they are not being returned when running agains Postgres on Linux. When were remove the WHERE clause that compares the time range then it returns all the records on the Linux install but the 'commissioned_time' field shows

'2000-01-01 00:00:00'

for each record even though the records have all been entered the past few days.

Any guidance is appreciated.
gabrielnasser
 
Posts: 15
Joined: Wed 25 Jan 2006 19:57

Postby Alexey » Mon 07 May 2007 11:20

Is it possible to open your database to the internet, so we could investigate this problem?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby gabrielnasser » Mon 07 May 2007 12:18

I am afraid it is not possible to expose the database.
gabrielnasser
 
Posts: 15
Joined: Wed 25 Jan 2006 19:57

Postby Alexey » Mon 07 May 2007 12:31

This problem can be OS-specific. We might be unable to reproduce it with our linux box.
Is it possible to create a dummy database for testing purposes and open it to me?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Zoran » Tue 15 May 2007 13:53

We have the same problem. Using the same IIS 6.0 web server and a Windows 2003 Postgres 8 db, all the results below are correct (no "1.1.2000" or "151953-08-13"). When we switch to Ubuntu Linux Postgres server, the results are as follows after "//" at the and of each example.

// Example 1, CURRENT_TIMESTAMP:

cnn.CommandText = "UPDATE MetaStore SET Moment=CURRENT_TIMESTAMP WHERE Id=32";
cnn.executeNonQuery();

cnn.CommandText = "SELECT Moment FROM MetaStore WHERE Id=32";
System.DateTime dt1 = (System.DateTime)cnn.executeScalar(); //C#: "1.1.2000 0:00:00", pgAdmin: "2007-05-15 12:46:14.145087"


// Example 2, hardcoded parameter, the results the same as in example 1:

cnn.CommandText = "UPDATE MetaStore SET Moment='2007-12-31 17:50' WHERE Id=32";
cnn.executeNonQuery();

cnn.CommandText = "SELECT Moment FROM MetaStore WHERE Id=32";
System.DateTime dt2 = (System.DateTime)cnn.executeScalar(); //C#: "1.1.2000 0:00:00", pgAdmin: "2007-12-31 17:50:00"


// Example 3, external parameter:

cnn.CommandText = "UPDATE MetaStore SET Moment=$1 WHERE Id=32";
cnn.appendParameter(OdbcType.DateTime, Microline.Type.DateTime.Now);
cnn.executeNonQuery();

cnn.clearParameters();
cnn.CommandText = "SELECT Moment FROM MetaStore WHERE Id=32";
System.DateTime dt3 = (System.DateTime)cnn.executeScalar(); //C#: "15.5.2007 12:39:50", pgAdmin: "151953-08-13 08:08:47.07328"


It seems that the 2 formats are incopatible but each method can read correctly update it made.
Our locale for date is "dd.MM.yyyy." (including "." after yyyy).

There are also problems with TIME data type which I can document.
Zoran
 
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia

Postby Alexey » Wed 16 May 2007 06:17

Alexey wrote:Is it possible to open your database to the internet, so we could investigate this problem?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Zoran » Wed 16 May 2007 14:08

This is our production db. I will check if we will be able to make a ghost copy of the disk and then open it for the external access.
Zoran
 
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia

Postby Alexey » Thu 17 May 2007 06:32

Try to do your best.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

A new database

Postby Zoran » Sun 20 May 2007 23:56

We made a new database. You will be able to reproduce the problem. Please send me your e-mail to priba@microline.hr so that I can give you the URL and user account.
Zoran
 
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia

Postby Alexey » Mon 21 May 2007 07:43

Sent.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Problem solved

Postby Zoran » Thu 07 Jun 2007 22:39

The last version of Corelab driver works fine. There are no more problems with either TIMESTAMP or TIME type.
Zoran
 
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia

Postby Alexey » Mon 11 Jun 2007 07:45

Thanks for the information.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL