[SOLVED, fixed in svn] TSqlite3Dataset TableExists does not work
correctly
lazarus / 16-Databases

----------------------------------------------------------------------
totya · 2015-04-27 19:13

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 · 2015-04-27 19:42

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 · 2015-04-27 20:24

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 · 2015-04-27 20:46

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 · 2015-04-27 20:52

Hi!

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

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

----------------------------------------------------------------------
rvk · 2015-04-27 20:54

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 · 2015-04-27 21:19

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 · 2015-04-27 21:32

Hi!

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

----------------------------------------------------------------------
rvk · 2015-04-27 21:55

Quote from: totya on April 27, 2015, 11:32:21 pmThanks, 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 · 2015-04-27 22:08

Thanks for reporting.

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

----------------------------------------------------------------------
LuizAmérico · 2015-04-28 02:25

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

----------------------------------------------------------------------
rvk · 2015-04-28 06:50

Quote from: LuizAmérico on April 28, 2015, 04:25:39 amUpdated 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 · 2015-04-28 10:29

Quote from: rvk on April 28, 2015, 08:50:00 amDoesn'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 · 2015-04-28 10:54

Quote from: LuizAmérico on April 28, 2015, 04:25:39 amUpdated 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!