How to fix `no such table` error with SQLite

Question

I had to search for many posts regarding these errors, but still I cannot fix the problem. Here is my code, can anyone help me to see what is going wrong?

- (void) copyDatabaseIfNeeded {


    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
   NSString *path = [documentsDirectory stringByAppendingPathComponent:@"SQL.sqlite"];

    if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK) 
    {
        NSLog(@"Database opened successfully");

if(updateStmt == nil) {
            NSString *updStmt = [NSString stringWithFormat: @"UPDATE Coffee SET CoffeeName = '500 Plus', Price = '1.40' Where CoffeeID= '3'"];
   const char *mupdate_stmt = [updStmt UTF8String]; 

if(sqlite3_prepare_v2(newDBconnection, mupdate_stmt, -1, &updateStmt, NULL) != SQLITE_OK){
NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(newDBconnection));
            } else {
                NSLog(@"Update successful");
            }

        }
        if(SQLITE_DONE != sqlite3_step(updateStmt))
            NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(newDBconnection));
        else {
            sqlite3_reset(updateStmt);
            NSLog(@"Step successful");

        }
    } 
    else 
    {
        NSLog(@"Error in opening database  ");
    }
}

Answer 1

There is no table Coffee in SQL.sqlite.

SQLite silently creates the database file if it does not exist. So if you've got the path wrong, you are opening an empty database file, which of course does not contain any tables. Make sure the database file exists there and it is not empty.

You can see what tables are there in the database by running SELECT * FROM sqlite_master; query.

Answer 2

One thing my DBA tells me when I have database problems (Oracle in this case), is to try the query using the command line tools.

This is how you would diagnose problems with sqlite3 and the iPhone simulator:

1. Run the App in the iPhone Simulator so the database is created/copied in the Documents directory.
2. Start Terminal
3. cd ~/Library/Application Support/iPhone Simulator/<version>/Applications/<your-app-hash>/Documents. It's best to have just one App installed in the simulator so it's obvious what App is what.
4. sqlite3 SQL.sqlite and try your query from there. You can also use the .schema command to see what the schema looks like.

If the query works there and not in your App then you know you have a bug, else you have a sqlite query problem or broken schema.

Answer 3

try reinstalling the app.the tables are created at the time of installing the app.sometimes it might happen because tables are not created

Answer 4

I've had a "no such table error" because the db file was never copied to the bin folder. One the .db file in the project make sure its property "Copy to Output Directory" is set to either:

• Copy always (always copies the blank database)
• Copy if newer

Answer 5

I got the same error below:

Parse error: no such table: public.user

When I tried to rename the table name public.user to user as shown below. *The table name with . happened to me when converting a PostgreSQL dump file to a SQLite dump file with RebaseData:

ALTER TABLE public.user RENAME TO user;

So, I used "" for public.user as shown below, then I could rename the table name without error. *My answer explains it more:

            ↓           ↓
ALTER TABLE "public.user" RENAME TO user;

Answer 6

I was getting the same error but I could not see anything wrong. I figured out that my issue was in copying the CREATE TABLE from another terminal. I decided to delete the .db files and typed the syntax in by hand. No copy and paste. It stopped giving me the error after that. I can't see any special or hidden characters in the copied syntax, but doing it so was giving me that error every time until I typed it in by hand.