Flag This Hub

IOS 5 Tutorial: How-To Insert, Update, Delete with SQLite and Objective-C | iPhone | iPad | Apps

By

See all 2 photos

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) ?,?,?";

//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, [txt 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, [txt 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


Others are also reading...

Comments

mikeydcarroll67 4 months ago

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

klanguedoc 4 months ago

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.

mikeydcarroll67 4 months ago

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

klanguedoc 4 months ago

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.

mikeydcarroll67 4 months 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 4 months ago

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

mikeydcarroll67 4 months ago

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

klanguedoc 4 months ago

Do you mean in the SQL query?

mikeydcarroll67 4 months 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).

mikeydcarroll67 3 months 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 3 months ago

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: http://klanguedoc.hubpages.com/hub/IOS-5-SQLite-Ho

klanguedoc 3 months ago

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.

mikeydcarroll67 3 months 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 3 months ago

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?

mikeydcarroll67 3 months 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 3 months ago

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?

mikeydcarroll67 3 months ago

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

klanguedoc 3 months ago

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

Marc 3 months 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 3 months ago

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

Marc 3 months 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 3 months ago

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.

Marc 3 months 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 3 months ago

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

Marc 2 months 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!

Marc 2 months ago

I'm sorry, here is the screenshot lol:

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

klanguedoc 2 months ago

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 8 weeks ago

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 8 weeks ago

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

Alan 7 weeks 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 7 weeks ago

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

thinhpx 6 weeks ago

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

drop 3 weeks 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 3 weeks ago

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?

drop 3 weeks 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 3 weeks ago

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 2 weeks ago

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.

Submit a Comment
Members and Guests

Sign in or sign up and post using a hubpages account.



    Like this Hub?
    working