Performing a lot of queries fails (cross platform)

Hi,
I'm having a problem with database access on xamarin, testing on iOS but I believe it applies cross platform.

It is very similar to the below post:
http://stackoverflow.com/questions/18056166/performing-a-lot-of-sqlite-queries-in-xamarin-android-fails

I have worked through every possible scenario over the past few days to address this, in essence the code is :-

using (SqliteConnection connection = new SqliteConnection ("Data Source=" + pathToDatabaseFile))
{
connection.Open ();
doSomething();
connection.Close();
}

I've put this into a static class to do all my database query stuff, and as I understand it this is tight and secure (i.e. no connections left open). However, it always fails at the 'connection.Open' stage when I'm running a large number of queries. I'm totally out of ideas as to how to solve this, I'm very new to Xamarin but have released apps which are using this code which work fine, its only once the number of queries (I assume) goes over a certain point.

I've looked into singleton design patterns but can't find any explicit samples of one for database access, can anyone please help as I'm totally at a loss as to how to fix this.

Thanks
John

Posts

  • AndyFlisherAndyFlisher GBBeta, University ✭✭✭

    Not Xamarin specific, but does this help?

    http://stackoverflow.com/questions/2493331/what-are-the-best-practices-for-sqlite-on-android

    Personally, if possible, I would try to reuse an existing connection with appropriate check exists / connect logic, and thus reducing the number of specific connect / disconnects (let the underlying SQLite handlers manager that)

    As an aside, have you tried iterating a counter to see how many connections / queries it manages before bombing out - that value may mean something to a better person than me?

  • softlionsoftlion FRBeta ✭✭✭

    Don't use Mono.Data.Sqlite, use sqlite.net pcl instead.

  • JohnChapmanJohnChapman GBMember

    thanks to both of you for taking the time to reply, yes Andy I did try maintaining a single correction as you suggested as a workaround but this didn't help.

    I'll have a go with the alternative library and get back to you with progress, reading round I'm not the first person to have a problem with this
    thanks for your help

  • JohnChapmanJohnChapman GBMember
    edited December 2013

    Sorry but still having the same problem. I've done the most explicit memory clearing I can think of and its still falling over after a large number of queries, here is the relevant code

        public static SqliteConnection conn; // connection to use throughout
        if ( conn!=null) conn.Dispose ();
        conn = new SqliteConnection("Data Source=" + dbPath);
        try{
            conn.Open ();
    

    // do stuff, run queries etc
    conn.Close();
    }
    catch(Exception e){
    // oh dear, can't open
    }

    This example, although I realise isn't the prettiest / most efficient, should work no matter if the connection is being reused, reset etc etc, however this code falls over after a large number of queries and is indicative that somewhere a memory leak is still going on.

    The exception is caught at the db.open stage.

    I'm inclined to think therefore this is a major bug with xamarin, does anyone have any example code / suggestions to make this work as the documentation for the sqllite stuff is pretty basic and doesn't address this area at all, I've no idea about how to create a Singleton etc as there is no documentation for it, so could someone post how they would change this code to make it work?

    Sqllite.net didn't work either;), same problem

    Thanks

  • JohnChapmanJohnChapman GBMember

    Fixed now - did an edit to sqllite.net to allow generic queries to return List<Dictionary<string,string>> instead of having to specify the objects etc as this was a faff. I've tried to attached the modified sqllite library but it isn't allowed so I'll paste in the relevant code, the call is

    List <Dictionary<string,string>> results = connection.Query2("Select something from table where something, "");

    and in sqllite.net

        public List<Dictionary<string, string>> Query2(string query, params object[] args)
        {
            var cmd = CreateCommand(query, args);
    
            List<Dictionary<string,object>> results = cmd.ExecuteQuery();
            List<Dictionary<string,string>> op = new List<Dictionary<string,string>> ();
    
            foreach(Dictionary<string,object> row in results){
    
                Dictionary<string, string> outputRow = row.ToDictionary(k => k.Key, k => k.Value == null ? "" : k.Value.ToString());
                op.Add(outputRow);
            }
            return op;
        }
    

    thanks for your help. By the sounds of it Xamarin need to update their documentation to ensure people don't use Mono.Data.Sqllite as its got a memory leak issue, as it still fails even with the singleton pattern etc.

  • softlionsoftlion FRBeta ✭✭✭
    edited January 2014

    The open source project site is here: https://github.com/oysteinkrog/SQLite.Net-PCL

    Could you create a pull request there ?

    The relevant file is https://github.com/oysteinkrog/SQLite.Net-PCL/blob/master/src/SQLite.Net/SQLiteConnection.cs

    And

    https://github.com/oysteinkrog/SQLite.Net-PCL/blob/master/src/SQLite.Net/SQLiteCommand.cs

    So you say that this one leaks ?

       public List<T> Query<T>(string query, params object[] args) where T : new()
            {
                SQLiteCommand cmd = CreateCommand(query, args);
                return cmd.ExecuteQuery<T>();
            }
    
  • JohnChapmanJohnChapman GBMember

    no, I don't think the SQLlite.Net leaks, but Mono.Data.Sqlite does. The problem with sqllite.net is that it only returns specific configured objects, whereas I need something more flexible - having List implemented as List<Dictionaty<string,string>> for example didn't work.

    More than happy to do a pull request, however I suspect my relative inexperience with xamarin in general would mean that I wouldn't be the best person to do this as I'll probably do more harm than good. The 'Query2' function works very well but I'm sure someone else with more expertise could improve it, but for anyone having similar problems I'd just suggest adding it in ad-hoc to sqllite.net as and when you need it.

Sign In or Register to comment.