Обсуждение: Crystal Reports 8, psqlODBC driver and stored procedures
I am trying to use Crystal Reports with PostgreSQL 7.3.2 and psqlODBC 7.03.02.
Everything seems to work fine EXCEPT stored procedures (postgreSQL functions).
The problems occur when adding DBfunctions as tables in the Crystal Reports Data
Explorer. While DBfunctions that return a single value such as int or datetime
work fine, any DBfunction that returns SETOF cannot be added to Crystal Reports
as a "table".
Upon further investigation I found the reason for this. When the ADD button is
pressed in Crystal Reports for a DBfunction called "get_visible_subtree" the
following SQL query is executed by psqlODBC:
conn=28653920, query='select proname, proretset, prorettype, pronargs,
proargtypes, nspname from pg_catalog.pg_namespace, pg_catalog.pg_proc where
pg_proc.pronamespace = pg_namespace.oid and (not proretset) and nspname like
'public' and proname like 'get_visible_subtree' order by nspname, proname,
proretset'
[ fetched 0 rows ]
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.
This results in the following error returned by the psqlODBC driver:
ODBC error: ERROR: Function public.get_visible_subtree() does not exist
Unable to identify a function that satisfies the given argument
types
You may need to add explicit typecasts
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? 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?
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
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 separaterecordset? 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 stilldoes 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.13971.dev@archonet.com> > From: Richard Huxton <dev@archonet.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.25182.h012.c000.wm@mail.childers.com.criticalpath.net> > References: <20040120100047.25182.h012.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, pgsql-odbc@postgresql.org > > 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
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? > 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
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