在C程序中利用SQLite JSON函数封装SQL表数据并在Web服务器HTML页面展示的技术求助

Hey there! Let's walk through this together—since you're new to combining SQLite and JSON, it's totally normal to hit a roadblock at first. The good news is SQLite's built-in JSON functions do work for this, we just need to adjust your query and code to use them properly.

Here's the core idea: #

Instead of fetching individual columns and printing them line by line, we'll use SQLite's json_object() to turn each row into a JSON object, then json_group_array() to bundle all those objects into a single JSON array. That array is exactly what you can pass to your web page's HTML/JS.

Step 1: Update Your SQL Query #

First, replace your basic SELECT * statement with one that builds the JSON structure. Let's assume your students table has three columns: let's say id, name, and age (swap these out for your actual column names!). Your new SQL will look like this:

SELECT json_group_array(json_object('id', id, 'name', name, 'age', age)) AS student_json FROM students;

• json_object('key', column) creates a JSON object for each row (e.g., {"id": "1", "name": "Alice", "age": "20"})
• json_group_array() wraps all those objects into a single array (e.g., [{"id":"1",...}, {"id":"2",...}])

Step 2: Modify Your C Code to Fetch the JSON #

Now adjust your C code to execute this new query and extract the JSON string instead of individual columns. Here's the full revised code with comments:

#include "sqlite3.h"
#include <stdio.h>
#include <stdlib.h> // For exit()

int main(void) {
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int rc = 0;

    // Open the database (same as before)
    rc = sqlite3_open("test.db", &db);
    if (rc != SQLITE_OK) {
        printf("Database could not be opened: %s \n", sqlite3_errmsg(db));
        exit(EXIT_FAILURE);
    } else {
        printf("Database successfully opened\n");
    }

    // Updated SQL query to generate JSON
    char *sql_stmt = "SELECT json_group_array(json_object('id', id, 'name', name, 'age', age)) AS student_json FROM students";
    
    // Prepare the statement (same as before)
    rc = sqlite3_prepare_v2(db, sql_stmt, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        printf("\nUnable to prepare query: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    // Execute the query and fetch the JSON result
    printf("Student records (JSON format):\n");
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // Get the JSON string from the first (and only) column
        const char *json_result = (const char *)sqlite3_column_text(stmt, 0);
        if (json_result) {
            printf("%s\n", json_result);
            // If you're using this in a web server (e.g., CGI), you'd output HTTP headers first:
            // printf("Content-Type: application/json\n\n");
            // printf("%s\n", json_result);
        } else {
            printf("No data found or JSON generation failed\n");
        }
    }

    // Cleanup (same as before)
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}

Key Notes for Success: #

• Check Your SQLite Version: json_group_array() was added in SQLite 3.33.0 (released in 2020). If your SQLite is older, you'll need to upgrade it—run sqlite3 --version to check.
• Match Column Names: Make sure the column names in json_object() exactly match the ones in your students table. If your columns are, say, student_id, full_name, year, adjust the query accordingly.
• Web Server Integration: If you're using this code in a web server (like a CGI script), you'll want to output the correct HTTP headers first (as commented in the code) so the browser recognizes the response as JSON. You can then use JavaScript on your HTML page to fetch this JSON and display the table data.

Troubleshooting Tips: #

• If you get no output, run the SQL query directly in sqlite3 command line to test it:

  sqlite3 test.db
  SELECT json_group_array(json_object('id', id, 'name', name, 'age', age)) FROM students;
  

  This will tell you if the query itself is working (if it returns JSON, the issue is in your C code; if not, check your table structure or column names).
• Always print sqlite3_errmsg(db) when something fails—it will tell you exactly what's wrong (e.g., missing column names, invalid JSON syntax).

You've got this! Start with the revised code, tweak it to match your table's columns, and test step by step. Let me know if you hit any specific snags along the way.

内容的提问来源于stack exchange，提问作者C-3PO