Is it possible to programmatically convert SQLite database to SQL statements in C/C++?

Question

I know of the existance of the .dump function in the SQLite command line tool, and Python has an iterdump command that emulates that .dump function.

Is there a standard API call or a C/C++ wrapper that provides that .dump functionality programmatically?

Answer 1

The API does not seem to have any dump function (https://www.sqlite.org/capi3ref.html), but you can construct your dump by:

• Creating a new function that will use your buffer result of sqlite3_exec() or sqlite3_get_table() and dump it to a FILE *

• Use the dump function provided in the source code of SQLite, you can find it in the (shell.c).

Edit: Adding this sample

/* TODO : This is just a sample code, modify it to meet your need */
void select_and_dump_sqlite3_table(sqlite3 *dbh)
{
    FILE    *dump_file;
    int i;
    sqlite3_stmt *stmt;

    dump_file = fopen(path_to_dump_file, "w");
    if (dump_file == NULL) {
        /* Error handling with errno and exit */
    }

    sqlite3_prepare_v2(dbh, "SELECT name, address, phone FROM Person",
                       0, &stmt, NULL);
    /* dump columns names into the file */
    for (i = 0; i < 3; i++) {
        fprintf (dump_file, "%30s | ", sqlite3_column_name(stmt, i));
    }
    printf ("\n");
  
    /* Dump columns data into the file */
    while (SQLITE_ROW == sqlite3_step(stmt)) {
        for (i = 0; i < 3; i++) {
          fprintf (dump_file, "%30s | ", sqlite3_column_text (stmt, i));
        }
      printf ("\n");
    }
    /* We're ready to leave */
    sqlite3_finalize (stmt);
}

Answer 2

You can do a SELECT * FROM sqlite_master to get all the tables and indices (each row has a type column that will be 'table' for tables and 'index' for indices, and an sql column that contains the sql statement used to create that table/index).

Then for each table found in sqlite_master, SELECT * from them (each sqlite_master row has a name column) and write out all the data in the tables.

See the SQLite FAQ and command line shell pages for more info.