Recent

Author Topic: [SOLVED, fixed in svn] TSqlite3Dataset TableExists does not work correctly  (Read 3503 times)

totya

  • Hero Member
  • *****
  • Posts: 636
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?  :o
« Last Edit: May 09, 2015, 10:30:42 pm by totya »

rvk

  • Hero Member
  • *****
  • Posts: 4327
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #1 on: April 27, 2015, 09:42:45 pm »
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

  • Hero Member
  • *****
  • Posts: 636
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #2 on: April 27, 2015, 10:24:14 pm »
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.

« Last Edit: April 27, 2015, 10:38:05 pm by totya »

rvk

  • Hero Member
  • *****
  • Posts: 4327
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #3 on: April 27, 2015, 10:46:15 pm »
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.
« Last Edit: April 27, 2015, 10:54:21 pm by rvk »

totya

  • Hero Member
  • *****
  • Posts: 636
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #4 on: April 27, 2015, 10:52:43 pm »
Hi!

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

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

rvk

  • Hero Member
  • *****
  • Posts: 4327
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #5 on: April 27, 2015, 10:54:56 pm »
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

  • Hero Member
  • *****
  • Posts: 4327
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #6 on: April 27, 2015, 11:19:27 pm »
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.
« Last Edit: April 27, 2015, 11:25:54 pm by rvk »

totya

  • Hero Member
  • *****
  • Posts: 636
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #7 on: April 27, 2015, 11:32:21 pm »
Hi!

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

rvk

  • Hero Member
  • *****
  • Posts: 4327
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #8 on: April 27, 2015, 11:55:16 pm »
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

  • Sr. Member
  • ****
  • Posts: 458
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #9 on: April 28, 2015, 12:08:25 am »
Thanks for reporting.

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

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #10 on: April 28, 2015, 04:25:39 am »
Updated the fpc repository. See at svn.freepascal.org/cgi-bin/viewvc.cgi/trunk/packages/fcl-db/src/sqlite/

rvk

  • Hero Member
  • *****
  • Posts: 4327
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #11 on: April 28, 2015, 08:50:00 am »
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

  • Hero Member
  • *****
  • Posts: 636
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #12 on: April 28, 2015, 12:29:58 pm »
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

  • Hero Member
  • *****
  • Posts: 636
Re: TSqlite3Dataset TableExists does not work correctly
« Reply #13 on: April 28, 2015, 12:54:01 pm »
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!

 

TinyPortal © 2005-2018