Re: Crystal Reports 8, psqlODBC driver and stored procedur

From: Godshall Michael <Michael_Godshall(at)gmachs(dot)com>
To: "'anthony(at)childers(dot)com'" <anthony(at)childers(dot)com>, pgsql-odbc(at)postgresql(dot)org
Subject: Re: Crystal Reports 8, psqlODBC driver and stored procedur
Date: 2004-01-20 18:22:44
Message-ID: A596FA3368757645AF862C701495CA0002A5E5A6@hor1mspmx01.gmachs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

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(at)postgresql(dot)org
[mailto:pgsql-odbc-owner(at)postgresql(dot)org]On Behalf Of
anthony(at)childers(dot)com
Sent: Tuesday, January 20, 2004 12:01 PM
To: pgsql-odbc(at)postgresql(dot)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(at)postgresql(dot)org

Browse pgsql-odbc by date

  From Date Subject
Next Message Richard Huxton 2004-01-20 19:17:13 Re: Crystal Reports 8, psqlODBC driver and stored procedures
Previous Message anthony 2004-01-20 18:02:27 Can't compile psqlODBC on Windows XP using Visual Studio .NET