Recent

Author Topic: [SOLVED] Problem with sqlite memory table  (Read 12509 times)

rocka1

  • New Member
  • *
  • Posts: 21
[SOLVED] Problem with sqlite memory table
« on: July 07, 2012, 09:06:46 pm »
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
« Last Edit: July 11, 2012, 10:58:49 am by rocka1 »

eny

  • Hero Member
  • *****
  • Posts: 1587
Re: Problem with sqlite memory table
« Reply #1 on: July 07, 2012, 09:25:59 pm »
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.
All posts based on: Win10 (Win64); Lazarus 1.8.0 'stable' (#56594 win64) unless specified otherwise...

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Problem with sqlite memory table
« Reply #2 on: July 08, 2012, 11:41:48 am »
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

  • Hero Member
  • *****
  • Posts: 849
Re: Problem with sqlite memory table
« Reply #3 on: July 08, 2012, 11:50:30 am »
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

  • New Member
  • *
  • Posts: 21
Re: Problem with sqlite memory table
« Reply #4 on: July 08, 2012, 05:41:47 pm »
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

  • New Member
  • *
  • Posts: 21
Re: Problem with sqlite memory table
« Reply #5 on: July 10, 2012, 04:45:58 pm »
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

  • Hero Member
  • *****
  • Posts: 1173
Re: Problem with sqlite memory table
« Reply #6 on: July 10, 2012, 06:05:29 pm »
Quote
Tried 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

  • Hero Member
  • *****
  • Posts: 1173
Re: Problem with sqlite memory table
« Reply #7 on: July 10, 2012, 07:14:47 pm »
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

  • New Member
  • *
  • Posts: 21
Re: Problem with sqlite memory table
« Reply #8 on: July 10, 2012, 11:04:51 pm »
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 :-)
« Last Edit: July 10, 2012, 11:40:26 pm by rocka1 »

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Problem with sqlite memory table
« Reply #9 on: July 11, 2012, 09:49:07 am »
Have you tried the sqlite3.dll for wince from http://www.lazarus.freepascal.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

  • New Member
  • *
  • Posts: 21
Re: Problem with sqlite memory table
« Reply #10 on: July 11, 2012, 10:44:17 am »
Thanks again ludob , my dll version is old.

Now working 100%  :D

franciscoluiz

  • New Member
  • *
  • Posts: 24
Re: [SOLVED] Problem with sqlite memory table
« Reply #11 on: July 19, 2012, 01:57:43 pm »
vou tentar fazer o mesmo...

valeu rocka1