Re: Q:Postgres 7.0 & Access'97?

Hello Emils,

Once, Tuesday, June 13, 2000, 8:42:05 PM, you wrote:

EK> I am using Postgres 7.0 on SuSE Linux 6.2 and Postdrv.exe
EK> 6.50.0000 (as downloaded from /latest/ dir of PG FTP site
EK> yesterday).

EK> Now, I have experienced 4 issues immediately with Postgres via
EK> ODBC from Access and I was wondering whether you could have
EK> any idea what could possibly be done about them:

EK> 1. Every time a query runs from Access, I get an error on the server
EK> coneole:
EK> pq_recvbuf: EOF the client closed the connection unexpectedly
EK> The query seems to run fine though.

I am getting the same messages, looks like cause somewhere in Access
because I never got such messages when using other programs working
with postgresql via odbc. And this messages appearing not after any
queries. Since it doesn't disturb to work I didn't
look into this.

EK> 2. The tables on the SQL server were exported from Access tables,
EK> using Access' File | Export -> ODBC connection.

We used Pgupt for this: http://dspace.dial.pipex.com/boylesa/pgupt/pgupt.shtml
and some hand works.
Also look at the http://www.sevainc.com/Access/index.html

EK> Now I can only access them from psql monitor if I enclose both the
EK> table and field names in double quotes, like "Field1". The
EK> tables/fields do have alphanumeric chars in them only (A-z,0-9). If I
EK> try to access table "Test" like:
EK> \d Test, I get: can't find relation 'test'.
EK> (Note the caps both in table name & error msg)
EK> \d "Test" works.

When you create table with name in double quote (create table "Table"
...) then you must to use the same characters case (Table) in queries.
It's written somewhere in docs or in FAQs.

EK> 3. The connection seems to be QUITE slow (approximately 1-2
EK> seconds to show a form in Access), considering that the total of 19
EK> tables in the dbase contain a maximum of a couple of thousand of
EK> rows (I think even less). The server is a HP Netserver PIII650 and
EK> does not at the moment run anything else. The client computer is
EK> not very up-to-date, yet the same Access db with local tables runs
EK> practically instantly.

I think 1-2 seconds is not a demonstrative time. Try to run a
complex queries on a large tables in order to estimate speed.

EK> 4. This is almost definitely an Access problem, but even if so: are
EK> there any workarounds available?
EK> A WHERE clause that compares a boolean value with a boolean
EK> constant gives error: can't compare bool and int, use explicit cast.
EK> Ie. in Access trying to use a SELECT .... WHERE a=True , yields
EK> the above error, considering that a is a boolean field.
EK> Same query works from psql monitor.

You need to create operator "=" for int4 and bool. I used the
following (you need to have a plpgsl language installed in postgresql):

drop operator = (bool,int4);
drop function MsAccessBool(bool,int4);
create function MsAccessBool(bool,int4)
returns bool
as '
begin
if $1 is NULL then
return NULL;
end if;
if $1 is TRUE then
if $2 <> 0 then
return TRUE;
end if;
else
if $2 = 0 then
return TRUE;
end if;
end if;
return FALSE;
end;
'
language 'plpgsql';

create operator = (
leftarg=bool,
rightarg=int4,
procedure=MsAccessBool,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);

Also uncheck "bool as char" option in odbc driver properties.

EK> Thanks a LOT in advance for any comments.

EK> Emils, trying to make Access frontend work with Postgres
EK> backend.

Yury, successfuly maded Access frontend work with Postgres backend :-)

--
Best regards,
Yury ICQ 11831432
mailto:yura(at)vpcit(dot)ru