sqlite3应用

int sqlite3_open(
const char *filename,   /* Database filename (UTF-8) */
sqlite3 **ppDb          /* OUT: SQLite db handle */
);

int sqlite3_open_v2(
const char *filename,   /* Database filename (UTF-8) */
sqlite3 **ppDb,         /* OUT: SQLite db handle */
int flags,              /* Flags */
const char *zVfs        /* Name of VFS module to use */
);

int sqlite3_close(sqlite3 *);

//int rscallback(void *p, int argc, char *argv[], char *argvv[])
typedef int (*sqlite3_callback)(void*,int,char**, char**);

int sqlite3_exec(
sqlite3*,                                  /* An open database */
const char *sql,                           /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**),  /* Callback function */
void *,                                    /* 1st argument to callback */
char **errmsg                              /* Error msg written here */
);

** The pointer arguments to [sqlite3_free()] and [sqlite3_realloc()]
** must be either NULL or else pointers obtained from a prior
** invocation of [sqlite3_malloc()] or [sqlite3_realloc()] that have
** not yet been released.

void *sqlite3_malloc(int);
void *sqlite3_realloc(void*, int);
void sqlite3_free(void*);

**The sqlite3_mprintf() and sqlite3_vmprintf() routines write their
** results into memory obtained from [sqlite3_malloc()].
** The strings returned by these two routines should be
** released by [sqlite3_free()].  ^Both routines return a
** NULL pointer if [sqlite3_malloc()] is unable to allocate enough
** memory to hold the resulting string.

SQLITE_API char *sqlite3_mprintf(const char*,...);
SQLITE_API char *sqlite3_vmprintf(const char*, va_list);
SQLITE_API char *sqlite3_snprintf(int,char*,const char*, ...);
SQLITE_API char *sqlite3_vsnprintf(int,char*,const char*, va_list);

#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 */
/* end-of-error-codes */

** ^The sqlite3_errcode() interface returns the numeric [result code] or
** [extended result code] for the most recent failed sqlite3_* API call
** associated with a [database connection]. If a prior API call failed
** but the most recent API call succeeded, the return value from
** sqlite3_errcode() is undefined.  ^The sqlite3_extended_errcode()
** interface is the same except that it always returns the
** [extended result code] even when extended result codes are
** disabled.
**
** ^The sqlite3_errmsg() and sqlite3_errmsg16() return English-language
** text that describes the error, as either UTF-8 or UTF-16 respectively.
** ^(Memory to hold the error message string is managed internally.
** The application does not need to worry about freeing the result.
** However, the error string might be overwritten or deallocated by
** subsequent calls to other SQLite interface functions.)^
**
** When the serialized [threading mode] is in use, it might be the
** case that a second error occurs on a separate thread in between
** the time of the first error and the call to these interfaces.
** When that happens, the second error will be reported since these
** interfaces always report the most recent result.  To avoid
** this, each thread can obtain exclusive use of the [database connection] D
** by invoking [sqlite3_mutex_enter]([sqlite3_db_mutex](D)) before beginning
** to use D and invoking [sqlite3_mutex_leave]([sqlite3_db_mutex](D)) after
** all calls to the interfaces listed here are completed.
**
** If an interface fails with SQLITE_MISUSE, that means the interface
** was invoked incorrectly by the application.  In that case, the
** error code and message may or may not be set.
*/

SQLITE_API int sqlite3_errcode(sqlite3 *db);
SQLITE_API int sqlite3_extended_errcode(sqlite3 *db);
SQLITE_API const char *sqlite3_errmsg(sqlite3*);
SQLITE_API const void *sqlite3_errmsg16(sqlite3*);

#include <sqlite3.h>
#include <stdio.h>

int main()
{
      int ret;
      sqlite3 * db;
      char *err = NULL;
      char *sql = NULL;
      char id[10];
      char name[20];
      char age[10];

      //当数据库文件不存在时，sqlite3_open不会报错，会创建一个空文件
      //而sqlite3_open_v2会报错，并且不会产生空的数据库文件
//    ret = sqlite3_open_v2("mydatabase1.dat", &db, SQLITE_OPEN_READWRITE, NULL);  
      ret = sqlite3_open("mydatabase1.dat", &db);
      if(ret != SQLITE_OK)
      {
           //printf("open database error!
");
           fputs(sqlite3_errmsg(db), stderr);
           return -1;
      }

      ret = sqlite3_exec(db, "create table student(id integer primary key, name text, age integer);", NULL, NULL, &err);
      if(ret != SQLITE_OK)
      {
           fputs(err, stderr);
           return -1;
      }

      printf("please input student info:
");
      printf("Id:
");
      scanf("%s", id);
      printf("Name:
");
      scanf("%s", name);
      printf("Age:
");
      scanf("%s", age);
 
      sql = sqlite3_mprintf("insert into student values(%s, %Q, %s);",
                 id, name, age);
      ret = sqlite3_exec(db, sql, NULL, NULL, &err);

      /*
      ret = sqlite3_exec(db, "insert into student values(4, 'XiaoLi', 23);", NULL, NULL, &err);*/
      if(ret != SQLITE_OK)
      {
           fputs(err, stderr);
           return -1;
      }

      sqlite3_free(sql);
      sqlite3_close(db);
      return 0;
}

#include <sqlite3.h>
#include <stdio.h>

//select查询用到的回调函数
//回调函数是检测到有一条记录就执行一次，有几条记录就执行几次
//p是&empty, argc是字段个数，argvv是字段名字， argv是字段值
int rscallback(void *p, int argc, char *argv[], char *argvv[])
{
      int i;
      *(int *)p = 0; //有记录，我们就把empty改变成0
      for(i=0; i<argc; ++i)
           printf("%s=%s ", argvv[i], argv[i]?argv[i]:"null");
      printf("
");
return 0;
}

int main()
{
      int ret;
      sqlite3 * db;
      int empty=1; //为1表示查询结果为空，没有匹配的记录
      char *err = NULL;
 
      ret = sqlite3_open("mydatabase.dat", &db);
      if(ret != SQLITE_OK)
      {
           printf("open database error!
");
           return -1;
      } 

      ret = sqlite3_exec(db, "select * from student;", rscallback, &empty, &err);

      if(ret != SQLITE_OK)
      {
           fputs(err, stderr);
           return -1;
      }

      if(empty)
      {
           printf("查询结果为空!
");
      }

      sqlite3_close(db);

      return 0;
}