ADO and timestamp/date errors

Hi

I am trying to read a data set in ADO. If I exclude a timestamp field
that I
have in the return type, all works wonderfully. The moment I include
the
timestamp (i've even tried casting it as date) it bombs out with an
OLEDB
Exception of some type.

Has anyone got any ideas?

Thanks
Craig

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Craig Bryden wrote:

Hi

I am trying to read a data set in ADO. If I exclude a timestamp field that I
have in the return type, all works wonderfully. The moment I include the
timestamp (i've even tried casting it as date) it bombs out with an OLEDB
Exception of some type.

Try reading the text of the exception. You are using an old version of
OLE DB. Try the latest version
(http://gborg.postgresql.org/project/oledb). It should either work, or
give you a proper error message.

Has anyone got any ideas?

OLE DB is a binary interface. As such, it needs to know the binary
structure of any data type it encounters. It does not yet support all
the data types returned by Postgres 8. Please place (CC to this list)
the way you defined the column that offends OLE DB.

Please note that a newer version of OLE DB did have some additions in
the time types category, so I heartily suggest you try that first.

Thanks
Craig

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html

Can you please send me the table creation command, and the command that
failed? It sounds to me like a bug in the time handling by the OLE DB.

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html

Hi Shachar

Unfortunately due to a Non disclose agreement that I have on the
project, I
cannot send the whole table creation statement. But I have included
some
fields below:
CREATE TABLE tb_Player (
PlayerID SERIAL NOT NULL PRIMARY KEY ,
Firstname varchar (100) NOT NULL ,
Lastname varchar (100) NOT NULL ,
Initials varchar (15) NULL ,
EMail varchar (255) NOT NULL ,
DateCreated timestamp NOT NULL DEFAULT current_timestamp
) WITHOUT OIDS;

The C# code is:
string connString = "Location=MyDB;Provider=\"PostgreSQL.1\";User
ID=XXX;Data Source=localhost;Extended Properties=;Password=XXX";
System.Data.OleDb.OleDbConnection dbConn = new
System.Data.OleDb.OleDbConnection(connString);
dbConn.Open();
DSet1 = new DataSet();
System.Data.OleDb.OleDbCommand comm = new
System.Data.OleDb.OleDbCommand("select datecreated from
pr_GetPlayerByID(2500 )",dbConn);
System.Data.OleDb.OleDbDataAdapter da = new
System.Data.OleDb.OleDbDataAdapter(comm);
DataTable dt = new DataTable("Table1");
DSet1.Tables.Add(dt);
da.Fill(DSet1.Tables["Table1"]);
dgResults.DataSource = DSet1;
dgResults.DataMember = "Table1";

The type that is returned by the function is:
CREATE TYPE pr_getplayerbyid_returntype AS
(playerid int4,
firstname varchar(100),
lastname varchar(100),
initials varchar(15),
email varchar(255),
datecreated timestamp);

Thanks
Craig

----- Original Message -----
From: "Shachar Shemesh" <psql@shemesh.biz>
To: "Craig Bryden" <postgresql@bryden.co.za>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Tuesday, February 22, 2005 9:26 PM
Subject: Re: [GENERAL] ADO and timestamp/date errors

Craig Bryden wrote:

Hi Shachar

I have confirmed that I am running the latest version.
The error that I get (in ADO .Net) is :

***********************************************************************
*****
**

An unhandled exception of type 'System.ArgumentOutOfRangeException'
occurred in system.data.dll

Additional information:
Specified argument was out of the range of valid values.\r\nParameter
name: Year, Month, and Day parameters describe an unrepresentable

DateTime

Can you please send me the table creation command, and the command
that
failed? It sounds to me like a bug in the time handling by the OLE DB.

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html

---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend

Hi Craig,

Out of curiosity, beside portability, why haven't you use Npgsql?

regards,

-----
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate
On Feb 23, 2005, at 1:48 PM, Craig Bryden wrote:

Hi Shachar

Unfortunately due to a Non disclose agreement that I have on the
project, I
cannot send the whole table creation statement. But I have included
some
fields below:
CREATE TABLE tb_Player (
PlayerID SERIAL NOT NULL PRIMARY KEY ,
Firstname varchar (100) NOT NULL ,
Lastname varchar (100) NOT NULL ,
Initials varchar (15) NULL ,
EMail varchar (255) NOT NULL ,
DateCreated timestamp NOT NULL DEFAULT current_timestamp
) WITHOUT OIDS;

The C# code is:
string connString = "Location=MyDB;Provider=\"PostgreSQL.1\";User
ID=XXX;Data Source=localhost;Extended Properties=;Password=XXX";
System.Data.OleDb.OleDbConnection dbConn = new
System.Data.OleDb.OleDbConnection(connString);
dbConn.Open();
DSet1 = new DataSet();
System.Data.OleDb.OleDbCommand comm = new
System.Data.OleDb.OleDbCommand("select datecreated from
pr_GetPlayerByID(2500 )",dbConn);
System.Data.OleDb.OleDbDataAdapter da = new
System.Data.OleDb.OleDbDataAdapter(comm);
DataTable dt = new DataTable("Table1");
DSet1.Tables.Add(dt);
da.Fill(DSet1.Tables["Table1"]);
dgResults.DataSource = DSet1;
dgResults.DataMember = "Table1";

The type that is returned by the function is:
CREATE TYPE pr_getplayerbyid_returntype AS
(playerid int4,
firstname varchar(100),
lastname varchar(100),
initials varchar(15),
email varchar(255),
datecreated timestamp);

Thanks
Craig

----- Original Message -----
From: "Shachar Shemesh" <psql@shemesh.biz>
To: "Craig Bryden" <postgresql@bryden.co.za>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Tuesday, February 22, 2005 9:26 PM
Subject: Re: [GENERAL] ADO and timestamp/date errors

Craig Bryden wrote:

Hi Shachar

I have confirmed that I am running the latest version.
The error that I get (in ADO .Net) is :

***********************************************************************
*****
**

An unhandled exception of type 'System.ArgumentOutOfRangeException'
occurred in system.data.dll

Additional information:
Specified argument was out of the range of valid values.\r\nParameter
name: Year, Month, and Day parameters describe an unrepresentable

DateTime

Can you please send me the table creation command, and the command
that
failed? It sounds to me like a bug in the time handling by the OLE DB.

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html

---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq