SQLite bug after changing sqlite request

Discussion in 'iOS Programming' started by snowboarderz69, Mar 11, 2010.

  1. snowboarderz69 macrumors newbie

    Joined:
    Feb 24, 2010
    #1
    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 :
    Code:
    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
     
  2. dejo Moderator

    dejo

    Staff Member

    Joined:
    Sep 2, 2004
    Location:
    The Centennial State
    #2
    SELET is not a valid SQL command. :)
     
  3. snowboarderz69 thread starter macrumors newbie

    Joined:
    Feb 24, 2010
    #3
    It's just a mistake in the thread, I didn't copy/pasted the code.. I wish it was that simple :(
     
  4. dejo Moderator

    dejo

    Staff Member

    Joined:
    Sep 2, 2004
    Location:
    The Centennial State
    #4
    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?
     
  5. snowboarderz69 thread starter macrumors newbie

    Joined:
    Feb 24, 2010
    #5
    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 :
    Code:
    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 =(
     
  6. robbieduncan Moderator emeritus

    robbieduncan

    Joined:
    Jul 24, 2002
    Location:
    London
    #6
    And most likely that's where the error is. Copy/paste the exact statement being executed.
     
  7. dejo Moderator

    dejo

    Staff Member

    Joined:
    Sep 2, 2004
    Location:
    The Centennial State
    #7
    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?
     
  8. snowboarderz69 thread starter macrumors newbie

    Joined:
    Feb 24, 2010
    #8
    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 :
    Code:
    -(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 :)
     
  9. dejo Moderator

    dejo

    Staff Member

    Joined:
    Sep 2, 2004
    Location:
    The Centennial State
    #9
    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?
     
  10. snowboarderz69 thread starter macrumors newbie

    Joined:
    Feb 24, 2010
    #10
    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
     
  11. snowboarderz69 thread starter macrumors newbie

    Joined:
    Feb 24, 2010
    #11
    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
    Code:
    - (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]);
        }
    }
     
  12. snowboarderz69 thread starter macrumors newbie

    Joined:
    Feb 24, 2010
    #12
    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 :D Thanks alot guys !
     

Share This Page