看了一下SQLITE的資料,邊學習邊練習了下,主要涉及到數據庫打開,建表、插入記錄、查詢、關閉數據庫等操作,SQLITE支持多種編程語言來操作,今天用C做為實現工具,具體方法如下:
1 開發環境:
操作系統: windows xp
代碼編譯器:SI
編譯器:DEV C++
API庫:sqlite3
其中日志記錄使用我自己寫的一個日志操作文件,見文章《簡單的分級別寫日志程序》 http://www.linuxidc.com/Linux/2012-02/54210.htm
2 實現代碼:
main.c
[cpp]
- #include <stdio.h>
- #include <stdlib.h>
- #include <string.h>
- #include "./sqlite3/sqlite3.h"
- #include "./tools/write_log.h"
-
- #define SOC_OK (0)
- #define SOC_ERR (-1)
-
- /*數據庫操作句柄*/
- extern sqlite3 *g_pdb;
-
- /*數據庫路徑*/
- extern char g_szdbPath[];
-
- /*********************************************************************
- * 函數名稱:int print_dbinfo
- * 說明:打印表內容
- * 調用者:
- * 輸入參數:
- * 無
- * 輸出參數:
- * 無
- * 返回值:
- * void --
- * 作者: duanyongxing
- * 時間 : 2011-12-04
- *********************************************************************/
- int print_dbinfo(void *pPara, int iColumn, char **pColumnValue, char **pColumnName)
- {
- int iIndex = 0;
-
- Write_Log(LOG_TYPE_INFO, "++++++++記錄中包含%d個字段", iColumn);
-
- for (iIndex = 0; iIndex < iColumn; iIndex++)
- {
- Write_Log(LOG_TYPE_INFO, "++++++++字段名:%s , 字段值:%s",
- pColumnName[iIndex], pColumnValue[iIndex]);
- }
-
- return SOC_OK;
- }
-
- /*********************************************************************
- * 函數名稱:int opeate_tbl_product
- * 說明:tbl_product表操作函數
- * 調用者:
- * 輸入參數:
- * 無
- * 輸出參數:
- * 無
- * 返回值:
- * void --
- * 作者: duanyongxing
- * 時間 : 2011-12-04
- *********************************************************************/
- int opeate_tbl_product(sqlite3 *pdbHandle)
- {
- int iRet = SQLITE_OK;
- char *pErrMsg = NULL;
-
- /*建表語句*/
- char *pSql = " CREATE TABLE tbl_product(\
- i_index INTEGER PRIMARY KEY,\
- sv_productname VARCHAR(12)\
- );";
-
- if (NULL == pdbHandle)
- {
- Write_Log(LOG_TYPE_ERROR, "pdbHandle is null ptr.");
- return SOC_ERR;
- }
-
- /*注意,如果sqlite3_exec返回值為ok, 此時pErrMsg內容為NULL,
- 此場景下,如果試圖操作pErrMsg, 程序將訪問內存越界*/
-
- /*創建表*/
- iRet = sqlite3_exec(pdbHandle, pSql, 0, 0, &pErrMsg);
- if (SQLITE_OK != iRet)
- {
- Write_Log(LOG_TYPE_ERROR, "call tbl_product (create table )return error. errorcode = %d", iRet);
-
- if (NULL != pErrMsg)
- {
- Write_Log(LOG_TYPE_ERROR, "cpErrMsg = %s", pErrMsg);
- }
-
- return SOC_ERR;
- }
-
- /*插入記錄*/
- pSql = "INSERT INTO tbl_product(sv_productname) values('iphone4s');";
-
- iRet = sqlite3_exec(pdbHandle, pSql, 0, 0, &pErrMsg);
- if (SQLITE_OK != iRet)
- {
- Write_Log(LOG_TYPE_ERROR, "call sqlite3_exec (insert) return error. errorcode = %d", iRet);
-
- if (NULL != pErrMsg)
- {
- Write_Log(LOG_TYPE_ERROR, "cpErrMsg = %s", pErrMsg);
- }
-
- return SOC_ERR;
- }
-
- /*查詢表記錄,並通過回調函數將內容打印到日志中*/
- pSql = "SELECT * FROM tbl_product;";
- iRet = sqlite3_exec(pdbHandle, pSql, print_dbinfo, 0, &pErrMsg);
- if (SQLITE_OK != iRet)
- {
- Write_Log(LOG_TYPE_ERROR, "call sqlite3_exec (select) return error. errorcode = %d", iRet);
-
- if (NULL != pErrMsg)
- {
- Write_Log(LOG_TYPE_ERROR, "cpErrMsg = %s", pErrMsg);
- }
-
- return SOC_ERR;
- }
-
- return SOC_OK;
-
- }
-
- /*********************************************************************
- * 函數名稱:int main
- * 說明:程序主入口
- * 調用者:
- * 輸入參數:
- * 無
- * 輸出參數:
- * 無
- * 返回值:
- * void --
- * 作者: duanyongxing
- * 時間 : 2011-12-04
- *********************************************************************/
- int main(int argc, char *argv[])
- {
-
- int iRet = SOC_ERR;
-
- Write_Log(LOG_TYPE_INFO, "func main entering...");
-
- /*打開數據庫*/
- iRet = sqlite3_open(g_szdbPath, &g_pdb);
- if (SQLITE_OK != iRet)
- {
- Write_Log(LOG_TYPE_ERROR, "open db return error. iRet = %d, db_path = %s\n", iRet, g_szdbPath);
- return SOC_ERR;
- }
-
- Write_Log(LOG_TYPE_INFO, "open db succ.");
-
- /*操作數據庫*/
- iRet = opeate_tbl_product(g_pdb);
- if (SOC_OK != iRet)
- {
- Write_Log(LOG_TYPE_ERROR, "call opeate_tbl_product return error.", iRet);
- return SOC_ERR;
- }
-
- /*關閉數據庫*/
- iRet = sqlite3_close(g_pdb);
- if (SQLITE_OK != iRet)
- {
- Write_Log(LOG_TYPE_ERROR, "close db return error. iRet = %d, db_path = %s", iRet, g_szdbPath);
- return SOC_ERR;
- }
-
- Write_Log(LOG_TYPE_INFO, "func main leaing...");
-
- return SOC_OK;
-
- }
sqlite_golbal.c
[cpp]
- #include "./sqlite3/sqlite3.h"
- #include <stddef.h>
-
- sqlite3 *g_pdb = NULL;
- char g_szdbPath[256] = "./db/sqlite_study.db";
-
執行結果:
app_info.log
[html]
- 2011-12-05 01:12:41 func main entering...
- 2011-12-05 01:12:41 open db succ.
- 2011-12-05 01:12:42 ++++++++記錄中包含2個字段
- 2011-12-05 01:12:42 ++++++++字段名:i_index , 字段值:1
- 2011-12-05 01:12:42 ++++++++字段名:sv_productname , 字段值:iphone4s
- 2011-12-05 01:12:43 func main leaing...
其他更多操作見。http://www.linuxidc.com/Linux/2012-02/54212.htm 與http://www.linuxidc.com/Linux/2012-02/54213.htm