Thread: ADO, set returning functions, ms acccess

Thanks for your time...
 
I've written a plpgsql function that produces a recordset (i think):
 
CREATE OR REPLACE FUNCTION "public"."function_name" (int integer) RETURNS SETOF "public"."tbl_nm" AS
$body$
/* New function body */
DECLARE
       rec public.tbl_nm;
    BEGIN
        FOR rec IN
              select * from tbl_nm where id <> int
        LOOP
            RETURN NEXT rec;
        END LOOP;
    END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 
When I execute the function from the sql editor, it produces the desired results. select * from function_name(1);
 
When I try to incorporate this function into my ado application, it doesn't seem to return a recordset. 
 
    Dim objCnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
   
        Set objCnn = data_layer.Open_ConnectPostGRESql(objCnn)
        Set cmd = New ADODB.Command
       
        cmd.ActiveConnection = objCnn
       
        cmd.CommandText = "Select  * from function_name(1);"
       
        Set rs = cmd.Execute
 
rs record count is -1.
 
Can you help?
 

---

Express yourself - download free Windows Live Messenger themes! Get it now!

brian stapel schrieb:
>
> When I try to incorporate this function into my ado application, it
> doesn't seem to return a recordset.
> [...]
>
> rs record count is -1.

I write without any testing or even looking at your stuff.

At least the 8.1.xx ODBC driver didn't support the recordcount of
returned recordsets.
It showed -1 regardless how many records were produced.

Try to read the recordset anyway and catch EOF to prevent a runtime-error.

Aside from recordcount not working, your function wouldn't work from
the sql editor either because you don't have a final return statement.

Also, does Execute return a recordset or do you need to call an Open
function?

Sim
________________________________________________________________________________

brian stapel schrieb:
>
> When I try to incorporate this function into my ado application, it
> doesn't seem to return a recordset.
> [...]
>
> rs record count is -1.

I write without any testing or even looking at your stuff.

At least the 8.1.xx ODBC driver didn't support the recordcount of
returned recordsets.
It showed -1 regardless how many records were produced.

Try to read the recordset anyway and catch EOF to prevent a runtime-error.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org