pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [INTERFACES] Slowly getting there...
6+ messages / 4 participants
[nested] [flat]
* Re: [INTERFACES] Slowly getting there...
@ 1998-04-22 15:34 Julia A.Case <julie@hub.org>
parent: The Hermit Hacker <scrappy@hub.org>
0 siblings, 1 reply; 6+ messages in thread
From: Julia A.Case @ 1998-04-22 15:34 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; Scott Tomer x6214 Austin <stomer@bmc.com>; +Cc: pgsql-interfaces
Quoting The Hermit Hacker (scrappy@hub.org):
> Hrmmm...it didn't ask me that...is there a way of setting it up
> after the fact?
>
Good question I'll check that out tonight... It should have asked
you that when you liked the tables. You can unlink them and re-link them
pretty easily though
Julie
--
[ Julia Anne Case ] [ Ships are safe inside the harbor, ]
[Programmer at large] [ but is that what ships are really for. ]
[ Admining Linux ] [ To thine own self be true. ]
[ Windows/WindowsNT ] [ Fair is where you take your cows to be judged. ]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Slowly getting there...
@ 1998-04-22 19:28 The Hermit Hacker <scrappy@hub.org>
0 siblings, 2 replies; 6+ messages in thread
From: The Hermit Hacker @ 1998-04-22 19:28 UTC (permalink / raw)
To: pgsql-interfaces
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...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] Slowly getting there...
@ 1998-04-22 20:17 Scott Tomer x6214 Austin <stomer@bmc.com>
parent: The Hermit Hacker <scrappy@hub.org>
1 sibling, 1 reply; 6+ messages in thread
From: Scott Tomer x6214 Austin @ 1998-04-22 20:17 UTC (permalink / raw)
To: scrappy@hub.org; +Cc: pgsql-interfaces
On Wed, 22 Apr 1998 scrappy@hub.org 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...
>
>
>
Did you setup the "Access" unique keys? When you setup the link tables,
It should have asked you to specify the unique key.
If you didn't specify these, the table is not updateable. :(
Scott
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] Slowly getting there...
@ 1998-04-22 20:30 The Hermit Hacker <scrappy@hub.org>
parent: Scott Tomer x6214 Austin <stomer@bmc.com>
0 siblings, 1 reply; 6+ messages in thread
From: The Hermit Hacker @ 1998-04-22 20:30 UTC (permalink / raw)
To: Scott Tomer x6214 Austin <stomer@bmc.com>; +Cc: pgsql-interfaces
On Wed, 22 Apr 1998, Scott Tomer x6214 Austin wrote:
>
> Did you setup the "Access" unique keys? When you setup the link tables,
> It should have asked you to specify the unique key.
Hrmmm...it didn't ask me that...is there a way of setting it up
after the fact?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] Slowly getting there...
@ 1998-04-22 21:10 David Hartwig <daveh@insightdist.com>
parent: The Hermit Hacker <scrappy@hub.org>
1 sibling, 0 replies; 6+ messages in thread
From: David Hartwig @ 1998-04-22 21:10 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-interfaces
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
* Re: [INTERFACES] Slowly getting there...
@ 1998-04-23 03:59 The Hermit Hacker <scrappy@hub.org>
parent: Julia A.Case <julie@hub.org>
0 siblings, 0 replies; 6+ messages in thread
From: The Hermit Hacker @ 1998-04-23 03:59 UTC (permalink / raw)
To: Julia A.Case <julie@hub.org>; +Cc: Scott Tomer x6214 Austin <stomer@bmc.com>; pgsql-interfaces
On Wed, 22 Apr 1998, Julia A.Case wrote:
> Quoting The Hermit Hacker (scrappy@hub.org):
> > Hrmmm...it didn't ask me that...is there a way of setting it up
> > after the fact?
> >
> Good question I'll check that out tonight... It should have asked
> you that when you liked the tables. You can unlink them and re-link them
> pretty easily though
Actually, it turns out it did ask me...I misunderstood the screen
when it came up :( Operator Error :(
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1998-04-23 03:59 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-04-22 19:28 Slowly getting there... The Hermit Hacker <scrappy@hub.org>
1998-04-22 20:17 ` Scott Tomer x6214 Austin <stomer@bmc.com>
1998-04-22 20:30 ` The Hermit Hacker <scrappy@hub.org>
1998-04-22 15:34 ` Julia A.Case <julie@hub.org>
1998-04-23 03:59 ` The Hermit Hacker <scrappy@hub.org>
1998-04-22 21:10 ` David Hartwig <daveh@insightdist.com>
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