database

#include <stdio.h>

#include <stdlib.h>

#include <sqlite3.h>

int create_table(sqlite3 * db)

{

char * errmsg = NULL;

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 failed:%s\n",errmsg);

exit(-1);

}

}

int insert_record(sqlite3 * db)

{

char * errmsg = NULL;

char sql[100];

char name[10];

int id;

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

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

if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))

{

printf("insert failed:%s\n",errmsg);

exit(-1);

}

// char * sql;

/* sql = "insert into mytable (id,name) values(NULL,'zhang');";

if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))

{

printf("insert1 failed:%s\n",errmsg);

exit(-1);

}

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

if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))

{

printf("insert2 failed:%s\n",errmsg);

exit(-1);

}

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

if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))

{

printf("insert3 failed:%s\n",errmsg);

exit(-1);

}*/

}

int displaycb(void * para,int n_col,char ** col_val,char ** col_name)

{

int i;

printf("total column is %d\n",n_col);

for(i = 0;i < n_col;i++)

{

printf("col_name: %s--->col_val: %s\n",col_name[i],col_val[i]);

}

printf("***************************************\n");

return 0;

}

int inquire_usecb(sqlite3 * db)

{

char * errmsg = NULL;

char * sql;

sql = "select * from mytable;";

if(SQLITE_OK != sqlite3_exec(db,sql,displaycb,NULL,&errmsg))

{

printf("inquire failed:%s\n",errmsg);

exit(-1);

}

}

int delete_record(sqlite3 * db)

{

char * errmsg = NULL;

char sql[100];

int id;

printf("please input a id to be deleted:\n");

scanf("%d",&id);

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

if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))

{

printf("delete failed:%s\n",errmsg);

exit(-1);

}

}

int inquire_nocb(sqlite3 * db)

{

int nrow,ncolumn;

char ** azresult;

char *errmsg;

char *sql;

int i;

sql = "select * from mytable;";

if(SQLITE_OK != sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg))

{

printf("inquire failed:%s\n",errmsg);

exit(-1);

}

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

printf("the result of querying 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()

{

    sqlite3 * db = NULL;

int ret;

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

if(ret != SQLITE_OK)

{

perror("sqlite open");

exit(-1);

}

else

{

printf("you have opened a sqlite3 database successfully!\n");

}

create_table(db);

insert_record(db);

inquire_usecb(db);

delete_record(db);

inquire_nocb(db);

sqlite3_close(db);

    return 0;

}