Chapter 10: Deleting rows from a table

stevedm · September 22nd, 2010, 02:38 PM

Hi all,
I was successfully able to implement inserting rows and selecting rows. Update query worked well when wrote instead of select query.
BUt now having problems with DELETE query. I implemented in the same format for retrieving records, but with the delete query. But not working.
The code I wrote is the same as retrieving the records, but with the Delete query

NSString *qsql = @"Delete From Tablename Where S = 0";

Also even I tried NSString *qsql = @"Delete From Tablename";

But nothing worked . Can any one please tell me what will be the problem?

Thanks in advance!!!!
sdm

thepianoguy · September 23rd, 2010, 01:25 PM

The examples in the book are my first experience with sqlite3. This is my working implementation of the delete command

Code:

-(void)deleteRecordFromTableNamed:(NSString *)tableName
					   withField1:(NSString *)field1
					  field1Value:(NSString *)field1Value
						andField2:(NSString *)field2
					  field2Value:(NSString *)field2Value
					 
{
	NSString *sql = [NSString stringWithFormat:@"DELETE FROM '%@' WHERE %@ = '%@' AND %@ = '%@'",tableName,field1,field1Value,field2,field2Value];
	
	NSLog(@"The sql string is %@",sql);
	
	char *err;
	
	if (sqlite3_exec(db, [sql UTF8String],NULL, NULL, &err) != SQLITE_OK) {
		sqlite3_close(db);
		NSAssert(0, @"Error updating table");
	}
}

To test I simply added a delete call in -(void)viewDidLoad after the -(void)getAllRowsFromTable, and then re-listed the rows.

Note the absence of the single quotes around the tokens for the column names. It does not work if there are single quotes. When comparing the book examples to the tutorial webpage the author referred to, it seems that the book goes single quote crazy. Every token is written as '%@'. The preceding works with the book code. I also redid the example removing all single quotes from all tokens in every method except those representing values. This syntax was consistent with the reference page, and the program ran correctly. I don't know why the insert and replace works with both the single quotes present and absent, but the delete only worked when the single quotes were not present.

stevedm · September 23rd, 2010, 05:40 PM

Thanks thepianoguy..
I changed my code to the one you gave me and its works fine, but doesnt deletes the row in the sqlite3 database physically. After executing the delete query, if I try to access the deleted row from the code, it shows that the data is not found, which is good. But when I open the db, the record is still there. Why is it like that?
stevedm.

thepianoguy · September 23rd, 2010, 11:14 PM

Think of the text file in the same manner as your hard drive. Data deleted from the hard drive is not "gone" unless deleted securely, it still exists but the path to locate it is gone. Over time other data will be written over it and it will be unrecoverable. Data deleted in the .sql file is marked as deleted, so the space can be reused, and in time will be overwritten, but it still exists in the file. The fact that it is still present, though over time in a more and more fragmented fashion is what allows recovery tools to work. To eliminate the records entirely you could rebuild the directory using the following in the
-(void)viewDidLoad method after
[self getAllRowsFromTableNamed:…];

Code:

char *err;

	if (sqlite3_exec(db, "VACUUM;", NULL, NULL, &err) != SQLITE_OK) {
		sqlite3_close(db);
		NSAssert(0, @"Error updating table");
	}
	else
	sqlite3_close(db);
	    [super viewDidLoad];

The file will have all deleted records wiped clean.

stevedm · September 24th, 2010, 03:00 AM

Thanks for the reply. As the code you wrote will have all records wiped clean, If your could please tell me how to just delete/wipe few records from the database(I mean wipe the datas which satisfies the condition)
Example : Delete from tablename Where a = 1;
Here Not only I need to delete the rows with a = 1, but also want to wipe those rows with a = 1 from table. how can I do that?
Thanks a lot for helping me. Really appriciated!!!
stevem

thepianoguy · September 24th, 2010, 01:37 PM

Not sure if I follow the question. You want some deleted data to be wiped, and some deleted data to not be wiped?

stevedm · September 24th, 2010, 01:52 PM

Thanks for your reply.
I just want all deleted data to be wiped. Suppose I have 10 records and I deleted 5 records. I want to wipe just those 5 records deleted.
Thanks and really appriciated.
stevedm.

thepianoguy · September 24th, 2010, 01:56 PM

The code that I provided removes the deleted records. The other records are intact.

stevedm · September 27th, 2010, 02:21 PM

Thanks you very much thepianoguy. The code works. Thanks for helping me, really really appreciated!!!!!!.