Thread: Unexpected ADO properties

Unexpected ADO properties

From
Bernard Henry Voynet
Date:
Hello
I  am  working  on  a project that access databases through ADO
over ODBC and must now include PostgreSQL.

The  code  of  this  project  needs  to  know  about the fields
caracteristics like whether or not it is:
- a primary key
- an autoincrement key
- nullabble
- updatable
- etc..

For   this   I   use   the  ADO  properties  that  give  theses
informations.

For a Jet database I get (for example):
serialkey
     Type = adInteger
     DefinedSize = 4
     Precision = 10
     NumericScale = 255
     Status = adRecOK
     Attributes = adFldFixed
     Properties (9)
          BASECATALOGNAME (adVarWChar) = (Null)
          BASECOLUMNNAME (adVarWChar) = serialkey
          BASESCHEMANAME (adVarWChar) = (Null)
          BASETABLENAME (adVarWChar) = genericfields
          CALCULATIONINFO (adVarBinary) = (Null)
          ISAUTOINCREMENT (adBoolean) = True
          KEYCOLUMN (adBoolean) = True
          OPTIMIZE (adBoolean) = False
          RELATIONCONDITIONS (adVarBinary) = (Null)

However, with  PostgreSQL  8.0.3  (psqlodc 08.00.0102), I don't get what is expected.

When I declare le serialkey field as as SERIAL PRIMARY KEY, instead of the expected result, I get:
serialkey
     Type = adInteger
     DefinedSize = 4
     Precision = 10
     NumericScale = 255
     Status = adRecOK
     Attributes = adFldUnknownUpdatable, adFldFixed, adFldIsNullable, adFldMayBeNull
     Properties (9)
          BASECATALOGNAME (adVarWChar) = (Null)
          BASECOLUMNNAME (adVarWChar) = serialkey
          BASESCHEMANAME (adVarWChar) = (Null)
          BASETABLENAME (adVarWChar) = genericfields
          CALCULATIONINFO (adVarBinary) = (Null)
          ISAUTOINCREMENT (adBoolean) = False
          KEYCOLUMN (adBoolean) = True
          OPTIMIZE (adBoolean) = False
          RELATIONCONDITIONS (adVarBinary) = (Null)

Where both:
- Attributes
- ISAUTOINCREMENT
Are not correct since serialkey is a non nullable serial key

Would you have any idea on this problem ?
And any help to get the right values ?

Best regards


_____________________________________________________________
Obtenez aussi votre adresse electronique gratuite de
Quebecemail.com http://www.quebecemail.com, un service gratuit et permanent.

Re: Unexpected ADO properties

From
"Jeff Eckermann"
Date:
"Bernard Henry Voynet" <bhv@quebecemail.com> wrote in message
news:20050820115339.D2B57834@dm21.mta.everyone.net...
> Hello
> I  am  working  on  a project that access databases through ADO
> over ODBC and must now include PostgreSQL.
>
> The  code  of  this  project  needs  to  know  about the fields
> caracteristics like whether or not it is:
> - a primary key
> - an autoincrement key
> - nullabble
> - updatable
> - etc..
>
> For   this   I   use   the  ADO  properties  that  give  theses
> informations.
>
> For a Jet database I get (for example):
> serialkey
>     Type = adInteger
>     DefinedSize = 4
>     Precision = 10
>     NumericScale = 255
>     Status = adRecOK
>     Attributes = adFldFixed
>     Properties (9)
>          BASECATALOGNAME (adVarWChar) = (Null)
>          BASECOLUMNNAME (adVarWChar) = serialkey
>          BASESCHEMANAME (adVarWChar) = (Null)
>          BASETABLENAME (adVarWChar) = genericfields
>          CALCULATIONINFO (adVarBinary) = (Null)
>          ISAUTOINCREMENT (adBoolean) = True
>          KEYCOLUMN (adBoolean) = True
>          OPTIMIZE (adBoolean) = False
>          RELATIONCONDITIONS (adVarBinary) = (Null)
>
> However, with  PostgreSQL  8.0.3  (psqlodc 08.00.0102), I don't get what
> is expected.
>
> When I declare le serialkey field as as SERIAL PRIMARY KEY, instead of the
> expected result, I get:
> serialkey
>     Type = adInteger
>     DefinedSize = 4
>     Precision = 10
>     NumericScale = 255
>     Status = adRecOK
>     Attributes = adFldUnknownUpdatable, adFldFixed, adFldIsNullable,
> adFldMayBeNull
>     Properties (9)
>          BASECATALOGNAME (adVarWChar) = (Null)
>          BASECOLUMNNAME (adVarWChar) = serialkey
>          BASESCHEMANAME (adVarWChar) = (Null)
>          BASETABLENAME (adVarWChar) = genericfields
>          CALCULATIONINFO (adVarBinary) = (Null)
>          ISAUTOINCREMENT (adBoolean) = False
>          KEYCOLUMN (adBoolean) = True
>          OPTIMIZE (adBoolean) = False
>          RELATIONCONDITIONS (adVarBinary) = (Null)
>
> Where both:
> - Attributes
> - ISAUTOINCREMENT
> Are not correct since serialkey is a non nullable serial key

