SQLite3的綁定函數族使用與其注意事項詳解，sqlite3綁定

SQLite3的綁定函數族使用與其注意事項詳解，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 workPRAGMA foreign_keys=OFF;BEGIN TRANSACTION;CREATE TABLE work (baodanhao text unique primary key , chudanriqi text,qudao text,lianxiren text,xiaoshou text,beibaorenxingming text,chufariqi text,baoxianpinpai text,baoxianjihua text,baoxianjine real,yongjinbilv real,jingbaofei real,huankuanfangshi text,haikuanjine real,huankuanriqi text,shifouquane text,lirunbilv real,lirunjine real,huoliriqi text,fapiaojisong text,shifubaoxiangongsi text,beizhu text);

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的協助，如果有疑問大家可以留言交流，謝謝大家對幫客之家的支援。