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?
