[SOLVED] Problem with sqlite memory table
lazarus / 16-Databases

----------------------------------------------------------------------
rocka1 · 2012-07-07 19:06

Problem with sqlite memory table

The file has a table named test1 with some data.

When I open the app, for private reasons I want to load the file into
memory, dont want to access the file in the disk.
The file is only for read, not to write data.

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
var
 Ms2: TmemoryStream;
 //fStream: TFileStream;
begin
 ms2 := TMemoryStream.Create;
 ms2.LoadFromFile(ExtractFilePath(Application.ExeName)+'test.db');
end;  

Then I'd like to use that memory file to use with Tsqlite3, but no
solution ...

Can anyone help me with some code please?

Thanks

----------------------------------------------------------------------
eny · 2012-07-07 19:25

You'll have to create an in memory db via sqlite commands i.e. open a
database with name ':memory:'.
Then you can copy the contents of the disk db to the in mem database
and access it as a regular sqlite db.

----------------------------------------------------------------------
ludob · 2012-07-08 09:41

I have written a new sqlite3backup unit for fpc. You can find the
patch here: http://bugs.freepascal.org/view.php?id=22394
AFAIK, the patch can be applied to fpc 2.6.0.

To create a memory database from file you use the following code
(SQLite3Connection1 created before, on form or at runtime, and
closed):
Code: [Select]uses ...,sqlite3backup;
...
var
  bu:TSQLite3Backup;
begin
  bu:=TSQLite3Backup.Create;
  try
    SQLite3Connection1.DatabaseName:=':memory:';
    bu.Restore('your_database_on_file.sq3',SQLite3Connection1);
  finally
    bu.Destroy;
  end;
  ...

----------------------------------------------------------------------
Elmug · 2012-07-08 09:50

It seems to me that perhaps you have the file in a usb stick (your
security reasons), otherwise, if the file is in the hard-disk already,
then there is already the security problem you seem to want to avoid.

Therefore, if the file is external to the pc (usb stick) for instance,
that file could be indeed itself an SQLite3 database, and all you'd
have to do is ATTACH it, since SQLite3 can connect to more than one
database, by means of attach.

----------------------------------------------------------------------
rocka1 · 2012-07-08 15:41

Its a file with passwords, exported and encrypted in a aplication made
in Delphi.

This file later is to read in a app in Windows Mobile.

So thats why I want it in memory after decrypted.

I'll try the sugestions.

----------------------------------------------------------------------
rocka1 · 2012-07-10 14:45

Cant find any documentation in SQLITE3 about insert from file to
memory or from stream to memory, in pascal language.

I have to use Sqlite3, with no more components because its for use in
Wince, and later in Android.

Tried sqlite3backup, but when I compile give's me this error, and some
more... :
sqlite3backup.pas(97,25) Error: Identifier not found "psqlite3backup"

So, the catch is :

File "test.db" with table test1                               <- No
problem
File encrypted previuosly in Delphi.                       <- No
problem
File is used only for read and for locate records.   <- No problem
App in Lazarus, decrypt file "test.db"                    <- No
problem

Now, 2 possible cenarios, from  my point of view :

1 - Decrypt the file, to memory and will be used/read with
Sqlite3Dataset, then, 'Select * from test1 where ....'
2 - Decrypt the file to another physical file, load it in
Sqlite3Dataset in memory, and then delete the decrypted file from
disk.

The size of the file usually does not exceed 100 kb, and has 1 memo
field

I can't find any lines of code that can help me, only in C/C++ or
other languages .. 

Please, some lines of code ...  pascal ... 

Thank you so much for your time

----------------------------------------------------------------------
ludob · 2012-07-10 16:05

QuoteTried sqlite3backup, but when I compile give's me this error, and
some more... :
sqlite3backup.pas(97,25) Error: Identifier not found
"psqlite3backup"That is because you didn't apply the patch to
sqlite3.inc in sqlite3bu.diff. When applied you have also to rebuild
the compiler.

----------------------------------------------------------------------
ludob · 2012-07-10 17:14

I changed the sqlite3backup unit a little bit so that it doesn't
require a patched fpc. File attached. Downside is that the sqlite
library has to be initialised before creating a TSQLite3Backup. An
exception will be raised when sqlite isn't initialised. So the little
snippet I gave earlier becomes:
Code: [Select]uses ...,sqlite3backup;
...
var
  bu:TSQLite3Backup;
begin
  SQLite3Connection1.DatabaseName:=':memory:';
  SQLite3Connection1.Connected:=true;
  bu:=TSQLite3Backup.Create;
  try
    bu.Restore('your_database_on_file.sq3',SQLite3Connection1);
  finally
    bu.Destroy;
  end;
  ...

----------------------------------------------------------------------
rocka1 · 2012-07-10 21:04

Thank you ludob.

Working ... but only in Windows.

My tests in Windows Mobile gives me error:

Sqlite3_backup functions not found ...

Maybe incompatibility... But, thanks :-)

----------------------------------------------------------------------
ludob · 2012-07-11 07:49

Have you tried the sqlite3.dll for wince from http://www.lazarus.freep
ascal.org/index.php/topic,10301.msg95352.html#msg95352? When looking
at the dll with a hex editor the backup functions seem to be included.

----------------------------------------------------------------------
rocka1 · 2012-07-11 08:44

Thanks again ludob , my dll version is old.

Now working 100%

----------------------------------------------------------------------
franciscoluiz · 2012-07-19 11:57

vou tentar fazer o mesmo...

valeu rocka1