SQLite 常用语句示例

    
    示例一
    
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc, colNumber = 0, j,i, row_count = 0;
    printf("sqlite3 info:\n libversion:%s\n souceid:%s\n vesion_num:%d\n",sqlite3_libversion(), sqlite3_sourceid(),
      sqlite3_libversion_number());
    
    sqlite3_stmt *pStmt = NULL;
    // 打开数据库文件
    rc = sqlite3_open("argv[1]", &db);
    if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      sqlite3_close(db);
      return(1);
    }
    // 把一条SQL语句编译成字节码留给后面的执行函数
    rc = sqlite3_prepare(db, "SQLString", strlen("SQLString"), &pStmt, NULL);
    printf("rc =%d\n", rc);
    if(rc != SQLITE_OK){
        fprintf(stderr, "SQL error1: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        return 1;
    }
  
    printf("pStmt=%p\n",pStmt);
    // 获取数据库有多少列
    ret = sqlite3_column_count(pStmt);
    printf("BEFORE STEP col count= %d, rc = %d\n", ret, rc);
    printf("pStmt22=%p\n",pStmt);
    // 遍历数据库每列的名字、类型
    /*
    sqlite3_column_type()函数返回第N列的值的数据类型. 具体的返回值如下:
       #define SQLITE_INTEGER  1
       #define SQLITE_FLOAT    2
       #define SQLITE_TEXT     3
       #define SQLITE_BLOB     4
       #define SQLITE_NULL     5
    */
    i = 0;
    while(i < ret){
        printf("%20s \t %d \t %20s \t %d\n",
        sqlite3_column_name(pStmt, i), //第N列的字段名
        sqlite3_column_type(pStmt, i),
        sqlite3_column_decltype(pStmt, i),  //该列在 CREATE TABLE 语句中声明的类型. 它可以用在当返回类型是空字符串的时候
        sqlite3_column_bytes(pStmt, i));  //返回 UTF-8 编码的BLOBs列的字节数或者TEXT字符串的字节数
        i++;
    }
    /*
    int sqlite3_step(sqlite3_stmt*);
        如果SQL返回了一个单行结果集，sqlite3_step() 函数将返回 SQLITE_ROW , 
        如果SQL语句执行成功或者正常将返回 SQLITE_DONE , 否则将返回错误代码. 
        如果不能打开数据库文件则会返回 SQLITE_BUSY . 
        如果函数的返回值是 SQLITE_ROW, 那么下边的这些方法可以用来获得记录集行中的数据:
         const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
         int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
         int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
         int sqlite3_column_count(sqlite3_stmt*);
         const char *sqlite3_column_decltype(sqlite3_stmt *, int iCol);
         const void *sqlite3_column_decltype16(sqlite3_stmt *, int iCol);
         double sqlite3_column_double(sqlite3_stmt*, int iCol);
         int sqlite3_column_int(sqlite3_stmt*, int iCol);
         long long int sqlite3_column_int64(sqlite3_stmt*, int iCol);
         const char *sqlite3_column_name(sqlite3_stmt*, int iCol);
         const void *sqlite3_column_name16(sqlite3_stmt*, int iCol);
         const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
         const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
         int sqlite3_column_type(sqlite3_stmt*, int iCol);
    */
    rc = sqlite3_step(pStmt);
    printf("pStmt3=%p\n",pStmt);
    
    if(rc != SQLITE_ROW){
          fprintf(stderr, "SQL error2: %s,rc =%d\n", zErrMsg, rc);
          sqlite3_free(zErrMsg);
            return 1;
    }
    
    while(rc == SQLITE_ROW){
        // 遍历该行的每一列
        i = 0;row_count++;
         for(j=0;j< ret;j++){
        //   printf("%s ", sqlite3_column_text(pStmt,j));
        // }
        //printf("%d -> %d w:%d\n", sqlite3_column_int(pStmt,1), sqlite3_column_int(pStmt,2), sqlite3_column_int(pStmt,4));
        insertArc(GPt,sqlite3_column_int(pStmt,1), sqlite3_column_int(pStmt,2), sqlite3_column_int(pStmt,4));
        #if 0
        printf("%d, %s, %s, %s\n",
          sqlite3_column_int(pStmt, 0),
          sqlite3_column_text(pStmt, 1),
          sqlite3_column_text(pStmt, 2),
          sqlite3_column_text(pStmt, 3));
        #endif
        // 遍历每一行
        rc = sqlite3_step(pStmt);
   }
   printf("row_count=%d\n ", row_count);
   //  将销毁一个准备好的SQL声明. 在数据库关闭之前，所有准备好的声明都必须被释放销毁.
   sqlite3_finalize(pStmt);

   sqlite3_close(db);



   示例二

    sqlite3* db = NULL;
    if (sqlite3_initialize() != SQLITE_OK) {
        fprintf(stderr, "error: failed to initialize sqlite\n");
        exit(EXIT_FAILURE);
    }
    int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
    if (sqlite3_open_v2(file, &db, flags, NULL) != SQLITE_OK) {
        fprintf(stderr, "error: failed to open %s\n", file);
        exit(EXIT_FAILURE);
    }
        
    sqlite3_stmt *stmt = NULL;
    result = sqlite3_prepare_v2(db, input->buffer, length, &stmt, NULL);
    if (result != SQLITE_OK) {
        send_error(sqlite3_errcode(db), sqlite3_errmsg(db));
        continue;
    }

    /* Print out rows. */
    int ncolumns = sqlite3_column_count(stmt);
    while (sqlite3_step(stmt) == SQLITE_ROW) 
    {
        int i;
        for (i = 0; i < ncolumns; i++) {
            // 获取每一列的类型并赋值
            int type = sqlite3_column_type(stmt, i);
            switch (type) {
            case SQLITE_INTEGER:
                printf("%lld", sqlite3_column_int64(stmt, i));
                break;
            case SQLITE_FLOAT:
                printf("%f", sqlite3_column_double(stmt, i));
                break;
            case SQLITE_NULL:
                printf("nil");
                break;
            case SQLITE_TEXT:
                fwrite(sqlite3_column_text(stmt, i), 1,
                       sqlite3_column_bytes(stmt, i), stdout);
                break;
            case SQLITE_BLOB:
                printf("nil");
                break;
            }
        }
    }
    
    if (sqlite3_finalize(stmt) != SQLITE_OK) {
        send_error(sqlite3_errcode(db), sqlite3_errmsg(db));
    } else {
        printf("success\n");
    }

    sqlite3_close(db);
    sqlite3_shutdown();