Result set return to Coldfusion MX?

Avi Schwartz <avi@CFFtechnologies.com> writes:

Hi,

We are currently running in a mostly Linux environment except for one
(big) thing, the database server. We are currently using Coldfusion
MX running on Linux and MS SQL Server but we are trying to get off SQL
Server into a Linux DB. The thing is that we are using stored
procedures very heavily and these stored procedures return a result
set in many cases. I tried to use a pl/pgsql function to return a
result set but it seems that Coldfusion MX does not know what to do
with the refcursor that PostgreSQL is providing. Is there another way
to return a result set to CF that it can handle? This is definitely a
show stopper for us.

Have you tried using 7.3's set returning functions? See the docs...

-Doug

No I didn't, I missed this part in the docs. Thanks for pointing me to
the sets, I will give it a try Monday when I am back in the office. I
do wonder though if sets are more expensive then cursors since the docs
tell me that the select runs to completion before returning while
cursors do not. This means that if the result set is large,
performance may suffer, for example in paging situation (web page that
displays only few rows until the user clicks on the Next button).
Another problem I see with sets is that if the function returns only a
subset of the table's columns, I will have to declare a special type to
match. Did I understand this correctly?

Thanks,
Avi

On Friday, May 16, 2003, at 18:30 America/Chicago, Doug McNaught wrote:

Avi Schwartz <avi@CFFtechnologies.com> writes:

Hi,

We are currently running in a mostly Linux environment except for one
(big) thing, the database server. We are currently using Coldfusion
MX running on Linux and MS SQL Server but we are trying to get off SQL
Server into a Linux DB. The thing is that we are using stored
procedures very heavily and these stored procedures return a result
set in many cases. I tried to use a pl/pgsql function to return a
result set but it seems that Coldfusion MX does not know what to do
with the refcursor that PostgreSQL is providing. Is there another way
to return a result set to CF that it can handle? This is definitely a
show stopper for us.

Have you tried using 7.3's set returning functions? See the docs...

-Doug

--
Avi Schwartz
avi@CFFtechnologies.com