Re: Informix Stored Procedures
In article <33B2188A.5DEF@home.com> John Graffio <jgraffio@home.com> wrote:
>
> To all,
>
> Has anybody had any luck getting results back from Informix stored
> procedures? How is it coded? (in Delphi, I guess)
>
The simplest example: getting logged in user name.
Informix Server Site (Programming Language = SPL):
create procedure sp_get_user_name() returning char(8);return USER;
end procedure;
Client Site (Programming Language = Delphi C/S):
Query1: TQuery;
UserName: String[8];
with Query1 do begin
SQL.Clear;
SQL.Add('execute procedure sp_get_user_name()');
Open;
UserName := Fields[0].AsString;
Close;
end;
Notes:
1. If SP returns no data you should call ExecSQL method instead of Open/Close.
2. If SP returns multiple set of data it behaves like a regular SELECT:
...
Open;
While not EOF do begin
... // Fetch data here
Next;
end;
Close;
...
Hints:
1. Always use DBA-granted SPs for Database Modifications in your applications
(but never plain INSERT/UPDATE)
2. Grant SELECT privilege on all the tables only.
It may seem not a good idea at first sight because most of 'SUPER'
methods (INSERT/APPEND/POST, etc) will not work since they require
Read/Write Mode rather than ReadOnly one; however, it's only way
to protect you database from DBExplorer-weaponed users and keep its integrity.
Take care,
Pavel <pavel@cso.spb.su>