PDA

View Full Version : How do I return a record set through a stored procedure

dwilliams
09-06-02, 14:49
We are looking to migrate a 3 tier app to PostgreSQL. One function we must get to work is the ability to run most of our business logic at the DB layer as called by a COM object.

We are layered like so

IIS (ASP)
COM
- ODBC
PGSQL

but for testing purposes we are like this

IIS (ASP)
ODBC
PGSQL

I have been unable to return a complete record set from the database to ASP through ODBC but have not been successful.

I have tried this:

CREATE FUNCTION getall(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT user_id FROM users;
RETURN $1;
END;
' LANGUAGE 'plpgsql';

followed by:

Begin;
getall('test')
fetch all in test;
commit;

It executes ok but I get 0 results.

When I switch from 'SELECT user_id FROM users' to ' SELECT * FROM users' it bombs completely.

Any help would be appreciated.

thanks

Dave
dwilliams
09-08-02, 21:36
If the answer is I have no idea that would be helpful as well.
eperich
09-09-02, 05:36
how much data amount goes through ODBC
I've tested width ODHC and postgresql and it's not very amusing to me that it crashes when the data amount gets bigger.

And I don't think this construction is not possible

why do you need this
if you need the resulkts of this query you can easily run it in ASP.
dwilliams
09-09-02, 09:29
It would be easy enough to run this in ASP and submit it through ODBC but we are migrating an existing business application that uses a dynamic database allocation.

Also, due to our 3 tier architecture we do not want to rewrite our business layer.

edit: Also I do not need alot of data I just need complete record sets. This probably would not exceed several hundred records and no more than 50 fields.
eperich
09-09-02, 10:19
As far as I know. this construction is not possible in Postgresql
maybe you try it with version 7.3b1
there is a lot of new in this sector
dwilliams
09-11-02, 15:47
Originally posted by eperich
As far as I know. this construction is not possible in Postgresql
maybe you try it with version 7.3b1
there is a lot of new in this sector

Where do I get the beta from? Do you have a url?
eperich
09-11-02, 15:57
strange question!!

but anyway here is the link:
7.3b1 (http://developer.postgresql.org/beta.php)
dwilliams
09-11-02, 15:57
Originally posted by eperich
strange question!!

but anyway here is the link:
7.3b1 (http://developer.postgresql.org/beta.php)

Thanks :D
dwilliams
09-13-02, 10:49
I have managed to return a record set through a stored procedure/function at the psql layer. However, it does not work when I try to call the function from pgadmin II through ODBC. All I get is the cursor name as text. :(

Any suggestions on where to turn for help on this since it seems to be a pgadmin or odbc problem?
eperich
09-13-02, 11:30
The project leader is Dave page
I think you should write him a mail

He can say whether it's a pgadmin or ODBC bug

pgadmin page (http://pgadmin.postgresql.org/pgadmin2.php?ContentID=3)

At point 4 there is his email adress.
If he doesn not know
nobody does