SQLite Forum

Mono.Data.Sqlite In-Memory Shared Database
Login

Mono.Data.Sqlite In-Memory Shared Database

(1) By anonymous on 2020-05-13 14:34:41 [link] [source]

Hello all, I apologize for having to post and take up your time with this question, but I am at my wits end. I am attempting to create an in-memory shared database with Mono.Data.Sqlite and it just isn't working.

I've followed all the guidelines here: https://www.sqlite.org/inmemorydb.html to no avail.

I've tried: connectionString = "Data Source=InMemorySample88;Mode=Memory;Cache=Shared";

and

"ATTACH DATABASE URI=file:memdb1?mode=memory&cache=shared AS aux1;";

No matter what I do the database is getting created on the local hard drive and not in memory.

Can anyone tell me the proper connection string to use?

Thank you so much, John

(2) By Larry Brasfield (LarryBrasfield) on 2020-05-13 14:52:18 in reply to 1 [source]

The doc you cite as one whose guidelines you followed says that in-memory databases are named ":memory:" (without the quotes.) Have you tried that?

(3) By anonymous on 2020-05-13 15:02:40 in reply to 2 [link] [source]

Hi Larry, Thanks for the reply. Yes I've tried the exact string on that page,

example: "ATTACH DATABASE 'file::memory:?cache=shared' AS aux1";

When I do so I get the error "unable to open database file"

I've also tried it with URI=File thinking that may be required, but get the same result.

I will say the only thing that HAS worked is creating a temporary db in memory using:

"ATTACH DATABASE '' AS 'aux_test_temp'";

But the problem with that is it cannot be accessed by other connections. I need a shared database.

(4) By Larry Brasfield (LarryBrasfield) on 2020-05-13 15:07:53 in reply to 1 [link] [source]

I should also mention that the examples in the cited doc page work. You should try spelling the options just as shown instead of title-casing them.

(5) By anonymous on 2020-05-13 15:12:26 in reply to 4 [link] [source]

Hi Larry, Are you also using Mono? I understand there are several different .Net frameworks such as System.Data.Sqlite, Micorosft.Data.Sqlite, and Mono.Data.Sqlite.

I happen to be using Mono and from what I can tell not everything is implemented or implemented the same across all these frameworks.

If you have Mono and are saying that this is just user error, that would at least give me some glimmer of hope that I can get this working.

Thanks again for trying to help!

(6) By Keith Medcalf (kmedcalf) on 2020-05-13 18:36:22 in reply to 1 [link] [source]

I would suspect that the problem is that you do not have URI filenames enabled.

If you try to connect to a file called 'file:hello.db' what happens?

If a file called 'hello.db' is created then URI filenames are enabled. If anything else happens then URI filenames are not enabled.

Similarly try to connect to a file called 'file::memory:' and then write to it. If a file is created in local storage or an error occurs then URI filenames are not enabled.

(7.1) By Larry Brasfield (LarryBrasfield) on 2020-05-14 01:42:21 edited from 7.0 in reply to 5 [link] [source]

I'm away from my Mono-running machine for a few days, so did not try that. I doubt it matters because the same implemented-in-C code is reached regardless of whether Mono or some other .Net platform adapter layer is used, or just the C API is directly called. While it may be true that some implementation differences exist in .Net on its various platforms, I highly doubt that this is affecting anything so fundamental as passing parameters from your code, through the adapter layer, to the actual SQLite3 C API that handles the DB open.

I tried using the sqlite3.exe shell, but since that is distributed without SQLITE_USE_URI defined, I used a locally built one where it is defined. That, of course, makes it harder to see the effect of independent "connections" to the same, shared-memory DB.

So, to show that a shared memory DB works as documented, I wrote and ran this little Perl program, named shmem.pl:

use DBI;
use DBD::SQLite;
use autodie;

my $dbh1 = DBI->connect("dbi:SQLite:uri=file:somemem?mode=memory&cache=shared", '', '',
                       { AutoCommit => 1, RaiseError => 1 });

my $dbh2 = DBI->connect("dbi:SQLite:uri=file:somemem?mode=memory&cache=shared", '', '',
                       { AutoCommit => 1, RaiseError => 1 });

$dbh1->do("create table Furd(name text)");

$dbh1->do("insert into Furd values ('Bird')");
$dbh1->do("insert into Furd values ('Burp')");

my $sth = $dbh2->prepare("select * from Furd");

my $rv = $sth->execute() or die $DBI::errstr;

if($rv < 0) {
   print $DBI::errstr;
}

while(my $row = $sth->fetch) {
      print $row->[0] . "\n";
}

$dbh1->disconnect();
$dbh2->disconnect();

which produced the following output:

> perl shmem.pl
Bird
Burp

>

As you can see, the table with values created via $dbh1 was read via $dbh2.

I encourage you to investigate your calls rather than .Net platform differences.

Good luck!

(Edited to add:) None of this contravene's Keith's advice. If the SQLite library you are using was not compiled with SQLITE_USE_URI defined, you will need to study and carefully heed Uniform Resource Identifiers section 2.

(8) By Larry Brasfield (LarryBrasfield) on 2020-05-14 02:02:25 in reply to 3 [link] [source]

I trust you mean, "the problem with that is it cannot be accessed by other connections made within the same process." If you are hoping for an in-memory DB to be shared across processes, that is not going to happen without some more work on your part than just following the API doc hints. You would need to customize the VFS that implements the :memory: store to do cross-process memory sharing; a feat which varies markedly among platforms. Getting that to work through the SQLite.Net adapter would be yet another challenge.