Re: Examples of savepoints and transactions
- Date: Tue, 23 Jan 2018 23:19:29 +0100
- From: Lars Nielsen <lars@xxxxxxxx>
- Subject: Re: Examples of savepoints and transactions
Den 22-01-2018 kl. 22:01 skrev shawn l.green:
On 1/21/2018 3:37 PM, Lars Nielsen wrote:
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.
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.
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
The issue is that when I call the first commit then the savepoint is
Is this possible at all?