前言
本文給大家展示的代碼實際上就是如何利用Sqlite3的參數化機制做數據插入，也可以update操作，就看你怎么玩了，這里只列出代碼，然后說一些注意事項。
下面的代碼，有一個問題，插入后的東西一定是：
|
1
|
INSERT INTO "work" VALUES('鉿','鉿鉿鉿鉿鉿',NULL,NULL,NULL,NULL,'鉿鉿鉿鉿鉿',NULL,NULL,110.0,1.0,108.9,NULL,NULL,'鉿鉿鉿鉿鉿',NULL,NULL,NULL,'鉿鉿鉿鉿鉿',NULL,NULL,NULL); |
看看有問題的代碼：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
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，需要在做：
|
1
|
rc = sqlite3_step(stmt); |
的時候統一提交，而上面的代碼使用的臨時變量，rc = sqlite3_step(stmt);的時候，早就不存在了。因此亂碼也是正常的。
修改如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
|
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語法：
|
1
2
3
4
5
6
|
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); |
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對服務器之家的支持。
原文鏈接：http://www.doocr.com/articles/58a875ac8d4f1f3990989f38
