the select method is adjusting but insert method not working properly

Question

I am working on sqlite database , trying to insert into database but it is giving error "Error while inserting " Database is locked . I searched some post and also write reset and finalize statement but it is giving same error.

Here is my code

 if(addStmt == nil) 
 {
 //const char *sql = "insert into Limittabel (limitAmt) Values(?)";
 //const char *sql="INSERT INTO Limittabel (limitAmt, Profileid) VALUES(?, ?)";
 const char *sql="insert into Limittabel (limitAmt,Profileid) Values (?,?)";
 // Insert into Limittabel (limitAmt,Profileid) Values (500,1ドル)
 if(sqlite3_prepare_v2(database, sql, -1, &addStmt, NULL) != SQLITE_OK)
 NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
 }
 sqlite3_bind_text(addStmt, 1, [limitAmt UTF8String], -1, SQLITE_TRANSIENT);
// NSString *str_id_poi = [[NSString alloc] initWithFormat:@"%d", [Profileid integerValue]];
// sqlite3_bind_text(addStmt, 2, [str_id_poi UTF8String], -1, SQLITE_TRANSIENT);
 //sqlite3_bind_text(addStmt, 2, [ UTF8String], -1, SQLITE_TRANSIENT);
 NSLog(@"***Storing END on Database ***");
 if(SQLITE_DONE != sqlite3_step(addStmt))
 {
 NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
 } // sqlite3_finalize(addStmt);
 else{
 //sqlite3_last_insert_rowid;
 limitid = sqlite3_last_insert_rowid(database);
 NSLog(@"YES THE DATA HAS BEEN WRITTEN SUCCESSFULLY");
 }
 sqlite3_finalize(addStmt);
 }
 sqlite3_reset(addStmt);
 //sqlite3_finalize(addStmt);
}

If I am not opening the database it is giving Database locked error, if opening database it is giving ' No such Table ' error Please Help me. Thanks

• Krunal: Does this table Limittabel exists at your Document Directory path ?

Answer 1

I don't understand the "if I am not opening the database it is giving Database locked error" comment, because you simply can't use a database without opening it. It makes no sense to call SQLite functions without first opening database. Maybe I'm not understanding this comment.

But ignoring that for a second, you later say, "if opening database it is giving 'No such table' error": Are you sure you're finding the database correctly? The problem is, if you open database and it's not found, then a blank database will be created. Are you sure this hasn't happened to you? If this may have happened to you, you might want to

1. Reset the simulator (if you're using simulator) or delete and reinstall the app to make sure any blank databases that sqlite3_open may have created for you will be removed; and

2. Check to make sure the database exists before you open it or replace your occurrence of sqlite3_open with sqlite3_open_v2 with a third parameter of SQLITE_OPEN_READWRITE and fourth parameter of NULL. See SQLite will not prepare query when accessing database in Xcode

3. As an aside, you don't mention whether you're opening this from the bundle or Documents, but generally I advise to programmatically copy from bundle to Documents and then open it from there.

4. If you're doing this on the simulator, I sometimes find it helpful to examine the database used by the simulator from my Mac. Thus, I go to "~/Library/Application Support/iPhone Simulator/5.1/Applications/" (replace the 5.1 with whatever version of your simulator you are using ... if the Library is hidden, unhide it from Terminal with chflags nohidden ~/Library), I then go into the directory for the particular application, go into it's Documents folder, and then open the database in the Mac command line sqlite3 program. You can use that to examine the database and make sure the db is as you expect it.

Just a few thoughts for the "no such table" error.

• The iOSDev: +1 for detail explanation and the resource links you provide :) And would like to suggest a simple shortcut for the 4th point you have mentioned to unhide the LIBRARY just click on the Finder > Go and with option(alt) pressed will show you the LIBRARY and just click ont it will lead you to the LIBRARY :)

Answer 2

Here is link for creating, Open , inserting, and retrieve data from the SQLite database . It will help you.

SQLite Datase Create,Insert and Retrieve from the database Click here

• jonty: Earlyer i had Tabbased application now i have put the view on Tabbased application.after this process i am getting the problem the Datsbase there not inserting any value in database.and i am not getting inserted any value.
• jonty: I am working on Sqlite database , trying to insert into database but it is giving error "Error while inserting " Database is locked . I searched some post and also write reset and finalize statement but it is giving same error.Earlyer i had Tabbased application now i have put the view on Tabbased application.after this process i am getting the problem the Datsbase there not inserting any value in database.and i am not getting inserted any value.
• Vineesh TP: Can you create Database and Table ?

Answer 3

jonty to deal with the database its compulsory that first we open the database and then we makes changes in database and if it giving the error No such table then i recommend you check the existence of table in database i.e. such table created or not.

• jonty: .Earlyer i had Tabbased application now i have put the view on Tabbased application.after this process i am getting the problem the Datsbase there not inserting any value in database.and i am not getting inserted any value.

Answer 4

Try this code,

if(sqlite3_open([YourDatabasePath UTF8String],&db) == SQLITE_OK)
{
 NSString *querySQL = [NSString stringWithFormat: @"insert into Limittabel (limitAmt,Profileid) Values (?,?)"];
 char *errmsg=nil;
 if(sqlite3_exec(db, [querySQL UTF8String], NULL, NULL, &errmsg)==SQLITE_OK)
 {
 NSLog(@"YES THE DATA HAS BEEN WRITTEN SUCCESSFULLY");
 }
}
sqlite3_close(db);

If this code doesn't work then there is a problem in your query, check your query again. this code works at my side.

• Krunal: put NSLog of querySQLand show me your query. is it proper ? or what you wrote instead of YourDatabasePath in first line ?
• jonty: please help me any person. database not save in data.
• jonty: CREATE TABLE LimitTable(limitid integer primary key,limitAmt varchar,Profileid integer,foreign key(limitid)references ProfileTable(Profileid))
• Krunal: I do not want this create table query, i want query of insert statement put log of insert query. are you able to see Limittabel inside your Db ?
• Krunal: there are 3 columns in your table and while inserting you are provide in only 2 value, thats why your value is not getting inserted in Limittabel