sqlite3的绑定函数族使用，及其注意事项

下面展示的代码实际上就是如何利用Sqlite3的参数化机制做数据插入，也可以update操作，就看你怎么玩了，这里只列出代码，然后说一些注意事项。

下面的代码，有一个问题，插入后的东西一定是：

INSERT INTO "work" VALUES('铪','铪铪铪铪铪',NULL,110.0,1.0,108.9,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,tMoney);
                }
                else
                {
                    char *str = (char *)DbStr.GetString();
                    int c = strlen(str);
                    int c1 = DbStr.GetLength();

                    rc = sqlite3_bind_text(stmt,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 (?,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,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(),tMoney);
                }
                else
                {
                    rc = sqlite3_bind_text(stmt,DbStr[count].GetString(),DbStr[count].GetLength(),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);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!