Select * from ... query results

[ChaitanyaYeole02]

How to get all the variables in a list or an array from select *?

   rc = db_exec(db1, "INSERT INTO sessions(id, tid, start_ts, stop_ts, m_start, m_stop, status, start_f, stop_f) VALUES('c3605', '12345', '2021-10-26 12:07:00', '2021-10-26 12:07:00', '1000', '1000', 'f', 't', 't');");
   if (rc != SQLITE_OK) {
       sqlite3_close(db1);
       return;
   }

   rc = db_exec(db1, "SELECT * FROM sessions");
   if (rc != SQLITE_OK) {
       sqlite3_close(db1);
       return;
   }

I am inserting this values and wanted to access the whole session in one go, is it possible?

@siara-cc @siara-in

[Fischkopppp]

I think, if you use db_exec function you have to use the callback function to get all the variables. However the prepare statement is (at least for me) easier to use.

Take a look at the web_console example:

          if (first) {
            int count = sqlite3_column_count(res);
            if (count == 0) {
                resp += "<tr><td>Statement executed successfully</td></tr>";
                rec_count = sqlite3_changes(db1);
                break;
            }
            resp += "<tr>";
            for (int i = 0; i<count; i++) {
                resp += "<td>";
                resp += sqlite3_column_name(res, i);
                resp += "</td>";
            }
            resp += "</tr>";
            first = false;
          }
          int count = sqlite3_column_count(res);
          resp += "<tr>";
          for (int i = 0; i<count; i++) {
              resp += "<td>";
              resp += (const char *) sqlite3_column_text(res, i);
              resp += "</td>";
          }

You can get the column names from sqlite3_column_name(res, i); and the column variables from (const char *) sqlite3_column_text(res, i); I hope this will help you a bit. However I am not an expert in c++ or sqlite. Maybe there is a better/quicker way? Pls. post your code if you should have succeeded ;)

[siara-cc]

@ChaitanyaYeole02 Not sure what you mean by accessing the whole session, but I think the PreparedStatement solution suggested by @Fischkopppp will let you access the records in a loop instead of accessing them one by one in the callback.

[Fischkopppp]

An example to store the data for one row with "i" columns in a one dimensional array: test[i]. If you want to store the data from multiple rows you have to use a two dimensional array.

sqlite3_column_int will try to return the value as an int. You can use:

• sqlite3_column_blob | → | BLOB result
• sqlite3_column_double | → | REAL result
• sqlite3_column_int | → | 32-bit INTEGER result
• sqlite3_column_int64 | → | 64-bit INTEGER result
• sqlite3_column_text | → | UTF-8 TEXT result
• sqlite3_column_text16 | → | UTF-16 TEXT result
• sqlite3_column_value | → | The result as an unprotected sqlite3_value object.
• sqlite3_column_bytes | → | Size of a BLOB or a UTF-8 TEXT result in bytes
• sqlite3_column_bytes16 | → | Size of UTF-16 TEXT in bytes
• sqlite3_column_type | → | Default datatype of the result-

 bool first = true;
      while (sqlite3_step(res) == SQLITE_ROW) {
          if (first) {
            int count = sqlite3_column_count(res);
            if (count == 0) {
              Serial.printf("Rec Count: %s\n", sqlite3_changes(db1));
                break;
            }
            for (int i = 0; i<count; i++) {
              Serial.printf("Column Name: %s\n", sqlite3_column_name(res, i));
            }
            first = false;
          }
          int count = sqlite3_column_count(res);
          int32_t test[count];
          for (int i = 0; i<count; i++) {
            test[i] =sqlite3_column_int(res, i);
          }