How do I find out what the exception is? Although I plan to invoke this stored procedure from my Delphi code, I am just testing it as a stand-alone inside of IB Manager for now. I have looked around, but cannot find adequate documentation on how to debug stored procedures (other than what I have figured out), or how to resolve problems such as this when I encounter them.
I am hoping that someone more knowlegeable in this area than I in this forum sees this and may be able to provide some help. Thanks in advance, Eric -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Rohit Gupta Sent: Friday, June 25, 2004 7:20 AM To: NZ Borland Developers Group - Delphi List Subject: Re: [DUG] Newbie problem with Stored Procedure As it says, your dataset is closed... maybe because it received an exception Eric Tishler wrote: >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 > > > _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi
