我要评论前言
本文给大家展示的代码实际上就是如何利用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);
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对代码网的支持。
发表评论