I have just created my very first stored procedure. Granted, it is not specific to
Delphi (although that happens to be what my application is wrtiten in that will use
this stored procedure), but I can't seem to find the right forum and I hope it is just
a general syntax problem I am having they may be common to all stored procedure
platforms.
I am running in Firebird 1.0.3 using IB Manager to create my stored procedure.
I get a clean compile and then I run the procedure in debug mode. I supply all the
required input parameters and start stepping through the code.
The first pass through, everything seems to work alright, but when I get into the
second iteration of a FOR SELECT ... DO BEGIN ... END, I get the following error
message in IBManager:
Can't fetch next record because fmDebugSP dataset closed.
The following below is my stored procedure:
-----------------------------------------------------
CREATE PROCEDURE "AddFundsPrePayAccts" (
ISITEID INTEGER,
SLOWCARDNUM VARCHAR (30),
SHICARDNUM VARCHAR (30),
ICREDITS INTEGER)
AS
DECLARE VARIABLE iAccountIDVar integer;
DECLARE VARIABLE iUserIDVar integer;
DECLARE VARIABLE iSiteIDVar integer;
DECLARE VARIABLE sCardNumVar varchar(30);
BEGIN
/*--------------------------------------------*/
/* loop to get UserID associated with */
/* each record matching a range of CardNumber */
/*--------------------------------------------*/
FOR
SELECT "AccountID", "UserID", "CardNumber"
FROM "UserAccounts", "UserCardInfo"
WHERE "CardNumber"
BETWEEN :sLowCardNum AND :sHiCardNum
AND ("UserID" = "OwnerUserID")
INTO :iAccountIDVar, :iUserIDVar, :sCardNumVar
/*------------------------------------*/
/* now get last session's SiteID for */
/* UserID from record in above select */
/*------------------------------------*/
DO
BEGIN
FOR
SELECT "SiteID" FROM "Sessions"
WHERE "EndDate" =
(SELECT MAX("EndDate") FROM "Sessions"
WHERE "UserID" = :iUserIDVAR
)
INTO :iSiteIDVar
/*---------------------------------*/
/* now check to see if last use is */
/* at the prescribed site */
/*---------------------------------*/
DO
BEGIN
IF (:iSiteIDVar = :iSiteID) THEN
BEGIN
UPDATE "UserAccounts"
SET "Credits" = "Credits" + :iCredits
WHERE "AccountID" = :iAccountIDVar;
END
END
END
SUSPEND;
END
-----------------------------------------------------
I am far from a seasoned expert with stored procedures, so I have no idea what this
error message means, or why it is happening.
This problem is consistently reproduceable.
Can anyone help me with this.
Thanks,
Eric Tishler
_______________________________________________
Delphi mailing list
[EMAIL PROTECTED]
http://ns3.123.co.nz/mailman/listinfo/delphi
