Обсуждение: 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

any sample code?

regards,

-----
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate
On Feb 21, 2005, at 2:05 PM, 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.
>
> 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

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

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
>
>
>

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
>

Hi Jonel

In our project, we have some Visual C++ Applications that will also talk to0
the database. We are trying to stick to common drivers. The VC++ clients are
having the same problem as I am reporting.

Thanks
Craig


----- Original Message -----
From: "Jonel Rienton" <jonel@road14.com>
To: "Craig Bryden" <postgresql@bryden.co.za>
Cc: "Shachar Shemesh" <psql@shemesh.biz>; "pgsql"
<pgsql-general@postgresql.org>
Sent: Wednesday, February 23, 2005 11:09 PM
Subject: Re: [GENERAL] ADO and timestamp/date errors


> 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
>
>