TestComplete, SQL and Microsoft Access

As your test projects grow, you might migrate test data declared in script variables to external text files and on to Excel. Similarly, you may yearn to break beyond the bounds of the Test Log for sharing results.

After a while, your test data spreadsheets may grow out of control and you will get annoyed at other people opening the spreadsheet and leaving it unable to be written to.

This is where the power of databases comes in. Access is absolute garbage but will do for a quick (by which I mean "slow") prototype. If you want to run a distributed test system with a central data repository and providing real-time results available to all, you need to look to SQL Server.


//c#script
function OpenSQL 
{
    var ConnStr = "DRIVER=SQL Server; SERVER=SQLTestServer; "
        + "UID=SQLTestUser; PWD=SQLTestPwd; "
        + "DATABASE=SQLTestDB";
    var Connection = Sys.OleObject("ADODB.Connection","");
    Connection.Open(ConnStr);
    Connection.CommandTimeout = 60;
    return Connection;
}


'VBscript
Function OpenSQL
  ConnStr = "DRIVER=SQL Server; SERVER=SQLTestServer; "
        + "UID=SQLTestUser; PWD=SQLTestPwd; "
        + "DATABASE=SQLTestDB";
  set Connection = Sys.OleObject("ADODB.Connection","")
  Connection.Open(ConnStr)
  Connection.CommandTimeout = 60
  set OpenSQL = Connection
End Function


//CurrentFailures returns a csv list of TestCode
//(unique id for a test) values for the most recent
//test run on the "HostName" machine
function CurrentFailures(HostName)
{
    var RegressionTests = OpenSQL();
    var CurrentFailures = "";
    var SQLcmd = "SELECT TestCode FROM TestResults "
        + "WHERE RunID = (SELECT MAX(RunID) FROM "
        + "TestRun WHERE TestPC = '" & HostName & "') "
        + "AND (OutputMatch = 'FAIL') ORDER BY TestCode";
    var LastFailures = Sys.OleObject("ADODB.Recordset");
    LastFailures.open(SQLcmd,RegressionTests,1,1);
    if (lastFailures.RecordCount > 0)
    {
        while (!LastFailures.EOF)
        {
            CurrentFailures +=
                LastFailures("TestCode") + ", " ;
            LastFailures.MoveNext();
        }
    }
    LastFailures.Close();
    if (CurrentFailures <> "")
    {
        //slice off the last comma and space
        //- clumsy but works for now
        CurrentFailures =
            Left(CurrentFailures, Len(CurrentFailures) - 2);
    }
    RegressionTests.Close();
    RegressionTests = null;
}

//CreateNewTestRun creates a new test run record based on
//the test environment and test scope and
//returns the auto number assigned as its index
function CreateNewTestRun(runHost, runStarted, TestBuild, runScope)
{
    var RegressionTests = OpenSQL();
    var SQLcmd = "INSERT INTO TestRun (RunScope, RunStarted, "
        + "TestPC, TestBuild) VALUES ('" + runScope + "', '"
        + runStarted + "', '" + runHost+ "', '" + TestBuild + "')";
    RegressionTests.Execute(SQLcmd);
    SQLcmd = "SELECT MAX(RunID) FROM TestRun";
    var LastEntry = Sys.OleObject("ADODB.Recordset");
    LastEntry.open(SQLcmd,RegressionTests,1,1); 
    var LatestTestRunID =
        LastEntry.Fields.Item("RunID").Value.toString(); 
    RegressionTests.Close();
    RegressionTests = null;
    return LatestTestRunID;
}

//SetRunIDonTestPC assigns a run ID to a test PC
//so you know which run it's busy with (or if it's free)
function SetRunIDonTestPC(TestRunID, HostName)
{
    var RegressionTests = OpenSQL();
    var SQLcmd = "UPDATE TESTBED SET Running = " 
        + (TestRunID).to & " WHERE testPC = '" & HostName & "'"
    RegressionTests.Execute(SQLcmd);
    RegressionTests.Close();
    RegressionTests = null;
}

Useful things do do with TestComplete and SQL:

Connect to the database
Read a record from a table
Write a record to a table
Update a record in a table
Pull the data into a website

OpenSQL

By using OLE to make an ADODB connection to a SQL database , you can get an object that gives you programmatic control (using standard SQL commands) over the database from within your scripts.

Because you have the ability to create linked tables, you are no longer constrained by Excel's essentially 2D (3D if you use multiple sheets) nature.

CurrentFailures

For instance, you could have all your test machines detailed in TestBed (indexed on TestPC) and these each perform a TestRun (indexed on RunID) which in turn comprises a list of tests described in TestCases (indexed on TestCode), writing the outcome to TestResults using all these indices. You could go wild and have further tables of sub-groups of test conditions to pull into your test cases at run time. Trying to put this into a spreadsheet will drive you bonkers.

CreateNewTestRun

When you write to a table, you can target variable values to specific fields. Most of the errors you get with INSERTs happen because you forget the type of the field - for instance, remember to enclose text values in quotes even when you are passing in what you know is a string.

SetRunIDonTestPC

Similarly for UPDATEs, make sure values passed into integer type fields are specifically cast as integer.

SQL test data and results in a website

OK, strictly not much to do with TestComplete per se, but once the tests have run, JAWS is a lightweight technique for pulling SQL data into a website.

Sample Tables

These examples are based on this skeleton outline of some sample tables and relationships:
Example SQL Tables

TestingMisconfused