ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

iOS Tutorial: How-To Insert, Update, Delete with SQLite and Objective-C

Updated on January 31, 2017
klanguedoc profile image

Kevin is Software Developer with 20 years experience designing and building software applications including iOS and Android apps.

Book on How to Develop iOS 10 database apps using SQLite and Swift 3
Book on How to Develop iOS 10 database apps using SQLite and Swift 3 | Source

copyright 2014, klanguedoc

The SQLite database offers all the “standard” SQL constructs, including Select, Insert, Update and Delete. SQLite is an excellent solution for data persistence on IOS 5 devices: iPad, iPhone, iPod Touch. This tutorial demonstrates how to perform an insert, update and delete. For a detailed tutorial on creating, configuring a SQLite database for IOS 5 device and add it to your project and includes step by step code and instructions on how to select records from the database, read my tutorial: Tutorial on Creating an IOS 5 SQLite Database Application | IOS 5 | SQLite.

SQLite’s code in written in C, not Objective-C. C and Objective-C can be combined in the same Objective-C implementation files, or header files for that matter. For the SQLite parts of the code, you use C; for the IOS parts, you use Objective-C.

Overview of SQLite Operations


Like any other database, SQLite support where clauses when writing SQL statements for insert, updating, deleting and of course, selecting. You can also parametrized the value of the WHERE clause like you would do with other languages like Java and C#, or PHP.

Here is a snippet of SQL in SQLite with a parametrized WHERE clause. The “?” is replaced at runtime by the real value from a variable, field, method, etc. like any other program or database SQL statements.

Select col1, col2 from database where col1 = ?

What is different is how you pass the value to the statement. You need to bind the values to the statement. There are binding methods for each data type. The general syntax is:

int sqlite3_bind_datatype(sqlite3_stmt*, int, datatype);

Binding


Here is a list of binding methods for each supported data type.

  • Text : int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*), void(*));
  • Text16 : int sqlite3_bind_text16(sqlite3_stmt*, int, const char*, int n, void(*), void(*));
  • Null : int sqlite3_bind_text(sqlite3_stmt*, int);
  • Blob : int sqlite3_bind_blob(sqlite3_stmt*, int, const char*, int n, void(*), void(*));
  • Int : int sqlite3_bind_int(sqlite3_stmt*, int, int);
  • Int64 : int sqlite3_bind_int64(sqlite3_stmt*, int, int);
  • Double : Int64 : int sqlite3_bind_double(sqlite3_stmt*, int, double);
  • Value : int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value);
  • Zeroblob : int sqlite3_bind_blob(sqlite3_stmt*, int, int n);

Setting Up the CRUD Operations


Before getting into the actual operations of inserting, updating or deleting, we need to create an Objectice-C class to handle the operations and to do some housekeeping. Start by creating an Objective-C class, CrudOps.

In the header file define the following variables:
...
NSInteger dataId;
NSString *coltext;
NSInteger colint;
double coldbl;
NSFileManager *fileMgr;
NSString *homeDir;
NSString *title;


@property (nonatomic,retain) NSString *title;
@property (nonatomic,retain) NSString *coltext;
@property (nonatomic,retain) NSString *homeDir;
@property (nonatomic, assign) NSInteger dataId;
@property (nonatomic,assign) NSInteger colint;
@property (nonatomic, assign) double coldbl;
@property (nonatomic,retain) NSFileManager *fileMgr;

dataId, coltext, colint, coldbl represent the same columns as in the database. The fileMgr variable is to get the current path of the database in the Resource folder, homeDir is the Documents folder where we will copy the database because the Resource folder is always read-only. Finally the title variable is for an UIAlertView to display error messages.

Next configure the following methods for the crud operations:

-(void)CopyDbToDocumentsFolder;
-(NSString *) GetDocumentDirectory;
-(void)InsertRecords:(NSMutableString *)txt :(int) integer :(double) dbl;
-(void)UpdateRecords:(NSString *)txt :(NSMutableString *) utxt;
-(void)DeleteRecords:(NSString *)txt;

The CopyDbToDocumentFolder method will handle the copying of the database from the Resource folder to the Documents folder. Set the fileMgr object to the defaultManager. On the next line, define a dbPath NSString for the current database path. The copydbpath NSString is the variable for the target path to the Documents directory where we will copy the database.

Next we will use the removeItemAtPath method to delete the database from the Resource location to copy the database to the new location use the copyItemAtPath. If the copy method is unable to copy an alert is displayed to the user. Naturally this isn’t the best code, but is kept simplistic to demonstrate the operation. This is the code for the method:

...
-(void)CopyDbToDocumentsFolder{
NSError *err=nil;

fileMgr = [NSFileManager defaultManager];

NSString *dbpath = [[[NSBundle mainBundle] resourcePath]

stringByAppendingPathComponent:@"cruddb.sqlite"];


NSString *copydbpath = [self.GetDocumentDirectory

stringByAppendingPathComponent:@"cruddb.sqlite"];


[fileMgr removeItemAtPath:copydbpath error:&err];

if(![fileMgr copyItemAtPath:dbpath toPath:copydbpath error:&err])
{
UIAlertView *tellErr = [[UIAlertView alloc] initWithTitle:title message:@"Unable to copy

database." delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];

[tellErr show];

}

}
....

The GetDocumentDirectory will get the path of the Documents directory. It will create the directory if it doesn’t exist. The fileMgr object uses the NSHomeDirectory() method to get the home directory of the app.

-(NSString *)GetDocumentDirectory{
fileMgr = [NSFileManager defaultManager];
homeDir = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];

return homeDir;
}
….

Inserting Records


Performing an insert is quite straightforward. Again start by setting the fileMgr to the defaultManager. Declare variables for the sqlite3 statement and the database. Next declare a const, sql, for the SQL query string. Afterwards, open the database and pass in the sql3_statement object: stmt and the query string. One important point I need to make is about indexing the parameter statement. Notice the second parameter in each “bind” method, the first one has a 1, the second a 2 and the third a 3. This is to establish the order of the parameter statements. I know this might be obvious to most while not so obvious for others.

Bind the input values using the sql3_bind_text, sql3_bind_int and sql3_bind_double. Apply the query with the sqlite3_step method and finally call the the sqlite3_finalize and sqlite3_close methods respectively.

These are the basic steps to doing CRUD (create, update and delete) operations as you will notice when we look at the other methods.


-(void)InsertRecords:(NSMutableString *) txt :(int) integer :(double) dbl{
fileMgr = [NSFileManager defaultManager];
sqlite3_stmt *stmt=nil;
sqlite3 *cruddb;...


//insert
const char *sql = "INSERT INTO data(coltext, colint, coldouble) VALUES(?,?,?)";

//Open db
NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
sqlite3_open([cruddatabase UTF8String], &cruddb);
sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_int(stmt, 2, integer);
sqlite3_bind_double(stmt, 3, dbl);
sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlite3_close(cruddb);
}
...

Updating Records


Updating records is almost a repeat of inserting records. The only change is syntax of the query string, which is a very standard SQL query string for updating records.

-(void)UpdateRecords:(NSString *)txt :(NSMutableString *)utxt{

fileMgr = [NSFileManager defaultManager];
sqlite3_stmt *stmt=nil;
sqlite3 *cruddb;


//insert
const char *sql = "Update data set coltext=? where coltext=?";

//Open db
NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
sqlite3_open([cruddatabase UTF8String], &cruddb);
sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, [utxt UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlite3_close(cruddb);

}
….

Deleting Records


Again this is a repeat the first two; the only difference being the query string again.


-(void)DeleteRecords:(NSString *)txt{
fileMgr = [NSFileManager defaultManager];
sqlite3_stmt *stmt=nil;
sqlite3 *cruddb;

//insert
const char *sql = "Delete from data where coltext=?";

//Open db
NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
sqlite3_open([cruddatabase UTF8String], &cruddb);
sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlite3_close(cruddb);

}
….

UI Implementation


Now that we have our CRUD class and methods to allow us to perform CRUD operations, we will need to implement this functionality in our ViewController Scene (Figure 1). For the purposes of this tutorial, I created three fields and added an UISegementedControl to allow a user to choose the type of CRUD operation to do. By default the UISegmentedControl has two segements and it is set to Momentary. For our purposes, I will need to add an additional segment and change the type to: Bar. To make these changes, select the UISegmentedControl and open the Attributes Inspector and change the type to Bar and add an extra segment in the number of segements field. Finally select each segment in the combobox and rename them to Insert, Update and Delete.

Figure 1: App Layout and Output
Figure 1: App Layout and Output

Once the TextFields are setup, create a delegate for each field (Ctrl+left mouse button) to the ViewController object.

Next we will move to the kcbViewController header file to setup the IBOIutlets and IBActions and variables. Basically I created an UISegmentedControl variable, seg and I also created IBOutlets for each field on my ViewController and also one for the UISegmentedControl. I also created an IBAction for my UISegmentedControl on the ViewController. Take care when creating it to choose the UISegmentedControl as the Type and also change Action to IBAction, Click “Connect” create the connection.

Note: If you need to how to create connections and delegates, please read my other tutorials on performing these tasks. You will also find step by step instructions on how to make the keyboard close after you click on the return button. The links to the other tutorials are at the end of this tutorial.

In the kcbViewController header file
...
@interface kcbViewController : UIViewController
{
UISegmentedControl *seg;
}


@property (weak, nonatomic) IBOutlet UITextField *stringFld;
@property (weak, nonatomic) IBOutlet UITextField *intFld;
@property (weak, nonatomic) IBOutlet UITextField *doubleFld;
@property (weak, nonatomic) IBOutlet UISegmentedControl *seg;

- (IBAction)segButton:(id)sender;

In the implementation file, kcbViewController.m, I setup my setter or accessor methods using the @synthesize keyword. Finally I create a CrudOps object, called dbCrud and initialize it. Then I convert the value from textfield, self.stringFld.text to a NSMutableString so that it can be changed and also to match the input parameter of CRUD method sin my class.The rest of the code is pretty easy, I define a switch to detect which button is clicked on the UISegmentedControl using the selectedSegmentIndex and executing the corresponding method in the dbCrud object of the CrudOp class.

@synthesize stringFld;
@synthesize intFld;
@synthesize doubleFld;


- (IBAction)segButton:(id)sender {
CrudOp *dbCrud = [[CrudOp alloc] init];
NSMutableString *fldTxt = [NSMutableString stringWithString:self.stringFld.text];

switch(self.seg.selectedSegmentIndex)
{
case 0:
[dbCrud InsertRecords:fldTxt :[self.intFld.text intValue] :[self.doubleFld.text doubleValue]];
break;
case 1:
[dbCrud UpdateRecords:self.stringFld.text :fldTxt];

break;

case 2:
[dbCrud DeleteRecords:self.stringFld.text];
break;
}
}

Source Code

As usual here is the complete source code for the CrudOp class and ViewController class.

CrudOp.h

//
//  CrudOp.h
//  crud
//
//  Created by Kevin Languedoc on 11/29/11.
//  Copyright (c) 2011 kCodebook. All rights reserved.
//

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

@interface CrudOp : NSObject{
    NSInteger dataId;
    NSString *coltext;
    NSInteger colint;
    double coldbl;
    sqlite3 *db;
    NSFileManager *fileMgr;
    NSString *homeDir;
    NSString *title;
 

    

}
@property (nonatomic,retain) NSString *title;
@property (nonatomic,retain) NSString *coltext;
@property (nonatomic,retain) NSString *homeDir;
@property (nonatomic, assign) NSInteger dataId;
@property (nonatomic,assign) NSInteger colint;
@property (nonatomic, assign) double coldbl;
@property (nonatomic,retain) NSFileManager *fileMgr;

-(void)CopyDbToDocumentsFolder;
-(NSString *) GetDocumentDirectory;

-(void)InsertRecords:(NSMutableString *)txt :(int) integer :(double) dbl;
-(void)UpdateRecords:(NSString *)txt :(NSMutableString *) utxt;
-(void)DeleteRecords:(NSString *)txt;



@end

CrudOp.m

//
//  CrudOp.m
//  crud
//
//  Created by Kevin Languedoc on 11/29/11.
//  Copyright (c) 2011 kCodebook. All rights reserved.
//

#import "CrudOp.h"

@implementation CrudOp
@synthesize  coldbl;
@synthesize colint;
@synthesize coltext;
@synthesize dataId;
@synthesize fileMgr;
@synthesize homeDir;
@synthesize title;


-(NSString *)GetDocumentDirectory{
    fileMgr = [NSFileManager defaultManager];
    homeDir = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];
    
    return homeDir;
}

-(void)CopyDbToDocumentsFolder{
    NSError *err=nil;
   
    fileMgr = [NSFileManager defaultManager];
   
    NSString *dbpath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"cruddb.sqlite"]; 
    
    NSString *copydbpath = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
    
    [fileMgr removeItemAtPath:copydbpath error:&err];
    if(![fileMgr copyItemAtPath:dbpath toPath:copydbpath error:&err])
    {
        UIAlertView *tellErr = [[UIAlertView alloc] initWithTitle:title message:@"Unable to copy database." delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
        [tellErr show];

    }
               
}

-(void)InsertRecords:(NSMutableString *) txt :(int) integer :(double) dbl{
    fileMgr = [NSFileManager defaultManager];
    sqlite3_stmt *stmt=nil;
    sqlite3 *cruddb;
    
    
    //insert
    const char *sql = "Insert into data(coltext, colint, coldouble) ?,?,?";
    
    //Open db
    NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
    sqlite3_open([cruddatabase UTF8String], &cruddb);
    sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 2, integer);
    sqlite3_bind_double(stmt, 3, dbl);
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    sqlite3_close(cruddb);    
}
            
-(void)UpdateRecords:(NSString *)txt :(NSMutableString *)utxt{
    
    fileMgr = [NSFileManager defaultManager];
    sqlite3_stmt *stmt=nil;
    sqlite3 *cruddb;
    
    
    //insert
    const char *sql = "Update data set coltext=? where coltext=?";
    
    //Open db
    NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
    sqlite3_open([cruddatabase UTF8String], &cruddb);
    sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, [utxt UTF8String], -1, SQLITE_TRANSIENT);
  
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    sqlite3_close(cruddb);  
    
}
-(void)DeleteRecords:(NSString *)txt{
     fileMgr = [NSFileManager defaultManager];
    sqlite3_stmt *stmt=nil;
    sqlite3 *cruddb;
    
    //insert
    const char *sql = "Delete from data where coltext=?";
    
    //Open db
    NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
    sqlite3_open([cruddatabase UTF8String], &cruddb);
    sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);

    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    sqlite3_close(cruddb);  
    
}


@end

kcbViewController.h

//
//  kcbViewController.h
//  crud
//
//  Created by Kevin Languedoc on 11/29/11.
//  Copyright (c) 2011 kCodebook. All rights reserved.
//

#import <UIKit/UIKit.h>

@interface kcbViewController : UIViewController
{
    UISegmentedControl *seg;
}


@property (weak, nonatomic) IBOutlet UITextField *stringFld;
@property (weak, nonatomic) IBOutlet UITextField *intFld;
@property (weak, nonatomic) IBOutlet UITextField *doubleFld;
@property (weak, nonatomic) IBOutlet UISegmentedControl *seg;

- (IBAction)segButton:(id)sender;



@end

kcbViewController.m

//
//  kcbViewController.m
//  crud
//
//  Created by Kevin Languedoc on 11/29/11.
//  Copyright (c) 2011 kCodebook. All rights reserved.
//

#import "kcbViewController.h"
#import "CrudOp.h"

@implementation kcbViewController
@synthesize stringFld;
@synthesize intFld;
@synthesize doubleFld;


-(void)setSeg:(UISegmentedControl *)seg{
    
}
- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Release any cached data, images, etc that aren't in use.
}

#pragma mark - View lifecycle

- (void)viewDidLoad
{
    CrudOp *dbCrud = [[CrudOp alloc] init];
    [dbCrud CopyDbToDocumentsFolder];
    [super viewDidLoad];
	// Do any additional setup after loading the view, typically from a nib.
}

- (void)viewDidUnload
{
    [self setStringFld:nil];
    [self setIntFld:nil];
    [self setDoubleFld:nil];
    [super viewDidUnload];
    // Release any retained subviews of the main view.
    // e.g. self.myOutlet = nil;
}

- (void)viewWillAppear:(BOOL)animated
{
    [super viewWillAppear:animated];
}

- (void)viewDidAppear:(BOOL)animated
{
    [super viewDidAppear:animated];
}

- (void)viewWillDisappear:(BOOL)animated
{
	[super viewWillDisappear:animated];
}

- (void)viewDidDisappear:(BOOL)animated
{
	[super viewDidDisappear:animated];
}

- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation
{
    // Return YES for supported orientations
    return (interfaceOrientation != UIInterfaceOrientationPortraitUpsideDown);
}





- (IBAction)segButton:(id)sender {
    CrudOp *dbCrud = [[CrudOp alloc] init];
    NSMutableString *fldTxt = [NSMutableString stringWithString:self.stringFld.text];
    
    switch(self.seg.selectedSegmentIndex)
    {
        case 0:
            [dbCrud InsertRecords:fldTxt :[self.intFld.text intValue] :[self.doubleFld.text doubleValue]];
            break;
        case 1:
            [dbCrud UpdateRecords:self.stringFld.text :fldTxt];

            break;
            
        case 2:
            [dbCrud DeleteRecords:self.stringFld.text];
            break;
    }
}
@end

In Summary


Performing SQL operations using SQLite is very easy, once you know how to do it. SQLite offers a very handy data persistent store and is very easy to use. I hope you enjoyed this tutorial and found it useful.


Here are the links to my other IOS 5 tutorials that explain how to make connections, delegates. How to make the keyboard disappear after use.

Tutorial on Creating an IOS 5 SQLite Database Application | IOS 5 | SQLite

IOS 5 Storyboarding Tutorial using Segues | Scenes | View Controllers | Navigation

Quick Tutorial on Storyboarding with IOS 5

