Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Apple Systems and Services > Programming > iPhone/iPad Programming

Reply
 
Thread Tools Search this Thread Display Modes
Old Dec 10, 2008, 09: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, 12: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 12:14 AM. Reason: comment missing
gopicanwin is offline   0 Reply With Quote
Old Dec 11, 2008, 05: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, 09: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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
thread Thread Starter Forum Replies Last Post
Using UUID as pkey in sqlite db mraheel iPhone/iPad Programming 9 Dec 2, 2011 01:28 PM
sqlite problem ghousemd iPhone/iPad Programming 1 Jul 5, 2011 01:40 AM
Designing an SQLite backend, workups for a todo app mraheel iPhone/iPad Programming 0 Feb 6, 2011 04:24 AM
SQLite creating blank DB at startup markredson iPhone/iPad Programming 0 Feb 27, 2009 06:14 PM
I wonder if I send SQLite or sth DB data from the iPhone to my desktop program okskpark iPhone/iPad Programming 1 Oct 2, 2008 07:22 PM


All times are GMT -5. The time now is 02:12 PM.

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

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