Re: ODBC drive strange behavior

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é

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é

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

Le ven, 21 jan 2000, vous avez crit :
> >%_Sorry I forgot to send the attachement :)
>
>
> Jose Soares wrote:

> > 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
>
In my case using M$_Access97 without problem.
a)Try to upgrade to PsqlODBC v6.40.0007

b)Try to tune your odbc DSN with the help of PsqlODBC FAQ

c)Try to upgrade MDAC (odbc32 ==> About) in control panel
Regards.
Emmanuel

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é

Compte utilisateur Sultan-advl wrote:

> Le ven, 21 jan 2000, vous avez écrit :
> > >%_Sorry I forgot to send the attachement :)
> >
> >
> > Jose Soares wrote:
>
> > > 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é
> >
> In my case using M$_Access97 without problem.
> a)Try to upgrade to PsqlODBC v6.40.0007

Is there a new release? I can't find it at:
http://www.insightdist.com/psqlodbc/
Is there another site for psqlODBC ?

>
>
> b)Try to tune your odbc DSN with the help of PsqlODBC FAQ
>
> c)Try to upgrade MDAC (odbc32 ==> About) in control panel
> Regards.
> Emmanuel

José

At 16:18 01/25/2000 +0100, Jose Soares wrote:
>
>
>Compte utilisateur Sultan-advl wrote:
>
>> Le ven, 21 jan 2000, vous avez écrit :
>> > >%_Sorry I forgot to send the attachement :)
>> >
>> >
>> > Jose Soares wrote:
>>
>> > > 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é
>> >
>> In my case using M$_Access97 without problem.
>> a)Try to upgrade to PsqlODBC v6.40.0007
>
>Is there a new release? I can't find it at:
>http://www.insightdist.com/psqlodbc/
>Is there another site for psqlODBC ?

An old message from Byron:

Hi,

I've posted a new odbc driver at the following locations:

ftp://ftp.postgresql.org/pub/odbc/

AND

http://members.home.net/byron.nikolaidis (my personal homepage). Note
I have disabled downloading the full install version because of
bandwidth limitations on my ISP, but the source and dll and everything
else is there.

I'm not sure if there is a link from the postgres web site to the driver
yet.

If there are any problems, with the driver or the downloading or html
stuff, please let me know.

Byron
version.txt date: 09/02/99 version: 06.40.0007
----------------------------------------------------------------------
This file describes changes and or fixes since the last update.

NEW FEATURES:

none

BUG_FIXES:

1. Fix all info functions to check whether the table is a view.

2. Fix for Access 2000 (unconfirmed)

3. Change most statement errors to General Error S1000 instead of 08S01
(communication link failure).

Jose Soares wrote:
> Is there a new release? I can't find it at:
> http://www.insightdist.com/psqlodbc/
> Is there another site for psqlODBC ?
>

Yes, I have version 6.40.0007 on my own website. I'm not sure if it is
available elsewhere. The address is
http://members.home.net/byron.nikolaidis

Unfortunately, it does not include some recent patches that other people
have applied. I know of at least one with large objects.

I could put these in I suppose. Anybody know where these patches are?
Perhaps the author?

Byron

Byron Nikolaidis wrote:
> Jose Soares wrote:
> > Is there a new release? I can't find it at:
> > http://www.insightdist.com/psqlodbc/
> > Is there another site for psqlODBC ?
>
> Yes, I have version 6.40.0007 on my own website. I'm not sure if it is
> available elsewhere. The address is
> http://members.home.net/byron.nikolaidis
>
> Unfortunately, it does not include some recent patches that other people
> have applied. I know of at least one with large objects.
>
> I could put these in I suppose. Anybody know where these patches are?
> Perhaps the author?

I posted an large object patch.
I will send this patch to you.

=====
Hiroki Kataoka