SQLite3的绑定函数族使用与其注意事项详解
程序员文章站
2024-01-02 13:25:46
前言本文给大家展示的代码实际上就是如何利用sqlite3的参数化机制做数据插入,也可以update操作,就看你怎么玩了,这里只列出代码,然后说一些注意事项。下面的代码,有一个问题,插入后的东西一定是:...
前言
本文给大家展示的代码实际上就是如何利用sqlite3的参数化机制做数据插入,也可以update操作,就看你怎么玩了,这里只列出代码,然后说一些注意事项。
下面的代码,有一个问题,插入后的东西一定是:
insert into "work" values('铪','铪铪铪铪铪',null,null,null,null,'铪铪铪铪铪',null,null,110.0,1.0,108.9,null,null,'铪铪铪铪铪',null,null,null,'铪铪铪铪铪',null,null,null);
看看有问题的代码:
sqlite3_stmt *stmt; cstring sql = "insert into work values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; int rc = sqlite3_prepare_v2(db, sql.getstring(), -1, &stmt, null); if(rc != sqlite_ok) { messagebox("sqlite3_prepare_v2 failed!"); return; } count = 0; p_wnd = prevwnd; while(count++ < id_totalcount) { cstring dbstr; p_wnd = cwnd::getnextdlgtabitem(p_wnd, false); if(p_wnd == null) { return; } p_wnd->getwindowtext(dbstr); do { if(!dbstr.getlength()) { rc = sqlite3_bind_null(stmt, count); break; } //日期相关 if( count == id_chudanriqi || count == id_chufariqi || count == id_huankuanriqi || count == id_huoliriqi) { cdatetimectrl *timectl = (cdatetimectrl *)p_wnd; cstring time = datetimetostring(*timectl); rc = sqlite3_bind_text(stmt, count, time.getstring(), time.getlength(), sqlite_static); break; } else { //金钱相关的处理real类型 if( count == id_baoxianjine || count == id_yongjinbilv || count == id_jingbaofei || count == id_huankuanjine || count == id_lirunbilv || count == id_lirunjine) { double tmoney = 0.0; int rtn = sscanf_s(dbstr.getstring(), "%lf", &tmoney); assert(rtn == 1); rc = sqlite3_bind_double(stmt, count, tmoney); } else { char *str = (char *)dbstr.getstring(); int c = strlen(str); int c1 = dbstr.getlength(); rc = sqlite3_bind_text(stmt, count, dbstr.getstring(), -1/*dbstr.getlength()*/, sqlite_static); } } }while(0); if(rc != sqlite_ok) { cstring errstr = sqlite3_errstr(rc); messagebox(errstr); return; } } rc = sqlite3_step(stmt); if(rc != sqlite_done) { if(rc == sqlite_error) { cstring dberr; dberr.format("sql insert failed, %s", sqlite3_errmsg(db)); messagebox(dberr); } else { messagebox("sqlite3_step failed!"); } } sqlite3_finalize(stmt);
为什么呢?
因为,sqlite3_bind_text绑定的text,需要在做:
rc = sqlite3_step(stmt);
的时候统一提交,而上面的代码使用的临时变量,rc = sqlite3_step(stmt);
的时候,早就不存在了。因此乱码也是正常的。
修改如下:
sqlite3_stmt *stmt; cstring sql = "insert into work values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; int rc = sqlite3_prepare_v2(db, sql.getstring(), -1, &stmt, null); if(rc != sqlite_ok) { messagebox("sqlite3_prepare_v2 failed!"); return; } count = 0; p_wnd = prevwnd; cstring dbstr[id_totalcount + 1]; while(count++ < id_totalcount) { dbstr[count].empty(); p_wnd = cwnd::getnextdlgtabitem(p_wnd, false); if(p_wnd == null) { return; } p_wnd->getwindowtext(dbstr[count]); do { if(!dbstr[count].getlength()) { rc = sqlite3_bind_null(stmt, count); break; } //日期相关 if( count == id_chudanriqi || count == id_chufariqi || count == id_huankuanriqi || count == id_huoliriqi) { cdatetimectrl *timectl = (cdatetimectrl *)p_wnd; cstring time = datetimetostring(*timectl); dbstr[count] = time; rc = sqlite3_bind_text(stmt, count, time.getstring(), time.getlength(), sqlite_static); } else { //金钱相关的处理real类型 if( count == id_baoxianjine || count == id_yongjinbilv || count == id_jingbaofei || count == id_huankuanjine || count == id_lirunbilv || count == id_lirunjine) { double tmoney = 0.0; int rtn = sscanf_s(dbstr[count].getstring(), "%lf", &tmoney); assert(rtn == 1); rc = sqlite3_bind_double(stmt, count, tmoney); } else { rc = sqlite3_bind_text(stmt, count, dbstr[count].getstring(), dbstr[count].getlength(), sqlite_static); } } }while(0); if(rc != sqlite_ok) { cstring errstr = sqlite3_errstr(rc); messagebox(errstr); return; } } rc = sqlite3_step(stmt); if(rc != sqlite_done) { if(rc == sqlite_error) { cstring dberr; dberr.format("sql insert failed, %s", sqlite3_errmsg(db)); messagebox(dberr); } else { messagebox("sqlite3_step failed!"); } } sqlite3_finalize(stmt);
附上数据库创建的sql语法:
sqlite> .dump work pragma foreign_keys=off; begin transaction; create table work (baodanhao text unique primary key , chudanriqi text,qudao text,lianxiren text,xiaoshou text,beibaorenxingming text,chufar iqi text,baoxianpinpai text,baoxianjihua text,baoxianjine real,yongjinbilv real,jingbaofei real,huankuanfangshi text,haikuanjine real,huanku anriqi text,shifouquane text,lirunbilv real,lirunjine real,huoliriqi text,fapiaojisong text,shifubaoxiangongsi text,beizhu text);
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对的支持。