Thread: 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
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@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.
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 aklaver@comcast.net
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: aklaver@comcast.net
> To: pgsql-general@postgresql.org; carl.sopchak@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
> aklaver@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. Find out more.
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: aklaver@comcast.net
> To: pgsql-general@postgresql.org; carl.sopchak@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
> aklaver@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. Find out more.
hai all, i'm trying create function like this CREATE OR REPLACE FUNCTION penjualan(idcb integer) RETURNS SETOF penjualan AS $BODY$ BEGIN IF ($1 IS NULL) THEN return query SELECT * FROM PENJUALAN; ELSE return query SELECT * FROM PENJUALAN WHERE IDCABANG=$1; END IF; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 1000; but this function is not work with this error : ERROR: structure of query does not match function result type CONTEXT: PL/pgSQL function "penjualan" line 6 at RETURN QUERY on the time i try with other table and working well thanks for your help
ataherster wrote: > hai all, i'm trying create function like this > > CREATE OR REPLACE FUNCTION penjualan(idcb integer) > RETURNS SETOF penjualan AS > > > but this function is not work with this error : ERROR: structure of > query does not match function result type > CONTEXT: PL/pgSQL function "penjualan" line 6 at RETURN QUERY > > on the time i try with other table and working well > > thanks for your help This is because Postgresql does know the structure of the data to be returned. So the choice either use OUT command like so http://www.postgresql.org/docs/current/static/plpgsql-declarations.html Create or Replace Function penjualan(idcb, integer, OUT f1 text, OUT f2 integer) Returns SETOF penjualan AS Or create a new data type describing the data structure http://www.postgresql.org/docs/8.3/static/sql-createtype.html Create Type myTable as ( f1 text, f2 integer) Create or Replace Function penjualan(idcb, integer) Returns SETOF myTable AS
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
On Saturday, March 14, 2009, Dann Corbit wrote: > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > > owner@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
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.
ataherster <ataherster@yahoo.co.id> writes:
> ... but this function is not work with this error :
> ERROR: structure of query does not match function result type
> CONTEXT: PL/pgSQL function "penjualan" line 6 at RETURN QUERY
This looks like a known limitation in plpgsql: it's not very good with
rowtypes that contain dropped columns. Have you dropped some columns
in table PENJUALAN? If so, try remaking the table from scratch.
regards, tom lane
thanks Tom Lane, my problem resolved, I'm trying to re-create my table and function, and this working well Tom Lane wrote: > ataherster <ataherster@yahoo.co.id> writes: > >> ... but this function is not work with this error : >> ERROR: structure of query does not match function result type >> CONTEXT: PL/pgSQL function "penjualan" line 6 at RETURN QUERY >> > > This looks like a known limitation in plpgsql: it's not very good with > rowtypes that contain dropped columns. Have you dropped some columns > in table PENJUALAN? If so, try remaking the table from scratch. > > regards, tom lane > >