mORMot Open Source

longge007

Member

Registered: 2010-06-22

Posts: 107

last problem,how to use Tstream Data in SQLite3 Framework

I  notice that Author have add some new funtions about handling blob data.like retriewblob()[get blob content] and updateblob() [updateblobcontent]
look at my codes
Table  use Example sampleRecord

type
TSQLSampleRecord = class(TSQLRecord)
  private
    fQuestion: RawUTF8;
    fName: RawUTF8;
    fTime: TDateTime;
    fWave: TSQLRawBlob;
  published
    property Time: TDateTime read fTime write fTime;
    property Name: RawUTF8 read fName write fName;
    property Question: RawUTF8 read fQuestion write fQuestion;
    property Wave: TSQLRawBlob read fWave write fWave;
  end;

.................
add procedure

procedure TForm1.AddButtonClick(Sender: TObject);
var Rec: TSQLSampleRecord;
    AWave:array  of single;
    tempWave:TSQLRawBlob;
    i,n:integer;
    Tempstream:tHeapMemorystream;
begin
  n:=15;
  setLength(AWave,n);
  for I := 0 to n - 1 do
    AWave[i]:=Intpower(-1,i)*100*Random;
  Rec := TSQLSampleRecord.Create;
  try
    Rec.Time := Now;
    // we use explicit StringToUTF8() for conversion below
    // a real application should use TLanguageFile.StringToUTF8() in SQLite3i18n
    Rec.Name := StringToUTF8(NameEdit.Text);
    Rec.Question := StringToUTF8(QuestionMemo.Text);

    //SetString(TempWave,PAnsiChar(pointer(AWave)),length(AWave)*sizeof(single));
    //Rec.Wave:=TempWave;
      Tempstream:=THeapMemoryStream.Create;
      Tempstream.Write(AWave[0], (High(aWave) + 1) * SizeOf(Single));
    if Database.Add(Rec,true)=0 then
      ShowMessage('Error adding the data') else begin
      NameEdit.Text := '';
      QuestionMemo.Text := '';
      NameEdit.SetFocus;
      if rec.ID<>0 then
      Label4.Caption:=inttostr(rec.ID);
      CurrentID:=rec.ID;
      Database.UpdateBlob(TSQLSamplerecord,rec.ID,StringToUTF8('fWAVE'),TempStream);
    end;
  finally
    Rec.Free;
    tempstream.Free;
    AWave:=nil;
  end;
end;

Find procedure

procedure TForm1.FindButtonClick(Sender: TObject);
var Rec: TSQLSampleRecord;
  tempWave:TSQLRawBlob;
begin
  Rec := TSQLSampleRecord.Create(Database,'Name="%"',[StringToUTF8(NameEdit.Text)]);
  try
    if Rec.ID=0 then
      QuestionMemo.Text := 'Not found' else
      QuestionMemo.Text := UTF8ToString(Rec.Question);
      //TempWave:=Rec.Wave;
  Database.RetrieveBlob(TSQLSampleRecord,rec.ID,StringToUTF8('fWAVE'),tempwave);
     showMessage(inttostr(Length(TSQLRawBlobToBlob(TempWave))));
  finally
    Rec.Free;
  end;
end;

result:   Length =0?

and  Blobfield name  is  fWave or Wave?when I used Wave ,an error occured.if i used Fwave, No error occured. enenthougth there is no Blob data updated successfully.

Last edited by longge007 (2010-07-05 02:46:32)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,228

Website

Re: last problem,how to use Tstream Data in SQLite3 Framework

The property name is "Wave" not "fWave"....

Note: if you just put the property name, you can put 'Wave' in the Delphi code, the StringToUTF8() is not necessary, since Delphi is able to make all conversion from a string containing 7bits-Ascii-only characters to RawUTF8 without any problem.

All blob-related methods are functions which return a boolean. You code should check it returned TRUE, i.e. the method was successful.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,228

Website

Re: last problem,how to use Tstream Data in SQLite3 Framework

you could create an index for this field values: your request will be much faster. Use the CreateSQLIndex() method.

longge007

Member

Registered: 2010-06-22

Posts: 107

Re: last problem,how to use Tstream Data in SQLite3 Framework

sir, are you here, i want to send you one email including my samplecodes.
when add the message, an eror will occure

First chance exception at $75519617. Exception class EAccessViolation with message 'Access violation at address 00405184 in module 'Project04Client.exe'. Read of address 32C7FFF8'. Process Project04Client.exe (3996)

if you have time ,you can use my code to compile 04 httpclient and httpServer.

   ......
   if CurrentID>0 then
   Database.UpdateBlob(TSQLSampleRecord,CurrentID,stringtoUTF8('Wave'),tempStream);

i don't know what's the matter ? it can't update the blob field "wave" in my Sample, can you write one example about blob field like my saveing Wave data or other pic file,and so. thanks a lot!!!
and  you mentioned "CreateSQLIndex()" ,  i realized in HttpServer project ,  whether o not is right?

  Model := CreateSampleModel;
  DB := TSQLRestServerDB.Create(Model,ChangeFileExt(paramstr(0),'.db3'));
  DB.CreateMissingTables(0);
  DB.CreateSQLIndex(TSQLSampleRecord,'Wave',true);
  Server := TSQLite3HttpServer.Create('8080',DB);

Last edited by longge007 (2010-07-05 09:19:00)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,228

Website

Re: last problem,how to use Tstream Data in SQLite3 Framework

1. As I wrote above, you can sumup stringtoUTF8('Wave') by just 'Wave' - that's fine and more easy to write/read;

2. There was some bugs in RetrieveBlob/UpdateBlob methods, it's now corrected in our source code repository;

3. I've added unitary tests dedicated for RetrieveBlob/UpdateBlob methods;

4. About CreateSQLIndex() the best way according to the framework is to do it like in the code below:

type
TSQLSampleRecord = class(TSQLRecord)
  private
    fQuestion: RawUTF8;
    fName: RawUTF8;
    fTime: TDateTime;
    fWave: TSQLRawBlob;
  published
    property Time: TDateTime read fTime write fTime;
    property Name: RawUTF8 read fName write fName; stored false; // made UNIQUE
    property Question: RawUTF8 read fQuestion write fQuestion;
    property Wave: TSQLRawBlob read fWave write fWave;
  end;

The only modification is to add "stored false;" after the property declaration, so that this field will be made unique to the database, i.e. only ONE name value will be allowed. This is a SQLite3 feature, which performs automatically:
- ensure Name is unique, i.e. raise an error if a duplicated Name is updated or added;
- create an Index on Name property, for fast retrieval of the Name.

This is more elegant than calling CreateSQLIndex(), according to your application needs: it looks clearly that you DO need UNIQUE name field values, because you identify your record according to the name value.

longge007

Member

Registered: 2010-06-22

Posts: 107

Re: last problem,how to use Tstream Data in SQLite3 Framework

thanks your quick reply ,now everything is good, ^_^。i can do more in my project with SQLite3 Framework.

one semicolon is deleted.

property Name: RawUTF8 read fName write fName stored false; // made UNIQUE

Last edited by longge007 (2010-07-06 02:50:22)