Below are some facts to remember when creating and using database checkpoints in TestComplete:
When verifying database data, TestComplete checks only stored values that are selected in the DBTable Element editor. Unselected values are excluded from verification.
When you call a database checkpoint in your test, TestComplete retrieves data from the database according to the conditions defined by the DBTable element that stores baseline data and then checks the retrieved data against the stored data.
If you specify key columns in your baseline database table, TestComplete ignores the records order while performing verification. It uses key columns to identify records.
If your baseline database table does not have key columns, TestComplete performs verification according to the records order. In other words, if the stored record exists in the retrieved recordset, but its position differs from the stored position, verification will fail.
You can use BLOB fields in checkpoints. TestComplete calculates hash values for these fields and stores them to the DBTable element. During verification, TestComplete extracts data from the BLOB field of the tested data source, calculates a hash value for it and then compares this value against the stored hash value.
The names of the specified databases may contain special characters, such as spaces, $, ^ or other. If you try specifying such names in the database table checkpoint, TestComplete will raise the "Error in the FROM clause" error message. Typically, this situation occurs when you try reading data from an Excel sheet because, by default, Microsoft Excel generates the following names for the sheets: Sheet1$, Sheet2$ and so on.
To resolve such problems, you need to use the Custom Query mode and specify the following query for the checkpoint: Select * From [Table_name] (in other words, you need to enclose the name of the database in brackets). So, when you need to retrieve data from an Excel sheet, you need to specify the following query: Select * From [Sheet1$].
The mentioned query equals the situation when you select the specified table (or sheet) in the Table mode and lets you avoid the problems described above.
A specific of the Microsoft ADO engine, which is used by the database checkpoint, is that it converts Decimal values to the Currency type during retrieval.
Since the Currency type has a smaller size than the Decimal type, an overflow error occurs if the Decimal value being retrieved is large and falls outside of the Currency type’s data range. You may encounter this error if the dataset you are going to check contains Decimal fields with large values (more than 14 significant digits). To avoid the issue, you need to exclude the problematic Decimal field from the checkpoint.