macOS SQLITE3 problem - SQLITE_ROW

[sundark]

I am using a SQLITE database through sqlite3 interface (not CoreData). It retrievs data from a table with 2 binding variables. It works fine until some kind of run time event makes it stop working. It returns no rows. This was due to the fact that return value of 101 which corresponds to SQLITE-DONE instead of SQLITE_ROW. It was returning 100 all along and suddenly returns 101. No changes were made to either the database table or the application. I was able to retrieve the records using SQLite Manager add-in for Firefox browser.
Last time it happened I dropped the table and recreated it with data. It mysteriously started working. I did samething this time with no success.

Any insights ?
Thx
Sundar

 

[idelovski]

It retrievs data from a table with 2 binding variables.

So, your sql looks something like "SELECT * FROM a_table WHERE something1 = ? AND something2 = ?" and you're sending it to sqlite3_prepare_v2() function, then you call sqlite3_bind_xxx() twice for those two questionmarks, then you loop sqlite3_step() as long as it returns SQLITE_ROW and then you call sqlite3_finalize().

Or, can you post some code?

 

[sundark]

Thanks for your reply.
Here is the code:

- (void)viewDidLoad {
    [super viewDidLoad];
 UIBarButtonItem *rightButtonItem = [[UIBarButtonItem alloc] initWithTitle:@"Search" style:UIBarButtonItemStyleBordered target:self action:@selector(searchBtnClicked:)]; 

 self.navigationItem.rightBarButtonItem = rightButtonItem; 

	NSString *sqlStmt = [NSString stringWithFormat:@"SELECT Cat1, Cat2, Description FROM Phrases WHERE Phrases.Cat1 = ? AND Phrases.Cat2 = ? order by Description asc;"];
		
 self.allPhrases = [[Sqlite3DB database] getPhrases:(NSString *)sqlStmt forCat1:(NSString *)cate1 forCat2:(NSString *)cate2] ;

 self.title = cate2;
	[cate1 release];
	[cate2 release];
	[rightButtonItem release];
 
 }

I have a class which performs database related activities like retrieving data from tables to populate several table views. One of them is getPhrases and here is the code:

-(NSMutableArray *)getPhrases:(NSString *)sqlStmt forCat1:(NSString *)cate1 forCat2:(NSString *)cate2 		
		
	if (!_database) return itemsL3; // earlier problems
	
	// build select statement
	selStmtPhrases1 = nil; // no compiled statement yet
	if (!selStmtPhrases1)
	{
		if (sqlite3_prepare_v2(_database, [sqlStmt UTF8String], -1, &selStmtPhrases1, NULL) != SQLITE_OK)
		{
			selStmtPhrases1 = nil;
		}
		sqlite3_bind_text(selStmtPhrases1, 2, [cate2 UTF8String], -1, SQLITE_TRANSIENT); 
		sqlite3_bind_text(selStmtPhrases1, 1, [cate1 UTF8String], -1, SQLITE_TRANSIENT); 
	}

	if (!selStmtPhrases1)
	{
		NSAssert1(0, @"Can't build SQL to read items [%s]", sqlite3_errmsg(_database));
	}
	
	// loop reading items from list
	[itemsL3 removeAllObjects]; // clear list for rebuild
	int ret;
	while ((ret=sqlite3_step(selStmtPhrases1))==SQLITE_ROW) 
	{  
		char *s1 = (char *)sqlite3_column_text(selStmtPhrases1, 0);
		NSString *str1 = [NSString stringWithUTF8String:(char *)s1];
		char *s2 = (char *)sqlite3_column_text(selStmtPhrases1, 1);
		NSString *str2 = [NSString stringWithUTF8String:(char *)s2];
		char *s3 = (char *)sqlite3_column_text(selStmtPhrases1, 2);
		NSString *str3 = [NSString stringWithUTF8String:(char *)s3];
		Phrases *phrases = [[Phrases alloc] initWithcat1:(NSString *)str1
											cat2: (NSString *)str2
									description: (NSString *)str3];

		
		[itemsL3 addObject:phrases]; // add to list
		[phrases release]; // free item
	}
	sqlite3_reset(selStmtPhrases1); // reset (unbind) statement
	sqlite3_finalize(selStmtPhrases1); // release memory
	return itemsL3;
}

In the above code, the problem is when it's retrieving the row, i.e.,
while ((ret=sqlite3_step(selStmtPhrases1))==SQLITE_ROW)
the Ret value is 101(SQLITE_DONE) instead of 100 (SQLITE_ROW).

Also I attached the output of the SELECT statement which works in the SQL Manager. It retrieved the data.

In fact, I changed the SELECT statement to a simple statement without any binding variables - still it didn't work.

My gut feeling is that I am not re-initializing something that puts me in the end of table returning SQLITE-DONE.

Hope this helps

Thanks
Sundar

 

[idelovski]

The only thing that looks suspicious are those two releases in viewDidLoad:

	[cate1 release];
	[cate2 release];

Are they instance variables? I see you use them for bindings with the query. And you probably initialize the view controller with them? Something like:

- (id)initWithCate1:(NSString *)c1 andCate2:(NSString *)c2
{
   if (self = [super init])  {
      cate1 = c1;
      [cate1 retain];  // or just self.cate1 = c1;
      cate2 = c2;
      [cate2 retain];  // or just self.cate2 = c2;
   }

   return (self);
}

 

[sundark]

SQLITE# Problem

Thanks again for the help.

Both Cate1 and Cate2 are passed as arguments - I am directly using them in the binding statements - I initially had something similar to suggested by you ,assigning it to instance variables such as Self.Cate1 & Self.Cate2, and used them in the code. Then I thought I don't need instance variables and instead directly use the argument names in Function definition. I got rid of the instance variables and forgot to get rid of the release statements. That may be the problem.

Is it always a good practice to define instance variables assign arguments to them before start using in the code.

However, last night, I recreated the database from scratch and it worked. I am sure that the issue will happen again.

But I feel that you may have given me the answer. I will go back to using the instance variables.

Thanks
Sundar