Tutorial | Make a SQLite Mobile Database App for iPhone | iOS | iPad

The iOS SDK has many data persistent solutions, including SQLite, Core Data, archiving, property lists and files to name a few. Prior to Core Data, SQLite was the de facto standard for data persistence on an iOS device. The database continues to be a favorite solution for developers over Core Data. To help you learn how to implement SQLite within an iOS application I have written this tutorial to demonstrates how to load and display data from a SQLite database in an UITableView and to display selected details in a DetailViewController based on a user’s selection. The tutorial also shows how to insert data using an input form.

Setup The Project


  • Create a Single View project
  • Add the sqlite3 library. Complete instructions are provided in this tutorial

Create the Database

Before we can access the data in the database, it has to be created. SQLite can dynamically create databases if it doesn't exists with the open command. This operation should be performed in the AppDelegate when the app is loaded.

  • Open the AppDelegate implementation file and in the didFinishLaunchingWithOptions method declare a variable for any possible error messages, NSError *errMsg.
  • Also add a BOOL variable which will be used to check to see if the database already exists in the Document directory. If it doesn't exist, create it otherwise do nothing.
  • To get a handle on the Documents directory we will use the NSSearchPathForDirectoriesInDomains method from the Foundations Framework. This method retrieves an array of all the directories in the search path. For this example, the search path is the Document directory, NSDocumentDirectory, which is a constant in the Foundations Framework.
  • Since the array will contain only one entry, the Documents directory, we can use the ObjectAtIndex:0 to retrieve the first entry and store the value in a NSString variable,documentPath. The NSDocumentDirectory returns the complete path to the Documents directory and so all we need to do next is append the name of the database we will create and use to store the data.
  • The next line of code, fileExist = [[NSFileManager alloc] fileExistsAtPath:documentPath]; checks to see if the database already exists at the path specified with the documentPath variable. If it does, the return value will be YES. Otherwise the fileExistAtPath will return NO.
  • If the fileExist is false issue an open database command using the sqlite3_open SQLite command. If the database fails to open (or get created) by checking the return code SQLITE_OK, log an error and exit. Otherwise we will setup the query to create a table, todoTbl if it doesn't exist.
  • The sqlite3_exec command, used here, is a handy and compact command that encapsulates the prepare statement, the step statement and the finalize statement which are the statements to execute a SQLite query.
  • If the sqlite3_exec return code from the SQLITE_OK, we will have completed the database setup process. The complete code is provided in code listing 1 below.


Code Listing 1: The didFinishLaunchingWithOptions Implementation

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
    
    char *emsg;
    BOOL fileExist;
 
    //Get list of directories in Document path 
    NSArray * dirPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    
    //Define new path for database
    NSString * documentPath = [[dirPath objectAtIndex:0] stringByAppendingPathComponent:@"taskDb.db"];
    
    fileExist = [[NSFileManager alloc] fileExistsAtPath:documentPath];
    
    if(fileExist){
        
        if(!(sqlite3_open([documentPath UTF8String], &db) == SQLITE_OK))
        {
            NSLog(@"An error has occured.");
            
        }else{
            
            const char *sqlTable = "create table if not exists todoTbl(todoName varchar, todoDescription varchar, todoDate varchar)";
                        
            if(sqlite3_exec(db, sqlTable, NULL, NULL, &emsg) != SQLITE_OK)
            {
                NSLog(@"There is a problem with statement");
                
            }
            
        }

    }
       
    // Override point for customization after application launch.
    return YES;
}

Create Data Model


  • Create a new class, todo as a subclass of NSObject to define the data object. In this example I have defined a ToDo class
  • Add three variables: todoName, todoDescription and todoDate to the todo.h header file. See Code listing 2 to see how to code the variables.
  • For simplicity make all three a NSString type even though one of the fields on the input form is for a date.
  • Next open the todo.m or implementation file and synthesize these three variables using the @synthesize directive.

Code listing 2

@interface ToDo : NSObject

@property(nonatomic, strong) NSString *todoName;
@property(nonatomic, strong) NSString *todoDescription;
@property(nonatomic, strong) NSString *todoDate;

@end

Create DAO Methods


  • Next create another class yet again as a subclass of NSObject to handle the insert and select operations
  • Name the NSObject subclass DbOps
  • Add an #Import statement for the ToDo class see code listing 3 for the details.
  • Define a sqlite3 variable: db which will be used to create the database and access it for the CRUD operations.
  • Define a ToDo variable: todo, to capture data from the database to list in table view and detail view controller.

  • Next add a method to handle the insert queries: upsertToDo with one parameter for each of the ToDo properties. Code listing 3 provides the method signature.

Code listing 3

#import <Foundation/Foundation.h>
#import "sqlite3.h"
#import "ToDo.h"

