Droppiing table and recreating seems to hold old structure.

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
nszmnsky
Posts: 11
Joined: Wed 21 May 2014 17:07

Droppiing table and recreating seems to hold old structure.

Post by nszmnsky » Mon 03 Aug 2015 16:39

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
Devart Team
Posts: 1024
Joined: Mon 06 Jul 2015 11:34

Re: Droppiing table and recreating seems to hold old structure.

Post by MaximG » Tue 04 Aug 2015 11:02

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
Posts: 11
Joined: Wed 21 May 2014 17:07

Re: Droppiing table and recreating seems to hold old structure.

Post by nszmnsky » Thu 06 Aug 2015 16:18

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
Devart Team
Posts: 1024
Joined: Mon 06 Jul 2015 11:34

Re: Droppiing table and recreating seems to hold old structure.

Post by MaximG » Fri 07 Aug 2015 08:04

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
Devart Team
Posts: 1024
Joined: Mon 06 Jul 2015 11:34

Re: Droppiing table and recreating seems to hold old structure.

Post by MaximG » Tue 11 Aug 2015 08:13

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
Posts: 11
Joined: Wed 21 May 2014 17:07

Re: Droppiing table and recreating seems to hold old structure.

Post by nszmnsky » Mon 17 Aug 2015 18:39

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
Devart Team
Posts: 1024
Joined: Mon 06 Jul 2015 11:34

Re: Droppiing table and recreating seems to hold old structure.

Post by MaximG » Thu 27 Aug 2015 12:34

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.

Post Reply