Web lists-archives.com

Re: Examples of savepoints and transactions




Den 22-01-2018 kl. 22:01 skrev shawn l.green:
Hello Lars,

On 1/21/2018 3:37 PM, Lars Nielsen wrote:
Hi,
I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome.

Best regards
Lars Nielsen


Can you mock up an example (a simple text walkthrough) of how you think a savepoint should work with what you are calling "test data" ?  I think that the term "test data" is too general to make much sense to most of us in the context you described.


Yours,
Hello Shawn,
Thanks for your interest. Here is an example of my idea.

I have a php site working through PDO connections. I insert some data through php like this :

|START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; |||

||Now I want to do automated tests that create "dummy" data that i want to remove after the test has finished:
like this :

|SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL OPERATIONS.... ROLLBACK TO autotest1; |||

||All done. I have tested the application and have cleaned up the dummy test-data.

The issue is that when I call the first commit then the savepoint is deleted.

Is this possible at all?

Regards Lars

||

||||