Objective-C and MySQL

Question

I've been able to connect to a MySQL database in my app and use the C API which is almost exactly like PHP commands (mysql_real_connect(), mysql_query(), mysql_fetch_array(), etc.) and which I am pretty comfortable with I'm just not sure how the data query is returned. Do I use an array or dictionary and then how would I parse it. For example, in PHP I would do something like so (after the connection):

$results = mysql_query("SELECT * FROM theDatabase"); 
if (mysql_num_rows($results) > 0) { 
    while($row = mysql_fetch_array($results)) {
       print $row;
    }
}

What would the objective-c equivalent be? Thanks.

Edit:

OK, so I made some progress - I can make the query and get the number of fields/rows returned, just can't seem to access the data itself. Here's my code, which I stitched together from the MySQL docs and a few other sites:

- (IBAction)dbConnect:(id)sender {


NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
MYSQL mysql;
mysql_init(&mysql);

if (!mysql_real_connect(&mysql, "10.1.1.99", "******", "******", "oldphotoarchive", 0, NULL, 0)) {
    NSLog(@"%@", [NSString stringWithUTF8String:mysql_error(&mysql)]);
} else { 

    MYSQL_RES *result;
    MYSQL_ROW row;
    unsigned int num_fields;
    unsigned int num_rows;
    unsigned long *lengths;

    if (mysql_query(&mysql,"SELECT * FROM photorecord")) {
        // error
    } else { // query succeeded, process any data returned by it  

        result = mysql_store_result(&mysql);
        if (result)  {
            num_fields = mysql_num_fields(result);
            while ((row = mysql_fetch_row(result))) {
                lengths = mysql_fetch_lengths(result);

                for(int i = 0; i < num_fields; i++) {
                                            //the line below is my problem, printing row[i] fails, I get the GNU gdb error...
                    row[i] ? NSLog(@"%@", row[i]) : NSLog(@"wtf");
                }
            }


        } else  {// mysql_store_result() returned nothing; should it have?
            if (mysql_errno(&mysql)) {
                NSLog(@ "Error: %s\n", mysql_error(&mysql));
            } else if (mysql_field_count(&mysql) == 0) {
                // query does not return data
                // (it was not a SELECT)
                num_rows = mysql_affected_rows(&mysql);
            }
        }

    }

}

[pool release];
}

Answer 1

There's no Apple-supplied Objective-C API for MySQL. There are a few third-party wrappers of the C API, though. Take a look at the MySQL-Cocoa Framework, for example.

Given your familiarity with the PHP and C API, it may be more straightforward for you simply to use the C API. You'll need to handle conversion between objects and C data types, but this isn't much work.

Edit

You're crashing because the row value returned by the mysql API isn't an object, and your format string is telling NSLog to treat it as one. The %@ is a format-string placeholder for an object, not a C data type.

It's not clear what the value is in this case. The context seems to imply that it's image data. If that's the case, you'll likely want to create an NSData object from the blob returned by the query, e.g.:

NSData    *imageData;

imageData = [[ NSData alloc ] initWithBytes: row[ i ] length: lengths[ i ]];
NSLog( @"imageData: %@", imageData );
/* ...create NSImage, CGImage, etc... */
[ imageData release ];

If your result fields are just strings, use NSString's -initWithBytes:length:encoding: method:

NSString    *s;

s = [[ NSString alloc ] initWithBytes: row[ i ] length: lengths[ i ]
                        encoding: NSUTF8StringEncoding ];
NSLog( @"result column %d: %@", i, s );
[ s release ];