Droppiing table and recreating seems to hold old structure.

[nszmnsky]

Hello
I'm working on an app that needs to create statistics from differing sets of columns in a table. In my code, I drop the existing STATS table then create new version with the desired columns. There are 10 different sets of stats that need to be calculated from two different tables (which is set in TMPtable string) and between 5 and 12 columns (in SQLselect string).

Code: Select all

  DBM.LiteSQL1.SQL.Clear;
  DBM.LiteSQL1.SQL.Add('DROP TABLE IF EXISTS '+TMPtable+'STATS');
  DBM.LiteSQL1.Execute;

  DBM.LiteSQL1.SQL.Clear;
  DBM.LiteSQL1.SQL.Add('CREATE TABLE '+TMPtable+'STATS AS ' + SQLselect);
  DBM.LiteSQL1.SQL.Add(' WHERE '+SQLwhere);
  DBM.LiteSQL1.Execute;

The problem seems to be that the structure of the existing table is "cached" and used even after the table is re-created. So when re-created table contains new column, such as "scSIZE1", accessing using STATS.FieldByName("scSIZE1").asInteger returns error indicating column does not exist. When I check db using "SQLite Expert Pro" the column is in fact there.
I've tried opening and closing, changing active state, and ApplyUpdates but problem still exists. I'm sure I'm missing something obvious but just not seeing the solution so any insights would be appreciated

[MaximG]

Unfortunately, we couldn't reproduce the problem using the provided code. For the following investigation, please send us a small example that demonstrates the problem, including a test DB. You can send to maximg*devart*com

[nszmnsky]

Maxim, I've forward link to download the request items. I also forgot to mention that the behavior is different when not running within XE7, when running the exe directly I get database lock errors.

[MaximG]

We have received your email with the problem description. However, the link to DropBox is broken. Please try to send your sample again or attach it to a letter. If the sample is rather large, then leave the source code only, i.e. exclude *.dcu, *.exe, etc. files.

[MaximG]

Unfortunately, we couldn't reproduce the "Field Not Found" error using your test project, because the provided archive doesn't include required files (in particular, CRTTplus_ScoreReporting.dpr и CRTT_DB_Reporting.dfm).
The "database file locked" error occurs when trying to delete a table using the "DROP TABLE ..." SQL statement, in case if there are incomplete SELECT statements at the moment (see more details about the error at https://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked). In your test sample, incomplete SELECT statements are contained in in open datasets(TLiteTable, TLiteQuery). To solve the problem, you should either close all the open datasets before deleting the table or set the FetchAll property of all the datasets to True.

[nszmnsky]

After some offline communications with Maxim, he did find a solution to getting the structure to update. I was closing the tables prior to the DROP but it appears that need to close after drop and create, then re-open prior to use.

However still seeing a table lock when trying to drop table occurring when I run the program outside of the Delphi IDE, but doesn't occur while in the IDE. Maxim suggests that it is due to an incomplete SELECT but changing the compenents FetchAll to true hasn't worked, so trying to close tables and queries before trying brute force of closing and re-opening connection.

[MaximG]

After reviewing the application you sent, we found the cause of the «column does not exist» error, and informed you about it. If you still have questions related to the use of LiteDAC, please contact us.