database

/*****************************************************
copyright (C), 2014-2015, Lighting Studio. Co.,     Ltd. 
File name：
Author：Jerey_Jobs    Version:0.1    Date: 
Description：
Funcion List: 
*****************************************************/


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


int create_db(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("create table error!: %s\n",errmsg);
exit (-1);
}
return 0;
}


int insert_db(sqlite3 *db)
{
char * errmsg = NULL;
char *sql;
int id;


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


if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("insert table error! : %s",errmsg);
exit(-1);
}
sql = "insert into mytable (id,name)values(NULL,'li');";


if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("insert table error! : %s",errmsg);
exit(-1);
}
sql = "insert into mytable (id,name)values(NULL,'zhan');";


if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("insert table error! : %s",errmsg);
exit(-1);
}
}


int displaydb(void * para,int col_cnt,char ** col_value,char ** col_name)
{
int i;
printf("total column is %d\n",col_cnt);
for(i = 0;i < col_cnt;i++)
{
printf("column_name :%s--> column_value : %s\n",col_name[i],col_value[i]);
printf("－－－－－－－－－－－－－－－－－－－－－－－－－－－－\n");
}
return 0;
}


int insquire_cb(sqlite3 *db)
{
char *errmsg = NULL;
char *sql;


sql = "select * from mytable;";


if(SQLITE_OK != sqlite3_exec(db,sql,displaydb,NULL,&errmsg))
{
printf("insquire table error! : %s\n",&errmsg);
exit(-1);
}
}


int delete(sqlite3 *db)
{
char * errmsg;
char sql[100];
int n;
printf("the id will be deleted:");
scanf("%d",&n);
    sprintf(sql ,"delete from mytable where id = %d;",n);


if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("create table error!: %s\n",errmsg);
exit (-1);
}


}


int insquire_nocb(sqlite3 *db)
{
int nrow,ncolumn;
char ** azresult;
char *sql;
char *errmsg;
int i;


sql = "select *from mytable;";
if(SQLITE_OK != sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg))
{
printf("operate error!:%s",errmsg);
exit(-1);
}
printf("row:%d  column:%d\n",nrow,ncolumn);
printf("the result of quiring is :\n");


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


int main()
{
int ret;
sqlite3 *db = NULL;


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


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


create_db(db);
insert_db(db);
insquire_cb(db);
delete(db);
insquire_nocb(db);


sqlite3_close(db);
    return 0;
}