iOS SQLite bug after changing sqlite request

[snowboarderz69]

Hello everyone,

I made a simple app that show the content of a table on a cell view. Everything is working fine but when I'm trying to change the query to get informations from another table, it dosen't work... The only thing I do is change the request in the database initialization and in the initWithPrimaryKey fonction. (I change "SELECT pk FROM itineraire" to "SELET pk FROM etape" and it's not a database problem..)

When it try to bind the sql, it dosen't pass the first IF and go to the error message :

if (sqlite3_step(init_statement) == SQLITE_ROW){
			self.text = [NSString stringWithUTF8String:(char*)sqlite3_column_text(init_statement,0)];
		} else {
                      //It goes here and show the message 4 time (there's 4  //element supposed to be returned by my db)
			self.text = @"Aucune itininéraire disponible";
		}

Anyone have an idea on what's going on ?
Thanks

 

[dejo]

(I change "SELECT pk FROM itineraire" to "SELET pk FROM etape" and it's not a database problem..)

SELET is not a valid SQL command.

 

[snowboarderz69]

It's just a mistake in the thread, I didn't copy/pasted the code.. I wish it was that simple

 

[dejo]

It's just a mistake in the thread, I didn't copy/pasted the code.. I wish it was that simple

Pasting the actual code into a thread helps us to better evaluate what the code is actually trying to do.

Also, can you show us how init_statement is defined and set?

 

[snowboarderz69]

The code is copy/pasted, it's only the select that isn't.

The init_statement is defined at the begining of the class with :
static sqlite3_stmt *init_statement = nil;

He's set after the if with :

sqlite3_bind_int(init_statement, 1, primaryKey);
		if (sqlite3_step(init_statement) == SQLITE_ROW){
			self.text = [NSString stringWithUTF8String:(char *)sqlite3_column_text(init_statement,0)];
		} else {
			self.text = @"Aucune itininéraire disponible";
		}
		sqlite3_reset(init_statement);

It's very strange =(

 

[robbieduncan]

The code is copy/pasted, it's only the select that isn't.

And most likely that's where the error is. Copy/paste the exact statement being executed.

 

[dejo]

It's very strange =(

So, you have no sqlite3_prepare_v2?

Why not just paste the actual method(s) making these calls so that we can see the real code, in their entirety?

 

[snowboarderz69]

Yeah sorry I didn't wanted to flood with the whole code but I guess it's the only way that you guys can help me.. Here it is :

-(void)initializeDatabase {
	NSMutableArray *itineraireArray = [[NSMutableArray alloc] init];
	self.itineraires = itineraireArray;
	[itineraireArray release];
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *documentsDirectory = [paths objectAtIndex:0];
	NSString *path = [documentsDirectory stringByAppendingPathComponent:@"itineraire.sqlite"];
	//Ouvre la base de donnée pour effectuer les requêtes
	if (sqlite3_open([path UTF8String], &database) == SQLITE_OK){
		const char *sql = "SELECT pkEtape FROM etape";
		sqlite3_stmt *statement;
		
		if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
			while (sqlite3_step(statement) == SQLITE_ROW){
				int primaryKey = sqlite3_column_int(statement,0);
				Itineraire *iti = [[Itineraire alloc] initWithPrimaryKey:primaryKey database:database];
				//Etape *eta	=	[[Etape alloc] initWithPrimaryKey:primaryKey database:database];
				//[etapes addObject:eta];
				[itineraires addObject:iti];
				//[eta release];
				[iti release];
			}
		}
		sqlite3_finalize(statement);
	} else {
		sqlite3_close(database);
		NSAssert1(0, @"Erreur l'hors de l'ouverture de la base de donnée : '%s'.", sqlite3_errmsg(database));
	}
}

//Then I initialize my view with my primarykey 
-(id)initWithPrimaryKey:(NSInteger)pkEtape database:(sqlite3 *)db {
	if (self = [super init]) {
		primaryKey = pkEtape;
		database   = db;
		if (init_statement == nil){
			const char *sql = "SELECT text FROM etape WHERE pkEtape=?";
			if (sqlite3_prepare_v2(database, sql, -1, &init_statement, NULL) != SQLITE_OK){
				NSAssert1(0, @"Erreur: La preparation de la commande à échoué '%s'.", sqlite3_errmsg(database));
			}
		}
		sqlite3_bind_int(init_statement, 1, primaryKey);
		if (sqlite3_step(init_statement) == SQLITE_ROW){
			self.text = [NSString stringWithUTF8String:(char *)sqlite3_column_text(init_statement,0)];
		} else {
			self.text = @"Aucune itininéraire disponible";
		}
		sqlite3_reset(init_statement);
	}
	return self;
}

This code show me 4 times : Aucune itinéraire disponbible. If I change both SELECT for SELECT pkItineraire FROM itineraire And SELECT text FROM itineraire WHERE pkItineraire=? , it will work.

I tought it could be my DB but it shouldnt. itinerare have text and pkItineraire, etape have text and pkEtape..

Thank you very much to take some time to help me, I really apreciate it

 

[dejo]

I tought it could be my DB but it shouldnt. itinerare have text and pkItineraire, etape have text and pkEtape..

But do they have the same primary key values?

Also, sqlite3_step(init_statement) is not returning SQLITE_ROW. So, the question then becomes: what is it returning?

 

[snowboarderz69]

Yeah that's what i'm wondering..

If that can help, my database look like this :
Table itineraire

pkItineraire text
1 Bahamas
2 Montreal
3 Hawaii

---
Table etape
pkEtape text
1 Nasseau
2 La plage
3 L'hotel
4 Something else

 

[snowboarderz69]

I just tought that I have a function to creat an editable copy of the database. Is it possible that it created a copy of my database when there we're mistake in it (there was some before) and that my app is stilll reading it?

Here's my function

- (void)createEditableCopyOfDatabaseIfNeeded {
	//Vérifie si une copie existe déja
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"itineraire.sqlite"];
    success = [fileManager fileExistsAtPath:writableDBPath];
    if (success) return;
    //La copie n'existe pas donc on la crée.
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"itineraire.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        NSAssert1(0, @"Impossible de créer une copie de la base de donnée :'%@'.", [error localizedDescription]);
    }
}

 

[snowboarderz69]

I deleted any local files that we're in link with my itineraire.sqlite and it was not working. I started doing something else and a few minutes later I tryed it again and now it's working.. Sounds like a "cache" bug I guess.. ?

Anyway, it's working now Thanks alot guys !