I have two problems that drive me crazy.

The first one is to integrate JSON in the following code to use JSON as a kind of wrapper around the sqlite statements. This should be for the purpose to display just the entire sql table on a webserver page embedded in HTML. To connect to the database and fetch the table I used sqlite3 and the given functions like sqlite3_open()/sqlite3_close() and so on. I know that sqlite3 has functions to handle JSON as well like json_array()/json_insert() etc. I think this should be a good possible solution to reach my goal. I played around with those functions a bit, but nothing has worked yet. Means there was absolutely no result. Maybe anyone knows how to put the sqlite3 commands/requests in JSON objects to display in a further step those table entries on a simple HTML page. I have to say I`m a absolutely beginer in this section, but read and tried a lot to get into it. But didnt catch it yet.

The second problem is, that the following C code does not execute the while loop to return the rows of my sql table. Output is "Database sucessfull opened unable to fetch data, means it stopps within the last if statement. But I dont know why. Tried to fix it but didnt solve the puzzle yet. The while statement "sqlite3_step(stmt) == SQLITE_ROW" seems to be false, but why? Maybe something is wrong with the stmt pointer.

Hope you could help me. Would be very thankful for every hint to get it work.

C code:

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

int main(void) {
    sqlite3 *db;
    sqlite3_stmt *stmt;            
    int rc = 0;
    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 sucessful opened");
    }

    sqlite3_close(db);

    char *sql_stmt = "SELECT * FROM students";

    rc = sqlite3_prepare_v2(db, sql_stmt, -1, &stmt, 0);
        
    if(rc != SQLITE_OK)
    {
        printf("\nUnable to fetch data");
        sqlite3_close(db);
        return 1;
    }

    printf("student records\n");

    while(sqlite3_step(stmt) == SQLITE_ROW)
    {
        printf("%s %s %s\n", sqlite3_column_text(stmt, 0), sqlite3_column_text(stmt, 1), 
        sqlite3_column_text(stmt, 2));
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);
    
    return 0;
}

CodePudding user response：

I suggest you first figure out why rc = sqlite3_prepare_v2(db, sql_stmt, -1, &stmt, 0); doesn't return an error. Then remove the first sqlite3_close(db); so you are operating on a open database handle.