@interface DbOps : NSObject
{
    sqlite3 * db;
    ToDo * myTodo;
}
-(void) upsertToDo:(NSString *)todoName :(NSString *) todoDescription :(NSString *)todoDate;
  • Also add a method for the select operations: -(void) selectToDo;
  • We will also need a NSMutableArray to add the contents of the database which will be the data source for the table. Code listing 4 is the complete DbOps header definition.

Code listing 4

#import <Foundation/Foundation.h>
#import "sqlite3.h"
#import "ToDo.h"

@interface DbOps : NSObject
{
    sqlite3 * db;
    ToDo * myTodo;
}

@property(nonatomic,strong) NSMutableArray * todoArr;
-(void) upsertToDo:(NSString *)todoName :(NSString *) todoDescription :(NSString *)todoDate;
-(void) selectToDo;

@end

Switch to the implementation file and synthesize the todoArr NSMutableArray. Next we will implement the selectTodo method because it will be the first method called when the app is loaded. Any data in the database will read into the todoArr and displayed in the UITableView. This method will load the data from the SQLite database using a SELECT query.

  • Next initialize the todoArr: todoArr = [[NSMutableArray alloc] init];
  • Since the database will be dynamically created using the sqlite3 library in the Documents directory, we will need the path to this directory which will be returned as an array using the NSSearchPathForDirectoriesInDomains Foundation function as in code listing 5

Code listing 5

 NSArray * dirPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
  • To extract the path to the directory and the database, use the NSMutableArray method, objectAtIndex and get the object at index 0. By default there should only have one item in the array, so the index is always 0 unless you add subdirectories. This database path will be assigned to the documentPath NSString variable and will be passed to the sqlite3_open Sqlite 3 function to create and open the database. If the database cannot be created or opened, the program should log an error and exit the method. In a production app instead of using the NSLog, an UIAlert could be used.
  • The query string will be assigned to the sql variable, const char * sql, will be execute the SELECT statement. This query will be used to populate the todoArr, hence becoming the data source for the UITableView. The query are listed in Code listing 6.

Code listing 6

const char *sql = "select todoName, todoDescription, todoDate from todoTbl";
  • To execute the query we will need a prepared statement. The sqlite3 library provides the sqlite3_stmt function, which will be assigned the query string and will be passed to the sqlite3_prepare_v2 function. The query,code listing 7, is executed if there are records in the database. The step function will iterate over the records in the database and assign the values to a ToDo object’s properties. With each iteration, the object is added to the todoArr. Remember to include the SQLITE_ROW to check if there are a row otherwise the statement will loop until the app runs out of memory and crash.
  • Since you can't assign a NULL cString to a NSString (you will get a SIGBRT error and crash), we will check if there are any characters in the first column using the char * checkChar = (char*)sqlite3_column_text(sqlStatement, 1); statement. There are numerous ways to handle this, but for the sake of simplicity if there is a NULL in the first column the code will skip that row and move on to the next row.

Code listing 7

if(sqlite3_prepare_v2(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)
                {
                    NSLog(@"There is a problem with prepare statement");
                    return;
                }else{
                    while (sqlite3_step(sqlStatement)==SQLITE_ROW) {
                        
                        char * checkChar = (char*)sqlite3_column_text(sqlStatement, 1);
                        if (checkChar!=NULL) {
                            ToDo * newToDo = [[ToDo alloc] init];
                            newToDo.todoName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(sqlStatement, 0)];
                            newToDo.todoDescription= [NSString stringWithUTF8String:(char *)sqlite3_column_text(sqlStatement, 1)];
                            
                            //get date value
                            const unsigned char *charDate = sqlite3_column_text(sqlStatement, 2);
                            
                            if(charDate!=NULL){
                                //Convert char Date value to NSString
                                NSString *strDate = [[NSString alloc] initWithUTF8String: (const char *) charDate];
                                
                                NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
                                
                                [dateFormatter setDateFormat:@"MM/dd/yyyy"];
                                
                                newToDo.todoDate = strDate;
                            }else {
                                 newToDo.todoDate = @"";
                            }
                            
                            [todoArr addObject:newToDo];
                            newToDo = nil;
                        }
                       
                    }
                    sqlite3_finalize(sqlStatement);
                    sqlite3_close(db);
                } 

Code listing 10 is the full implementation of the selectToDo method:

Code listing 10

-(void) selectToDo{
    int recordCount = 0;

    todoArr = [[NSMutableArray alloc] init];
    
 
    //Get list of directories in Document path 
    NSArray * dirPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    
    //Define new path for database
   NSString * documentPath = [[dirPath objectAtIndex:0] stringByAppendingPathComponent:@"taskDb.db"];
    
   if(!(sqlite3_open([documentPath UTF8String], &db) == SQLITE_OK))
        {
            NSLog(@"An error has occured.");
            return;
        }else{
            
            
            const char *sql = "select todoName, todoDescription, todoDate from todoTbl";
       
            sqlite3_stmt *sqlStatement;

         
                if(sqlite3_prepare_v2(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)
                {
                    NSLog(@"There is a problem with prepare statement");
                    return;
                }else{
                    while (sqlite3_step(sqlStatement)==SQLITE_ROW) {
                        
                        char * checkChar = (char*)sqlite3_column_text(sqlStatement, 1);
                        if (checkChar!=NULL) {
                            ToDo * newToDo = [[ToDo alloc] init];
                            newToDo.todoName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(sqlStatement, 0)];
                            newToDo.todoDescription= [NSString stringWithUTF8String:(char *)sqlite3_column_text(sqlStatement, 1)];
                            
                            //get date value
                            const unsigned char *charDate = sqlite3_column_text(sqlStatement, 2);
                            
                            if(charDate!=NULL){
                                //Convert char Date value to NSString
                                NSString *strDate = [[NSString alloc] initWithUTF8String: (const char *) charDate];
                                
                                NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
                                
                                [dateFormatter setDateFormat:@"MM/dd/yyyy"];
                                
                                newToDo.todoDate = strDate;
                            }else {
                                 newToDo.todoDate = @"";
                            }
                            
                            [todoArr addObject:newToDo];
                            newToDo = nil;
                        }
                       
                    }
                    sqlite3_finalize(sqlStatement);
                    sqlite3_close(db);
                } 
              
    }
    

    
}

The next implementation is the insert method upsertTodo. For this example the method only performs inserts but should be modified to also provide updates to existing records as well. Also this method assumes that the database already exist, which would need to be verified in a production app.

  • The first to do is get the path of the Documents directory where the database is located and assign the complete path to the documentPath variable as be did in the previous method, see listing 11.

Code listing 11

NSArray * dirPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
//Define new path for database
NSString * documentPath = [[dirPath objectAtIndex:0] stringByAppendingPathComponent:@"taskDb.db"];
  • The next block of code will attempt to open the database and perform the insert. As we have seen before, opening the database is done with the sqlite3_open SQLite3 function, which uses the documentPath value in for its first argument.
  • If the database is successfully opened, the insert query string is defined and assigned to the sql variable, sql.
  • Next, we bind the parameter values which will contains the values of the three IBOutlets in the klViewController, where the method will be called. We will look at this View Controller in later section. See code listing 12 below. The tutorial on CRUD operations provides more detailed information on column binding, if you need it.

Code listing 12

NSString *insertSQL = [NSString stringWithFormat: 
                               @"INSERT INTO todoTbl(todoName, todoDescription, todoDate) VALUES ('%@','%@','%@')", 
                                                              todoName,todoDescription, todoDate];
                                                              
        const char *sql = [insertSQL UTF8String];        
            
        sqlite3_stmt *sqlStatement;
  • The last step is to execute the query and insert the data. This is accomplished using the sqlite3_step and sqlite3_finalize statements. Finally the database is closed using the sqlite3_close statement. The complete implementation of the upsertToDo is provided in code listing 13.

Code listing 13

