Linux下的C语言编程——sqlite3的基本操作

约定不等于承诺〃 2022-07-15 10:40 245阅读 0赞

在C语言中实现sqlite3的基本操作

#include <stdio.h>
    #include <stdlib.h>
    #include <sqlite3.h>
    
    int delete(sqlite3 *db)
    {
    
    	char *errmsg;
    	char sql[100];
    	int id;
    
    	printf("input a id you want to delete!\n");
    	scanf("%d",&id);
    
    	sprintf(sql,"delete from mytable where id = %d;",id);
    
    	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
    	{
    		printf("fail:%s\n",errmsg);
    		printf("\n");
    		exit(-1);
    	}
    }
    
    int display(void *para,int col_count,char **col_value,char **col_name)
    {
    	int i;
    
    	printf("total column is %d\n",col_count);
    
    	for(i = 0; i < col_count; i++)
    	{
    		printf("%s: %s: \n",col_name[i],col_value[i]);
    	}
    
    	printf("********************\n");
    
    	return 0;
    }
    
    int display_usecb(sqlite3 *db)
    {
    
    	char *errmsg;
    	char *sql;
    
    	sql = "select * from mytable;";
    
    	if(SQLITE_OK != sqlite3_exec(db,sql,display,NULL,&errmsg))
    	{
    		printf("fail:%s\n",errmsg);
    		printf("\n");
    		exit(-1);
    	}
    }
    
    int insert_record(sqlite3 *db)
    {
    	
    	char *errmsg;
    	char sql[100];
    	
    	int id;
    	char name[10];
    
    	printf("input a id you want to insert!\n");
    	scanf("%d",&id);
    
    	printf("input a name you want to insert!\n");
    	scanf("%s",name);
    
    	sprintf(sql,"insert into mytable (id,name) values (%d,'%s');",id,name);
    
    	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
    	{
    		printf("fail:%s\n",errmsg);
    		printf("\n");
    		exit(-1);
    	}
    	
    /*	sql = "insert into mytable (id,name) values (NULL,'qi');";
    
    	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
    	{
    		printf("fail:%s\n",errmsg);
    		printf("\n");
    	}
    	
    	sql = "insert into mytable (id,name) values (NULL,'fei');";
    
    	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
    	{
    		printf("fail:%s\n",errmsg);
    		printf("\n");
    	}*/
    }
    
    int creat_table(sqlite3 *db)
    {
    	char *errmsg;
    	char *sql;
    
    	sql = "create table if not exists mytable (id integer primary key,name text);";
    
    	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
    	{
    		printf("fail:%s\n",errmsg);
    		printf("\n");
    		exit(-1);
    	}
    }
    
    int display_nocb(sqlite3 *db)
    {
    	int nrow;
    	int ncolumn;
    	int i;
    	char *errmsg;
    	char **aresult;
    	char *sql;
    	
    	sql = "select * from mytable;";
    
    	if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncolumn,&errmsg))
    	{
    		printf("file : %s\n",errmsg);
    		printf("\n");
    		exit(-1);
    	}
    
    	printf("row : %d	column : %d\n",nrow,ncolumn);
    
    	for(i = 0; i < (nrow + 1) * ncolumn; i++)
    	{
    		printf("%10s",aresult[i]);
    
    		if((i + 1) % ncolumn == 0)
    		{
    			printf("\n");
    		}
    	}
    
    	sqlite3_free_table(aresult);
    
    }
    
    int main()
    {
    	int ret;
    	sqlite3 *db;
    
    	ret = sqlite3_open("test.db",&db);
    
    	if(ret != SQLITE_OK)
    	{
    		perror("sqlite open:");
    		exit(-1);
    	}
    
    	creat_table(db);
    	insert_record(db);
    	display_usecb(db);
    	delete(db);
    	//display_usecb(db);
    	display_nocb(db);
        return 0;
    }