学步园

下面展示的代码实际上就是如何利用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);