Retrieve Sqlite table data in C++

Question

I have the code as below. I am trying to simply get data from a table i have already created. How do i get the data in the array p_fields that is defined in the callback function into a variable in main. Can i define a char ** array in main and copy the data somehow into it in the callback function?

Thanks very much, Shyam.

#include <string.h>
#include <stdio.h>
#include "sqlite3.h"
#include <iostream>
#include <fstream>
#include <sstream>
using namespace std;

sqlite3* db;

int select_callback(void *p_data, int num_fields, char **p_fields, char **p_col_names) {

  int i;

  for(i=0; i < num_fields; i++) {
    if (p_fields[i]) {
      printf("%20s", p_fields[i]);
    }
    else {
      printf("%20s", " ");
    }
  }
  printf("\n");
  return 0;
}

void select_stmt(const char* stmt) {
  char *errmsg;
  int   ret;
  int   nrecs = 0;
  float var;

  ret = sqlite3_exec(db, stmt, select_callback, &nrecs, &errmsg);

  if(ret!=SQLITE_OK) {
    printf("Error in select statement %s [%s].\n", stmt, errmsg);
  }
  else {
    printf("\n   %d records returned.\n", nrecs);
  }

  cout<< ret << endl;
}

void sql_stmt(const char* stmt) {
  char *errmsg;
  int   ret;

  ret = sqlite3_exec(db, stmt, 0, 0, &errmsg);

  if(ret != SQLITE_OK) {
    printf("Error in statement: %s [%s].\n", stmt, errmsg);
  }
}

int main() {

  sqlite3_open("Flamelet.db", &db);

  if(db == 0) {
    printf("\nCould not open database.");
    return 1;
  }

  sqlite3_stmt *stmt;
  select_stmt("SELECT density from Ftable where PROG=10.0");

  sqlite3_close(db);
  return 0;
}

Answer 1

You need to pass the array where you want to store the data in as the 1st argument to the callback as shown in sqlite3 docs

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 */
);

Here's a contrived example based on your code with some tidy up:

#include <vector>
#include <string>
#include <iostream>
#include "sqlite3.h"
using namespace std;

sqlite3* db;

using Record = std::vector<std::string>;
using Records = std::vector<Record>;

int select_callback(void *p_data, int num_fields, char **p_fields, char **p_col_names)
{
  Records* records = static_cast<Records*>(p_data);
  try {
    records->emplace_back(p_fields, p_fields + num_fields);
  }
  catch (...) {
    // abort select on failure, don't let exception propogate thru sqlite3 call-stack
    return 1;
  }
  return 0;
}

Records select_stmt(const char* stmt)
{
  Records records;  
  char *errmsg;
  int ret = sqlite3_exec(db, stmt, select_callback, &records, &errmsg);
  if (ret != SQLITE_OK) {
    std::cerr << "Error in select statement " << stmt << "[" << errmsg << "]\n";
  }
  else {
    std::cerr << records.size() << " records returned.\n";
  }

  return records;
}

void sql_stmt(const char* stmt)
{
  char *errmsg;
  int ret = sqlite3_exec(db, stmt, 0, 0, &errmsg);
  if (ret != SQLITE_OK) {
    std::cerr << "Error in select statement " << stmt << "[" << errmsg << "]\n";
  }
}

int main()
{
  if (sqlite3_open("test.db", &db) != SQLITE_OK) {
    std::cerr << "Could not open database.\n";
    return 1;
  }

  Records records = select_stmt("SELECT * FROM test");
  sqlite3_close(db);

  for (auto& record : records) {
    // do something with your records
  }

  return 0;
}

Your question is tag c++, so I've removed your use of the standard c api calls in favour of standard c++.