Home → Help and guidance →

Impossible to use SQLite in memory

Michael

20 May, 2010 01:44 PM via web

Admin Functions

• Edit
• Remove

Hello all,

I'm using FluentNHibernate under VS2010, and thus developping with the .NET 4 framework.

When I use SQLite with a file, I have no problems at all. But when I switch for an in memory database (for unit tests), I have some weird problems. I recreate the database before each test, and so I use the ExposeConfiguration function and inside call :

new SchemaExport(cfg).Create(true, true);

The database is successfully created (I can see that thanks to the ShowSql function), as well the tables, but whenever I want to add an entity in the database, I have the following error:

System.Data.SQLite.SQLiteException: SQLite error
no such table: Dummy

And again, if I switch from InMemory to UsingFile, all works perfectly well.

Is there something that must be done?

Thanks in advance

1. Support Staff 2 Posted by James Gregory on 20 May, 2010 01:58 PM

   

   An in memory database is destroyed when the session is closed, are you
   closing your session?

   Admin Functions

   • Edit
   • Remove

2. 3 Posted by Michael on 20 May, 2010 02:09 PM

   

   No, I'm not. Here is the code I use:

   public class Dummy : BaseEntity<int, Dummy>
   {
       public Dummy()
       {
       }
   
       public Dummy(string foo)
       {
           Foo = foo;
       }
   
       public virtual int Id { get; private set; }
       public virtual string Foo { get; set; }
   }
   
   public class DummyMap : ClassMap<Dummy>
   {
       public DummyMap()
       {
           Id(d => d.Id)
               .GeneratedBy.Increment();
   
           Map(d => d.Foo)
               .Not.Nullable();
       }
   }
   
   public class Helper : IDisposable
   {
       private readonly ISessionFactory sessionFactory;
       private readonly ISession session;
   
       public Helper()
       {
           sessionFactory = CreateSessionFactory();
   
           session = sessionFactory.OpenSession();
   
           using (ITransaction transaction = session.BeginTransaction())
           {
               session.Save(new Dummy("Foo 1"));
               session.Save(new Dummy("Foo 2"));
               session.Save(new Dummy("Foo 3"));
   
               transaction.Commit();
           }
       }
   
       public ISession Session { get { return session; } }
   
       public void Dispose()
       {
           session.Dispose();
           sessionFactory.Dispose();
       }
   
       private ISessionFactory CreateSessionFactory()
       {
           return Fluently.Configure()
               .Database(SQLiteConfiguration.Standard.ShowSql().InMemory)
               .Mappings(m =>
                         m.FluentMappings.AddFromAssemblyOf<Dummy>())
               .ExposeConfiguration(cfg => new SchemaExport(cfg).Create(true, true))
               .BuildSessionFactory();
       }
   }
   
   public class RepositoryTests
   {
       [Fact]
       public void Count_Returns_CorrectNumberOfEntities()
       {
           using (var helper = new Helper())
           {
               var repository = new Repository<Dummy, int>(helper.Session);
               Assert.Equal(3, repository.Count());
           }
       }
   }
   

   And here is the complete callstack I have with xUnit:

   System.Data.SQLite.SQLiteException: SQLite error
   no such table: Dummy
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, ref String strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.Id.IncrementGenerator.GetNext(ISessionImplementor session)
   NHibernate.Exceptions.GenericADOException: could not fetch initial value for increment generator[SQL: SQL not available]
   at NHibernate.Id.IncrementGenerator.GetNext(ISessionImplementor session)
   at NHibernate.Id.IncrementGenerator.Generate(ISessionImplementor session, Object obj)
   at NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event)
   at NHibernate.Impl.SessionImpl.FireSave(SaveOrUpdateEvent event)
   at NHibernate.Impl.SessionImpl.Save(Object obj)
   at Emidee.NHibernate.Tests.Helper..ctor() in RepositoryTests.cs: line 56
   at Emidee.NHibernate.Tests.RepositoryTests.Count_Returns_CorrectNumberOfEntities() in RepositoryTests.cs: line 104

   I hope this helps :)

   Admin Functions

   • Edit
   • Split
   • Remove

3. 4 Posted by Andy on 21 May, 2010 02:16 PM

   

   Use this as your connection string (don't bother with the other fluent inmemory parts, just use ConnectionString): Data Source=:memory:;Version=3;New=True;Pooling=True;Max Pool Size=1;

   Then, add this call after the connection string:
   Raw("connection.release_mode", "on_close")

   That tells NHibernate to keep your session open until you explicitly close it. Otherwise it closes it because it knows reopening one will be fast due to connection pooling. But it still closes the connection, thus losing your DB.

   We're doing this fine with VS2008 / .Net 3.5.

   Andy

   Admin Functions

   • Edit
   • Split
   • Remove

4. 5 Posted by Michael on 25 May, 2010 12:25 PM

   

   Thanks for your answer, but unfortunately it doesn't change anything.

   Admin Functions

   • Edit
   • Split
   • Remove

5. Support Staff 6 Posted by Paul Batum on 05 Jun, 2010 10:42 AM

   

   Whenever I've wanted to use an in memory db, I've used an approach along the
   same lines as this post:
   http://www.tigraine.at/2009/05/29/fluent-nhibernate-gotchas-when-testing-with-an-in-memory-database/

   Maybe there's an easier way? I haven't found one yet.

   Admin Functions

   • Edit
   • Remove