Why can't sqlite3 find my table, while it's there?

The relevant part of my code:

  char sSQL[] = "INSERT INTO stations (sHost,sPath,sPort,sName) VALUES ('%s','%s','%ld','%s')";
  char *pSQL  = sSQL;       // allocate pointer for the sql query
  sqlite3_stmt *pStmt;       // allocate pointer for the statement
  
  char sHost[50] = "", sPath[50] = "", sName[50] = "";
  long sPort = 0;

  sprintf(pSQL, sSQL, sHost, sPath, sPort, sName);
  rc = sqlite3_prepare_v2(pDb, pSQL, -1, &pStmt, NULL);
  if (rc != SQLITE_OK)
  {
     fprintf(stderr, "Failed to fetch data: %s\n", sqlite3_errmsg(pDb));
     sqlite3_close(pDb);
     return 1;
  }

The error message is: "Failed to fetch data: no such table: stations"
This is my database: Database — ImgBB

Your construct of using sSQL as the format string and the target will almost certainly cause a problem with buffer overflow.

@wildbill
I appreciate your good intention, but with all due respect, I have no clue what your trying to say.

sprintf takes a format string and a buffer that the resultant output is to go into and some parameters that should match what was in the format string.

When you did this:

  char *pSQL  = sSQL;

and later this:

  sprintf(pSQL, sSQL, sHost, sPath, sPort, sName);

You have ensured that pSQL and sSQL are the same address. Bad things will happen. Define an array for the result instead.

I'm wondering on which Arduino this is running. I've never seen stderr in Arduino code; although that might not mean everything.

@wildbill,
Thank you, I understand that.

I'm trying my luck on a simpler version; this the relevant code:

#include <string.h>
#include <stdlib.h>
#include <sqlite3.h>

int main(int argc, const char * argv[])
{
  sqlite3 *db;
  if (sqlite3_open("the_insert.db", &db))
  {
    printf("Could not open the.db\n");
    exit(-1);
  }
  printf("Database %s is open\n", "the_insert.db");
  //
  // Prepare a statement for multiple use:
  // =====================================
  //
  char sqlQuery[] = "INSERT INTO someTable (second, third) VALUES (?,?)";
  sqlite3_stmt *stmt;
  if (sqlite3_prepare_v2(db, sqlQuery, -1, &stmt, NULL))
  {
    printf("Error executing prepare statement\n");
    sqlite3_close(db);
    exit(-1);
  }

The error message is:
Database the_insert.db is open
Error executing prepare statement.

Rest asure that someTable is in place and with the expected structure.

What is going on with that statement: "sqlite3_prepare_v2"?
Deprecated, different use or parameters of not used for INSERT?

This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.