Problem with SQL statement

Question

Hello,

I have the following code:

if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) 
    {
        // Setup the SQL Statement and compile it for faster access
        const char *sqlStatement = "select ZWAYPOINT_X, ZWAYPOINT_Y from ZWAYPOINT where ZMAP_ID %@", mapID;
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) 
        {
            // Loop through the results and add them to the Route Name array
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) 
            {
                // Read the data from the result row
                if((char*)sqlite3_column_text(compiledStatement, 0) != NULL)
                {                                       
                    NSString *xCoordinate = [NSString stringWithUTF8String:(char*)sqlite3_column_text(compiledStatement, 0)];
                    NSString *yCoordinate = [NSString stringWithUTF8String:(char*)sqlite3_column_text(compiledStatement, 1)];

                    NSLog(@"xCoordinate: %@", xCoordinate);
                    NSLog(@"yCoordinate: %@", yCoordinate);

                    CLLocationCoordinate2D coordinate = {xCoordinate, yCoordinate};
                    MapPin *pin = [[MapPin alloc]initWithCoordinates:coordinate
                                                           placeName:@"Keenan Stadium"
                                                         description:@"Tar Heel Football"];
                    [self.mapView addAnnotation:pin];
                    [pin release];
                }
            }
        }
        else 
        {
            NSLog(@"Error: failed to select details from database with message '%s'.", sqlite3_errmsg(database));
        }

I have a few questions:

1. in my SQL statement how do I include the variable mapID as part of the SQL statement

2. the line

   CLLocationCoordinate2D coordinate = {xCoordinate, yCoordinate};

gives me the following warning "Incompatible types in initialization"

Thanks

Answer 1

To include MapID,

Change this:

const char *sqlStatement = "select ZWAYPOINT_X, ZWAYPOINT_Y from ZWAYPOINT where ZMAP_ID %@", mapID; 

To this:

const char *sqlStatement = "select ZWAYPOINT_X, ZWAYPOINT_Y, MapID from ZWAYPOINT where ZMAP_ID %@", mapID; 

Answer 2

You get a warning creating that struct since the values are not NSString * but double. http://developer.apple.com/iphone/library/documentation/CoreLocation/Reference/CLLocation_Class/CLLocation/CLLocation.html#jumpTo_17

Answer 3

You should use a parameterized statement and bind values, like this:

const char *sqlStatement = "select ZWAYPOINT_X, ZWAYPOINT_Y from ZWAYPOINT where ZMAP_ID = ?";
sqlite3_stmt *compiledStatement;
if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) 
{
    sqlite3_bind_text( compiledStatement, 1, [mapID UTF8String], -1, SQLITE_TRANSIENT );

(Note that when binding parameters, you start at 1, but when reading columns from result rows, you start at 0...). I'm assuming your mapID is an NSString since you tried to stick it in your query using %@. If it's something else, you'll have to use a different bind function and obviously skip the UTF8String.