Re: Q:Postgres 7.0 & Access'97? - Mailing list pgsql-sql

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 11831432mailto:yura@vpcit.ru