Re: Crystal Reports 8, psqlODBC driver and stored procedures

On Tue, 20 Jan 2004 20:07:43 +0000, Richard Huxton wrote:

> Message-Id: <200401202007.43967.dev@archonet.com>
> From: Richard Huxton <dev@archonet.com>
> Received: (cpmta 3113 invoked from network); 20 Jan 2004 12:07:50 -0800
> Received: from 194.217.242.86 (HELO anchor-post-37.mail.demon.net)
>     by smtp.c000.snv.cp.net (209.228.32.61) with SMTP; 20 Jan 2004 12:07:50 -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 1Aj2A9-000Hjj-0b; Tue, 20 Jan 2004 20:07:49 +0000
> Received: by mainbox.archonet.com (Postfix, from userid 529)
>     id A546716357; Tue, 20 Jan 2004 20:07:43 +0000 (GMT)
> Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
>     by mainbox.archonet.com (Postfix) with ESMTP
>     id 52A0816314; Tue, 20 Jan 2004 20:07:41 +0000 (GMT)
> Date: Tue, 20 Jan 2004 20:07:43 +0000
> Cc: pgsql-odbc@postgresql.org
> User-Agent: KMail/1.5
> Content-Disposition: inline
> Content-Type: text/plain;
>     charset="iso-8859-1"
> X-Received: 20 Jan 2004 20:07:50 GMT
> Subject: Re: [ODBC] Crystal Reports 8, psqlODBC driver and stored procedures
> In-Reply-To: <20040120113803.13880.h018.c000.wm@mail.childers.com.criticalpath.net>
> References: <20040120113803.13880.h018.c000.wm@mail.childers.com.criticalpath.net>
> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3
> Delivered-To: childers.com%anthony@childers.com
> Return-Path: <dev@archonet.com>
> MIME-Version: 1.0
> Content-Transfer-Encoding: 7bit
> To: anthony@childers.com
>
> On Tuesday 20 January 2004 19:38, anthony@childers.com wrote:
> > 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?
>
> SETOF integer returns a recordset containing zero or more integers. Since this
> is a SET proretset is True (the PROcedure RETurns a SET).
> If you call it as SELECT * FROM function_name() then proretset should be true
> (if I understand things correctly).
>
> > Further reading on what Crystal Reports requires can be found here:
> > http://support.businessobjects.com/communityCS/TechnicalPapers/scr_oracle_s
> >tored_procedures.pdf
>
> Hmm - looks fiddly with Oracle. That bit on page 11 where it mentions the
> Database Expert dlg-box and the Stored Procedures node - PG's set returning
> functions aren't occuring there - have I got that right?
Correct, that is where "stored procedures" are added to the Crystal Report. PG's functions show up there just fine. If
asingle value is 
returned the functions work just fine in the report too. The error I mentioned is returned when the Add button is
clickedin this dialog. 
When the Add button is clicked, Crystal tells the ODBC driver to execute the query I included in my original post.
Sincethe query executed 
by the ODBC driver excludes anything that has proretset=TRUE, nothing is returned and I get the error.

I suspect if the query was modified to not exclude functions where proretset=TRUE that the function would be added to
Crystalwithout any 
problems.

>
> > 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?
>
> What matters, I suspect is how compatible an ODBC cursor is between Oracle and
> PG. I don't know, but if Crystal just reads sequentially, I would be
> surprised if they looked *very* different from an ODBC app.
>
> --
>   Richard Huxton
>   Archonet Ltd