20240523

#include <myhead.h>

int do_add(sqlite3 *db){
    int id;
    char name[1024] = {0};
    double score;
    printf("请输入id\n");
    scanf("%d",&id);
    printf("请输入姓名\n");
    scanf("%s",name);
    printf("请输入成绩\n");
    scanf("%lf",&score);

    char sql[1024] = {0};
    snprintf(sql,sizeof(sql),"insert into mylite values(%d,\"%s\",%lf);",id,name,score);
    printf("succeeded\n");
      char *errmsg = NULL;        //用于接收错误信息
    //执行sql语句
    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("Stu create error:%s\n", errmsg);
        //释放errmsg空间
        sqlite3_free(errmsg);
        return -1;
    }
    printf("插入成功\n");
    return 0;
}

int callback(void *arg, int cols, char **col_text, char ** col_name){
    printf("执行callback\n");
    if(*(int *)arg == 0) {
        for(int i = 0; i < cols; i++){
            printf("%s\t",col_name[i]);

            *(int *)arg = 1;
    }
    printf("\n");
    }
    
    for(int i = 0; i < cols; i++){
            printf("%s\t",col_text[i]);
           
    }
    return 0;
}


int do_search(sqlite3 *pd)
{
    int flag = 0;          //定义标识

    //准备sql语句
    char sql[128] = "select * from mylite;";

    //执行sql语句
    char *errmsg = NULL;        //用于接收错误信息
    //执行sql语句
    if(sqlite3_exec(pd, sql, callback, &flag, &errmsg) != SQLITE_OK)
    {
        printf("select error:%s\n", errmsg);
        //释放errmsg空间
        sqlite3_free(errmsg);
        return -1;
    }
    printf("查找结束\n");
    return 0;
}

int do_serch_1(sqlite3 *db){
    char sql[128] = "select * from mylite;";
    char *errmsg = NULL;        //用于接收错误信息
    char **pazResult = NULL;
    int cols = 0;
    int rows = 0;
    //执行sql语句
    if(sqlite3_get_table(db,sql,&pazResult, &rows, &cols, &errmsg)\
    != SQLITE_OK){
        printf("sql error:%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    for (size_t i = 0; i < rows+1; i++)
    {
        for (size_t j = 0; j < cols; j++)
        {
            printf("%s\t",pazResult[i*cols+j]);
        }
        printf("\n");
        
    }
    sqlite3_free_table(pazResult);
    return 0;
    
}
int main(int argc, char const *argv[])
{
    sqlite3 *db = NULL;
    if(sqlite3_open("my.db", &db) != SQLITE_OK){
        printf("Error opening\n");
        return -1;
    }
    printf("数据库已经打开\n");
   
    char *err = NULL;
    char sql[1024] = {"create table if not exists mylite(id int,name char,\
     score double);"};
   

   if(sqlite3_exec(db, sql, NULL, NULL , &err) != SQLITE_OK){
        printf("Stu create error:%s\n", err);
        //释放errmsg空间
        sqlite3_free(err);
        return -1; 
         };
     do_add(db);
   do_search(db);
   
    do_serch_1(db);

   
    
    
    return 0;
}