pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedODBC Doc
3+ messages / 3 participants
[nested] [flat]
* ODBC Doc
@ 1998-04-23 19:11 David Hartwig <daveh@insightdist.com>
0 siblings, 2 replies; 3+ messages in thread
From: David Hartwig @ 1998-04-23 19:11 UTC (permalink / raw)
To: Patrice Hede <patrice@idf.net>; pgsql-interfaces; Documentation List <pgsql-docs@postgreSQL.org>
Patrice,
Here is what I have for the FAQ ODBC so far. I believe this is it
needs a lot more work. I will try to update you as frequent questions
arise.
Attachments:
PSQLODBC_FAQ.htm (text/html, 8.9K) 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 is the cause of the following message: </FONT></U><FONT SIZE=+1>
ODBC connection to '<I>database</I>' failed.</FONT>
<OL>
<LI>
Either incorrect hostname, port number, database name, user name,
or password in the data source setup.</LI>
<LI>
The postmaster not running with the -i option.</LI>
<LI>
The pg_hba.conf file in $PGPATH/data directory is not configured to allow
your host to connect. (Note that if password authentication
is required for your remote host, a password is required. Unpassworded
accounts can not connect.)</LI>
</OL>
<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>This message comes from the PostgreSQL backend. Sometimes
graphical query builders will generate unexpected queries in an atempt
to optimize. Often a single query will be broken into multiple queries.
The smaller queries will GROUB BY columns not in the target (projection).
The solution is to incrementally include join columns which are not
in the target list until the error is resolved. Then,
most graphical query builders will allow the user to hide the extranious
columns from the visual field of the result.
<BR>
<P><U><FONT SIZE=+1>Why does the browsing large tables lock the respective
tables from updates?</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: [DOCS] ODBC Doc
@ 1998-04-24 01:24 Henry B. Hotz <hotz@jpl.nasa.gov>
parent: David Hartwig <daveh@insightdist.com>
1 sibling, 0 replies; 3+ messages in thread
From: Henry B. Hotz @ 1998-04-24 01:24 UTC (permalink / raw)
To: David Hartwig <daveh@insightdist.com>; Patrice Hede <patrice@idf.net>; pgsql-interfaces; Documentation List <pgsql-docs@postgreSQL.org>
At 12:11 PM -0700 4/23/98, David Hartwig wrote:
>In the case of int4[] and MS Access 97 it does an effective job. The
>array
>MS Excel in combination w/ MS Query may provide a better solution. It passes
Anyone have any idea about Mac Office 98 support? I presume this is all on
a PC since the last I looked that was the only freeware ODBC driver
available.
Maybe Office will support JDBC connections someday? I should check this.
Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ODBC Doc
@ 1998-04-24 11:58 Patrice Hédé <patrice@idf.net>
parent: David Hartwig <daveh@insightdist.com>
1 sibling, 0 replies; 3+ messages in thread
From: Patrice Hédé @ 1998-04-24 11:58 UTC (permalink / raw)
To: David Hartwig <daveh@insightdist.com>; +Cc: pgsql-interfaces; Documentation List <pgsql-docs@postgreSQL.org>
On Thu, 23 Apr 1998, David Hartwig wrote:
> Here is what I have for the FAQ ODBC so far. I believe this is it
> needs a lot more work. I will try to update you as frequent questions
> arise.
Thank you !
I quickly browsed through it. I'll incorporate it into the FAQ when I do
this part. This is good so far. I'll sure welcome any addition you may
have :)
This is also true for the other interfaces !
Patrice
--
Patrice HÉDÉ --------------------------------- patrice@idf.net -----
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1998-04-24 11:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-04-23 19:11 ODBC Doc David Hartwig <daveh@insightdist.com>
1998-04-24 01:24 ` Henry B. Hotz <hotz@jpl.nasa.gov>
1998-04-24 11:58 ` Patrice Hédé <patrice@idf.net>
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