From: David Hartwig <daveh@insightdist.com>
To: The Hermit Hacker <scrappy@hub.org>
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Slowly getting there...
Date: Wed, 22 Apr 1998 17:10:36 -0400
Message-ID: <353E5CCB.7B640CC9@insightdist.com> (raw)
References: <Pine.NEB.3.95.980422152709.6405X-100000@hub.org>
The Hermit Hacker wrote:
> Just got Access to see my tables...but, if I create a 'form' so that I can
> edit the fields, it tells me "Unupdatable' :(
>
> When I created my File DSN, I 'unclicked' ReadOnly...so shouldn't this
> work? :(
>
> Thanks...
Slowly the noose tightens...
The problem here is that each application is implemented differently. Some
apps like Access 2.0 will query the database dynamically to find a unique
key. A unique key is required by most applications to do table updates.
Others, like MS Access 7.0 & 97, require the user to supply the unique key at
link time. The bottom line is that the app needs to know a unique key.
We have just discovered an bug in the driver that prevents the desired result
using the dynamic method used by Access 2.0. A patch is in the works.
MS Access 7.0 & 97 updates will work fine. Except for one thing...
Watch out for multipart keys. The backend does not react well to the queries
generated when browsing an updatable table with 3 or more key parts. See bug
report and TODO concerning lots of ANDs and ORs.
I am attaching a the start of a FAQ that addresses some of these issues.
Attachments:PSQLODBC_FAQ.htm (text/html, 7.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>
<P><U><FONT SIZE=+1>What can't I connect to the PostgreSQL server?</FONT></U>
<BR>1. Server not running.
<BR>2. Incorrect host name or port number.
<BR>3. Valid user name
<BR>4. pg_hba.conf
<P><U><FONT SIZE=+1>What data types does the driver support?</FONT></U>
<BR>The driver provides full support for all PostgreSQL standard data types.
These are: char, int2, int4, float4, float8, abstime, date datetime,
text.
<P>There is partial support for all other data types. Examples
of these: point, circle, box and arrays. String support
only is provided for these non-standard types. In other words,
they are returned as SQL_VARCHAR and can be displayed and updated like
any other data type. The resulting behavior will probably vary
some with each application and data type.
<P>In the case of int4[] and MS Access 97 it does an effective job.
The array can be displayed and updated cleanly.
Arithmetic is a little tricky. MS Access complains about
"t.a[1]" in the query builder. It does not like the syntax
and never sends it to the backend.
<P>The work around is to choose the SQL Pass-thru option. This will
allow you to build expressions like "t.a[0] * t.a[1]".
The hassle is that every time you run the query in Access 97 it prompts
you with a database connection dialog.
<P>MS Excel in combination w/ MS Query may provide a better solution.
It passes every query through.. I try to stay away from the
more exotic types if I know I am going to expose them to the public.
<P><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 complain about running
out of memory when some I browse tables in MS Access?</FONT></U>
<BR>When an Access user specifies a key at link time, this 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>
<BR><B>-- The question marks ar replaced with the key values</B></BLOCKQUOTE>
Unfortunately, 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 cause this to occur. Using a table
with couple rows the query may work in with Access because it builds the
query with OR clauses.
<BR>
<P><U><FONT SIZE=+1>With MS Access, why can't I GROUP BY, ORDER BY
use WHERE on columns which are of type "text"?</FONT></U>
<BR>Text fields are now being returned as SQL_LONGVARCHAR. As a result
MS Access treats these colomns as "Memo" types. This good news and
bad news. The good news is that you can store up to the PostgreSQL
block size limit in a text column. Currently, PostgreSQL has
a tuple limit of just under 8k.
<BR>
<P><U><FONT SIZE=+1>Can I use large objects or OLE?</FONT></U>
<BR>Large objects are mapped to LONGVARBINARY in the driver to allow storing
things like OLE objects in Microsoft Access. Multiple SQLPutData
and SQLGetData calls are usually used to send and retrieve these objects.
The driver creates a new large object and simply inserts its 'identifier'
into the respective table. However, since Postgres uses an 'Oid'
to identify a Large Object, it is necessary to create a new Postgres type
to be able to discriminate between an ordinary Oid and a Large Object Oid.
Until this new type becomes an official part of Postgres, it must be added
into the desired database and looked up for each connection. The
type used in the driver is simply called "lo" and here is the command used
to create it:
<P><B> create type lo (</B>
<BR><B> internallength=4,</B> <B>
externallength=10,</B>
<BR><B> input=int4in,</B> <B>output=int4out,</B>
<BR><B> default='',</B> <B>
passedbyvalue);</B>
<P><B> create table employee (</B>
<BR><B> id integer,</B>
<BR><B> name varchar(30),</B>
<BR><B> picture lo</B>
<BR><B> )</B>
<P>Once this is done, simply use the new 'lo' type to define columns in
that database. When the driver sees an 'lo' type, it will handle
it as SQL_LONGVARBINARY.
<P>Another important note is that this new type is lacking in functionality.
It will not cleanup after itself on updates and deletes, thus leaving orphans
around and using up extra disk space. And currently, Postgres does
not support the vacuuming of large objects.
<P>It would not be too difficult to write a interim stand-alone cleanup
process to run at some interval on the server. It is only a matter
of searching pg_attribute for lo data type columns and building a list
of lo's by querying each table that contains lo's. Then compare this
list with with the xinv.* in pg_class. The xinv.* with out
a pointer are orphans and should be dropped.
<P>Hopefully in the future, a real large object data type will be available
as a base type. But for now, it sure is fun to stick a Word
document, Visio document, or AVI of a dancing baby into a database column,
even if you will fill up your server's hard disk after a while!
<P><U><FONT SIZE=+1>Why when I join tables in MS Access the does it sometimes
complain about a GROUP BY not being in the target list?</FONT></U>
<BR>
<P>
<BR>
<BR>
<BR>
<BLOCKQUOTE>
<BR> </BLOCKQUOTE>
</BODY>
</HTML>
vcard.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: daveh@insightdist.com, scrappy@hub.org
Subject: Re: [INTERFACES] Slowly getting there...
In-Reply-To: <353E5CCB.7B640CC9@insightdist.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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