executing '.tables' sqlite statement

Discussion in 'iOS Programming' started by fergusjk, Sep 23, 2010.

  1. fergusjk macrumors member

    Joined:
    Aug 23, 2010
    Location:
    Ayrshire Coast, Scotland
    #1
    I'm building an app that uses an sqlite DB.

    I created the sqlite DB manually on my mac during development but obviously I need this to be automated in the software. So I need a way to create 3 DB tables if they do not already exist.

    The way I check manually is .tables in the sqlite command line tool which returns the three tables. I tried using this in a command but to no avail. The following code works in terms of connecting and executing the sql correctly as I have used the same structure in several other places in my code.

    Code:
    -(void) checkDBExists{
    	// Get the path to the documents directory
    	NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    	NSString *sqlFile = @"mySQLFile.sql";
    	NSString *databasePath = [[documentPaths objectAtIndex:0] stringByAppendingPathComponent:sqlFile];
    	
    	// Setup the database object
    	sqlite3 *database;
    	
    	// Open the database from the users filessytem
    	if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
    		// Setup the SQL Statement and compile it for faster access
    		NSLog(@"IM IN CheckDB!");
    		const char *sqlStatement = ".tables";
    		sqlite3_stmt *compiledStatement;
    		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
    			NSString *tables;
    			// Loop through the results and add them to the feeds array
    			while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
    				// Read the data from the result row
    				tables = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
    
    			}
    			NSLog(@"tables=", tables);
    		}
    		// Release the compiled statement from memory
    		sqlite3_finalize(compiledStatement);
    		
    	}
    	sqlite3_close(database);
    
    }
    The problem lies in the fact that either it doesn't like the prepending period or it is putting a semi-colon at the end and trying to execute .tables; which doesn't work when I try it manually. It must be .tables without the semicolon.

    How do others check to see if DB tables exist in their apps? Anyone any idea as to how to get .tables executed successfully?
     
  2. dejo Moderator

    dejo

    Staff Member

    Joined:
    Sep 2, 2004
    Location:
    The Centennial State
    #2
    Normally, I would use the "IF NOT EXISTS" clause of the CREATE TABLE command.
     
  3. bredell macrumors regular

    Joined:
    Mar 30, 2008
    Location:
    Uppsala, Sweden
    #3
    If you want to access the list of tables you can use the 'sqlite_master' system table.

    Code:
    select name from sqlite_master where type = 'table';
    select 1 from sqlite_master where type = 'table' and name = 'my_table';
    
     
  4. fergusjk thread starter macrumors member

    Joined:
    Aug 23, 2010
    Location:
    Ayrshire Coast, Scotland
    #4
    Excellent!!!

    Both valid answers but for my purposes I don't actually need to return the DB tables - I was only returning them because I was unaware of the ultra-efficient 'if not exists' clause.
    So I'm going to run with

    CREATE TABLE IF NOT EXISTS myTable (table_definition);

    Thanks guys :D
     

Share This Page