Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2002
    Posts
    17

    Question Unanswered: How do I return a record set through a stored procedure

    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
    Last edited by dwilliams; 09-06-02 at 14:55.

  2. #2
    Join Date
    Sep 2002
    Posts
    17
    If the answer is I have no idea that would be helpful as well.

  3. #3
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    ODBC

    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.
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  4. #4
    Join Date
    Sep 2002
    Posts
    17
    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.

  5. #5
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    Cursors

    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
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  6. #6
    Join Date
    Sep 2002
    Posts
    17

    Re: Cursors

    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?

  7. #7
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    link for beta

    strange question!!

    but anyway here is the link:
    7.3b1
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  8. #8
    Join Date
    Sep 2002
    Posts
    17

    Re: link for beta

    Originally posted by eperich
    strange question!!

    but anyway here is the link:
    7.3b1
    Thanks

  9. #9
    Join Date
    Sep 2002
    Posts
    17
    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?

  10. #10
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    ODBC- Pgadmin

    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

    At point 4 there is his email adress.
    If he doesn not know
    nobody does
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •