sqlite循环插入时使用stmt需要reset，否则会插入出错

sqlite正常的插入操作，在用到stmt时，如果要循环使用stmt，必须reset()

如果不reset，直播再bind 后step，则会出现未知状况，有可能某些情况下也是OK的，存储的数据也是正确的，但有些情况下数据是错乱的，比如第2行与第一行数据相同，或完全为空数据。

示例代码见下，在一个for循环中，bind后step， 之后必须reset()，否则，会数据保存出错。（血泪啊）

 

std::string sql = "REPLACE INTO task_set values(?, ?, ?, ?, datetime(CURRENT_TIMESTAMP,'localtime'));";
	Mystmt stmt = DBmgr::getinstance()->getstmt(sql.c_str());
	if (stmt.prepare())
	{
		printf("save_task_to_db prepare error: %s\n", stmt.geterrmsg().c_str());
		return false;
	}

	for (auto item : m_nodemgr->getNodeList())
	{
		auto ninfo = item.second;

		auto nidStr = ninfo->nodeid.id.to_hex();
		string ipStr = ipntoa(ninfo->ip);
		string portStr = to_string(ntohs(ninfo->port));
        string hashStr = hash.to_hex()

		stmt.bindtext(1, nidStr.c_str(), nidStr.size());
		stmt.bindtext(2, ipStr.c_str(), ipStr.size());
		stmt.bindtext(3, portStr.c_str(), portStr.size());
		stmt.bindtext(4, hashStr.c_str(), hashStr.size());

		if (0 != stmt.step())
			myprintf("save_task_to_db() execute error: %s", stmt.geterrmsg().c_str());
		
		stmt.reset();//重复使用stmt必须reset
	}

 

在sqlite的C语言API文档中，有如下说明： https://www.sqlite.org/cintro.html 

Binding Parameters and Reusing Prepared Statements

In prior discussion, it was assumed that each SQL statement is prepared once, evaluated, then destroyed. However, SQLite allows the same prepared statement to be evaluated multiple times. This is accomplished using the following routines:

 

• sqlite3_reset()
• sqlite3_bind()

After a prepared statement has been evaluated by one or more calls to sqlite3_step(), it can be reset in order to be evaluated again by a call to sqlite3_reset(). Think of sqlite3_reset() as rewinding the prepared statement program back to the beginning. Using sqlite3_reset() on an existing prepared statement rather than creating a new prepared statement avoids unnecessary calls to sqlite3_prepare(). For many SQL statements, the time needed to run sqlite3_prepare() equals or exceeds the time needed by sqlite3_step(). So avoiding calls to sqlite3_prepare() can give a significant performance improvement.

It is not commonly useful to evaluate the exact same SQL statement more than once. More often, one wants to evaluate similar statements. For example, you might want to evaluate an INSERT statement multiple times with different values. Or you might want to evaluate the same query multiple times using a different key in the WHERE clause. To accommodate this, SQLite allows SQL statements to contain parameters which are "bound" to values prior to being evaluated. These values can later be changed and the same prepared statement can be evaluated a second time using the new values.

 

这一点是与mysql是非常不同的，mysql的stmt不需要进行reset，可以直接进行bind再step()。