Re: [GENERAL] ODBC limitation??
On Saturday, March 14, 2009, Adrian Klaver wrote: > On Saturday 14 March 2009 5:40:40 pm Carl Sopchak wrote: > > 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... > > > > Thanks for the help, > > > > Carl > > This is an OO problem, at some point OO Base translates ODBC and JDBC > queries into its native SDBC format and it has some parser limitations. To > get this to run you will have to turn of the query builder and just run it > as a pass through query. Using Pass-Through did the trick. Thanks for the help! Carl P.S., In OpenOffice, on the SQL view window, there's a button with a box and "SQL" in it. Activating that turns on pass-through. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ODBC limitation??
On Saturday, March 14, 2009, Dann Corbit wrote: > > -Original Message- > > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > > ow...@postgresql.org] On Behalf Of Carl Sopchak > > Sent: Saturday, March 14, 2009 5:41 PM > > To: pgsql-general@postgresql.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ODBC limitation??
On Saturday, March 14, 2009, Adrian Klaver wrote: > On Saturday 14 March 2009 5:40:40 pm Carl Sopchak wrote: > > 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... > > > > Thanks for the help, > > > > Carl > > This is an OO problem, at some point OO Base translates ODBC and JDBC > queries into its native SDBC format and it has some parser limitations. To > get this to run you will have to turn of the query builder and just run it > as a pass through query. Thanks for the suggestion. I've searched for hours trying to find how to force pass-through with no luck. Got any ideas? Thanks for the help, Carl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ODBC limitation??
I would see if you could trim down the statement and keep the statement all on one line (if possible) if you cant trim it down try putting the logic in a function HTH Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > From: akla...@comcast.net > To: pgsql-general@postgresql.org; carl.sopc...@cegis123.com > Subject: Re: [GENERAL] ODBC limitation?? > Date: Sat, 14 Mar 2009 18:36:51 -0700 > > On Saturday 14 March 2009 5:40:40 pm Carl Sopchak wrote: > > 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... > > > > Thanks for the help, > > > > Carl > > This is an OO problem, at some point OO Base translates ODBC and JDBC queries > into its native SDBC format and it has some parser limitations. To get this > to > run you will have to turn of the query builder and just run it as a pass > through query. > > -- > Adrian Klaver > akla...@comcast.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Hotmail® is up to 70% faster. Now good news travels really fast. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_70faster_032009
Re: [GENERAL] ODBC limitation??
On Saturday 14 March 2009 5:40:40 pm Carl Sopchak wrote: > 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... > > Thanks for the help, > > Carl This is an OO problem, at some point OO Base translates ODBC and JDBC queries into its native SDBC format and it has some parser limitations. To get this to run you will have to turn of the query builder and just run it as a pass through query. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ODBC limitation??
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Carl Sopchak > Sent: Saturday, March 14, 2009 5:41 PM > To: pgsql-general@postgresql.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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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... Thanks for the help, Carl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