-(void) upsertToDo:(NSString *)todoName :(NSString *) todoDescription :(NSString *)todoDate{
      
    //Get list of directories in Document path 
    NSArray * dirPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    
    //Define new path for database
    NSString * documentPath = [[dirPath objectAtIndex:0] stringByAppendingPathComponent:@"taskDb.db"];


    if(!(sqlite3_open([documentPath UTF8String], &db) == SQLITE_OK))
    {
        NSLog(@"An error has occurred.");
        return;
    }else{
        NSString *insertSQL = [NSString stringWithFormat: 
                               @"INSERT INTO todoTbl(todoName, todoDescription, todoDate) VALUES ('%@','%@','%@')", 
                                                              todoName,todoDescription, todoDate];
                                                              
        const char *sql = [insertSQL UTF8String];        
            
        sqlite3_stmt *sqlStatement;
        if(sqlite3_prepare_v2(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)
        {
            NSLog(@"Problem with prepare statement");
            return;
        }else{
          
            if(sqlite3_step(sqlStatement)==SQLITE_DONE){
                sqlite3_finalize(sqlStatement);
                sqlite3_close(db);
            }
            
        }
    }
    
}

Setup Storyboard


  • The storyboard that was created with the initial project creation will need to be enhanced with an UITableViewController and a second UIViewController
  • On the klViewController, add three UITextFields and an UIButton.
  • Also add three UILabels: “To Do”, “Description” and “Do By Date”
  • Label the UIButton: Save.
  • Next open the klViewController header file using the Assistant editor and three an IBOutlet for each of the UITextField: todoName, todoDescription and todoDate. This tutorial provides detailed instructions to create IBOutlets and IBActions. See listing 14.
  • Also add an instance variable for the DbOps class.

Code listing 14

#import <UIKit/UIKit.h>
#import "DbOps.h"


@interface klViewController : UIViewController
@property (strong, nonatomic) IBOutlet UITextField *todoName;
@property (strong, nonatomic) IBOutlet UITextField *todoDescription;
@property (strong, nonatomic) IBOutlet UITextField *todoDate;
@property (nonatomic, strong) DbOps * ops;

- (IBAction)saveToDo:(id)sender;

@end
  • Also create an IBAction, saveToDo, which will call the upsertToDo method.
  • Next add another UIViewController to the canvas and add three UILabels to display the contents of the ToDo object when it is selected in the UITableView. But before creating the IBOutlets, we will need a view controller class for the UIViewController. So create a new class as a subclass of the UIViewController and name it todoViewController.
  • Add the view controller class to the UIViewController as previously discussed and open the header file and add the IBOutlets: todoName, todoDescription and todoDate.
  • Also in the todoViewController header, import the Todo class and add a @class directive for the ToDo class.
  • Next define a ToDo instance variable that will be used to de-reference the object that will be passed using the Segue. See code listing 15 below.
  • Also you will need to synthesize these variables in the implementation file.
  • As a final step, create a connection the Cell Prototype to the UIViewController as a Push Segue so when a user clicks on the selection in the cell, the object will be pushed and displayed in the DetailViewController.

Code listing 15

#import <UIKit/UIKit.h>
#import "ToDo.h"

@class ToDo;
@interface todoDetailViewController : UIViewController

@property (nonatomic, strong) ToDo * todoDetail;
@property (strong, nonatomic) IBOutlet UILabel *todoName;
@property (strong, nonatomic) IBOutlet UILabel *todoDescription;
@property (strong, nonatomic) IBOutlet UILabel *todoDate;

@end
  • Finally add a UITableViewController to the canvas
  • Set the “Is Initial View Controller” attribute in the Attribute inspector
  • Select the Cell Prototype and add an identifier through the Attribute inspector. We will this value to assign the values of the todoArr to the cells in the cellForRowAtIndexPath later.
  • Control+Drag a connection from the UITableViewController to the Proxy object (Yellow Globe at the bottom of the Scene) to set the DataSource and also the Delegate.
  • Each ViewController needs a controller class to perform the logic and interface between the Model and View, so add new file as a subclass of the UITableView class. Called it ListViewController.
  • Once it is created, add it to the UITableViewController selected the custom class in the Custom Class section of the Identity Inspector.
  • Once set we will need to add an IBOutlet to the header file. This IBoutlet will be called to reload any new data that is added using the input Scene. See the code in listing 16. You add an IBoutlet by opening the header alongside the storyboard using the Assistant Editor and Control+Dragging a connection from the UITableView to the header. Add a name for the IBOutlet in the popover. For this example I called it "todoTable".
  • In the header file, add the following code, listing 16, add import statements for the ToDo and DbOps classes as well as the klViewController and todoDetailViewController classes.
  • Also add the UITableViewDelegate and UITableViewDataSource. See code listing 16.

Code listing 16

#import <UIKit/UIKit.h>
#import "DbOps.h"
#import "ToDo.h"
#import "klViewController.h"
#import "todoDetailViewController.h"

@interface ListViewController : UITableViewController<UITableViewDataSource, UITableViewDelegate>


@property(nonatomic, strong) ToDo * todoItem;
@property(nonatomic, strong) DbOps * ops;
@property(nonatomic, strong) NSMutableArray * todoArray;
@property (strong, nonatomic) IBOutlet UITableView *todoTable;

@end
  • With the UITableViewController selected, create a navigation controller using the (Editor->Embed In->Navigation Controller) command from the Editor menu in Xcode.
  • Next add a Navigation Item button to the Navigation Bar in the UITableViewController and add a Push Segue to the klViewController that was initially created with the project. Read this tutorial” IOS 5 Storyboarding Tutorial using Segues | Scenes | View Controllers | Navigation”, if you need help creating Scenes and Segues.

The complete layout of the Storyboard is presented in Figure 1 below

Figure 1 - Storyboard Layout
Figure 1 - Storyboard Layout

Implement the View Controller


Now that the Storyboard is done, we will add code to the implementation files.

  • Open the klViewController implementation, or whatever you call it the initial view controller when you created the project
  • In the saveToDo method add the following code, listing 17, to pass the values of the IBOutlets (UITextFields) to the upsertToDo method:

Code listing 17

- (IBAction)saveToDo:(id)sender {
    ops = [[DbOps alloc] init];
    [ops upsertToDo:self.todoName.text :self.todoDescription.text :self.todoDate.text];

}

That is all that is needed for the klviewController implementation

  • In the ListViewController, in the viewWillAppear method, add the following code, listing 18, to initialize the todoArray, initialize the ops instance variable, call the selectTodo method and assign the values from the todoArr to the todoArray variable. This will load any existing items into the UITableView when it is loaded.
  • To be able to reload any new data at runtime, call the reloadData method as in coding listing 18.

Code listing 18

- (void)viewWillAppear:(BOOL)animated
{
    [super viewWillAppear:animated];
    todoArray = [[NSMutableArray alloc] init];
    ops = [[DbOps alloc]init];
    [ops selectToDo];
    todoArray = [ops todoArr];
    [todoTable reloadData];

}
  • In the numberOfRowsInSection method define the number of rows the table will need to display by changing the return value to the number of items in the todoArray as in listing 19.

Code listing 19

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    // Return the number of rows in the section.
    return [todoArray count];
}
  • Another important method is the numberOfSectionsInTableView. This defines the number of cells per section. For this example, we will have only one, which will be the todoName. So the return value to 1. The code is listed below in code listing 20.

