From daveh@insightdist.com Fri Apr  17 18:39:00 1998
Received: from u1.abs.net (root@u1.abs.net [207.114.0.131]) by hub.org
	(8.8.8/8.7.5) with ESMTP id OAA13858 for
	<pgsql-interfaces@postgreSQL.org>;
	Fri, 17 Apr 1998 14:39:00 -0400 (EDT)
Received: from insightdist.com (nobody@localhost)
	by u1.abs.net (8.8.5/8.8.5) with UUCP id OAA25988
	for pgsql-interfaces@postgreSQL.org;
	Fri, 17 Apr 1998 14:15:04 -0400 (EDT)
X-Authentication-Warning: u1.abs.net: nobody set sender to
	insightdist.com!daveh using -f
Received: by insightdist.com (AIX 3.2/UCB 5.64/4.03)
	id AA35184; Fri, 17 Apr 1998 14:10:28 -0400
Received: from daveh by ceodev (AIX 4.1/UCB 5.64/4.03)
	id AA78252; Fri, 17 Apr 1998 14:05:02 -0400
Message-Id: <35379956.9CF38CD0@insightdist.com>
Date: Fri, 17 Apr 1998 14:03:02 -0400
From: David Hartwig <daveh@insightdist.com>
Organization: Insight Distribution Systems
X-Mailer: Mozilla 4.05 [en] (Win95; I)
Mime-Version: 1.0
To: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>,
	pgsql-interfaces <pgsql-interfaces@postgreSQL.org>
Subject: Can't Update from MS Access  (Was - Insight ODBC driver)
References: <199804170615.IAA01926@gate.va.fh-ulm.de>
Content-Type: multipart/mixed; boundary="------------28FC7B415F8C27FE394B42D6"

This is a multi-part message in MIME format.
--------------28FC7B415F8C27FE394B42D6
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit



Olaf Mittelstaedt wrote:

> Thank you Byron and David for your answers (and Marc for redirecting
> my message to pqsql-interfaces).
>
> Here a few more details on my problem.
>
> > 1.  I am not as familiar with Access 2.0.   But the 7.0 and 97 versions
> > require a unique key to be specified at link time for an update to be
> > allowed.   I want to be sure that you have done that, and that you not
> > referring to the "create unique index" statement which creates the index
> > in the database table on the server.
>
> Access 2.0 should automatically use a unique index on the database
> server if it's present. Below the statements I've used to create,
> populate and test the table.
>

Access 7.0 & 97 do not automatically use a unique index on the database server if
it's present.   They need to be told the key at link time.   I do not possess a
2.0. version.   I am including a portion of a drafted of the new FAQ.   I hope this
helps.

PS.    You are using a 32 bit Windows OS aren't you.

--------------28FC7B415F8C27FE394B42D6
Content-Type: text/html; charset=us-ascii; name="PSQLODBC_FAQ.htm"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="PSQLODBC_FAQ.htm"
Content-Base: "file:///G|/pages/work/PSQLODBC_FAQ.htm"

<HTML>
<HEAD>
   <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
   <META NAME="Author" CONTENT="David Hartwig">
   <META NAME="GENERATOR" CONTENT="Mozilla/4.05 [en] (Win95; I) [Netscape]">
   <TITLE>PSQLODBC FAQ</TITLE>
</HEAD>
<BODY>

<CENTER><FONT SIZE=+2>PSQL ODBC - FAQ</FONT></CENTER>

<CENTER><FONT SIZE=+2></FONT></CENTER>
<FONT SIZE=+1><U>How do I get my application to recognize primary keys?</U>&nbsp;</FONT>
<BR>SQLPrimaryKeys() is implemented in the driver.&nbsp;&nbsp; As of the
driver's release, however, there was no way to query the PostgreSQL system
tables to discover a table's primary key.&nbsp; Therefore the following
convention was used.&nbsp;&nbsp; The driver queries the system tables in
search of a unique index named with the using "{table}_key".&nbsp;&nbsp;
Example:
<BLOCKQUOTE><B><FONT COLOR="#333333">create table foo&nbsp; (id integer,
name varchar(20));</FONT></B>
<BR><B><FONT COLOR="#333333">create unique index foo_key on foo using btree(id);</FONT></B></BLOCKQUOTE>
<FONT COLOR="#333333">Creating this index does not guarantee that your
application is using the </FONT>SQLPrimaryKeys() call.&nbsp;&nbsp; For
example, MS Access 7.0 &amp; 97 require the user to manually specify the
key at link time.&nbsp; This key specification is required to modify a
table from MA Access.&nbsp;&nbsp; See "Why does the PosgreSQL backend crash
every time I browse some tables in MS Access?".
<BR>&nbsp;

