Обсуждение: Odbc and Visual Basic

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.

Bye!

    Dr. Sbragion Denis
    InfoTecna
    Tel, Fax: +39 39 2324054
    URL: http://space.tin.it/internet/dsbragio

infotecn@tin.it said:
>     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 don't quite follow this.

I have VB5 apps which update PostgreSQL tables which do not have unique
indexes. The same apps work under VB4 as well and with both the old and new
ODBC drivers.

I have never tried the Access route (and never will) but understood that the
unique index problem was an Access thing rather than VB.

Does anyone have any pointers to canonical statements on this?

Cheers and thanks,
Stephen.

========================================================================
Stephen Davies Consulting                                            scldad@sdc.com.au
Adelaide, South Australia.                                             Voice: 61-8-82728863
Computing & Network solutions.                                     Fax: 61-8-82741015

Hello,

At 18.11 27/04/98 +0930, you wrote:
>I don't quite follow this.
>
>I have VB5 apps which update PostgreSQL tables which do not have unique
>indexes. The same apps work under VB4 as well and with both the old and new
>ODBC drivers.
>
>I have never tried the Access route (and never will) but understood that the
>unique index problem was an Access thing rather than VB.

I'm pretty sure about this. It's in the VB 5 help (I'll search it again in
the help and I'll quote it out in my next message). At least if you wan't
to use datacontrols and recordset to do updates. Of course you can always
perform the same updates using passthrough queries, and this, of course,
works.
    You can do some test for example using VisData. It uses datacontrols to
perform operations and it needs unique indexes on the tables to allow updates.
Well, if I'm wrong, please explain me how to do it. I'm really interested.

Hope it helps.

Bye !

    Dr. Sbragion Denis
    InfoTecna
    Tel, Fax: +39 39 2324054
    URL: http://space.tin.it/internet/dsbragio

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.

Hello,

This message is concerning the unique index problem.

There is an ODBC function called SQLStatistics which currently does not properly
handle reporting unique indexes.  It never has in the past either.  It assumed that
Postgres could not have unique indexes.  I have modified this function to return
correct information concerning unique indexes from the Postgres system tables.  I am
currently testing it.  A new driver will be up on the web site soon.

Regards,

Byron

Hello,

At 08.14 27/04/98 -0400, David Hartwig wrote:
>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,
...

Unfortunately VB 5.0 seems to ignore the SQLPrimaryKey() call and insists
looking for a unique index. It was the first thing I tried when I saw the
announce for the support of that call. I also know that the concept of
Primary Key is different from the concept of Unique Index (though on most
database system, including PostgreSQL, the Primary Key concept is
implemented using unique indexes), and using the SQLPrimaryKey() call would
be The Right Way To Do Things (tm), but as long as Microsoft doesn't
understand it... I don't know what would have happened if the ODBC
interface specification have come from someone else than Microsoft itself...

Thanks anyway !

    Dr. Sbragion Denis
    InfoTecna
    Tel, Fax: +39 39 2324054
    URL: http://space.tin.it/internet/dsbragio

Hello,

At 09.41 27/04/98 -0400, Byron Nikolaidis wrote:
>This message is concerning the unique index problem.
...
>currently testing it.  A new driver will be up on the web site soon.

Great ! Just a little comment: I asked for index support 3 days ago,
without being in any hurry; it was just a little question. Today Byron is
already testing it. May be within few days or even less we'll have a test
release with index support to play with. When commercial software will be
able to provide such a good support?

Thanks again Byron.



    Dr. Sbragion Denis
    InfoTecna
    Tel, Fax: +39 39 2324054
    URL: http://space.tin.it/internet/dsbragio

See Byron's posting on this subject.   We are getting some interesting result
from our yet-to-be-posted hacks.   The cool part is that, MS Access no longer
requests the user to tag the primary key at link time.   The downside, though,
is that it has a higher potential for exposing a known bug in the backend's
optimizer.   The bug has to with multipart keys and the large numbers of ANDs
and ORs generated in a single statement while browsing tables.

Sbragion Denis wrote:

> Hello,
>
> At 08.14 27/04/98 -0400, David Hartwig wrote:
> >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,
> ...
>
> Unfortunately VB 5.0 seems to ignore the SQLPrimaryKey() call and insists
> looking for a unique index. It was the first thing I tried when I saw the
> announce for the support of that call. I also know that the concept of
> Primary Key is different from the concept of Unique Index (though on most
> database system, including PostgreSQL, the Primary Key concept is
> implemented using unique indexes), and using the SQLPrimaryKey() call would
> be The Right Way To Do Things (tm), but as long as Microsoft doesn't
> understand it... I don't know what would have happened if the ODBC
> interface specification have come from someone else than Microsoft itself...
>