PostgreSQL has no notion of "autoincrement".  The "serial" type is an
equivalent implementation, but is not actually a type in itself; it is an
integer type with a default value drawn from a sequence.

The values reported in the "attributes" are certainly wrong; this is
definitely a bug somewhere, but where, I don't know enough to say.  Perhaps
someone more knowledgeable can help.

>
> Would you have any idea on this problem ?
> And any help to get the right values ?
>
> Best regards
>
>
> _____________________________________________________________
> Obtenez aussi votre adresse electronique gratuite de
> Quebecemail.com http://www.quebecemail.com, un service gratuit et
> permanent.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>



Re: Unexpected ADO properties

From
Bernard Henry Voynet
Date:
Hye Jeff,

You are right when you say that : PostgreSQL has no notion of "autoincrement"

However, since :
- we are talking of pgsql-odbc (and not PostgreSQL);
- ODBC is an abstract layer that is supposed to uniformize backends access and isolate its users from the backends
"technicaldetails"; 
- the sequenced serial fields mechanism is a "technical detail" that offers an "autoincrement behavior";

Don't you think, therefore, that even though PostgreSQL has no notion of "autoincrement", it is the pgsql-odbc's
responsabilityto "hide" and manage this particular technical detail ? 

Technically speaking, don't you think that pgsql-odbc could in fact query PostgreSQL's catalog to check whether a
serialfield has its DEFAULT set with a 'nextval' and return, in such case, to ODBC user that it IS an autoincrement
field? 

Best regards



--- "Jeff Eckermann" <jeff_eckermann@yahoo.com> wrote:

From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
Date: Mon, 22 Aug 2005 13:54:03 +0930
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Unexpected ADO properties

"Bernard Henry Voynet" <bhv@quebecemail.com> wrote in message
news:20050820115339.D2B57834@dm21.mta.everyone.net...
> Hello
> I  am  working  on  a project that access databases through ADO
> over ODBC and must now include PostgreSQL.
>
> The  code  of  this  project  needs  to  know  about the fields
> caracteristics like whether or not it is:
> - a primary key
> - an autoincrement key
> - nullabble
> - updatable
> - etc..
>
> For   this   I   use   the  ADO  properties  that  give  theses
> informations.
>
> For a Jet database I get (for example):
> serialkey
>     Type = adInteger
>     DefinedSize = 4
>     Precision = 10
>     NumericScale = 255
>     Status = adRecOK
>     Attributes = adFldFixed
>     Properties (9)
>          BASECATALOGNAME (adVarWChar) = (Null)
>          BASECOLUMNNAME (adVarWChar) = serialkey
>          BASESCHEMANAME (adVarWChar) = (Null)
>          BASETABLENAME (adVarWChar) = genericfields
>          CALCULATIONINFO (adVarBinary) = (Null)
>          ISAUTOINCREMENT (adBoolean) = True
>          KEYCOLUMN (adBoolean) = True
>          OPTIMIZE (adBoolean) = False
>          RELATIONCONDITIONS (adVarBinary) = (Null)
>
> However, with  PostgreSQL  8.0.3  (psqlodc 08.00.0102), I don't get what
> is expected.
>
> When I declare le serialkey field as as SERIAL PRIMARY KEY, instead of the
> expected result, I get:
> serialkey
>     Type = adInteger
>     DefinedSize = 4
>     Precision = 10
>     NumericScale = 255
>     Status = adRecOK
>     Attributes = adFldUnknownUpdatable, adFldFixed, adFldIsNullable,
> adFldMayBeNull
>     Properties (9)
>          BASECATALOGNAME (adVarWChar) = (Null)
>          BASECOLUMNNAME (adVarWChar) = serialkey
>          BASESCHEMANAME (adVarWChar) = (Null)
>          BASETABLENAME (adVarWChar) = genericfields
>          CALCULATIONINFO (adVarBinary) = (Null)
>          ISAUTOINCREMENT (adBoolean) = False
>          KEYCOLUMN (adBoolean) = True
>          OPTIMIZE (adBoolean) = False
>          RELATIONCONDITIONS (adVarBinary) = (Null)
>
> Where both:
> - Attributes
> - ISAUTOINCREMENT
> Are not correct since serialkey is a non nullable serial key

PostgreSQL has no notion of "autoincrement".  The "serial" type is an
equivalent implementation, but is not actually a type in itself; it is an
integer type with a default value drawn from a sequence.

