SQlite C/C++调用接口

下面是对SQLite函数接口的部分简介：
先看一下SQLite3的错误代码（在安装好SQLite之后，可以从SQLite3.h中找到下面信息）：

#define SQLITE_OK           0   /* Successful result */
/* beginning-of-error-codes */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* Unknown opcode in sqlite3_file_control() */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* Database is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_FORMAT      24   /* Auxiliary database format error */
#define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB      26   /* File opened that is not a database file */
#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */

几个最常用的函数接口：

1、 int sqlite3_open(const char *filename, sqlite3 **ppDb);
操作数据库的入口函数。
通过该函数可以打开现有的数据库，在数据库不存在的情况下可以新建数据库。

2、 int sqlite3_open16(const void*, sqlite3**);
sqlite3_open16()使用UTF-16编码(使用本地主机字节顺序)传递数据库文件名。如果要创建新数据库, sqlite3_open16()将内部文本转换为UTF-16编码, 反之sqlite3_open() 将文本转换为UTF-8编码。

3、 int sqlite3_close(sqlite3*);
关闭数据库。

4、 int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
将SQL语句作为第二个参数传入，可实现对数据库的操作。


下面是一个简单的实现操作数据库的例子：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int  main (int argc, char ** argv)
{
    int result = 0;
    sqlite3* db = NULL;
    char * errMsg = NULL;
    char sql_cmd[200];
    memset(sql_cmd, 0x00, sizeof(sql_cmd));

    result = sqlite3_open("lester.db", &db);
    printf("Hello Lesterk, result = %d\n", result); 

    char* sqlCreate = "create table LesterData2 (LesterId INTEGER, LesterName Integer);";
    result = sqlite3_exec(db, sqlCreate, 0, 0, &errMsg);
    printf("Create_Result = %d , Msg = %s \n", result, errMsg);
    
    char* sqlInsert = "INSERT INTO \"LesterData2\" VALUES( 1, 2);";
    result = sqlite3_exec(db, sqlInsert, 0, 0, &errMsg);
    printf("Insert_Result = %d , Msg = %s \n", result, errMsg);
    
    char* sql = "SELECT * FROM  LesterData2";
    int nrow = 1, ncolumn = 1;
    char **azResult;
    result = sqlite3_get_table( db, sql, &azResult, &nrow, &ncolumn, &errMsg);
    printf("Select_Result = %d , Msg = %s \n", result, errMsg);
    
    int i = 0;
    printf( "row = %d,  column=%d \n" , nrow , ncolumn );
    printf( "\nThe result of querying is : \n" );
    
    for( i = 0; i < ( nrow + 1 ) * ncolumn ; i++){
         printf( "azResult[%d] = %s\n", i , azResult[i] );
    }
    
    sqlite3_free_table( azResult );

    printf("Hello World\n, %d", result);
    sqlite3_close(db);
    return 0;
}

建立两个存在外键关系的表：

#include "string.h"
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int  main ()
{                                                                   
    int result = 0;
    sqlite3* db = NULL;
    char * errMsg = NULL;
    char sql_cmd[200];
    memset(sql_cmd, 0x00, sizeof(sql_cmd));

    result = sqlite3_open("lester.db", &db);
                                     
    char* sqlCreate = "create table LesterTable1 (LesterId PRIMARY KEY, LesterNum Integer, LesterName Integer);";
    result = sqlite3_exec(db, sqlCreate, 0, 0, &errMsg);
    
    sqlCreate ="create table LesterTable2 (Lester2Id PRIMARY KEY, Lester2Name Integer, foreign2 Integer, FOREIGN KEY(foreign2) REFERENCES LesterTable1(LesterId));";
    result = sqlite3_exec(db, sqlCreate, 0, 0, &errMsg);
    
    sqlite3_close(db);                                
    return 1;
}