Posts Tagged ‘App Development’
My Talk on “Intro to iOS Development” at ASEI
Today, I gave an introductory level technical talk about developing mobile apps for iOS platform at the American Society Of Engineers Of Indian Origin (ASEI), MI. The ASEI is "a two level (National and Local) non-profit organization of engineers and technical professionals of Indian origin". You can learn about them at http://www.aseimi.org. I was aware of the group but I had never attended any of their meetings, so I was not sure what to expect.
Right after work, I made the 45 mile drive to the ASEI meeting. Fighting the evening rush hour traffic, I reached there on the nick of time ; I probably made the organizers quite nervous!
There was a pretty good turn out. These were people, who just like me, had driven in from work and who probably had ten other places they'd rather be on a fall evening with picture perfect weather. I had to ensure that my talk was well worth their evening.
Soon after the featured mobile app presentation, I got started. I surveyed the room and learnt that there were less than five developers in the room. The rest of the audience was a mix of people with diverse backgrounds (different industries , different roles, varying demographics, a few were not even iPhone users).
My presentation was intended to be fairly technical , so my challenge was to make it appeal to the diverse audience. Although they were all not developers, I knew they all had one thing in common – they were very keen on learning more about iOS mobile development. I knew that was a start.
So for the next hour or so, I quickly moved through my slides. I had material for couple of hours but I tried to focus on material that would broadly appeal. Then the questions started pouring in and they were all very relevant. People were paying attention (well- at least most of them were) and it was interesting to see different perspectives.
I left the meeting with a greater sense of community.
You can download my presentation from here. It is intended to be a primer to the iOS platform and developing apps for it .
Intro to SQLite for iOS developers
The SQLite engine is a lightweight library optimized for managing relational databases in embedded environments. It is supported on the iOS platform. Described below is a set of basic SQLite queries that will come in handy if you need /choose to use the C-style SQLite APIs to directly manipulate your database tables. It is intended for folks who are noobs to the database world. Direct manipulation using SQLite APIs is an alternative to using the high-level Object Oriented Core Data wrapper framework.
Note: The sample code snippets are intended for reference purposes. You should include appropriate error handling and any relevant synchronization support depending on the needs of your application. If you are interested in the details of the routines, please refer to the sqlite3.h header file.
Adding SQLite Support
SQLite libraries is available as part of the iOS SDK. Include the libsqlite3.0.dylib to your project using the usual procedure, and add
#import <sqlite3.h>
Note: The following code snippets assume an ivar named “dbHandle” corresponding to the database Handle defined in your header file as follows-
sqlite3* dbHandle;
Opening the connection to the database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
NSString* const dbFileName = @"AddressBook.sqlite"; NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString* dbFile = [documentsDirectory stringByAppendingPathComponent:dbFileName]; sqlite3_config(SQLITE_CONFIG_SERIALIZED); int result = sqlite3_open([dbFile UTF8String], &dbHandle); if (result != SQLITE_OK) { NSLog(@"Failure in connecting to the database with result %d",result); } else { NSLog(@ "Succesfully opened connection to DB") ; } |
Creating a table
The following code snippet creates a table named “Contacts” in the “AddressBook.sqlite” database. Every row in the table corresponds to a “Contact” and has the following columns-
|
FirstName |
VARCHAR (non-null) |
|
LastName |
VARCHAR (null) |
|
|
VARCHAR (null) |
|
Telephone |
VARCHAR (null) |
|
1 2 3 4 5 6 7 8 9 |
char* error; const char *sqlQuery = "CREATE TABLE Contacts (FirstName TEXT NOT NULL, LastName TEXT, Email TEXT, Telephone TEXT,PRIMARY KEY (FirstName))"; int result = sqlite3_exec(dbHandle, sqlQuery, NULL, NULL, &error); if (result != SQLITE_OK) { NSLog(@ "Failure in creating the table with result %d",result) ; } else { NSLog(@ "Succesfully created table ") ; } |
Common queries on a table
Query execution follows the following basic sequence –
- “Prepare” the SQL query for executing using the sqlite3_prepare_v2() call. A SQL query can be comprised of multiple SQL statements, That said, the “preparation” routines only compile the first SQL statement in the query so I typically specify only one statement within a query.
- Optionally,“Bind” SQL query arguments with values using the sqlite3_bind_*() commands. Binding would be required only if there are arguments specified in the SQL query statement.
- “Evaluate” the compiled SQL query (that was output from the sqlite3_prepare_v2() command ) using the sqlite3_step() command.
- Optionally, “Retrieve” the results of the SQL query evaluation using the sqlite3_column_*() commands.You can access the value of every column of the resulting row using these routines. The leftmost column begins at index 0.
- “Reset” the compiled SQL query so it can be evaluated again. You can execute the query again using the sqlite3_bind() and sqlite3_step() commands
- “Destroy” the query object once you are done with the query using the sqlite3_finalize() command
Fetch total number of entries in the table
The code snippet returns the number of rows in the Contacts table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
int count = 0; int retVal; const char *sqlQuery = "SELECT COUNT(*) FROM Contacts"; sqlite3_stmt *query = nil; if ((retVal =sqlite3_prepare_v2(dbHandle, sqlQuery, -1, &query, NULL)) == SQLITE_OK) { int queryResult = sqlite3_step(query); if (queryResult == SQLITE_ROW) { count = sqlite3_column_int(query, 0); NSLog(@"Succesfully selected row count %d ",count) ; } else { NSLog(@"Failed to selected row count ") ; } sqlite3_reset(query); sqlite3_finalize(query); } else { NSLog(@"Failure in preparing SELECT statement with result %d",retVal) ; } |
Add a row entry to the table
The code snippet adds a new Contact entry to the Contacts table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
NSString* contactFirstName = @"Foo"; NSString* contactLastName = @"Bar"; NSString* contactEmail = @"foo@bar.com"; NSString* contactTel = @"555-1234"; const char *sqlQuery = "INSERT into Contacts (FirstName, LastName, Email, Telephone) Values(?, ?, ?, ?)"; sqlite3_stmt *query = nil; int retVal; if ((retVal = sqlite3_prepare_v2(dbHandle, sqlQuery, -1, &query, NULL)) == SQLITE_OK) { // The contactFirstName is a primary key. So cannot be NULL. Do the relevant checks for null values before you get here sqlite3_bind_text(query, 1, [contactFirstName UTF8String], -1, SQLITE_TRANSIENT); if (contactLastName) { sqlite3_bind_text(query, 2, [contactLastName UTF8String], -1, SQLITE_TRANSIENT); } else { sqlite3_bind_null(query, 2); } if (contactEmail != nil) { sqlite3_bind_text(query, 3, [contactEmail UTF8String], -1, SQLITE_TRANSIENT); } else { sqlite3_bind_null(query, 3); } if (contactTel != nil) { sqlite3_bind_text(query, 4, [contactTel UTF8String], -1, SQLITE_TRANSIENT); } else { sqlite3_bind_null(query, 4); } int result = sqlite3_step(query); if (result != SQLITE_DONE) { NSLog(@ "Failure in adding row to table. Result is %d",result) ; } else { NSLog(@ "Succesfully added row ") ; } sqlite3_reset(query); sqlite3_finalize(query); } else { NSLog(@ "Error in preparing INSERT statement %d",retVal); } |
Select a row entry from the table
The code snippet selects an entry in the Contacts table with FirstName = “Foo” . It also retrieves the rowId corresponding to the entry.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
NSString* searchName = @"Foo"; NSInteger rowId; NSString* lastName; NSString* email; NSString* tel; const char *sqlQuery = "SELECT rowid, FirstName, LastName, Email, Telephone FROM Contacts WHERE FirstName == ?"; sqlite3_stmt *query = nil; int retVal; if ((retVal = sqlite3_prepare_v2(dbHandle, sqlQuery, -1, &query, NULL)) == SQLITE_OK) { sqlite3_bind_text(query, 1, [searchName UTF8String], -1, SQLITE_TRANSIENT ); int result; if( (result = sqlite3_step(query)) == SQLITE_ROW) { rowId = sqlite3_column_int(query, 0); lastName = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(query, 2)]; email = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(query, 3)]; tel = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(query, 4)]; NSLog(@"Retrieved the row with Id %d for FirstName %@ :LastName :%@, Email:%@, Telephone:%@", rowId,searchName,lastName,email,tel); } else { NSLog(@ "Failure in select row from table. Result is %d",result) ; } sqlite3_reset(query); sqlite3_finalize(query); } else { NSLog(@ "Failure in preparing SELECT statement with result %d",retVal) } |
Update a row entry in the table
The code snippet updates the Telephone number associated with an entry in the Contacts table with FirstName = “Foo” to “555-7777”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
NSString* searchStr = @"Foo"; NSString* updateStr = @"555-7777"; const char *sqlQuery = "UPDATE Contacts SET Telephone = ? WHERE FirstName == ?"; sqlite3_stmt *query = nil; int retVal; if ((retVal = sqlite3_prepare_v2(dbHandle, sqlQuery, -1, &query, NULL)) == SQLITE_OK) { sqlite3_bind_text(query, 1, [updateStr UTF8String], -1, SQLITE_TRANSIENT ); sqlite3_bind_text(query, 2, [searchStr UTF8String], -1, SQLITE_TRANSIENT ); int result; if ((result =sqlite3_step(query)) != SQLITE_DONE) { NSLog(@"Failed to execute the query %d",result); } else { NSLog(@"Succesfully updated row !"); } sqlite3_reset(query); sqlite3_finalize(query); } else { NSLog(@ "Error in preparing UPDATE statement %d",retVal); } |
Delete a row entry from the table
The code snippet deletes an entry in the Contacts table with FirstName = “Foo” .
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
NSString* searchStr = @"Foo"; const char *sqlQuery = "DELETE FROM Contacts WHERE FirstName == ?"; sqlite3_stmt *query = nil; int retVal; if ((retVal = sqlite3_prepare_v2(dbHandle, sqlQuery, -1, &query, NULL)) == SQLITE_OK) { sqlite3_bind_text(query, 1, [searchStr UTF8String], -1, SQLITE_TRANSIENT ); int result; if ((result = sqlite3_step(query)) != SQLITE_DONE) { NSLog(@ "Failure in delete row from table. Result is %d",result) ; } else { NSLog(@ "Succesfully deleted row ") ; } sqlite3_reset(query); sqlite3_finalize(query); } else { NSLog(@ "Failure in preparing DELETE statement with result %d",retVal) ; } |
Closing connection to the database
The following code snippet closes the connection to the database. All resources must have been “finalized” before you invoke this routine.
|
1 2 3 4 5 6 7 |
int result = sqlite3_close(dbHandle); if (result != SQLITE_OK){ NSLog(@"Failure in closing connection to database. Result %d",result); } else { NSLog(@ "Successfully closed DB connection ") ; } |
Testing
You can use the freely available plugin “SQLite Manager” (https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/) for Mozilla Firefox to examine/verify the contents of your sqlite database.