pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Can't Update from MS Access (Was - Insight ODBC driver) 2+ messages / 2 participants [nested] [flat]
* Can't Update from MS Access (Was - Insight ODBC driver) @ 1998-04-17 18:03 ` David Hartwig <daveh@insightdist.com> 1 sibling, 0 replies; 2+ messages in thread From: David Hartwig @ 1998-04-17 18:03 UTC (permalink / raw) To: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>; pgsql-interfaces 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. Attachments:PSQLODBC_FAQ.htm (text/html, 2.8K) download
<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> </FONT> <BR>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: <BLOCKQUOTE><B><FONT COLOR="#333333">create table foo (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. 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?". <BR> <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. When the key is multipart the query looks something like: <BLOCKQUOTE><B>-- This is a 3 part key</B> <BR><B>select ... from foo where</B> <BR><B> (v1 = "?" AND v2 = "?" AND v3 ="?") OR -- line 1</B> <BR><B> (v1 = "?" AND v2 = "?" AND v3 ="?") OR -- line 2</B> <BR><B> ...</B> <BR><B> (v1 = "?" AND v2 = "?" AND v3 ="?") OR -- line 27</B> <BR><B> (v1 = "?" AND v2 = "?" AND v3 ="?"); -- 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 exponential amounts of memory and eventually crash. On some server systems a two part key will do the job. Using a table with couple rows the query may work in with Access because it builds the query with less lines. <BLOCKQUOTE> <BR> </BLOCKQUOTE> </BODY> </HTML>^ permalink raw reply [nested|flat] 2+ messages in threadvcard.vcf (text/x-vcard, 202B) download
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
* Re: [INTERFACES] Can't Update from MS Access (Was - Insight ODBC driver) @ 1998-04-18 08:09 ` Sbragion Denis <infotecn@tin.it> 1 sibling, 0 replies; 2+ messages in thread From: Sbragion Denis @ 1998-04-18 08:09 UTC (permalink / raw) To: David Hartwig <daveh@insightdist.com>; +Cc: pgsql-interfaces 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1998-04-18 08:09 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 1998-04-17 18:03 ` David Hartwig <daveh@insightdist.com> 1998-04-18 08:09 ` Sbragion Denis <infotecn@tin.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox