iOS Const to Char for SQL Statement

Kizmar · Nov 15, 2010

This should be easy enough, but I just can't get this work work.

I'm trying to use 3 const variables to create a sql statement to pass to my DB access class.

I'm concatenating the constants in different methods of the class to build a full SQL statement. This way I don't have to have the same select showing up once in each method.

Something I'm doing isn't working though. The app either crashes or returns zero records. If I change this all to a straight char and dump the sql statement in it works just fine.

Here's my constants:

Code:

#define sqlSelect "SELECT beers.ID, beers.name, beers.breweryID, breweries.name, beers.type, beers.style, beers.flavor, beers.rating, beers.url, beers.desc, beers.abv, beers.imageURL "
#define sqlFrom "FROM beers "
#define sqlJoinBrewery "INNER JOIN breweries ON beers.breweryID = breweries.ID "

Here's where I'm trying to concatenate them:

Code:

dbAccess.sql = (char *)[NSString stringWithFormat:@"%@%@%@", sqlSelect, sqlFrom, sqlJoinBrewery];;

sql is a char property of the dbAccess class which is used to run a sqlite3_prepare_v2.

With this code the app crashes as soon as I try to hit this method. I'm not familiar enough with the console to know what's going wrong.

I'm pretty new to Objective-C and I'm guessing there's something blatantly obvious to more experienced people as to why this isn't working.

When I changed from #define constants to this type...

Code:

NSString * const MyFirstConstant = @"FirstConstant";

...the app doesn't crash, but the result set is empty.

The following code works but then I have SQL repeated all over the class:

Code:

dbAccess.sql = "SELECT ... FROM ...";

Need a point in the right direction. Thanks.

dejo · Nov 15, 2010

Kizmar said:
When I changed from #define constants to this type...

Code:

NSString * const MyFirstConstant = @"FirstConstant";

...the app doesn't crash, but the result set is empty.

Notice that your variable uses @ whereas your #defines don't.

PhoneyDeveloper · Nov 15, 2010

The following code works but then I have SQL repeated all over the class:

Not a problem. Do it that way.

chown33 · Nov 15, 2010

Kizmar said:
Here's where I'm trying to concatenate them:

Code:

dbAccess.sql = (char *)[NSString stringWithFormat:@"%@%@%@", sqlSelect, sqlFrom, sqlJoinBrewery];;

The class method stringWithFormat: returns an NSString* type. Casting it to char* does not magically convert it into a C-style string, any more than casting a double to char* automatically converts it to a string of numerals.

If you want a C-style string, there are NSString methods that can produce them, but you must call the method.

Also, sqlSelect et al. are not NSString objects (nor are they any type of id), so %@ will invariably fail. There is a %s format specifier, but you have to use it. Look it up.

You also need to post how the sql property is defined, because I can't think of a way that char* would work correctly with any of the property attributes. It may be that doing so is impossible, and you shouldn't be using a property in this case. No way to tell without seeing code.

You seem to be confused about when to use plain C and when to use Objective-C.

I suggest you start asking yourself the question "Is this an object type or is it a plain C type?" every time you have a format argument or an assignment to any variable or property. You should also ask yourself that question whenever you have a type cast, asking it about the value being cast to the different type. "Object type" includes anything that is a pointer to an object, which includes the id type. "Plain C type" includes all C basic types and all pointers thereto, including pointers to structs or other composites (a C string is a composite). If you can't answer the question by yourself, each time you ask it, then you need to go back and review some fundamentals.

I also recommend that if you don't need Objective-C to perform an operation, that you don't try using it. I even suggest going so far as to not mix plain C and Objective-C in the same function or method. Your defined C strings can be concatenated with snprintf(), a plain C function. That's just one option. Also see strlcat().

Kizmar · Nov 16, 2010

Great replies. Thanks!

Based on your direction, I've done some homework and ended up with this:

Code:

dbAccess.sql = (char *)[[[NSString alloc] initWithFormat:@"%s%s%s", sqlSelect, sqlFrom, sqlJoinBrewery] UTF8String];

I don't know if this seems dirty to you guys, but it works.

@chown33 : Here's what's happening with the "sql" char property in the dbAccess class:

Code:

- (void)prepareDatabase {
	sqlResult = sqlite3_prepare_v2(database, sql, -1, &statement, NULL);
}

Resources:
NSString Documentation
String Format Specifiers

chown33 · Nov 16, 2010

Kizmar said:
Great replies. Thanks!

Based on your direction, I've done some homework and ended up with this:

Code:

dbAccess.sql = (char *)[[[NSString alloc] initWithFormat:@"%s%s%s", sqlSelect, sqlFrom, sqlJoinBrewery] UTF8String];

I don't know if this seems dirty to you guys, but it works.

It also leaks the alloc'ed NSString.

Code:
@chown33 : Here's what's happening with the "sql" char property in the dbAccess class:

Code:

- (void)prepareDatabase { sqlResult = sqlite3_prepare_v2(database, sql, -1, &statement, NULL); }

I meant "show how the sql property is declared and synthesized, if it's a property, or show its declared type if it's a struct member". I also meant "show the type of the dbAccess" variable.

It's unclear whether dbAccess is an object with a sql property, or a struct with a sql member, since the syntax would be identical: dbAccess.sql. And the normal convention for Obj-C class names is Capitalized, so it's even less clear what types are involved.

Kizmar · Nov 16, 2010

I'm starting to realize that (what I think to be) simple things like variable declaration and string manipulation are much more complex in Objective-C/C then what I'm used to with C#.NET.

I'm also still learning the ropes when it comes to classes, properties and using these.

That said... here's my DBAccess class... be gentile...

DBAccess.h:

Code:

#import <Foundation/Foundation.h>
#import <sqlite3.h>

@interface DBAccess : NSObject {
	sqlite3 *database;
	char *sql;
	sqlite3_stmt *statement;
	int sqlResult;
}

@property (nonatomic) sqlite3 *database;
@property (nonatomic) char *sql;
@property (nonatomic) sqlite3_stmt *statement;
@property (nonatomic) int sqlResult;

// database access methods
- (void)closeDatabase;
- (void)initDatabase;
- (void)prepareDatabase;
- (void)finalizeDatabase;

@end

DBAccess.m

Code:

#import "DBAccess.h"

@implementation DBAccess

@synthesize database;
@synthesize sql;
@synthesize statement;
@synthesize sqlResult;

#pragma mark Init type stuff
- (id)init {
	if (self = [super init]) {
		[self initDatabase];
	}
	return self;
}

- (void)initDatabase {
	NSString *path = [[NSBundle mainBundle] pathForResource:@"<dbname>" ofType:@"sqlite"];
	
	if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		NSLog(@"Opening Database");
	}
	else {
		sqlite3_close(database);
		NSAssert1(0, @"Failed to open database: '%s'.", sqlite3_errmsg(database));
	}
}
				  
- (void)closeDatabase {
	if (sqlite3_close(database) != SQLITE_OK) {
		NSAssert1(0, @"Failed to close database: '%s'.", sqlite3_errmsg(database));
	}
}

- (void)prepareDatabase {
	sqlResult = sqlite3_prepare_v2(database, sql, -1, &statement, NULL);
}

- (void)finalizeDatabase {
	sqlite3_finalize(statement);
}

// create a writable copy of the default database from the bundle
- (void)createEditableDatabase {
	...
}

- (void)dealloc {
	[super dealloc];
}

@end

I've been making changes to core code like this as I learn what I'm doing right and wrong. I'm attempting to apply what I already know about object oriented programming to what I'm learning reading these "iOS 4 Application Development" books.

Kizmar · Nov 16, 2010

chown33 said:
It also leaks the alloc'ed NSString.

So this would be ideal then, in order to not leak an allocated NSString?

Code:

dbAccess.sql = (char *)[[NSString stringWithFormat:@"%s%s%s", sqlSelect, sqlFrom, sqlJoinBrewery] UTF8String];

This also compiles and runs... does what I want it to do.

chown33 · Nov 16, 2010

Kizmar said:
So this would be ideal then, in order to not leak an allocated NSString?

Code:

dbAccess.sql = (char *)[[NSString stringWithFormat:@"%s%s%s", sqlSelect, sqlFrom, sqlJoinBrewery] UTF8String];

This also compiles and runs... does what I want it to do.

It doesn't leak, but it's far from ideal. You still have a serious variable-lifetime problem.

The buffer returned by UTF8String will disappear when the NSString that returned it is dealloc'ed. Yet you've assigned that buffer to the dbAccess.sql property, which does not copy it. So now you have a bug that is akin to a "you didn't retain an object" bug, but because the char* isn't an object, you have no way of actually retaining the object in order to preserve the buffer. So basically, the whole DBAccess class is misdesigned. You can't just declare anything and everything a property and expect it to work. In particulare, any property that's a pointer but not an object type will be practically impossible to get working properly, due to the lifetime issue. In other words, neither the object with the property nor the property accessors have control over the object that actually exists behind the char*, so there's no way for them to control its lifetime by calling -retain or -copy on the object. The code that needs to control the object's lifetime doesn't even have an object: it's a fundamental design flaw.

There is little more than a superficial similarity between C# and other C-based languages (C, Objective-C, C++). The most obvious difference is in how object lifetime is handled: C# has automatic GC, the others don't. (Although there's GC for Obj-C under Mac OS 10.5+, there's no GC for iOS so it's irrelevant.)

And when dealing directly with C memory, as a char* or other non-object pointers, there's essentially no similarity to C# at all. If you don't really understand how pointers in C work, then you can't possibly expect to use them properly in C or Objective-C. Your code may work in simple cases, or by accident, or for short periods of time that happen to not run into lifetime bugs. But that's like saying the ability to hold your breath for 30 seconds is all that's needed to live underwater indefinitely.

If you want to see what a real SQLite wrapper in Objective-C looks like, get the source for FMDB or OmniDataObjects and read it:
http://github.com/ccgus/fmdb
http://www.omnigroup.com/company/developer/

In my opinion you're in over your head by attempting to write the DBAccess class in the way you've shown. Using the underwater metaphor, you will shortly be drowning with almost no hope of rescue, but have no idea how quickly that's going to happen. You would be much better off going back to the fundamentals, and reading the above code as working examples. Write some simpler programs first, instead of the final app.

Finally, the C compiler can automatically concatenate C-string literals simply by listing them adjacent to one another, e.g.:

Code:

char * s = "Some " "string " "here";

will result in the string "Some string here". This will work when the string literals are #defined constants, e.g.:

Code:

char * s = sqlSelect sqlFrom sqlJoinBrewery;

Kizmar · Nov 16, 2010

Thanks for having patience with my ignorant questions. I'll check out the links you provided.

Search

Search

iOS Const to Char for SQL Statement

Kizmar

macrumors newbie

dejo

Moderator emeritus

PhoneyDeveloper

macrumors 68040

chown33

Moderator

Kizmar

macrumors newbie

chown33

Moderator

Kizmar

macrumors newbie

Kizmar

macrumors newbie

chown33

Moderator

Kizmar

macrumors newbie

Our Staff