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.

Post one : Create an Sqlite database for your project.

Post two : Copy Sqlite file in the document directory.

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], -1,  SQLITE_TRANSIENT);
            sqlite3_bind_text(sqlstatement, 2, [empdept UTF8String], -1,  SQLITE_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

            sqlite3_step(sqlstatement);

    

            // clearing the sql statement

            sqlite3_finalize(sqlstatement);

            //closing the database after the query execution

            sqlite3_close(databaseReference);

            

            return YES;
        }
        else
        {
            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

 sqlite3_close(databaseReference);

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.