Code listing 20

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView
{
    // Return the number of sections.
    return 1;
}
  • To actually display the values in the cells, we will need to implement the cellForRowAtIndexPath method as in listing 21 which assigns the todoName value for each item in the todoArray to the cell.textLabel property.

Code listing 21

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
    static NSString *CellIdentifier = @"todoCell";
    
    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
    if (cell == nil) {
        cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier];
    }
    
    ToDo * todoObj = [ops.todoArr objectAtIndex:indexPath.row];
    cell.textLabel.text = todoObj.todoName;
    
    return cell;
}
  • Finally the selected object is pushed to the todoViewController using the prepareForSegue method. This method defines a todoViewController object as the destinationViewController of the Segue, showDetail. The selected object which is defined by the indexPathForSelectedRow’s row property, is assigned to the todoDetail variable of the todoViewController. See code listing 22 below.

Code listing 22

-(void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender
{
    if ([[segue identifier] isEqualToString:@"showDetail"]) {
        todoDetailViewController *detailViewController = [segue destinationViewController];
        
        detailViewController.todoDetail = [self.todoArray objectAtIndex:[self.tableView indexPathForSelectedRow].row];
        
    }
}
  • The details of the selection will be displayed in the todoDetailViewController in the viewWillAppear method presented in code listing 23
  • The todoDetail values are assigned to the corresponding IBOutlets for display when the view controller is loaded.

Code listing 23

- (void)viewWillAppear:(BOOL)animated
{
    [super viewWillAppear:animated];
    todoDetail = (ToDo *)[self todoDetail];
    self.todoName.text =  todoDetail.todoName;
    self.todoDescription.text = todoDetail.todoDescription;
    self.todoDate.text = todoDetail.todoDate;
}

In Summary



Below are screenshots of the running app. Of course this is a rudimentary design and its objective is to provide an example of how to display content from a SQLite database in an UITableView and also how to push the details to a second view controller. However in a real production app, more error checking and proper controls and validation must be implemented, not to mention proper testing to make sure the application performs as desired. The code itself is only for demonstration purposes only and the author doesn’t provide any guarantees on its functionality in any application where it is implemented.

Figure 2: Input Screen
Figure 2: Input Screen
Figure 3: UITableView with Data
Figure 3: UITableView with Data
Figure 4: Detail View with Selected Details
Figure 4: Detail View with Selected Details

More by this Author


Comments 81 comments

mikeydcarroll67 4 years ago

Exactly what I needed! I was looking for something like this yesterday!


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Hope it helps! Btw how's the app coming along? Did you get your sql inner join query to work?


mikeydcarroll67 4 years ago

Kinda. I am still kinda fiddling with it to get it to work the way I like


mikeydcarroll67 4 years ago

When you do the save function, do you get a sigabrt?


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

No, is there more details in the bold text in the log?


mikeydcarroll67 4 years ago

SIGABRT Error-[AppName saveData]: Unrecognized selector passed to instance (can't remember the instance it is hanging on). It is just on the KlViewController on the save button.


mikeydcarroll67 4 years ago

Here is the exact log: [AddPhraseVC saveData]: unrecognized selector sent to instance 0x784ae80. I only get it when I press the save button.


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Sorry Mike, for some reason your comments don't show up in the notifications. I sincerely apologize for not responding sooner. i hope you have found the problem already. The error message in question is saying that one of your objects is not found.

This sometimes happens if you add IBActions or IBOutlets to the storyboard and then delete them but the reference are not deleted from the Storyboard XML. To check, right click on the Storyboard file in the Navigator as select "Open As- Source Code". Next do a ctrl+find to search for references that you have deleted from the Storyboard.

Hope this helps

Kevin


adan 4 years ago

can you upload the project or the code please??

your tutorials are very well done mate thanks a lot!


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

I can't attach any project files here but send me an e-mail: kevinlanguedoc[at]gmail.com and I will send it to you.

Cheers


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Hi Jakub82

I was pondering your problem. The easiest way I know is to preload your data in an existing sqlite db (see my turorial on creating a db in firefox) and then run a C script to read in your data (long., lat, title, subtitle, etc) from a file.

or you can create the db with sqlite_open and execute an insert from the command line.

After your db is created and loaded, add it to your project.

1- Create a db using Firefox or dynamically using sqlite_open

2-open sqlite

3-create table is not exists

2-insert data from file using command line interface CLI

char buffer[300];

for (unsigned i = 0; i [less Than] annotationValues; i++)

{

sprintf(buffer, "INSERT INTO annotations VALUES ('%lf', '%lf', '%s', '%s')",

longitudeObj, lattitudeObj, titleObject, subtitleObj);

sqlite3_exec(mDb, buffer, NULL, NULL, NULL);

}

If you have the time, I can put together a little Mac App over the weekend


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Hi Jakub82

I was think for loading your annotations, why don't make a list in Excel or something similar and save as cvs file in Resource folder of your project. Then when your app loads for the first time in the AppDelegate you just need to open the file using Objective-C classes, see https://turbofuture.com/misc/How-To-Read-and-Write...

and loop through your file and insert into the database.

If you need actual code, let me know

Kevin


Sunny 4 years ago

Hi, can you send me the code?

my email is sunny_tan0627@livemail.tw

I need it, I had tried for days to insert data into sqlite but not successful, is there anyway to prevent xcode to create database by itself?

Thanks for sharing :)


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

I have sent you the code. I hope it helps.


marco 4 years ago

Hi, can you send me the code?

my email is 27@2727.eu i speak very little English .

thanks


Aric 4 years ago

Where i can find the full sample code project ? thanks


ariccheah 4 years ago

HI there, may I know where I can download the sample code for study it?

thanks...

Aric


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Aric, Ariccheah: Send me your e-mails and i send you the sample code

Marco, what version of the SDK are you using to run the app in the iOS Simulator: It has to be version 5. To fix your problem re-run the app in the iOS Simulator. I had a similar problem and this is what I have done and everything works fine.


Aric 4 years ago

Hi again, thanks for your reply, here my email, aric@epochhosting.com. can you dun post my email on the comment?

thanks a lot.

Aric / Aric Cheah.. is same person..lol


Aric 4 years ago

I no receive your code yet.. thanks...

regards

Aric


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Hey sorry Aric,

I didn't see your comment. I will send the code tomorrow since I will be away from my Mac until very late today.


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

Hi, Kevin, can you send me the sample code please?


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

sorry my e-mail is stefano.dicati@gmail.com


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

I have sent :)


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

