Creating an SQLite3 database file through Objectiv

Question

I'm trying to create an SQLite3 database file through Objective-C at run time. I am trying to create a table called "tblStore". I want the field names to be called "strStoreNumber" and "strStoreReg". I'm new to iOS and SQLite, so I'm having a hard time finding the syntax to perform this. In addition to creating the table, I want the created table to reside NOT in the app bundle, but rather it would reside/be stored somewhere on the phone. The table needs to be readable/writeable. I've done some reading on "user sandbox" and also a "documents directory". I'm not sure I understand the difference between the two. Ideally, my app would use a button to take input from Text Fields. After the input from the texts fields is put into strings, a check would be done to see if my "tblStore" SQLite table exists, and if it doesn't, the table will be created.

To recap: 1. What is the syntax for Obj-C/SQLite to create a table called "tblStore" with the fields "strStoreNumber" and "strStoreReg"? 2. Where should the db file reside? I need to read from and write to the tblStore db file. 3. What is the difference between a "user sandbox" and a "documents directory"?

This is what I have currently:

-(IBAction)setInput:(id)sender
{
    NSString *strStoreNumber;
    NSString *strRegNumber;
    NSString *tableName = @"tblStore";
    NSString *dbStrStore = @"strStore";
    NSString *dbStrReg = @"strReg";


    strStoreNumber = StoreNumber.text;
    strRegNumber = RegNumber.text;

    NSArray* paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString* documentsDirectory = [paths lastObject];
    NSString* databasePath = [documentsDirectory stringByAppendingPathComponent:@"tblStore.sqlite"];
//  NSString* databasePath = [[NSBundle mainBundle] pathForResource:@"tblStore" ofType:@"sqlite"];

    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) 
    {
        NSLog(@"Opened sqlite database at %@", databasePath);

        char *err; 
        NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('%@' TEXT PRIMARY KEY, '%@' TEXT);", tableName, dbStrStore, dbStrReg];
        if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) 
        { 
            sqlite3_close(database);
            NSAssert(0, @"Table failed to create.");
        }
        //...stuff
    } 
    else 
    {
        NSLog(@"Failed to open database at %@ with error %s", databasePath, sqlite3_errmsg(database));
        sqlite3_close (database);
    }

    NSString *querystring;

    // create your statement
    querystring = [NSString stringWithFormat:@"SELECT strStore, strReg FROM tblStore WHERE strStore = %@ AND strReg = %@;", strStoreNumber, strRegNumber];  

    const char *sql = [querystring UTF8String];

    NSString *szStore = nil;
    NSString *szReg = nil;

    sqlite3_stmt *statement = nil;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL)!=SQLITE_OK) //queryString = Statement
    {
        NSLog(@"sql problem occured with: %s", sql);
        NSLog(@"%s", sqlite3_errmsg(database));
    }
    else
    {
        // you could handle multiple rows here
        while (sqlite3_step(statement) == SQLITE_ROW) 
        {            
            szStore = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 0)];
            szReg = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)];
        }        
    }

    sqlite3_finalize(statement);

    lblStoreNumber.text = szStore;
    lblRegNumber.text = szReg;   
} 

When I run my app, I get the following errors:

2012-05-10 14:58:38.169 CCoDBTry[355:f803] Opened sqlite database at /Users/Matt****/Library/Application Support/iPhone Simulator/5.1/Applications/5DB7A218-A0F6-   485F-B366-91FD2F9BC062/Documents/tblStore.sqlite
2012-05-10 14:58:38.307 CCoDBTry[355:f803] sql problem occured with: SELECT strStore, strReg FROM tblStore WHERE strStore = 8053 AND strReg = 4;
2012-05-10 14:58:38.308 CCoDBTry[355:f803] no such column: strStore

I appreciate anyone who takes the time out to explain some of this stuff, as I am very new and have been unsuccessful in accomplishing some of the things I've tried. Thanks so much for the help!

Answer 1

sqlite is a pain if you dont know what you're doing. I also had some problems with sqlite c functions but then i decided to use sqlite wrapper.

FMDB and BWDB are good and easy to use sqlite wrappers for objective c. I suggest you use one of those .

Note that BWDB is in a lynda.com tutorial (this one) and if you don't find it on the web...leave a comment and i'll upload it somewhere.

edit: the only place you can write stuff in your app is in your documents directory...so..it plain terms...if the db is not in your documents directory..is read-only..also..when you read/write to your db..the OS copies the db in the documents directory..and does all the reading and writing there so you can have a db in your app bundle but you can't edit that one...so you'll end up with 2 db.I had the same problem myself..and i fixed it by merging the 2 db when i updated the app

edit2: i uploaded BWDB final project ( you have your wrapper there and project to see how it works)

Answer 2

maybe you forgot Table Insertions. Created Database and Table but its empty table . you trying to read the records from Table which is empty.

Answer 3

you can open terminal and cd to /Users/Matt**/Library/Application Support/iPhone Simulator/5.1/Applications/5DB7A218-A0F6-485F-B366-91FD2F9BC062/Documents/

then sqlite3 tblStore.sqlite

then use .schema tblStore should show your table schema and you can see if it was built correctly.

Your database needs to be in the documents directory for write access. If your database was only going to be read, never written to it could be in your application bundle. One way to accomplishing this is to create your database.sqlite file and add it to the bundle and copy it to the documents directory (if it doesn't already exist there) on launch.

Answer 4

You can use following code to get the database created at in Documents folder. Just pass a path in documents folder and the function will copy the sqlite database in Documents folder at the given path if required. You can then use this path to create and query database tables.

+ (NSString*) createDatabaseIfRequiredAtPath:(NSString*)databasePath {

    if (databasePath == nil)
       return nil;


   NSString *path = [NSString stringWithFormat:@"%@/%@", databasePath, kMainDBName];
   NSFileManager *fileManager = [NSFileManager defaultManager];
   NSError *error = nil;

   if ([fileManager fileExistsAtPath:path] == NO) 
   {
    // The writable database does not exist, so copy the default to the appropriate location.
      NSString *defaultDBPath = [[NSBundle mainBundle] pathForResource:kMainDBName
                                                              ofType:nil];
      BOOL success = [fileManager copyItemAtPath:defaultDBPath 
                                       toPath:path
                                        error:&error];
      if (!success)
      {
        NSCAssert1(0, @"Failed to create writable database file with message '%@'.", [  error localizedDescription]);
        return nil;
    }
}

return path;

Answer 5

// Create DB

-(NSString *) filePath
{
 NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
 NSString *documentDirectory=[paths objectAtIndex:0];
 return [documentDirectory stringByAppendingPathComponent:@"LoginDatabase.sql"];
}

// Open DB

-(void)openDB
{
 if(sqlite3_open([[self filePath]UTF8String], &db) !=SQLITE_OK)
 {
   sqlite3_close(db);
   NSAssert(0, @"Database failed to Open");
 }
}

// Create Table

-(void) createTableNamed:(NSString*)tableName withField1:(NSString*) field1 withField2:(NSString*) field2

{
   char *err;
   NSString *sql=[NSString stringWithFormat:@" CREATE TABLE IF NOT EXISTS '%@'('%@' TEXT PRIMARY KEY,'%@' TEXT);",tableName,field1,field2];

   if(sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) !=SQLITE_OK)
   {
    sqlite3_close(db);
    NSAssert(0, @"Table failed to create");
   }
}

// Inserting records

-(void)insertrecordIntoTable:(NSString*) tableName withField1:(NSString*) field1 field1Value:(NSString*)field1Vaue andField2:(NSString*)field2 field2Value:(NSString*)field2Value
{

  NSString *sqlStr=[NSString stringWithFormat:@"INSERT INTO '%@'('%@','%@')VALUES(?,?)",tableName,field1,field2];
 const char *sql=[sqlStr UTF8String];

 sqlite3_stmt *statement1;

 if(sqlite3_prepare_v2(db, sql, -1, &statement1, nil)==SQLITE_OK)
 {
  sqlite3_bind_text(statement1, 1, [field1Vaue UTF8String], -1, nil);
  sqlite3_bind_text(statement1, 2, [field2Value UTF8String], -1, nil);
 }
 if(sqlite3_step(statement1) !=SQLITE_DONE)
    NSAssert(0, @"Error upadating table");
 sqlite3_finalize(statement1);
}

// Retrieve data from table

-(void)getAllRowsFromTableNamed:(NSString *)tableName
{
  NSString *field1Str,*field2Str;

  NSString *qsql=[NSString stringWithFormat:@"SELECT * FROM %@",tableName];
  sqlite3_stmt *statement;
  if(sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, nil)==SQLITE_OK)
  {
   while(sqlite3_step(statement) ==SQLITE_ROW)
   {
     char *field1=(char *) sqlite3_column_text(statement, 0);
     char *field2=(char *) sqlite3_column_text(statement, 1);

     field1Str=[[NSString alloc]initWithUTF8String:field1];
     field2Str=[[NSString alloc] initWithUTF8String:field2];

     NSString *str=[NSString stringWithFormat:@"%@ - %@",field1Str,field2Str];
     NSLog(@"%@",str);
   }
}

}

In viewDidLoad call the methods

- (void)viewDidLoad
{

  [self openDB];
  [self createTableNamed:@"Login" withField1:@"USERNAME" withField2:@"PASSWORD"];
  [self insertrecordIntoTable:@"Login" withField1:@"USERNAME" field1Value:username andField2:@"PASSWORD" field2Value:password];

}

Where username and password are NSString values;

Answer 6

Is there a specific reason you'd like to use SQLLite directly, as opposed to using CoreData? CoreData uses an SQLLite database but is itself a higher-level API, and particularly with table views and such, you get a lot of functionality and template methods that are already set up for it in Xcode. Defining data models is trivial, you get tons of boilerplate code, and it's all optimized.

http://www.raywenderlich.com/934/core-data-on-ios-5-tutorial-getting-started

CoreData is sometimes described as having a steep learning curve. I disagree. If you're considering writing SQL yourself in your app, you're not going to have any trouble with CoreData.

The term "sandbox" is an abstract term for the portion of the device's filesystem that your application has read/write access to. The "Documents" directory is a specific directory within your application's sandbox. There are other files in your sandbox than just the documents directory, but most applications that save data to the filesystem in iOS do so in the documents directory.