var dbo = []; //initialize the database
var dbFile = Titanium.Filesystem.getFile(Titanium.Filesystem.getResourcesDirectory()+"\\anvil.db");
dbo = Titanium.Database.openFile(dbFile);
var SQL_race = dbo.execute("SELECT * FROM races WHERE raceid = " + raceid);

13 Answers

• Hey guys, I had this same problem with loading pre-made databases. A problem that may occur is if you try to open a database that you thought you installed, but in fact the install failed, then Ti will create the database for you. Then if you try to install the premade database again, it will not overwrite the database you opened.

  For this reason it is tough to experiment with databases when you are just starting out, since once they are created they are persistent (which is the point).

  What you should do is goto the application directory, delete the database files and then run the application, making sure your paths are right. I just named the database the name of the file, like so:

   var db = Ti.Database.install('drinks.db', 'drinks.db');
  

  This installs the drinks.db SQLite database file and names it 'drinks.db' for future use.

  After this, I just open the database anywhere in code using this:

  var db = Ti.Database.open('drinks.db');
  
  // Heres an example of reading in a dataset
  var picker_data = [];
  var ingrRS = db.execute('SELECT name FROM ingredient');
  while (ingrRS.isValidRow()) {
      var ingrName = ingrRS.fieldByName('name');
      picker_data.push(Titanium.UI.createPickerRow(
                 {title:ingrName}));
  
      ingrRS.next();
  }
  ingrRS.close();
  db.close();
  

  The key is to remember that once you call open you have created a database (or loaded one). This may not be the database you intended though.

• How large is your database file and how many rows is that table?

• why are you calling getFile?

  var dbFile = Titanium.Filesystem.getFile(Titanium.Filesystem.getResourcesDirectory()+"anvil.db");
  

  Usually the file will be in the Resources directory anyway. So you should be able to use something like this …

  var dbo = Titanium.Database.open('anvil.db', 'anvil');
  races=dbo.execute('SELECT * FROM races WHERE raceid = 1');
  while (races.isValidRow()){
  ... do something
  races.next();
  }
  races.close();
  dbo.close();
  

• Thanks for that attempt Gregor. Now I am getting the following error utilizing the code you provided above. Note: my anvil.db file is under the "Resources" folder of the main application folder.

  SQL Statement invalid or database missing
  [23:09:10:015] [Titanium.Database.DB] [Error] 
     Exception executing: SELECT * FROM races WHERE raceid = 1, 
     Error was: SQL Statement invalid or database missing
  

• Still not having any luck whatsoever with this. Does anyone have a link to some documentation or at least an example of how these database functions interact with the file system to access pre-existing database files?

  Is that somehow my problem? That the database was created prior to attempting using it with Titanium?

• Whoops sorry.

  If the SQLite database already exists then you should be using

  var dbo = Titanium.Database.install('anvil.db', 'anvil');
  

  According to the docs, this installs a database from the application Resources folder (at build time) and returns a reference to the opened database. It is safe to call this method multiple times since this method will only install once if it doesn't already exist on the device.

  The Titanium.Database.open API also opens an SQLite database. If it doesnt exist, it will create it. The Titanium.Database.open API does not have a second parameter according to the documentation, so the usage would be

  var dbo = Titanium.Database.open('anvil.db');
  

• Sorry Carlos,
  use

  var dbo = Titanium.Database.install('anvil.db', 'anvil');
  

  instead.

• Thanks again … I can't find that function for the desktop. I am not installing on a mobile platform, but rather the desktop. When I attempt to use that function it fails saying

  TypeError: Result of expression
  'Titanium.Database.install' [undefined] is not a function.
  

• I haven't been able to get the sdk to even recognize install(). Seems like the desktop sdk has a different API than mobile platform and install() ain't part of it. Go fish?

• In answer to others here, I don't believe Titanium.Database.open will allow you to use an existing database. If you check console, there will be something similar to the following:

  [19:24:03:050] [Titanium.Database.WebKitDatabases] [Debug] Creating new db: 0000000000000003.db
  [19:24:03:051] [Titanium.Database.WebKitDatabases] [Debug] path to new database: ...
  

  I believe you must use Titanium.Database.openFile to open an existing database.

  However, this appears to be no better:

  var db = Titanium.Database.openFile(Titanium.Filesystem.getResourcesDirectory()+"/app.db");
  var rc = db.execute('CREATE TABLE IF NOT EXISTS testing (file_number INTEGER, title TEXT, description TEXT)');
  rc = db.execute("INSERT INTO images (title, description) VALUES ('test', 'desc')");
  var rows = db.execute("select * from images");
  alert('result set is: ' + rows.fieldByName('title') + ' : ' + rows.fieldByName('description'));
  

  The table gets successfully created, but the remaining queries fail:

  [19:32:12:619] [Titanium.Database.DB] [Debug] Execute called with CREATE TABLE IF NOT EXISTS testing (file_number INTEGER, title TEXT, description TEXT)
  [19:32:12:620] [Titanium.Database.DB] [Debug] sql returned: 0 rows for result
  [19:32:12:620] [Titanium.Database.DB] [Debug] Execute called with INSERT INTO images (title, description) VALUES ('test', 'desc')
  [19:32:12:621] [Titanium.Database.DB] [Error] Exception executing: INSERT INTO images (title, description) VALUES ('test', 'desc'), Error was: SQL Statement invalid or database missing
  

  So, I'm not sure that helps considering it seems broken all around. Does anyone have a clue what is going on here? Is there a problem with the sqlite files themselves? I created my sqlite file in rails, but other attempts with the sqlite cli have been unsuccessful as well.

  This shouldn't be difficult…

• isnt the database name suppose to be *.sqlite?

  var dbo = Titanium.Database.install('anvil.sqlite', 'anvil');
  

• It's very small. That table only has 1 row. There aren't any tables with more than a dozen rows or so. 13KB database file.

  [21:10:43:088] [Titanium.Database.DB] [Error] Exception executing: SELECT * FROM races WHERE raceid = 1, Error was: Out of Memory
  

• Uhm Does Install() actually work?