[SOLVED, fixed in svn] TSqlite3Dataset TableExists does not work correctly

[totya]

Hi!

See this sample:

Code: [Select]

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, Sqlite3DS, db,  FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Sqlite3Dataset1: TSqlite3Dataset;
    Sqlite3Dataset2: TSqlite3Dataset;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }



procedure TForm1.Button1Click(Sender: TObject);
var
  n: integer;
  c: TComponent;
  FileNameValue: string;
begin
  FileNameValue:= 'test.db';
  DeleteFileUTF8(FileNameValue);

  for n:= 0 to ComponentCount -1 do
  begin
    c:= Components[n];

    if c is TSqlite3Dataset then
      with (c as TSqlite3Dataset) do
       begin
         FileName:= FileNameValue;
       end;
  end;

  with Sqlite3Dataset1 do
  begin
    TableName:= 'table1';

    FieldDefs.Clear;
    FieldDefs.Add('id', ftAutoInc);
    CreateTable;
  end;

  with Sqlite3Dataset2 do
  begin
    TableName:= 'table2';

    FieldDefs.Clear;
    FieldDefs.Add('id', ftAutoInc);
    CreateTable;
  end;

  with Sqlite3Dataset1 do
   if TableExists
     then ShowMessage(TableName+' is OK!')
     else ShowMessage(TableName+' is NOK!');

  with Sqlite3Dataset1 do
   if TableExists
     then ShowMessage(TableName+' is OK!')
     else ShowMessage(TableName+' is NOK!');

  with Sqlite3Dataset2 do
   if TableExists
     then ShowMessage(TableName+' is OK!')
     else ShowMessage(TableName+' is NOK!');

  with Sqlite3Dataset2 do
   if TableExists
     then ShowMessage(TableName+' is OK!')
     else ShowMessage(TableName+' is NOK!');

  Close;
end;

end.


First message is NOK, but why? 

[rvk]

I'm not familiar with TSqlite3Dataset but shouldn't there be a Applyupdates; after CreateTable; ?

(and maybe a Transaction.commitretaining; if you're working with transactions?)

[totya]

Hi Master!

Well, Applyupdates generate exception, so new lines are:

Code: [Select]

CreateTable; Open; ApplyUpdates; Close; 
But the result is same....

Edit.:
If table is not empty, result is same too... see:

Code: [Select]

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, Sqlite3DS, db,  FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Sqlite3Dataset1: TSqlite3Dataset;
    Sqlite3Dataset2: TSqlite3Dataset;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }



procedure TForm1.Button1Click(Sender: TObject);
var
  n: integer;
  c: TComponent;
  FileNameValue: string;
begin
  FileNameValue:= 'test.db';
  DeleteFileUTF8(FileNameValue);

  for n:= 0 to ComponentCount -1 do
  begin
    c:= Components[n];

    if c is TSqlite3Dataset then
      with (c as TSqlite3Dataset) do
       begin
         FileName:= FileNameValue;
       end;
  end;

  with Sqlite3Dataset1 do
  begin
    TableName:= 'table1';

    FieldDefs.Clear;
    FieldDefs.Add('id', ftAutoInc);
    FieldDefs.Add('id2', ftString);
    CreateTable;

    Open;
    Append;
    FieldByName('id2').AsString:='test';
    Post;
    Append;
    FieldByName('id2').AsString:='test';
    Post;
    ApplyUpdates;
  end;

  with Sqlite3Dataset2 do
  begin
    TableName:= 'table2';

    FieldDefs.Clear;
    FieldDefs.Add('id', ftAutoInc);
    FieldDefs.Add('id2', ftString);
    CreateTable;

    Open;
    Append;
    FieldByName('id2').AsString:='test';
    Post;
    Append;
    FieldByName('id2').AsString:='test';
    Post;
    ApplyUpdates;
  end;

  with Sqlite3Dataset1 do
   if TableExists
     then ShowMessage(TableName+' is OK!')
     else ShowMessage(TableName+' is NOK!');

  with Sqlite3Dataset1 do
   if TableExists
     then ShowMessage(TableName+' is OK!')
     else ShowMessage(TableName+' is NOK!');

  with Sqlite3Dataset2 do
   if TableExists
     then ShowMessage(TableName+' is OK!')
     else ShowMessage(TableName+' is NOK!');

  with Sqlite3Dataset2 do
   if TableExists
     then ShowMessage(TableName+' is OK!')
     else ShowMessage(TableName+' is NOK!');

  Close;
end;

end.


[rvk]

Ok,

Do something like this:

Code: [Select]

    if not CreateTable then
      Showmessage('Something went wrong (code=' + inttostr(ReturnCode) + ')');
What error-message are you getting from CreateTable?

(CreateTable gives back true if it succeeds and false is it fails. You can readout ReturnCode afterwards to see the error)


EDIT: Correction... Yeah. There is definitly something fishy with this component

CreateTable always returns false because SQLite returns SQLITE_DONE and TCustomSqliteDataset expects SQLITE_OK.

Also the first call to TableExists fails after that.

Weird.

[totya]

Hi!

Tables are created (and fields too), I see them with SQLiteDatabaseBrowser.

But you are right, I get error code twice time: 101

[rvk]

Yeah. Just edited my answer:
-----
There is definitly something fishy with this component

CreateTable always returns false because SQLite returns SQLITE_DONE and TCustomSqliteDataset expects SQLITE_OK.

Also the first call to TableExists fails after that.

Weird.

[rvk]

You could put a Sqlite3Dataset1.Active := true; above the TableExists-lines. (maybe setting it to false directly afterwards)

Code: [Select]

  Sqlite3Dataset1.Active := true;
  Sqlite3Dataset1.Active := false;
  with Sqlite3Dataset1 do
   if TableExists
     then ShowMessage(TableName+' is OK!')
     else ShowMessage(TableName+' is NOK!');

It's not really a solution but it works.

BTW: Maybe you WANT the dataset open afterwards. In that case you can leave out the Active := false line.

[totya]

Hi!

Thanks, it's working, but only if table is exist. If not, I get exceptions...

[rvk]

Thanks, it's working, but only if table is exist. If not, I get exceptions...

You can catch exceptions (try/except constuction)

You could also only do the createtable if the table does not already exists.
Like this:

Code: [Select]

    if not TableExists then
      if not CreateTable then
        Showmessage('Something went wrong (code='+inttostr(ReturnCode)+')');

After that it should be save to set Active to true but with the except construct:

Code: [Select]

  try
    Sqlite3Dataset1.Active := true;
    Sqlite3Dataset2.Active := true;
  except
    on E: EDatabaseError do
    begin
      ShowMessage('An error has occurred: ' + E.Message);
      exit;
    end;
  end;

(Note: You will get an exception when running in the IDE but you can click continue or check always ignote this exception type. Outside the ide you'll only get the showmessage)

[LuizAmérico]

Thanks for reporting.

I fixed both bugs locally. I will update repository later today

[LuizAmérico]

Updated the fpc repository. See at svn.freepascal.org/cgi-bin/viewvc.cgi/trunk/packages/fcl-db/src/sqlite/

[rvk]

Updated the fpc repository. See at svn.freepascal.org/cgi-bin/viewvc.cgi/trunk/packages/fcl-db/src/sqlite/

Confirmed the two bugs are fixed.
1) CreateTable not returns correct result-boolean
2) After CreateTable the first ExistsTable returns correct result.

But. Wow. Normally I compile from trunk (complete new FPC and Lazarus) but for this occasion I patched Lazarus 1.4 itself. For me it's no problem but I'm guessing compiling this patch (via fpc -O2 -gl sqlite3ds.pas) isn't for everyone (as explained as option 1 here).

Doesn't the release version of Lazarus come with some GUI to recompile FPC-units too?
Wouldn't it make sense to have a "Rebuild FPC-units" in the menus (besides Rebuild IDE) too?

[totya]

Doesn't the release version of Lazarus come with some GUI to recompile FPC-units too?
Wouldn't it make sense to have a "Rebuild FPC-units" in the menus (besides Rebuild IDE) too?

Thanks for your help, and this is a very good idea!

[totya]

Updated the fpc repository. See at svn.freepascal.org/cgi-bin/viewvc.cgi/trunk/packages/fcl-db/src/sqlite/

Thank you, this patch is working, the results are perfect for me

Thanks again for the patch and for the very qiuick response!