用C程序操作SQLite數據庫

   今天看了一下SQLITE的資料，邊學習邊練習了下，主要涉及到數據庫打開，建表、插入記錄、查詢、關閉數據庫等操作，SQLITE支持多種編程語言來操作，今天用C做為實現工具，具體方法如下：

1 開發環境：

    操作系統: windows xp

    代碼編譯器：SI

    編譯器：DEV C++

    API庫：sqlite3

2 實現代碼：

main.c

#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;

}

#include "./sqlite3/sqlite3.h"
#include <stddef.h>

sqlite3 *g_pdb = NULL;
char    g_szdbPath[256] = "./db/sqlite_study.db";
  

app_info.log

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...