iOS sqlite3 : re-storing results

[DennisBlah]

Hi all,

I'm trying to grab the day of date from my agenda table where the month is current month.

[NSString stringWithFormat: @"SELECT strftime('%d', agenda_datum) as dayOfMonth FROM agenda_items WHERE strftime('%m', agenda_datum) = '%i'", month]

However this NSString assumes that.. %d %m and %i must be set.
However the %d should say, return day of month from date
and %m should say, return month from date

Here I try to get all days, from my agenda where month is equal to >month< (int of current month, or the month I'm looking at in my agenda)

---
Resolved:
I had to do the query setup in 2 steps.

        NSString *tempQuery = @"SELECT >DISTINCT< strftime('%d', agenda_datum) as dayOfMonth FROM agenda_items WHERE strftime('%m', agenda_datum) = ";
        NSString *finalQuery = [NSString stringWithFormat: @"%@'%i'", tempQuery, month];

---


Now I try to store these 'days' into an NSArray. Like this:

NSArray *dates = [NSArray arrayWithObjects:[NSNumber numberWithInt:1],[NSNumber numberWithInt:5], nil];

But this needs to be done programaticly.

This is giving me back an different number every time:

        NSString *tempQuery = @"SELECT >DISTINCT< strftime('%d', agenda_datum) as dayOfMonth FROM agenda_items WHERE strftime('%m', agenda_datum) = ";
        NSString *finalQuery = [NSString stringWithFormat: @"%@'%i'", tQuery, month];
        NSArray *markDays = [[NSArray alloc] initWithArray:[dbManager loadDataFromDB:finalQuery]];
        NSInteger indexOfDayOfMonth = [dbManager.arrColumnNames indexOfObject:@"dayOfMonth"];
        NSMutableArray *markedDays = [[NSMutableArray alloc] initWithObjects: nil];
        for(int a=0; a<markDays.count; a++) {
            int mDay = (int)[[markDays objectAtIndex: a] objectAtIndex: indexOfDayOfMonth];
            [markedDays addObject: [[markDays objectAtIndex: a] objectAtIndex: indexOfDayOfMonth]];
        }

(p.s. DISTINCT is like >DISTINCT< because I get blocked due SQL Injection :-/... woops ;D )

 

[xStep]

Your NSString stringWithFormat is looking for 3 values for the parameters designated by the percent signs. You are supplying only 'month'. Perhaps you want to escape the percent signs. Try adding a percent sign immediately before them.

I'm guessing what you want your actual SQL to be something like this

SELECT strftime('%d', agenda_datum) as dayOfMonth FROM agenda_items WHERE strftime('%m', agenda_datum) = ’12’

Here is a sample broken down, without the escaping since it isn't needed in this case.

    NSInteger month = 12;
    NSString * coreSQL = @"SELECT strftime('%d', agenda_datum) as dayOfMonth FROM agenda_items WHERE strftime('%m', agenda_datum) = ";
    NSString * paramSQL = [NSString stringWithFormat: @"%@'%ld'",coreSQL,(long)month];
    NSLog(@"SQL:  %@", paramSQL);

I suggest you break down that line into several so that you can better understand the problem.

 

[DennisBlah]

Your NSString stringWithFormat is looking for 3 values for the parameters designated by the percent signs. You are supplying only 'month'. Perhaps you want to escape the percent signs. Try adding a percent sign immediately before them.

I'm guessing what you want your actual SQL to be something like this

SELECT strftime('%d', agenda_datum) as dayOfMonth FROM agenda_items WHERE strftime('%m', agenda_datum) = ’12’

Here is a sample broken down, without the escaping since it isn't needed in this case.

    NSInteger month = 12;
    NSString * coreSQL = @"SELECT strftime('%d', agenda_datum) as dayOfMonth FROM agenda_items WHERE strftime('%m', agenda_datum) = ";
    NSString * paramSQL = [NSString stringWithFormat: @"%@'%ld'",coreSQL,(long)month];
    NSLog(@"SQL:  %@", paramSQL);

I suggest you break down that line into several so that you can better understand the problem.

^_^ that's indeed what I did, now I'm having a next issue with storing these days in an new array.