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

||

||||