Go Back   MacRumors Forums > Apple Systems and Services > Programming > iPhone/iPad Programming

Reply
 
Thread Tools Search this Thread Display Modes
Old Dec 10, 2008, 10:26 AM   #1
Picapau
macrumors newbie
 
Join Date: Dec 2008
SQLite Commit to DB

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
Picapau is offline   0 Reply With Quote
Old Dec 11, 2008, 01:13 AM   #2
gopicanwin
macrumors newbie
 
Join Date: Dec 2008
Quote:
Originally Posted by Picapau View Post
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;
}

Last edited by gopicanwin; Dec 11, 2008 at 01:14 AM. Reason: comment missing
gopicanwin is offline   0 Reply With Quote
Old Dec 11, 2008, 06:32 AM   #3
Picapau
Thread Starter
macrumors newbie
 
Join Date: Dec 2008
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
Picapau is offline   0 Reply With Quote
Old Dec 11, 2008, 10:41 AM   #4
beachdog
macrumors member
 
Join Date: Aug 2008
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?
beachdog is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Systems and Services > Programming > iPhone/iPad Programming

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
missing contacts, but available in backup/sqlite?! temen iPhone 0 Sep 2, 2013 04:52 PM
Just purchaised a no commit iPhone 5, plug it in 1st, or call at&t? SAIRUS iPhone 2 Sep 22, 2012 11:49 AM
Slow SQLite Query Starfox Mac Programming 3 Sep 10, 2012 01:04 PM
Sqlite and Core Data kimbebot iPhone/iPad Programming 5 Jul 23, 2012 02:44 AM

Forum Jump

All times are GMT -5. The time now is 10:22 PM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC