Re: Crystal Reports 8, psqlODBC driver and stored procedures

On Tuesday 20 January 2004 18:00, anthony@childers.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