Creating a SQLite3 Database File Using Objective-C

I am trying to create a SQLite3 database file through Objective-C at runtime. 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 it's hard for me to find the syntax for this. In addition to creating the table, I want the created table not to contain NOT in the application set, but rather to remain / be stored somewhere on the phone. The table should be read / write. I did some reading on the "user sandbox" as well as in the "document directory". I'm not sure I understand the difference between the two. Ideally, my application will use a button to enter input from text fields. After entering the text in the fields, the texts will be checked if the SQLite table "tblStore" existsand if not, a table will be created.

Recall: 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 be? I need to read and write tblStore file in db. 3. What is the difference between a "user sandbox" and a "document directory"?

This is what I have:

-(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 application, 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 to explain some of these things, as I am very new and unable to accomplish some of the things I tried. Thank you very much for your help!

+5
source share
6 answers

sqlite - , , . sqlite c, sqlite.

FMDB BWDB sqlite c. .

, BWDB lynda.com(), ... , -.

edit: , , ... ... db .. .... / db.. db .. , db , ..., 2 . .. , 2 ,

edit2: BWDB ( , , )

+2

//

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

// DB

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

//

-(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");
   }
}

//

-(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);
}

//

-(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);
   }
}

}

viewDidLoad

- (void)viewDidLoad
{

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

}

- NSString;

+11

, , "". , sqlite Documents , . .

+ (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;
+2

- /Users/Matt **/Library/Application Support/iPhone Simulator/5.1/Applications/5DB7A218-A0F6-485F-B366-91FD2F9BC062/Documents/

sqlite3 tblStore.sqlite

.schema tblStore , , .

. , . - database.sqlite ( ) .

0

, SQLLite , CoreData? CoreData SQLLite, API , .., , Xcode. , , .

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

CoreData . . SQL , CoreData.

"" , /. "" . , , , iOS, .

0

, . , .   .

0

All Articles