With crystal 9 you can use the Database expert command option to enter in a sql statement from scratch using the return setof functionality.
select * from foo_table({?param1},{param2}) as re( column1_name text,
column2_name int4
);
I don't believe the add Command function was an option in Crystal 8.
If you have the advanced version of Crystal 8, maybe the professional, you might be able to enter the sql statement directly via the Show SQL Query if I remember correctly.
I don't know how postgresql works behind the scenes but hopefully the above might give you a workaround.
Mike
-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of
anthony@childers.com
Sent: Tuesday, January 20, 2004 12:01 PM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] 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?
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
