Database Table Checkpoint issue

Question

Hi all,
&nbsp;
I am having an issue with DB checkpoint, on some values it complaints in the log that the values are different even though they are not.
E.G&nbsp;The "BeginChainage" field of row 2 contains a value (25) that differs from the stored one (25).
The of row 22 contains a value (34861.6789703723) that differs from the stored one (34891.6789703723). The "BeginDistanceStamp" field of row 23 contains a value (349.836644687862) that differs from the stored one (301.55632468786). The "EndDistanceStamp" field of row 23 contains a value (365.930084687862) that differs from the stored one (317.64976468786). The "BeginChainage" field of row 23 contains a value (34861.6789703723) that differs from the stored one (34891.6789703723). The "EndChainage" field of row 23 contains a value (34851.6789703723) that differs from the stored one (34881.6789703723). The "BeginDistanceStamp" field of row 24 contains a value (365.930084687862) that differs from the stored one (317.64976468786). The "EndDistanceStamp" field of row 24 contains a value (382.023524687862) that differs from the stored one (333.74320468786). The "BeginChainage" field of row 24 contains a value (34851.6789703723) that differs from the stored one (34881.6789703723). The "EndChainage" field of row 24 contains a value (34841.6789703723) that differs from the stored one (34871.6789703723). The "BeginDistanceStamp" field of row 25 contains a value (382.023524687862) that differs from the stored one (333.74320468786). The "EndDistanceStamp" field of row 25 contains a value (398.116964687862) that differs from the stored one (349.83664468786). The "BeginChainage" field of row 25 contains a value (34841.6789703723) that differs from the stored one (34871.6789703723). The "EndChainage" field of row 25 contains a value (34831.6789703723) that differs from the stored one (34861.6789703723). The "BeginDistanceStamp" field of row 26 contains a value (398.116964687862) that differs from the stored one (349.83664468786). The "EndDistanceStamp" field of row 26 contains a value (414.210404687862) that differs from the stored one (365.93008468786). The "BeginChainage" field of row 26 contains a value (34831.6789703723) that differs from the stored one (34861.6789703723). The "EndChainage" field of row 26 contains a value (34821.6789703723) that differs from the stored one (34851.6789703723). The "BeginDistanceStamp" field of row 27 contains a value (414.210404687862) that differs from the stored one (365.93008468786). The "EndDistanceStamp" field of row 27 contains a value (430.303844687862) that differs from the stored one (382.02352468786). The "BeginChainage" field of row 27 contains a value (34821.6789703723) that differs from the stored one (34851.6789703723). The "EndChainage" field of row 27 contains a value (34811.6789703723) that differs from the stored one (34841.6789703723). The "BeginDistanceStamp" field of row 28 contains a value (430.303844687862) that differs from the stored one (382.02352468786). The "EndDistanceStamp" field of row 28 contains a value (446.397284687862) that differs from the stored one (398.11696468786). The "BeginChainage" field of row 28 contains a value (34811.6789703723) that differs from the stored one (34841.6789703723). The "EndChainage" field of row 28 contains a value (34801.6789703723) that differs from the stored one (34831.6789703723). The "BeginDistanceStamp" field of row 29 contains a value (446.397284687862) that differs from the stored one (398.11696468786). The "EndDistanceStamp" field of row 29 contains a value (462.490724687862) that differs from the stored one (414.210404687859). The "BeginChainage" field of row 29 contains a value (34801.6789703723) that differs from the stored one (34831.6789703723). The "EndChainage" field of row 29 contains a value (34791.6789703723) that differs from the stored one (34821.6789703723). The "BeginDistanceStamp" field of row 30 contains a value (462.490724687862) that differs from the stored one (414.210404687859). The "EndDistanceStamp" field of row 30 contains a value (478.584164687862) that differs from the stored one (430.303844687859). The "BeginChainage" field of row 30 contains a value (34791.6789703723) that differs from the stored one (34821.6789703723). The "EndChainage" field of row 30 contains a value (34781.6789703723) that differs from the stored one (34811.6789703723). The "BeginDistanceStamp" field of row 31 contains a value (478.584164687862) that differs from the stored one (430.303844687859). The "EndDistanceStamp" field of row 31 contains a value (494.677604687862) that differs from the stored one (446.397284687859). The "BeginChainage" field of row 31 contains a value (34781.6789703723) that differs from the stored one (34811.6789703723). The "EndChainage" field of row 31 contains a value (34771.6789703723) that differs from the stored one (34801.6789703723). The "BeginDistanceStamp" field of row 32 contains a value (494.677604687862) that differs from the stored one (446.397284687859). The "EndDistanceStamp" field of row 32 contains a value (510.771044687862) that differs from the stored one (462.490724687859). The "BeginChainage" field of row 32 contains a value (34771.6789703723) that differs from the stored one (34801.6789703723). The "EndChainage" field of row 32 contains a value (34761.6789703723) that differs from the stored one (34791.6789703723). The "BeginDistanceStamp" field of row 33 contains a value (510.771044687862) that differs from the stored one (462.490724687859). The "EndDistanceStamp" field of row 33 contains a value (526.864484687862) that differs from the stored one (478.584164687859). The "BeginChainage" field of row 33 contains a value (34761.6789703723) that differs from the stored one (34791.6789703723). The "EndChainage" field of row 33 contains a value (34751.6789703723) that differs from the stored one (34781.6789703723). The "BeginDistanceStamp" field of row 34 contains a value (526.864484687862) that differs from the stored one (478.584164687859). The "EndDistanceStamp" field of row 34 contains a value (542.957924687862) that differs from the stored one (494.677604687859). The "BeginChainage" field of row 34 contains a value (34751.6789703723) that differs from the stored one (34781.6789703723). The "EndChainage" field of row 34 contains a value (34741.6789703723) that differs from the stored one (34771.6789703723). The "BeginDistanceStamp" field of row 35 contains a value (542.957924687862) that differs from the stored one (494.677604687859). The "EndDistanceStamp" field of row 35 contains a value (559.051364687862) that differs from the stored one (510.771044687859). The "BeginChainage" field of row 35 contains a value (34741.6789703723) that differs from the stored one (34771.6789703723). The "EndChainage" field of row 35 contains a value (34731.6789703723) that differs from the stored one (34761.6789703723). The "BeginDistanceStamp" field of row 36 contains a value (559.051364687862) that differs from the stored one (510.771044687859). The "EndDistanceStamp" field of row 36 contains a value (575.144804687862) that differs from the stored one (526.864484687858). The "BeginChainage" field of row 36 contains a value (34731.6789703723) that differs from the stored one (34761.6789703723). The "EndChainage" field of row 36 contains a value (34721.6789703723) that differs from the stored one (34751.6789703723). The "BeginDistanceStamp" field of row 37 contains a value (575.144804687862) that differs from the stored one (526.864484687858). The "EndDistanceStamp" field of row 37 contains a value (591.238244687862) that differs from the stored one (542.957924687858). The "BeginChainage" field of row 37 contains a value (34721.6789703723) that differs from the stored one (34751.6789703723). The "EndChainage" field of row 37 contains a value (34711.6789703723) that differs from the stored one (34741.6789703723). The "BeginDistanceStamp" field of row 38 contains a value (591.238244687862) that differs from the stored one (542.957924687858). The "EndDistanceStamp" field of row 38 contains a value (607.331684687862) that differs from the stored one (559.051364687858). The "BeginChainage" field of row 38 contains a value (34711.6789703723) that differs from the stored one (34741.6789703723). The "EndChainage" field of row 38 contains a value (34701.6789703723) that differs from the stored one (34731.6789703723). The "BeginDistanceStamp" field of row 39 contains a value (607.331684687862) that differs from the stored one (559.051364687858). The "EndDistanceStamp" field of row 39 contains a value (623.425124687862) that differs from the stored one (575.144804687858). The "BeginChainage" field of row 39 contains a value (34701.6789703723) that differs from the stored one (34731.6789703723). The "EndChainage" field of row 39 contains a value (34691.6789703723) that differs from the stored one (34721.6789703723). The "BeginDistanceStamp" field of row 40 contains a value (623.425124687862) that differs from the stored one (575.144804687858). The "EndDistanceStamp" field of row 40 contains a value (639.518564687862) that differs from the stored one (591.238244687858). The "BeginChainage" field of row 40 contains a value (34691.6789703723) that differs from the stored one (34721.6789703723). The "EndChainage" field of row 40 contains a value (34681.6789703723) that differs from the stored one (34711.6789703723). The "BeginDistanceStamp" field of row 41 contains a value (639.518564687862) that differs from the stored one (591.238244687858). The "EndDistanceStamp" field of row 41 contains a value (655.612004687862) that differs from the stored one (607.331684687858). The "BeginChainage" field of row 41 contains a value (34681.6789703723) that differs from the stored one (34711.6789703723). The "EndChainage" field of row 41 contains a value (34671.6789703723) that differs from the stored one (34701.6789703723). The "BeginDistanceStamp" field of row 42 contains a value (655.612004687862) that differs from the stored one (607.331684687858). The "EndDistanceStamp" field of row 42 contains a value (671.705444687862) that differs from the stored one (623.425124687858). The "BeginChainage" field of row 42 contains a value (34671.6789703723) that differs from the stored one (34701.6789703723). The "EndChainage" field of row 42 contains a value (34661.6789703723) that differs from the stored one (34691.6789703723). The "BeginDistanceStamp" field of row 43 contains a value (671.705444687862) that differs from the stored one (623.425124687858). The "EndDistanceStamp" field of row 43 contains a value (687.798884687862) that differs from the stored one (639.518564687857). The "BeginChainage" field of row 43 contains a value (34661.6789703723) that differs from the stored one (34691.6789703723). The "EndChainage" field of row 43 contains a value (34651.6789703723) that differs from the stored one (34681.6789703723). The field of row&nbsp;
&nbsp;

tristaanogre · Answer

Most likely the problem is that the data in your SQL table has more significant digits than what is "stored" in your database checkpoint.&nbsp; So, while the data may LOOK the same in the log, there is a difference.
&nbsp;
I'm honestly not sure how to overcome this as I don't use Database Table checkpoints... I create my own SQL comparison routines because, more often than not, the data in my SQL is dynamic enough that a "static" table checkpoint just doesn't work.&nbsp; You might want to consider the same so you can account for these kinds of differences.&nbsp;&nbsp;

alexpodimov · Answer

Hello,
&nbsp;
Thanks for sharing your knowledge.
&nbsp;
I am ok with "static" comparison as it does now, because our intension here is to verify "apples to apples", and it work fine when both DBs are identical, no mater how many rows/columns it is!
But when it's not, it shoots me bunch of records that are incorrect along with those that are correct (but it still says that it's not, E.G. 25!=25) which is anoying.
&nbsp;
If you coud share some info with me on your approaches for DB data comparison that's would be nice :)

tristaanogre · Answer

I use the ADO objects in TestComplete to build my own SQL queries and return a record set.&nbsp; I then have a baseline of what I'm looking for stored in a CSV file.&nbsp; I loop through the record set and compare column values per record with column values per row in the CSV file. It allows me to be very specific in my queries, allows me to format and trim values, and all sorts of stuff like that.
&nbsp;
Now, that said, I THINK Data Table comparisons like you're doing allow you to alter the SQL query used.&nbsp; You might want to consider writing a custom query instead of using the default to "cast" values or trim them or round them or whatever.... what you're experiencing, as mentioned, is that the data table stored values only store up to a certin number of digits...but the data in the SQL table itself is a float or something like that which may have additional significant digits that don't display.&nbsp; Doing a custom query allows you to do these kind of formatting to actually compare "apples to apples" rather than what's going on right now.

alexpodimov · Answer

Thanks for quick reply and info shared.
I do use ADO objects, mainly to connect and and drop DBs after comaprison is done, I might consider using more of them.
Speaking about "built-in" feature for DB tables comparison, I always use SELECT * FROM table_name to avoid missing anything, and these tables are stored in TC project, so I know they are correct. Again, my goal is to verify let's call it an aggreagtion process with different versions of our software, and so nothing get's broken/missing and I suppose to get identical data all the time. If so, the whole process works like a charm, when tbl_abc = tbl_abc with a "clean" log after, when at least one cell is unequal, it inconsistently (sometimes that unmatched value only and sometimes more of those that are actually correct and equal) are populating the log. Well, at least it catches when I have at least one incorrect record, but with many other correct and incorrect ones! :) &nbsp;

Forum Discussion

Database Table Checkpoint issue

4 Replies

Related Content

Edit Database table checkpoint SQL

Web Table Checkpoints

Database Checkpoint

web table checkpoints

how can I create dynamic checkpoints to validate database information?

Recent Discussions

Floating window bleep overlapping window takes longer time (~10 min) to click the button on it.

Test case randomly fails with "parent object was not found"

Does TC have a .within() method similar to Cypress.io?

SessionCreator /Timeout option not firing EventControl_OnTimeout

testcomplete feature Intelligent Quality Add Ons