pg.ddx.io  pgsql-interfaces@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jose Soares <jose@sferacarta.com>
To: Byron Nikolaidis <byron.nikolaidis@home.com>
Cc: interfaces <pgsql-interfaces@postgresql.org>
Cc: hackers <pgsql-hackers@postgresql.org>
Subject: Re: [INTERFACES] Re: ODBC drive strange behavior
Date: Mon, 24 Jan 2000 16:26:44 +0100
Message-ID: <388C6F34.608E5E4A@sferacarta.com> (raw)
References: <38881F35.CAA49A56@sferacarta.com>
	<38881FC3.D34B1BEC@sferacarta.com>
	<3889DD8D.32ACABD8@home.com>

Byron Nikolaidis wrote:

> Jose Soares wrote:
> >
> > Sorry I forgot to send the attachement  :)
> >
> > Jose Soares wrote:
> >
> > > Please help!
> > >
> > > The PSQLODBC aborts a transaction with a strange error while execute a
> > > legal query.
> > > The message is:
> > >
> > > "Could not begin a transaction; unexpected protocol character from
> > > backend (sen_query) (#1)"
> > >
> > > I tried the same query with psql client and it works with no problems.
> > >
> > > Does anyone know what this message means ?
> > >
> > > My configuration:
> > >
> > > Data base server: PostgreSQL v6.5.2
> > > OS server: Linux 2.0.37 (Debian)
> > > Win Client: M$_Access95
> > > PsqlODBC v6.40.0006
> > > log file attached.
> > >
> > > Any help would be very apreciated.
> > >
> > > José
> >
> >   ------------------------------------------------------------------------
> >                     Name: LOG_ERROR.log
> >    LOG_ERROR.log    Type: Text Document (application/x-unknown-content-type-txtfile)
> >                 Encoding: base64
>
> The error means the driver didn't receive the expected response
> character from the backend.  For queries, the expected response would be
> something like:
>
> 'T': results are coming (this one is the most likely expected)
> 'C': no tuples produced
> 'Z': ready for new query (in >= postgres 6.4 only).
> 'I': empty query produces this response
> 'N': notice
> 'E': error
>
> In your case, the query begins:  (SELECT "figure"."azienda"
>
> It might be the extra parenthesis around the query ?  Try removing
> them.  If that's not it, try making the query really short, just as an
> experiment.  Also, using the wrong protocol with the backend can make
> this happen.
>
> Byron

* About parenthesis around select this is the way M$-access translates
queries with UNIONs and I can't do nothing to change this behavior.
Thanks to developers, PostgreSQL now recognizes this syntax, in fact if I execute this
query on psql
it works.

* Just for an experiment I did the following change:

* The original query in M$-access was:
(
SELECT figure.azienda ,figure.codice_figura ,utenti.ragione_sociale ,utenti.istat
,utenti.cap,
    utenti.indirizzo ,utenti.civico ,figure.tipo ,utenti.codice_fiscale
,utenti.partita_iva ,
    figure.fine_attivita ,figure.data_esportazione ,figure.data_aggiornamento
,utenti.distretto,
    figure.gruppo ,figure.data_esportazione ,utenti.data_esportazione
FROM figure INNER JOIN utenti ON (figure.codice_figura = utenti.azienda)
WHERE (figure.tipo IN ('D' ,'DB' ,'DO' ,'DS' ) )
 )
UNION ALL
(
SELECT figure.azienda ,figure.codice_figura ,utenti.ragione_sociale ,utenti.istat ,
    utenti.cap ,utenti.indirizzo,utenti.civico,figure.tipo,utenti.codice_fiscale,
    utenti.partita_iva ,figure.fine_attivita ,figure.data_esportazione,
    figure.data_aggiornamento,utenti.distretto,figure.gruppo,figure.data_esportazione,
    utenti.data_esportazione
FROM figure INNER JOIN utenti ON (figure.codice_figura = utenti.azienda)
WHERE (figure.tipo IN ('P' ,'PB' ,'PO' ,'PS' ,'A' ) )
)

and it was translated to PostgreSQL as:

(SELECT "figure"."azienda" ,"figure"."codice_figura" ,"utenti"."ragione_sociale"
,"utenti"."istat" ,"utenti"."cap" ,"utenti"."indirizzo" ,"utenti"."civico"
,"figure"."tipo" ,"utenti"."codice_fiscale" ,"utenti"."partita_iva"
,"figure"."fine_attivita" ,"figure"."data_esportazione" ,"figure"."data_aggiornamento"
,"utenti"."distretto" ,"figure"."gruppo" ,"figure"."data_esportazione"
,"utenti"."data_esportazione"
FROM "figure","utenti"
WHERE (("figure"."tipo" IN ('D' ,'DB' ,'DO' ,'DS' ) )
AND ("figure"."codice_figura" = "utenti"."azienda" ) )
)
UNION ALL
(
SELECT "figure"."azienda" ,"figure"."codice_figura" ,"utenti"."ragione_sociale"
,"utenti"."istat" ,"utenti"."cap" ,"utenti"."indirizzo" ,"utenti"."civico"
,"figure"."tipo" ,"utenti"."codice_fiscale" ,"utenti"."partita_iva"
,"figure"."fine_attivita" ,"figure"."data_esportazione" ,"figure"."data_aggiornamento"
,"utenti"."distretto" ,"figure"."gruppo" ,"figure"."data_esportazione"
,"utenti"."data_esportazione"
FROM "figure","utenti"
WHERE (("figure"."tipo" IN ('P' ,'PB' ,'PO' ,'PS' ,'A' ) )
AND ("figure"."codice_figura" = "utenti"."azienda" ) )
)

I replaced the keyword INNER with LEFT and now it works but I can't realize why.

Any ideas Byron ?

Thanks,
José

view thread (9+ messages)  latest in thread

Message-ID: <388C6F34.608E5E4A@sferacarta.com>
Permalink:  ../388C6F34.608E5E4A@sferacarta.com/
Also on:    postgresql.org/message-id/388C6F34.608E5E4A@sferacarta.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-interfaces@postgresql.org
  Cc: jose@sferacarta.com, byron.nikolaidis@home.com, pgsql-hackers@postgresql.org
  Subject: Re: [INTERFACES] Re: ODBC drive strange behavior
  In-Reply-To: <388C6F34.608E5E4A@sferacarta.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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