SQLite3的绑定函数族使用与其注意事项详解

前言

本文给大家展示的代码实际上就是如何利用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);

总结

作者:多客博图原文地址:http://www.doocr.com/articles/58a875ac8d4f1f3990989f38

%s 个评论

要回复文章请先登录注册