iOS 8 - sqlite with unknown error.

Question

This is the first time I use Sqlite in my app. So I follow this tutorial. I edit the database a bit to suit my purpose:

CREATE TABLE appointmentInfo(clientInfoID integer primary key, customername text, personname text, position text,appointmenttime text)

Then I query with this code:

NSString *query = [NSString stringWithFormat:@"insert into appointmentInfo values(null, '%@', '%@', '%@', '%@')", self.customernameTF.text, self.personnameTF.text, self.positionTF.text,self.meetingtimeTF.text];
[self.dbManager executeQuery:query];

I keep getting errors:

DB Error: unknown error

But when I check the sql file, the record is captured.

What seems to be the problem?

You are inserting null to primary field value, why is that ? — Midhun MP, Apr 24 '15 at 10:09
@MidhunMP I actually copy it from the tutorial. Which I downloaded the sample project and there is also null. If not, what should I put there? — sooon, Apr 24 '15 at 10:39
It is just because you will not insert Primary key, it is inserted by sqlite it self as it is auto incremented primary key.. That's why tutorial shows inserting null to primary key. — karan, Aug 20 '15 at 10:42

score 5 · Answer 1 · answered Apr 24 '15 at 10:46

5

I found the solution at the discussion below the tutorial:

I changed the variable "executeQueryResults" to an int instead of a BOOL. That seems to have solved the problem.

I do this and I get an success message.

answered Apr 24 '15 at 10:46

sooon

4,718
8
63
116

score 0 · Answer 2 · answered Apr 24 '15 at 09:10

this because record inserted successfully so, DB showing unknown Error. i also faced same problem but database update success every time.

-(BOOL)createDB{
    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: @"vcndatabase.db"]];
    BOOL isSuccess = YES;
    NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &database) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt ="CREATE TABLE IF NOT EXISTS ANSWERLIST (ID INTEGER PRIMARY KEY AUTOINCREMENT,ANSWER TEXT, INDEXPATH TEXT)";
            if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg)!= SQLITE_OK)
            {
                isSuccess = NO;
                NSLog(@"Failed to create table");
            }
            sqlite3_close(database);
            return  isSuccess;
        }
        else {
            isSuccess = NO;
            NSLog(@"Failed to open/create database");
        }
        sqlite3_reset(statement);
        sqlite3_finalize(statement);
        sqlite3_close(database);
    }

    return isSuccess;
}

- (BOOL) saveData:(NSString*)answer index:(NSString*)index
{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {

//        NSString *insert_stmtstr = [NSString stringWithFormat:@"INSERT OR REPLACE INTO ANSWERLIST (ANSWER, INDEXPATH) VALUES ( \"%@\",(SELECT ANSWER FROM ANSWERLIST WHERE INDEXPATH = \"%@\");",answer, index];

        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO ANSWERLIST (ANSWER, INDEXPATH) VALUES (\"%@\",\"%@\")",answer,index ];
                                const char *insert_stmt = [insertSQL UTF8String];
                                sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
                                if (sqlite3_step(statement) == SQLITE_DONE)
                                {
                                    NSLog(@"%s", sqlite3_errmsg(database));
                                   // return YES;
                                }
                                else {
                                    NSLog(@"%s", sqlite3_errmsg(database));
                                  //  return NO;

                                }


    sqlite3_finalize(statement);
    sqlite3_close(database);
    }
    return NO;
}

- (NSString*) findByRegisterNumber:(NSString*)index
{
            const char *dbpath = [databasePath UTF8String];
            if (sqlite3_open(dbpath, &database) == SQLITE_OK)
            {
//                NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM ANSWERLIST "];
                NSString *querySQL = [NSString stringWithFormat:@"SELECT ANSWER FROM ANSWERLIST WHERE INDEXPATH=\"%@\"",index ];
                const char *query_stmt = [querySQL UTF8String];

                if (sqlite3_prepare_v2(database,query_stmt, -1, &statement, NULL) == SQLITE_OK)
                {
                    if (sqlite3_step(statement) == SQLITE_ROW)
                    {
                        NSString *name = [[NSString alloc] initWithUTF8String:
                                          (const char *) sqlite3_column_text(statement, 0)];

//                         NSLog(@"%s", sqlite3_errmsg(database));
//                        NSString *department = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
                      //  [resultArray addObject:department];
//                        NSString *year = [[NSString alloc]initWithUTF8String:
//                                          (const char *) sqlite3_column_text(statement, 2)];
                        sqlite3_reset(statement);
                        sqlite3_finalize(statement);
                        sqlite3_close(database);
                        return name;
                    }
                    else{
                        NSLog(@"%s", sqlite3_errmsg(database));
                        sqlite3_reset(statement);
                        sqlite3_finalize(statement);
                        sqlite3_close(database);
                        NSLog(@"Not found");

                    }
                }

                }

            return nil;
}
-(void)update:(NSString*)answer index:(NSString*)index{


    const char *dbpath = [databasePath UTF8String];
    if(sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
     //   NSString *insertSQL = [NSString stringWithFormat:@"UPDATE ANSWERLIST SET ANSWER = '%@' WHERE INDEXPATH ='%@'",answer,index];

        const char *sql = "update ANSWERLIST Set ANSWER = ? Where INDEXPATH=?";
        if(sqlite3_prepare_v2(database, sql, -1, &statement, NULL)==SQLITE_OK){
            sqlite3_bind_text(statement, 2, [index UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 1, [answer UTF8String], -1, SQLITE_TRANSIENT);
        }
    }
    char* errmsg;
    sqlite3_exec(database, "COMMIT", NULL, NULL, &errmsg);

    if(SQLITE_DONE != sqlite3_step(statement)){
        NSLog(@"Error while updating. %s", sqlite3_errmsg(database));
    }
    else{

    }
    sqlite3_finalize(statement);
    sqlite3_close(database);

}

score 0 · Answer 3 · edited May 23 '17 at 12:26

In My case when I was inserting record, query was not executed and getting DB unknown error. there was a problem with my phone's memory. I restarted then checked there was memory. Even my app was not able to install.

Also was getting "installation failed invalid argument" for the same reason, which was solution to this problem also: Installation Failed "Invalid argument" when trying to run Today application extension

Restart phone, or check memory.

Worked for me... ;)

iOS 8 - sqlite with unknown error.

3 Answers3