正确使用SQLite的reset API

SQLite数据库的reset接口的含义是,其复位SQLite的statement对象到初始状态,并且可以再次被SQLite的字节码虚拟机执行.reset接口不会改变statement对象绑定的值,复位接口可以在step接口之前调用,也可以在其之后调用, 复位接口是重新执行虚拟机,因此多数场景下,是在step接口之后调用,并且, 复位接口通常和绑定接口(bind)配合使用来插入新行.下面通过两个场景来说明reset接口的使用.

场景1:

static int insert_with_reset1(sqlite3 *db) {
	int rc = -1;
	sqlite3_stmt *pStmt = NULL;
	rc = sqlite3_prepare_v2(db, "insert into tl  VALUES (?1, ?2);", -1, &pStmt, 0);
	if(rc != SQLITE_OK ) {
	    goto out;
	}

	rc = sqlite3_bind_text(pStmt, 1, "Colunm 1 text value.", -1, SQLITE_STATIC);
	if(rc != SQLITE_OK) {
		goto out;
	}

	rc = sqlite3_bind_int(pStmt, 2, 23);
	if(rc != SQLITE_OK) {
		goto out;
	}

	rc = sqlite3_step(pStmt);
	assert(rc == SQLITE_DONE);

	// Reset the vdbe and insert the same row.
	rc = sqlite3_reset(pStmt);
	if(rc != SQLITE_OK) {
		goto out;
	}

	rc = sqlite3_step(pStmt);
	assert(rc == SQLITE_DONE);
	
out:
	sqlite3_finalize(pStmt);
	return rc;
}


static void reset_test1() {
	sqlite3 *db = 0;
	char *zErrMsg = 0;

	// Open database.
	int rc = sqlite3_open("mysqlite.db", &db);
	if(rc != SQLITE_OK) {
		goto out;
	}

	// Create table.
	char *create_table_cmd = "CREATE TABLE tl (info TEXT, line INT);";
	rc = sqlite3_exec(db, create_table_cmd, exec_callback, 0, &zErrMsg);
	if(rc != SQLITE_OK) {
		goto out;
	}

	// Insert.
	rc = insert_with_reset1(db);
	if(rc != SQLITE_OK) {
		goto out;
	}

	//Query
	char *query_cmd = "select * from tl;";
	rc = sqlite3_exec(db, query_cmd, exec_callback, 0, &zErrMsg);
	if(rc != SQLITE_OK) {
		goto out;
	}

	out:
	if (rc != SQLITE_OK) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
	}
	sqlite3_free(zErrMsg);
	sqlite3_close(db);
}

在函数insert_with_reset1中, 第一次执行完step接口之后, 进行了复位,然后又执行了step接口,因此执行的statement对象绑定的是相同的值, 插入了完全相同的行数据, 当然,这里是行的内容完全相同, 其ROW ID是SQLite内部管理的,会自动更新.调用SQLite的查询接口可以验证这个结果,其 回调函数会返回2行,其内容完全一样.

场景2:

static int insert_with_reset2(sqlite3 *db) {
	int rc = -1;
	sqlite3_stmt *pStmt = NULL;
	rc = sqlite3_prepare_v2(db, "insert into tl  VALUES (?1, ?2);", -1, &pStmt, 0);
	if(rc != SQLITE_OK ) {
	    goto out;
	}

	rc = sqlite3_bind_text(pStmt, 1, "Colunm 1 text value.", -1, SQLITE_STATIC);
	if(rc != SQLITE_OK) {
		goto out;
	}

	rc = sqlite3_bind_int(pStmt, 2, 23);
	if(rc != SQLITE_OK) {
		goto out;
	}

	rc = sqlite3_step(pStmt);
	assert(rc == SQLITE_DONE);

	rc = sqlite3_reset(pStmt);
	if(rc != SQLITE_OK) {
		goto out;
	}

	rc = sqlite3_clear_bindings(pStmt);
	if( rc!=SQLITE_OK ){
		goto out;
	}

	rc = sqlite3_bind_text(pStmt, 1, "Colunm 1 text value after reset.", -1, SQLITE_STATIC);
	if(rc != SQLITE_OK) {
		goto out;
	}

	rc = sqlite3_bind_int(pStmt, 2, 24);
	if(rc != SQLITE_OK) {
		goto out;
	}

	rc = sqlite3_step(pStmt);
	assert(rc == SQLITE_DONE);

out:
	sqlite3_finalize(pStmt);
	return rc;
}

static void reset_test2() {
	sqlite3 *db = 0;
	char *zErrMsg = 0;

	// Open database.
	int rc = sqlite3_open("mysqlite.db", &db);
	if(rc != SQLITE_OK) {
		goto out;
	}
	
	// Create table.
	char *create_table_cmd = "CREATE TABLE tl (info TEXT, line INT);";
	rc = sqlite3_exec(db, create_table_cmd, exec_callback, 0, &zErrMsg);
	if(rc != SQLITE_OK) {
		goto out;
	}

	// Insert.
	insert_with_reset2(db);

	//Query
	char *query_cmd = "select * from tl;";
	rc = sqlite3_exec(db, query_cmd, exec_callback, 0, &zErrMsg);
	if(rc != SQLITE_OK) {
		goto out;
	}

	out:
	if (rc != SQLITE_OK) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
	}
	sqlite3_free(zErrMsg);
	sqlite3_close(db);
}

函数insert_with_reset2执行完第一次的step接口之后,进行了复位,并清除了statement对象绑定的值,重新绑定了新的值,然后再次执行step接口.即插入了不同内容的行数据.调用SQLite的查询接口可以验证这个结果,其 回调函数会返回2行,其内容是不同的.

SQLite的prepare接口是一个重量级的操作,通过执行reset和重新bind操作,可以提升执行效率,满足不同场景的使用需求.