cannot fetch value from sqlite in IOS

Question

Table values:

ID=1 CUSTOMERID=1 NAME=John EMAIL=email USERNAME=usernaeme

I am using this code to fetch customerId from Usertable with this code

@try {
    //CustomerIdField=@"admin";
   // customerUsername=@"admin";
    NSLog(@"the value of customerusername is %@",customerUsername);
    NSArray *dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *docsDir = [dirPaths objectAtIndex:0];
NSLog(@"inside function");
databasePath  = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"EBook.db" ]];
    const char *dbpath;
    @try {
        dbpath = [databasePath UTF8String];
        NSLog(@"the  const char is %s",dbpath);
    }
    @catch (NSException *exception) {
        NSLog(@"the exception3 is %@",exception);
    }


    if (sqlite3_open(dbpath, &Ebookreaderdb) == SQLITE_OK)

    {
    NSString *selectSQL = [NSString stringWithFormat: @"SELECT CUSTOMERID FROM Usertable WHERE EMAIL=\"%@\"",customerUsername];


        sqlite3_stmt  *selstatement;
    const char *select_stmt = [selectSQL UTF8String];
    //NSMutableArray *resultArray = [[NSMutableArray alloc]init];
    if (sqlite3_prepare_v2(Ebookreaderdb,
                           select_stmt, -1, &selstatement, NULL ) == SQLITE_OK)
    {

        NSLog(@"inside sqlite OK"); //this prints in log
        if (sqlite3_step(selstatement) == SQLITE_ROW)
        {

            NSLog(@"inside sqlite ROW"); // this is also printing in log
            NSString *userInfoStr = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selstatement,1)];
            NSLog(@"val is %@",userInfoStr);
          char *tmp = sqlite3_column_text(selstatement,1);
            if (tmp == NULL)
                CustomerIdField = nil;
            else
                CustomerIdField = [[NSString alloc] initWithUTF8String:tmp];



             CustomerIdField = [[NSString alloc] initWithUTF8String:
                             (const char *) sqlite3_column_text(selstatement,1)];



            NSLog(@"inside customer is %@",CustomerIdField);
           // [resultArray addObject:name];

        }

        else{
            NSLog(@"Not found");
          //  return nil;
        }
        sqlite3_reset(selstatement);
    }

But i am getting this exception Newpjtonfriday[2165:84017] the exception is 2 *** +[NSString stringWithUTF8String:]: NULL cString

I googled with the above result and everywhere it is saying that the value is null that is why the exception occurs. but in my case the value is there.

Because the code NSLog(@"inside sqlite ROW");

is coming in log meaning that a row exists in table. But cannot fetch it.

Please help

As an aside, do not use stringWithFormat to build a SQL statement. Use ? placeholders and sqlite3_bind_text function (which, confusingly, uses one-based index, unlike the zero-based index of sqlite3_column_text). — Rob
– Rob, Commented Apr 22, 2015 at 13:18
You called sqlite3_reset (used to reset bound columns to prepared statement, so the statement can be performed again) and you clearly meant to call sqlite3_finalize (which frees memory). Replace reset with finalize. Also, did you mean to open the database here and not close it? Personally, I'd separate opening of a database from the performing of SQL statements, but do whatever you want, but just make sure that every open has its corresponding close call. Needless to say, these annoying little details are what makes libraries like FMDB so compelling... — Rob
– Rob, Commented Apr 22, 2015 at 16:20

trojanfoe · Accepted Answer · 2015-04-22 12:42:59Z

2

I think it's a typo:

@"SELECT CUSTOMERID FROM Usertable WHERE EMAIL=\"%@\"",customerUsername;

You are passing the username instead of the e-mail? Or maybe you meant to SELECT ... WHERE USERNAME=?

Also there is no need for any of those @try/@catch blocks as I cannot see how an Objective-C exception can be thrown by that code.

One last thing; in order to avoid SQL Injection attacks you should bind the values into your statements, rather than formatting them as a string, as you have done here.

answered Apr 22, 2015 at 12:42

trojanfoe

123k23 gold badges219 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roman Safin · Accepted Answer · 2015-04-22 12:51:37Z

Little advise if you are just starting to develop on iOS, try to use some library(FMDB to easy work with SQLite) to make some task more straight forward.

Answer to your Question Try the "SELECT * FROM Usertable" if it has any row in your table. Second you should check if the value in your row is not NULL. To get easy solution use something like Datum SQLite Free(Mac os X) OR you could use SELECT COUNT to ensure you have any rows in table. Try your select and if the app return the rows but there is no value in your column that's mean you have problems with write to DB logic but not in read logic and you are trying to fetch row with have null value on column you want.That's why this method is not working:

[NSString stringWithUTF8String:]: NULL cString

its thrown an exception because you have no string and trying to send NULL to method.

Rob · Accepted Answer · 2015-04-22 13:25:29Z

2

The sqlite3_column_text index number is zero-based, not one-based. Thus, this line:

char *tmp = sqlite3_column_text(selstatement, 1);

Should be:

char *tmp = sqlite3_column_text(selstatement, 0);

By the way, your handling of this tmp variable is a prudent way of checking to make sure it's not NULL before you use it. Unfortunately, elsewhere in this same routine you use sqlite3_column_text value directly (which is why your app crashed, rather than gracefully reporting the error). You have several redundant calls to sqlite3_column_text here. I would suggest employing the pattern you used with this tmp variable.

edited Apr 22, 2015 at 13:25

answered Apr 22, 2015 at 13:11

Rob

441k74 gold badges851 silver badges1.1k bronze badges

Comments

Mukund · Accepted Answer · 2015-04-23 07:05:21Z

Let me give you my suggestion I tried what you just did and I also got the error. Then I did some googling and found this link

http://www.raywenderlich.com/913/sqlite-tutorial-for-ios-making-our-app

just modify you codes from these

 char *tmp = sqlite3_column_text(selstatement,1);
        if (tmp == NULL)
            CustomerIdField = nil;
        else
            CustomerIdField = [[NSString alloc] initWithUTF8String:tmp];



         CustomerIdField = [[NSString alloc] initWithUTF8String:
                         (const char *) sqlite3_column_text(selstatement,1)];



        NSLog(@"inside customer is %@",CustomerIdField);

to

int tmp = sqlite3_column_int(selstatement,0);// my case it is int
char *nameChars = (char *) sqlite3_column_text(selstatement, 1);// here is the change occuring please refer this
NSString *name = [[NSString alloc] initWithUTF8String:nameChars]; //  there are two steps first fetch as char, then change to String
 NSLog(@"customerid is %d",tmp);
  NSLog(@"customer name is %@",name);

Try this, It worked for me..

I think the problem is that you are fetching the coustomerId as String directly

In you case just try this

char *custId = (char *) sqlite3_column_text(selstatement, 0);
NSString *customerId = [[NSString alloc] initWithUTF8String:custId];

:-)

Collectives™ on Stack Overflow

cannot fetch value from sqlite in IOS

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related