title: SQLite problem "unable to open the database file"
body: <p>I come to you after a lot of hours googling and reading other discussions about SQLite on StackOverflow, but I definitely can't find any explanation to my problem, so here it is :</p>
  
  <ul>
  <li><p><strong>The context :</strong><br>
  I'm developping an application for iPad wich has to deal with some "large" amounts of data, in several occasions. In one of them, I must import points coordinates from a .kml file (Google's xml for geographical data) into my database, in order to reuse them later with a MKMapView and load them faster than by parsing xml when it needs to show a specific layer.</p></li>
  <li><p><strong>The details :</strong><br>
  The import thing is quite easy : when dealing with those files, I'm only concerned with 2 tables :  </p>
  
  <ul>
  <li>One containing zones definitions and details : for the moment, an <code>integer</code> as an id, and a <code>text</code> for naming.</li>
  <li>One containing two <code>real</code> for coordinate storage and an <code>integer</code> referencing the first table for knowing which zone point is part of.<br>
  So as long as reading my file, I first create an entry for the new zone, and then I insert points into the second table, with ID of the last zone created in the first table...nothing complicated!  </li>
  </ul>
  
  <p>But...  </p></li>
  <li><p><strong>The problem :</strong><br>
  After running fine a while, I get an exception from SQLite with the famous message "Unable to open the database file", and then it comes I can't do anything more with the database. This exception can randomly occur in the zone creation or the points insertion methods.</p></li>
  <li><p><strong>My reflexions :</strong><br>
  Considering the numerous points in those files, I suspected memory or disk saturation but other parts of my app discarded those points (to my mind).<br>
  First, memory : it comes that when the exception occurs, the app is using about 10 or 12 MB of RAM. It can seems quite huge, but it's due to the ~10MB .kml file loaded in memory, so it's explainable. And above it all, the MKMapView thing of my app deals with tons of high-res tiles layers above map, and so leads to memory peaks which can afford 20 or even 25MB without making the iPad to crash.<br>
  Second, disk : when reseting my database and filling only the 2 tables described above, the db file size when the exception occurs is always about 2.2 or 2.5MB, but when I fill other tables (the other parts of my apps works well!) the db file is about 6 or 7MB, and the device doesn't complain at all.</p></li>
  <li><p><strong>So what?!</strong><br>
  CPU-angryness and panic? I don't think so because some of the other tables of my database are filled at the same rythm without problem... and running my app in simulator crashes too, with a core i7 just laughing at the job.<br>
  SQLite bad use? There we go! To my mind, it's the only solution left! But I really can't understand what's going on here because I process my requests the same way I do in other app's parts which - repeating myself - work like a charm! </p></li>
  <li><p><strong>SQLite details :</strong><br>
  I have a <code>DB</code> class which is a singleton I use to avoid creating/releasing an <code>SqliteConnection</code> object each request I do, and all my methods dealing with database are contained in this class to be sure I don't play with the connection anywhere else without knowing it. Here are concerned methods of this class : </p>
  
  <pre><code>public void     saveZone(ObjZone zone)  { //at this point, just creates an entry with a name and let sqlite give it a new id
      lock (connection) { //SqliteConnection object
          try {
              openConnection();
              SqliteCommand cmd = connection.CreateCommand();
              cmd.CommandText = zone.id == 0 ?
                  "insert into ZONES (Z_NAME) values (" + format(zone.name) + ") ;" :
                  "update ZONES set Z_NAME = " + format(zone.name) + " where Z_ID = " + format(zone.id) + " ;";
              cmd.ExecuteNonQuery();
              if (zone.id == 0) {
                  cmd.CommandText = "select Z_ID from ZONES where ROWID = last_insert_rowid() ;";
                  zone.id = uint.Parse(cmd.ExecuteScalar().ToString());
              }
              cmd.Dispose();
          }
          catch (Exception e) {
              Log.failure("DB.saveZone(" + zone.ToString() + ") : [" + e.GetType().ToString() + "] - " +
                  e.Message + "\n" + e.StackTrace); //custom Console.WriteLine() method with some formating
              throw e;
          }
          finally {
              connection.Close();
          }
      }
  }
  
  public void     setPointsForZone(List&lt;CLLocationCoordinate2D&gt; points, uint zone_id) { //registers points for a given zone
      lock (connection) {
          try {
              openConnection();
              SqliteCommand cmd = connection.CreateCommand();
              cmd.CommandText = "delete from ZONESPOINTS where Z_ID = " + format(zone_id);
              cmd.ExecuteNonQuery();
              foreach(CLLocationCoordinate2D point in points) {
                  cmd.CommandText = "insert into ZONESPOINTS values " +
                      "(" + format(zi_id) + ", " + format(point.Latitude.ToString().Replace(",", ".")) + ", "
                      + format(point.Longitude.ToString().Replace(",", ".")) + ");";
                  cmd.ExecuteNonQuery();
                  cmd.Dispose();
              }
          }
          catch (Exception e) {
              Log.failure("DB.setPointsForZone(" + zone_id + ") : [" + e.GetType().ToString() + "] - " + e.Message);  
              throw e;
          }
          finally {
              connection.Close();
          }
      }
  }
  </code></pre>
  
  <p>And to be as clear as I can, here are some of the methods referenced in the two above (I use this custom <code>openConnection()</code> method because I use foreign keys constraints in most of my tables and cascading behaviours are not enabled by default, but I need them.) :   </p>
  
  <pre><code>void openConnection() {
      try {
          connection.Open();
          SqliteCommand cmd = connection.CreateCommand();
          cmd.CommandText = "PRAGMA foreign_keys = ON";
          cmd.ExecuteNonQuery();
          cmd.Dispose();
      }
      catch (Exception e) {
          Log.failure("DB.openConnection() : [" + e.GetType().ToString() + "] - " + e.Message);
          throw e;
      }
  }
  
  public static string format(object o) {
      return "'" + o.ToString().Replace("'", "''") + "'";
  }
  </code></pre></li>
  </ul>
  
  <p><strong>Well</strong>, sorry for the novel, I may already thank you for reading all that stuff, no?! Anyway, if I missed something that could be useful, let me know and I'll document it as soon as possible. 
  I hope someone will be able to help me, anyway, thank you by advance!<br>
  (And my apologies for my poor frenchie's english.)</p>
  
  <p><strong>EDIT</strong><br>
  My problem is "solved"! After a few changes for debugging pourposes, no big modifications, and no success, I put back the code in the state I posted it... and now it works. But I really would appreciate if some someone could give me an explanation of what may have happened! It seems like SQLite behaviour (on iPad at least - never used it anywhere else) can be quite obscure at some times... :/</p>