iOS | SQLite | How-To Create a Multi Database iOS App with SQLite

SQLite 3 provides the Attach Database statement for multi database queries. Many IOS 5 apps use multiple databases, although you could create manage multiple database connections via an Objective-C DAO class, it is much more efficient to use SQLite’s API.

The preceding function is part of the sqlite_stmt API. The value of exp is the filename of your database file. The filename parameter uses the same semantics as the sqlite_open command meaning the database filename must jave an UTF-8 encoding:

SQLite 3 C API for opening a database
SQLite 3 C API for opening a database

To open a SQLite3 database in Objective-C you would need to first define your database filename and its path in the app container as a NSString. Then use the sqlite3_open function passing in the database path as an UTF8String:

Parsing the SQLite database path to the open command
Parsing the SQLite database path to the open command

Similarly, to attach a database using the sqlite3 C API in Objective-C declare the name and path of the db.sqlite file. Pass this value to the attach database statement. You could write the code something like this:

Boilerplate code to define SQLite database paths
Boilerplate code to define SQLite database paths

After initializing the NSString objects and setting their paths, you could open and attach the databases using code similar to this:

Sample attach database statement
Sample attach database statement

Attaching a second or more databases is really only a SQL statement. The attach statement requires the “ attach database” command followed by the filename (including the full path) enclosed in single quotes. The “AS” database_ alias completes the statement.

Build This App

To test this concept out I have created a simple Single View app with a couple of fields and buttons to write and read to and from the databases. The first three fields will write to the database; the other fields will read from the databases.

UI Prototype
UI Prototype

If you need to know how to create a SQLite database and set it up in your project, read these tutorials:

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

and this tutorial demonstrates how to perform CRUD operations (Insert, Update and Delete).

IOS 5 SDK Database: Insert, Update, Delete with SQLite and Objective-C | C | How-To

Since these tutorials go into detail how to setup and use SQLite in an IOS 5 project and also to implement CRUD operations in detail I won’t repeat them here.

Furthermore if you need assistance to create the ViewController, the fields, the corresponding IBAction and IBOutlet connections, I invite you to read the following tutorials:

Quick Tutorial on Storyboarding with IOS 5

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

SQLite Databases

Create your databases using Firefox SQLite Manager:

  • multiDb.sqlite
  • otherDb.sqlite

Create a Single View Project in Xcode

  • multiDb

Add databases to your project

  • Read tutorials above

Add libs

  • Read tutorials above

The first database, multiDb, contains one table: table1 with two columns: name and gender. The second database, otherDb, also contains a table, table1 with a name column as a primary key and another column named: city.

Person Class
This Person sub class will handle the data the databases and in the UI. It is a simple data type subclassed from the NSObject class.

Create a the NSObject subclass, Person (.h and .m). In the Person header file, add the following instance variables:

Person header
Person header

Declare the same instance variables in the implementation file:

Person implementation
Person implementation

MultiDAO Class

This class will handle the database operations. It includes a method to read the data from the two tables, SelectRecords and another to insert records: InsertRecords. Four variables will be added :

MultiDAO header code
MultiDAO header code

In the implementation file first you will need to import the Person header file and create the accessor fileMgr and homeDir properties.

MultiDAO Implementation - Variables
MultiDAO Implementation - Variables

Add the SelectRecords method implementation:

MultiDAO implementation - SelectRecords
MultiDAO implementation - SelectRecords

The method declares a char constant, sql, for the SQL statement. Notice the syntax, it is just like any other multi table or multi database join, without the join statement. The other important piece of information is the naming scheme of the first database, it is always “main” and any other database that is attached is referred to by the alias name that was used in the attach statement.

The rest of the code is boilerplate sqlite3 commands that follows the proper lifecycle. If the prepare statement is parsed correctly, you can then prepare the input variables, execute the step to move to the first row. Finally call the finalize command to remove the sql statement and close the database.

The InsertRecords uses the Person parameter to insert records in each of the databases.

MultiDAO implementation - InsertRecords
MultiDAO implementation - InsertRecords

DatabaseOperations

This method is the main center of operations. It starts by getting the path to both databases and then moving them to the Documents directory so that the app can write to them, otherwise, if they remain in the Resource path, they will be read-only.

MultiDAO implementation - DatabaseOperations
MultiDAO implementation - DatabaseOperations

Once the databases are in the proper directory, the method attempts to open the first, or main database. If all goes well, a new query statement to attach the second database is created before being executed with the sqlite3_prepare_v2 command. If the operation is successful, the sqlite3_step is performed and the sqlite3_finalize to executed to complete the execution of the attach database statement. The next bit of business is to determine which operation to perform, “Insert” or “Select” based on the “operationName” parameter that is received from the QueryViewController which we will look at next.

QueryViewController

Depending on the type of template you used or will to create the sample app if you intend on testing the code and concepts in this tutorial, the ViewController may or may not be created. I chose to use an Empty Solution and then create the ViewController and Storyboard separately. The QueryViewController which I created as a sub class of the UIViewController class will handle the interaction with the UI. Again review the included links to create the ViewController. For the Storyboard, I create a new one from the Storyboard template and add three fields for the name, gender and city. I also added two buttons: Save and Query to perform the insert and select functions. Finally I add four more fields to display data from the database and one to enter a query value:

QueryViewController - UI layout
QueryViewController - UI layout

Since I created the Storyboard from the template, I needed to add it to the “MultiDB-Info.plist” file under the Supporting Files folder. In the opened file, right-click and select “Add Row”, then choose “Main storyboard file base name” from the list of attributes. For the value I entered multiDb, which is the name of the Storyboard file. You don’t have to enter the file extension.

In the open Storyboard, I added a reference to the custom ViewController class that I created. In the QueryViewController header file proper I added the following instance variables and methods:

QueryViewController - header
QueryViewController - header

The IBActions and IBOutlets were created by creating connections, see tutorials above to the header file. In the implementation, as is standard practice, I added the code for the SaveAction and QueryAction inclusively:

QueryViewController - SaveAction and SectionRecords
QueryViewController - SaveAction and SectionRecords

In the SaveAction method I demonstrated three different ways to assign a value to the “me” object.

QueryViewController - SelectRecords
QueryViewController - SelectRecords

In Summary


This turned out to be a bit longer than expected, but I wanted to show how the different pieces fitted together to create a CRUD app using multiple SQLite databases. Attaching databases is often used with apps with heavy data loads. I hope that the tutorial answered some question and will make using multiple SQLite databases easier to implement.

The Source Code


The source files for the MultiDAO sub class are included for your convenience.

Person.h

//
//  Person.h
//  MultiDB
//
//  Created by Kevin Languedoc on 1/26/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import <Foundation/Foundation.h>

@interface Person : NSObject{
    NSString * name;
    NSString * gender;
    NSString * fromWhere;
}

@property(nonatomic, retain) NSString * name;
@property(nonatomic, retain) NSString * gender;
@property(nonatomic, retain) NSString * fromWhere;
                             

@end

Person.m

//
//  Person.m
//  MultiDB
//
//  Created by Kevin Languedoc on 1/26/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import "Person.h"

@implementation Person

@synthesize name;
@synthesize gender;
@synthesize fromWhere;



@end

MultiDAO.h

//
//  MultiDAO.h
//  MultiDB
//
//  Created by Kevin Languedoc on 1/25/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

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

@interface MultiDAO : NSObject{
     sqlite3 *db;
     NSFileManager *fileMgr;
     NSString *homeDir;
     Person * people;
}



@property (nonatomic,retain) NSString *fileMgr;
@property (nonatomic,retain) NSString *homeDir;
-(void)databaseOperations:(NSString *)OperationName:(Person *) person;

-(Person *) selectRecords:(Person *)person;
-(void) insertRecords:(Person *) person;
-(NSString *) GetDocumentsDirectory;
-(void) CopyDocumentsFiles;

@end

MultiDAO.m

//
//  MultiDAO.m
//  MultiDB
//
//  Created by Kevin Languedoc on 1/25/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import "MultiDAO.h"
#import "Person.h"
#import "QueryViewController.h"


@implementation MultiDAO
@synthesize fileMgr = _fileMgr;
@synthesize homeDir = _homeDir;


-(Person *) selectRecords:(Person *)person{
  
    const char *sql = "SELECT main.table1.name, secondDb.table1.fromWhere FROM  main.table1 , secondDb.table1 where main.table1.name = secondDb.table1.name ";
    
    sqlite3_stmt *sqlStatement;
    if(sqlite3_prepare_v2(db, sql, 2, &sqlStatement, NULL) == SQLITE_OK)
    {
        sqlite3_bind_text(sqlStatement, 1, [person.name UTF8String], -1, SQLITE_TRANSIENT);
        
        sqlite3_step(sqlStatement);
        sqlite3_finalize(sqlStatement);
        sqlite3_close(db); 
    }else{
        int err= SQLITE_ERROR;
        
        NSLog(@"%d", err);

    }
   
    person.name = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement,0)];
    person.fromWhere = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement,1)];
        
       
    return person;
}

-(void)insertRecords:(Person *)person{
    fileMgr = [NSFileManager defaultManager];
    
    sqlite3_stmt *stmt=nil;

    //insert
    const char *sql = "Insert into main.table1(name , gender) ?,?";
    

    sqlite3_prepare_v2(db, sql, 1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [person.name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, [person.gender UTF8String], -1, SQLITE_TRANSIENT);
    

    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    sql = "Insert into secondDb.table1(name , city) ?,?";
    
    
    sqlite3_prepare_v2(db, sql, 1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [person.name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, [person.fromWhere UTF8String], -1, SQLITE_TRANSIENT);
    
    
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    
    sqlite3_close(db);    

}
                      

-(void)databaseOperations:(NSString *) OperationName :(Person *) person{
    @try {
        NSString * multiDbPath = [self.GetDocumentsDirectory stringByAppendingPathComponent:@"multiDB.sqlite"];
        NSString * otherDbPath = [self.GetDocumentsDirectory stringByAppendingPathComponent:@"otherDB.sqlite"];
        
        BOOL success = [fileMgr fileExistsAtPath:multiDbPath];
        if(!success)
        {
            NSLog(@"Cannot locate database file '%@'.", multiDbPath);
        }
        success = [fileMgr fileExistsAtPath:otherDbPath];
        if(!success)
        {
            NSLog(@"Cannot locate database file '%@'.", otherDbPath);
        }
        if(!(sqlite3_open([multiDbPath UTF8String], &db) == SQLITE_OK))
        {
            NSLog(@"An error has occurred.");
            return;
        }
        NSString * attach = [[NSString alloc] initWithString:[[@"attach database '" stringByAppendingPathComponent:otherDbPath] stringByAppendingString:@"' AS secondDb"]];
               
        NSLog(@"'%@'", attach);

        sqlite3_stmt * sqlStatement;
        
        if(sqlite3_prepare_v2(db, [attach UTF8String], -1, &sqlStatement, NULL) == SQLITE_OK)
        {
            sqlite3_step(sqlStatement);
            sqlite3_finalize(sqlStatement);

            if([OperationName isEqualToString:@"Select"]){

                person =[self selectRecords:person];
                
                QueryViewController * qv = [[QueryViewController alloc]init];
                qv.dispplayName.text = person.name;
                qv.displayGender.text = person.gender;
                qv.displayFromWhere.text = person.fromWhere;

                sqlite3_close(db);
            }else if([OperationName isEqualToString:@"Insert"]){
                [self insertRecords:person];
            }
        }else{
          
            NSLog(@"Attach error: %@ ", SQLITE_ERROR);
        }
    }
    @catch (NSException *exception) {
        NSLog(@"An exception occurred: %@", [exception reason]);
    }
}

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

-(void)CopyDocumentsFiles{
    NSError *err=nil;
    
    fileMgr = [NSFileManager defaultManager];
    
    NSString * multiDbpath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"multiDb.sqlite"]; 
    NSString * copyMultiDb = [self.GetDocumentsDirectory stringByAppendingPathComponent:@"multiDb.sqlite"];
    
    NSString *otherDbPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"otherDb.sqlite"]; 
    NSString *copyOtheDb = [self.GetDocumentsDirectory stringByAppendingPathComponent:@"otherDb.sqlite"];
    
    [fileMgr removeItemAtPath:copyMultiDb error:&err];
    if(![fileMgr copyItemAtPath:multiDbpath toPath:copyMultiDb error:&err])
    {
        NSLog(@"Unable to copy database files into documents folder");
    }
    
    [fileMgr removeItemAtPath:copyOtheDb error:&err];
    if(![fileMgr copyItemAtPath:otherDbPath toPath:copyOtheDb error:&err])
    {
        NSLog(@"Unable to copy database files into documents folder");
    }
    
}

