SQLITE学习笔记一(打开、操作及关闭数据库，C程序实现)

   今天看了一下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;}

 

sqlite_golbal.c

#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 , 字段值：12011-12-05 01:12:42 ++++++++字段名：sv_productname , 字段值：iphone4s2011-12-05 01:12:43 func main leaing...

 其他更多操作见后续博文。