iOS SQLite Commit to DB

[Picapau]

Hi everyone,

Im having a problem with SQLite access. I write some entries to the DB and later I write some more so I first check if they are already in the DB to not get an error.
The problem is that if I write the entries, then exit the program (so close the database) and then start it up again and try to write the same entries, the "check" routine works perfectly and it doesnt write the same entries again.
But if I write them and then check if they are written inmediatly without closing the database. It doesnt find the entries just written!!

The code is very similar to this one although, the "check" part is another method. But I think this shows better my problem:

if (insert_statement == nil) {
static char *sql = "INSERT INTO todo (id,value) VALUES(?,?)";
if (sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}
}
sqlite3_bind_int(init_statement, 1, t.id);
sqlite3_bind_int(init_statement, 2, t.value);

int success = sqlite3_step(insert_statement);

sqlite3_reset(insert_statement);

if (success != SQLITE_ERROR) {
pk = sqlite3_last_insert_rowid(database);
} else {
NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
}


// Now check that the entry has been set to the database
if (check_statement == nil) {
const char *sql = "SELECT value FROM todo WHERE id=?";
if (sqlite3_prepare_v2(database, sql, -1, &check_statement, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}
}

sqlite3_bind_int(check_statement, 1, t.id);
if (sqlite3_step(check_statement) == SQLITE_ROW) {
NSLog(@"Entry found in the database");
} else {
NSLog(@"Entry NOT found in the database");
}
sqlite3_reset(check_statement);


I always get the "Entry NOT found in the database" at least I dont close the database and open it up again.

Is there anyway to commit the insert so I can select the entries I have just inserted inmediatly?

Thanks in advance,
Picapau

 

[gopicanwin]

Hi everyone,

Im having a problem with SQLite access. I write some entries to the DB and later I write some more so I first check if they are already in the DB to not get an error.
The problem is that if I write the entries, then exit the program (so close the database) and then start it up again and try to write the same entries, the "check" routine works perfectly and it doesnt write the same entries again.
But if I write them and then check if they are written inmediatly without closing the database. It doesnt find the entries just written!!

The code is very similar to this one although, the "check" part is another method. But I think this shows better my problem:

if (insert_statement == nil) {
static char *sql = "INSERT INTO todo (id,value) VALUES(?,?)";
if (sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}
}
sqlite3_bind_int(init_statement, 1, t.id);
sqlite3_bind_int(init_statement, 2, t.value);

int success = sqlite3_step(insert_statement);

sqlite3_reset(insert_statement);

if (success != SQLITE_ERROR) {
pk = sqlite3_last_insert_rowid(database);
} else {
NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
}


// Now check that the entry has been set to the database
if (check_statement == nil) {
const char *sql = "SELECT value FROM todo WHERE id=?";
if (sqlite3_prepare_v2(database, sql, -1, &check_statement, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}
}

sqlite3_bind_int(check_statement, 1, t.id);
if (sqlite3_step(check_statement) == SQLITE_ROW) {
NSLog(@"Entry found in the database");
} else {
NSLog(@"Entry NOT found in the database");
}
sqlite3_reset(check_statement);


I always get the "Entry NOT found in the database" at least I dont close the database and open it up again.

Is there anyway to commit the insert so I can select the entries I have just inserted inmediatly?

Thanks in advance,
Picapau

Reply:
Hi,
you just insert this code only.

if (insert_statement == nil)
{
static char *sql = "INSERT INTO todo (id,value) VALUES(?,?)";
if (sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL) != SQLITE_OK)
{
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}
}
sqlite3_bind_int(init_statement, 1, t.id);
sqlite3_bind_int(init_statement, 2, t.value);

int success = sqlite3_step(insert_statement);

sqlite3_reset(insert_statement);

if (success != SQLITE_ERROR)
{
pk = sqlite3_last_insert_rowid(database);
} else
{
NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
return -1;
}

 

[Picapau]

Hi,

Well, the code i posted is not exactly the code in my application but a proof of concept. Im downloading some rss feed from internet and then inserting each entry into the database. If the user request new news, new items are downloaded but some of them may already exist in the database so before inserting a new entry I need to check if there is an entry with the same title and date already in the database.

Ive checked that after inserting the news they are commited to the database, but the select statement doesnt retrieve them if I dont close the database first. Strange, isnt it?

In addition, Im getting a random error when preparing statments. Sometimes (I cant figure out when or why) the application fails preparing an statment and I get a sqlite3prepare error ...

Thanks again for the help

 

[beachdog]

I've done a lot of work with SQLite and not had this problem. Are you perchance using different database connections (i.e., different 'database' instances in your example above) between the insert and the later select?