Incorrect Query Results After External DB Connection Revisions

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Bob Boyd
Posts: 16
Joined: Thu 28 Jul 2016 19:26

Incorrect Query Results After External DB Connection Revisions

Post by Bob Boyd » Tue 09 Oct 2018 19:27

I have discovered an unusual situation with the results of a query when a table is loaded with >25 records and an external connection like the DB Browser for SQLite utility or our LabView report server is used to modify a different table record.

The application opens two TLiteConnections, one for the user’s selected source database and another for the user’s private database for that contains the user’s temporary records for editing, reporting and data acquisitions. The private database that is stored in the C:\Users\...\AppData\Local\PWC60 folder. The UI has a TLiteTable and TLiteQuery objects that are connected to the private database which has an ATTACH of the source database transferring records between source and the private for any user processes such as acquiring and posting the data acquisitions records. The errors have only occurred for the private database connection.

The application allows the user to select report records to be batched together for a PDF report. There are several tables involved that are connected to the private database.

The private RptListBatch table contains the list of records the user selected from multiple source Archive data forms that will be combined into a single PDF document. The private RptList table is used to generate the reports by a LabView report server. The UI form for the batch report generation displays the selected RptListBatch table records in a grid. The batch report generation form has a PDF button that will execute a query to insert all RptListBatch table records into the RptList table for processing.

After the PDF report is generated, a data acquisition that reads the test configuration and stores the results in that private database needs to be checked to identify the acquisition result status fields. A query is used to return the field values that are checked for completion or an abort button. The results are correct until more than 25 records are loaded into the RptListBatch table and the PDF report query is executed to insert all records into the private RptList table.

I have tested this situation by disabling the LabView report server and using the DB Browser for SQLite utility to revise the two fields of the data acquisition table that are used in the query. The fields can be revised and the written to the database, however the results of the UI query of the private database table will only be correct when there are up to 25 records in the RptListBatch table when the PDF report query is executed. Adding record 26 will return the initialized record values from the private data acquisition table.

I have prepared a stripped down version of the application form that uses the existing procedures for startup, reporting and simulated data acquisition if needed for your debugging.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Incorrect Query Results After External DB Connection Revisions

Post by MaximG » Wed 10 Oct 2018 06:48

Yes, it's true, to investigate the issue, we will need a composed stripped down version of the application form, whose source code you can send us via the e-support form (https://www.devart.com/company/contactform.html)

Post Reply