sqlite应用

最新推荐文章于 2025-08-09 06:16:05 发布

原创最新推荐文章于 2025-08-09 06:16:05 发布 · 292 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlite

数据库专栏收录该内容

3 篇文章

订阅专栏

1、查询是否存在某值；

int doTest(StNvrGroupInfo *pUpdateGroupInfo)
{
    if(! pUpdateGroupInfo)
    {
        return FIRMWARE_ERR(_ErrUserInvArgs);
    }
    YA_SI32 s32Ret = 0;

    sqlite3 * conn = NULL;
    YA_C8 s8Sql[320] = "\0";
    sqlite3_stmt *stmt4 = NULL;

    if(! strcmp("admin", pUpdateGroupInfo->c8OldGroupName) || !strcmp("admin", pUpdateGroupInfo->c8NewGroupName))
    {
        return FIRMWARE_ERR(_ErrDefaultGroup);
    }

     // 打开数据库, 创建连接
     if(sqlite3_open_v2(DATABASEPATH, &conn, SQLITE_OPEN_READONLY, NULL) != SQLITE_OK)
     {
#ifdef USER_DEBUG
         printf("Open database failed！\n");
#endif
         s32Ret = FIRMWARE_ERR(_ErrNotOpenDB);
         goto ERR01;
     }

     //组是否有用户
      sprintf(s8Sql, "SELECT * FROM UGnameSharePer WHERE Gname =  '%s'", pUpdateGroupInfo->c8OldGroupName);
      //printf("%s\n", s8Sql);
      if(SQLITE_OK != sqlite3_prepare_v2(conn, s8Sql, strlen(s8Sql), &stmt4, NULL))
      {
    #ifdef USER_DEBUG
          printf("sqlite3_prepare_v2 failed！\n");
    #endif
          if(stmt4)
          {
              sqlite3_finalize(stmt4);
          }
          s32Ret = FIRMWARE_ERR(_ErrNoGroup);
          goto ERR01;
      }
      int i =sqlite3_step(stmt4);
      if(i != SQLITE_ROW)//如果不等于SQLITE_ROW，表示搜索结果为空；
      {
    #ifdef USER_DEBUG
          printf("sqlite3_step failed！\n");
    #endif
          if(stmt4)
          {
              sqlite3_finalize(stmt4);
          }
          s32Ret = FIRMWARE_ERR(_ErrNoGroup);
          goto ERR01;
      }

      sqlite3_finalize(stmt4);
      ERR01:
      //关闭数据库
            if(conn)
      {
          sqlite3_close(conn);
      }

      return s32Ret;
}

2、遍历查询每列的数据；

StCmdGetAllGrpUsrEcho *UserAccountInfoCreat(YA_SI32 *pErrCode, YA_SI32 *pUNum)
{
    if(! pUNum)
    {
        *pErrCode = FIRMWARE_ERR(_ErrUserInvArgs);
        return NULL;
    }
    *pErrCode = 0;
    sqlite3 * conn = NULL;
    sqlite3_stmt *stmt1 = NULL;
    YA_C8 s8Sql[320] = "\0";
    //YA_SI32 s32ColCount;
    YA_SI32 s32GUserNum = 0;
    YA_SI32 i;
    YA_SI32 s32EachMemNum = 10;
    YA_SI32 s32MaxMemBlock = 1;

    StCmdGetAllGrpUsrEcho *pUGInfoHeader = (StCmdGetAllGrpUsrEcho*)malloc(sizeof(StCmdGetAllGrpUsrEcho) *\
            s32EachMemNum * s32MaxMemBlock);
    if(NULL == pUGInfoHeader)
    {
        *pErrCode = FIRMWARE_ERR(_ErrGETACTNOM);
        return NULL;
    }

     // 打开数据库, 创建连接
     if(sqlite3_open_v2(DATABASEPATH, &conn, SQLITE_OPEN_READONLY, NULL) != SQLITE_OK)
     {
#ifdef USER_DEBUG
         printf("Open database failed！\n");
#endif
         *pErrCode = FIRMWARE_ERR(_ErrNotOpenDB);
         goto ERR01;
     }
     sprintf(s8Sql, "select * from GUInfo");
     printf("%s\n", s8Sql);

     if(SQLITE_OK != sqlite3_prepare_v2(conn, s8Sql, strlen(s8Sql), &stmt1, NULL))
     {
     #ifdef USER_DEBUG
       printf("sqlite3_prepare_v2 failed！\n");
     #endif
       if(stmt1)
       {
           sqlite3_finalize(stmt1);
       }
       *pErrCode = FIRMWARE_ERR(_ErrGETACCOUNT);
       goto ERR01;
     }

     //s32ColCount = sqlite3_column_count(stmt1);
     //3. Step rowset
     while(sqlite3_step(stmt1) == SQLITE_ROW)
     {
         //4. Get column value
         //向数组中赋值；
         i = 0;
         (pUGInfoHeader + s32GUserNum)->s32ErrCode = 0;
         //printf("%5s\n", sqlite3_column_text(stmt1, i));
         i++;
         memcpy((pUGInfoHeader + s32GUserNum)->c8GroupName, sqlite3_column_text(stmt1, i), GROUP_NAME_SIZE);
         //printf("%5s\n", sqlite3_column_text(stmt1, i));
         memcpy((pUGInfoHeader + s32GUserNum)->c8UserName, sqlite3_column_text(stmt1, i), USER_NAME_SIZE);
         s32EachMemNum--;
         i = 0;

         s32GUserNum++;
         if(0 == s32EachMemNum)
         {
             s32MaxMemBlock++;
             s32EachMemNum = 10;
             pUGInfoHeader = (StCmdGetAllGrpUsrEcho*)realloc(pUGInfoHeader, sizeof(StCmdGetAllGrpUsrEcho) \
                     * s32EachMemNum * s32MaxMemBlock);
             if(NULL == pUGInfoHeader)
             {
                 *pErrCode = FIRMWARE_ERR(_ErrGETACTNOM);
                 sqlite3_finalize(stmt1);
                 goto ERR01;
             }
         }
     }

     //5. Finalize statement.
     sqlite3_finalize(stmt1);
     *pUNum = s32GUserNum;

ERR01:
      //关闭数据库
      if(conn)
      {
          sqlite3_close(conn);
      }
      if(*pErrCode)
      {
          free(pUGInfoHeader);
      }
     return pUGInfoHeader;
}