Hi,

I am having some probs with TIB_StoredProc.

I have written a search execute procedure which will MAY return 0..*
records. Because of the multiple records it may return, I have set the
StoredProcForSelect = true; prior to ExecProc() However, I still get the
following error:

Code ERROR:335544652

ISC ERROR MESSAGE
multiple rows in singleton select
'.Process stopped...


The function looks like:


void __fastcall TdmCVPasswordDB::SearchForRecords(const TLolgPwdFields
&AStruct)
{
ConnectDB();
HideColumns();
UnPrepareSql();

// Set the execute procedure to be used.
stpCVDBStoreProcedure->StoredProcName = FSearchProcedure;

// Populate the parameters.
stpCVDBStoreProcedure->ParamByName("R_CREATED_BY_IN")->AsString =
AStruct.lpfCurrentUser;
stpCVDBStoreProcedure->ParamByName("R_SHARED_IN")->AsInteger =
AStruct.lpfShared;

PrepareSql();

// Possibly returning multiple records.
stpCVDBStoreProcedure->StoredProcForSelect = true;
stpCVDBStoreProcedure->ExecProc(); // Exception takes place here
}

This is what the procedure looks like:

SET TERM ^^ ;
CREATE PROCEDURE P_LOGPAS_SEARCH (
R_CREATED_BY_IN VarChar(255),
R_SHARED_IN SmallInt)
returns (
R_ID Integer,
R_CREATED_BY VarChar(255),
R_LOGIN VarChar(255),
R_PASSWORD VarChar(255),
R_URL VarChar(255),
R_CREATED Date,
R_MODIFIED Date,
R_SHARED SmallInt,
R_PRIMARY VarChar(32),
R_SECONDARY VarChar(32),
R_COMMENTS VarChar(1024),
R_FILE_NAME VarChar(255))
AS
begin
if( :R_SHARED_IN = 0 )then
begin
SELECT R_ID,
R_CREATED_BY,
R_LOGIN,
R_PASSWORD,
R_URL,
R_CREATED,
R_MODIFIED,
R_SHARED,
R_PRIMARY,
R_SECONDARY,
R_COMMENTS,
R_FILE_NAME
FROM LOGPAS
WHERE ((R_CREATED_BY = :R_CREATED_BY_IN) AND ( R_SHARED =
:R_SHARED_IN))
INTO
:R_ID,
:R_CREATED_BY,
:R_LOGIN,
:R_PASSWORD,
:R_URL,
:R_CREATED,
:R_MODIFIED,
:R_SHARED,
:R_PRIMARY,
:R_SECONDARY,
:R_COMMENTS,
:R_FILE_NAME;
end
else
if( :R_SHARED_IN = 1 )then
begin
SELECT
R_ID,
R_CREATED_BY,
R_LOGIN,
R_PASSWORD,
R_URL,
R_CREATED,
R_MODIFIED,
R_SHARED,
R_PRIMARY,
R_SECONDARY,
R_COMMENTS,
R_FILE_NAME
FROM LOGPAS
WHERE ((R_CREATED_BY = :R_CREATED_BY_IN) OR ( R_SHARED =
:R_SHARED_IN))
INTO
:R_ID,
:R_CREATED_BY,
:R_LOGIN,
:R_PASSWORD,
:R_URL,
:R_CREATED,
:R_MODIFIED,
:R_SHARED,
:R_PRIMARY,
:R_SECONDARY,
:R_COMMENTS,
:R_FILE_NAME;
end
end
^^
SET TERM ; ^^

Hope that you may spot the problem

danieL
____________________________
Comvision Pty. Ltd.

www.comvision.net.au