sqlite环境下的数据库

sqlite3数据库之处女作

By—it_zujun

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#define     LEN     20
#define     TAB_NAME "room"
#define     SIZE    1024
sqlite3 *db = NULL;
char** result;
char buffer[1024];          //send buffer to Android
int nx, ny;
char* errmsg;
int ret;
char tab[20];               //sql command sqace
char a[7] = {'"', '"', '(', ')', ',', ';', '*'};
char* create_tab_name(char tab[20]);     //build  tab command tab[20]
void create_tab(sqlite3* db);            //do create_tab command
char* cmd_insert(char tab[20],char* device, char* status);
char* sql_insert(char* device, char* status);
char* cmd_delete(char tab[20], char* device);
void sql_delete(char* device);
char*  sql_to_android(char tab[20]);

char*  sql_to_android(char tab[20])
{
    int i, j;
    memset(tab, 0, LEN);
    strcat(tab, "select * from room");
    ret = sqlite3_get_table(db, tab, &result, &ny, &nx,&errmsg);
    if (ret != SQLITE_OK)
        printf("serch the room data failed\n");
    else
        printf("serch thr room data succeed\n");

    printf("nx = %d\n", nx);
    printf("ny = %d\n", ny);

    memset(buffer, 0, SIZE);
    strcat(buffer,"R");
    
    for(i = 1; i< ny+1; i++)
    {
        strcat(buffer,":");
        for (j=0; j< nx; j++)
        {
            strcat(buffer, result[i*nx+j]);
        }
    }

/*    for (i= nx; i<(ny+1)*nx; i++)
    {
        strcat(buffer,":");
        strcat(buffer, result[i]);
        printf("string: %s\n",result[i]);
    }
 */
    printf("send_to_android: %s\n", buffer);
    
}

char* cmd_update(char* device, char* sta)
{
    memset(tab, 0, LEN);
    strcat(tab,"update room set sta = ");
    strncat(tab, &a[0], 1);
    strcat(tab, sta);
    strncat(tab, &a[0], 1);
    strcat(tab, " where device = ");
    strncat(tab, &a[0], 1);
    strcat(tab,device);
    strncat(tab, &a[0], 1);
    strncat(tab, &a[5], 1);
    printf("string = %s\n", tab);
    return tab;
}

void sql_update(char* device, char* sta)
{
    cmd_update(device, sta);
    ret = sqlite3_exec(db, tab, NULL,NULL, &errmsg);
    if (ret != SQLITE_OK)
        printf("sql update failed\n");
    else 
        printf("sql update succeed\n");
    return;
}

char* cmd_search(char tab[20], char* device)
{
    /*
     select * from room where device = %s
     */
    memset(tab, 0, LEN);
    strcat(tab, "select * from room where device = ");
    strcat(tab, device);
    printf("string: %s\n",tab);
    return tab;
}

void sql_search(char* device)
{
    ret = sqlite3_exec(db, tab, NULL,NULL, &errmsg);
    if (ret != SQLITE_OK)
        printf("sql search failed\n");
    else 
          printf("sql search succeed\n");
    return;
}

char* cmd_delete(char tab[20], char* device)
{
    memset(tab, 0, LEN);
    strcat(tab, "delete from room where device = ");
    strcat(tab, device);
    printf("string: %s\n", tab);
    return tab;
}

void sql_delete(char* device)
{
    cmd_delete(tab, device);
    ret = sqlite3_exec(db, tab, NULL,NULL, &errmsg);
    if (ret != SQLITE_OK)
        printf("sql delete failed\n");
    else
        printf("sql delete succeed\n");
    return;
}

char* cmd_insert(char tab[20],char* device, char* status)
{
    memset(tab, 0, LEN);
    strcat(tab, "insert into room values");
    strncat(tab, &a[2], 1); // (
    strncat(tab, &a[0], 1); // "
    strcat(tab, device);    // device
    strncat(tab, &a[1], 1); // "
    strncat(tab, &a[4], 1); // ,
    strncat(tab, &a[0], 1); // "
    strcat(tab, status);    // status
    strncat(tab, &a[1], 1); // "
    strncat(tab, &a[3], 1); // )
    strncat(tab, &a[5], 1); // ;
    return tab;
}

char* sql_insert(char* device, char* status)
{
  cmd_insert(tab, device, status);
  ret = sqlite3_exec(db, tab, NULL,NULL, &errmsg);
  if (ret != SQLITE_OK)
      printf("sql insert failed\n");
  else 
      printf("sql insert successe\n");
  return;
}

void tab_insert(sqlite3* db, char* device, char* status)
{
    ret = sqlite3_exec(db, tab, NULL,NULL, &errmsg);
    if (ret != SQLITE_OK)
        printf("create room tab failed\nyou have created tab !!!!!!\n");
    else 
        printf("crate root tab succeed\n");
}

char* create_tab_name(char tab[20])
{
    memset(tab, 0, LEN);
    strcat(tab,"create table ");
    strcat(tab, TAB_NAME);
    strcat(tab, "(device varchar(2), sta varchar(1));");
    return tab;
}

void create_tab(sqlite3* db)
{
    create_tab_name(tab);
    ret = sqlite3_exec(db, tab, NULL,NULL, &errmsg);
    if (ret != SQLITE_OK)
    {
        printf("create room tab failed\nyou have created tab !!!!!!\n");
    } else {
        printf("crate root tab succeed\n");
    }
    return;
}

int main(int argc, char *argv[])
{
    ret = sqlite3_open("uwan.db", &db);
    if (SQLITE_OK != ret)
    {
        printf("open uwan.db failed\n");
        exit(0);
    } else {
        printf("open uwan.db succeed\n");
    }

    create_tab(db);
    sql_insert("11","N");
    sql_insert("12","N");
    sql_insert("13","N");
    sql_insert("14","F");
    sql_insert("15","F");

  //  sql_delete("14");

    cmd_search(tab, "11");
    sql_to_android(tab);
    sql_update("13", "F");
    sql_search("12");
    sqlite3_close(db);
    printf("close uwan.db succed\n");
    
    return 0;
}