Re: Crystal Reports 8, psqlODBC driver and stored procedures

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

> Message-Id: <200401202007(dot)43967(dot)dev(at)archonet(dot)com>
> From: Richard Huxton <dev(at)archonet(dot)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(at)postgresql(dot)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(dot)13880(dot)h018(dot)c000(dot)wm(at)mail(dot)childers(dot)com(dot)criticalpath(dot)net>
> References: <20040120113803(dot)13880(dot)h018(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
>
> On Tuesday 20 January 2004 19:38, anthony(at)childers(dot)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 a single value is
returned the functions work just fine in the report too. The error I mentioned is returned when the Add button is clicked in this dialog.
When the Add button is clicked, Crystal tells the ODBC driver to execute the query I included in my original post. Since the 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 Crystal without 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