Ads 468x60px

##EasyReadMore##

31 5月, 2016

sqlite3 資料庫存取

將資料存入資料庫再讀出來的範例



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
#include <stdio.h>
#include <string.h>
#include <sqlite3.h>
#define __FILENAME__ (strrchr(__FILE__, '/') ? strrchr(__FILE__, '/') + 1 : __FILE__)

static char *create_judege_sql = "DROP TABLE IF EXISTS JudgeList;CREATE TABLE JudgeList "
  "(Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, RuleId TEXT NOT NULL, "
  "DatumUID TEXT NOT NULL, DatumService TEXT NOT NULL, DatumSetType TEXT NOT NULL, "
  "DatumValue TEXT NOT NULL, ExecutiveAction TEXT, RuleEnd TEXT NOT NULL, "
  "TriggerStatus TEXT NOT NULL);";
static char *create_rule_sql = "DROP TABLE IF EXISTS RuleList;CREATE TABLE RuleList "
  "(RuleId TEXT NOT NULL PRIMARY KEY, Rule TEXT NOT NULL, Action TEXT NOT NULL);";
static char *querysql = "SELECT * FROM JudgeList;";

int init_db(sqlite3 *db)
{
 char *errMsg = NULL;
 
 //check table have be create?
 printf("%d\n", sqlite3_exec(db,"SELECT Id FROM JudgeList", 0, 0, &errMsg));
 if( sqlite3_exec(db,"SELECT Id FROM JudgeList", 0, 0, &errMsg) == 1){
  /* create Table */
  sqlite3_exec(db, create_judege_sql, 0, 0, &errMsg);
  sqlite3_exec(db, create_rule_sql,0,0, &errMsg);
 }
 
 return 0;
}

int insert_judege_to_db(sqlite3 *db,char *ruid,char *uid,char *sid,char *set_type,char *value,char *action,char *ruleEnd,char *triggerStat)
{
 int rows, cols,i,j;
 char *errMsg = NULL;
 char **result;
 char judege_sql[1024];
 
 /* insert data */
 sprintf(judege_sql,"INSERT INTO JudgeList VALUES(NULL,'%s','%s','%s','%s','%s','%s','%s','%s');",ruid,uid,sid,set_type,value,action,ruleEnd,triggerStat);
 printf("billy[%s:%d,%s] judege_sql = %s\n", __FILENAME__, __LINE__, __FUNCTION__,judege_sql);
 sqlite3_exec(db, judege_sql, 0, 0, &errMsg);
 
 /* get last ID */
 printf("%d\n", sqlite3_last_insert_rowid(db));
 
 /* get database all data */
 sqlite3_get_table(db , querysql, &result , &rows, &cols, &errMsg);
 printf("billy[%s:%d,%s] rows = %d,cols = %d\n", __FILENAME__, __LINE__, __FUNCTION__,rows,cols);
 
 /* print all data */
 for (i=0;i<=rows;i++) {
    for (j=0;j<cols;j++) {
     printf("%s\t", result[i*cols+j]);
    }
    printf("\n");
 }
 
 /* release db */
 sqlite3_free_table(result);
 return 0;
}

void main(void)
{
 sqlite3 *db;
 
 /* open database file */
 if (sqlite3_open_v2("ruleEgine.db3", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)) {
  printf("billy[%s:%d,%s] open DB error\n", __FILENAME__, __LINE__, __FUNCTION__);
  return;
 }
 
 init_db(db);
 insert_judege_to_db(db,"5673d23699f13dd41a876e27","1-00124b0004261503","1","1","1.0","[{\"uid\":\"1-00124b0004261318\",\"v\":\"1\",\"sid\":\"4\"}]","1","0");
 
 /* close database */
 sqlite3_close(db);
}

結果:
billy[test.c:31,insert_judege_to_db] judege_sql = INSERT INTO JudgeList VALUES(NULL,'5673d23699f13dd41a876e27','1-00124b0004261503','1','1','1.0','[{"uid":"1-00124b0004261318","v":"1","sid":"4"}]','1','0');
1
billy[test.c:39,insert_judege_to_db] rows = 1,cols = 9
Id RuleId DatumUID DatumService DatumSetType DatumValue ExecutiveAction RuleEnd TriggerStatus
1 5673d23699f13dd41a876e27 1-00124b0004261503 1 1 1.0 [{"uid":"1-00124b0004261318","v":"1","sid":"4"}]

0 意見:

張貼留言

 
Blogger Templates