stored procedures
lazarus / 16-Databases

----------------------------------------------------------------------
Elmug · 2012-06-20 00:12

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 · 2012-06-20 02:11

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 · 2012-06-20 05:03

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

----------------------------------------------------------------------
Elmug · 2012-06-20 05:25

Quote from: circular on June 20, 2012, 07:03:03 amDoes 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 · 2012-06-20 05:31

Quote from: goodname on June 20, 2012, 04:11:20 amDon'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 · 2012-06-20 06:38

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 · 2012-06-20 07:06

QuoteCorrect 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.
QuoteWhat 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.
QuoteDo 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 · 2012-06-20 09:53

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.