@end

QueryViewController.h

//
//  QueryViewController.h
//  MultiDB
//
//  Created by Kevin Languedoc on 1/26/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import <UIKit/UIKit.h>
#import "MultiDAO.h"
#import "Person.h"

@interface QueryViewController : UIViewController{
    MultiDAO * mDao;
   }

@property(retain,nonatomic)MultiDAO * mDao;


@property (weak, nonatomic) IBOutlet UITextField *InputName;
@property (weak, nonatomic) IBOutlet UITextField *InputGender;
@property (weak, nonatomic) IBOutlet UITextField *InputFromWhere;
- (IBAction)saveAction:(id)sender;
- (IBAction)QueryAction:(id)sender;
@property (weak, nonatomic) IBOutlet UITextField *QueryText;
@property (weak, nonatomic) IBOutlet UITextField *dispplayName;
@property (weak, nonatomic) IBOutlet UITextField *displayGender;
@property (weak, nonatomic) IBOutlet UITextField *displayFromWhere;

@end

QueryViewController.m

//
//  QueryViewController.m
//  MultiDB
//
//  Created by Kevin Languedoc on 1/26/12.
//  Copyright (c) 2012 kCodebook. All rights reserved.
//

#import "QueryViewController.h"
#import "MultiDAO.h"
#import "Person.h"

@implementation QueryViewController

@synthesize mDao=_mDao;

@synthesize QueryText=_QueryText;
@synthesize dispplayName=_dispplayName;
@synthesize displayGender=_displayGender;
@synthesize displayFromWhere=_displayFromWhere;
@synthesize InputName;
@synthesize InputGender;
@synthesize InputFromWhere;

- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil
{
    self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];
    if (self) {
        // Custom initialization
    }
    return self;
}

- (void)didReceiveMemoryWarning
{
    // Releases the view if it doesn't have a superview.
    [super didReceiveMemoryWarning];
    
    // Release any cached data, images, etc that aren't in use.
}

#pragma mark - View lifecycle

/*
// Implement loadView to create a view hierarchy programmatically, without using a nib.
- (void)loadView
{
}
*/


// Implement viewDidLoad to do additional setup after loading the view, typically from a nib.
- (void)viewDidLoad
{
    [super viewDidLoad];
}


- (void)viewDidUnload
{
    [self setInputName:nil];
    [self setInputGender:nil];
    [self setInputFromWhere:nil];
    [self setQueryText:nil];
    [self setDispplayName:nil];
    [self setDisplayGender:nil];
    [self setDisplayFromWhere:nil];
    [super viewDidUnload];
    // Release any retained subviews of the main view.
    // e.g. self.myOutlet = nil;
}

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

- (IBAction)saveAction:(id)sender {
    mDao = [[MultiDAO alloc] init];
    Person * me = [[Person alloc] init];
    
    NSString * test = [[NSString alloc]initWithString:self.InputName.text];
    [me setName:test];
  
    me.gender = [self InputGender].text;
    me.fromWhere = self.InputFromWhere.text;
    
    [mDao databaseOperations:@"Insert":me];
}

- (IBAction)QueryAction:(id)sender {
    mDao = [[MultiDAO alloc] init];
    Person * human = [[Person alloc] init];
    [human setName:self.QueryText.text];
    
    
    [mDao databaseOperations:@"Select":human];
}
@end

More by this Author


Comments 10 comments

kriszsomogyi profile image

kriszsomogyi 4 years ago from Ashgrove, QLD

i'm stuck on a problem that should be quite simply, i've hunted the web for hours looking for a possible tutorial but none seem to work they way i need it to. This is off the topic of the current thread but you seem to be clued in and responsive.

I am trying to have a UISwitch assign a variable that can be used by a different view controller.

for example

-(IBAction) CocktailLoungesSwitch

{

if (CocktailLoungesSwitch.on)

{CocktailLounges = @"'Cocktail %'";}

else {CocktailLounges = @"Null";

}

i've tried creating an NSObject with a set of properties that I try to assign but it just doesn't work. Any ideas?


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Hi kriszsomogyi,

I am more than happy to help out if I can.

I am not sure what you are trying to do

1- Are you trying to assign a value to CocktailLounges?

Because I have tested your code and the CocktailLounges gets assigned its value correctly

2- Or are you send the value of CocktailLounges to another View Controller?

If you want to send to value of CocktailLounges to another View Controller

Add a segue between VCs

In the ViewController impl file of the source VC, add a prepareForSegue method

Create a second VC object like

MySecondViewController * secondVC = [[MySecondViewController alloc] init];

secondVC.ObjectOutlet.text = CocktailLounges;

Of course for this to work you to create CocktailLounges in the header with a global visibility

Also assign a delegate to the receiving object.

I will test out my theory and re post

K


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Hi kriszsomogyi,

Here is the solution, I think to you question

First in the sender method, you don't need to to include the "on" method since you you object returns a boolean value:

- (IBAction)SendInfo:(id)sender {

if(sender)

{

sendInfo = @"Give me a cocktail";

}else

{

sendInfo = @"Last call";

}

}

in the prepareForSegue, you need to assign the value of your instance Variable (NSString) sendInfo or CocktailLounges to the id object in the detailViewController. In others words you need to define an id object in the destinationVC and you assign the value(s) that you want to display to the id object.

-(void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender{

if ([[segue identifier] isEqualToString:@"SendText"]) {

Destination *detailViewController = [segue destinationViewController];

NSLog(@"text : %@",sendInfo);

//This is the id infoRequest, which is a pointer to the object

//Look at the viewDidLoad in the Destination implementation.

detailViewController.infoRequest = sendInfo;

}

}

in The destinationVC, i the viewDidLoad, you get the id object and reference the description information property to obtain the information sent over and display it in the label or whatever you need to to do with it.

- (void)viewDidLoad

{

[super viewDidLoad];

NSLog(@"received info %@", [self.infoRequest description]);

//Receive id (object) from klViewController and display in label

self.ReceiveInfo.text = [self.infoRequest description];

}

I will write up a quick tutorial with screenshots. I will post a link here

Hope this helps and sorry I took so long

Kevin


kriszsomogyi profile image

kriszsomogyi 4 years ago from Ashgrove, QLD

Thanx mate, I'll give it a go in the morning


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

If you need extra information, have a look at this tutorial that explains in detail the steps to pass info to another view controller:

http://hubpages.com/technology/IOS-5-Tutorial-on-H...

Cheers


mikeydcarroll67 4 years ago

I have a question. Could this be adapted to a single database with multiple tables?


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Yes, like any other database you can have multiple tables with a join


mikeydcarroll67 4 years ago

Ok. I'll figure out. If I need help, I'll ask.


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Just open the db in firefox sql manager and add tables and indexes. In obj-c, write select like

select a.col, a.col, b.col b.col from table1 a, table2 b where a.colID = b.colID

Hope this helps


mikeydcarroll67 4 years ago

It does!

    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