Wednesday, March 20, 2013

Sqlite Database part 4 : The Final Code

Before starting with this post i request you to read previous post related to sqlite

How to create Sqlite Database.

How to copy database from bundle to document directory

How to insert record in sqlite database

If you have already done that then here's the code for the database demo that we have learned and i hope this helps you out in understanding the sqlite database.

Until then happy iCoding and have a great day.

Join Us on Facebook

Friday, March 1, 2013

Sqlite Database part 3 : Inserting Records in Database

Before beginning this post I hope that you have read my earlier two posts on sqlite, if not then please have a look at them first and then you may come here and if you have already done that then lets begin.

In this post we will learn on how to insert data into sqlite database, assuming that you have read my earlier posts I shall begin with the insertion part.

Step 1: We have created a function named insertEmployeeRecord in the DatabaseHelper.h file and now its time to give body to that function. Give below is the code for the same, the function returns Boolean value indicating whether the data is successfully inserted in the database or not.

// inserts the new employee record
- (BOOL)insertEmployeeRecordWithEmployeeName:(NSString*)empname andEmployeeDepartment:(NSString*)empdept andEmployeeImage:(UIImage*)empImage
    if (empname.length!=0 && empdept.length!=0)
        // checking for any previously open connection which was not closed
        [self closeanyOpenConnection];
        // preparing my sqlite query
       const char *sqliteQuery = "insert into EmployeeRecord(EmpName,EmpDept,EmpImage) values(?,?,?)";
        sqlite3_stmt *sqlstatement = nil;
        if (sqlite3_prepare_v2(databaseReference, sqliteQuery, -1, &sqlstatement, NULL)==SQLITE_OK )
            sqlite3_bind_text(sqlstatement, 1, [empname UTF8String], -1SQLITE_TRANSIENT);
            sqlite3_bind_text(sqlstatement, 2, [empdept UTF8String], -1SQLITE_TRANSIENT);
            // You need to convert the image data into NSData first
            NSData *imgData = [NSData dataWithData:UIImagePNGRepresentation(empImage)];
            // insert the binary representation of the byte data into the database
            sqlite3_bind_blob(sqlstatement, 3, [imgData bytes], [imgData length], SQLITE_TRANSIENT);
            // executes the sql statement with the data you need to insert in the db
            // clearing the sql statement
            //closing the database after the query execution
            return YES;
            return NO;
    return NO;

Code Explanation: Before beginning with any DB operation I always make sure that all my previous db connections are closed which is done with the help of closeanyOpenConnection function.

Next I prepare my sqlite insert query with the help of a const character pointer, the main function for insertion or any particular operation is the sqlite api named sqlite3_prepare_v2 this function returns an integer value  which indicates whether your sql operation will be carried forward or whether it will be stopped, 

You sql operation can stop due to reasons like if you have written some wrong sql query or your database might not have been copied in the document directory, this function returns zero for successful operation i.e. SQLITE_OK and it returns 1 for failure which  is indicated by SQLITE_ERROR

The function sqlite3_prepare_v2 takes few parameters that are given as below

Now its time to insert your data based upon the data type of your columns, my first two columns are of text type so I will be using sqlite3_bind_text function, which takes five parameters:

sqlite3_bind_text(sqlstatement, 1, [empname UTF8String], -1,  SQLITE_TRANSIENT);

1.sqlstatement : This is the pointer of sqlite3_stmt which will carry the statement which we want to execute.

2.Column Number: This is the column number into which you want to insert the data and is the second parameter

3.Actual Value in UTF8 or C style: This is the actual value that you want to insert in the database, if its NSString then you have to convert it into a UTF8 string.

4.Negative Integer one: This value indicates the number of bytes in the value and if its negative then the length of the string is the number of bytes upto the first zero terminator.

5.SQLITE_TRANSIENT: Means that this content will change in the coming future and SQlite should make its own private copy of the content,moreover it’s like a destructor which would delete the value once the insertion process has been done (this is what I understood till now)

If there is integer data, which you want to insert, then you may use the method sqlite3_bind_int

Adding binary data to the column is a piece of cake sqlite has an inbilt method for doing this and its called as the sqlite3_bind_blob, this statement is similar to your sqlite3_bind_text expect that it takes the byte representation of your image with the image length.

// You need to convert the image data into NSData first
            NSData *imgData = [NSData dataWithData:UIImagePNGRepresentation(empImage)];

// insert the binary representation of the byte data into the database
            sqlite3_bind_blob(sqlstatement, 3, [imgData bytes], [imgData length], SQLITE_TRANSIENT);

Once you have set all the values for your database columns then its time to execute your sqlite statement and this is done with the help of a method named sqlite3_step which accepts the pointer of sqlite3_stmt. 

Once your sql statement is executed then you need to finalize it with the help of sqlite3_finalize this step is important because it is used to clear the sqlstatement which you have just created to insert record in the database also the finalize statement is important in case where you want to insert multiple records in a table

After that close the database connection with the help of sqlite3_close

//closing the database after the query execution

With this function you can now insert data into the sqlite database without breaking a sweat, you may modify the function to do stuff as per your business logic and if you are facing any issues then feel free to contact me I will help you out.

I hope that i have cleared points on how to insert data in the sqlite database and if you are having any sort of queries then feel free to ask them via comments or emails

I will attach the copy of the code in the last tutorial of sqlite where you may download the copy and check the app for yourself.

Until then Happy iCoding and have a great day.

Sqlite Database part 2 : Copying the database file from project bundle to document directory

Before reading this part kindly have a look at my first post for sqlite here.

To handle the database there is an in-build library that comes with xcode which is called as libsqlite3.0dylib and if you are working with any database oriented project then in that case you need to first add this library into your project. 

To use this library you have to use #import<sqlite3.h> in your header file. 

Steps to add this library is similar to the way you add a framework into your project.

In this post we will first create a class which will help us in performing the database operations. I will be adding an employee name and his corresponding department with his image, user may select the image from the iOS gallery by tapping on the image. 

The main focus of this post will be database oriented only hence i wont be showing you the UI part which you may see by downloading the source code and in case if you are facing any issues with the UI part feel free to ask me via mails or via comments.

I have created an NSObject subclass named DatabaseHelper where i have wrote all the code for handling the database related operations, given below is an image displaying the list of functions in the DatabaseHelper.h file

So as you may see from the above image the functions that i have declared that will assist me with data insertion with sqlite, the description about the function is mentioned as comments so that you can have a proper understanding regarding its role in the play.

Note: The database file present in your project bundle is just a copy from which you may read the data but not write new data into it, since its read only and hence inorder to add data in our sqlite file we need to copy this file (sqlite file) in the applications document directory from where we can not only read the data but as well write new data into it. 

Hence before working with sqlite database you have to copy this file in the document directory of your application and then begin with reading or adding of data in the database.

Document Directory : This is a folder which is present in every iOS app, this folder contains some files related to your project which only your app can access.

Let's begin with the development process and see how to manage data with sqlite.

Step 1 : The first step is to copy the database file from your bundle or groups and files to the document directory and for that there are three functions that i have wrote which is given below

Function 1 : This function wil help us in copying the database file from the bundle in the doc directory

Code Explanation : First i am checking if my sqlite file is present at document directory and then with the help of NSFileManager i am copying the sqlite file from my project bundle to it's new location i.e. document directory. The function returns boolean indicating whether the database file is copied in the doc directory or has failed in the process.

Function 2 : This function will get the location of the database file from the document directory

Code Explanation : The name of the database that i am using is called as "AdventureWorks.sqlite", documentDirectoryFolderLocation array contains the  path of document directory for your application, and the retuned location from this function will be the new location for our database.

Function 3 : Will check if the db has been copied or if the operation has failed and will print the appropriate message in the Log.

Code Explanation : This function will make a call to the copyDatabaseFromBundletoDocumentDirectory and will return if the copying of the database operation has been success or has failed.

Step 2: After copying the database into the document directory i create a function which will close any open connection to the sqlite database before the execution of any new operation.

Code Explanation : The above code checks if any db connection is open with the help of sqlite3_open function and if a connection is open then it closes the connection with the help of sqlite3_close function which accepts the parameter of databaseReference which is the object of sqlite3.

I hope that you have understood the above steps we will have a look at rest of the steps in our next post where we will start adding and fetching records from the database.

If you have any questions or queries regarding this post then feel free to ask me via comments or emails.

Happy iCoding and have a great day.

Move to the third part of tutorial on sqlite here.