ODBC limitation??

-----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.
http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_70faster_032009

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: