Cheatsheet of AnyDbTest-Part2

Content of cheat sheet

	Part 1
	AnyDbTest GuiRunner command line
	Perform performance test
	Setup and Teardown
	Ignore one test ad-hoc in test file
	How to run one uncompleted unit testing case
	Test query SQL statement
	Test stored function
	Test DML statement
	Compare two record sets with different field count
	Compare two record sets with different field name
	Show set-style and sequential-style comparison for unit test
	Part 2
	Sandbox test (aka. AutoRollBack)
	Use shared DB Connection file
	Export data set into XML
	Convert XML-fomat data file into Excel format
	How to use Excel as reference test data
	Cross database test
	Cross database test-Compare target result set with reference DB
	Cross database test-Pump data from reference DB
	Validate replication, ETL, DTS and SSIS tasks

 

Sandbox test (aka. AutoRollBack)

If one test is in sandbox, all actions on each DB will be auto-rollback after the test. Otherwise, all actions will commit. Sandbox modal is only applicable for those DBs transaction supported such as Oracle and SQL server and so on, other than Excel.

You need only add an extra testInSandbox attribute in test case section. And set the testInSandbox attribute as true. This feature is applicable to both performance test and unit test. By default, the attribute value is false.

<unitTestCase testCaseName="UT_Test_UpdateCountryRegion" 
              assertion="RecordCountEqual" testInSandbox="true">
  <setup>
    <statement statementAlias="proc_UpdateCountryRegion">
      <inputArguments>
        <argument name="@CountryRegionCode" value="ABC"/>
        <argument name="@Name" value="new country"/>
      </inputArguments>
    </statement>
  </setup>
  <targetResultset statementAlias="sql_GetCountryRegion" 
                   outputArgument="@Return_Table">
    <inputArguments>
      <argument name="@CountryCode" value="ABC"/>
    </inputArguments>
  </targetResultset>
  <referenceResultset statementAlias="sql_get_const_number" 
                      outputArgument="@result">
    <inputArguments>
      <argument name="@constant" value="1"/>
    </inputArguments>
  </referenceResultset>
</unitTestCase>


 

<<Back to table of content   

 

Use shared DB Connection file

We often need to create many test case files to test our database. It will be a bore thing if we declare the same DB connection string in every case file.

If we want to share some case files that they need Excel spreadsheet as data source to other persons, the recipients will have to change Excel connection string in every case file possibly, because the connection string for Excel spreadsheet contains Excel file full path.

AnyDbTest will provide us with an approach to handle this kind of problem. We can define an XML configuration file as dedicated DB connection file. All of your test case files can import the DB configuration file. If you want to change DB connection, you need only change this DB connection file other than all case files.
Let¡¯s take an example. The following dbCfg.XML is just a DB connection file.

<?xml version="1.0" encoding="utf-8"?>
<dbConnections xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:noNamespaceSchemaLocation="file:/C:/DbConnectionSchema2.xsd">
  
  <dbConnection connectionName="Excel_pump"
   connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
   Data Source=c:\Pump_Source.xls;
   Extended Properties='Excel 8.0;HDR=YES'"
   providerName="Microsoft.Jet.OLEDB.4.0" />

  <dbConnection connectionName="Excel_refer"
   connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
   Data Source=c:\External_Refer.xls;
   Extended Properties='Excel 8.0;HDR=YES'"
   providerName="Microsoft.Jet.OLEDB.4.0" />
</dbConnections>


 

The following XML is one part of a case file. In its globalSetting we use dbConnectionFile attribute to reference one DB connection configuration file. We can also define normal DB connections in this section.

<globalSetting dbConnectionFile="dbCfg.xml">
  <dbConnection connectionName="db1"
      connectionString="Data Source=localhost\SQLEXPRESS;
          Initial Catalog=AdventureWorks;Integrated Security=True;"
      providerName="System.Data.SqlClient" />
</globalSetting>


 

<<Back to table of content   

 

Export data set into XML

You can export data into XML file by AnyDbTest GUI Runner. For performance testing, you can export all output-type arguments of the query statement, including record sets and scalar values. For unit testing, AnyDbTest will export the argument result specified by outputArgument attribute, because in most cases, we only care those arguments other than all output-type arguments.

You can set two attributes, one is exportRecordCount and another is wrapStringByCDATA. Separately they are to control exported record count and determine whether wrap all string-type record value in CDATA element. By default, the AnyDbTest will only export 200 records for performance reason.

exportRecordCount must be in -1, 0, other integer value greater than 0.
If exportRecordCount =-1, it will output all records
if exportRecordCount =0, it will not output any record
if exportRecordCount =Value, it will output the given number of records

There are two places to customize export setting. They are in globalSetting and outputSetting section of test case. Obviously, the former setting is global level; it will be the default setting in the test case file. If you set these attributes in outputSetting section of one test case. AnyDbTest will use the setting to replace the global setting for this test case.

Global level setting,

<globalSetting exportRecordCount="1000" wrapStringByCDATA="false">  
</globalSetting>


 

Testing case level setting,

<perfTestCase testCaseName="PT_Fetch_USA_States" 
              statementAlias="proc_Get_state_in_country">
  <inputArguments>
    <argument name="@CountryCode" value="US"/>
  </inputArguments>
  <testCaseRemark>
    test how long it takes to execute usp_GetStatesInCountry
  </testCaseRemark>
  <outputSetting exportRecordCount="20" wrapStringByCDATA="false" >
    <field name="StateProvinceID" />
    <field name="StateProvinceCode" />
    <field name="CountryRegionCode" />
    <field name="StateProvinceName"  outputName="StateProvinceName"/>
  </outputSetting>
</perfTestCase>


 

<<Back to table of content   

 

Convert XML-fomat data file into Excel format

Your test often needs some data to work with. In most situations you will work with Excel spreadsheet. But we know it is bore and error-prone to prepare the data Excel file, especially when you need a lot of data. You can manually create an Excel data file from scratch or create one by some data from your database.

AnyDbTest provides us with an easy approach rather than from scratch to create Excel data file. You can export data into XML file by AnyDbTest GUI Runner, and then convert XML data file into Excel file by using a utility namely Xml2Excel.exe.

We must be aware that Excel file has some constraints when it will be used as data source.

• 1. Row count must be less than 65536.
• 2. Column count must be less than 256.
• 3. The length of test in every cell must be less than 256.

So, the xml file that will be converted must need the following demands,

• 1. In xml file, row node count must be less than 65536.
• 2. In xml file, column node count must be less than 256.
• 3. In xml file, length of every data node value must be less than 256.

After conversion, please remove the first row manually in order to use the excel file for future data comparison.

<<Back to table of content   

 

How to use Excel as reference test data

In AnyDbTest, Excel data file is treated as a database just like Oracle or SQL Server. Important note - it must be an Excel 2003 format - not the 2007 format. When we use Excel as test data source, the typical steps are,

• 1. Create DB connection string for excel file by using DbConnectionBuilder.exe wizard.
• 2. Write query SQL statement.
• 3. Use the query SQL statement in unit testing case.

In this chapter, we will learn Excel SQL dialect firstly because it has some differences from normal database.

Your Excel data may be contained in your workbook in one of the following ways:

• An entire worksheet.
• A named range of cells on a worksheet.
• An unnamed range of cells on a worksheet.

Specify a Worksheet

The file contains the following, note you must include a header (otherwise your first test case is ignored):

To specify a worksheet as your data source, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example:

SELECT * FROM [Sheet1$]

Or,

SELECT CountryCode, Name FROM [Sheet1$]

Microsoft prefers the square brackets, which are the standing convention for problematic database object names. If you omit both the dollar sign and the brackets, or just the dollar sign, you receive the following error message:

... the Jet database engine could not find the specified object

If you use the dollar sign but omit the brackets, you will see the following error message:

Syntax error in FROM clause.

If you try to use ordinary single quotes, you receive the following error message:

Syntax error in query. Incomplete query clause.

Specify a Named Range

To set the data as a named range, right the block of data (including header), right click and select Name a range.

If you want to edit or delete an existing name, then you need to use the Name Manager under the Formulas tab.

To specify a named range of cells as your data source, simply use the defined name. For example:

SELECT * FROM TestData

Specify an Unnamed Range

To specify an unnamed range of cells as your record source, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:

SELECT * FROM [Sheet1$A1:B10]

A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheet title above and to the left of the data in cell A1.

A caution about specifying ranges: When you specify a worksheet as your record source, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range.

With MDAC versions prior to 2.5, when you specify a named range, you cannot add new records beyond the defined limits of the range, or you receive the following error message:

Cannot expand named range.

<<Back to table of content   

 

Cross database test

How to perform unit testing cross multiple databases? It is a common issue that we often face. AnyDbTest can help us to handle these problems in two ways.

• 1. We can also compare result sets between target DB and reference DB.
• 2. We can transfer some necessary data from dependency DB into our target DB.

These methods can fulfill our demands completely. We will discuss in next two chapters.

<<Back to table of content   

 

Cross database test-Compare target result set with reference DB

We can use AnyDbTest to compare results from two databases. In this chapter, we will demonstrate to compare the result set of SQL Server query statement with external excel file.

The following sqlDeclaration will fetch all CountryRegion from AdventureWorks database.

<sqlDeclaration alias="sql_GetAllCountryRegion" 
                dbConnection="db1">
  <remark>to get country region info </remark>
  <sql>
    <![CDATA[select * from [Person].[CountryRegion]]]>
  </sql>
  <arguments>
    <argument name="@Return_Table" direction="Return" 
              type="MSSQL_RECORDSET"/>
  </arguments>
</sqlDeclaration>


 

The following sqlDeclaration will retrieve CountryRegion from the excel file.

