Bug: Temporary table locked

[AleksandarBeograd]

Hi!
Here is the bug related to temporary table locking.
I created one database table (Language) and inserted a few records:

CREATE TABLE Language (
LanguageID varchar(10) PRIMARY KEY NOT NULL,
LanguageName varchar(30) NOT NULL UNIQUE,
IsDefault boolean NOT NULL DEFAULT 0
);

INSERT INTO Language (LanguageID, LanguageName, IsDefault) VALUES ('SR', 'Srpski', '0');
INSERT INTO Language (LanguageID, LanguageName, IsDefault) VALUES ('EN', 'English', '1');
INSERT INTO Language (LanguageID, LanguageName, IsDefault) VALUES ('SV', 'Svenska', '0');
INSERT INTO Language (LanguageID, LanguageName, IsDefault) VALUES ('NO', 'Norsk', '0');
INSERT INTO Language (LanguageID, LanguageName, IsDefault) VALUES ('DE', 'Deutch', '0');
COMMIT;

--------------

Here is the C# code:

const string languageId = "EN";
const string strSql = @"
CREATE TEMPORARY TABLE IF NOT EXISTS TempTbl (
LanguageID varchar(10),
LanguageName varchar(30)
);

insert into TempTbl(LanguageID, LanguageName)
select LanguageID, LanguageName
from Language
order by LanguageName;

select rowid, TempTbl.* from TempTbl order by rowid;

DROP TABLE TempTbl;
";

var dt = new DataTable
{
Locale = System.Globalization.CultureInfo.InvariantCulture
};

using (var sqlConnect = new SQLiteConnection(@"Data Source=D:\Projects\TestDevArtSqlite\DbModel\TestDb.db;FailIfMissing=True"))
{
var command = new SQLiteCommand(strSql, sqlConnect);

var dataAdapt = new SQLiteDataAdapter(command);
dataAdapt.Fill(dt);
}

dataGridResults.DataSource = dt;

--------

I've got an error message: 'database table locked'.
This is because of 'DROP TABLE' statement, but it shouldn't happend.

If I erase the last 'select' statement (select rowid, ...) it will work, but
I'll not receive the data I need.

There is a workaround. If I change sql script in the following way:

const string strSql = @"
DROP TABLE IF EXISTS TempTbl;

CREATE TEMPORARY TABLE IF NOT EXISTS TempTbl (
LanguageID varchar(10),
LanguageName varchar(30)
);

insert into TempTbl(LanguageID, LanguageName)
select LanguageID, LanguageName
from Language
order by LanguageName;

select rowid, TempTbl.* from TempTbl order by rowid;
";

this will work, but it's not good enought because resources are not released. In this case, we have small amount of data in temporary table,
but generally speaking, resources must be released by using drop table at the end of script.

Thank you for your efforts.

[Shalex]

Thank you for your report. We will investigate the issue. As a temporary workaround, please drop your temporary table on the same connection after filling data table. Please note, pooling should be turned on (the default behaviour), because, in opposite (Pooling=false), it will be closed physically after filling by data adapter. Please use the following code:

Code: Select all

      private void buttonTest2_Click(object sender, EventArgs e)
      {
         try
         {
             const string strSql = @"
               CREATE TEMPORARY TABLE IF NOT EXISTS TempTbl (
                 LanguageID varchar(10),
                 LanguageName varchar(30)
               );

               insert into TempTbl(LanguageID, LanguageName)
                  select LanguageID, LanguageName
                  from Language 
                  order by LanguageName;

               select rowid, TempTbl.* from TempTbl order by rowid;";

            var dt = new DataTable
            {
               Locale = System.Globalization.CultureInfo.InvariantCulture
            };

            using (var sqlConnect = new SQLiteConnection(@"Data Source=D:\Projects\TestDevArtSqlite\DbModel\TestDb.db;FailIfMissing=True;"))
            {
               var command = new SQLiteCommand(strSql, sqlConnect);

               var dataAdapt = new SQLiteDataAdapter(command);
               dataAdapt.Fill(dt);
               sqlConnect.Open();
               command.CommandText = "DROP TABLE TempTbl;";
               command.ExecuteNonQuery();
            }

            dataGridResults.DataSource = dt;

            tabControl1.SelectedTab = tabResults;
            rtbMessages.Text = "Command successfully completed.";
         }
         catch (Exception ex)
         {
            tabControl1.SelectedTab = tabMessages;
            rtbMessages.Text = ex.Message;
         }
      }

[AleksandarBeograd]

Thank you for quick response.

I noticed your suggestion and I have another one workaround.
Since I want to use the Sqlite for one Web-oriented project I want to access to database in a way - "connect-fetch data-disconnect". So, if we close connection after code executed, temporary table will be erased and "drop table" statement is not necessary at all.

Anyway, I hope that "drop table" problem will be solved soon.

My congratulations to your work.

[Shalex]

Sorry, but this is the designed behaviour, and we don't plan to change it. According to our implementation (for increasing performance), commit-dispose is not executed for every statement in CommandText step-by-step, all statements are executed as one block, and table cannot be dropped if it is not created (and committed) yet. Please use a workaround.