Hi Kevin, thanks so much for the code, but i have some problem whit the code, he does not go, I can not do load the tableview with data from the database, why?


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Are you getting an error message? Trying setting the delegate for the table by dragging a connection from table to proxy object and select "delegate". I will run the app to see what the issue is.

Also what SDK / Simulator are you using, I have tested with 5.0.


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

The problem is that the - (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *) method is not called when the view is loaded when the back button is used to return tot he main view.


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

There is indeed a problem between the app and the SDK 5.1 and up. I will find a fix and update the tutorial with the new information.


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

Hi Kevin, my xcode is 4.2.1 and the simulator is 5.0. Xcode doesn't give me any error, the only problem is that doen't load the sqlite in to tableview, and can't see the nothing


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Yes because the table methods aren't being called for some reason when the "back" button is pressed, when you add new items. This was working fine with 5.0 and Xcode 4.1. I will do some research today and provide a fix a soon as possible. Sorry about this.


afridikhalid 4 years ago

can you please send me the code files

afridikhalid@yahoo.com

thanks in advance


afridikhalid 4 years ago

can you please send me the source code

afridikhalid@yahoo.com


TheCoding 4 years ago

Does this work with xcode 4.5? There is always an error at:

if(sqlite3_prepare_v2(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)

{

NSLog(@"sql not working correctly.");

return;

} ....


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

I haven't tried with 4.5. what is the error message?


TheCoding 4 years ago

There is never an error message. I'm using breakpoints and can see the NSLog messages: "SQL is not working correctly." Which I believe means there's something wrong with either the SQL statement (fails both on INSERT and on SELECT) or the rest of the prepare statement or the database. The code in the AppDelegate that creates the database and the table works correctly. It's frustrating that there's not more useful info available from the prepare statement. Or I should say, I've never been able to get more detailed information from the prepare statement.


pbox10 4 years ago

Hi, Kevin, can you send me the sample code please? pbox10@hotmail.com


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

thanks for the feedback. I have sent you a link to the source code


Chad Johnson 4 years ago

Hi Kevin, thanks for the tutorial. Could you also send me a link to the sample code? chad.d.johnson (at) gmail.com


TheCoding 4 years ago

There is always an error- by which I mean it is not successful. Do you have any advice for sqlite3_prepare_v2 problems?


TheCoding 4 years ago

In Code Listing 1 above, in the sqlite3_open statement there is a de-referenced pointer (db). Where does that pointer exist? Is it an instance sqlite3 pointer object in the AppDelegate? Or was it just forgotten in the code listing where it should be created locally within the scope of the method it appears in? Or ....


TheCoding 4 years ago

Kevin, I appreciate your tutorials. Could you send me a link to the source code?

Thanks, W

w chadwick (at) g mail dot com


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

TheCoding,

In the AppDelegate, replace the following code

if(!fileExist){

...

}

by

if(fileExist){

}

. This was causing an issue with the sqlStatement

Question 2: Yes, the db is in the AppDelegate

You can download the code form my web site iosdev101.com , it the DBUITablewview.zip under downloads


Ray 4 years ago

It's a great article. I followed every step but unfortunately I had fail to get it to work after several trys.

If you don't mind, please send me a copy of the source code, so I can try to trace what I had done wrong.

My email address is rl1215@hotmail.com . Thanks a lot in advance.


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Thanks...

You can download the source code from my web site here:

http://www.iosdev101.com/downloads

The file you need is : DBTUITableView.zip

There is bug in the app which I am working on. when you add new items, they don't appear in the table unless you restart the app. This bug appeared with v. 5.1 and later. I will post a fix as soon as possible.


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

thanks for your site Kevin but in CRUD tutorial there isn't the database can you upload?


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

I have a fix that i am testing for this tutorial. i will hopefully post the update by Friday this week.

for the crud, as this tutorial, you need to refresh the tableview with new data as it is added. I haven't look at that tutorial in a while. i will check later and add the uitableview refresh today and post the update.


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

I have fix the reload issue. Please review tutorial above. You can also download the source code from my web site:

http://www.iosdev101.com/downloads

The file to download is : DBTUITableView v2.zip


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

Hello Kevin, thanks for the tutorial again, I wanted to ask why the file ops.m no mention of the database todoDb.sqlite taskDb.db but we are talking about, in fact if I try to change the name of the database to TodoDb.sqlite read data does not load the tableview and in fact the report xcode me "BTUITableView [2827:11303] There is a problem with prepare statement" that is the message that you put to indicate the error, in the end I wanted to ask how I could directly read the database.

thanks


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Sorry about that. In the text, I put taskDb.sqlite, but in the actual code I have taskDb.db. Look at the downloaded source code. I am not getting any errors when I run the app and if you run the app as is, you will see my new entry in the database: "Fix this" when I entered on Thursday.

I don't have a tododb.sqlite in the code or tutorial, so I am not sure what you mentioning. Also since the db was created as taskDb.db in my project, then you must use that. Again I have made several test with no errors.

I will upload a video of running app ;)


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

Thank you very much Kevin, in fact I get errors if I change the name of the database todoDb.sqlite and I can not find the file in the project taskDb.db, where is it? and then if I find I can insert ourselves data directly in the database and read them in the app?


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

I am sorry to see that you are having problems. I don't understand because I have search the project source code for tododb.sqlite and didn't find anything. Did you download the new version of the source code.

The todoDb.sqlite doesn't exist. The taskDb.db is in the documents directory since this directly is writable. Try deleting the tododb.sqlite using NSFileManager, then in the AppDelegate, the database is taskDb.db and will be created in the documents directory.

I also added a video of the running app at the end of tutorial :)


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

I understand, but how would I go if I want to enter a database that is already written by me?


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

If you want to use my project as a starting point, I would first delete the existing database file using NSFileManager because you cannot browse the documents directory in the App sandbox.

Then create a new one in AppDelegate and also change the filename in DbOps. This should work. If it doesn't let me know I will try on my end and send you the source code.


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

Kevin, you're the best, thank you very much for the patience that brings about my questions may be trivial, but trust me they are not even a little, the bad is that I can not understand even changing suits and replacing parts the name of the database I can not see anything and I do not know why.


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

In finally how i can read the database sqlite whit storyboard?


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Vitranks,

I noticed that I had added a todo.sqlite in the resource folder. This database is useless and can be deleted. I didn't notice before. Open the AppDelegate and you will notice that I opted to create the database instead dynamically instead.

To read from the storyboard, you have to do in the corresponding view controller or tableviewcontroller that is associated with the controller in the storyboard.

