Page 1 of 1

How to capture Return values of a stored procedure

Posted: 14.06.2007, 09:14
by Antz
Another very silly question (for those being experts in Delphi and SQL) but for a novice a really difficult cookie to solve. Using Zeoslib 6.6.1 and Delphi7 and will set out the problem including source extracts. But my program is this:

How to have a TComboBox component populated by the values returned from a stored procedure (on the database).

Application User Table Data
user_name user_role user_status
RTLANT CLNADMIN Active
RTLANT STCADMIN Active
RTLNAO SUPADMIN Active
RTLTST FINADMINI nactive

Application Role Table Data
role_code role_desc
CLNADMIN Client Administrator
STCADMIN Stock Administrator
SUPADMIN Supplier Administrator
FINADMIN Financial Administrator
SYSADMIN System Administrator

Stored Procedure (on the database)
PROCEDURE FetchRoles (UserName CHAR(9))
RETURNS (RoleCode CHAR(9), RoleDesc VARCHAR(25))
AS
BEGIN
FOR SELECT role_code, role_desc FROM user_roles, user_table
WHERE user_name = :UserName
AND role_desc = user_role
INTO :RoleCode, :RoleDesc
DO
SUSPEND;
END

Executing this procedure using IBEasySQL with the input value of RTLANT returns the correct two rows from user_table. Now, executing this procedure using Zeoslib (TZStoredProc component) I don’t know how to populate a TComboBox (the items property) with the two values returned.

I have set the PARMS to include the UserName (as input) and RoleCode and RoleDesc (as output) on the component properties. The StoredProcedure is set to the stored procedure on the database. The application executes without errors but I don’t know how to capture the returning values of the stored procedure. Please take into account, more than one row is returned.

Any help would be greatly appreciated.

Posted: 21.06.2007, 14:22
by Antz
Okay, it took a while but I have answered the question asked myself. Don’t know if anybody else had the problem merely because no responses were received. I know one can work with a table and query but because all my stored procedures are on the server and not the application this is not an option. I use the ZStoredProcedure for all the actions on the tables in the database.

If anybody is interested in the solution I will be glad to share it.

:roll: