var dbOld = Titanium.Database.install('../dbEvents.db','events');
dbOld.remove();
dbEvents = Titanium.Database.install('../dbEvents.db','events');

6 Answers

• Accepted Answer

  Katrin,

  >Is anybody out there who would be able to tell me what I'm missing or doing wrong?

  The main thing you are doing wrong is trying to do your file.write() to a file in the Resources folder. That folder is read-only on the device so the .write() operation is failing. This in turn leads to your setData() function simply re-installing the original database.

  Thus you must download to a writable folder instead, such as Ti.Filesystem.applicationDataDirectory. However, that leads to a new problem in that the Ti.Database.install() method assumes the database to be installed is in the Resources folder, which you cannot update at run-time. Or at least a path relative to the Resources folder.

  The way I solve this problem is to instead download the new db directly to the folder where Titanium stores the real database. That is, when you use the .install() method and the real db does not yet exist, install() copies it from the Resources folder to a folder that is writeable. (The copy in Resources is never directly opened because sqlite could not update it.) So the new problem becomes how to find where the "real" database is at, and swap it out.

  Unfortunately, how/where to do that varies by platform according to my research. Not only does the location change, but the actual filename extension is different too. Your original post did not mention if you need this cross-platform, but you did add iphone as a tag so I assume you at least need it to work under iOS.

  This is a simplified version of how I do it in lieu of the xhr.onload code, using the names in your sample code :

  xhr.onreadystatechange = function()
  {
      if ( c.readyState == 4 ) {
          var dbName = 'events';
          var file;
          if ( Ti.Platform.osname == 'android' ) {
              file = Ti.Filesystem.getFile('file:///data/data/' + Ti.App.getID() + '/databases/' + dbName );
          }
          else {
              file = Ti.Filesystem.getFile(Ti.Filesystem.applicationSupportDirectory + '/database/' + dbName + '.sql');
          }
  
          if ( file.exists() ) {
              file.deleteFile();
          }
          file.write(this.responseData);
      }
  };
  

  For this to work, the database cannot already be open. In your example code, it wouldn't be but I just want to mention it as a point to keep in mind. This just swaps out the actual db file which is otherwise used.

  Note that I use onreadystatechange() instead of onload() because some threads in this forum suggest onload() is less reliable. I am not sure if that is an old issue that is no longer true, so I just use onreadystatechange() instead. Since that gets called for multiple reasons, we have to also test the readyState to make sure the change is from the download being complete.

  With the above code, you should not need the .install() method in your setData() function. Instead just use Ti.Database.open('events'). In the setup portion of your app.js, you may want to include:

      var db = Ti.Database.install('../dbEvents.db','events');
      dbClose();
  

  That will make sure the default database gets installed in case you try to call your setData() before you call your getRemoteDB() function, or in case there is a network or other failure in getRemoteDB().

  Also note the '../' prefix to dbEvents.db is needed only if the .js context where this is run is in a subfolder of the app Resources folder. That is the case in Kitchen Sink, where database.js in in the examples folder. However, if your .js files are in Resources itself, you should leave off the '../' prefix.

• your application architecture is not visible from the posting but I would suggest the following steps

  • download data
  • update database
  • fire event in application to reload database from file
  • fire event in application to update tableView
  • finish process, cleanup

  if you provide more information, maybe the community could give you a better suggestion

• Hi Aaron,

  Thank you very much for your quick reply and willingness to help me.

  Apologies if I wasn't specific enough. Here's my code:

  /* TABLE VIEW */
  table = Titanium.UI.createTableView({
      minRowHeight:70,
      top:0
  });
  // go to detail page on click
  table.addEventListener('click', function(e)
  {
      var detail = Titanium.UI.createWindow({
          url:'eventDetail.js',
          title:e.rowData.customTitle,
          backButtonTitle: "back",
          barColor: '#000000'
      });
      var eventID = e.rowData.id;
      Titanium.App.Properties.setInt('id', eventID);
      Titanium.UI.currentTab.open(detail,{animated:true});
  });
  
  function setData(){
      var dbOld = Titanium.Database.install('../dbEvents.db','events');
      dbOld.remove();
      dbEvents = Titanium.Database.install('../dbEvents.db','events');
      // select data to put in rows
      var dbRows = dbEvents.execute('SELECT * FROM EVENTS ORDER BY eventID DESC');
      // table data and rows
      var eventsData = [];
      //loop through data and put into rows
      while (dbRows.isValidRow())
          {
              var eventID = dbRows.field(0);
              var rowImage = dbRows.field(7);
              var title = dbRows.field(1);
              var titleLabel = Ti.UI.createLabel({
                  text: title,
                  top:10,
                  left:70,
                  color: '#000000',
                  textAlign:'left',
                  width: 'auto',
                  height:'auto',
                  font:{fontWeight:'bold',fontSize:14}
              });
  
              var date = dbRows.field(2);
              var venue = dbRows.field(3);
              var subLabel = Ti.UI.createLabel({
                  text: date + ', ' + venue,
                  left:70,
                  top:30,
                  color: '#000000',
                  textAlign:'left',
                  width: 'auto',
                  height:'auto',
                  font:{fontWeight:'normal',fontSize:12}
              });
              // add everything to row and add custom data to be used on click event
              var tableRow = Ti.UI.createTableViewRow({
                  height:'auto',
                  hasChild: true,
                  leftImage: rowImage,
                  customTitle:title,
                  id: eventID
              });
              tableRow.add(titleLabel);
              tableRow.add(subLabel);
              // push row info into data array
              eventsData.push(tableRow);
              dbRows.next();
          }
      dbRows.close();
      dbEvents.close();
      table.setData(eventsData);
  }
  setData();
  
  /* get new database and refresh table data */
  function getRemoteDB(){    
      xhr = Titanium.Network.createHTTPClient();
      xhr.setTimeout(5000);
      xhr.open('GET','http://www.cogdesign.com/iphone/dbEvents.db');
      xhr.onerror = function()
      {
          Ti.UI.createAlertDialog({title:'Very slow internet connection', message:'Please try again later'}).show();
          actInd.hide();
      };
      xhr.onload = function()
      {
          var filename = 'dbEvents.db';
          var file = Titanium.Filesystem.getFile(Titanium.Filesystem.resourcesDirectory,filename);
          file.write(this.responseData);
      };
      xhr.send();
  }
  
  if (Titanium.Network.online) {
      // refresh button        
      var refresh = Titanium.UI.createButton({
          systemButton:Titanium.UI.iPhone.SystemButton.REFRESH
      });
  
      refresh.addEventListener('click',function()
      {
          getRemoteDB();;
          table.setData([]);
          setTimeout(function()
          {
              setData();
          },1000);
      });
      win.setRightNavButton(refresh);
  }
  

• Is anybody out there who would be able to tell me what I'm missing or doing wrong?

  Thanks,
  Katrin

• Katrin,

  use the following code to remove your DB first from the filesystem, then install it again using Database.install()

    var f = Ti.Filesystem.getFile( Ti.Filesystem.applicationSupportDirectory + '/database/events.sql' );
    if ( f.exists() ) {
        f.deleteFile();
    }
  

  Hope it helps

  =H=

• Hi,

  This code dosent work on iOS 6.1.4

  var dbOld = Titanium.Database.install('../dbEvents.db','events');
  dbOld.remove();
  dbEvents = Titanium.Database.install('../dbEvents.db','events');
  

  This code dosent work too

  var f = Ti.Filesystem.getFile( Ti.Filesystem.applicationSupportDirectory + '/database/events.sql' );
    if ( f.exists() ) {
        f.deleteFile();
    }
  

  Please go read my post here and add an answer if you have an idea for iOS6.1.4

  Thanks