Actually now that I'm looking into this more deeply, turns out I'm already doing exactly that. At least on OS X. There I can't get that :memory: to work with ?cache=shared, so it always uses a file in the end. I get this with your latest code:

$ ls -l file*
ls: file*: No such file or directory
$ ./test.pl > /dev/null
$ ls -l file*
-rw-r--r--  1  19456 Jul 24 22:46 file:memdb2?mode=memory&cache=shared
$ ./test3.pl > /dev/null
$ ls -l file*
-rw-r--r--  1  36864 Jul 24 22:46 file:memdb2?mode=memory&cache=shared
[download]

When I use 'dbi:SQLite:dbname=:memory:' alone, it creates the database in-memory (no file), but I can't share it so that's no use.

I've also tried all of these different URIs, but no joy (below are the corresponding files that get created):

CONNECT=> 'dbi:SQLite:dbname=file:memory:?mode=memory&cache=shared'
19456 Jul 24 23:15 file:memory:?mode=memory&cache=shared

CONNECT=> 'dbi:SQLite:dbname=:memory:?cache=shared'
19456 Jul 24 23:18 :memory:?cache=shared

CONNECT=> 'dbi:SQLite:dbname=:memory:\?cache=shared'
19456 Jul 24 23:19 :memory:\?cache=shared

CONNECT=> 'dbi:SQLite:dbname=:memory:cache=shared'
19456 Jul 24 23:20 :memory:cache=shared

CONNECT=> 'dbi:SQLite:dbname=:memory?cache=shared:'
19456 Jul 24 23:21 :memory?cache=shared:
[download]

So far when I use the file it's fast enough for what I need. But just out of curiosity, any chance that ?cache=share can be set outside of the URI in some way?

There I can't get that :memory: to work with ?cache=shared, so it always uses a file in the end.

You need to read the sqlite docs I linked above :)

It is:

• either: 'dbi:SQLite:dbname=:memory:' which creates a memory db; but cannot be shared between connections; thus, with the DBI restrictions, cannot be shared between threads under Perl.
• or: 'dbi:SQLite:dbname=file:filename?mode=memory&cache=shared' which (should) also create a memory db, but one that can be shared between distinct connection.

  (But that has this anomalous problem with the undeleted file. Leastwise, under Windows.)

I get this with your latest code:-rw-r--r--  1  19456 Jul 24 22:46 file:memdb2?mode=memory&cache=shared

That's interesting. And it may explain the problem under windows.

I was expecting that the uri syntax 'dbi:SQLite:dbname=memdb2?...' would create a file called (just) memdb2.

Ie. That the file: bit was just the URI scheme (equiv. to http:). But it seems that it is being taken as a part of the filename to be used for the db.

Which explains the problem with Windows in as much as Windows uses the syntax filename:streamname to access its Alternate File Streams.

I've also tried all of these different URIs, ...

In order to use your existing file from multiple threads (via multipe connects) you do need to use the URI-form of the connect.

Say your existing file is in the current directory, and is called 'mysqlite.db'; then (I think) you should use the connect string:

    CONNECT=> 'dbi:SQLite:dbname=file:mysqlite.db?cache=shared'
[download]

If it is in some other place then:

    CONNECT=> 'dbi:SQLite:dbname=file:/path/to/mysqlite.db?cache=share
+d'
[download]

I can't verify this at the moment, so see how you get on with that. (And read the docs to see if you agree with me.)

---

With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'

Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.

"Science is about questioning the status quo. Questioning authority".

In the absence of evidence, opinion is indistinguishable from prejudice.

Actually, those URI question mark query parameters don't seem to be getting across - they're just considered part of the filename for whatever reason:

    CONNECT=> 'dbi:SQLite:dbname=file:mysqlite.db?cache=shared'
19456 Jul 25 00:32 file:mysqlite.db?cache=shared

    CONNECT=> 'dbi:SQLite:dbname=mysqlite.db?cache=shared'
19456 Jul 25 00:34 mysqlite.db?cache=shared
[download]

I'm getting exactly the same results on OS X 10.8 and Ubuntu 13.04. I even tried updating DBI and DBD::SQLite to the latest versions, but with no effect. I'm using the exact code you posted today and I'm just changing the CONNECT constant as shown above.

I must be doing something very stupid I just can't find what that is :-(