SQLite Useful Functions for Beginners

SQLite Useful Functions for Beginners

SQLite database provides advanced functions for adding, removing, updating records into a database. It also allows performing some of the advanced levels of operations like database migration, Inner join, a grouping of tables etc.

Its normally used to store large records into the application such as managing user list, or offline records of web-based data to make sure it supports an offline load of the data in case of no internet connection.

Running with SQL functions is as faster as any other database storage options & its more convenient options too. The reason is it provides advance inserting & deleting features & clauses which allows developers to perform complex operations over a local database.

So here are some basic functions which we normally use in iOS Application for managing local database.

Save record :

– (void) doSaveMyData  {

sqlite3 *database;

[self CheckConnection];

if (sqlite3_open([dbpath UTF8String], &database) == SQLITE_OK)  {

NSString *query = [NSString stringWithFormat:@”insert into myTable values(‘%@’, ‘%@’)”, value1, value2];

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpSqlStmt;

sqlite3_prepare_v2(database, sqlStmt, -1, &cmpSqlStmt, NULL);

sqlite3_step(cmpSqlStmt);

sqlite3_finalize(cmpSqlStmt);

}

sqlite3_close(database);

}

For Check Database Connection (This will commonly used before any database operation)

-(void)CheckConnection {

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

NSString *docdir = [docpath objectAtIndex:0];

dbpath = [docdir stringByAppendingPathComponent:database];

BOOL success;

NSFileManager *fm = [NSFileManager defaultManager];

success = [fm fileExistsAtPath:dbpath];

if(success) {

return;   }

NSString *dbPathFromApp = [[[NSBundle mainBundle]resourcePath]stringByAppendingPathComponent:database];

[fm copyItemAtPath:dbPathFromApp toPath:dbpath error:nil];

}

Get list of records :

-(void)doGetRecords:(NSString*)strCatID {

[self CheckConnection];

if (arrList.count > 0) {

[arrList removeAllObjects];

arrList = nil;

}

arrOutFitsList = [[NSMutableArray alloc] init];

sqlite3 *database;

if (sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) {

NSString *query = [NSString stringWithFormat:@”select * from myTable where cat_id = ‘%@””,strCatID];

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpsqlstmt;

if (sqlite3_prepare_v2(database, sqlStmt, -1, &cmpsqlstmt, NULL) == SQLITE_OK) {

while (sqlite3_step(cmpsqlstmt) == SQLITE_ROW) {

DetailDict *obj = [DetailDict new];

obj.id = [NSString stringWithFormat:@”%d”,(int)sqlite3_column_int(cmpsqlstmt, 0)];

obj.Tag = [NSString stringWithUTF8String:(char*)sqlite3_column_text(cmpsqlstmt, 1)];

[arrOutFitsList addObject:obj];

}

sqlite3_finalize(cmpsqlstmt);

}

sqlite3_close(database);

}

Update record :

-(void)doUpdaterecord {

[self CheckConnection];

sqlite3 *database;

if (sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) {

NSString *query = [NSString stringWithFormat:@”update myTable set cat_id=’%@’, Tag=’%@'”, self.objDetailsDict.cat_id, self.objDetailsDict.Tag];

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpSqlStmt;

sqlite3_prepare_v2(database, sqlStmt, -1, &cmpSqlStmt, NULL);

sqlite3_step(cmpSqlStmt);

sqlite3_finalize(cmpSqlStmt);

}

sqlite3_close(database);

}

Remove Record :

-(void)doRemoveRecord:(NSString*)stID {

[self CheckConnection];

sqlite3 *database;

if (sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) {

NSString *query = [NSString stringWithFormat:@”DELETE FROM myTable WHERE id=’%@'”,stID];

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpSqlStmt;

sqlite3_prepare_v2(database, sqlStmt, -1, &cmpSqlStmt, NULL);

sqlite3_step(cmpSqlStmt);

sqlite3_finalize(cmpSqlStmt);

}

sqlite3_close(database);

}

Count Number of Records :

-(int)doCountToalRecords:(NSString *)ID {

[self CheckConnection];

int totalIS = 0;

sqlite3 *database;

if (sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) {

NSString *query = [NSString stringWithFormat:@”SELECT Count(*) FROM myTable where cat_id = ‘%@'”,ID];

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpsqlstmt;

if (sqlite3_prepare_v2(database, sqlStmt, -1, &cmpsqlstmt, NULL) == SQLITE_OK) {

while (sqlite3_step(cmpsqlstmt) == SQLITE_ROW) {

totalIS = sqlite3_column_int(cmpsqlstmt, 0);

}

}

sqlite3_finalize(cmpsqlstmt);

sqlite3_close(database);

}

return totalIS;

}

Add new field to existing table :

-(void)doAddNewFieldToTable {

sqlite3 *database;

if (sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) {

NSString *query = @”ALTER TABLE myTable ADD COLUMN newField Varchar”;

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpsqlstmt;

if (sqlite3_prepare_v2(database, sqlStmt, -1, &cmpsqlstmt, NULL) == SQLITE_OK) {

while (sqlite3_step(cmpsqlstmt) == SQLITE_DONE) {

NSLog(@”Success”);

}

}

sqlite3_finalize(cmpsqlstmt);

sqlite3_close(database);

}

}

So in this blog we have covered up all basic operations performed in sqlite database such as inserting, fetching, updating, removing records & also for counting number of records, adding new field to existing table while working with Objective C in iOS Application.

Share
Convert Your Project to Latest Swift Version

Convert Your Project to Latest Swift Version

Whenever new Xcode version release or any new OS releases apps developed with the swift language are needed to convert to the new swift version. There might be syntax changes or any bugs solving or something done in the swift language which requires apps to be updated with latest stable version.

Now, in this case, developers often try to make compatibility by manually running app & finding the bugs. Now, this process consumes more time & sometimes it takes too long to debug & find the exact solution. Overall it becomes tidy process & not exactly what Apple prefers.

So here are the proper steps to perform the process:

First of all, open your project into latest Xcode version & make sure don’t run it the first time.

Go to Edit -> Convert -> To current swift syntax

This will open hierarchy of files. Make sure there will be all files selected with a check mark which you wants to convert to latest swift syntax. If any files not selected or you don’t want that to a concert than manually uncheck it. Then clicking on done will do the rest of the work.

Note :

In new swift version, latest swift conversion process works perfectly & don’t require actually to manually resolve any errors. But still, there could be the chance of any errors skipped by the compiler or unable to detect the difference will keep remain & can show an error so that requires handling manually.

Share
How to save data to PList files in iOS

How to save data to PList files in iOS

While working with iOS Applications there are various ways to store data locally into an app. Such as NSUserdefaults, SQLite database, Plist file, NSKeyArchiever, Core data etc.

A developer can choose any of the medium suitable for the application. It can be measured by the complexity of the data & length of the data. Usually, plist is used for storing flag values or for in-app purchase settings or for storing some of the key settings of the application. This kind of data doesn’t require any complex data structure of any advanced functions.

Plist file doesn’t require to fire any queries nor any complex process for fetching or updating data. So it loads faster than the other data storage options.

That is the reason why its more often used in the game for storing scores, statistics of the game. Implementation is also very easy as just a few lines of code required to set up 2 main functions for handle plist files records. First for reading records, Second for writing records.

Here are few easy steps with a graphical representation for creating, reading & writing plist file.

Create New plist file :

Right click on projects & click create new file

From Resources select property list & name it as you want (For ex: mySettings.plist).

Set up prefix value into plist :

You can store anything type of variable like Dictionary, Array, Strings to the plist. Note that the root type will remain NSDictionary always. Lets say we have to store string value inside plist file.

So here i am storing string value by setting up prefix value to 0. We can store & manage multiple strings, arrays & dictionary values inside same plist file.

Writing value to plist file:

-(void)doWriteSettingFilePlist {
NSError *error;
NSString *path = [documentsDirectory stringByAppendingPathComponent:@”SettingFile.plist”];
NSFileManager *fileManager = [NSFileManager defaultManager];
// if File not exist at the same path
if (![fileManager fileExistsAtPath: path]) {
NSString *bundle = [[NSBundle mainBundle] pathForResource:@”SettingFile” ofType:@”plist”];
[fileManager copyItemAtPath:bundle toPath: path error:&error];
}
NSMutableDictionary *data = [[NSMutableDictionary alloc] initWithContentsOfFile:path];
[data setObject:self.strProVersion forKey:@”proversion”];
[data writeToFile:path atomically:YES];
}

Reading from plist file :

-(void)doReadSettingFilePlist {
NSError *error;
NSString *path = [documentsDirectory stringByAppendingPathComponent:@”SettingFile.plist”];
NSFileManager *fileManager = [NSFileManager defaultManager];
// if File not exist at the same path
if (![fileManager fileExistsAtPath: path]) {
NSString *bundle = [[NSBundle mainBundle] pathForResource:@”SettingFile” ofType:@”plist”];
[fileManager copyItemAtPath:bundle toPath: path error:&error];
}
NSMutableDictionary *data = [[NSMutableDictionary alloc] initWithContentsOfFile:path];
self.strProVersion = [data valueForKey:@”proversion”];
}

Notes :

Plist is more faster in terms of fetching data & handle small amount of records. Although its no recommended for use in high level database records storage where more complex operations are requires to handle. One more drawback is plist doesn’t provide update functions like SQLite does. Its just simply overwrites the data.

Overall its still useful feature for handling default app settings of the app like flag values sound, inApp purchase properties etc. with less coding & less efforts & minimum compilation time.

Share
Share