Re: [INTERFACES] Odbc and Visual Basic

Sbragion Denis wrote:

> Hello,
>
> first of all my compliments to Byron for the upcoming new ODBC driver.
> Really good job. Now a little request.
> One of the things that prevents wider use of PostODBC under Visual Basic
> (at least v. 5.0) is the lack of a good index support in the ODBC driver
> itself. Visual Basic requires that at least one unique index should be
> available on a table to allow updates. Currently this is circumvented using
> an Access database with all the tables linked in it and specifying at the
> Access level wich is the unique key for the tables. Anyway, to use the
> Visual Basic ODBCDirect functionality true index support is required to
> allow udpates. Is it difficult to implement it ?
> I saw there's a similar problem also in the OpenLink Postgres ODBC driver.
> To implement the index functionality they used a dummy table with a special
> procedure (buggy! I had to modify it myself to make it work) that took the
> index list from the postgres system tables an put them in the dummy table.
> Then, after running such procedure, the driver was able to report the index
> list to Visual Basic. Of course the procedures needed to be run again every
> time a new index was added. Things didn't work anyway because of other bugs
> in the OpenLink drivers, but this is another story.
> I think that if exists a procedure to put the index list in a table it
> should exists also a procedure to get them directly from the Postgres
> system tables and report them through the driver.
> Of course I'm ready to help and give any further information to help
> implementing this ODBC functionality. I can also perform any test needed to
> help developing it and, if needed, I can also try to find some little spare
> time to help coding things.
>
> Let me know.
>

Here is an excerpt from the soon to be published ODBC FAQ.

How do I get my application to recognize primary keys?

SQLPrimaryKeys() is implemented in the driver. As of the driver's release, however,
there was no way to query the PostgreSQL system tables to discover a table's primary
key. Therefore the following convention was used. The driver queries the system
tables in search of a unique index named with the using "{table}_key". Example:

create table foo (id integer, name varchar(20));
create unique index foo_key on foo using btree(id);

Creating this index does not guarantee that your application is using the
SQLPrimaryKeys() call. For example, MS Access 7.0 & 97 require the user to manually
specify the key at link time. This key specification is required to modify a table
from MS Access.

=============================================

UPDATE NOTE: The very recent build of the driver uses the pattern "{table}_pkey" to
locate the a unique index. This index is automatically generated by the "PRIMARY
KEY" constraint of the CREATE TABLE statement.