Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA36178; Mon, 24 Jan 2000 10:39:20 -0500 (EST) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 4.1) with ESMTP id QAA23754; Mon, 24 Jan 2000 16:39:12 +0100 (MET) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 12CmHH-0003IC-00; Mon, 24 Jan 2000 16:23:43 +0000 Message-ID: <388C6F34.608E5E4A@sferacarta.com> Date: Mon, 24 Jan 2000 16:26:44 +0100 From: Jose Soares X-Mailer: Mozilla 4.6 [en] (WinNT; I) X-Accept-Language: en MIME-Version: 1.0 To: Byron Nikolaidis CC: interfaces , hackers Subject: Re: [INTERFACES] Re: ODBC drive strange behavior References: <38881F35.CAA49A56@sferacarta.com> <38881FC3.D34B1BEC@sferacarta.com> <3889DD8D.32ACABD8@home.com> Content-Type: multipart/alternative; boundary="------------420A31138A746F2AC6E116CF" --------------420A31138A746F2AC6E116CF Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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é --------------420A31138A746F2AC6E116CF Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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é
  --------------420A31138A746F2AC6E116CF--