<P><U><FONT SIZE=+1>Why does the PostgreSQL backend crash every time I
browse some tables in MS Access?</FONT></U>
<BR>When an Access user specifies a key at link time, the key is used to
generate queries to browse through the rows of the table.&nbsp;&nbsp;&nbsp;
When the key is multipart&nbsp; the query looks something like:
<BLOCKQUOTE><B>--&nbsp;&nbsp; This is a 3 part key</B>
<BR><B>select ... from foo where</B>
<BR><B>&nbsp;&nbsp;&nbsp; (v1 = "?" AND v2 = "?" AND v3 ="?") OR&nbsp;&nbsp;&nbsp;&nbsp;
-- line 1</B>
<BR><B>&nbsp;&nbsp;&nbsp; (v1 = "?" AND v2 = "?" AND v3 ="?") OR&nbsp;&nbsp;&nbsp;&nbsp;
-- line 2</B>
<BR><B>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
...</B>
<BR><B>&nbsp;&nbsp;&nbsp; (v1 = "?" AND v2 = "?" AND v3 ="?") OR&nbsp;&nbsp;&nbsp;&nbsp;
--&nbsp; line 27</B>
<BR><B>&nbsp;&nbsp;&nbsp; (v1 = "?" AND v2 = "?" AND v3 ="?");&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
--&nbsp; line 28</B></BLOCKQUOTE>
Unforunatly as of the latest release of the driver, this kind of query
cause the optimizer in the PostgreSQL backend to devour&nbsp; exponential
amounts of memory and eventually crash.&nbsp;&nbsp;&nbsp; On some server
systems a two part key will do the job.&nbsp;&nbsp; Using a table with
couple rows the query may work in with Access because it builds the query
with less lines.
<BLOCKQUOTE>&nbsp;
<BR>&nbsp;</BLOCKQUOTE>
&nbsp;
</BODY>
</HTML>

--------------28FC7B415F8C27FE394B42D6
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for David Hartwig
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             David Hartwig
n:              Hartwig;David
email;internet: daveh@insightdist.com
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard


--------------28FC7B415F8C27FE394B42D6--




From infotecn@tin.it Sun Apr  19 03:00:59 1998
Received: from mail.tol.it (mail.tin.it [194.243.154.49]) by hub.org
	(8.8.8/8.7.5) with ESMTP id XAA28307 for
	<pgsql-interfaces@postgreSQL.org>;
	Sat, 18 Apr 1998 23:00:59 -0400 (EDT)
Received: from Server.InfoTecna.com (Monza6-106.tin.it [195.103.249.105])
	by mail.tol.it (8.8.4/8.8.4) with ESMTP
	id FAA26639; Sun, 19 Apr 1998 05:00:33 +0200 (MET DST)
Received: from tm3.InfoTecna.com (Tm1.InfoTecna.com [192.168.1.1])
	by Server.InfoTecna.com (8.8.5/8.8.5) with SMTP id KAA01672;
	Sat, 18 Apr 1998 10:09:16 +0200
Message-Id: <3.0.5.32.19980418100915.007f1c60@MBox.InfoTecna.com>
X-Sender: denis@MBox.InfoTecna.com
X-Mailer: QUALCOMM Windows Eudora Light Version 3.0.5 (32)
Date: Sat, 18 Apr 1998 10:09:15 +0200
To: David Hartwig <daveh@insightdist.com>
From: Sbragion Denis <infotecn@tin.it>
Subject: Re: [INTERFACES] Can't Update from MS Access  (Was - Insight
	ODBC driver)
Cc: pgsql-interfaces@postgreSQL.org
In-Reply-To: <35379956.9CF38CD0@insightdist.com>
References: <199804170615.IAA01926@gate.va.fh-ulm.de>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

Hello,

At 14.03 17/04/98 -0400, you wrote:
>From the FAQ part:
...
>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 MA Access.   See "Why does the PosgreSQL
backend crash every time I >browse some tables in MS Access?". 

actually when you specify something like:

create table test06
        (
                Posizione int4 not null primary key,
                Testo char(50),
                Campo float8,
                DataEOra datetime
        );

postgres automagically creates an index named test06_pkey (note the 'p' in
fron of 'key'). I think changing the ODBC driver to look for such type of
index, instead of '{table}_key', would simplify things a lot.

Hope it helps.

P.S. My compliments to all the great development effort coming up on PostODBC.



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