数据库函数练习

/*****************************************************
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_table(sqlite3 *db)
{
 char *errmsg = NULL;
 char *sql;

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

 if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
 {
  printf("operater failed1 : %s \n",errmsg);
  exit(0);
 }
}

int insert_record(sqlite3 *db)
{
 char *errmsg;
 char sql[100];
 char name[10];
 int id;
    int n;
 int i;
 printf("enter the number \n");
 scanf("%d",&n);
 for(i = 0; i < n;i++)
 {
 printf("enter the id you want to insert :\n");
 scanf("%d",&id);
 printf("enter the 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("operater failed2 : %s \n",errmsg);
  exit(0);
 }
 }

/* sql = "insert into mytable(id,name) values(NULL,'xie');";
 if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
 {
  printf("operater failed3 : %s \n",errmsg);
  exit(0);
 }

 sql = "insert into mytable(id,name) values(NULL,'zhen');";
 if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
 {
  printf("operater failed4 : %s \n",errmsg);
  exit(0);
 }*/
}

int displaycb(void *para,int n_column,char **column_value,char **column_name)
{
 int i;
 printf("total column is %d \n",n_column);
 for(i = 0;i < n_column;i++)
 {
//  printf("\t\t %s \t\t %s \n",column_name[i],column_value[i]);
   printf("column_name : %s ----> column_value : %s \n",column_name[i],column_value[i]);
 }
 return 0;
}

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

 sql = "select * from mytable;";

 if(SQLITE_OK != sqlite3_exec(db,sql,displaycb,NULL,&errmsg))
 {
  printf("operater failed5 : %s \n",errmsg);
  exit(0);
 }
}
int delete_record(sqlite3 *db)
{
 char *errmsg;
 char sql[100];
 int id;

 printf("enter the 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("operater failed6 :%s \n",errmsg);
  exit(0);
 }
}

int inquire_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("operater failed : %s\n",errmsg);
  exit(0);
 }
 printf("row :%d        column :%d\n",nrow,ncolumn);
 printf("the result of querying : \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("open error!\n");
  exit(0);
 }
 else
 {
  printf("you have opened a qulite3 database successfully !\n");
 }

 create_table(db);
 insert_record(db);
 inquire_usecb(db);
    delete_record(db);
 inquire_nocb(db);
 sqlite3_close(db);
    return 0;
}