Query has locked sqlite database

[Exemptor]

Hello!

Used: Delphi 7, UniDAC 3.50.

Component placed on form: TUniConnection, TUniQuery, TUniDataSource, TDBGrid, TDBNavigator, Button.

Before run project UniConnection, UniQuery is inactive. All components connected.

Code: Select all

UniQuery1.SQL.Strings:='select * from users order by id_user';
UniQuery1.LockMode:=lmNone;

So! I do:

Code: Select all

UniConnection1.Connect;
UniQuery1.Open;
UniConnection1.StartTransaction;
UniConnection1.ExecSQL('DELETE FROM USERS WHERE ID_USER = 62',[]);
UniConnection1.Rollback;

It's not work. On last row has error: "Database is locked".

But this code work fine:

Code: Select all

UniConnection1.Connect;
UniConnection1.StartTransaction;
UniConnection1.ExecSQL('DELETE FROM USERS WHERE ID_USER = 62',[]);
UniConnection1.Rollback;

Why?

[AlexP]

Hello,

I can not reproduce the problem.
Please check that you don't lock the table from another application.
Also specify the version of your SQLite.

[Exemptor]

I swear, another application don't locked database!

I check sqlite3.dll (in windows\system32) version is 3.6.19. Updated to 3.7.2 => no fix problem
*Scan all paths on another sqlite3.dll - not found.

Now i create new project, and change properties components only run-time on button click.

Code: Select all

unit Unit1;

interface

uses
  Windows, Messages, Classes, Controls, Forms,
  StdCtrls, DB, DBAccess, Uni, MemDS, UniProvider,
  SQLiteUniProvider;

type
  TForm1 = class(TForm)
    UniConnection1: TUniConnection;
    UniQuery1: TUniQuery;
    Button1: TButton;
    SQLiteUniProvider1: TSQLiteUniProvider;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  UniConnection1.Database:='f:\projects\account\outputbin\cashdesk.s3db';
  UniConnection1.ProviderName:='SQLite';
  UniConnection1.Connect;
  UniQuery1.Connection:=UniConnection1;
  UniQuery1.SQL.Text:='select * from users';
  UniQuery1.Open;
  UniConnection1.StartTransaction;
  UniConnection1.ExecSQL('DELETE FROM USERS WHERE ID_USER = 62',[]);
  UniConnection1.Rollback;
end;

end.

Also if comment row "UniQuery1.Open;" work fine. Else - error "database is locked".

File "cashdesk.s3db" created by python 3.1.2.

Please, help, where my error..?

[AlexP]

Hello,

What do you mean by "File "cashdesk.s3db" created by python 3.1.2."?
I created a database and a table in SQLite Expert and tried to execute your code - it works.

[Exemptor]

Hmm.... If create DB in SQLite Expert, my code is works... It's true.

What do you mean by "File "cashdesk.s3db" created by python 3.1.2."?

I mean, i write script on Python, and execute him (Win XP, Python 3.1.2). Maybe need options for module sqlite in Python..

Thanks.

[AlexP]

Hello,


You can create a database and a table using UniDAC. In this case your code works correctly. To create a database and a table you can use the following code:

UniConnection1.Database:='E:\test.db3';
UniConnection1.ProviderName:='SQLite';
UniConnection1.Connect; //<-- if database doesn't exists, it will be created automaticaly
UniConnection1.ExecSQL('CREATE TABLE test(name VARCHAR2(50),ID NUMBER(1));', [null]);