Sunday, May 5, 2013

SQLite Tutorial Part 1


Credits:

Most of the codes are from Techotopia and I refer this tutorial from their SQLite tutorial 
For more information and detailed discussion, refer to their site. :D


This is what we will be trying to achieve in this first Tutorial. (Yes, I'm gonna have a Part 2 of SQLite Tutorial)


Prerequisite:

1. SQLite or MySQL
2. Creating Tabbed Views
3. Adding UITableView

1. Create a Single View Application Template.
2. Add SQLite dynamic library in Build Phases under "Link Binary With Libraries."








3. Add a New File, subclass of UITableViewController. In my case, I named it "ListViewController." We will use this class to show all the added entries from our database in to this table.


4. Add a UITabBarController variable in our AppDelegate.
@property (strongnonatomic) UITabBarController *tabController; //Interface Section
@synthesize tabController; //Implementation Section


5. Edit AppDelegate's ApplicationDidFinishLaunchingWithOptions method:

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
    self.window = [[UIWindow allocinitWithFrame:[[UIScreen mainScreenbounds]];
    // Override point for customization after application launch.
    
    tabController = [[UITabBarController alloc] init];
    self.viewController = [[ViewController  alloc] initWithNibName:@"ViewController" bundle:nil];
    ListViewController *listVC = [[ListViewController alloc] initWithStyle:UITableViewStylePlain];
    tabController. viewController = [[NSArray  alloc] initWithObjects:self.viewController, listVC, nil];
     self.window.rootViewController = self.tabController;
    [self.window makeKeyAndVisible];
    return YES;
}

6. #import <sqlite3.h> to ViewController and ListViewController classes.

Try to Build and Run your program to make sure that nothing's wrong when you imported sqlite3.

7. Let's create variables for our databasePath (NSString *) and our database (sqlite3 *) to ViewController and ListViewController classes.
@property (strongnonatomic)  NSString *databasePath;
@property (nonatomic)  sqlite3 *myDatabase;

8. Add a textfield and a button to our ViewController.xib file. The user enters any message inside our textfield and when the user taps the button, it will be added to our database.

Create an IBOutlet property for our textfield and connect it to our object in our xib file. Remember also to synthesize the UITextField property.
@property (strongnonatomic)  IBOutlet UITextField *textField; //Interface section
@synthesize textField; //Implementation section

9. Prepare an IBAction method for our button. 
- (IBAction) addTextToDatabase:(id) sender {
     //We will add the codes later. 
}
Connect this IBAction to our button object in our xib file.

10. Prepare and create our database if it has not existed yet. Call this method inside our viewDidLoad in ViewController.m (You may disregard the UIAlertView codes.)

- (void)prepareDatabase {
    // Get the documents directory
    NSArray *dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsDir = dirPaths[0];
    
    // Build the path to the database file. We declared databasePath on Step 7
   databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent:@"sampleDatabase.db"]];
    //NSLog(@"DB Path: %@", databasePath);
    
    NSFileManager *filemgr = [NSFileManager defaultManager];
    
    if ([filemgr fileExistsAtPathdatabasePath ] == NO) {
        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK) {  //We declared myDatabase on Step 7
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS SAMPLETABLE (ID INTEGER PRIMARY KEY AUTOINCREMENT, MESSAGE TEXT)";

/*
Name of Our Database: sampleDatabase.db
Name of Our Table: SAMPLETABLE
Columns in SAMPLTABLE: ID & Message
Datatype of Message: Text
*/
            
            if (sqlite3_exec(myDatabasesql_stmt, NULLNULL, &errMsg) != SQLITE_OK) {
                statusOfAddingToDB = @"Failed to create table"; //iVariable statusOfAddingToDB (NSString *) 
            } else {
                statusOfAddingToDB = @"Success in creating table";
            }


            UIAlertView *alert = [[UIAlertView allocinitWithTitle:@"DB Status" message: statusOfAddingToDB delegate:nil cancelButtonTitle:@"OK" otherButtonTitles: nil];
            [alert show];


            sqlite3_close(myDatabase);
        } else {
            statusOfAddingToDB = @"Failed to open/create database";
            UIAlertView *alert = [[UIAlertView allocinitWithTitle:@"DB Status" message: statusOfAddingToDB delegate:nil cancelButtonTitle:@"OK" otherButtonTitles: nil];
            [alert show];
        }
    }
}

Our viewDidLoad in ViewController.m must now look like this:


- (void)viewDidLoad {
    [super viewDidLoad];
// Do any additional setup after loading the view, typically from a nib.
    self.title = @"Add To DB"; //For the tab title of our TabController
    [self prepareDatabase];
}


11. Add Entry to Database

- (IBAction)addTextToDatabase:(id)sender {
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK) {
        NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO SAMPLETABLE (MESSAGE) VALUES (\"%@\")",
                               self.textField.text];
        
        const char*insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(myDatabaseinsert_stmt,  -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE) {
            statusOfAddingToDB = [NSString stringWithFormat:@"Text added -- %@"textField.text];
        } else {
            statusOfAddingToDB = @"Failed to add contact";
        }
        
        UIAlertView *alert = [[UIAlertView allocinitWithTitle:@"DB Status" message: statusOfAddingToDB delegate:nil cancelButtonTitle:@"OK" otherButtonTitles: nil];
            [alert show];
        
        sqlite3_finalize(statement);
        sqlite3_close(myDatabase);
    }
}

12. I also added a UITextFieldDelegate to dismiss our keyboard after the user types in our textfield.

- (BOOL)textFieldShouldReturn:(UITextField *)tf {
    if (tf == textField) {
        [tf resignFirstResponder];
    }
    return NO;
}

ListViewController Class (UITableView)

I assume you did Steps 6 and 7 to ListViewController Class.

13. Add an NSMutableArray property to this class. Synthesize this property also. 

@property (strongnonatomic) NSMutableArray *list//Interface Section
@synthesize list//Implementation Section

14. Add a tab title for our TabController in initWithStyle method of ListViewController.m.
- (id)initWithStyle:(UITableViewStyle)style {
    self = [super initWithStyle:style];
    if (self) {
        self.title = @"List"; //tab title
    }
    return self;
}

15. Let's get the data we want from our database.

- (void)getTextFomDB {
    NSString *docsDir;
    NSArray *dirPaths;
    
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = dirPaths[0];
    
    // Build the path to the database file
    databasePath = [[NSString alloc]
                    initWithString: [docsDir stringByAppendingPathComponent:
                                     @"sampleDatabase.db"]];
    
    const char *dbpath = [databasePath UTF8String];
    sqlite3_stmt    *statement;
    
    if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK)
    {
        //Select all from SAMPLETABLE. This includes the 'id' column and 'message' column.
        NSString *querySQL = @"SELECT * FROM SAMPLETABLE"
        const char *query_stmt = [querySQL UTF8String];
        
        if (sqlite3_prepare_v2(myDatabasequery_stmt, -1, &statement, NULL) == SQLITE_OK{
            [list removeAllObjects];
            while (sqlite3_step(statement) == SQLITE_ROW) {
                NSString *text = [[NSString alloc]
                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)]; //Num 1 means on what column. Column 0 = 'id' column while Column 1 = 'message' column in our query result.

                [list addObject:text];
                //NSLog(@"count: %d", [list count]);
            }
            sqlite3_finalize(statement);
        }
        sqlite3_close(myDatabase);
    }
}

16. We call this method getTextFromDB in our viewWillAppear method. So that every time this view will be shown, it will get the data again from our database. Then reloadData to our tableview.
- (void)viewWillAppear:(BOOL)animated {
    [self getTextFomDB];
    [self.tableView reloadData];
}

17. Hope you are already familiar with UITableview's delegate and datasource methods. Edit the following methods such that:
  • numberOfSectionsInTableView == 1
  • numberOfRowsInSection == total number of elements in our NSMutableArray
  • cellForRowAtIndexPath --> set cell's textLabel = text in our NSMutableArray

SQLite Tutorial Part 2 will cover the following:

  1. Having multiple columns in one (DB) table.
  2. Getting all data in our (DB) table (with multiple columns)
  3. Search and return data from our (DB) table.

Download Sample Project here.




76 comments:

  1. Thanks a lot Jenn.......but i have an issue that in view controller when i check with creakpoint "Statement" is geeting null value and tried so many time but it is not working.i have also check dbpath but still not working :(

    ReplyDelete
  2. failed to add contect please help.........

    ReplyDelete
  3. Wow , That was excellent !!!! Very easy to understand . Thanks a lot :)

    ReplyDelete
  4. Hi, you have a very nice lecture. So thank you. I have a problem as follows;
    I have created the id number in the database using SQLite, that row of data corresponding to the label you want to print. Somehow I could not. Thanks for your help.
    E.g.

    ID (int) | TEXT (text)
    1 | iOS
    2 | Objective-C
    3 | xcode

    When I was number 1 Select the label you want to write iOS. Thank you ...

    ReplyDelete
  5. very excellent,easy to understand .Thanks a lot

    ReplyDelete
  6. Hi this is java i am having 3 years of experience as a java developer and i am certified. i have knowledge on OOPS concepts in java but dont know indepth. After learning hadoop will be enough to get a good career in IT with good package? and i crossed hadoop training in chennai website where someone please help me to identity the syllabus covers everything or not??

    thanks, vignesh

    ReplyDelete
  7. Hi, this is Ganesh I am having 3 years of experience as a Dot Net developer and I am certified. I have Knowledge on OOPS Concepts in .NET indepth. After learning Salesforce will be enough to get a good career in IT with good Package? and i crossed Salesforce Training in Chennai website where someone please help me to identify the syllabus covers everything or not??

    Thanks,
    Ganesh

    ReplyDelete

  8. Hi this is abinaya i am having 3 years of experience as a java developer and i am certified. i have knowledge on OOPS concepts in java but dont know indepth. After learning oracle will be enough to get a good career in IT with good package? and i crossed Oracle Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

    Thanks, abinaya

    ReplyDelete


  9. Hi this is dwarakesh i am having 3 years of experience as a php developer and i am certified. i have knowledge on OOPS concepts in php but dont know
    indepth. After learning java will be enough to get a good career in IT with good package? and i crossed
    java training in chennai website where someone please help me to identity the syllabus covers everything or not??

    thanks, dwarakesh

    ReplyDelete

  10. Hi this is dwarakesh i am having 3 years of experience as a php developer and i am certified. i have knowledge on OOPS concepts in php but dont know
    indepth. After learning java will be enough to get a good career in IT with good package? and i crossed
    java training in Chennai website where someone please help me to identity the syllabus covers everything or not??

    thanks, dwarakesh

    ReplyDelete
  11. Hi this is Rajesh i am having 3 years of experience as a php developer and i am certified. i have

    knowledge on OOPS concepts in php but dont know indepth. After learning Dot Net will be

    enough to get a good career in IT with good package? and i crossed dot net training in

    chennai
    website where someone please help me to identity the syllabus covers everything or

    not??

    thanks,
    Rajesh

    ReplyDelete
  12. Hi this is Rajesh i am having 3 years of experience as a php developer and i am certified. i have

    knowledge on OOPS concepts in php but dont know indepth. After learning Dot Net will be

    enough to get a good career in IT with good package? and i crossed dot net training in chennai website where

    someone please help me to identity the syllabus covers everything or not??

    thanks,
    Rajesh

    ReplyDelete
  13. Your blog is really awesome. Thanks for sharing this blog. If anyone want to get PHP Course in Chennai, please visit Fita academy located at Chennai, Velachery.

    ReplyDelete

  14. Nice blog, here I had an opportunity to learn something new in my interested domain. I have an expectation about your future post so please keep updates.

    Fita Chennai Reviews, Hadoop training in chennai

    ReplyDelete
  15. The steps you have explained about IOS was crystal clear thank you so much for posting...
    PHP Training in Chennai

    ReplyDelete
  16. Thanks for your informative post!!! After completing my graduation, i am confused whether to choose web design as my career. Your article helped me to make a right choice. PHP Training in Chennai | Best PHP training in Chennai | PHP Training Institute in Chennai

    ReplyDelete

  17. Thanks for sharing this valuable post to my knowledge great pleasure to be here SAS has great scope in IT industry. It’s an application suite that can change, manage & retrieve data from the variety of origin & perform statistical analytic on it, if anyone looking for best sas training in Chennai get into FITA…
    sas training institutes in Chennai|sas training center in Chennai

    ReplyDelete
  18. This is extremely helpful info!! Very good work. Everything is very interesting to learn and easy to understood. Thank you for giving information. Android training in chennai | Android training chennai |Android course in chennai

    ReplyDelete
  19. Brilliant article. The information I have been searching precisely. It helped me a lot, thanks. Keep coming with more such informative article. Would love to follow them.
    Shashaa
    PHP Training in Chennai | HTML5 Training in Chennai | Android Training in Chennai

    ReplyDelete
  20. Brilliant article. Found it helpful. We at fita, provide Java Training in Chennai, along with placement assurance. Reach us if you are interested in Java Training in Chennai. Thank you, Java Training in Chennai

    ReplyDelete
  21. Wow, brilliant article that I was searching for. Helps me a lot, using it in my work. Thanks a ton. Keep writing, would love to follow your posts.
    Rithika
    Dot Net Training in Chennai

    ReplyDelete
  22. Thank you for the informative post. It was thoroughly helpful to me. Keep posting more such articles and enlighten us.
    Shashaa
    Software testing training in Chennai | Software testing training in Chennai | Software testing training in Chennai

    ReplyDelete
  23. There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Hadoop Training in Chennai). By the way you are running a great blog. Thanks for sharing this. FITA chennai reviews

    ReplyDelete
  24. Very informative post. If interested, one can take up AngularJS training in Chennai and stay up to date in technology.

    ReplyDelete
  25. Hi, I wish to be a regular contributor of your blog. I have

    read your blog. Your information is really useful for us.we

    are providing seo

    training in vijayawada

    ReplyDelete
  26. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly,but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    QTP Training in Chennai

    ReplyDelete
  27. Informatica Training in chennai
    Thanks for sharing such a great information..Its really nice and informative..

    ReplyDelete
  28. Wow, brilliant article that I was searching for. Helps me a lot in taking class for my students, so using it in my work. Thanks a ton. Keep writing, would love to follow your posts.
    Shashaa
    Dot Net training in Chennai

    ReplyDelete
  29. Hello, thank you for the useful post on Selenium training in Chennai. I share your blog with my students as a part of my Selenium testing training in Chennai. Keep writing more such posts that can be used for Selenium training Chennai, would love to follow.

    ReplyDelete
  30. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
    Regards,
    Angular institutes in Chennai|Angular training in chennai|Angularjs training chennai

    ReplyDelete
  31. I am very impressed with the article I have just read,so nice.......
    Dot Net Training in Chennai

    ReplyDelete
  32. Upgrading ourselves to the upcoming technology is the best way to survive in this modern and fast paced technology world. Reading contents like this will create a positive impact within me. Thanks for writing such a valuable content. Keep up this work.

    JAVA Training in Chennai | JAVA Training | Best JAVA Training in Chennai | JAVA Course in Chennai

    ReplyDelete
  33. Hello,
    I really enjoyed while reading your article, the information you have mentioned in this post was damn good. Keep sharing your blog with updated and useful information.
    Regards,
    Python Training in Chennai|python training chennai|Python Course in Chennai

    ReplyDelete
  34. In recent days Angular plays vital role to loading your site content in a fastest way, so it’s a required skill for everyone, thanks for sharing this useful information to our vision keep blogging.
    Regards,
    Angularjs training in chennai|Angularjs training center in Chennai|cognos Training in Chennai

    ReplyDelete
  35. Internship & Recruitment Program for MCA students
    Webtrackker also provide the 6 Month/ weeks industrial training / Internship & Recruitment Program for MCA students in Java, dot net, Web designing, web developments, Angular.js, Node.js, Hybrid apps, computer networking, Plc Scada, Auto cad, All modules in ERP sap, sap mm, sap fico. Php, Oracle Dba, networking etc for MCA, BCA, B.Tech Students.
    Webtrackker Technologies
    B-47, Sector- 64
    Noida- 201301
    Phone: 0120-4330760, 8802820025
    Email: Info@Webtrackker.Com
    Web: www.webtrackker.com

    ReplyDelete
  36. Really interesting content which is unique which provided me the required information.
    Dot Net Training in Chennai | .Net training in Chennai | FITA Training | FITA Velachery .

    ReplyDelete
  37. Pretty much information you have shared with me, thanks for updating your blog
    Regards,

    SAS Training in Chennai|Python Training in Chennai|Fita Chennai reviews

    ReplyDelete

  38. Web designing Training Institute in noida - with 100% placement support - web trackker is the is best training institute for web designing, web development in delhi. In you are looking web designing Training in noida, web designing Training Institute in Noida, web designing training in delhi, web design training institute in Ghaziabad, web designing training institute, web designing training center in noida, web designing course contents, web designing industrial training institute in delhi, web designing training coaching institute, best training institute for web designing training, top ten training institute, web designing training courses and content then Webtrackker is the best option for you.

    ReplyDelete
  39. Linux Training Institute in Noida
    Best Linux & UNIX Training Institute In Noida, Delhi- Web Trackker Is The Best Linux & Unix Training Institute In Noida, Top Linux & Unix Coaching In Noida Sector 63, 53, 18, 15, 16, 2, 64 Providing The Live Project Based Linux & Unix Industrial Training.

    ReplyDelete
  40. SAS Training Institute in noida
    Best SAS training in Noida- with 100% placement support - Fee Is 15000 Rs - web trackker is the best institute for industrial training institute for SAS in Delhi, Ghaziabad, if you are interested in SAS industrial training then join our specialized training programs now. SAS Training In Noida, SAS industrial training in noida, SAS training institute in noida, SAS Training In ghaziabad, SAS Training Institute in noida, SAS coaching institute in noida, SAS training institute in Ghaziabad.

    ReplyDelete
  41. 1800-640-8917 Norton antivirus technical support phone number, Norton customer support toll free number, NORTON antivirus customer Support number, 1800-640-8917 NORTON antivirus tech support number, Norton antivirus technical support phone number, 1800-640-8917 Norton antivirus technical support number, 1800-640-8917 Norton antivirus technical support toll free number, Norton technical support number.

    ReplyDelete
  42. Webtrackker Technologies- Webtrackker is an IT company and also provides the project based industrial training in Java, J2EE. Webtrackker also provide the 100% job placement support. JAVA Training Institute in Meerut, Best J2EE training Institute in Meerut, best JAVA Training Institute in Meerut, best JAVA Training Institute in Meerut, project JAVA Training in Meerut, JAVA Training on live project based in Meerut, Java Training Courses in Meerut, Summer Training Program in Meerut, Summer Training Program on java in Meerut.

    ReplyDelete
  43. Thank you for sharing this blog. This blog will help to improve my knowledge.• I enjoyed reading your post specially those on Wonderful article....You can also visit Dotnet developer
    dotnet training in bangalore

    ReplyDelete
  44. Nice tips. Very innovative... Your post shows all your effort and great experience towards your work Your Information is Great if mastered very well.
    java training in chennai | java training in bangalore

    java online training | java training in pune

    java training in chennai | java training in bangalore

    ReplyDelete
  45. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    rpa training in Chennai | rpa training in pune

    rpa training in tambaram | rpa training in sholinganallur

    rpa training in Chennai | rpa training in velachery

    rpa online training | rpa training in bangalore

    ReplyDelete
  46. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.

    Best Selenium Training in Chennai | Selenium Training Institute in Chennai | Besant Technologies

    Selenium Training in Bangalore | Best Selenium Training in Bangalore

    AWS Training in Bangalore | Amazon Web Services Training in Bangalore

    ReplyDelete
  47. Thanks for sharing this valuable information to our vision. You have posted a worthy blog keep sharing.

    Article submission sites
    Guest posting sites

    ReplyDelete
  48. This is a terrific article, and that I would really like additional info if you have got any. I’m fascinated with this subject and your post has been one among the simplest I actually have read.
    devops online training

    aws online training

    data science with python online training

    data science online training

    rpa online training

    ReplyDelete
  49. Really great post, I simply unearthed your site and needed to say that I have truly appreciated perusing your blog entries. I want to say thanks for great sharing.
    Microsoft Azure online training
    Selenium online training
    Java online training
    uipath online training
    Python online training

    ReplyDelete
  50. I have to thank for sharing this blog admin, really helpful to me.

    Article submission sites
    Technology

    ReplyDelete
  51. Attend The Python training in bangalore From ExcelR. Practical Python training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python training in bangalore.
    python training in bangalore

    ReplyDelete
  52. Get the best nursing services baby care services medical equipment services and allso get the physiotherapist at home in Delhi NCR For more information visit our site nice page
    Ai & Artificial Intelligence Course in Chennai
    PHP Training in Chennai
    Ethical Hacking Course in Chennai Blue Prism Training in Chennai
    UiPath Training in Chennai

    ReplyDelete
  53. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it. The angular js programming language is very popular which are most widely used.


    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery





    ReplyDelete
  54. What are the prescribed procedures followed by the group for Salesforce including whether it is composed correspondence or spoken audits and so forth? Suggest me a Salesforce courses in pune

    ReplyDelete
  55. We came up with a great learning experience of Azure training in Chennai, from Infycle Technologies, the finest software training Institute in Chennai. And we also come up with other technical courses like Cyber Security, Graphic Design and Animation, Big Data Hadoop training in Chennai, Block Security, Java, Cyber Security, Oracle, Python, Big data, Azure, Python, Manual and Automation Testing, DevOps, Medical Coding etc., with great learning experience with outstanding training with experienced trainers and friendly environment. And we also arrange 100+ Live Practical Sessions and Real-Time scenarios which helps you to easily get through the interviews in top MNC’s. for more queries approach us on 7504633633, 7502633633

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

    ReplyDelete
  57. How to Activate Windows 7 Pro with Product Key · First of all Click on Start Menu, then find My Computer · Right Click on My Computer and Go to Properties · There .Free Windows 7 Professional Product Key

    ReplyDelete
  58. A simple Christmas greeting: Keep it short and sweet with a simple “Merry Christmas and a happy New Year”. A personalised Christmas card message for close .
    I Love You Christmas Quotes/a>

    ReplyDelete
  59. Good effort by the author in posting about the programming languages neatly with images.

    ReplyDelete

Blocks From one Class to Another

One popular scenario in using blocks is when you need to update your view after a web service call is finished.