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

在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;
}