Thread

Re: [INTERFACES] Re: ODBC drive strange behavior

Jose Soares <jose@sferacarta.com> — 2000-01-24T15:26:44Z

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é