SQLITE - Unable to drop tables

[FarshadV]

I am using Delphi XE with UniDac 4.0.1 15-Sep-11 and can't drop temporary tables which I am creating in code. Each time I try to drop it I get the "Database in Use" message and it looks like my only option is to close my database connection and re-open it which makes me lose all my current data driven UI controls including grids. How can I drop a temporary table without closing the connection?

Thanks,

Farshad R. Vossoughi

[ZEuS]

Hello.

Such error can occur if there is a "pending" query to the temporary table when you are trying to drop it. For example, if there is an opened TUniQuery that selects from the temporary table, with the FetchAll property set to False.
To solve the problem you should close all the datasets that use the temporary table before dropping the table. Or you should open datasets that use the temporary table, with FetchAll set to True.
You can find more detailed information about database locking here: http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked.

[FarshadV]

Unfortunately that does not work. I have a very basic procedure such as:

The following code will create a temporary table called "tmp001", it will wait 2 seconds, then it will drop the table.

Code: Select all

    // Deactivate and Clear the query    
    //
    qryTempTable.Active:= False;
    qryTempTable.SQL.Clear;
    qryTempTable.Params.Clear;
    qryTempTable.SpecificOptions.Values['FetchAll']:= 'True';

    // Create the temp table
    //
    qryTempTable.SQL.Add('CREATE TEMP TABLE tmp001');
    qryTempTable.SQL.Add('(');
    qryTempTable.SQL.Add('  ID            integer,');
    qryTempTable.SQL.Add('  FieldID       integer');
    qryTempTable.SQL.Add(');');

    qryTempTable.Execute;
    qryTempTable.Active:= False;

    sleep(2000);

    // Deactivate and Clear the query    
    //
    qryTempTable.Active:= False;
    qryTempTable.SQL.Clear;
    qryTempTable.Params.Clear;
    qryTempTable.SpecificOptions.Values['FetchAll']:= 'True';

    // Drop the table
    //
    qryTempTable.SQL.Add('DROP TABLE IF EXISTS tmp001;');
    qryTempTable.Execute;
    qryTempTable.Active:= False;

Drop table will raise "ESQLiteError" with message "database table is locked"

Any ideas as to how I can fix this?

Thanks,

Farshad R. Vossoughi

[ZEuS]

Unfortunately, I still cannot reproduce the problem.
Your sample code works correctly without any errors in Delphi XE with UniDAC 4.0.1 and the latest SQLite client library downloaded from the official SQLite site.
Please try to download the archive with the latest SQLite client library from the official site ( http://www.sqlite.org/sqlite-dll-win32-x86-3071200.zip ), unzip it to the folder where the EXE-file of your project is located and check if the problem persists when using the latest client library.