Skip site navigation (1) Skip section navigation (2)

Re: Crystal Reports 8, psqlODBC driver and stored procedures

From: Richard Huxton <dev(at)archonet(dot)com>
To: anthony(at)childers(dot)com, pgsql-odbc(at)postgresql(dot)org
Subject: Re: Crystal Reports 8, psqlODBC driver and stored procedures
Date: 2004-01-20 19:17:13
Message-ID: 200401201917.13971.dev@archonet.com (view raw or flat)
Thread:
Lists: pgsql-odbc
On Tuesday 20 January 2004 18:00, anthony(at)childers(dot)com wrote:
[query getting function details]
>
> Notice the result of "[ fetched 0 rows ]". This is because
> "get_visible_subtree" returns SETOF integer. Any DBfunction which returns
> SETOF anything causes proretset to be set to TRUE. So the function will not
> be returned by this query.

> The description for proretset says it is TRUE when "Function returns a set
> (i.e., multiple values of the specified data type".
>
> This is the question... Does using a return type of SETOF in a DBfunction
> ALWAYS result in multiple result sets?

Yes - it might return a set containing only one integer, but that is still a 
set.

> The documentation seems to indicate
> that the result is not returned from the DBfunction until the final RETURN
> statement in the function, just before it exits. If this is the case should
> proretset ALWAYS be set to TRUE for any function that returns SETOF? What
> about a function that returns a table? Is this a bug?

I'm not sure what you mean by a function that returns a table. If you mean a 
function that returns a set of complex type (e.g. a int4, b text, c date) 
then there's no difference between that and a set of integers - it's just a 
matter of complexity (after all, you can have a table with just one column, 
it just doesn't happen often).

I'm guessing the issue is that either Crystal or the ODBC driver doesn't know 
/ isn't equipped to deal with set returning functions, and expects them all 
to be like sin(),substr() etc.

Someone else has suggested an SQL mode in Crystal Reports, I'm afraid I don't 
know enough to comment. If that doesn't work, the only thing I can think of 
is to wrap the function in a view, but that stops you passing variables to it 
(which presumably you want to do).

-- 
  Richard Huxton
  Archonet Ltd

In response to

pgsql-odbc by date

Next:From: anthonyDate: 2004-01-20 19:38:03
Subject: Re: Crystal Reports 8, psqlODBC driver and stored procedures
Previous:From: Godshall MichaelDate: 2004-01-20 18:22:44
Subject: Re: Crystal Reports 8, psqlODBC driver and stored procedur

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group