Hi All
I have the following simple table:
CREATE TABLE log
( Channel INT, Start_Time INT, End_Time INT, Data_Value BLOB);
And I have this delete statement:
DELETE FROM log WHERE Start_Time <= ?;
I prepare this statement when I open the database, along with a bunch of
others. I keep them in a structure (called log_db in the code below) along
with the database pointer. I finalize all the statements just before I
close the DB.
When I want to do a delete, I reset the statement, then I bind a large
uint64 (number of centi-seconds since the start of UNIX time) to the
parameter and step the statement.
void delete_old_logs(log_database_t *log_db, uint64_t cutoff_time)
{
reset_statement(log_db->database, log_db->delete_old_stmt,
"delete_old_stmt");
bind_int64(log_db->database, log_db->delete_old_stmt, 1, cutoff_time,
"delete_old_stmt", "cutoff_time");
step_statement(log_db->database, log_db->delete_old_stmt,
"delete_old_stmt");
reset_statement(log_db->database, log_db->delete_old_stmt,
"delete_old_stmt");
}
I use a few simple wrapper functions to avoid all the inline error checking.
During development I exit the application on any error - I do not see any of
my error fprintf's when I try to delete rows.
void reset_statement(sqlite3 *db, sqlite3_stmt *stmt, const char *stmt_name)
{
int rc = sqlite3_reset(stmt);
if ( rc != SQLITE_OK ) {
LogEvent(ERROR, "Cannot reset statement %s. %s", stmt_name,
sqlite3_errmsg(db));
fprintf(stderr, "%s %d: Cannot reset statement %s. %s\n", __FILE__,
__LINE__, stmt_name, sqlite3_errmsg(db));
//exit(rc);
}
}
int step_statement(sqlite3 *db, sqlite3_stmt *stmt, const char *stmt_name)
{
int rc = sqlite3_step(stmt);
if ( rc == SQLITE_ERROR ) {
LogEvent(ERROR, "Cannot step statement %s. %s", stmt_name,
sqlite3_errmsg(db));
fprintf(stderr, "%s %d: Cannot step statement %s. %s\n", __FILE__,
__LINE__, stmt_name, sqlite3_errmsg(db));
//exit(rc);
}
return rc;
}
void bind_int64(sqlite3 *db, sqlite3_stmt *stmt, int index, uint64_t data,
const char *stmt_name, const char *data_name)
{
int rc = sqlite3_bind_int64(stmt, index, data);
if ( rc != SQLITE_OK ) {
LogEvent(ERROR, "Cannot bind %s to %s. %s", data_name, stmt_name,
sqlite3_errmsg(db));
fprintf(stderr, "%s %d: Cannot bind %s to %s. %s\n", __FILE__,
__LINE__, data_name, stmt_name, sqlite3_errmsg(db));
//exit(rc);
}
}
I have another statement that deletes all rows
DELETE FROM log;
I use it exactly the same way, except I do not bind a parameter. Delete all
works fine. Delete old doesn't do anything! I have been using SQL for >20
years and these are pretty simple statements. Does parameter binding just
not work with DELETE?
Any insight would be greatly appreciated.
Thanks
John
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
