Sqlite Database in ios

Sqlite is an in-process library that implement a self-contained,  serverless,  zero-configuration, transactional SQL database engine. Sqlite is very small and light weight, less than 400kiB fully configured or less than 250 KiB with optional features omitted and its not required a separated server process or system to operate.

Click here for more details.

We can use Sqlite for storage data in iOS Application as a database.

This post is related to get data from two different-different database's tables.

First step is to two get both database path.

So create a method which return db path.

-(NSString *) getDbpathWithName:(NSString *)dbName{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
  
    NSString *documentsDir = [paths objectAtIndex:0];
    return [documentsDir stringByAppendingPathComponent:dbName];
}

     

NSString *firstDbPath = [self getDbpathWithName:@"Firstdb.Sqlite"];

NSString *secondDbPath = [self getDbpathWithName:@"Seconddb.Sqlite"];

//declare a Sqlite database instance

sqlite3 *secondDataBase;

//Open second database

if (sqlite3_open([secondDbPath UTF8String], &secondDataBase)!=SQLITE_OK) {
        NSLog(@"Error to open database = %s",sqlite3_errmsg(userDatabase));
        sqlite3_close(userDatabase);
        secondDataBase = nil;
        return ;
    }

//write a query for attach first database to second database

 NSString *attachQuery = [NSString stringWithFormat:@"ATTACH DATABASE \"%@\" AS aDB",firstDbPath];

//execute query
    sqlite3_exec(secondDataBase, [attachQuery UTF8String], NULL, NULL, &error);
    if (error) {
        NSLog(@"Error to Attach = %s",error);
        sqlite3_close(userDatabase);
        secondDataBase = nil;
    }

Now we have attached both database, and can use to get data from both database's tables.

//Write a query to retrieve data

NSString *queryString = @"Select e.id,  e.name,  e.address, ex.car_allowance. ex.medical_allowance, ex.food_allowance from employee e inner join expenses ex on (e.id == ex.empId)".

Here table 'employee' is in first database and table 'expenses' is in second database, because we need to fetch data from two different table so we are using 'join'.

//Now Prepare statement

sqlite3_stmt *selectStmt;
    if (sqlite3_prepare_v2(secondDataBase, queryString.UTF8String, -1, &selectStmt, NULL) != SQLITE_OK) {
        NSLog(@"Error to prepare annotation select stmt all comment= %s",sqlite3_errmsg(queryString));
        sqlite3_finalize(selectStmt);
        sqlite3_close(secondDataBase);
        selectStmt = nil;
        secondDataBase = nil;
        return;
    }

Its time to fetch all records 

NSMutableArray *empRecordArray = [[NSMutableArray alloc] init];
   

 while (sqlite3_step(selectStmt)==SQLITE_ROW) {

        NSString *empid = [NSString stringWithFormat:@"%s",(const  char*)sqlite3_column_text(selectStmt, 0)];

        NSString *name = [NSString stringWithFormat:@"%s",(const char*)sqlite3_column_text(selectStmt, 1)];

       NSString *address = [NSString stringWithFormat:@"%s",(const char*)sqlite3_column_text(selectStmt,2)]; 

       NSString *car_allowance = [NSString stringWithFormat:@"%s",(const char*)sqlite3_column_text(selectStmt, 3)]; 

       NSString *medical_allowance = [NSString stringWithFormat:@"%s",(const char*)sqlite3_column_text(selectStmt, 4)]; 

       NSString *food_allowance = [NSString stringWithFormat:@"%s",(const char*)sqlite3_column_text(selectStmt, 5)];

      

     NSDictionary *recordDict = @{@"id":empid, @"name":name, @"address":address, @"CarAllowance":car_allowance,@"MedicalAllowance":@"medical_allowance", @"FoodAllowance":food_aalowance};

[empRecordArray addObject:recordDict];

}//end while loop

NSLog(@"All record : %@", empRecordArray);

Check out more post related to