iOS

How to Use SQLite to Manage Data in iOS Apps


Among the numerous applications existing on the App Store today, it would be hard for someone to find more than a few of them that do not deal with data. Most of the apps handle some sort of data, no matter in what format they are, and always perform some actions upon it. There are various solutions offered to developers for storing and managing data, and usually each one of them is suitable for different kind of applications. However, when working with large amount of data, the preferred method it seems like a one-way path: That is the use of a database.

Indeed, making use of a database can solve various kind of problems that should be solved programmatically in other cases. For programmers who love working with databases and SQL, this is the favorite data-managing method at 90% of the cases, and the first think that crosses their minds when talking about data. Also, if you were used to working with other databases or database management systems (DBMSs), then you’ll really appreciate the fact that you can keep applying your SQL knowledge in the iOS platform as well.

The database that can be used by apps in iOS (and also used by iOS) is called SQLite, and it’s a relational database. It is contained in a C-library that is embedded to the app that is about to use it. Note that it does not consist of a separate service or daemon running on the background and attached to the app. On the contrary, the app runs it as an integral part of it. Nowadays, SQLite lives its third version, so it’s also commonly referred as SQLite 3.

sqlite-database

SQLite is not as powerful as other DMBSs, such as MySQL or SQL Server, as it does not include all of their features. However, its greatness lies mostly to these factors:

  • It’s lightweight.
  • It contains an embedded SQL engine, so almost all of your SQL knowledge can be applied.
  • It works as part of the app itself, and it doesn’t require extra active services.
  • It’s very reliable.
  • It’s fast.
  • It’s fully supported by Apple, as it’s used in both iOS and Mac OS.
  • It has continuous support by developers in the whole world and new features are always added to it.

Focusing now on our tutorial, let me start by stating that my goal is not to show you how to become a SQLite expert. Instead, my plan is to implement a database class step by step, which will utilize the most important features of the SQLite library, but it will also become a reusable tool for your own applications. Unfortunately, even though SQLite is supported by Apple, a mechanism or a pre-made database management library does not exist. Going into more details, the database class that we will implement will be capable of executing all the standard SQL queries (select, insert, update, delete). The most important is that we’ll create it in such way, so it accepts clear SQL statements, and if you had worked with SQL in the past, you’ll definitely know what to do here too.

Besides the database class that we’ll develop, we’ll also create a sample application to test it and to see it in action. More details about that you’ll find in the next section though. Note that the queries we’ll write in the demo app will be simple enough for the sake of the tutorial, however be sure that more complex queries can be executed as well.

As a final word before we proceed, I would recommend to make a web search and read some more stuff about the SQLite itself. Of course, the first one should be the the official website.

App Overview

One could say that this tutorial is composed by three parts. In the first and most important one, we are going to create a new class and in there we’ll implement all the database handling. As I have already said in the introduction, after we have it finished, you can take it and use it as a reusable component to your own apps. In the second part we are going to leave Xcode for a while, as it’s necessary to work in the Terminal and in the SQLite command line environment. There, we will create a simple database, which we will add to the project and use it. Finally, in the third part, we are going to implement a sample application where we will make use of both the database class and the database file. More specifically about the third part, we will make the necessary configuration in the Interface Builder, and then we’ll write the required code so we’ll have a fully functional app in which the database class will have the most significant role.

Regarding the database class that we will create in a while, I would say that it can be used as-is in the most cases of applications. To tell the truth, I rarely needed to do any updates or modifications to the class’s code after its initial implementation, as it has been suitable for every app I developed or have been developing. Nevertheless, keep in mind that even though we will build a reusable tool, you should feel free to change it, evolve it and add any extra features you might think that would be useful for your own needs. Nothing is set of stone.

In the sample database that we will create to test the class, we are going to add just one table. We’ll name that table peopleInfo, and it will contain some basic people’s data. These will be the first name, the last name and the age. The datatype of the first two fields will be set to text, while the age will be an integer value. For the primary key of the table, we will set an auto-increment integer value. Generally, using such a key is recommended in most of cases, unless special requirements of your app define otherwise. The sample database will be created in Terminal, and then it will be added to the project. Maybe you are wondering if there are any Mac applications that could be used for that purpose, and the answer is that there are. To be honest though, I prefer and like a lot more to use the command line environment, as I think that it consists of the fastest and most efficient way to work with a database.

Finally, focusing a bit more on the sample app, it’s necessary to say that it’s going to be a navigation based app. However, initially we will create a simple, view-based app, and later through the Interface Builder we will convert it to navigation-based. Furthermore, the app will be parted by two view controllers. In the first view controller, we will display all the records fetched from the database in a table view. Besides than simply listing them, we’ll also make our app capable of editing and deleting records. The second view controller will be used to add a new record, or edit an existing one. Upon finishing, our app will be able to perform all the basic kind of queries: select for loading the data, insert for adding new records, update for editing existing records and delete to completely delete a record.

In the animated graphic below you can see the final outcome of our effort in this tutorial:

SQLite Database Demo App

Having said all the above, I think is time to get started. We have a long journey ahead of us, so let’s don’t waste any more time.

Creating the Project

Unless we have a very special topic to discuss, the first step will always be the creation of our project. So, let me guide you through the necessary steps and make sure that we get along in all that.

Initially, launch Xcode and wait for the Welcome screen to appear. There, select to create a new project, as shown in the next figure:

Xcode Welcome Dialog

Next, in the first step of the guide that is appeared, select the Single View Application template, in the Application category, under the iOS section.

Xcode Template

Click on the Next button to proceed. In the second step, in the Product Name field set the SQLite3DBSample as the project’s name, and also make sure that the iPhone is the selected value in the Device drop down control.

Xcode Project Option

Click on the Next button once again, and in the last step of the guide select a directory in your computer to save the project. Click on the Create button and wait until Xcode prepares the project.

The SQLite 3 Library

Before we begin implementing, it’s necessary to perform a small prerequisite step. That is to add the appropriate SQLite 3 library to the project, so we can import it later on to our class.

In the Project Navigator on Xcode click on the project name. Next, make sure that the General tab is on, and then go to the bottom of the page, in the Linked Frameworks and Libraries section. Click on the small plus icon as shown in the next image:

Xcode Project Plus Icon

In the modal window that appears, type the term sqlite, and from the suggested options select the one with the libsqlite3.dylib title.

XCode Select Library

Finally, click on the Add button and this step is over.

Creating and Initializing the Database Class

So, now that the SQLite 3 library has been added to the project, the first thing we are going to do is to create a new class to manage all the database functionality. In this class, we will write all the code needed to execute queries and to load data from the database.

Let’s get started by adding a new class file to the project. Go to the File > New > File… menu of the Xcode, or just hit the Command-N key combination on your keyboard. The familiar Xcode guide will appear. In the first step, select the Objective-C class option in the Cocoa Touch category, under the iOS section.

Xcode adding new file

Click on the Next button, and in the next step perform two tasks: First, make sure that the Subclass of field contains the NSObject value. If not, then type it. Next, in the Class field type the name of our class, which is DBManager.

Xcode Adding New file

After having done the above, click once again on the Next button, and in the third and last step make sure that the SQLite3DBSample target is selected. If not do it now, and click on the Create button. The guide will close and the new class will be ready.

Now we have an empty class in our hands, and we just have to write code in it. We’ll begin by creating a custom init method, as upon initialization we want to specify the database file name. Click on the DBManager.h file on the Project Navigator to open it. In there, add the following declaration:

Now, go to the DBManager.m file, and write the standard definition of all the init methods:

As I have already said, the original database file will reside in the application’s bundle (in other words, the app package). Our mission is to make a copy of that file to the application’s documents directory, and work with that copy later on. Note that you should never work directly with a file existing in the app bundle, especially if your app is going to modify it. Always make a copy of it to the documents directory.

Focusing on the init method again, we’re going:

  1. To specify the path to the documents directory of the app and store it to a property .
  2. To store the database filename that is provided as an argument in the above init method to another property.
  3. To copy the database file from the app bundle into the documents directory if that’s necessary.

We’ll use properties to store the documents directory and the database filename values, as we’ll need them later as well.

Having said all the above, go at the top of the file and create the private class section, by declaring at the same time the two properties as described.

Now, once being at the top of the file, make the next import:

So far, so good. Back to the init method, let’s do some initialization. Right next you are given the method again, containing this time all the proper code:

At first, we specify the path to the documents directory and we store it to the documentsDirectory property. Next, we assign the database filename to the databaseFilename property, and finally we copy the database file from the app bundle to the documents directory.

As you correctly assume, the copyDatabaseIntoDocumentsDirectory is a custom, private method that we must implement, and in here we’ll do the file copying, if needed of course. Surely Xcode have issued an error by now, so let’s work against it.

Go back to the private class section, and declare the method as shown below:

Let’s go to the definition right away. What we’ll do here is fairly simple: We’ll check if the database file exists or not in the documents directory, and if it’s not there we’ll copy it. Here’s the implementation:

There’s nothing especially difficult in the above method. We are based on the NSFileManager class to check for the file existence and to copy it if needed. It’s also quite interesting the way we access the original file in the app bundle: [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:self.databaseFilename].

The above method will be called every time that an object of the DBManager class is initialized. If the database file won’t be found in the documents directory, then the code in the condition block will be executed, otherwise it will be skipped. Normally, that code should be executed just once per app install, as the database file should always remain in the documents directory after having been copied in there.

So, quickly summarizing, we’ve managed to perform two important tasks here: To create the DBManager class files, and to create a custom init method, which makes sure that the database file will exist to the documents directory of the app. Now, we are ready to go deeper in the details of the SQLite 3 database.

SQLite 3 Functions Preview

Our next step is to implement the heart of our class, the method that will take care of all the work with the database. However, I believe it would be much better to take a quick look at the SQLite 3 functions that we are going to need first, and then proceed to implementation. There’s a great number of functions supported and provided, but we’ll need just some of them. Let’s see them one by one:

  • sqlite3_open: This function is used to create and open a database file. It accepts two parameters, where the first one is the database file name, and the second a handler to the database. If the file does not exist, then it creates it first and then it opens it, otherwise it just opens it.
  • sqlite3_prepare_v2: The purpose of this function is to get a SQL statement (a query) in string format, and convert it to an executable format recognizable by SQLite 3.
  • sqlite3_step: This function actually executes a SQL statement (query) prepared with the previous function. It can be called just once for executable queries (insert, update, delete), or multiple times when retrieving data. It’s important to have in mind that it can’t be called prior to the sqlite3_preprare_v2 function.
  • sqlite3_column_count: This method’s name it makes it easy to understand what is about. It returns the total number of columns (fields) a contained in a table.
  • sqlite3_column_text: This method returns the contents of a column in text format, actually a C string (char *) value. It accepts two parameters: The first one is the query converted (compiled) to a SQLite statement, and the second one is the index of the column.
  • sqlite3_column_name: It returns the name of a column, and its parameters are the same to the previous function’s.
  • sqlite3_changes: It actually returns the number of the affected rows, after the execution of a query.
  • sqlite3_last_insert_rowid: It returns the last inserted row’s ID.
  • sqlite3_errmsg: It returns the description of a SQLite error.
  • sqlite3_finalize: It deletes a prepared statement from memory.
  • sqlite3_close: It closes an open database connection. It should be called after having finished any data exchange with the database, as it releases any reserved system resources.

Core Functionality Implementation

Now that you’ve been provided with a short description of every SQLite function we’ll meet next, let’s go to the implementation of the core method of our class. In this one, we are going to run both executable and non-executable queries. Based on that, we will make sure to implement it that way so it accepts two parameters: The query itself, and a boolean value indicating whether the query is executable or not. The logic applied in the method is quite simple, but let’s give it a look:

  • Initially, we’ll perform some necessary initializations, regarding mostly the structures that will store our data (NSMutableArray objects).
  • Then, we’ll open the database and if that’s successful, we’ll compile the query to a SQLite 3 statement (prepared statement).
  • If the query is not executable, meaning that is a select statement, we’ll fetch the desired data row by row. For each one, we’ll get each single column’s contents as a text value, and we’ll add it to a temporary mutable array. When all the columns of a data row have been read, we’ll add the temporary array to the final results array. At the same time, we’ll use another array to save the column names, as it’s quite possible to become handy when the database class will be used.
  • If the query is executable, meaning that is an insert, update, or delete statement, things will be much simpler. We’ll just execute the query and if the result is successful, then we’ll store to properties the affected rows and the last inserted ID values.
  • In case of any error, we’ll just output its description.
  • Finally, we’ll make sure to free up all used resources.

Let me now denote a couple of things. First of all, and according to what I said in the above description, we’ll get each column’s contents as a text value. This is a generic approach and you may think that it doesn’t suit to all kind of data existing to a database table. However, don’t forget that we want to implement a class that will work for all applications (or the most of them), so our solution shouldn’t be oriented to specific data. Moreover, the text (string) results can be converted to other data types and be handled in any desired way by an app.
At second, the fetched results will be stored to a two-dimensional array. Into each index of the results array, there will be another array containing the data of a single row. Personally, I consider this to be the best option for having the result set from the database file to memory. If you wish so, you could try to modify the implementation and use NSDictionary objects instead.

Let’s get started with the implementation now. We’ll do one step at the time, so we discuss each action we take. Besides that, there are plenty of comments in the code that will help you understand it much better.

First off, go to the private class section, and declare a mutable array property for storing our results, and of course, the private method itself:

If you wonder why this method is private, while it should be public, then the answer is simple: After having finished with it, we’ll create two small public methods, where we’ll use the first one just to load data from the database, and the second one to run executable queries. Both of those methods will call this, providing each one the proper arguments. Besides that, notice that the query parameter is a const char (a C string) and not a NSString* object. That’s because the SQLite functions don’t know anything about NSStrings, they just know how to handle C strings.

Next, go to the DBManager.h file, and in the public class section declare the next three properties:

I think there’s no need to explain what these properties are for.

Back to the DBManager.m file to get started with the implementation. As you’ll see right next, initially we perform four specific tasks: We declare a local SQLite 3 object to handle the database, we set the path to the database file, and we initialize the two array properties (the arrResults and the arrColumnNames arrays).

Note that if any previous data exist in any of the arrays, we get rid of them before we initialize the arrays again to make sure that nothing remains on memory.

Next, we must open the database, and if no error occurs we will use the sqlite3_prepare_v2 function to convert the query into a executable SQLite part:

As you see, we check if the database was opened successfully comparing with the SQLITE_OK value. Then, we declare the compiledStatement variable, which is a sqlite3_stmt type, for storing the compiled statement. Finally, we make use of the sqlite3_prepare_v2 function, in which we pass the database handler, the query and the compiled statement variable as parameters.

Inside the inner if clause above, we’ll check if the query is executable or not. In case it’s a select query, we’ll load all data specified by the query. Here we go:

We wrote some code here, so let’s discuss it. At first we declare a local array, named arrDataRow. In this one, we’ll store each data row fetched from the dataset existing in memory. Then, using the sqlite3_step function, we go through all the results row by row. Inside the while loop, we initialize the arrDataRow array and we get the total number of columns existing in the result set. Then, inside a new for loop, we get each column’s contents and we assign them to a char variable. If that value is not null, we add it to the temp arrDataRow array converted to a NSString* object. Next, if we haven’t done so already, we store each column name to the arrColumnNames array. Finally, when the for loop is over, we add the fetched data row to the arrResults array.

Let’s proceed to the case of an executable query:

Things are much simple here. We run the query, and if it’s all okay, we store the affected rows and the last inserted row ID to the respective properties. In the else cases you see that we show on the debugger the error description, when the query cannot be executed and when the database can’t be opened.

Finally, we need to free up the memory by releasing all used resources, and by closing the connection to database:

That was the last step needed to be performed in the method. Right below it’s given in one piece, as all the if-else cases and the curly brackets become confusing enough:

Loading Data and Executing Queries

Now that the core method of our class has been implemented, we have only left to make use of it. That’s simple, as we just have to implement two new small, public methods: One for running select queries and loading data, and one for executing insert, update and delete queries.

Let’s begin by declaring both of them. Open the DBManager.h file, and add the next two lines:

Let’s focus on the first method, named loadDataFromDB:. This method accepts as a parameter value the query we want to be executed as a NSString object. For example, such a valid string would be this: “select * from people where age > 18”. The fetched result set is returned as an array, and according to what I said in the previous section, this is going to be a two-dimensional array. The first array represents the rows, while each sub-array represents the columns of each row. Having said all that, let’s go back to the DBManager.m file, and let’s implement it:

As you see, it’s just a matter of two lines. In the first line, we call the runQuery:isQueryExecutable: method and we convert the query to a C string object. At the same time, we specify that the query is not executable.

In the second line we return the arrResults array that contains the query results, casting it first to a NSArray object. It would be a bad idea to make the arrResults array public and allow apps to have direct access to it, as then the returned data could be potentially altered before it used.

Going to the executeQuery: method now, here’s it its definition:

In this one, there is not a return value. However, the affectedRows property can be used to verify whether there were any changes or not after having executed a query.

A Sample Database

The database class is ready to be used, but how can we do that without having an actual database to test it? So, in this part I’m going to show you how to create a new database on Terminal, how to create a new table and finally how to embed the database file to the Xcode project. If you’re not used to work in a command line environment don’t worry; you’ll find out that doing so it’s interesting, different, even amusing.

For the purpose of this example we won’t create a complex database. On the contrary, we will create just one table, named peopleInfo, with the following fields:

  • peopleInfoID: An integer value, the primary key of the table.
  • firstname: A text value.
  • lastname: A text value.
  • age: An integer value.

Such a table is just fine to try out all the functionalities we want. Let’s see everything in action now. Click on the Spotlight icon, and type the Terminal word in the textfield:

Spotlight terminal

Once it gets spotted, hit the Return key on the keyboard, or click on its name with your mouse and the terminal window will appear. We are going to create a new database file named sampledb.sql. For the record, let me say that you can use any extension you would like for the file, or no extension at all. The most common extensions are:

  • .sql
  • .sqlite
  • .db

Personally, I like to give the .sql extension to my database files, but feel free to use anything that better suits to your preferences.

To create the above database, simply write the following in the terminal:

The database file will be created at once, and you’ll enter into the SQLite command line environment. The command needed to create a new table is quite similar to other SQL databases or DBMSs, with some slight differences though. Here it is:

Even though this tutorial has nothing to do with offering instructions on how to use the SQLite command line environment, I believe that it would be useful to mention just a couple of commands. These are the .tables and the .schema TABLE_NAME. The first one displays a listing with all the tables you’ve created in a database. The second one displays the the Create Table command for the table specified by the Table_Name parameter. Notice that both commands are preceded by the dot point (.), and there’s not a semicolon at the end of the commands. For clear SQL commands, you use no dot mark, but you add the semicolon.

To quit from the SQLite environment, just type:

That way you’ll return back to the terminal environment. The next screenshot of my terminal synopsizes all the above:

SQLite Terminal Sample

Our next task is to add the database file to the Xcode project. You have two options for doing that:

  • Either open the Finder, locate the file, and then drag and drop to the Project Navigator on Xcode, or,
  • Go to Xcode, open the menu File > Add Files to “SQLite3DBSample”…, and then locate the file and click on the Add button.

No matter which way you’ll choose, make sure that the Copy items into destination group’s folder (if needed) and the SQLite3DBSample target checkmarks are selected in the window that will appear before the database file gets added to the project.

Adding file option

Demo App Interface Setup

In most of my tutorials, this part is usually one of the first things you read. However, due to the fact that at the previous sections we created the database class and the sample file, the setup of the interface for our demo app had to be waiting up to this point. In here, as you’ve already assumed, we’ll work in the Interface Builder, and we will add all those subviews needed to make the app working, just like the sample demonstrated at the beginning of the tutorial. Further than that, we’ll add the necessary IBOutlet properties and IBAction methods that will make everything properly work.

On the Project Navigator, click on the Main.storyboard file and wait until it appears in the Interface Builder. As I said in the App Overview section, this is going to be a navigation based app, therefore select the View Controller scene and then go to the menu: Editor > Embed In > Navigation Controller. Instantly, a navigation controller will be added at the left side of the scene, and a new arrow will point from the navigation controller to the View Controller scene.

Adding navigation controller

Let’s focus now on adding all the necessary subviews to the scene, and after having finished with that, we’ll add one more scene (a new view controller) which we’ll use to add new records to the database. Right next you are given all the subviews you need to drag from the Objects library to the scene, along with the attributes needed to be specified:

UITableView

  • Frame: X=0, Y=0, Width=320, Height=568

UITableViewCell

Drag a table view cell in the table view to make it prototype
* Row Height: 60
* Style: Subtitle
* Identifier: idCellRecord
* Selection: None
* Accessory: Detail Disclosure

UIBarButtonItem

  • Add it to the right side of the navigation bar
  • Identifier: Add
  • Tint: Red=255, Green=128, Blue=0

Besides all the above, select the Navigation Item and set the next two attributes:
1. Title: SQLite 3 Demo
2. Back Button: Go Back

These are all the subviews of the View Controller scene. Here’s how it should look like:

Viewcontroller scene

Now, from the Objects library take a UIViewController object and drop it to the canvas. For this view controller, we need to create a new class, so for the time being we’ll just pause our work here.

Following the same way you did when you added the database class file to the project, add a new class of UIViewController kind. Specifically:

  1. Go to the menu: File > New > File….
  2. As the template for the file, select the Objective-C class in the Cocoa Touch category, under the iOS section.
  3. In the second step, make sure that in the Subclass of field there is the UIViewController value. If there is not, just type it. In the Class field, set the EditInfoViewController as the name for the new class, and then click on the Next button to proceed.
  4. In the last step, make sure that the SQLite3DBSample is checked, and click on the Create button.

Head back to the Interface Builder, and select the second, new scene. In the Utilities pane show the Identity Inspector, and in the Class field type the EditInfoViewController value. Immediately, the second view controller scene will turn into the Edit Info View Controller scene.

To connect the View Controller scene with the Edit Info View Controller scene, go to the Document Outline pane, Ctrl-Click on the View Controller scene object, and drag right onto the Edit Info View Controller object.

Custom segue in storyboard

In the black popup window that appears (titled Manual Segue), select and click the Push option:

Segue Push - SQLite Database

The two scenes will become connected, but there’s still one last thing to do. Click on the circle of the connection line, and in the Attributes inspector set the idSegueEditInfo as the identifier value for the segue.

SQLite - Setting segue identifier

Now, we can add the necessary subviews here too:

UITextField

  • Frame: X=20, Y=93, Width=280, Height=30
  • Placeholder: Type the first name…
  • Color: Red=255, Green=128, Blue=0
  • Border Style: No border style
  • Background: Red=217, Green=219, Blue=221
  • Capitalization: Words
  • Return Key: Done

UITextField

  • Frame: X=20, Y=139, Width=280, Height=30
  • Placeholder: Type the last name…
  • Color: Red=255, Green=128, Blue=0
  • Border Style: No border style
  • Background: Red=217, Green=219, Blue=221
  • Capitalization: Words
  • Return Key: Done

UITextField

  • Frame: X=20, Y=186, Width=280, Height=30
  • Placeholder: Type the age…
  • Color: Red=255, Green=128, Blue=0
  • Border Style: No border style
  • Background: Red=217, Green=219, Blue=221
  • Capitalization: Words
  • Return Key: Done
  • Keyboard: Numbers and Punctuation

UIBarButtonItem

  • Add it to the right side of the navigation bar
  • Identifier: Save
  • Tint: Red=255, Green=128, Blue=0

Also, edit the navigation item and set the Edit Info value as its title.

Here’s how the second view controller should look like:

SQLite - Edit infoview controller

Let’s see now what IBOutlet properties and IBActions are needed to be declared for the subviews we previously added. Starting from the View Controller scene, we want an IBOutlet property for the table view, so as we can display our data, and an IBAction method for the bar button item, which we’ll use to perform the segue and transit to the EditInfoViewController view controller. Open the ViewController.h file, and add the next two declarations:

Go back to the Interface builder and make the proper connections.

Next, for the Edit Info View Controller scene we’ll need an IBOutlet property for each textfield, and an IBAction method for the save button. Similarly to the previous steps, open the EditInfoViewController.h file and declare them:

Finally, go to the Interface Builder once again and do the appropriate connections, as shown in the next figure:

SQLite - IBconnection

Note that when you’ll be viewing the EditInfoViewController view controller, the back button will appear to the navigation bar along with the Save button. To let it have the same color to the Save button you just need to go to the EditInfoViewController.m file, and to add the next line in the viewDidLoad method:

Adding New Records

Having the interface of the sample app prepared, we can go towards the implementation of our sample app where we’ll try out our database class. As no data still exist in our database, the best point to start from would be to develop the feature of adding a new record. That requires work in both of our view controllers: In the View Controller class we should make the Add (plus button) functioning, so we can navigate to the next view controller. In the Edit Info View Controller class, we will get all values from textfields, and using an insert command, we’ll add a new record to database.

Let’s see everything in details. Go to the ViewController.m file, and straight ahead to the addNewRecord: IBAction method. Navigating to a new view controller using segues is easy, so the only thing we have to do here is to write just one line of code:

Focusing now our attention on the EditInfoViewController class, the first thing we should take care about is to dismiss the keyboard every time the Done button on it is tapped. To do so, we must implement one textfield delegate method, but first, we must adopt the respective protocol. So, open the EditInfoViewController.h file, and modify the header line as follows:

Next, we must indicate that our class is the delegate of the textfields, and that can be done in two ways: Either to do it in Interface Builder, or to do it programmatically. The second is the way I prefer, as I think is much simpler. Open the EditInfoViewController.m file and go to the viewDidLoad method. In there add these three lines:

Now, we are ready to implement a simple delegate method and know when the Done button of the keyboard gets tapped. That method is the textFieldShouldReturn:, and in its implementation we’ll perform just one task: We’ll resign the textfield from first responder. Here it is:

Time to work with the database. Go to the top of the file, and import our custom class:

Now that the database class can be used, let’s declare a private property to do so. Go to the private class section and add the next declaration:

Then, in the viewDidLoad method initialize it using the custom init method we implemented:

By initializing as shown above, upon the first run the database class will check if the sampledb.sql file exists or not in the documents directory, and it will copy it there if not found.

What we have left to do now is easy, but the most important part. Using all the textfield text values, we’ll prepare an insert query and we’ll execute it. To verify the outcome of the execution we’ll output to the debugger either a success message, or the error message if any error occurs. Also, upon successful saving we’ll display the number of the affected rows, so we’ll manage to see if our class works as expected.

To do all that go to the saveInfo: IBAction method. Right next is given its implementation:

Notice that after having added a new record successfully, we also pop the view controller and return back to the View Controller one.

The app can be tested now for first time. Compile and run it, and try to add a new record. When you finish typing data, tap on the Save button and watch at the debugger. If you see a message similar to the next one, then congratulations, the database is working!

SQLite - Insert Record

Loading and Displaying Records

Now that we can successfully add records to the database, the most reasonable next step is to display them, so we’ll work on the View Controller class. Summarizing what we have to do here, I would say that our job has two parts: The first one is to implement all the necessary, required table view methods so our loaded data from the database get listed properly on the table view. The second one is to create a select query, load our data and finally display them.

If you recall, when we did all the setup in the Interface Builder, we declared an IBOutlet property for the table view. Using it, we’ll make the View Controller class both the delegate and datasource of it, but prior to that we must adopt two necessary protocols, the UITableViewDelegate and the UITableViewDataSource. As always, this will take place in the ViewController.h file, where we’ll modify the @interface header line as follows:

Next, in the ViewController.m file and in the viewDidLoad method we just have to write these:

Now, let’s get started by loading the data from the database. To do so, we need three things:

  1. To declare and initialize a DBManager property.
  2. To declare and array for storing the returned data.
  3. A private method to do the job.

Go to the private section of the class, and declare the next two properties, and one private method:

Don’t worry if Xcode is showing an error. We just need to import our database class header:

Now, as we exactly did in the EditInfoViewController class, we’ll initialize the database property here as well:

Our next task is to implement the loadData method, and prepare the select query. Let’s see it first:

Three things happen here: At first, the query string is prepared, and as you see, is a simple select statement. Then, the arrPeopleInfo array gets initialized, and upon its initialization we call the loadDataFromDB: method of the dbManager object. Once the data has been fetched and returned, we reload the table view to display it. For the time being the last line will do nothing, but after we have had our table view ready, it will be working just fine.

Loading data is as simple as you see in the previous code snippet. Having done so, enables us to move ahead and work with the table view. Note that based on the arrPeopleInfo array, we will be able to define the total rows of the table view.

Regarding the table view methods, let’s get started from the easy ones:

In the first one, we tell our table view that we want to have just one section. With the second one, we specify the total number of rows displayed in the table view. Actually, this method will return as many rows as those existing in the result set that will be loaded from the database. Finally, with the third one we set each row’s height to 60.0 points.

To display a row’s data, we’ll use the tableView:cellForRowAtIndexPath: method. In it, initially we’ll dequeue the prototype cell we created in the Interface Builder. Then, making use of the arrColumnNames array of the dbManager property we’ll define the index of each column (field) in the sub-array for the current index of the arrPeopleInfo array. Finally, we’ll get the actual data and assign it to the textLabel and detailTextLabel labels of the cell. Here’s the implementation:

Notice that instead of using the indexOfFirstname, indexOfLastname and indexOfAge variables for accessing the proper data, we could have used the index number for each field directly. For example, instead of writing this:

we could just have written this:

Using or not the column names array is totally up to you, and I used it here just for demonstrative reasons. I just wanted to make clear how you can specify the index of a field programmatically, even though our example is quite simple and it is not necessary to do this.

So, it seems that we’ve finished taking all the proper actions for loading the data and showing it to the table view. However, if you run the app now, you’ll see no data displayed at all. That’s because of a simple reason: We didn’t call the loadData method anywhere. So, go to the viewDidLoad method and add the next line:

Run the app once again. This time, the records you added in the previous step are shown in the table view. That’s great.

Before we consider this section as finished, there’s one more detail we should take care about. You’ll definitely notice that when adding a new record and then tapping on the Save button, the record is saved and you’re transferred back to the View Controller view controller, but the table view doesn’t get updated with the new data, unless you re-launch the app. That’s because for the time being the View Controller view controller cannot know whether new data have been added to the database after the Edit Info View Controller view controller has popped from the navigation stack. To workaround this, we must create a protocol in the EditInfoViewController class, declare a delegate method to be used for notifying when a new record has been added, and adopt that protocol to the ViewController class.

Let’s give life to all the above. Start by opening the EditInfoViewController.h file. At the top of it, add the next code fragment:

Next, in the interface right below, add a declaration for the delegate property:

Finally, go to the saveInfo: IBAction method and modify it, so the delegate method is called before popping the view controller. Right next, you are given that method updated:

With the above three steps, the EditInfoViewController will notify the delegate class every time a new save action occurs.

Let’s go now to the ViewController.h file. Go to the top of it, and make the next import:

Then, modify the @interface header a bit:

As you see, now we are adopting the EditInfoViewControllerDelegate protocol, so the only thing we have left is to implement the delegate method. Go to the ViewController.m file, and add the next code segment:

As you may have expected, the only thing we do is to load our data once again.

However, nothing is going to happen until we make the ViewController class the delegate of the EditInfoViewController one. We’ll do that in the prepareForSegue:sender: method, which is called before the segue is performed. Here it is:

If you run the app, you’ll see that is working and behaving much better now. When adding new records, they immediately appear in the table view after having tapped on the Save button.

Editing a Record

Our sample app will be much better if we add support for editing and deleting records, apart from just adding new and loading existing ones. Also, that way we’ll see in action how the update and delete queries work, and we’ll test our database class even more.

In this section we’ll see how to edit a record. Running the app and looking at the table view, you notice that we have added a detail disclosure indicator on the prototype cell. That’s what we’ll use to edit a record matching to the respective row. The actual editing will take place in the EditInfoViewController class, which we’ll modify a little bit, so when saving it will either execute an insert or an update query.

The most important part of the underlying logic is the existence of a public int property in the EditInfoViewController class, in which the edited record’s ID will be assigned. When saving, that property will be used to specify the exact data that should be updated. However, in case of adding a new record, we’ll set the −1 value to this property, indicating that way we want to run an insert query, not an update. Lastly, when the EditInfoViewController view controller is appeared to edit a record, the data for that specific record will be loaded and assigned to the textfields.

If all the above seem confusing, don’t worry. Come along to perform some simple steps, and everything will become crystal clear.

Let’s get started from the EditInfoViewController.h file, where we should declare the public int property:

Now, open the EditInfoViewController.m file and declare a private method which will be used to load the edited data. Go to the private section of the class, and add the next line:

Now, let’s implement it:

Everything is quite straightforward here. At first, we form the query. As you notice, it’s the first time we have a where clause. Then, we get the data from the database and assign the appropriate values to the textfields.

The above method won’t work if we don’t call it, so go to the viewDidLoad method and make a conditional call:

Great, but we’ve left one more step in this class: To modify the saveInfo: IBAction method and make it update an existing record instead of just inserting new records to the database. Right below you’re given the method implemented in its final form. Notice how using the recordIDToEdit property we specify the query type:

Our job in the EditInfoViewController class is over. However, that was just the half part of what we should do. We must go to the ViewController class now and make the detail disclosure button of the cells functional.

To detect tappings on the detail disclosure buttons, UITableViewDelegate protocol provides the tableView:accessoryButtonTappedForRowWithIndexPath: delegate method. We are going to implement it, and inside its body we’ll do two simple tasks:

  1. We will specify the record ID that’s about to be edited.
  2. We’ll perform the segue so the EditInfoViewController view controller gets pushed to the navigation stack and make us able to edit.

We’ll get started by declaring a private property to the ViewController class similar to the recordIDToEdit, because we’ll need a way to keep the record ID matching to the row of the tapped button. So, go to the private section of the interface, and do so:

With the above private property declared, we can proceed to the tableView:accessoryButtonTappedForRowWithIndexPath: implementation:

Easy enough, but now we must tell the EditInfoViewController class what the record ID we want to edit is. This should take place before the segue is performed, so go to the prepareForSegue:sender: and add just one line:

We are almost finished. The only issue that we’re still having, is that we haven’t specified the value of the recordIDToEdit property when we’re about to add a new record. That can be easily fixed, as we just have to go to the addNewRecord: IBAction method definition, and modify it as follows:

Everything is ready now. Feel free to run the app once again, and then try to edit one or more of any existing records. As it seems, our database class perfectly works and our sample app gains more features.

Deleting a Record

Now that we can insert, load and update records, let’s make our app able to delete them as well. To delete a record, we’ll use the built-in table view functionality, where by swiping on a row towards left, the delete button is appeared.

To do that possible, we must implement the tableView:commitEditingStyle:forRowAtIndexPath: table view method. In it, if the editing style is a delete style, we’ll execute a delete query, and then we’ll reload the data. As simple as that, and the record deletion will be ready. Let’s see it:

At first, we set the ID of the record to be deleted. Then, we compose the query string appropriately, we execute it using the executeQuery: method of our database class, and finally we load the data once again. That way, every time the delete button of a row is tapped, the respective record on the database will be permanently removed.

If you want now give the app one more try, and see how it works. All the basic features are now implemented and fully functional.

Compile and Run the App

This section exists in almost every tutorial in case you are one of those guys who wait to arrive at the finish point, and then run and test the app. Well, we’re almost at the end of our mission, so if you still haven’t run it, do it now. Begin by adding a new record, and then save it. Next, try to edit it, and finally delete it. Add as many records as you want and generally do whatever you want for testing both the sample app and the database class. Even more, add any extra features you may think of and build more complex queries.

Right next it’s demonstrated the animated graphic presented at the beginning of the tutorial too:

SQLite Database Demo App

Summary

I’ve always believed that working with databases is the best possible solution when dealing with data, and having relevant tools or libraries ready for use is a must for every developer. SQLite might not be as powerful as other databases or database management systems, however is light and maybe the most suitable solution for a mobile platform. The class we developed in this tutorial is not set of stone, so keep in mind that you can change it and evolve it at any time and in any possible way that serves you. With it, I just wanted to provide you with a working, suitable for the most cases, reusable component, which could potentially have double results: Both to teach you the basics of SQLite, and to get you out of the hassle of developing other custom data handling solutions. That said, I truly hope you have learned and earned something new by this tutorial, and as always let me know your thoughts or questions. Until next time, I wish an easy, bug-free programming to all!

For your reference, you can download the complete Xcode project from here.

iOS
A Swift Tutorial for Google Maps SDK
iOS
A Beginner’s Guide to Automated UI Testing in iOS
iOS
iOS Programming 101: How To Send SMS Text Message in iPhone Apps
  • Fernando

    FernandoFernando

    Author Reply

    How can I store an image?


  • Ioannis Gman

    greek??nice to see such a good explained tutorial..bookmarked and will try this soon.. even tough i like the use of storyboards instead of interface builder! 😀
    well done! and thank you very much


    • Steely

      SteelySteely

      Author Reply

      The pictures above indicates that he is using Storyboard 🙂


  • bader

    baderbader

    Author Reply

    Thank you. Regards


  • Anita Agrawal

    Thank you for this tutorial.


  • Anita Agrawal

    How do we provide a path for image to store in data base from gallery?


    • Steely

      SteelySteely

      Author Reply

      simply use the tutorial to get the name of the image.
      Store the image in files and add the image name to the database.

      Get the name from database and use it like this:
      UIImage *myImage = [UIImage imageNamed:yourStringFromDatabase];


      • Anita Agrawal

        what will be the storage type for image in database table,which I need to create on terminal?


        • Steely

          SteelySteely

          Author Reply

          If the image name is only numbers = INT
          If the image contains characters and numbers = TEXT


          • Anita Agrawal

            Anita AgrawalAnita Agrawal

            Author

            Actually I want to choose image from gallery, so I used in editViewController a button named upload image,and an UIImageView to show the image.
            I am able to choose image from gallery,but facing problem while saving that image in the database.


          • Steely

            SteelySteely

            Author

            First Method:
            The easy way to do that is then to take the picture from the gallery and save it in documents. This should give you an idea on how to do so: http://stackoverflow.com/questions/6545180/ios-copy-a-file-in-documents-folder

            Then get the image name and store that in the database.

            Second Method: (complicated)
            Take the picture and convert the picture into binary blobs and save the blobs to database.


      • Gabriel Theodoropoulos

        Hello everbody,
        Sorry for the so late reply…
        The best way to store images is as Steely says. Keep the image file names to the database, and store the actual images to files. Don’t forget that your database is going to work on a mobile device, and you don’t want to consume too much memory when loading the database. Keep it as lightweight as you can.


        • Alex

          AlexAlex

          Author Reply

          Hi Gabriel,

          I try this to store the image in the database :
          UIImage *coverPhotoImage = _backgroundPicture.image;
          NSData *coverPhotoData = UIImagePNGRepresentation(coverPhotoImage);
          And it works.

          But when I try to retrieve it, it doesn’t work :
          NSData *coverPhotoData = [[self.arrPeopleInfo objectAtIndex:0] objectAtIndex:indexOfBackgroundPicture];
          UIImage *coverPhotoImage = [UIImage imageWithData:coverPhotoData];
          _backgroundPicture.image = coverPhotoImage;

          Do you know why ?


  • Marina Landisberg

    i named my sqlite database sampledb.sqlite. When i was running in “debug” mode, I was getting: “SQLite3DBSample[493:60b] The operation couldn’t be completed. (Cocoa error 4.)” after executing line:”[[NSFileManager defaultManager] copyItemAtPath:sourcePath toPath:destinationPath
    error:&error];”


    • Marina Landisberg

      I found why that was happening. instead constant “NSDocumentDirectory” I got something like NSDocumentationDirectory from one of constants, suggested by xcode. Now everything works fine. Thanks


  • cymst~

    cymst~cymst~

    Author Reply

    A little complex… and thanks very much


  • FF

    FFFF

    Author Reply

    Hi there, is there a way to use the same database with a desktop app or web app?


    • Gabriel Theodoropoulos

      Hi,
      There are two options to do what you want: Either to keep a local and an online version of the database and always keep both of them updated, or just have an online database (MySQL or another DBMS, not SQLite), and store your data on the web. The second is simpler of course, but everything depends on your app requirements.


  • yaseen Almannaع

    Thanks for this great tutorial and if you can add “how to implement a search bar” on this project that will be appreciated.


    • Gabriel Theodoropoulos

      Hi Yaseen,

      Here you can find a tutorial on how to add a search bar on a table view that will definitely help you out. Currently, there are other tutorials being prepared, and I’m afraid that I won’t manage to create a new project for this one quite soon. Hope it helps you.


      • mostafa

        mostafamostafa

        Author Reply

        Hi , It was wonderful and very good tutorial. Thanks for this.

        But I added the search bar and added some code for this. but it does not work for me.

        Is it possible to add search module on this or another sample that have search bar and work with database?


  • Jeroen

    JeroenJeroen

    Author Reply

    Thanks for this nice tutorial


  • Alex

    AlexAlex

    Author Reply

    Great tutorial!! Nice starting point for working with SQL engines.


  • ShanghaiTimes

    Nice tutorial. You don’t always say which file you are adding code to, and that can be a little confusing, but I worked it all out in the end, and it works nicely.
    Having this foundation, I can now add SQL functionality to my other projects as needed.
    Thanks for sharing.


    • Gabriel Theodoropoulos

      Hi, thanks…
      I’ll keep in mind to always specify with more details the files where the code should be added to, but I’m glad you found your way at the end. Keep up the good work!


  • Alexander

    AlexanderAlexander

    Author Reply

    This is the best tutorial I’ve ever seen !!! Thank you so much !!!


  • Alex

    AlexAlex

    Author Reply

    For those who wants to use a “Disclosure indicator” instead of a “Detail disclosure” : select the Table View Cell, then the “Show the attributes inspector”, and change the “accessory” to “Disclosure indicator”. In ViewController.m, you just have to change the “accessoryButtonTappedForRowWithIndexPath” by “didSelectRowAtIndexPath”


  • Ash Cresswell

    brilliant Tutorial, however just one question, as im adding it the data on either my iPhone or the emulator, its not displaying in the.sql file, should it be and if not where is it storing the information? it remains on my phone and in the emulator mind like it would if i was using CoreData


    • Ryan Michael

      I too would like to know how to pass null into the query successfully. I get a syntax error and it will not allow me to add a record (could not execute the query


      • Ryan Michael

        Ash….think i figured it out. it is an auto incremented value, therefore null is just a placeholder for the id value of the table……….at least that it what I am thinking because I got it too work


  • ShanghaiTimes

    Hi again. I’ve made a few changes, all working fine, except … how do I store a URL into an sqlite3 database? ie;

    http://www.isleofskyeweather.org.uk/24elgol/clientraw.txt

    generating, [1028:60b] unrecognized token: “:” type errors. I realise I have to escape the string .. but how?

    I can not save a url until I can 🙂
    thanks


  • Abdulaziz

    AbdulazizAbdulaziz

    Author Reply

    hello,

    first of all thanks a lot for this tutorial.

    I try to understand the code, and I came a cross a statement that I don’t know the purpose of it.
    in the copyDatabaseIntoDocumentsDirectory method, What is the purpose of the following statement:
    NSString *destinationPath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFileName];

    doesn’t the documentsDirectory contain the path, so in the if condition we check if the documentsDirectory exists or not?

    plz, if i’m missing something or misunderstood anything explain that for me.


  • Esra

    EsraEsra

    Author Reply

    Hi, when I “Reset Content and Settings” the iOS Simulator, it doesn’t keep the data that I stored. Why is that? Thanks


    • Gabriel Theodoropoulos

      Hi. That’s normal to happen, as the *Reset Content & Settings” works like the formatting does on hard drives. It deletes any contents existing on the Simulator.


      • Esra

        EsraEsra

        Author Reply

        Thank you for reply 🙂


  • Miguel Angel Rodríguez

    Hi Gabriel,

    Excellent tutorial! Thanks for sharing.

    When I run the app on an iPhone using iOS 7 from XCode I get the following error:

    DB: Error Unknown when I try to save some data set. In the phone, the Save button is not working as expected since it is not closing the Edit Info VC… But, if I close the app and reopen it again the values are on the Table View.

    Can you help us?

    Thanks.


    • Chris

      ChrisChris

      Author Reply

      Hi,

      I had the same problem.

      I changed the variable “executeQueryResults” to an int instead of a BOOL. That seems to have solved the problem.


      • Vikram

        VikramVikram

        Author Reply

        Thanks a lot. Had almost given up. Works fine now


      • Stacey

        StaceyStacey

        Author Reply

        Thanks, same thing was happening with me as well.


      • Fraed Jay

        Fraed JayFraed Jay

        Author Reply

        Thanks,my problem has been solved.


    • AzusaD

      AzusaDAzusaD

      Author Reply

      Thanks so much! I spent a few hours trying to figure out why this was happening. Then I checked the comments and there was the answer.


  • Vakas

    VakasVakas

    Author Reply

    I have an existing sqlite3 database. I want to use it in my iOS app. I have configured it successfully to use in my app. It saves whatever I want to save from the app but doesn’t read the data entries already present in the that database. What’s wrong here?
    Also the data doesn’t appear which is added by iOS app when I retrieve using terminal or any other tool but inside application it reads those records.


  • Matty G.

    Matty G.Matty G.

    Author Reply

    Fantastic tutorial! I feel it has given me a strong, solid grounding in using sqlite databases in future apps. Thanks for writing such a thorough and detailed tutorial, it’s very much appreciated!


  • TechUser2011

    In the DBManager class, are all the data fields kept as NSString? For example, in your database you have an integer field for the person’s age. Is this field kept as a string? And if we are reading from the table, we would have to convert the NSString back to integer to use its int value, right?


  • velociround

    Great tutorial! Thank you very, very much!

    There’s just a problem I found with the downloadable project.

    BOOL executeQueryResults = sqlite3_step(compiledStatement);
    if (executeQueryResults == SQLITE_DONE) {
    // code
    } else {
    // error code
    }

    Comparison of constant with bool is always false. You should change to int, NSInteger or whatever, otherwise SQLite will insert the data, but your code will always think there’s an error.
    int executeQueryResults = sqlite3_step(compiledStatement);


    • Ted

      TedTed

      Author Reply

      THIS IS HUGE. My code on iPhone 6 was failing because of this. Thank you for noticing! The author should update his code ASAP


    • Joey

      JoeyJoey

      Author Reply

      Very important remark there! Please integrate in this otherwise awesome tutorial!


    • matogus

      matogusmatogus

      Author Reply

      But. you can run on iOS 7.1


    • Ben

      BenBen

      Author Reply

      I found this as well. Got round it by getting rid of the BOOL execute.. line and making the the if statement directly if(sqlite3_step(compiledStatement) == SQLITE_DONE){..


    • 张一一

      张一一张一一

      Author Reply

      Please author update this code! This BUG take me so much time, 🙁


    • sam

      samsam

      Author Reply

      Thanks for correcting the error. How can i check if a name already exists in the database and let users know that name already exists if they try to save. Thanks.


    • Bill

      BillBill

      Author Reply

      Yes, this ‘bug’ took me hours, author please update it!


    • Mohammed Hasen

      BOOL executeQueryResults = sqlite3_step(compiledStatement);
      if (sqlite3_step(compiledStatement)) {
      self.affectedRows= sqlite3_changes(sqlite3Database);
      self.lastInsertedRowID = sqlite3_last_insert_rowid(sqlite3Database);
      }
      change it to above one it will work


  • Dwight

    DwightDwight

    Author Reply

    I tried this tutorial, but I got a uncaught expression:
    __NSArrayI objectAtIndex:]: index 0 beyond bounds for empty array

    how can I fix this?


    • Dwight

      DwightDwight

      Author Reply

      Nvm Fixed it ^^;


      • Eric

        EricEric

        Author Reply

        how did you fix this? I’m getting this too


      • Amir Ashamalla

        How did you fix it????? i had to put a record with id=0 but then all my edits are to that exact record could never retrieve any other record


        • Neroroms

          NeroromsNeroroms

          Author Reply

          Amir, Please check for loop at DBManager. Did you set i = 1 or i = 0.

          If you already solve it. Sorry for this reply.


  • Carlos

    CarlosCarlos

    Author Reply

    Sorry, but I had a few problems putting the code together, I keep getting messages of missing identifiers, for example: -(void)copyDatabaseIntoDocumentsDirectory{
    // Check if the database file exists in the documents directory.
    NSString *destinationPath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFilename];
    if (![[NSFileManager defaultManager] fileExistsAtPath:destinationPath]) {
    // The database file does not exist in the documents directory, so copy it from the main bundle now.
    NSString *sourcePath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:self.databaseFilename];
    NSError *error;
    [[NSFileManager defaultManager] copyItemAtPath:sourcePath toPath:destinationPath error:&error];

    It says I need a semi colon before the bracket and after I add it, it then says that I need to declare an identifier or use a parenthesis. Can anybody tell me if I’m missing something?


  • orit

    oritorit

    Author Reply

    How can I import SQLite files to swift? I don’t want to work on the built-in SQL. Is there any recommended guide?


  • Domingo Figueroa

    Muchas gracias por tu gran aporte.


  • Abhijeet Gite

    Please provide the sample code in SWIFT. Need urgently.


  • najoua mahi

    Very Very Very interesting tutorial, well explained and rich of informations. Thank you so much 🙂


  • evian

    evianevian

    Author Reply

    Great tutorial—just wondering why you don’t choose to use Core Data?


  • Ashley

    AshleyAshley

    Author Reply

    Amazing Tutorial! Very well written and a fantastic treasure trove of information for someone starting out with DBs in iOS apps. Thank you very much!! Loved it.


  • shyam

    shyamshyam

    Author Reply

    First of all thanks Gabriel for this helpful article.
    I am new to IOS development. I have integrated your solution into my project. I have created an sqlite db and added it to my project. But i am facing an issue in copyDatabaseIntoDocumentsDirectory method. Database is not getting copied and it always return following error:
    The operation couldn’t be completed. (Cocoa error 4.)

    Can you please help to sort it out.


  • Amir Ashamalla

    all my edits are to a single record (id=0)could never retrieve any other record; i checked and the recordtoedit is always 0???? please help


  • sagar vaholiya

    #import “TableViewController.h”
    #import “DBManager.h”
    #import

    @interface TableViewController ()
    @property (nonatomic, strong) NSArray *dataInfo;
    @property (nonatomic,strong) DBManager *dbManager;

    @end

    @implementation TableViewController

    – (void)viewDidLoad {
    [super viewDidLoad];
    // [self openDB];
    self.tableView.delegate = self;
    self.tableView.dataSource = self;

    self.dbManager = [[DBManager alloc] initWithDatabaseFilename:@”db_tableview.db”];

    NSString *query = @”select name,age from data”;

    if(self.dataInfo != nil)
    self.dataInfo = nil;

    self.dataInfo = [[NSArray alloc] initWithArray:[self.dbManager loadDataFromDB:query]];
    NSLog(@”%@”,self.dataInfo);

    [self.tableView reloadData];

    }

    //tableView methods

    -(NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
    {
    return self.dataInfo.count;
    }

    -(NSInteger)numberOfSectionsInTableView:(UITableView *)tableView
    {
    return 1;
    }

    -(UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
    {
    static NSString *cellidentifier = @”cell”;
    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:cellidentifier forIndexPath:indexPath];
    NSInteger name = [self.dbManager.arrColumnNames indexOfObject:@”name”];
    NSInteger age = [self.dbManager.arrColumnNames indexOfObject:@”age”];

    if (cell == nil)
    {
    cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:cellidentifier];
    [cell setSelectionStyle:UITableViewCellSelectionStyleBlue];
    [cell setAccessoryType:UITableViewCellAccessoryDisclosureIndicator];
    }
    cell.textLabel.text = [NSString stringWithFormat:@”%@”,[[self.dataInfo objectAtIndex:indexPath.row] objectAtIndex:name]];
    // cell.textLabel.text = @”this is text label”;
    NSLog(@”Name is: %@”,cell.textLabel.text);
    // cell.detailTextLabel.text = @”This is detail label”;
    cell.detailTextLabel.text =[NSString stringWithFormat:@”%@”,[[self.dataInfo objectAtIndex:indexPath.row] objectAtIndex:age]];
    NSLog(@”Age is: %@”,cell.detailTextLabel.text);
    return cell;
    }
    -(CGFloat)tableView:(UITableView *)tableView heightForRowAtIndexPath:(NSIndexPath *)indexPath{
    return 60.0;

    }


    • sagar

      sagarsagar

      Author Reply

      error while fetch value of “age” field from an array….


  • wgiesel

    wgieselwgiesel

    Author Reply

    Hello,

    This is working great in my little project. But once database is written in the document folder it won’t be overwritten when you ship an update with a newer database.
    Is there any way to improve DBmanager Class with a version control for Database.
    Thanks in advance.
    Wolfgang


  • Vishnu

    VishnuVishnu

    Author Reply

    Hi,In this case of creating sqlite file by coding directly does result in creation of sqlite file in the bundle of the project file? please help


  • iCoderOnline

    Thanks for this sample project. Got a question tough, is there a way to show the last updated/added insert first in the tableview instead of last? Thanks in advance.


  • hj

    hjhj

    Author Reply

    If I have an app where I made purchases like ringtones and photos, can I extract the these audio and picture files using this?


  • veeru

    veeruveeru

    Author Reply

    Awesome article – I am a newbie in programming and I could understand a major part of the project. However when I tried to implement this with a custom data then I am getting duplicate data – duplicate data count = number of columns in the data.
    I understand that array count was total number of elements, what I don’t understand is why tableView cellForRowAtIndexPath is called as many times as the count and not based on the index of the database. when I delete though, it deletes all the rows of that specific data along with duplicates.

    Plz help me out.

    -(NSInteger) tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section{
    // NSLog(@”arrTestInfoCount is %lu”,(unsigned long)self.arrTestInfo.count);
    return self.arrTestInfo.count;
    }

    -(NSInteger) numberOfSectionsInTableView:(UITableView *)tableView{
    return 1;
    }

    -(CGFloat) tableView:(UITableView *)tableView heightForRowAtIndexPath:(NSIndexPath *)indexPath{
    return 60.0;
    }

    -(UITableViewCell *) tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath{
    static NSString *cellIdentifier = @”idCellRecord”;
    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:cellIdentifier];
    if(cell == nil){
    cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:cellIdentifier];
    }
    // NSLog(@”Creating a new cell”);
    //set the loaded data to the appropriate cell labels
    cell.textLabel.text = [NSString stringWithFormat:@”%@ %@”,[[self.arrTestInfo objectAtIndex:indexPath.row] objectAtIndex:0],[[self.arrTestInfo objectAtIndex:indexPath.row] objectAtIndex:1]];

    cell.detailTextLabel.text = [NSString stringWithFormat:@”time: %@”,[[self.arrTestInfo objectAtIndex:indexPath.row] objectAtIndex:2]];

    return cell;
    }


    • veeru

      veeruveeru

      Author Reply

      Never mind, my DBManager.m had an error. Imported the class files and it worked :). thank you so much for this awesome tutorial and source code.


  • Rajneesh Gosai

    i have error in line :

    self.dbManager = [[DBManager alloc] initWithDatabseFileName:@”sample.sql”];

    No visible @interface for ‘DBManager’ declares the selector ‘initWithDatabaseFilename’


  • Igor Bastos

    What a awesome tutorial! It helps a lot!! Thanks


  • megha

    meghamegha

    Author Reply

    i have make remote application but i want to make it locally i have done database connection but i am getting trouble to assign table value to store data can you just help me how to do it as i have create class for cell and i also use nsdictionary for parsing data


  • Gabriel Theodoropoulos

    Hi everybody…

    Please refer to the comments of this post in case you have problems in executing queries. There’s a reply of mine where I highlight a couple of changes you could do.


  • Juan

    JuanJuan

    Author Reply

    Hello excellent tutorial everything works perfect.

    I now want to add a picture to the UITableView.

    The object image of the phone book contacts, as the firstname, lastname, phone etc…

    but it does not work leg image add a BLOB field (I convert the image to NSData), seems it saves well but when I try get image it gives me error.


  • amorbytes

    amorbytesamorbytes

    Author Reply

    I am bit new to iOS development. what is the usage of “queryExecutable” variable ? I am unable to understand it.


  • amorbytes

    amorbytesamorbytes

    Author Reply

    Nice tutorial too.


  • amorbytes

    amorbytesamorbytes

    Author Reply

    Do you allow me to create a screenshot of “SQLite 3 Functions Preview” section and pin in on pinterest or post it on social media ?


  • WonHee

    WonHeeWonHee

    Author Reply

    Replace

    if (executeQueryResults == SQLITE_DONE)

    to

    if (sqlite3_step(compiledStatement))


  • Dan BearJew Friedman

    Hi, I have a question about populating the results into a UIPickerView. When I call the results and put them into an array, then use that array to populate a pickerview, it comes back with showing me “?” instead of the actual values. However, the values show up just fine in the NSLog readout and when I select a particual field on the pickerview, it responds with the approporiate value in the NSLog readout. What could be the problem?


  • Fraed Jay

    Fraed JayFraed Jay

    Author Reply

    Thanks for sharing! And I like to ask one question what is the “@”idCellRecord” for in “UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:@”idCellRecord” forIndexPath:indexPath]; ”
    When I run my app the debuger display something like this:

    2015-08-25 14:03:16.569 SQLite3DSSample[3711:144903] *** Assertion failure in -[UITableView dequeueReusableCellWithIdentifier:forIndexPath:], /SourceCache/UIKit_Sim/UIKit-3347.44.2/UITableView.m:6245

    2015-08-25 14:03:16.576 SQLite3DSSample[3711:144903] *** Terminating app due to uncaught exception ‘NSInternalInconsistencyException’, reason: ‘unable to dequeue a cell with identifier idCellRecord – must register a nib or a class for the identifier or connect a prototype cell in a storyboard’

    What can I do to solve this problem?


  • Hooray

    HoorayHooray

    Author Reply

    Has anyone converted this to swift yet?

    The main thing that should be converted is the DBManager file.


  • Roy

    RoyRoy

    Author Reply

  • Ashik

    AshikAshik

    Author Reply

    I create a database and table from terminal.Database created and copy to document directory from app bundle successfully.

    But this statement,
    sqlite3_prepare_v2(sqlite3Database, query, -1, &compiledStatement, NULL) is
    return 21.

    It also show, no such table:tableName error.


  • Wouter Liefting

    Nice tutorial, but there’s one thing I really want to get off my chest.

    The tutorial consistently suggests that this is proper the way to assemble an SQL query:

    NSString *query = [NSString stringWithFormat:@”select * from peopleInfo where peopleInfoID=%d”, self.recordIDToEdit];

    This is absolutely, utterly WRONG!

    Doing it this way will open up your code to “SQL injection attacks”, where somebody enters data containing special characters like single quotes, semicolons and such, that have a special meaning to SQL. They may then alter your query into something that reveals or destroys data.

    The correct way of doing this is different. You define the query with a question mark in place of the user data. You then use the appropriate “bind” call to let the SQLite code insert the user data in the proper position. This also takes care of quoting your metacharacters properly, and is thus THE way to prevent SQL injection attacks.

    The query preparation then becomes:

    NSString *query = [NSString stringWithFormat:@”select * from peopleInfo where peopleInfoID=?”];
    sqlite3_prepare_v2(sqlite3Database, query, -1, &compiledStatement, NULL);
    sqlite3_bind_int( compiledStatement, 1, self.RecordIDToEdit);

    The bind_int statement will replace occurrence 1 of the question mark symbol with the integer value self.RecordIDToEdit, but in an SQL-injection safe way.

    There are several sqlite3_bind_* calls, depending on the type of data you’re working with:
    https://www.sqlite.org/c3ref/bind_blob.html


    • nikolay_d

      nikolay_dnikolay_d

      Author Reply

      You are generally right, but in this case that %d specifies just an int, so no SQL injection can possibly occur. recordIDToEdit is an int…


  • Tim

    TimTim

    Author Reply

    Really great tutorial. Thanks so much.

    I am having a problem where *paths is returning a directory that is, in fact, NOT where the sampledb.sql is copied to when it is imported.

    The path that is being set is one which involves two separate guids. Any thoughts?


    • Tim

      TimTim

      Author Reply

      Ha! Nevermind. My fault. Turns out my problem was the first time I ran the program, sampledb.sql was not properly imported, so sqlite3_open created the file (empty) in the proper location.

      Once I properly imported the sampledb.sql file into the project, it didn’t matter because copyDatabaseIntoDocumentsDirectory jumped out as soon as it saw the empty file of the same name sitting in the directory. So the right file never got copied in and I was trying to execute the insert against an empty file.


  • newbee-feng

    if there are no content under a column,it maybe cause problem.

    because the column is 4, but the length of arrDataRow is less than 4. out of index.

    if (dbDataAsChars != NULL) {

    // Convert the characters to string.

    [arrDataRow addObject:[NSString stringWithUTF8String:dbDataAsChars]];

    }

    // Keep the current column name.

    if (self.arrColumnNames.count != totalColumns) {

    dbDataAsChars = (char *)sqlite3_column_name(compiledStatement, i);

    [self.arrColumnNames addObject:[NSString stringWithUTF8String:dbDataAsChars]];

    }


    • Zubbs

      ZubbsZubbs

      Author Reply

      I added an else clause to deal with this:

      if (dbDataAsChars != NULL) {
      // Convert the characters to string.
      [arrDataRow addObject:[NSString stringWithUTF8String:dbDataAsChars]];
      }
      else {
      [arrDataRow addObject:[NSNull null]];
      }

      And then later null elements in the results array can be checked for by:

      if ([thisElement isEqual:[NSNull null]]) …

      (Btw adding these null elements is useful for accessing results by fixed column index as well, if some may be null.)

      Hope this helps someone. Thanks to the author for a great tutorial overall.


  • Jimmy

    JimmyJimmy

    Author Reply

    How would you check for multiple conditions in a query?


  • Velimir

    VelimirVelimir

    Author Reply

    I have just one question. Why should I call sqlite3_close after every query? Why can’t I just open a connection on main tread in ApplicationDidBecomeActive(for example), and close it on didEnterBackground(for example)?


  • jinyang li

    jinyang lijinyang li

    Author Reply

    I also found a problem… If any column’s value is NULL, array bounds occurring.

    if (dbDataAsChars != NULL) {
    [arrDataRow addObject:[NSString stringWithUTF8String:dbDataAsChars]];
    }

    if (self.arrColumnNames.count != totalColumns) {
    dbDataAsChars = (char *)sqlite3_column_name(compiledStatement, i);
    [self.arrColumnNames addObject:[NSString stringWithUTF8String:dbDataAsChars]];
    }

    SHOULD BE :

    if (dbDataAsChars != NULL) {

    [arrDataRow addObject:[NSString stringWithUTF8String:dbDataAsChars]];
    if (self.arrColumnNames.count != totalColumns) {
    dbDataAsChars = (char *)sqlite3_column_name(compiledStatement, i);
    [self.arrColumnNames addObject:[NSString stringWithUTF8String:dbDataAsChars]];
    }
    }


  • SashaDudash

    can smb tell how to search through database using UISearchBar using this code


  • Sunsun

    SunsunSunsun

    Author Reply

    Great tutorial!!! Thank you very much for your effort!!

    I’d tried to follow and doing my own app, but I have a dumb problem. I cannot make my “add button” appears in my navigation bar. I’m not sure, if I have to add a Navigation controller to each viewController I create on the storyboard, or just with the Navigation controller we created at the beginning it’s assumed that we can use other navigation bar in each view.
    Could anybody help me???
    Thanks in advance


  • charis mosquera

    Creative comments – For my two cents if others need a ID DoL Generic Employment Application , my friend saw a sample version here https://goo.gl/W9ikCZ


  • BacchusPlateau

    I am SO happy you wrote this…it really helped me out. Thanks so much.


  • Sree Hari

    Sree HariSree Hari

    Author Reply

    download file link is missing can any one plz update the download link file


Shares