<sqlDeclaration alias="Sql_GetCountryFromReferExcel" 
                dbConnection="excel_refer">
  <sql>
    <![CDATA[ Select CountryCode,Name from [Sheet1$]]]>
  </sql>
  <arguments>
    <argument name="RETURN_Table" direction="Return"  
              type="XLS_RECORDSET"  />
  </arguments>
  <remark> Get one recordset from excel</remark>
</sqlDeclaration>


 

The following unit testing will compute whether recordset of AdventureWorks is superset of recordset of Excel.

<unitTestCase testCaseName="UT_DemoReferExternalData" 
          assertion="IsSupersetOf" testInSandbox="true">
  <targetResultset statementAlias="sql_GetAllCountryRegion" 
                   outputArgument="@Return_Table">
    <outputSetting>
      <field name="CountryRegionCode" outputName="CountryCode" />
      <field name="Name"/>
    </outputSetting>
  </targetResultset>
  <referenceResultset statementAlias="Sql_GetCountryFromReferExcel" 
                      outputArgument="RETURN_Table">
    <outputSetting>
      <field name="CountryCode" outputName="CountryCode" />
      <field name="Name"/>
    </outputSetting>
  </referenceResultset>
</unitTestCase>


 

<<Back to table of content   

 

Cross database test-Pump data from reference DB

We can use a pump action to initialize test data before performing test case. If we set the test in sandbox, after test, the table of will restore to intact state.

In the following example, we will use one excel file as pump data source. It contains column header and 3 rows of data.

The following sqlDeclaration will retrieve CountryRegion from the excel file.

<sqlDeclaration alias="Sql_GetCountryFromPumpExcel" 
                dbConnection="excel_pump">
  <sql>
    <![CDATA[ Select CountryCode,Name from [Sheet1$]]]>
  </sql>
  <arguments>
    <argument name="RETURN_Table" direction="Return"  
              type="XLS_RECORDSET"  />
  </arguments>
  <remark> Get one recordset from excel</remark>
</sqlDeclaration>


 

The following sqlDeclaration will insert one CountryRegion record into AdventureWorkds DB.

<sqlDeclaration alias="sql_InsertCountryRegion" dbConnection="db1">
  <sql><![CDATA[insert Person.CountryRegion (CountryRegionCode,Name)
    values (@CountryRegionCode,@Name)]]></sql>
  <arguments>
    <argument name="@CountryRegionCode" direction="Input" 
              type ="MSSQL_NCHAR"/>
    <argument name="@Name" direction="Input" type="MSSQL_NCHAR"/>
  </arguments>
</sqlDeclaration>


 

The following sqlDeclaration will fetch all CountryRegion from AdventureWorks database.

<sqlDeclaration alias="sql_GetAllCountryRegion" 
                dbConnection="db1">
  <remark>to get country region info </remark>
  <sql>
    <![CDATA[select * from [Person].[CountryRegion]]]>    
  </sql>
  <arguments>
    <argument name="@Return_Table" direction="Return" 
              type="MSSQL_RECORDSET"/>
  </arguments>
</sqlDeclaration>

The following unit testing will perform data pump action at first, then determine whether record count of CountryRegion table in AdventureWorks is equal with 238 or not. At last all inserted records will disappear because the test is in sandbox.

<unitTestCase testCaseName="UT_DemoPump" assertion="RecordCountEqual"
              testInSandbox="true">
  <setup>
    <dataPumpAction>
      <pumpSourceStatement statementAlias="Sql_GetCountryFromPumpExcel"
                           outputArgument="RETURN_Table">
      </pumpSourceStatement>
      <pumpDestinationStatement statementAlias="sql_InsertCountryRegion">
        <inputArguments>
          <argument name="@CountryRegionCode"
                    value="CountryCode" valueIsReferFieldName="true"/>
          <argument name="@Name"
                    value="Name" valueIsReferFieldName="true"/>
        </inputArguments>
      </pumpDestinationStatement>
    </dataPumpAction>
  </setup>
  
  <targetResultset statementAlias="sql_GetAllCountryRegion"
                   outputArgument="@Return_Table">
  </targetResultset>
 
  <referenceResultset statementAlias="sql_get_const_number"
                      outputArgument="@result">
    <inputArguments>
      <argument name="@constant" value="238"/>
    </inputArguments>
  </referenceResultset>
</unitTestCase>

<<Back to table of content   

 

Validate replication, ETL, DTS and SSIS tasks

We often need to validate the replication, ETL, DTS & SSIS result, because the databases are our most critical knowledge and wealth. It is always necessary to verify the replication/ETL process is successful. AnyDbTest is exactly fit this task because it's very reliable and also very fast and very flexible.

The instructions are just like performing a simple unit testing.

• Step 1, write a unit testing case including two query statements. One statement is to retrieve data from replication source database. Another statement retrieves data replication destination database. And then choose an assertion type suitable for your needs.


• Step 2, run the unit testing case by using AnyDbTest GUI Runner,


• Step 3, open diff diagram, and click one create diff file button suitable for your case.

Please refer Cross database test-Compare target result set with reference DB.

<<Back to table of content