学习日志 上课190804

嵌入式数据库

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>

int create_table(sqlite3 *db)
{
	char *sql;
	char *errmsg;
	int rec;

	sql = "create table if not exists mytable (id integer primary key,name text);";

    rec = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

	if(rec != SQLITE_OK)
	{
		printf("create table error!\n");
		exit(-1);
	}

	return 0;
}

int insert_record(sqlite3 *db)
{

	char sql[100];
	char *errmsg;
	int rec;
	char name[20];
	int id;

	scanf("%d%s",&id,name);

    sprintf(sql,"insert into mytable (id,name) values(%d,'%s');",id,name);

    rec = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

	if(rec != SQLITE_OK)
	{
		printf("insert table error:%s!\n",errmsg);
		exit(-1);
	}
    
/*	sql = "insert into mytable (id,name) values(NULL,'wang');";

    rec = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

	if(rec != SQLITE_OK)
	{
		printf("insert table error!\n");
		exit(-1);
	}

	sql = "insert into mytable (id,name) values(NULL,'li');";

    rec = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

	if(rec != SQLITE_OK)
	{
		printf("insert table error!\n");
		exit(-1);
	}*/

	return 0;
}

int displaycb(void * para,int ncolumn,char **columnvalue,char **columnname)
{
    int i;
	printf("total column is:%d\n",ncolumn);

	for(i = 0; i < ncolumn;i++)
	{
		printf("columnname:%s---->columnvalue:%s\n",columnname[i],columnvalue[i]);
	}
	printf("***************************\n");

	return 0;
}

int inquire_usecb(sqlite3 *db)
{
    
	char *sql;
	char *errmsg;
	int rec;

	sql = "select * from mytable;";

    rec = sqlite3_exec(db,sql,displaycb,NULL,&errmsg);

	if(rec != SQLITE_OK)
	{
		printf("select error:%s!\n",errmsg);
		exit(-1);
	}

	return 0;
}

int delete_record(sqlite3 *db)
{
	char sql[100];
	char *errmsg;
	int rec;
    int id;

	scanf("%d",&id);

	sprintf(sql,"delete from mytable where id = %d;",id);

    rec = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

	if(rec != SQLITE_OK)
	{
		printf("delete error:%s!\n",errmsg);
		exit(-1);
	}

}

int inquire_nocb(sqlite3 *db)
{
	int nrow;
	int ncolumn;
	char *sql;
	char *errmsg;
	char **azresult;
	int i;
	int rec;

	sql = "select * from mytable;";

	rec = sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg);

	if(rec != SQLITE_OK)
	{
		printf("get table error!\n");
		exit(-1);
	}

	printf("row:%d  column:%d\n",nrow,ncolumn);

	for(i = 0; i< (nrow + 1) * ncolumn;i++)
	{
	//	printf("azresult[%d]=%s\n",i,azresult[i]);
	    printf("%10s",azresult[i]);
		if((i + 1) % ncolumn == 0)
		{
			printf("\n");
		}
	}
	sqlite3_free_table(azresult);

	return 0;
}

int main()
{
	sqlite3 *db;
	int rec;

	rec = sqlite3_open("mydatabase.db",&db);

	if(rec != SQLITE_OK)
	{
		printf("open error!\n");
		exit(-1);
	}
	else
	{
		printf("open database successfully!\n");
	}

	create_table(db);
    insert_record(db);
    inquire_usecb(db);
	delete_record(db);
    inquire_nocb(db);

    return 0;
}