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

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]

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
>

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]

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

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

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?

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]

Roy

Very sneaky, I'll test it out later. Thanks again.

Peter

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