Re: Crystal Reports 8, psqlODBC driver and stored procedures

Richard,

Thanks for the reply.
What I am trying to say is, does SETOF integer get returned as a single recordset? or is each integer returned as a separate recordset?
If it is a single recordset, should proretset be TRUE?

Further reading on what Crystal Reports requires can be found here:
http://support.businessobjects.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf

This document is for Oracle and talks about using cursors to get the job done. I have tried this too but have had no luck. A DBfunction that
returns type refcursor does not exhibit the same behavior as I described previously (because proretset=FALSE) but it still does not get
added to the Crystal Report.

How compatible are PG Cursors with Oracle?

On Tue, 20 Jan 2004 19:17:13 +0000, Richard Huxton wrote:

> Message-Id: <200401201917(dot)13971(dot)dev(at)archonet(dot)com>
> From: Richard Huxton <dev(at)archonet(dot)com>
> Received: (cpmta 17493 invoked from network); 20 Jan 2004 11:17:19 -0800
> Received: from 194.217.242.86 (HELO anchor-post-37.mail.demon.net)
> by smtp.c000.snv.cp.net (209.228.33.183) with SMTP; 20 Jan 2004 11:17:19 -0800
> Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com)
> by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1)
> id 1Aj1NG-0009is-0b; Tue, 20 Jan 2004 19:17:18 +0000
> Received: by mainbox.archonet.com (Postfix, from userid 529)
> id E960018299; Tue, 20 Jan 2004 19:17:16 +0000 (GMT)
> Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
> by mainbox.archonet.com (Postfix) with ESMTP
> id 8FA1816B9C; Tue, 20 Jan 2004 19:17:14 +0000 (GMT)
> Date: Tue, 20 Jan 2004 19:17:13 +0000
> User-Agent: KMail/1.5
> Content-Disposition: inline
> Content-Type: text/plain;
> charset="iso-8859-1"
> X-Received: 20 Jan 2004 19:17:19 GMT
> Subject: Re: [ODBC] Crystal Reports 8, psqlODBC driver and stored procedures
> In-Reply-To: <20040120100047(dot)25182(dot)h012(dot)c000(dot)wm(at)mail(dot)childers(dot)com(dot)criticalpath(dot)net>
> References: <20040120100047(dot)25182(dot)h012(dot)c000(dot)wm(at)mail(dot)childers(dot)com(dot)criticalpath(dot)net>
> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3
> Delivered-To: childers(dot)com%anthony(at)childers(dot)com
> Return-Path: <dev(at)archonet(dot)com>
> MIME-Version: 1.0
> Content-Transfer-Encoding: 7bit
> To: anthony(at)childers(dot)com, pgsql-odbc(at)postgresql(dot)org
>
> 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