Topic: Thanks for your components

I tried your components and think they could be very usefull even if they need some improvements....
Waiting for the next version ! wink

Re: Thanks for your components

Thanks for this family support !

Re: Thanks for your components

BLOB's with SQLite

Hi, together,
first off all thanks for your Work with SQLitePass !!:)

I'm -yet- not very experienced with SQlite,
but nevertheless i think it is exactly what I'm looking for.

The Idea:
Storing binary Data ( (smaller) Documents, Tables and Grafiks, Icons,etc... )
in a SQLite Database..
With Text , Numbers etc. it is not a Problem.
But I've got no Idea how to do that with Binary Data,
usin SQLite Pass..
Id'd like to create a Table which keeps an ID (KEY),  a String (for the File Extension)
and a BLOB Field where the Document ( Picture, Icon, Table..) is stored as pure Binary Data
So, it would be possible, to recreate the Document by reading the Information
out of a Table, into a Buffer and then storing this -using the stored File Extension-
as a "recreated" Document..
So the Plan.....
I tried it using large Strings, but that did not work (i didnt' really expect that) tongue
probably it is just a few Lines of code,
but i've got no Idea...
roll

Has anybody got Experience how this can be done using
SQLitePass...
I'm (still) working with Delphi 5 lol
but i think the main Concept should not differ too much between
several Versions -not even using Free Pascal-
I can picture lots of use-cases where something like that coul be very helpful.

Wolfy

Re: Thanks for your components

Hi Wolfy,

The good news is : Yes SqlitePass handles blobs. (Images, Memo, ...etc)
The bad news is : In current version (0.41), when using images, This only works with Delphi and bmp.

FPC and Lazarus handles Memo but I still have problems with the Lazarus TdbImage that doesn't display images yet. This fix is planned for futur releases but I don't think it will be ready for the next 0.42 as I have to post questions on lazarus forum...I need help too...

Anyway, for xp and Delphi

Dowload and install the demo program from my site
Run and open the demo database ...SQLitePass\Demo\Databases\ExpertPersonal_dbdemos.db3
Select the Table Biolife.
You should be able to display Fishes bmp and Memo, Clear, Import, Export it...etc

Here is the code used in demo program (It uses a classic TDbImage component) :

procedure TMainForm.BtLoadPictureFromFileClick(Sender: TObject);
begin
if OpenPictureDialog.Execute
  then begin
       Dataset.Edit;
       TBlobField(DbImage.Field).LoadFromFile(OpenPictureDialog.Filename);
       end;
end;

procedure TMainForm.BtClearPictureClick(Sender: TObject);
begin
Dataset.Edit;
DbImage.Field.Clear;
end;

procedure TMainForm.BtSavePictureToFileClick(Sender: TObject);
begin
If SavePictureDialog.Execute
  then TBlobField(DbImage.Field).SaveToFile(SavePictureDialog.Filename);
end;

Using classic BlobField should be ok with other kinds of large data.

I know documentation is not very usefull for now but the components are having to many changes rigth now.

Let me know if you still have problem so others users could share our experience.

Regards.

Luc

Re: Thanks for your components

Hi, well, thats nice ,

but i did not want to know how to load a Picture
into a DB image vias a Open Picture Dialog...
thats trivial...:)

Im interested in the Part where The Image ist stored into the Database..
something like -> INSERT INTO Mytable ( Values TextData, BLOBData) ???
This is the point that im looking for.
In some Documentations i read about "Prepare Statement" and
Queries like
INSERT INTO Mytable (TextData, BLOBData) VALUES (?, ?);
but i still have got no idea how to get it working..
Here a Snipnet of C-Code that ( so it pretends ) should solve it all..
Thats exactly what I'm searchin for !!
But it did not help me any further as i wasn't able yet to translate it to Pascal..
Maybe I'm too stupid..:mad::(

static int writeBlob(
  sqlite3 *db,                   /* Database to insert data into */
  const char *zKey,              /* Null-terminated key string */
  const unsigned char *zBlob,    /* Pointer to blob of data */
  int nBlob                      /* Length of data pointed to by zBlob */
){
  const char *zSql = "INSERT INTO blobs(key, value) VALUES(?, ?)";
  sqlite3_stmt *pStmt;
  int rc;

  do {
    /* Compile the INSERT statement into a virtual machine. */
    rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
    if( rc!=SQLITE_OK ){
      return rc;
    }

    /* Bind the key and value data for the new table entry to SQL variables
    ** (the ? characters in the sql statement) in the compiled INSERT
    ** statement.
    **
    ** NOTE: variables are numbered from left to right from 1 upwards.
    ** Passing 0 as the second parameter of an sqlite3_bind_XXX() function
    ** is an error.
    */
    sqlite3_bind_text(pStmt, 1, zKey, -1, SQLITE_STATIC);
    sqlite3_bind_blob(pStmt, 2, zBlob, nBlob, SQLITE_STATIC);

    /* Call sqlite3_step() to run the virtual machine. Since the SQL being
    ** executed is not a SELECT statement, we assume no data will be returned.
    */
    rc = sqlite3_step(pStmt);
    assert( rc!=SQLITE_ROW );

    /* Finalize the virtual machine. This releases all memory and other
    ** resources allocated by the sqlite3_prepare() call above.
    */
    rc = sqlite3_finalize(pStmt);

    /* If sqlite3_finalize() returned SQLITE_SCHEMA, then try to execute
    ** the statement again.
    */
  } while( rc==SQLITE_SCHEMA );

  return rc;
}

Here's where i found The (whole) Example,
after a long search...

http://www.sqlite.org/cvstrac/wiki?p=BlobExample   

But I was not able yet to translate it to Pascal...
Somebody around who's tough enough to handle it ?
would be cool

Wolfy

Re: Thanks for your components

Hi Wolfy,

You don't really need this 'C' code since SqlitePass Dataset can do the job for you. It is as trivial as loading a picture via a DbImage... smile Just joking.

I understood you wanted to embed any kind of file into a Db and then be able to save them back to another location.

If this is your goal, I wrote a little demo program for this purpose. 
Download here
The source is included and the database is already filled with a txt, a pdf, a bmp, the latest SqliteToolbox.exe (Not officially available...) and his database...

The complete unit is :

unit BlobForm;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  DBCtrls, Grids, DBGrids, Db, ExtCtrls, SqlitePassDbo, Buttons;

type
  TForm1 = class(TForm)
    SqlitePassDatabase1: TSqlitePassDatabase;
    Dataset: TSqlitePassDataset;
    Panel1: TPanel;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    SbLoadBlob: TSpeedButton;
    SbSaveBlob: TSpeedButton;
    DatasetFileId: TLargeintField;
    DatasetFileLocation: TStringField;
    DatasetBlobData: TBlobField;
    OpenDialog: TOpenDialog;
    SaveDialog: TSaveDialog;
    procedure SbLoadBlobClick(Sender: TObject);
    procedure SbSaveBlobClick(Sender: TObject);
  private
    { Déclarations privées }
  public
    { Déclarations publiques }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.SbLoadBlobClick(Sender: TObject);
begin
if OpenDialog.Execute
  then begin
       Dataset.Edit;
       DatasetFileLocation.Value := OpenDialog.FileName;
       DatasetBlobData.LoadFromFile(OpenDialog.Filename);
       Dataset.Post;
       end;
end;

procedure TForm1.SbSaveBlobClick(Sender: TObject);
begin
if SaveDialog.Execute
  then DatasetBlobData.SaveToFile(SaveDialog.Filename);
end;

end.

The Db was created with SqliteToolbox :

CREATE TABLE "BlobTable"
(
"FileId" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
"FileLocation" STRING(250),
"BlobData" BLOB
)

I hope this will help you.
Have fun

Luc big_smile

Re: Thanks for your components

Hi, Luc !
cool cool cool
big_smile

I tried out the BLOB- Demo and thats exactly what I needed !!
Now I'm looking forward to the weekend, to
continue...I've already seen what i wanted..
so i think i'll get along now..

This small demo and the source ist really very helpful !!
(i always like these small demos, as ist easier to
look through for more the stupid ones smile like me) roll 

Yes, it seems to be easy -if one knows how to do it-

Thank you so very much..
specially for your "Turbo-Fast" help...

Wolfy

Re: Thanks for your components

Hi,
so, meanwhile, everything works...
But I want -as a Payback- show how i finally solved it..
maybe it is helpfull for others... smile
The problem was, that i did not want to
pre-define te Fields at Design Time...

Here is a small snippet...
Thats another way how it works
and the Fields can be created at runtime.. wink

.....

// Remark:  The Table has already been created at an earlier Step..
// something lke:
// 'CREATE TABLE BLOB_Data ( Name VARCHAR(255), Typ VARCHAR(255), Datasize Integer, Data BLOB ); ';
// DB1 -> your Database
// DS1 -> your Dataset
procedure TForm1.SpeedButton4Click(Sender: TObject);
Var
  blob     : TStream;
  fs         : TFileStream;
  fsz       : Integer;
  FN        : String;
  Ext       : String;
  FName    : String;
  Text1,
  Text2,
  Value1    : TField;
begin
  If not DB1.Connected then exit;
  Opendialog2.InitialDir := OutDir;
  If Opendialog2.Execute then begin
    Fname := Opendialog2.FileName;
    Ext      := ExtractFileExt ( FName );
    FN      := ExtractFileName ( FName );
    Delete ( FN, Pos ( Ext, FN ), Length ( Ext ));
    DS1.Active       := False; // needed to set DB
    DS1.Database   := DB1; 
    DS1.Active       := True;
// just some Text Fields and an Integer...
    Text1     := DS1.FieldByName ('Name');
    Text2     := DS1.FieldByName ('Type');
    Value1   := DS1.FieldByName ('Datasize');
    DS1.Insert;
    DS1.Edit;
    Blob   := DS1.CreateBlobStream ( DS1.FieldByname ('Data'),bmWrite );
    Try
//      Application.ProcessMessages;  // not really needed i use it for own purposes
      blob.Seek(0, soFromBeginning);  // important !
      fs    := TFileStream.Create ( Fname, fmOpenRead or fmShareDenyWrite );
      fsz  := fs.Size;
      try
        blob.CopyFrom ( fs, fsz);
      finally
        fs.Free;
      end;
    Finally
      Blob.Free;
    end;
    Text1.AsString      := FN;
    Text2.AsString      := Ext;
    Value1.AsInteger  := fsz;
    DS1.Post;
  end
  else Exit;
end;
....
Thats it..

Maybe this Post does not really fit in this
Thread, (->"Thanks for your components")
-anyways the thanks are ment as said-
so Luc, feel free to place its somewere else..
I didnt know how...

Wolfy
...nice Easter Eggs...
cool lol

Re: Thanks for your components

Thank you Wolfy,

I will probably move your post to another topic.

We will take care of the easter eggs today...

Regards