title: How to share SQLite in-memory database between two connections in RSQLite in R?
body: <p>I'm trying to test a way of sharing large datasets between .NET and R. My current idea is to</p>
  
  <ol>
  <li>Open a SQLite database connection from .NET;</li>
  <li>Write all the data I generated in .NET to this database; </li>
  <li>Run R in the same process using REngine from <a href="https://rdotnet.codeplex.com/" rel="nofollow">R.NET</a> and connect to the opened SQLite connection using RSQLite.</li>
  </ol>
  
  <p>I already have a prototype working by writing to SQLite database in a temporary file located on the hardisk. It takes about 15 minutes to transfer 50 mil of rows each having 6 columns from .NET to R. However, I want to improve the performance possibly by using an in-memory or temporary SQLite database. I tried starting a SQLite in-memory database in .NET using the following connection string:
  <code>FullUri=file::memory:?cache=shared</code>.
  Then R is started in the same process. The following code is executed in R.NET's REngine in a attempt to connect to the same in-memory database. However, I then realized that it is a new in-memory database and the table I originally saved using .NET is not available in this new connection. So the question I'm asking is whether I can connect to a shared in-memory SQLite database with "cache=shared" using RSQLite as mentioned in the <a href="http://www.sqlite.org/inmemorydb.html" rel="nofollow">documentation of SQLite</a>?</p>
  
  <pre><code>library(RSQLite)
  sqlite = SQLite(shared.cache=TRUE)
  conn = dbConnect(sqlite, dbname = ':memory:')
  dbListTables(conn)
  </code></pre>
  
  <p>To add on:
  I played around with the following code in a standalone R instance and I found that even using the desktop R, I cannot start two connections sharing the same in-memory database. For example, the following conn2 does not have any table in it.</p>
  
  <pre><code>library(RSQLite)
  sqlite = SQLite(shared.cache=TRUE)
  conn = dbConnect(sqlite, dbname = ':memory:')
  dbWriteTable(conn, name="Test", test)
  dbListTables(conn)
  conn2 = dbConnect(sqlite, ':memory:')
  dbListTables(conn2)
  </code></pre>