Result set returns only one record

[Antz]

A very silly question and something that I have done which worked and now it seems that I am completely stumped when trying to do the same thing again.

On the database I have a stored procedure (FetchRoles) which looks like this:

[font=Courier New]PROCEDURE FetchRoles(
UserId CHAR(10))
RETURNS(
RoleCode CHAR(8),
RoleDesc VARCHAR(25))
AS
BEGIN
FOR
SELECT user_role, user_code
FROM user_roles, app_users
WHERE user_role.role_code = app_users.user_code
AND app_user.user_id = :UserId
AND app_user.user_status = ‘Active’
DO
SUSPEND;
END^;[/font]

When calling the procedure using ZStoredProcedure (ZEOSLIB 6.6.1) with the following code the ResultSet returned contains only one record. In the database itself there are three records. The Delphi code looks like this:

The DataModule contains all the components (in this case the TZConnection component and a TZStoredProcedure component).

[font=Courier New]
with datamod.spFetchRoles do
begin
Close;
Params[0].Value := editUserName.Text;
Open;
DbcResultSet.First
comboRoles.Items.Add(DbcResultSet.GetString(2));
while not DbcResultSet.Last do
begin
comboRoles.Items.Add(DbcResultSet.GetString(2));
DbcResultSet.Next;
end;
end;[/font]

The combo box has only one item within the list whereas the database has three entries that matches the UserName parameter. Is there something I am missing trying to get all the records? Again, any help would be greatly appreciated.

[cipto_kh]

Can you attcah the sample in Delphi and your database here? So I can reproduce that error?

Thx

[andy]

Use a normal Query (TZQuery).
This is a selectable Stored Procedure.

[cipto_kh]

@Andy: TZStoredProcedure also can have resultset

@Antz: I try this and it run fine (Zeos 6.6.1 beta, FB 2.0.1):
Firebird Stored Procedure (using Employee database):
CREATE PROCEDURE SPSELECTCOUNTRY(
PCOUNTRY VARCHAR(15))
RETURNS (
COUNTRY VARCHAR(15),
CURRENCY VARCHAR(50))
AS
begin
for select c1.country, c1.currency from country c1
where c1.country=:pcountry
union all
select c2.country, c2.country||' '||c2.currency from country c2
where c2.country=:pcountry
into :country, :currency
do
begin
suspend;
end
end

In Delphi:
memo2.Lines.Clear;
ZStoredProc1.Close;
ZStoredProc1.ParamByName('pcountry').AsString:='USA';
ZStoredProc1.Open;
ZStoredProc1.First;
while not ZStoredProc1.Eof do
begin
Memo2.Lines.Add(ZStoredProc1CURRENCY.AsString);
ZStoredProc1.Next;
end;

You can use the method Next, First, Eof from TZStoredProcedure directly, not need to acces DbcResultSet

And your code contain:
while not DbcResultSet.Last do
begin
................
Which always return 1 row, because you call the Last method (DbcResultSet.Last), it should be Eof method

[Antz]

cipto_kh,

Thanks a million. Althought I did not use the ZStoredProc you were right in the method that I used in addressing the resultset. The Last method takes me to the last entry in the returning resultset. I have kept all code the same but merely changed DbcResultSet.Last to DbcResultSet.IsLast which solves the problem. DbcResultSet does not have a Eof method, as Eof, so I guess that IsLast is the same.

Thanks again.