How-To Read and Write to Files with IOS 5 SDK (Objective-C) | iPad | iPhone | iPod Touch


Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      mikeydcarroll67 5 years ago

      I have a question. How could you adapt this to multiple databases?

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Hi Mike,

      I would create NSObject subclass, one of each database and one for the DAO NSObject subclass. I would then manage to write ops and the select ops from the DAO object using the same primary key or something similar for each database.

      Since the SQLite is not a server based technology, you more or less have to handle the read/write ops like using a flat file.

      Hope this helps. Keep me posted.

    • profile image

      mikeydcarroll67 5 years ago

      Ok. I will try that tonight or tomorrow and let you know more later.

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Great. You can also use the Attach method of the SQLite Connection to attach more than one database to the same connection. I have never used it. Apparently you can do joins between the two databases. Its worth a try. I will tried on my side and write tutorial on it. Good luck.

    • profile image

      mikeydcarroll67 5 years ago

      I think I can duplicate the basic files that you have given and then edit accordingly. Like have multiple files for the different databases. The thing that is bothering me is that CRUD and seeing if it will work with multiple files.

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      You should be able to write the CRUD like any other relational:

      update db

      set field = db2.field

      from db, db2

      where db.field1 = 'blala', db2.field2 = 'text'

      or a select

      select db.fielda, db2.fieldb from db, db2

      where db.fieldc = 'some value' and db2.fieldc='some value

    • profile image

      mikeydcarroll67 5 years ago

      would it be possible to be able to arrange the setInt, setString to those found within the database?

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Do you mean in the SQL query?

    • profile image

      mikeydcarroll67 5 years ago

      yea. I am trying to make a language app so they need to be able to insert certain characters and I think the default (or what you have shown might confuse them).

    • profile image

      mikeydcarroll67 5 years ago

      http://www.iphonesdkarticles.com/2008/10/sqlite-tu... I was thinking something like this link but couldn't figure out how to add it to your tutorials

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      I have finish writing tutorial on multi databases. In the tutorial I show how to take data from the UI and insert it into a couple of databases, but you could use the code for one database. This is new tutorial: https://hubpages.com/technology/IOS-5-SQLite-How-T...

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Mike I have looked at the other tutorial., it seems to provide similar information. I am trying to grasp what you are trying to do. Are you trying to enter data from an UI and store that information through a loop using a custom object? I am currently writing a tutorial, as someone else's request to display data from a NSArray and display that information in a UITableView. The data will added to a NSArray from data in a SQLite database. I should have this done by wednesday.

    • profile image

      mikeydcarroll67 5 years ago

      I keep getting errors on this. I have 3 string fields. I need to have a field for them to post a chinese character, the romanization for it and then the definition (all three are NSString fields, so I didn't need the integer or the doublevalue). But I keep getting an error on the InsertRecords portion of the ViewController. My code (where error is produced): case 0:

      [dbCharacter InsertRecords:fldTxt :[self.characterFld.text] :[self.pinyinFld.text]:[self.definitionFld.text]];

      break;

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Is the chinese character actually text. Did you try a sqlite3_column_bytes or a sqlite3_column_blob. What is the error you are getting?

    • profile image

      mikeydcarroll67 5 years ago

      The character is actually text. The error: expected ']' right where the insert statement is on the line. I am not sure what to put right after the .text in each set of brackets.

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Would it be possible to post the actual code where the error occurs? Sometimes two sets of eyes are better than one, as the saying goes. Maybe I can spot something. From above, what is the fldtxt param in your insertRecords instance method?

    • profile image

      mikeydcarroll67 5 years ago

      I am not exactly sure I would have to look back through it

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Have you tried removing the brackets; Have you tried storing the values in instance variables and passing these to your instance method?

    • profile image

      Marc 5 years ago

      Hey! I have a question, when do you use the CopyDbToDocumentsFolder method? Its decelerated and implemented but you are not using it :S

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Marc 87,

      Thanks for catching that! I had the code in the AppDelegate in the didFinishLaunchingWithOptions method, which I forgot to include in the tutorial. But you can also add the copy method to the viewDidLoad method, see above and will also work.

      Thanks

      Kevin

    • profile image

      Marc 5 years ago

      Thnks kevin.

      I followed your tutorial but on my own way, i used my own db, that have an id(integer), username,password and email(STRINGS). I didn't use your UISegementedControl, i used a UIBUTTON to add and one to delete. I don't know why it doesn't insert and it doesn't delete users to my database. I think is something related with the homeDir = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];, but I'm not sure. I have a lot of breakpoints and NSLogs to look what happens in each moment, but i have no idea why it doesn't insert and delete.

      I have my login.sqlite(is the name of my db) in the path: /Documents/Proyectos iPhone/SQL/SQL, i have to change the NSHomeDirectory()?

      I used the NSLog(dbpath); in the copyDbtoDocumentsFolder to see in which path is saving my login.sqlite, and it is saved in the /Users/Marc/Library/Application Support/iPhone Simulator/5.0/Applications/92738AD9-B0EC-4C55-9DC5-B95CCB955E1E/SQL.app/login.sqlite, and when i open it with the sqlite manager, that database is empty. Do you have any idea what could be happening?

      Sorry for my english, I'm spanish ;)

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Your english is perfect!

      Are you sure that it is actually writing to the login.sqlite in the app container path and not in the resource path. Because if its in the Resource path, you won't necessarily get an error message but the resource path is read only.

    • profile image

      Marc 5 years ago

      My login.sqlite is in the supporting files. I have the path SQL, the path MODEL, the path FRAMEWORKS... Where i have to put the login.sqlite??

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      The database has to go into the Documents directory in the app container for it to be writeable

    • profile image

      Marc 5 years ago

      Klanguedoc i did an screenshot to show you where i have my login.sqlite, because i don't understand u when u say to put it on the app container to be writeable(i don't know where is that). I hope with the screenshot you could say me where exactly has to go my database ;). Thanks!

    • profile image

      Marc 5 years ago

      I'm sorry, here is the screenshot lol:

      http://i39.tinypic.com/2mdruqx.png

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Hi Marc,

      You have to copy the database using the code. Use the -(void)CopyDbToDocumentsFolder{

      NSError *err=nil;

      fileMgr = [NSFileManager defaultManager];

      NSString *dbpath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"cruddb.sqlite"];

      NSString *copydbpath = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];

      [fileMgr removeItemAtPath:copydbpath error:&err];

      if(![fileMgr copyItemAtPath:dbpath toPath:copydbpath error:&err])

      {

      UIAlertView *tellErr = [[UIAlertView alloc] initWithTitle:title message:@"Unable to copy database." delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];

      [tellErr show];

      }

      }

      to copy sqlite to the container folder for your app. when you run the app, you can see the path in NSlog.

    • Krisscross90 profile image

      Krisscross90 5 years ago from Belfast, United Kingdom

      Hi, I've followed your tutorial but omitted the delete and update functions as I'm looking to only insert fields into my database (registration information).

      I've tried to implement a single "save" button instead of the segmented control but it's not working (also I haven't really got a clue what I'm doing)...

      Could you tell me a) how I add a save button instead of the segmented control to insert the records into the db and b) how I would go about hosting the db on a web server so that whatever the user enters in the registration page will get saved remotely. I've been researching and it seems php and json is used to do this? I'm just not 100% on how to do this.

      I'm new to iOS development and starting to panic as I've chosen to create an app as my major project for my university course. I've only 5 weeks left now to complete it.

      Any help would be greatly appreciated :)

      Thanks,

      Kristina

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Hi Kristina,

      I feel your pain :)

      1-For the Button, drag a UIButton on to the canvas

      2-Open the Assistant Editor and control drag a connection to the header file

      3-Change the connection type to : Action

      4- Give the IBAction a name in the field

      5- Accept the other defaults and click the Connect button

      6-In the IBAction method, add your code to perform an insert

      For the web server, SQLite is not designed for that type of operation. It is strictly a local storage facility. For web server ops, you need a database server like MySQL, SQL Server or whatever your hosting company is offering or your University. Then you would need to implement a web service to send the information to the database on the web server or create a java servlet

      The web service could be in whatever language your University supports like Java or .Net, PHP

      On the iOS app, you would need to implement a NSURL and NSURLRequest to POST the data or check out the CFNetServices. But if you can create or have access to a Java Web Server, I would recommend a Java Servlet to use the NSUrlRequest to perform a POST. You can ever get callbacks. PHP and .Net are equally easy to work with for HttpRequest ops.

      Hope this helps and i wish you lots of success with your major

      Kevin

    • profile image

      Alan 5 years ago

      I noticed that a number of your CURD methods use NSMutableString rather NSString. Wouldn't it be better to use NSString since the string is not being manipulated in the method?

      Have you looked at https://github.com/ziminji/objective-c-sql-query-b... for building and executing SQLite statements? It handles all of the same functionality that you are describing, including the ability to copy the database over to the document directory.

      Also, how do indicate the order of parameters to be binded to the SQL statement being executed using the following implementation?

    • klanguedoc profile image
      Author

      Kevin Languedoc 5 years ago from Canada

      Hi Alan,

      Yes there are a number of utility frameworks that allow to work with SQLite from Objective-C but this wasn't the intention with this tutorial. Rather I wanted to demonstrate how to work with the SQLite library. I tend to stick to the frameworks that are supported by Apple when writing tutorials.

      For the parameters binding order. the second argument is the parameter index as below. They must be in the same order was the placeholders in the sql query string in the sqlstatement.

      sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);

      sqlite3_bind_text(stmt, 2, [txt UTF8String], -1, SQLITE_TRANSIENT);

      sqlite3_bind_text(stmt, 3, [txt UTF8String], -1, SQLITE_TRANSIENT);

    • profile image

      thinhpx 5 years ago

      Hello, I've followed your tutorial but Errol.Please,send me your code through email.My email: thinhpx91@gmail.com .Thanks

    • profile image

      drop 4 years ago

      Hi ,

      unfortunately I can't get the code to work properly. When I try to insert something, it won't. It does not give me any errors or anything. I have a question though: Why do you delete the cruddb.sqlite file each time in the (void)CopyDbToDocumentsFolder method? Don't we want use it as a database. So if we each time delete it and the recopy it, won't we lose the data we inserted into that file a minute ago?

      Thanks for the tutorial and your help in advance.

      Cheers drop.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      The delete is to remove from resource folder. Actually, this part of the code should be in the app Delegate and you should add some code to see if the database is already in the document folder.

      As for saving, I have noticed since upgrading the version of iOS my inserts are no longer working. I will have to investigate the header files or the forums to see if this is a trend (I hope not).

      Have you tried FMDB, which is a Objective-C lib for SQLite?

    • profile image

      drop 4 years ago

      I found a solution to entering new data. Instead for binding the variable one only needs them to put them into a NSString withStringFromat: In your example it would be:

      NSString *sql = [NSString stringWithFormat: @"INSERT INTO data(coltxt, colint, coldbl) VALUES ('%@','%i','%g')", txt, integer, dbl];

      const char *insert_stmt = [sql UTF8String];

      //Open db

      NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];

      sqlite3_open([cruddatabase UTF8String], &cruddb);

      sqlite3_prepare_v2(cruddb, insert_stmt, -1, &stmt, NULL);

      I hope this helps. I put the (void)CopyDbToDocumentsFolder method with all the crud methods into one file and surrounded it with a if else statement, so that when the file exist he does nothing, else he copies it. Do you think that approach is fine? It works fine in my app, but could any issues arise? If I understood it correctly, we put the file into the documents folder because only it that folder it is editable right?

      Thanks again for the great tutorial. Please continue being this productive. Without guys like you that take their free time to teach people free I would never be able to create an app.

      Thanks again.

      drop

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      hi drop,

      Nice but for the db copy, (and I will update this tutorial accordingly) is to put in the AppDelegate and check if the file already exist in the document folder. But I like your solution. There seems to be a lot (I am following discussions in different forums) of problems with inserts with 5.0 and more so with 5.1. I will try your suggestion later today and update tutorials accordingly. thanks

      Kevin

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Btw the original code is, was working. i had a problem with column sequencing; it should read 0,1,2, and not 1,2,3. Also I moved the db create to the AppDelegate. I will update the code and I have tried your suggestion for the insert statement which i like very much. Also remove the recordCount query as it is useless as I have replaced with a char variable to check for cString = null issues.

    • profile image

      rozmichelle 4 years ago

      Hello! Thanks for your tutorial. it's very helpful.

      Question about the UpdateRecords:: method that you have posted here. You wrote:

      sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);

      sqlite3_bind_text(stmt, 2, [txt UTF8String], -1, SQLITE_TRANSIENT);

      But it looks like this won't update the database at all. Technically it does, but it seems like you are setting the original text column to equal itself... Didn't you mean to make the first statement use the utxt variable instead of using the txt variable both times?

      Also, I see that the comments in the thread suggest passing the info as raw data to the sql query. I don't think this is a good idea. It allows injection-like errors and will cause problems if apostrophes are inserted into the table and then fetched or tested for removal later. I believe binding is always the way to go.

      Thanks!

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Thanks rozmichelle for catching that.

      This tutorial is in need of a revision which I will work on today. Thanks again and sorry if I lead you astray.

      Kevin

    • profile image

      rozmichelle 4 years ago

      Awesome to hear!!! I'm actually going to see if I can implement your CRUD class today. If I get stuck, I'll be sure to look out for updates to this tutorial. I learned so much from it.

      :)

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Thanks, you can also check out this tutorial that I wrote:

      https://hubpages.com/technology/iOS-5-How-To-Displ...

    • profile image

      Ingila 4 years ago

      Hi. Can you please post the same tutorial for tab bar controller? One view to save data and the other one to retrieve that in table view?

    • profile image

      TechieWidget 4 years ago

      Hi.

      I really appreciate the tutorial a lot. :D

      I have been really trying to understand the tutorials works, but I haven't been able to make it successfully work. I also tried implementing the UpdateRecords function but it doesn't seem to change any of the values in my sqlite file.

      Would it be possible for you to send me your code and the database for this tutorial? My email is nkc.thomas@gmail.com. I would greatly appreciate it if you can, and I think I would understand better if I look at both the database and the code.

      Thanks!

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Yes of course!. It would be my pleasure to send the source code to you.

      Kevin

    • profile image

      Andres 4 years ago

      Hello thank you very much for the tutorial I have a small problem is that selecting either option from the Control segmented the Console says "unrecognized selector instance 0x681dfc0 sat to" you could help me please thanks

      apologize for my bad English

    • profile image

      MissingHiker 4 years ago

      Which version of the db file is it supposed to be updating, the one in the bundle, or the one in the documents folder?

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      MissingHiker,

      The one in the documents folder. The one in the bundle is read-only

      Andres,

      Did you create an IBOutlet? or did you change the name of the IBOutlet for the segmented control?

    • profile image

      MissingHiker 4 years ago

      Drat. OK, I've been tinkering with this all week. My code doesn't give me errors, but it doesn't update the copy of the database in the documents folder, either.

      The program runs fine, but I wanted to make sure it was doing what it looked like it was doing, and look at the updated database to see my new record in there. I go into the application folder in Finder under iPhone Simulator/6.0/Applications/595D363B-0569-4AC8-B66F-FCC493C1E055/Documents and I copy out the cruddb.sqlite to my desktop and open it in SQLite Manager and it doesn't contain the record I inserted. And the timestamp on the file is old, too.

      Has anyone else gone through this and what did they do to fix it? I've been googling but to no avail.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      So in essence you are not writing to the cruddb.sqlite in the Documents folder. You won't never get an error for writing to a read-only database (Resource folder). I start the app and try.

    • profile image

      MissingHiker 4 years ago

      Well, I'm NSLogging the cruddatabase variable to verify that it is actually working on the one in the Documents folder and it is.

      I actually thought of the permissions issue and set the permissions in both the folder and on the file itself to read and write.

      I read somewhere that a program can't update a database the program itself didn't create. I don't believe that to be true, but at this point I'll try anything so I deleted all my cruddb.sqlite files, and added code that would create a database and add your table to it if no cruddb.sqlite file is found. It does create a file in the documents folder, but it just makes it zero bytes. And when I try to add to it, the same thing happens, it says it added it, and then the file just sits there at zero bytes.

    • profile image

      MissingHiker 4 years ago

      OK, hold on, boy do I feel stupid. I was trying to insert data into a column called "coldouble," but when I made the database in SQLite Manager, I called the column "coldbl." *sigh*.

      It just can't be underscored enough, and klanguedoc, you hit it but it still managed to clang off my head (no pun intended, sorry), SQLite seems to be verrrrry secretive about errors. I'm just a beginner but from what I can tell, when SQL can't do something, instead of throwing a flag, it just does the "best that it can," and doesn't make much of a fuss out of it, leaving you with unexpected results that you don't know how you got. I'm not sure that's right, but that's the vibe I'm getting.

      Ya gotta really take advantage of NSError &err and NSLog those all over the place while you're debugging, and put break points in and look at the error variables in the console. And look reeeeeal close or you'll wind up like a dork like me wasting ohhhh so much time.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      I love your comments, very informative and useful. NSError and NSLog are indispensable. To keep the examples simple enough, I don't always add all the necessary error checking that an app requires in production.

    • profile image

      AramDIV 4 years ago

      Hello guys I have an error , please help me

      "Error Domain=NSCocoaErrorDomain Code=260 "The operation couldn’t be completed. (Cocoa error 260.)" UserInfo=0x7551610 {NSFilePath=/Users/arammusaelyan/Library/Application Support/iPhone Simulator/6.0/Applications/701CC684-6DF1-438B-9A28-2DEA0F2D7017/CRUD.app/cruddb.sqlite, NSUnderlyingError=0x754c790 "The operation couldn’t be completed. No such file or directory"}"

      It seems some problem with path "Applications/701CC684-6DF1-438B-9A28-2DEA0F2D7017/CRUD.app/cruddb.sqlite" , but the thing that the cruddb.sqlite file exist in my project

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      you are trying to access the database in your code at one location but the database is at another location (if that makes sense). You are trying to access the database in the Resource. Is it really there? or is it just a reference to a file that is elsewhere on your mac?

    • profile image

      AramDIV 4 years ago

      Hello ! :) ... concerning DB error I have fixed ( I did't copy DB file properly) .... But there are another error which have already discussed above " [dbCrud InsertRecird:fldTxt :[self.intFld.text intValue] :[self.doubleFld.text doubleValue]]; " .. the error msg is "-[CrudOps InsertRecird:::]: unrecognized selector sent to instance 0x75ba250" ... what I should do or give me hint for solving it .. Thanks a lot !...

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      This error means that method or object being sent is not the one the project is looking for. From the above msg: maybe InsertRecird should be InsertRecord instead

    • profile image

      AramDIV 4 years ago

      Hello again ! :) ... I have renamed the name of function "InsertRecord" ... but still error is available " -[CrudOps InsertRecord:::]: unrecognized selector sent to instance 0x75a41f0 "

    • profile image

      AramDIV 4 years ago

      sorry .. I fixed it :) ...

    • profile image

      Samuel 4 years ago

      Hello everybody ... the program works perfect ... but then I open cruddb.sqlite("Library/Application Support/iPhone Simulator/6.0/Applications/701CC684-6DF1-438B-9A28-2DEA0F2D7017/Documents/cruddb.sqlite") file by firefox SQLite Manager , the table is empty .. why data is not filled in DB ? ....... Thanks !

    • profile image

      Samuel 4 years ago

      Hello again .... I find out that sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL) dose not execute :(

    • profile image

      ebrahim elgeded 4 years ago

      it Does not work

    • profile image

      ebrahim elgeded 4 years ago

      it Does not work

    • profile image

      Joshua 4 years ago

      Hi.

      I'm very new to iOS development and I would like to thank you for your tutorials. They really helped me out a lot on the project I'm working on, specifically: Tutorial on Creating an IOS 5 SQLite Database Application IOS 5 SQLite.

      Anyway, I have been trying to understand how this works for 3 days now but I haven't been able to make it successfully work. I'm having a hard time understanding the CopyDbToDocumentsFolder and GetDocumentDirectory methods and I'm only trying to implement UpdateRecords but I can't seem to get it right. The says that it is unable to locate the database file.

      Would it be possible for you to send me project for this tutorial? I would greatly appreciate it if you can cause I'll be able to properly scan and study the entire project and hopefully understand those methods. My email is anarki_joshua@yahoo.com.

      Again, Thanks You!

    • profile image

      Joshua 4 years ago

      *...Thank You!

    • profile image

      Omid 4 years ago

      hi ...thanks for your good tutorials ... so helpful ... i have a similar problem with Joshua .. i copied the .sqlite file into my project and the code is not working ...i am not able to update or insert any record into my database file ... i would appreciate any help regarding this ...

      Thank you.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Hi Guys,

      Thanks for great feedback. The most common problem either not copying the db properly not not writing to the proper db version once copied. Anyway, you can download the source code from this url:

      http://www.iosdev101.com/downloads

      It is crud.zip

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      If you copy the db but are still insert records into the version in the resource folder, you won't get an error but the records won't be saved.

    • profile image

      Roger Clark 4 years ago

      Hi,

      I downloaded the example code, but it didn't work.

      cduddb.sqlite wasn't included in the zip so I made my own.

      Also.

      In MainStoryboard UISegmentedControl "insert update delete" has a "value changed" callback linked to kcb:segcrud which doesn't exist.

      If you remove this linkage, it does "Insert" records, but neither Update or Delete work,this is because there is no linkage between the UISegmentedControl and the seg property in KCB

      However there is no need for this property as the "sender" for segButton is the UISegmentedControl, so its easier just to cast sender to UISegmentedControl * in the switch statement.

      Using segmented control is slighly problematic as it wont let you do multiple Inserts or Updates etc because segButton only gets called on "value changed". Perhaps separate buttons would be better.

      At the moment neither update or delete are working for me, but I'm not entirely sure why :-(

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Roger,

      Sorry to hear you are having issues. Let me run the sample code since it has been a while and see what the issue is.

    • profile image

      guntarion 4 years ago

      klanguedoc, your objective-C tutorial is so marvelous. Thank you, you really help us a lot! :-) And I even amazed how you master many subjects beside objective C.

      I have question, Sir: where do the generated .sql file is actually located in the harddrive or on the iOS device? let's say I want to edit the content via external tool.

    • klanguedoc profile image
      Author

      Kevin Languedoc 4 years ago from Canada

      Hi Guntarion,

      Thanks for the feedback. I write about technology I use through the course of my work and projects. I have this tutorial on how to locate the documents directory:

      https://hubpages.com/technology/Locate-iOS-iPhone-...

      Hope this helps

      Kevin

    • profile image

      saravanan 4 years ago

      hi!!!! i am new for ios development..... how can d o this project can any one help to me.... ple......

    • profile image

      Seb.FCT 3 years ago

      Hi klanguedoc,

      I read many of your tutorials, and I really want to thank you for them. Your blog is probably one of the best I found since your tutorials are really easy to understand, but also because you are awnsering to everyone actively.

      Be sure I will recommend this blog to my friends.

      Sincerely,

      Sebastien

    • profile image

      Sanjay Belani 3 years ago

      Hi, I did everything and I'm getting this error when I click any button (Insert, update, delete)

      Terminating app due to uncaught exception 'NSInvalidArgumentException', reason: '-[kcbViewController segCrud:]: unrecognized selector sent to instance 0x7191120'

      However, it shows build succeeded. Thank you so much

    • klanguedoc profile image
      Author

      Kevin Languedoc 3 years ago from Canada

      Thanks Seb.FCT. I was away on some big dev projects and didn't have the time or energy to put any effort into these tutorials. I am sorry if I let anyone down. I should be able to get back to writing soon.

      Thanks for the support....

      Sanjay,

      You have an object that is not properly initialized. This is a standard error.

      Review your code

      Kevin

    • profile image

      Tiwi 3 years ago

      Hi klanguedoc,

      Your tutorial rocks! Thank you for providing such a great tutorial. However, I have a quick question. Since I only updated the sqlite file in the "Documents" folder, not in the main bundle, Does this mean any changes I made will be lost when the app is closed? Is there any way to save it, and have a local database?

      I tried to overwrite the main bundle's sqlite file with the Documents' one, but it doesn't seem to work..

      Thanks

    • klanguedoc profile image
      Author

      Kevin Languedoc 3 years ago from Canada

      No as long as you are writing to the database in the Documents folder you should be ok

    • profile image

      Ramandeep Singh 2 years ago

      Hi I am getting an alert "Unabel to copy the database".why is it so??

    • klanguedoc profile image
      Author

      Kevin Languedoc 2 years ago from Canada

      Not sure. Is it open?

    • profile image

      Sam 23 months ago

      program nt executing

    • klanguedoc profile image
      Author

      Kevin Languedoc 23 months ago from Canada

      Ramandeep

      Have you managed to copy db?

      Sam

      Are you getting error messages

    • profile image

      SuziQ 2 months ago

      Hi Kevin, I sent Apress an email and also one to you via this post about your book, I've been having problems building the example code and wondered if you would be able to let me know if there is an errata to the book. I only bought it a couple of weeks ago.

    Click to Rate This Article