#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;

}