Forum Discussion

ajohnson2020's avatar
ajohnson2020
Contributor
13 years ago

Multi-operation SQL testing in TestComplete

Hello all,



I have been using TestComplete a long time to test our Delphi application with an MS SQL database.  I have quite a few SQL validation tests, but they're all in the nature of building an individual query, updating an individual piece of data, or validating data in a table after performing an operation in the main application.



I would like to use TestComplete to implement some more complex dedicated SQL tests, requiring a minimum of : (1) Execute one or more commands or procedures, (2) validate resulting data, and (3) rollback the entire test sequence so data is not affected for subsequent tests.



Any suggestions on the best way to accomplish this?  Can it all be done in one long ADO session somehow?



Thanks.

Allen
  • hlalumiere's avatar
    hlalumiere
    Regular Contributor
    13 years ago
    You could select each table you need to query into a temporary copy and query it without affecting your actual data:



    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SELECT * INTO #history FROM usrHistory;

    UPDATE #history SET Field1 = NULL WHERE Field1 = 0;

    DROP TABLE #history;



    If you need to test the result inside your application, then just make a backup of the database or a snapshot of the host VM before running your test, then restore it.
  • ajohnson2020's avatar
    ajohnson2020
    Contributor
    13 years ago
    Most of the testing involves procedure calls where I don't necessarily need to know what's going on inside of them (many tables and other procedures may be involved), only some resulting data changes.  So unfortunately sucking the data into local tables and then deleting them afterwards is not really feasible.

    I'm really looking for some way to leverage the SQL ability to perform multiple operations in a transaction, then use the rollback command to undo the entire transaction.  That way SQL handles all the work of tracking what needs to be undone. 



    Thanks.

    Allen



    PS I've been doing this with an open source tool called DBFit based on FitNesse, but after years of neglect by the developer it's getting very fragile and I'm looking for a replacement.
  • hlalumiere's avatar
    hlalumiere
    Regular Contributor
    13 years ago
    Like I said, if your database is not too big, just back it up before your test, run your test, then restore the backup. A full backup of a moderately sized database through SQL Server takes seconds/minutes to perform. 2GB takes about 5 minutes on network share.