The values reported in the "attributes" are certainly wrong; this is
definitely a bug somewhere, but where, I don't know enough to say.  Perhaps
someone more knowledgeable can help.

>
> Would you have any idea on this problem ?
> And any help to get the right values ?
>
> Best regards
>
>
> _____________________________________________________________
> Obtenez aussi votre adresse electronique gratuite de
> Quebecemail.com http://www.quebecemail.com, un service gratuit et
> permanent.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org


_____________________________________________________________
Obtenez aussi votre adresse electronique gratuite de
Quebecemail.com http://www.quebecemail.com, un service gratuit et permanent.

Re: Unexpected ADO properties

From
Bernard Henry Voynet
Date:
Hye Jeff,

You are right when you say that : PostgreSQL has no notion of "autoincrement"

However, we are talking of pgsql-odbc.

ODBC is an abstract layer that "hides" databases technical details.
Even though PostgreSQL uses some "technical details" for giving the "autoincrement" behavior, on the (ODBC) user point
ofview, it still is an "autoincrement" behavior. 

Therefore, concerning PostgreSQL it is the pgsql-odbc responsability to manage this "technical detail".

Technically speaking, pgsql-odbc could in fact query PostgreSQL's catalog to check whether a serial field has its
DEFAULTset with a 'nextval' and return, in such case, to ODBC user that it IS an autoincrement field. 

Best regards


--- "Jeff Eckermann" <jeff_eckermann@yahoo.com> wrote:

From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
Date: Mon, 22 Aug 2005 13:54:03 +0930
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Unexpected ADO properties

"Bernard Henry Voynet" <bhv@quebecemail.com> wrote in message
news:20050820115339.D2B57834@dm21.mta.everyone.net...
> Hello
> I  am  working  on  a project that access databases through ADO
> over ODBC and must now include PostgreSQL.
>
> The  code  of  this  project  needs  to  know  about the fields
> caracteristics like whether or not it is:
> - a primary key
> - an autoincrement key
> - nullabble
> - updatable
> - etc..
>
> For   this   I   use   the  ADO  properties  that  give  theses
> informations.
>
> For a Jet database I get (for example):
> serialkey
>     Type = adInteger
>     DefinedSize = 4
>     Precision = 10
>     NumericScale = 255
>     Status = adRecOK
>     Attributes = adFldFixed
>     Properties (9)
>          BASECATALOGNAME (adVarWChar) = (Null)
>          BASECOLUMNNAME (adVarWChar) = serialkey
>          BASESCHEMANAME (adVarWChar) = (Null)
>          BASETABLENAME (adVarWChar) = genericfields
>          CALCULATIONINFO (adVarBinary) = (Null)
>          ISAUTOINCREMENT (adBoolean) = True
>          KEYCOLUMN (adBoolean) = True
>          OPTIMIZE (adBoolean) = False
>          RELATIONCONDITIONS (adVarBinary) = (Null)
>
> However, with  PostgreSQL  8.0.3  (psqlodc 08.00.0102), I don't get what
> is expected.
>
> When I declare le serialkey field as as SERIAL PRIMARY KEY, instead of the
> expected result, I get:
> serialkey
>     Type = adInteger
>     DefinedSize = 4
>     Precision = 10
>     NumericScale = 255
>     Status = adRecOK
>     Attributes = adFldUnknownUpdatable, adFldFixed, adFldIsNullable,
> adFldMayBeNull
>     Properties (9)
>          BASECATALOGNAME (adVarWChar) = (Null)
>          BASECOLUMNNAME (adVarWChar) = serialkey
>          BASESCHEMANAME (adVarWChar) = (Null)
>          BASETABLENAME (adVarWChar) = genericfields
>          CALCULATIONINFO (adVarBinary) = (Null)
>          ISAUTOINCREMENT (adBoolean) = False
>          KEYCOLUMN (adBoolean) = True
>          OPTIMIZE (adBoolean) = False
>          RELATIONCONDITIONS (adVarBinary) = (Null)
>
> Where both:
> - Attributes
> - ISAUTOINCREMENT
> Are not correct since serialkey is a non nullable serial key

PostgreSQL has no notion of "autoincrement".  The "serial" type is an
equivalent implementation, but is not actually a type in itself; it is an
integer type with a default value drawn from a sequence.

The values reported in the "attributes" are certainly wrong; this is
definitely a bug somewhere, but where, I don't know enough to say.  Perhaps
someone more knowledgeable can help.

>
> Would you have any idea on this problem ?
> And any help to get the right values ?
>
> Best regards
>
>
> _____________________________________________________________
> Obtenez aussi votre adresse electronique gratuite de
> Quebecemail.com http://www.quebecemail.com, un service gratuit et
> permanent.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org


_____________________________________________________________
Obtenez aussi votre adresse electronique gratuite de
Quebecemail.com http://www.quebecemail.com, un service gratuit et permanent.