欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQLite3的绑定函数族使用与其注意事项详解

程序员文章站 2024-01-02 13:25:46
前言本文给大家展示的代码实际上就是如何利用sqlite3的参数化机制做数据插入,也可以update操作,就看你怎么玩了,这里只列出代码,然后说一些注意事项。下面的代码,有一个问题,插入后的东西一定是:...

前言

本文给大家展示的代码实际上就是如何利用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);

总结

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

上一篇:

下一篇: