iOS sqlite query from UITextfield

[hervey]

I'm able to query the sqlite database using a searchBar and delegate, but a quicker interface for the user would be direct from a UITextField with a four character string (an airport identifier) which the user would know more often than not.

I'm not able to visualize the code to make this happen - further fogged up because my sense is that the answer is very basic. Any direction would be appreciated.

 

[PhoneyDeveloper]

sql = @"Select * from MyAirportTable WHERE AirportName = ?";

Just bind your airport four character id that you get from the textfield for the ?

 

[ghayenga]

sql = @"Select * from MyAirportTable WHERE AirportName = ?";

Just bind your airport four character id that you get from the textfield for the ?

And don't forget to put single quotes around it.

CODE]sql = @"Select * from MyAirportTable WHERE AirportName = '?'";[/CODE]

 

[PhoneyDeveloper]

And don't forget to put single quotes around it.

No, that's not needed. That's why I said to bind it. Don't use a string replace, which can have bad side effects. You bind it.

 

[hervey]

Thank you for the redirection to Bind. It will be a bit before I get it, but it appears to be an elegant solution and I see I can use it on other elements of the program. Thanks!

 

[hervey]

Bindings is an elegant solution, but according to the apple docs - not for the iPhone. I failed to state the project is for the iPhone; this newbie is back to square one.

 

[PhoneyDeveloper]

You're mistaken. These all work

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite_int64);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);

Also, if you are letting users type in text and using that in your query you MUST use binding. For important databases not using binding is the basis of SQL injection attacks. For the rest of us it will be the source of bugs. If the user types one quote or double quote, let alone some SQL code, into the text field your query is going to fail, leading to crashes, and worse, bad reviews.

BTW, I recommend that you use FMDB. It will make your life easier.

 

[hervey]

Yes, you're correct, of course. I was confusing Cocoa bindings with sqlite bind. The following code is working for the search bar search and I was trying to connect to it with something like bindings from another view controller. I still don't have it, but I'm new and need to do more studying.

Thanks for your comments. I did check out FMDB. I'm new and unfamiliar with wrappers like this so I think I'll work with the Michael Owens book a little longer. I am open to all suggestions.

Cheers

	...
	
char *cQuery = " SELECT City,Ident,Latitude,Longitude FROM testAER3 WHERE Ident LIKE ?";

if (sqlite3_prepare(database,cQuery, -1, &statement, NULL) != SQLITE_OK) }
  
const char *cSearchText = [searchText StringUsingEncoding:NSUTF8StringEncoding];

sqlite3_bind_text(statement, 1, cSearchText, -1, SQLITE_TRANSIENT);

while (sqlite3_step(statement) == SQLITE_ROW) {

const char *cCityName = (const char*)sqlite3_column_text(statement,0);
			
NSString *cityName = [[NSString alloc] initWithUTF8String:cCityName];
            [cityName autorelease];
			
const char *cIdent = (const char *)sqlite3_column_text(statement,1);
			
NSString *ident = [[NSString alloc] initWithUTF8String:cIdent];
            [ident autorelease];
	...