SQLite Blob Fields Problem

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Raphael Vital
Posts: 4
Joined: Tue 25 Nov 2014 13:43

SQLite Blob Fields Problem

Post by Raphael Vital » Tue 25 Nov 2014 13:53

Hi,

I'm trying to use evaluation version of UniDac5512 but in my first test I see the problem with blobs text fields:

I have two fields blob text in my table, fieldA, fieldB and other fields, string, booleans and so on. When I make a update in any other field in this table, the contents of blob text fields vanish. Yes! The text inside both of them just disappear. The other fields, including string fields (varchar 40) remains with the correct contents.

Additional info: I'm using TuniQuery, TClientDataset, TDataSetProvider, TDataSource.
Delphi Version 7 Update Pack2, Windows XP. In problem table, the
TDataSetprovider is set updates to "WhereChanged" and the KeyField set properly in both
(TUniQuery, TClientDataSet).

I'm try this component for access SQLite by indication and I planned to buy, if everything is fine on my system. I tried to understand what happens with the blob text contents and why JUST text on the blobs dissapears. :shock:

Thanks for any kind of help.

R.Vital Mello
Brasil.

P.S: Sorry, my english bites sometimes! :oops:
Top
AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: SQLite Blob Fields Problem

Post by AlexP » Wed 26 Nov 2014 07:31

Hello,

We couldn't reproduce the error on the latest LiteDAC version 2.4.12. The code below works correctly, please modify this code so that the problem can be reproduced and send it to us.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils, LiteAccess, DB, DBClient, Provider;

var
  LiteConnection: TLiteConnection;
  LiteQuery: TLiteQuery;
  DataSetProvider: TDataSetProvider;
  ClientDataSet: TClientDataSet;

begin
  LiteConnection := TLiteConnection.Create(nil);
  try
    LiteConnection.ConnectString := 'Data Source=:memory:;LoginPrompt=False';
    LiteConnection.Connect;
    LiteConnection.ExecSQL('CREATE TABLE TEST(ID INTEGER, TXT VARCHAR2(40), fieldA TEXT, fieldB TEXT, CONSTRAINT PK_TEST PRIMARY KEY(ID))');
    LiteConnection.ExecSQL('INSERT INTO TEST VALUES(1, ''test'', ''fieldA'', ''fieldB'')');
    LiteQuery := TLiteQuery.Create(nil);
    try
      LiteQuery.Connection := LiteConnection;
      LiteQuery.SQL.Text := 'SELECT * FROM TEST';
      LiteQuery.KeyFields := 'ID';
      DataSetProvider := TDataSetProvider.Create(nil);
      try
        DataSetProvider.DataSet := LiteQuery;
        DataSetProvider.UpdateMode := upWhereChanged;
        ClientDataSet := TClientDataSet.Create(nil);
        try
          ClientDataSet.SetProvider(DataSetProvider);
          ClientDataSet.Open;
          Writeln('Blob Values Before Edit:');
          Writeln(Format('Value of FieldA: %s%sValue of Fieldb: %s', [ClientDataSet.FieldByName('fieldA').AsString, #13#10, ClientDataSet.FieldByName('fieldB').AsString]));
          ClientDataSet.Edit;
          ClientDataSet.FieldByName('TXT').AsString := 'New text';
          ClientDataSet.Post;
          ClientDataSet.ApplyUpdates(-1);
          Writeln('Blob Values After Edit:');
          Writeln(Format('Value of FieldA: %s%sValue of Fieldb: %s', [ClientDataSet.FieldByName('fieldA').AsString, #13#10, ClientDataSet.FieldByName('fieldB').AsString]));
        finally
          ClientDataSet.Free;
        end;
      finally
        DataSetProvider.Free;
      end;
    finally
      LiteQuery.Free;
    end;
  finally
    LiteConnection.Free;
    Readln;
  end;
end.
Top
Raphael Vital
Posts: 4
Joined: Tue 25 Nov 2014 13:43

Re: SQLite Blob Fields Problem

Post by Raphael Vital » Fri 28 Nov 2014 12:10

Alexp,

Maybe I have posted in wrong place. Not is LiteDAC version 2.4.12. I have installed UNIDAC 5.5.12. The code you posted return an error in LiteAcess.dcu (file not found).

Have any difference between LiteDac and UniDac or I'm do anything wrong? Sorry if I made a mistake. My environment variables (paths) are set correctly for UniDac 5512.

Thanks for your attention.

R.Vital Mello
Brasil.

P.S: Sorry, my english bites a lot sometimes! :oops:
Top
AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: SQLite Blob Fields Problem

Post by AlexP » Mon 01 Dec 2014 09:01

Please replace the LiteAccess module with the Uni and SQLiteUniProvider ones in the Uses section, as well declarations and creation of the TLiteConnection and TLiteQuery classes with TUniConnection and TUniQuery respectively, and test the code once more (UniDAC 6.0.1).
Top
Raphael Vital
Posts: 4
Joined: Tue 25 Nov 2014 13:43

Re: SQLite Blob Fields Problem

Post by Raphael Vital » Wed 03 Dec 2014 12:34

Hi Alex,

The Result is:

Blob Values Before Edit:
Value of FieldA: fieldA
Value of Fieldb: fieldB
Blob Values After Edit:
Value of FieldA: fieldA
Value of Fieldb: fieldB
ClientDataSet.FieldByName('TXT').AsString := 'New text';
In FieldA the text inserted 'NEW TEXT' does not appear.

I'm running the code right or I'm doing a little mess?

Because missing Provider and DataSetProvider declarations, I change the code to:


{$APPTYPE CONSOLE}

uses
SysUtils,
Uni,
SQLiteUniProvider,
DB,
DBClient,
Provider;

var
UniConnection: TUniConnection;
UniQuery: TUniQuery;
DataSetProvider: TDataSetProvider;
ClientDataSet: TClientDataSet;

begin
UniConnection := TUniConnection.Create(nil);
try
UniConnection.ProviderName := 'SQLite';
UniConnection.ConnectString := 'Provider Name=SQLite;Data Source=:memory:;LoginPrompt=False';
UniConnection.Connect;
UniConnection.ExecSQL('CREATE TABLE TEST(ID INTEGER, TXT VARCHAR2(40), fieldA TEXT, fieldB TEXT, CONSTRAINT PK_TEST PRIMARY KEY(ID))');
UniConnection.ExecSQL('INSERT INTO TEST VALUES(1, ''test'', ''fieldA'', ''fieldB'')');


UniQuery := TUniQuery.Create(nil);
try
UniQuery.Connection := UniConnection;
UniQuery.SQL.Text := 'SELECT * FROM TEST';
UniQuery.KeyFields := 'ID';
DataSetProvider := TDataSetProvider.Create(nil);
try
DataSetProvider.DataSet := UniQuery;
DataSetProvider.UpdateMode := upWhereChanged;
ClientDataSet := TClientDataSet.Create(nil);
try
ClientDataSet.ProviderName := 'DataSetProvider';
ClientDataSet.SetProvider(DataSetProvider);
ClientDataSet.Open;
Writeln('Blob Values Before Edit:');
Writeln(Format('Value of FieldA: %s%sValue of Fieldb: %s', [ClientDataSet.FieldByName('fieldA').AsString, #13#10, ClientDataSet.FieldByName('fieldB').AsString]));
ClientDataSet.Edit;
ClientDataSet.FieldByName('TXT').AsString := 'New text';
ClientDataSet.Post;
ClientDataSet.ApplyUpdates(-1);
Writeln('Blob Values After Edit:');
Writeln(Format('Value of FieldA: %s%sValue of Fieldb: %s', [ClientDataSet.FieldByName('fieldA').AsString, #13#10, ClientDataSet.FieldByName('fieldB').AsString]));
finally
ClientDataSet.Free;
end;
finally
DataSetProvider.Free;
end;
finally
UniQuery.Free;
end;
finally
UniConnection.Free;
Readln;
end;
end.


Thanks for your support
Raphael Vital
Top
AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: SQLite Blob Fields Problem

Post by AlexP » Wed 03 Dec 2014 13:17

When executing this code, data in a varchar field are saved correctly.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  Uni,
  SQLiteUniProvider,
  DB,
  DBClient,
  Provider;

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
  DataSetProvider: TDataSetProvider;
  ClientDataSet: TClientDataSet;

begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ProviderName := 'SQLite';
    UniConnection.ConnectString := 'Provider Name=SQLite;Data Source=:memory:;LoginPrompt=False';
    UniConnection.Connect;
    UniConnection.ExecSQL('CREATE TABLE TEST(ID INTEGER, TXT VARCHAR2(40), fieldA TEXT, fieldB TEXT, CONSTRAINT PK_TEST PRIMARY KEY(ID))');
    UniConnection.ExecSQL('INSERT INTO TEST VALUES(1, ''test'', ''fieldA'', ''fieldB'')');


    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.SQL.Text := 'SELECT * FROM TEST';
      UniQuery.KeyFields := 'ID';
      DataSetProvider := TDataSetProvider.Create(nil);
      try
        DataSetProvider.DataSet := UniQuery;
        DataSetProvider.UpdateMode := upWhereChanged;
        ClientDataSet := TClientDataSet.Create(nil);
        try
          ClientDataSet.ProviderName := 'DataSetProvider';
          ClientDataSet.SetProvider(DataSetProvider);
          ClientDataSet.Open;
          Writeln('Blob Values Before Edit:');
          Writeln(Format('Value of FieldA: %s%sValue of Fieldb: %s%sValue of TXT: %s', [ClientDataSet.FieldByName('fieldA').AsString, #13#10, ClientDataSet.FieldByName('fieldB').AsString, #13#10, ClientDataSet.FieldByName('TXT').AsString]));
          ClientDataSet.Edit;
          ClientDataSet.FieldByName('TXT').AsString := 'New text';
          ClientDataSet.Post;
          ClientDataSet.ApplyUpdates(-1);
          Writeln('Blob Values After Edit:');
          Writeln(Format('Value of FieldA: %s%sValue of Fieldb: %s%sValue of TXT: %s', [ClientDataSet.FieldByName('fieldA').AsString, #13#10, ClientDataSet.FieldByName('fieldB').AsString, #13#10, ClientDataSet.FieldByName('TXT').AsString]));
        finally
          ClientDataSet.Free;
        end;
      finally
        DataSetProvider.Free;
      end;
    finally
      UniQuery.Free;
    end;
  finally
  UniConnection.Free;
  Readln;
  end;
end.
Top
Raphael Vital
Posts: 4
Joined: Tue 25 Nov 2014 13:43

Re: SQLite Blob Fields Problem

Post by Raphael Vital » Wed 03 Dec 2014 13:57

Hi,

The question is: varchar data are saved in my system too but the contents of both blob fields are erased. This code perhaps not do this. I reviewed all my code and there's nothing i can see wrong to do that. And if are a bug, why ONLY this two blob fields? All the other data remains there.
I don't know why and I have no clue where I can search where the bug is. I've tried to remove all components and reset again and nothing happens.

There's a new data: in other form i have, with blob fields, the problem is the same, so, is not the set of components for "one" particulary table, is general.

In this other table, the componentes are setted in another way and using the same trio "clientdataset->dataseprovider->, uniquery".

It's funny, don't you think? Seems a little bug hidden in a small corner of code, but now I have one nervous breakdown. =)))


Thanks for all your support.

Raphael Vital
Top
AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: SQLite Blob Fields Problem

Post by AlexP » Thu 04 Dec 2014 05:44

Please send me your project (including the database file) to alexp*devart*com - and we will check this issue on your sample.
Top
Post Reply

Return to “SQLite Data Access Components”