操作sqlite. 最好是用 prepare, 用sqlite3_exec做短SQL操作.
sqlite3_prepare_v2(db, "insert into db_user values (?,?,?)", -1, &stmt, 0);
sqlite3_bind_text(stmt, 1, info->username ? info->username : "",
info->username ? strlen(info->username) : 0, SQLITE_STATIC);
sqlite3_bind_text(stmt, 2, info->pwd ? info->pwd : "",
info->pwd ? strlen(info->pwd) : 0,
SQLITE_STATIC);
sqlite3_bind_text(stmt, 3, info->sn ? info->sn : "",
info->sn ? strlen(info->sn) : 0,
SQLITE_STATIC);
rc = sqlite3_step(stmt);
rc = sqlite3_reset(stmt);
if (SQLITE_OK == rc) {
rt = TRUE;
} else {
log_w("sqlite3_prepare(%d): %s\n", rt, sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);
sqlite3_close(db);
rt = sqlite3_prepare_v2(db, "select * from db_user where username=?", -1,
&stmt, 0);
if (SQLITE_OK == rt) {
rt = sqlite3_bind_text(stmt, 1, username, strlen(username),
SQLITE_STATIC);
if (rt == SQLITE_OK) {
rt = sqlite3_step(stmt);
if (rt == SQLITE_ROW) { //if all is while
ret = TRUE;
//debug info
for (i = 0; i < sqlite3_column_count(stmt); i++) {
log_d("%s=%s", sqlite3_column_name(stmt, i),
sqlite3_column_text(stmt, i));
}
rt = sqlite3_step(stmt);
} else if( rt != SQLITE_DONE){
goto dberr;
}
} else if( rt != SQLITE_DONE) {
goto dberr;
}
} else {
goto dberr;
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return ret;
dberr: //数据库出错须要一个系统的回调函数,用于判断是否是数据库文件损坏
log_w("sqlite3_prepare(%d): %s\n", rt, sqlite3_errmsg(db));
sqlite3_finalize(stmt);
sqlite3_close(db);
SQLITE_ROW 100 为查询到数据-sqlite3_step()
SQLITE_DONE 101 为未查询到数据-sqlite3_step()
SQLITE_OK 0 执行成功结果
sqlite3_column_type() 查看列表的类型;
//
int sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
); //SQLITE_OK
int sqlite3_prepare_v2(
sqlite3 *db, /* Database handle */
const char *zSql, /* SQL statement, UTF-8 encoded */
int nByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /* OUT: Pointer to unused portion of zSql */
); //SQLITE_OK
sqlite3_bind_text(… //SQLITE_OK
int sqlite3_step(sqlite3_stmt*); //SQLITE_ROW SQLITE_DONE
sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
int sqlite3_reset(sqlite3_stmt*);//SQLITE_OK
int sqlite3_finalize(sqlite3_stmt *pStmt);
sqlite3_close(sqlite3 *pDb)
sqlite_mprintf( .... ) 格式化字符串操作.%q:处理字符串中的\' %Q: 为字符串两边并处理字符串添加\'
sqlite事物:
BEGIN TRANSACTION; --开始
COMMIT TRANSACTION; --显示事务被提交,数据表中的数据也发生了变化。
ROLLBACK TRANSACTION; --显示事务被回滚,数据表中的数据没有发生变化。
SQL语句中的日期与时间:
datetime().......................产生日期和时间
date()...........................产生日期
time()...........................产生时间
strftime().......................对以上三个函数产生的日期和时间进行格式化
SELECT datetime(1377168853, 'unixepoch', 'localtime'); --从时间戳 2013-08-22 18:54:13
SELECT strftime('%s','now'); --到时间戳 1380728519