Detailed description of SQLite3 binding function family usage and precautions; sqlite3 binding

Detailed description of SQLite3 binding function family usage and precautions; sqlite3 binding

Preface

The code displayed in this article is actually how to use the parameterization mechanism of Sqlite3 to insert data or update the data. It depends on how you play it. Here, only the code is listed, then let's talk about some precautions.

The following code has a problem:

Insert into "work" VALUES ('haid', 'haidr, haidr, haidr, and haidr, NULL, 'haidr, haidr, haidr, NULL, NULL, 110.0, 1.0, 108.9, NULL, NULL, 'hagha, hagha, and hagha ', NULL );

Check the problematic code:

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 ;} // Date related if (count = ID_CHUDANRIQI | count = ID_CHUFARIQI | count = Hangzhou | 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 {// money-related processing real-type if (cou Nt = ID_BAOXIANJINE | count = ID_YONGJINBILV | count = California | 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 = sq Lite3_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 );

Why?

Because sqlite3_bind_text is bound to the text, you need to do the following:

rc = sqlite3_step(stmt); 

While the above Code uses temporary variables,rc = sqlite3_step(stmt);And does not exist. Therefore, garbled characters are normal.

Modify as follows:

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 ;} // Date related if (count = ID_CHUDANRIQI | count = ID_CHUFARIQI | count = Hangzhou | 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 {// Money-related processing real type if (count = ID_BAOXIANJINE | count = ID_YONGJINBILV | count = numeric | 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 );

Attach the SQL syntax created by the database:

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

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.