Re: ODBC limitation??

On Saturday, March 14, 2009, Dann Corbit wrote:
> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> > owner(at)postgresql(dot)org] On Behalf Of Carl Sopchak
> > Sent: Saturday, March 14, 2009 5:41 PM
> > To: pgsql-general(at)postgresql(dot)org
> > Subject: [GENERAL] ODBC limitation??
> >
> > When I run the following query through psql, it executes successfully.
> > However, when I run it through ODBC (via OpenOffice Base), I get the
> > error
> >
> > SQL Status: HY000
> > Error code: 1000
> >
> > syntax error, unexpected $end, expecting BETWEEN or IN or
> > SQL_TOKEN_LIKE
> >
> > Is this some limitation of ODBC? (I don't think so, so I'm going to
> > ask on
> > the OpenOffice lists, but thought I'd check here, too...)
> >
> > Query:
> >
> > select number_of_years,
> > max(case when trial_id = 1 then period_results else null end) as
> > MaxResults1,
> > min(case when trial_id = 1 then period_results else null end) as
> > MaxResults1,
> > max(case when trial_id = 2 then period_results else null end) as
> > MaxResults2,
> > min(case when trial_id = 2 then period_results else null end) as
> > MaxResults2
> > from trial_results
> > where trial_id in (1,2)
> > group by number_of_years
> > order by number_of_years;
> >
> >
> > This is on Linux, Fedora 8, using PostgreSQL 8.3.6 and unixODBC...
>
> I am not speaking with specific knowledge about the official PostgreSQL
> ODBC driver, but support for the above grammar is not demanded by the
> actual ODBC specification. Many ODBC drivers have a pass-through mode.
> You might check the documentation for the official driver and see if it
> has one. Any query that will work from PSQL will work in pass-through
> mode.

Thanks for the suggestion.

Frankly, I'm quite surprised that the ODBC driver specification talks at all
about SQL grammar, although I have absolutely no knowledge of the
specification. I would think that the commands traveling over ODBC would not
be interpreted by ODBC at all. Why would it need to?

Also, the above grammar is standard SQL, if I'm not mistaken. If the ODBC
spec talks about grammar, I would think that it would support such a
widely-used standard...

In any case, I've been searching for how to turn on pass-through to no avail.
Got any pointers?

Thanks for the help,

Carl