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);

    总结

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对lingkb的支持。