Re: [INTERFACES] Re: ODBC drive strange behavior

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é