iOS SDK and SQLite: Building Data-Driven Apps (2013)
Illustration by Don Barnett

Creating an Objective-C interface for SQLite


iOS SDK and SQLite: Building Data-Driven Apps (2013)

with Bill Weinman

Start your free trial now, and begin learning software, business and creative skills—anytime, anywhere—with video instruction from recognized industry experts.

Start Your Free Trial Now

Video: Creating an Objective-C interface for SQLite

The native SQLite interface is written in C, and while it works very well, it's not very convenient in an Objective-C environment. So, we will begin by writing a wrapper, which is sometimes called an Adapter Pattern, to encapsulate the C Language SQLite interface in a native Objective-C interface that's easy to use and to extend for our purposes. So first, let's take a look at what we want this interface to accomplish. I'm just going to open this BWDB-testbed-code.txt, in my text editor, I'm using TextWrangler here.
Expand all | Collapse all
  1. 8m 29s
    1. Welcome
    2. Exercise files
      2m 17s
    3. Course overview
      3m 4s
    4. Application overview
      2m 11s
  2. 14m 49s
    1. Prototyping in a testbed
      1m 27s
    2. Building the view controller
      3m 45s
    3. Coding the testbed
      7m 56s
    4. Using the testbed
      1m 41s
  3. 37m 49s
    1. Understanding SQLite in iOS
      1m 41s
    2. Creating an Objective-C interface for SQLite
      9m 57s
    3. Testing the BWDB interface in the sandbox
      7m 1s
    4. Designing a database schema
      8m 7s
    5. Supporting the application with a specific interface
      7m 7s
    6. Using C pointers with automatic reference counting (ARC)
      3m 56s
  4. 21m 18s
    1. Understanding the table view
      1m 33s
    2. Creating the view controller
      6m 39s
    3. Reading from the database
      13m 6s
  5. 33m 50s
    1. Understanding the parsing process
      1m 57s
    2. Creating the item view controller
      12m 25s
    3. Reading data from the internet
      5m 30s
    4. Parsing the feed with NSXMLParser
      8m 2s
    5. Updating the item view with the feed items
      5m 56s
  6. 40m 14s
    1. Understanding the modal view
      1m 47s
    2. Constructing the view controller
      15m 5s
    3. Finding a feed link in a web page
      8m 55s
    4. Parsing the feed with NSXMLParser
      5m 4s
    5. Delegating back to the parent view
      6m 11s
    6. Deleting feeds
      3m 12s
  7. 21m 5s
    1. Creating the web view class
      12m 33s
    2. Coding the web view
      5m 25s
    3. Viewing pages in Safari
      3m 7s
  8. 14m 3s
    1. Understanding the iOS preferences system
      1m 23s
    2. Creating the preferences plist in Xcode
      7m 20s
    3. Reading preferences in your application
      5m 20s
  9. 6m 15s
    1. Adding pull-to-refresh functionality
      2m 34s
    2. Implementing the pull-to-refresh gesture for iOS 6
      3m 41s
  10. 27m 1s
    1. Understanding split view
      1m 4s
    2. Coding the table views
      11m 24s
    3. Implementing the iPad detail view
      6m 35s
    4. Implementing the iPad modal view
      7m 58s
  11. 35s
    1. Goodbye

please wait ...
Watch the Online Video Course iOS SDK and SQLite: Building Data-Driven Apps (2013)
3h 45m Intermediate Jan 16, 2013

Viewers: in countries Watching now:

The iOS software development kit (SDK) includes the popular SQLite library, a lightweight yet powerful relational database engine that is easily embedded into an application. In this course, Bill Weinman teaches you how to build an RSS reader for iOS devices, integrating XML data and a streamlined interface. He explains how to use the SQLite database, display information in a table view, code view controllers, and create a preferences pane for your app. The resulting application is optimized for all iPhone and iPad displays.

Topics include:
  • Prototyping the app
  • Coding and working with a testbed
  • Creating an Objective-C interface for SQLite
  • Designing a database schema
  • Creating the view controllers
  • Reading and writing to the database
  • Parsing the RSS feed with NSXMLParser
  • Updating the item view with feed items
  • Implementing the pull to refresh gesture for iOS 6
  • Creating a universal application with multiple views
Bill Weinman

Creating an Objective-C interface for SQLite

The native SQLite interface is written in C, and while it works very well, it's not very convenient in an Objective-C environment. So, we will begin by writing a wrapper, which is sometimes called an Adapter Pattern, to encapsulate the C Language SQLite interface in a native Objective-C interface that's easy to use and to extend for our purposes. So first, let's take a look at what we want this interface to accomplish. I'm just going to open this BWDB-testbed-code.txt, in my text editor, I'm using TextWrangler here.

You can use whatever editor you like just make sure it's a real plain text editor and not a Word Processor. So, you'll notice here is the testDatabase function. And this is the method that I'm going to be using to test this database interface, and so it shows how the interface is called. And really my goal was to have an interface that works like this. So for instance, I can have something, that you know, sends a message to the db object that says doQuery and in a string it has an SQL query "drop table if it exists", CREATE TABLE with this schema, insert into table, values this and that, and you notice that there are place markers and then there are various values.

These are actually Objective-C objects that are used as arguments. And so, my interface has to support what we call variadic arguments in C-based languages. And so, with variadic arguments I can have as many, or as few, of these arguments as I like after the string that has the format in it, and specifies how many arguments I'm going to actually be needing. My interface also allows me to do something like this for row in and then I have this expression here that calls this method getQuery on the db object and has a query in it.

And so, what that will do is it will actually go through the database and get one row at a time and pass those rows off into this row variable and allow me to operate on that. It's a very convenient syntax for calling, and it's accomplished in Objective-C with a protocol called NSFastEnumeration, and we'll see how that's done as well. We also have these CRUD-based methods where I can do something like insertRow and then have a dictionary. This is a literal dictionary using the new Objective-C literal syntax.

And I can just pass it a dictionary object, and it will insert that row into a table that has already been specified in the constructor. So, I have CRUD insert, here is a CRUD update and here is a CRUD delete. So, that's how we want to be able to use this. Let's go ahead and create an interface that will do exactly this. We're going to start by navigating to the Chapter02 folder in the exercise files. And we're going to make a working copy of Testbed.

I'm just going to press Command+C on this Mac and select my folder and press Command+V, and that will allow me to make a working copy here. And I'm going to call this Testbed-02-working, and I'm going to open it up by navigating to the Xcode project file inside of that folder and double-clicking. So, this is actually our finished Testbed from the previous chapter. You notice if I select the iPhone Simulator and press Run, it will build, and it will run it in the iPhone Simulator.

And you can see this is exactly the same Testbed that we had in the last chapter. So, I'm going to come back over here to Xcode, I'm going to press Command+Period to stop these simulators, here it says Finished running the simulator. Now, we're going to select the target, so we select Testbed up here in the top of the Project navigator. And then, I will select Target over here and Summary over here, and scroll down for a while, and you see we have these Linked Frameworks. We're going to add a framework for our SQLite here.

So, I'm going to press the plus button, and I'm going to type, sqlite in the Search there, sql was enough. I'm going to select sqlite 3. dynamic library and click Add. And now, you'll notice that there it is in our frameworks, and we've added the sqlite.dylib to our Link Frameworks. And then down here in our Testbed files, we want to add our database library. So, I've got a library called BWDB in the exercise files.

So, we come back out here in the exercise files and select Libraries, and just grab the BWDB folder. You'll notice inside that folder there are just a couple of files: a .m file and a .h file. We'll take a look at those in a little bit here. So, we're going to grab this whole folder, and we're going to bring it over here until Testbed is highlighted. You see, you can highlight different things here, and you want to highlight Testbed in the folder version of it there, and it will then drop that in right there. Brings up this dialog box, you want to select Copy items, Create groups, and Add to targets for Testbed, and Finish.

And so, it copies those files, it creates a group for them called BWDB, and there are the files, there's the m file which is the implementation and the h file which is the header file. Let's just take a quick look here. The header file has our interface, you notice it I reads in the sqlite3.h header file from the SQLite 3 Framework that we imported earlier. And you'll notice down here, we have our actual interface. We have a constructor with Filename, a constructor with a Filename and a TableName that's used for the CRUD methods.

We have an openDB, closeDB, a d structure-- dealloc is a d structure--and it basically just closes the database. We have Fast enumeration support. That allows us to do that trick where we put the object in an enumeration for loop. We have SQL queries, doQuery, getQuery, prepareQuery, valueFromQuery. And you notice these have the variadic arguments. If you want to know more about variadic arguments in C-based languages, you can take a look at my C and C++ Essential Training on the online training library.

These raw results are pretty much just used internally to translate these variadic queries into something that will return a value. And then, here we have the CRUD methods. This is the heart and soul of this. We can insert a row, update a row, delete a row, get a row, and count rows. And we also have Subscripting methods that support the new keyed subscripts that are available in the latest version of Objective-C. This is all implemented in our .m file and just briefly we're not going to go through this in a lot of detail.

You've got the source code here, and you're welcome to look through it and modify it and use it in whatever way you like. Our constructors simply set up the file name and open the database. And they call this openDB, which goes through a little bit of trouble to actually find the database file in the iPhone iOS file system. An SQLite database is kept entirely in one flat file, all the tables in a given database are all in one very portable file, which is why SQLite is used on so many mobile platforms.

closeDB simply calls SQLite 3 to close, that's the C interface for SQLite 3 and dealloc simply calls closeDB. Here is the Fast enumeration support. There's very little documentation in the Apple documentation of Objective-C as to how fast enumeration works. Basically they give you some examples, and you can play around with those examples until you get something to work, which is what I did here. Here is our basic doQuery, which you'll notice uses variadic arguments here and here is the va_list and va_start for the variadic arguments.

And you'll notice that it uses the SQLite C interface, sqlite3_step, sqlite3_finalize, sqlite3_changes. And again, prepareQuery is very simple, getQuery is likewise simple, as is valueFromQuery. bindSQL is where a lot of the work is done. This actually introspects the arguments, the objects that are passed in the variadic list using respondsToSelector to figure out what the type is of the argument and to call the appropriate sqlite3_bind_int, double sqlite3_ bind_text in order to interface with SQLite.

Our raw results here are used mostly internally within the BWDB class. And these are our CRUD methods, this is for inserting a row. InsertRow uses some string methods in order to construct a sql query to insert the row into the database. Likewise, updateRow constructs an sql query for updating a row in the table. deleteRow and getRow are very straightforward, because how you do that in SQL is very straightforward.

And likewise, these Keyed Subscripting Methods are also very simple. And then we have a couple of simple Utility Methods for getting a call in value in getting the lastInsertId. So, you can see that the value of the wrapper is in its ability to hide the complexity of the native SQLite interface from the Objective-C implementation. The C Language interface for SQLite is full-featured, but it's cumbersome. And when we finish the Testbed implementation using this wrapper, we'll get a good idea of just how much easier it is to use this in Objective-C.

Find answers to the most frequently asked questions about iOS SDK and SQLite: Building Data-Driven Apps (2013) .

Expand all | Collapse all
please wait ...
Q: Why is the RSSDB library in the exercise files different than the one in the videos?

A: The RSSDB library had to be updated to work around a bug in the iOS 7 SDK.

There is a bug in the iOS 7 SDK that prevents the BWDB fast enumeration implementation from working on a device. The symptom is code that runs fine on the emulator, but not on a device. iOS devices use an ARM processor, while the emulator runs on your Mac's Intel processor. This points to the LLVM ARM code generator as the source of the bug. Because the bug appears to be in the LLVM compiler, it may be some time before it is fixed.

As a workaround we have changed the getFeedIDs and getItemIDs methods in the RSSDB library so they don't use Objective C fast enumeration. 

Please note that this same bug also affects some of the BWDB testbed code in Chapter 2. The result is that it will run on the emulator but not on a device.

Q: After upgrading to Xcode 5.1 I get an error that says:

"Used type va_list (aka_builtin_va_list) where arithmetic or pointer type is required"

A: Please download the exercise files again to get the latest version of the BWDB library.
Q: I'm using Xcode 6. Why am I getting error messages with the exercise files?
A: A lot has changed in iOS since this course was released. The author is in the process of rewriting the code and updating the course for iOS 8. In the meantime he has prepared a version of the app that works in iOS 8 and Xcode 6. Download it here:
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.

Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

* Estimated file size

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.

Mark all as unwatched Cancel


You have completed iOS SDK and SQLite: Building Data-Driven Apps (2013).

Return to your organization's learning portal to continue training, or close this page.

Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member ?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any page and choose Site preferences from the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Learn more, save more. Upgrade today!

Get our Annual Premium Membership at our best savings yet.

Upgrade to our Annual Premium Membership today and get even more value from your subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.

Sign up and receive emails about and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from

Sign up and receive emails about and our online training library:

Here’s our privacy policy with more details about how we handle your information.

submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.