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
            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.

14 comments:

  1. thnak you so much....could you please post how to draw polyline in iphone.

    ReplyDelete
  2. First time I am visiting this blog and reading your post. I must say you did a great job. Being in iPhone software development this post will be quite useful for me. Thanks for sharing...Keep on good work!

    ReplyDelete
  3. I stored images in server. Then i fetch the images and save to db1.sqlite file through image URL. Images are saved to db1.sqlite file like URL. How to display the images from saved URL. product_image having the imageURL path.

    sqlite table structure:

    CREATE TABLE "product" ("id" INTEGER PRIMARY KEY NOT NULL , "cat_id" INTEGER NOT NULL , "product_image" VARCHAR NOT NULL , "order_by" INTEGER NOT NULL )

    Insert code:

    const char *sqlInsert = [[NSString stringWithFormat:@"insert into product (id, cat_id,product_image,order_by) values ('%@','%@','%@','%@')", [tuser objectForKey:@"id"], [tuser objectForKey:@"cat_id"],[tuser objectForKey:@"product_image"],[tuser objectForKey:@"order_by"]] cStringUsingEncoding:NSUTF8StringEncoding];


    NSLog(@"product insert %s", sqlInsert);

    if(sqlite3_prepare_v2(database, sqlInsert, -1, &addStmt, NULL) != SQLITE_OK)
    NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));



    if(SQLITE_DONE != sqlite3_step(addStmt))
    NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));

    Image fetching:

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    //NSLog(@"docs dir is %@", documentsDirectory);

    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"db1.sqlite"];
    //NSLog(@"filepath %@",path);

    mArray = [[NSMutableArray alloc]init];
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {

    const char *sql = "SELECT id,cat_id,product_image FROM product order by order_by";

    NSLog(@"sql is %s",sql);

    sqlite3_stmt *statement;
    // int catID = 0;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
    // We "step" through the results - once for each row.
    while (sqlite3_step(statement) == SQLITE_ROW) {

    int length = sqlite3_column_bytes(statement, 2);
    NSData *imageData = [NSData dataWithBytes:sqlite3_column_blob(statement, 2) length:length];

    UIImage *image = [UIImage imageWithData:imageData];

    [mArray addObject:image];

    [image release];

    }
    }
    sqlite3_finalize(statement);
    }

    else {
    sqlite3_close(database);
    NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    // Additional error handling, as appropriate...
    }

    ReplyDelete
  4. @Karthees Waran : To display the images from the server you should not store the image link in the database, you should use cache technique and display them, by using this the images are downloaded but not stored in the ios device, i mostly use SDwebImage which is a pretty good tool for doing this kind of stuff. If you store the link then too you have to give calls to the link to display the images which will take time.

    Check out SDwebImage at git https://github.com/rs/SDWebImage

    ReplyDelete
  5. thank u sir. its very benefitted to me

    ReplyDelete
  6. i would like to know how to add a text field data in google drive spreadsheet plz help me out

    ReplyDelete
  7. you did a Great job

    ReplyDelete
  8. Can You Post Delete and Update ,Retrieve code......Pls

    ReplyDelete
  9. if (backUPDone)
    {
    // ALTER TABLE original RENAME TO tmp

    string insertIntoQuery = @"INSERT INTO Sound
    (
    id, path, filename, description, start, length, hide_me , bitdepth , channels ,
    date_added, sample_rate , type , cd_title , artist , coding_history , original_date ,
    originator_ref , originator , timecode , subcategory , category , [index], comments , episode , frame_rate ,
    [group] , library , location , project_name , rating, tape , scene , sync_point, take, track_title, composer, designer, publisher, folder, genre
    )

    "How to write this code in objective-c"

    ReplyDelete
  10. How to write these sqlite query for ios objective code

    ReplyDelete
  11. What is the error that you get while you use your query?

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete