Archived PushToTest site

Publish Test Results From OpenOffice Directly

Using OpenOffice To Query TestMaker Results

Michael Acosta-Madiedo, an engineer on the PushToTest TestMaker project, developed a technique to publish performance test results data using OpenOffice. His technique uses the TestMaker architecture where logged results data is available in a Results Repository - normally a copy of MySQL or Oracle.

This is the kind of work PushToTest Global Services does for customers everyday. Learn more by clicking here.

Special note: Click here to view a description of the TestMaker Results Repository schema.

The Problem

While TestMaker produces hundreds of results reports there are several reasons why business managers, testers, and IT Ops managers would query results outside of TestMaker. The query and reports are able to deliver custom reports and workaround limitations in the built-in reports. For example, one organization needed weighted average response times from test execution.

The Solution

OpenOffice provides access methods to work with data in a database. We will show how to get to TestMaker data in an OpenOffice spreadsheet. In this case we show how this works with MySQL.
  1. Create a Mysql data source
  2. Add the mysql-connector.jar to the OpenOffice classpath
  3. Open an OpenOffice Spreadsheet

    Windows operating environment:
    a) Start OpenOffice.org
    b) Use Tools menu - Options - OpenOffice.org General Java.
    c) Click on the button Class Path..., then choose Add Archive... and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located.
    d) Click on Open, and the file should then appear in the list of Assigned folders and Archives.
    e) You can then click on OK and close OpenOffice.org completely.

    Note: You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

    Mac OS X operating environment:
    a) Use Tools menu -> Options -> OpenOffice.org -> General -> Java is OpenOffice.org -> Preferences -> OpenOffice.org -> Java
    b) In either case, you can use the mysql-connector.jar in the TestMaker/TestNode/bundles/mysql/5.0 directory.
    c) If a connection file has already been created … Load it in the Registered

  4. DatabaseOpenOffice.org->Preferences->OpenOffice.org Base->Databases
  5. Click New
  6. Select the odf file (example svn/testmaker-ent/ETM/cloud/<client>/<client>_db.odb)
  7. Click OK
  8. If a connection file hasn’t already been created … Use the Database Wizard to connect to your repository File->New->Database
  9. Select “connect to to an existing database”, and “MySQL”, click Next
  10. Use “Connect using JDBC”, click Next
  11. Database name = “testmaker”, Server=server name where you have mysqld running, Port=3306, driver=com.mysql.jdbc.Driver
  12. Click “Test class”, click Next
  13. User name = testmaker, check Password required, click Test connection, enter password, click Next
  14. Select “Yes, register the database for me”, click Finish
  15. Save to a filename (example svn/testmaker-ent/ETM/cloud/<client>/<client>_db.odb)
  16. Add a query for the data that interests you
  17. Open the ‘Data Sources’ Window

    Windows: F4
    Mac: View->Data Sources

  18. Right-Click on Queries and select “Edit Database File”
  19. Click on Queries
  20. Now use one of the tasks to create the query The easiest way is to have a SQL call that you want to use and select “Creat Query in SQL View” This opens an empty design window for you to paste your .sql into
  21. Let’s start with one of the queries used by one of the TestMaker reports (see svn/testmaker/TM/tags/tm601/modules/charting/src/com/pushtotest/tm/charting/reports/jasper/StepDuration.jrxml … specifically the data in the <queryString> area. Also, click here for the repository schema.)

    SELECT
    stepdefinitions.NAME AS stepdefinitions_Name,
    steps.DURATION AS steps_Duration,
    CONTROLLERS.CONTROLLERNAME AS CONTROLLERS_CONTROLLERNAME
    FROM
    TRANSACTIONS transactions INNER JOIN STEPS steps ON transactions.TransactionID = steps.TransactionID
    INNER JOIN SEQUENCES sequences ON transactions.SequenceID = sequences.SequenceID
    INNER JOIN ITERATIONS iterations ON sequences.IterationID = iterations.IterationID
    INNER JOIN EXECUTIONS executions ON iterations.ExecutionID = executions.ExecutionID
    INNER JOIN TESTMAKER.CONTROLLERS CONTROLLERS ON iterations.CONTROLLERID = CONTROLLERS.CONTROLLERID
    INNER JOIN STEPDEFINITIONS stepdefinitions ON steps.StepDefinitionID = stepdefinitions.StepDefinitionID
    WHERE
    TRANSACTIONS.SUCCESS = 1 and executions.ExecutionID = :Enter_ExecutionID

  22. Save with some memorable name (e.g. StepDuration)
  23. Make sure to save your changes to the .odb file before exiting
  24. Pull results into a spreadsheet
  25. With the ‘Data Sources’ View visible, open the connection and Queries tree
  26. Drag the Query you want down into your spreadsheet
  27. If it takes parameters enter them now
  28. The data will take a few moments but will then populate into the spreadsheet view
If you need to narrow the data you can either modify the query (adding AND to the WHERE clause) or you can use spreadsheet magic (e.g. =IF(A2==”Geo_LoadTestbyAddress”;B2;””) I tend to do this to get statistical information (e.g. =AVERAGE(D2:D1776))

Need Help With This Solution? Ask PushToTest Global Services