CODEWITHSUNDEEP
iphoneobjective-cdatabasexcodesqlite
rollingcodes
rollingcodes

Reputation: 16062

iPhone SQLite Database Reading And Writing

So I am trying to work with SQLite in one of iPhone applications and I am using the sqlite3 library. I am able to access the database and even make a query; in fact the query accesses the exact data but for some reason the string I am getting back is a long integer and not the string I was looking for. Here is the database and code:

Filename: Package.sql
Table Lessons
LessonID VARCHAR(64) Primary Key | LessonName VARCHAR(100) | EntryDate (DATETIME) | Chrono VARCHAR (20) 
bfow02nso9xjdo40wksbfkekakoe29ak | Learning The History    | 2010-08-05 16:24:35  | 0001

And the iPhone Code

...
-(NSString *)getRow:(NSString *)tablename where:(NSString *)column equals:(NSString *)value {

  const char *query = [[[[[[[@"SELECT * FROM `" stringByAppendingString:tablename] stringByAppendingString:@"` WHERE `"] stringByAppendingString:column] stringByAppendingString:@"` = '"] stringByAppendingString:value] stringByAppendingString:@"';"] cStringUsingEncoding:NSUTF8StringEncoding];

  NSString *result;

  if(sqlite3_open([dbpath UTF8String], &database) == SQLITE_OK) {

    sqlite3_stmt *compiledQuery;

    if(sqlite3_prepare_v2(database, query, -1, &compiledQuery, NULL) == SQLITE_OK) {

      while(sqlite3_step(compiledQuery) == SQLITE_ROW) {

        NSString *str_temp = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledQuery, 2)];

        result = str_temp;
        
      }

      sqlite3_finalize(compiledQuery);

    }

    sqlite3_close(database);

  }
 
  return result;

}
...

When the code executes:

  CDatabase *db = [[CDatabase alloc]initWithDatabase:@"Package.sql"];
  NSString *result = [db getRow:@"Lessons" where:@"Chrono" equals:@"0001"];

the returned value NSString *result has a value of "1,364,111". Why is it doing that??? It should be "Learning The History"

Upvotes: 1

Views: 3306

Answers (2)

rollingcodes
rollingcodes

Reputation: 16062

Haha whoops I realized that I was just displaying the string as a data format by using the %d string format. when i changed it to %@ i got the string format

Upvotes: 0

Jesse Beder
Jesse Beder

Reputation: 34054

Are you sure that any of your SQLite calls are successful? You should initialize result to nil so that your function returns nil if any errors are caught.

Three (probably related) issues with your code:

  1. The index to sqlite3_column_text should be zero-based; you're passing 2, which should refer to the third column. You probably mean to pass 1. From the docs:

    ...the second argument is the index of the column for which information should be returned. The leftmost column of the result set has the index 0.

  2. You really shouldn't use SELECT *. Specify the columns you want!

  3. You should specialize your query by binding values, not by concatenating strings! Your code is rife with the possibility of SQL injections (not to mention incorrect queries).

For example (with no error checking):

const char *query = "SELECT * FROM ? WHERE ?=?";
sqlite3_stmt *compiledQuery;
sqlite3_prepare_v2(database, query, -1, &compiledQuery, NULL);
sqlite3_bind_text(compiledQuery, 1, "Lessons", -1, SQLITE_TRANSIENT);
sqlite3_bind_text(compiledQuery, 2, "Chrono", -1, SQLITE_TRANSIENT);
sqlite3_bind_text(compiledQuery, 3, "0001", -1, SQLITE_TRANSIENT);

Note that the index here is 1-based (I don't know why they do that). From the docs:

The second argument is the index of the SQL parameter to be set. The leftmost SQL parameter has an index of 1.

Upvotes: 2

Related Questions