Thread: Access -> ODBC -> postgres

Hi!

I am trying to create a front-end to an existing postgres database in MS Access 2002 using 8.01.20 Unicode ODBC driver. Some of the simpler queries were working, but as soon as I try to use a dynamic parameter, the query sent to the server gets syntactically incorrect. I have attached some logs to make it clear:
                           hdbc=151272720, stmt=151283976, result=151266488
                 manual_result=1, prepare=0, internal=0
                 bindings=0, bindings_allocated=0
                 parameters=151288880, parameters_allocated=1
                 statement_type=0, statement='SELECT "public"."organization_user"."id","public"."bidding_auction"."id","public"."organization_company"."id" FROM "public"."bidding_auction","public"."organization_user","public"."organization_company" WHERE (("public"."organization_user"."id" = "public"."bidding_auction"."organizer_id" ) AND (("public"."organization_user"."company_id" = "public"."organization_company"."id" ) AND ("public"."organization_company"."id" =  ? ) ) ) '
                 stmt_with_params='SELECT "public"."organization_user"."id","public"."bidding_auction"."id","public"."organization_company"."id" FROM "public"."bidding_auction","public"."organization_user","public"."organization_company" WHERE (("public"."organization_user"."id" = "public"."bidding_auction"."organizer_id" ) AND (("public"."organization_user"."company_id" = "public"."organization_company"."id" ) AND ("public"."organization_company"."id" =  38'
                 data_at_exec=-1, current_exec_param=-1, put_data=0
                 currTuple=-1, current_col=-1, lobj_fd=-1
                 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
                 cursor_name='SQL_CUR09046908'
                 ----------------QResult Info -------------------------------
                 fields=151266624, manual_tuples=0, backend_tuples=0, tupleField=0, conn=0
                 fetch_count=0, num_total_rows=0, num_fields=0, cursor='(NULL)'
                 message='(NULL)', command='(NULL)', notice='(NULL)'
                 status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=108, sqlstate=42601, errmsg='ERROR:  syntax error at end of input at character 396'

My settings are as follows: Disable generic optimizer on, KSQO, Parse statements on (I experimented with toggling these with no difference)

As you can see, the stmt_with_params is missing  three ')'-s from the end. Can anybody tell me what causes this behaviour and how can I get rid of it?

Regards
Eliott

> I am trying to create a front-end to an existing postgres database in MS
> Access 2002 using 8.01.20 Unicode ODBC driver. Some of the simpler queries

Please could you try 07.03.026X version? You can download it from
pgfoundry.org. This version is much powerfull and stable (as I can see).

Regards,

Luf

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Ludek Finstrle
> Sent: 21 April 2006 14:28
> To: Eliott
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Access -> ODBC -> postgres
>
> > I am trying to create a front-end to an existing postgres
> database in
> > MS Access 2002 using 8.01.20 Unicode ODBC driver. Some of
> the simpler
> > queries
>
> Please could you try 07.03.026X version? You can download it
> from pgfoundry.org. This version is much powerfull and stable
> (as I can see).

I should probably build a new snapshot based on CVS tip sometime in the
next week or so. Anyone object?

Regards, Dave

Fri, Apr 21, 2006 at 02:30:56PM +0100, Dave Page napsal(a):
> > > I am trying to create a front-end to an existing postgres
> > database in
> > > MS Access 2002 using 8.01.20 Unicode ODBC driver. Some of
> > the simpler
> > > queries
> >
> > Please could you try 07.03.026X version? You can download it
> > from pgfoundry.org. This version is much powerfull and stable
> > (as I can see).
>
> I should probably build a new snapshot based on CVS tip sometime in the
> next week or so. Anyone object?

I agree.

I vote for renumbering experimental branch to stable one (to 08.01.1001
or maybe 08.01.1000). 08.01.0XXX has no additional support (my opinion
is it's waste of time).

Regards,

Luf

Hi!

I did that, but no difference. However I checked the other drivers and with "PostgreSQL 7.03.02.00" the query was OK, but all the unicode chars were messed up, with "Postgres ANSI 8.01.02.00" the query was ok and the utf-8 chars were translated to my codepage, but the since I have chars from cyrillic codepages, it's still not quite there yet.

Hasn't anybody experienced something like this?

Regards
Eliott