Crystal Reports 8, psqlODBC driver and stored procedures
От | anthony@childers.com |
---|---|
Тема | Crystal Reports 8, psqlODBC driver and stored procedures |
Дата | |
Msg-id | 20040120100047.25182.h012.c000.wm@mail.childers.com.criticalpath.net обсуждение исходный текст |
Ответы |
Re: Crystal Reports 8, psqlODBC driver and stored procedures
|
Список | pgsql-odbc |
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?
В списке pgsql-odbc по дате отправления: