SQLite Database Operations In iPhone

            7.1 Introduction

            To store small amount of data, array can be used. But in the case of large amount of data, we have to switch to some other option. Xcode allow the user to use sqlite3 data base for storage purpose. Sqlite allow the developer to create, store, delete, update a database table and provide a large amount of storage to store different types of data.

         In this session, we will see create a data base, create table, insert data in the table, delete data and finally update data in the table.

         7.2 Create a database and table in iPhone

         Now, we shall see how to create a database and table in iPhone development. First you have to create a Xcode project namely SqlDB ( you can give your own name), and in the framework section on the right pane, right click and add new frame work for sqlite called libsqlite3.0.dylib.

In the SqlDBViewController.h file, import the sqlite3 database header file.

          

         ViewUser.h is another one view controller file which is used to display the detail data for particular user. Table view is used to display the stored data from data base to iPhone. TextFields are used to get the data to store. We can directly get and set data from data base to table, for that we have to use set of arrays, so declare 3 array objects in the header section.

         Functions which are declare in the header section is used to create, open and store data in the database table.

         Now, we shall see the implementation coding for create a database and table in iPhone.

         By using the method  (NSString*)filePath, we will create one database.sql file in application's local folder. OpenDB is the function which is used to open a database. Create table function is used to create a table in database. We can define the create table query as per the requirement. Here, the table is created with three fields, namely UserId, User_Name and Password. User_Id is set as auto increment and we will get user name and password  as input.

         In create table function, first we have to define the create table query and save that in a string.

sqlite_exec query is used to execute the database queries. We have to pass   parameters in sqlite_exec such as database object, sql query and a character which indicate the error . If the execution process is succeeded, the table was created successfully otherwise close the database and show the error message.

7.3  Insert data in the table

         After creating a database table, we have to insert data in to data base table. For that purpose,

we have to write code as follows in the implementation section,

        

        

         Above method will insert the data in to the database. Here, we are inserting two fields namely user_name and password. So that the insert data function is defined using two fields .Here, first we have to define a query to insert the data in the table and pass that query in the function called sqlite3_prepare_v2. This will check whether the appropriate data are passed as input parameters , if the query was successful one, then bind the text in the database table by using the function sqlite3_bind_text. If   sqlite3_prepare_v2 function fails, throw an error message and close the database.

         To call insert data method, we have to define another one IBAction as follows,

         In the above function, first step is to open the database by calling openDB function using [self openDB]  and also call createTable function to create a table. Finally call insertRecord  function and close the database.

7.4 Select records from database table

         After insert the data in table, next step is to get and display the data in a table. Here, we have to write codes to get data from database as well as to store that in  arrays. Similar to previous process, first we have to write query to get the set of rows from table and prepare the query for validation. Finally, if the process was succeeded, then append each data from the table to array.

         Here, we are going to get three fields namely user_Id, user_name, password in three separate arrays. By using the name array we are reloading the table data.

         The implementation section coding for select records from the database table is as follows,

        

         Table view datasource coding will be as follow,

         7.5 Update and delete record from table

        

         Now we have to display each user data in detail manner. For that purpose, we have to use our second view controller file namely  “ViewUser”.  In the header section, import and declare object for sqlite3 database. Declare one method to get the name , password and user id from previous view.

The header section coding will be like as follows,

         update and delete user  actions are used to update table and delete table. Following codings are used to update a record from database table,

         updating a database is similar to insert record in the data base, only difference is that the query for both are differed.

         To delete particular record from a table, we have to use user_id for that purpose and the coding will be like as follows,          

        

        

         Connect the actions and outlets in both xib window and build and run the application. Output will be as follows,