SQlite Behaves strange

[ricuff]

Hi guys
I am developing an app that uses sqlite to store info about hotels. The table in the sqlite db is very simple, id, name, address.

I tried this sql using firefox Sqlite manager

select hotels.name,hotels.address from hotels where hotels.name like '%%hotel%%'

and I get the results

if I use this code it gives me no results at all

-(NSMutableArray*)searchString
{
NSMutableArray *ar1=[[[NSMutableArray alloc]init]autorelease];
int count = 0;
sqlite3_stmt *selectstmt;NSString *query = [NSString stringWithFormat:@"select hotels.name,hotels.address from hotels where hotels.name like '%%hotel%%'"];


NSLog(@"query %@ ", query);
const char * sql = [query UTF8String];
if (sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK)
{
while(sqlite3_step(selectstmt) == SQLITE_ROW)
{
element *x=[[element alloc]init];
x.name = [NSString stringWithUTF8Stringchar*)sqlite3_column_text(se lectstmt, 1)];
x.address = [NSString stringWithUTF8Stringchar*)sqlite3_column_text(se lectstmt, 2)];
}
}
}

if I change the sql query in select * from hotels' it works pefectly
-(NSMutableArray*)searchString
{
NSMutableArray *ar1=[[[NSMutableArray alloc]init]autorelease];
int count = 0;
sqlite3_stmt *selectstmt;NSString *query = [NSString stringWithFormat:@"select * from hotels'"];


NSLog(@"query %@ ", query);
const char * sql = [query UTF8String];
if (sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK)
{
while(sqlite3_step(selectstmt) == SQLITE_ROW)
{
element *x=[[element alloc]init];
x.name = [NSString stringWithUTF8Stringchar*)sqlite3_column_text(se lectstmt, 1)];
x.address = [NSString stringWithUTF8Stringchar*)sqlite3_column_text(se lectstmt, 2)];
}
}
}


I wonder if there are some limitations on the query

thanks for your time

[lgehrig1]

First, when you are posting code, please include it inside CODE tags. Code smiley faces are not funny

Second, you do realize that the SQL "sqlite3_column*" statements count fields starting from zero? So you are asking it to place the address in the name field. You can verify this at Result Values From A Query

Last, you should be more specific when saying "No results at all". Did you breakpoint or NSLog inside the while loop and see that there were no rows returned? A simple log statement dumping each name would probably have showed that your loop was returning the proper number of rows.

[ricuff]

Sorry I forgot the CODE tags, I counted the rows after the query is executed and it returns 0 rows.

What makes me confused is that if I use the query

select * from hotels everything works perfectly

if I use

select hotels.name,hotels.address from hotels where hotels.name like '%%hotel%%'

it says no results even though I am sure there are . Thanks anyway

[jonusx]

I suggest you move to core data if you're using the database like that. I think you'll encounter less issues

[fouldsc]

Originally Posted by ricuff

Sorry I forgot the CODE tags, I counted the rows after the query is executed and it returns 0 rows.

What makes me confused is that if I use the query

select * from hotels everything works perfectly

if I use

select hotels.name,hotels.address from hotels where hotels.name like '%%hotel%%'

it says no results even though I am sure there are . Thanks anyway

'%%hotel%%' is the problem , only needs to be '%hotel%' if you totally wildcarding it.

I recommend : SQLite Database Browser

Start your app in simulator , load database in this and try your SQL commands direct on the database rather than using firefox.