stored procedures

[Elmug]

Hi everyone.

Postgres has stored procedures capability.

Using the Posgres specific vcl, it seems to me that capability may not be available.

I wonder if one were to use the ODBC driver, can stored procedures facilities be used?

Thanks.

[goodname]

Don't know what "Posgres specific vcl" is. If you want to run a stored procedure or function it should as simple as running a select query that calls the procedure/function.

Scroll down to the examples section.
http://www.postgresql.org/docs/9.1/static/sql-createfunction.html

[circular]

Does vcl = visual component library ? As far as I know a database is not visual.

[Elmug]

Does vcl = visual component library ? As far as I know a database is not visual.

I meant the vcl objects specific for connecting to Postgres databases.

I just want to know if using Lazarus with Postgres, one can implement and use stored procedures, and if there are (or not) limitations.

Thanks!

[Elmug]

Don't know what "Posgres specific vcl" is. If you want to run a stored procedure or function it should as simple as running a select query that calls the procedure/function.

Scroll down to the examples section.
http://www.postgresql.org/docs/9.1/static/sql-createfunction.html

Thanks for replying.

Correct me if not so. From your reply, as long as the query to call the procedure reaches the database server, it will perform it?

What about the capability to receive at the Lazarus application the returns of the stored procedure?

Do stored procedures with no output raise exceptions useable by the application code,  if failed?

Thanks!

[BigChimp]

You might try using select from as if it were a query if it returns results; use connection.executedirect (or similar) or sqlquery.execsql to just execute and not ask for result sets.
See http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery

Edit: note: given what Ludo says about PostgreSQL: apparently an SP in PostgreSQL always returns data... On Firebird, an SP can but does not have to return data.

[ludob]

Correct me if not so. From your reply, as long as the query to call the procedure reaches the database server, it will perform it?

Yes. No matter if the procedure is called with execsql, open or executedirect, as it does for every select statement.

What about the capability to receive at the Lazarus application the returns of the stored procedure?

Open will receive the result set as it does for every other select statement.

Do stored procedures with no output raise exceptions useable by the application code,  if failed?

Yes, exceptions on errors are raised as it does for every other select statement.

On postgres calling a stored procedure is just a select statement with your stored procedure called as if it where a function. So you get the same behavior as for every select statement. 

[Elmug]

Thangs, BigChimp, ludob, and goodname.

All you explained makes good sense to me.

I think I have a much better grasp on what to try to achieve now.