WHAT YOU WILL LEARN IN THIS CHAPTER
In the previous chapter, you learned about data persistence using files. For simple applications, you can write the data you want to persist to a simple text file. For more structured data, you can use a property list. For large and complex data, it is more efficient to store them using a database. The iPhone comes with the SQLite3 database library, which you can use to store your data. With your data stored in a database, your application can populate a Table view or store a large amount of data in a structured manner.
This chapter shows you how to use the embedded SQLite3 database in your applications.
To use a SQLite3 database in your application, you first need to add the libsqlite3.dylib
library to your Xcode project. Use the following Try It Out to find out how. You will need to download the code files indicated for this and the rest of the Try It Out features in this chapter.
Chapter 13 discusses the various folders that you can access within your application's sandbox.
After the necessary library is added to the project, you can open a database for usage. You will use the various C functions included with SQLite3 to create or open a database, as demonstrated in the following Try It Out.
If the database is created successfully, it can be found in the Documents
folder of your application's sandbox. As discussed in Chapter 13, you can locate the Documents
folder of your application on the iPhone Simulator in the ~/Library/Application Support/iPhone Simulator/User/Applications/<App_ID>/Documents/
folder. Figure 12-2 shows the database.sql
file.
After the database is created, you can create a table to store some data. In the following Try It Out, you learn how to create a table with two text fields. For illustration purposes, create a table named Contacts
, with two fields called email
and name
.
For a jumpstart in the SQL language, check out the SQL tutorial at: http://w3schools.com/sql/default.asp
.
After the table is created, you can insert some records into it. The following Try It Out shows you how to write two rows of records into the table created in the previous section.
One of the common tasks involved in formulating SQL strings is the need to insert values into the query string and making sure that the string is well formulated and that it does not contain invalid characters. Earlier in the Inserting Records section, you saw that to insert a row into the database, you had to formulate your SQL statement like this:
NSString *sql = [NSString stringWithFormat: @"INSERT OR REPLACE INTO '%@' ('%@', '%@') VALUES ('%@','%@')", tableName, field1, field2, field1Value, field2Value]; char *err; if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) { sqlite3_close(db); NSAssert(0, @"Error updating table."); }
SQLite supports a feature known as bind variables to help you formulate your SQL string. For example, the preceding SQL string can be formulated as follows using bind variables:
NSString *sqlStr = [NSString stringWithFormat: @"INSERT OR REPLACE INTO '%@' ('%@', '%@') VALUES (?,?)", tableName, field1, field2]; const char *sql = [sqlStr UTF8String];
Here, the ?
is a placeholder for you to replace with the actual value of the query. In the preceding statement, assuming that tableName
is Contacts, field1
is email
, and field2
is name
, the sql
is now:
INSERT OR REPLACE INTO Contacts ('email', 'name') VALUES (?,?)
Note that the ?
can be inserted only into the VALUES
and WHERE
section of the SQL statement; you cannot insert it into a table name, for example. The following statement would be invalid: INSERT OR REPLACE INTO ? ("email", "name") VALUES (?,?)
.
To substitute the values for the ?
, you need to create a sqlite3_stmt
object and use the To substitute the values for the sqlite3_prepare_v2()
function to compile the SQL string into a binary form and then insert the placeholder values using the sqlite3_bind_text()
function, like this:
sqlite3_stmt *statement; if (sqlite3_prepare_v2(db, sql, −1, &statement, nil) == SQLITE_OK) { sqlite3_bind_text(statement, 1, [field1Value UTF8String], −1, NULL); sqlite3_bind_text(statement, 2, [field2Value UTF8String], −1, NULL); }
After the preceding call, the SQL string looks like this:
INSERT OR REPLACE INTO Contacts ('email', 'name') VALUES ('[email protected]', 'user0')
To execute the SQL statement, you use the sqlite3_step()
function, followed by the sqlite3_finalize()
function to delete the prepared SQL statement:
if (sqlite3_step(statement) != SQLITE_DONE) NSAssert(0, @"Error updating table."); sqlite3_finalize(statement);
Note that in the previous section, you used the sqlite3_exec()
function to execute SQL statements. In this example, you actually use a combination of sqlite3_prepare(), sqlite3_step()
, and sqlite3_finalize()
functions to do the same thing. In fact, the sqlite3_exec()
function is actually a wrapper for these three functions. For nonquery SQL statements (such as for creating tables, inserting rows, and so on), it is always better to use the sqlite3_exec()
function.
This chapter provides a whirlwind introduction to the SQLite3 database used in the iPhone. With SQLite3, you can now store all your structured data in an efficient manner and perform complex aggregations on your data.
EXERCISES
Explain the difference between the sqlite3_exec()
function and the other three functions: sqlite3_prepare(), sqlite3_step()
, and sqlite3_finalize()
.
How do you obtain a C-style string from an NSString
object?
Write the code segment to retrieve a set of rows from a table.
WHAT YOU HAVE LEARNED IN THIS CHAPTER
TOPIC | KEY CONCEPTS |
---|---|
Use a SQLite3 database in your application | Need to add a reference to the |
Obtain a C string from a | Use the |
Create and open a SQLite3 database | Use the |
Execute a SQL query | Use the |
Close a database connection | Use the |
Use bind variables | Create a Use the Use the Use the Use the |
Retrieve records | Use the |
Retrieve columns from a row | Use the |