Thread: ADO adCmdStoredProc PlPgSql-SP Parameters

ADO adCmdStoredProc PlPgSql-SP Parameters

From
Postgre.News.Firma@spamgourmet.net
Date:
Hi,

How do I call a StoredProcdure, written in PlPqSql
which returns a set of records (or tableWhatever)
and wants parameters,
from ADO while using "adCmdStoredProc" ?

ERROR:  set-valued function called in context that cannot accept a set


---------------------- VERY LONG VERSION OF THIS QUESTION
-------------------------


When I use Microsoft SQL-Server I make a SP like

CREATE PROCEDURE _test AS
select * from evt_event
return 42
GO

I call it just via name.






When I use PostgreSql I make a SQL-SP like

CREATE OR REPLACE FUNCTION "public"."_test" () RETURNS SETOF "public"."t1"
AS
$body$
select * from t1;
$body$
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

I call it just via name too.



When I use PostgreSql I make a PLPG-SQL-SP like
CREATE OR REPLACE FUNCTION "public"."_test" () RETURNS SETOF "public"."t1"
AS
$body$
declare
       rs record;
begin
     for rs in select * from t1 loop
         return next rs;
     end loop;
     RAISE NOTICE '42';
     return;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

How do I call this one ? (using adCmdStoredProc)


In the SQL-Editor I may call both pg-SPs via "select * from _test()"


EMS PostgreSql Manager SQL-Editor:
VariantePG1: select * from _test() : ok
VariantePG2: select * from _test() : ok

Ado:
RECORDSET rs = adoRecordSet.open <command>,...., adCmdStoredProc

VarianteMS : ok
VariantePG1: ok
VariantePG2: ERROR:  set-valued function called in context that cannot
accept a set


Help,
Andreas






Re: ADO adCmdStoredProc PlPgSql-SP Parameters

From
Shachar Shemesh
Date:
Postgre.News.Firma@spamgourmet.net wrote:

>Hi,
>
>How do I call a StoredProcdure, written in PlPqSql
>which returns a set of records (or tableWhatever)
>and wants parameters,
>from ADO while using "adCmdStoredProc" ?
>
>
>
Which driver are you using? If it's the OLE DB provider, then there is a
solution, provided that you modify your stored procedure a little. You
can probably write a wrapper procedure, if that's not acceptable as it is.

If your function returns "refcursor", then OLE DB will interpret that to
mean that the function has rowset returns, and will emulate the standard
calls surrounding returning multiple results.

Please feel free to let me know if this solves your problem. Please CC
me on replies.

          Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html


Re: ADO adCmdStoredProc PlPgSql-SP Parameters

From
Postgre.News.Firma@spamgourmet.net
Date:
>>Von: Duffner
> Von: Shachar Shemesh
> Betreff: Re: [GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters

> >How do I call a StoredProcdure, written in PlPqSql
> >which returns a set of records (or tableWhatever)
> >and wants parameters,
> >from ADO while using "adCmdStoredProc" ?

> Which driver are you using? If it's the OLE DB provider, then
> there is a solution,
> provided that you modify your stored procedure a little.
> You can probably write a wrapper procedure,
> if that's not acceptable as it is.

I had been told not to use the OLE driver because it is not working
correctly.
Now I will give it a try anyway.
There is nothing to change, because we are still investigating postgre to
know which way we will use it.
And if :-)

[One day later]
I tried it and we will not use the OLE driver for a LONG time.

> If your function returns "refcursor", then OLE DB will
> interpret that to  mean that the function has rowset returns, and will
emulate
> the standard calls surrounding returning multiple results.

When I tried to retrieve multiple cursors via ADO/ODBC I got a table
with the names of the cursors.
Is it possible to use these cursors somehow ?

Cu,
Andreas


Re: ADO adCmdStoredProc PlPgSql-SP Parameters

From
"Jeff Eckermann"
Date:
<Postgre.News.Firma@spamgourmet.net> wrote in message
news:000301c509f0$ae32c110$ca64a8c0@muenchen.oneXtwo.ag...
> Hi,
>
> How do I call a StoredProcdure, written in PlPqSql
> which returns a set of records (or tableWhatever)
> and wants parameters,
> from ADO while using "adCmdStoredProc" ?

I don't believe that works (anyone who thinks I'm wrong, please correct me).
You will need to use select syntax, e.g. "select * from
setreturningfunction(param1, param2,....);"  If you are using ODBC, you may
have a problem with that syntax, though I believe that the driver code has
been patched to deal with that, and a new release is on its way out (or you
could compile the source).

>
> ERROR:  set-valued function called in context that cannot accept a set
>
>
> ---------------------- VERY LONG VERSION OF THIS QUESTION
> -------------------------
>
>
> When I use Microsoft SQL-Server I make a SP like
>
> CREATE PROCEDURE _test AS
> select * from evt_event
> return 42
> GO
>
> I call it just via name.
>
>
>
>
>
>
> When I use PostgreSql I make a SQL-SP like
>
> CREATE OR REPLACE FUNCTION "public"."_test" () RETURNS SETOF "public"."t1"
> AS
> $body$
> select * from t1;
> $body$
> LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> I call it just via name too.
>
>
>
> When I use PostgreSql I make a PLPG-SQL-SP like
> CREATE OR REPLACE FUNCTION "public"."_test" () RETURNS SETOF "public"."t1"
> AS
> $body$
> declare
>       rs record;
> begin
>     for rs in select * from t1 loop
>         return next rs;
>     end loop;
>     RAISE NOTICE '42';
>     return;
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> How do I call this one ? (using adCmdStoredProc)
>
>
> In the SQL-Editor I may call both pg-SPs via "select * from _test()"
>
>
> EMS PostgreSql Manager SQL-Editor:
> VariantePG1: select * from _test() : ok
> VariantePG2: select * from _test() : ok
>
> Ado:
> RECORDSET rs = adoRecordSet.open <command>,...., adCmdStoredProc
>
> VarianteMS : ok
> VariantePG1: ok
> VariantePG2: ERROR:  set-valued function called in context that cannot
> accept a set
>
>
> Help,
> Andreas
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>