Thread: Unexpected ADO properties
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.
"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 >
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.
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.