First change the name in the AppDelegate in the didFinishWithOptions. A new database will be created. To remove the old database in the documents directory use this code, also in the AppDelegate:

NSFileManager *fmgr = [NSFileManager defaultManager] ;

NSArray *docPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

NSString *docDir = [docPath objectAtIndex:0];

NSString *fullPath = [docDir stringByAppendingPathComponent:[NSString stringWithFormat:@"%@.db", @"todoTask"]];

NSError *err=nil;

[fmgr removeItemAtPath:fullPath error:err];

You may have to do some tweaking since I didn't test this in Xcode but it should work as is.


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

ok well, and I'm asking if you read the database todoDb?


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Sorry Vetranks. Work has been way to hectic to do much. I hope you have figured things out.


Vetranks profile image

Vetranks 4 years ago from Shinjuku-ku, Tokyo, Japan

Hello Kevin, do not worry I also had a month of nightmare, and no, it could just be because I did not understand it and also because I've been busy.

thanks anyway


Iva 3 years ago

Hallo Kevin,

is it possible to insert into SQL only a value of UILabel?

This case is only for a text field, or?

NSString *insertSQL = [NSString stringWithFormat:

@"INSERT INTO todoTbl(todoName, todoDescription, todoDate) VALUES ('%@','%@','%@')",

todoName,todoDescription, todoDate];

Thank you very much!

Iva


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

You can insert a value from a UILAbel by creating an IBOutlet then accessing the text property of the label. Something like

@property (strong, nonatomic) IBOutlet UILabel *test;

NSString *try = [[NSString alloc] init];

try = self.test.text;


Stan 3 years ago

Hi Kevin,

would it be possible to send me the code at

stan4ev55@yahoo.com

It will be much appreciated!

thank you in advance!

Regards Stan


stan4olino 3 years ago from London UK

Hi Kevin,

would it be possible to send me the code at

stan4ev55@yahoo.com

It will be much appreciated!

thank you in advance!

Regards Stan


nix 3 years ago

can you provide the zip file of the entire working project to quickly tryout. copy pasting all the code is a pain for someone who wants to quickly see it working


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

Hi Stan & nix

You can download the code from www.iosdev101.com/downloads

thanks


stan4olino 3 years ago from London UK

thank you very much!!


phabrissse 3 years ago

Hi Kevin,

Once more great tutorial!

How to go back to tableview when save button is pressed?

Regards Kevin


phabrissse 3 years ago

- (IBAction)saveNewSkater:(id)sender {

[self.navigationController popViewControllerAnimated:YES];

}


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

Hi phabrisse,

Is the save button in the same UITableViewController or on another UIViewController. If you are using iOS6 you can use the unwind feature which will reload the UITableViewController from the stack. Here is a tutorial for that:

http://hubpages.com/technology/iOS-6-Storyboard-Un...

Kevin


durul profile image

durul 3 years ago from maryland, US

Hi Kevin,

I have two question. I would like to delete cell form tableview. you will see my code under below.

- (void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle

forRowAtIndexPath:(NSIndexPath *)indexPath

{

[todoArray removeObjectAtIndex:indexPath.row];

[tableView deleteRowsAtIndexPaths:[NSArray arrayWithObject:indexPath] withRowAnimation:UITableViewRowAnimationLeft];

}

But When ı close and re-open my application. My entry has still there. :(

it should be deleted. am I right ?

thanks


klanguedoc profile image

klanguedoc 3 years ago from Canada Author

Two possibly to try:

You have only removing the item from the table, not the data source. You need to remove from data source and refresh the table

You have to re-built the table index, like a refresh


seirra 2 years ago

seirra.smith@drake.edu

could i see the files?


klanguedoc profile image

klanguedoc 2 years ago from Canada Author

Please see www.iosdev101.com in the download page


Basvi 23 months ago

I download this code database file is missing

how can i solve this


klanguedoc profile image

klanguedoc 22 months ago from Canada Author

I can send later today.


ManishaParmar 22 months ago

Could you please send me the link from where can I find this code? I searched on iosdev101.com in downloads. I couldn't find it. You a doing a great help! Many thanks..


klanguedoc profile image

klanguedoc 20 months ago from Canada Author

Hi

the iosdev101.com web site was taken down. I will post the source code to gitHub over the next week.


muhamed 17 months ago

can you send me the code

this is my e-mail

muhamed_IT@yahoo.com


Guurpreet 16 months ago

Hi Klanguedoc ,

can you send me the database create code on ios applocation i am new in ios develoment in objective-c in my mail id . makkaar022@gmail.com

Thanks


Cheena 5 months ago

If i want to delete particular row from uitableview and database how can i do that


klanguedoc profile image

klanguedoc 5 months ago from Canada Author

Activate the delete button and implement the table view didSelectRowAtIndexPath function

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working