Re: [GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters
[EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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 [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters
[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
