/*    Others:         需要sqlite3的动态库  gcc -o db db.c ./libsqlite3.so //还没弄懂
                                      或 gcc -o db db.c -lsqlite3 //还没弄懂
*************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlite3.h>               

int DbOpenDatabase(char *strDbName, sqlite3 **hDbCon)
{
    int iRet = sqlite3_open(strDbName,hDbCon); //打开数据库
    if(iRet != SQLITE_OK)
    {
        return 0;
    }
    else
    {
        return 1;
    }
}

void DbCloseDatabase(sqlite3 *hDbCon)
{
    sqlite3_close(hDbCon);
}

int DbChangeDataRecord(sqlite3 *hDbCon,char *strSql)
{
    char *pErrMsg = 0;  //错误信息
    int iRet = sqlite3_exec(hDbCon, strSql, NULL, NULL, &pErrMsg);
    if(iRet != SQLITE_OK)
    {
        //printf("查询失败，错误码==>:%d，错误原因==>:%s\n", ret, errmsg );
        return -1;
    }
    else
    {
        return iRet;
    }    
}

int DbExecuteQuerySql(sqlite3 *hDbCon,
                      char *strSql,
                      char ***strResult,
                      int *iRow,
                      int *iColumn)
{
    char *pErrMsg = NULL;         //错误日志
    int iReturn = 0;
    int iResult = sqlite3_get_table( hDbCon,strSql, strResult, iRow, iColumn, &pErrMsg );
    if( SQLITE_OK == iResult )
    {
        iReturn = 1;            
    }
    else
    {
        iReturn = 0;
    }
    return iReturn;
}

void DbFreeResources(char **strResult)
{
    sqlite3_free_table( strResult );
}

int DbSqliteCallBack(sqlite3 * hDbCon,const char *strSql,int (*QueryResultBack)())
{
    char *pErrMsg;
    int iRetDb = 0;
    iRetDb = sqlite3_exec(hDbCon,strSql,QueryResultBack,NULL,&pErrMsg);
    if(iRetDb != SQLITE_OK)
    {
        return 0;
    }
    return 1;
}

//回调函数例子测试
/*static int QueryResultBack(void *para,int iColumn,char **cValue,char **cColumnValueName)
{
    int iCn;
    for(iCn = 0; iCn < iColumn; iCn++)
    {
        //printf( "%s = %s\n" , cColumnValueName[i], cValue[i] ? cValue[i] : "NULL" ); 
        printf( "%s = %s\n" , cColumnValueName[i], cValue[i]);
    }
    return 0;
}*/

int main(int argc, char *argv[]) 
{
    char *dbname="dtmsg.db";
    sqlite3 *db;
    int iReturn=DbOpenDatabase(dbname,&db);
    printf("-1->%d<---\n",iReturn);
    if(iReturn==1)
    {
        //增加、修改、删除
        char *sql="insert into t_msg (msg,msg_buff) values (‘test‘,‘测试信息11‘)";
        //char * sql="update t_msg set msg_buff=‘测试信息2222222‘ where msg=‘test‘";
        //char * sql="delete from t_msg where msg=‘test‘";
        printf("-2-->%s<--\n",sql);
        int iSqlRet=DbChangeDataRecord(db,sql);
        printf("-3-->%d<--\n",iSqlRet);
        
        //查询
        //char *sql="select * from t_msg";
        sql="select * from t_msg";
        char **dbResult;
        int nRow,nColumn,index;
        int i,j;
        int iQret=DbExecuteQuerySql(db,sql,&dbResult,&nRow,&nColumn);
        if(iQret==1)
        {
            index=nColumn;
            for( i = 0; i < nRow ; i++ )
            {
                printf( "第====================> %d 条记录\n", i+1 );
                for( j = 0 ; j < nColumn; j++ )
                {
                    printf( "字段名:%s ==>字段值:%s\n", dbResult[j],dbResult[index]?dbResult[index]:"" );
                    ++index; 
                }
            }
        }
        //不论数据库查询是否成功，都释放 char** 查询结果，使用 sqlite 提供的功能来释放
        //FreeResources(dbResult);
        sqlite3_free_table( dbResult );
    }
    DbCloseDatabase(db);
    return 1;
}