Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Spot the problem - Stored Procedure |
Wed, Jul 28 2010 4:57 AM | Permanent Link |
Peter | Hello all
I would like to have a generic procedure that moves one record from one table, and places it into an archive table of identical structure, resets the boolean Archive field to NULL in the archive table, then deletes the original record in the first table. It should only delete the original if everything has happened smoothly, so the procedure must be housed within a commit\rollback block. I have not yet used stored procs, and I have very limited knowledge of scripts, despite reading everything I could find. The following procedure does nothing, even though each of the individual SQL statements work just fine. PROCEDURE "GenericArchive3" (IN "TargetID" INTEGER, IN "TblFrom" VARCHAR(30) COLLATE ANSI, IN "TblTo" VARCHAR(30) COLLATE ANSI, IN "FldName" VARCHAR(30) COLLATE ANSI) BEGIN START TRANSACTION ON TABLES TblFrom, TblTo; BEGIN EXECUTE IMMEDIATE 'DELETE FROM "' + TblTo + '" WHERE (' + FldName + ' = ?);' USING TargetID; */ EXECUTE IMMEDIATE 'INSERT INTO "' + TblTo + '" SELECT * FROM "' + TblFrom + '" WHERE (' + FldName + ' = ?);' USING TargetID; EXECUTE IMMEDIATE 'UPDATE "' + TblTo + '" SET Archive = NULL WHERE (' + FldName + ' = ?);' USING TargetID; EXECUTE IMMEDIATE 'DELETE FROM "' + TblFrom + '" WHERE (' + FldName + ' = ?);' USING TargetID; COMMIT; EXCEPTION ROLLBACK; END; END This returns a 'The execution of the procedure did not complete...' message, regardless of which line I comment out , so I am left to assume that there is something basically wrong with the procedure. I have definitely added all the correct parameters. Any suggestions would be most welcome. Regards & TIA Peter |
Wed, Jul 28 2010 5:38 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
There's a useful little thing SET STATUS MESSAGE TO. Pop one before each line and it should allow you to at least figure out which line its failing on. What I (and I'm like yourself a novice but learning) would be to build the statements and use those as the status message eg PROCEDURE "GenericArchive3" (IN "TargetID" INTEGER, IN "TblFrom" VARCHAR(30) COLLATE ANSI, IN "TblTo" VARCHAR(30) COLLATE ANSI, IN "FldName" VARCHAR(30) COLLATE ANSI) BEGIN DECLARE MSG VARCHAR; START TRANSACTION ON TABLES TblFrom, TblTo; BEGIN SET MSG = 'DELETE FROM '+ TblTo +' WHERE ' + FldName + ' = ' + CAST(TargetID AS VARCHAR); SET STATUS MESSAGE TO MSG; EXECUTE IMMEDIATE MSG; SET MSG = 'INSERT INTO ' +TblTo+ ' SELECT * FROM '+ TblFrom +' WHERE ' + FldName + ' = ' + CAST(TargetID AS VARCHAR); SET STATUS MESSAGE TO MSG; EXECUTE IMMEDIATE MSG; SET MSG = 'UPDATE '+ TblTo +' SET Archive = NULL WHERE ' + FldName + ' = ' + CAST(TargetID AS VARCHAR); SET STATUS MESSAGE TO MSG; EXECUTE IMMEDIATE MSG; SET MSG = 'DELETE FROM '+ TblFrom +' WHERE ' + FldName + ' = ' + CAST(TargetID AS VARCHAR); SET STATUS MESSAGE TO MSG; EXECUTE IMMEDIATE MSG; COMMIT; EXCEPTION ROLLBACK; END; END Having just typed that I'm guessing the problem is with quotes Roy Lambert [Team Elevate] |
Wed, Jul 28 2010 11:42 AM | Permanent Link |
David Cornelius Cornelius Concepts | Roy's suggestion is a good one: don't use USING but build the SQL
statements out completely. But the problem I found is that the START TRANSACTION statement chokes because it can't find the table "TblFrom" which is not substituted with the actual value from the parameter. I tried changing that statement to using EXECUTE IMMEDIATE, but that didn't initially work. I didn't pursue it further, but just removed the transaction block. Then it worked. -- David Cornelius Cornelius Concepts On 7/28/2010 1:57 AM, Peter wrote: > Hello all > > I would like to have a generic procedure that moves one record from one table, and places it into an archive table of identical structure, resets the boolean Archive field to NULL in the archive table, then deletes the original record in the first table. It should only delete the original if everything has happened smoothly, so the procedure must be housed within a commit\rollback block. > > I have not yet used stored procs, and I have very limited knowledge of scripts, despite reading everything I could find. The following procedure does nothing, even though each of the individual SQL statements work just fine. > > PROCEDURE "GenericArchive3" (IN "TargetID" INTEGER, IN "TblFrom" VARCHAR(30) COLLATE ANSI, IN "TblTo" VARCHAR(30) COLLATE ANSI, IN "FldName" VARCHAR(30) COLLATE ANSI) > BEGIN > START TRANSACTION ON TABLES TblFrom, TblTo; > BEGIN > EXECUTE IMMEDIATE 'DELETE FROM "' + TblTo + '" WHERE (' + FldName + ' = ?);' USING TargetID; */ > EXECUTE IMMEDIATE 'INSERT INTO "' + TblTo + '" SELECT * FROM "' + TblFrom + > '" WHERE (' + FldName + ' = ?);' USING TargetID; > EXECUTE IMMEDIATE 'UPDATE "' + TblTo + '" SET Archive = NULL WHERE (' + FldName + ' = ?);' USING TargetID; > EXECUTE IMMEDIATE 'DELETE FROM "' + TblFrom + '" WHERE (' + FldName + ' = ?);' USING TargetID; > > COMMIT; > EXCEPTION > ROLLBACK; > END; > END > > This returns a 'The execution of the procedure did not complete...' message, regardless of which line I comment out , so I am left to assume that there is something basically wrong with the procedure. > > I have definitely added all the correct parameters. > > Any suggestions would be most welcome. > > Regards& TIA > > Peter > |
Wed, Jul 28 2010 12:20 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | David
>But the problem I found is that the START TRANSACTION statement chokes >because it can't find the table "TblFrom" which is not substituted with >the actual value from the parameter. Went straight past me and would certainly explain why commenting out the other lines didn't help. Roy Lambert [Team Elevate] |
Wed, Jul 28 2010 4:31 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Peter,
<< I have not yet used stored procs, and I have very limited knowledge of scripts, despite reading everything I could find. The following procedure does nothing, even though each of the individual SQL statements work just fine. >> David is correct - the issue is with the START TRANSACTION. I will see if I can modify the statement to allow for variables/parameters instead of just straight-up names for 2.03 Build 18, which will allow you to do what you're trying to do. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Jul 28 2010 8:14 PM | Permanent Link |
Peter | Thank you Roy for that tip. I will use that wherever possible,
David - well spotted, I would never have found that problem. Tim. I look forward to build 18. All - is there an example of using an OUT boolean parameter to tell my app that the procedure was a success? Do I just initialise the OUT param as False, then put Result = True after the end of the commit\rollback block? Regards Peter |
Wed, Jul 28 2010 11:12 PM | Permanent Link |
David Cornelius Cornelius Concepts | You're welcome!
Use an "out" parameter with the SET statement: SET MyResult = True; You'd want this just after the COMMIT statement inside the block, not after the COMMIT/ROLLBACK block. Yes, initialize it to False first. -- David Cornelius Cornelius Concepts On 7/28/2010 5:14 PM, Peter wrote: > All - is there an example of using an OUT boolean parameter to tell my app that the procedure was a success? Do I just initialise the OUT param as False, then put Result = True after the end of the commit\rollback block? |
Thu, Jul 29 2010 2:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
While I agree with David here's another tip - OUT Result INTEGER SET Result = -1; /* on the way in */ SET Result = error number - in an exception handler This then not only allows you to test if the SP was successful, but also what (or whereabouts) the error was. Roy Lambert [Team Elevate] |
Thu, Jul 29 2010 5:10 AM | Permanent Link |
Peter | Roy
Very sneaky, I'll test it out later. Thanks again. Peter |
Mon, Aug 2 2010 1:14 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Peter,
<< Tim. I look forward to build 18. >> Actually, I'm sorry, but this didn't make it. There's a bit more to it, so it will be included in 2.04 along with the changes to FETCH column names, which have the same issue. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |