Thread: ADO adCmdStoredProc PlPgSql-SP Parameters
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
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
>>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
<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) >