C & Sqlite3 - Variable database & table naming - SQL Syntax error

Question

I'm trying to write some code that initializes an sql database and table with the name of the database & table set through preprocessor commands and if not set, defaulting to the values given below.

I'm using a macro to stringify whatever name is given. The sqlite3_open correctly opens / creates the database with the given name or the default

sqlite3_open(TO_STRING(DB_NAME), &db);

For the creation of the table I'm relying on sprintf.

sprintf(str,"CREATE TABLE %s;", TO_STRING(TABLE_NAME));
sql = str;

When inspecting my code in debugging, pointer sql seems to be having the correct value of "CREATE TABLE SensorData;". However when I try to execute my code I get the error "SQL error: near ";": syntax error". Upon inspection it is clear that the error code 110 is returned by the function, I can't seem to find any information on this code however.

I can't seem to figure out what the exact problem is in this case, as the string resulting in the array str seems to be exactly the same as when I create the table in a static way.

This is all my code relevant to the problem.

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

#define REAL_TO_STRING(s) #s
#define TO_STRING(s) REAL_TO_STRING(s)   

#ifndef DB_NAME
  #define DB_NAME Sensor.db
#endif

#ifndef TABLE_NAME
  #define TABLE_NAME SensorData
#endif

#define DBCONN sqlite3 

int main()
{

    char *err_msg = 0;
    DBCONN * db;
    int ok = sqlite3_open(TO_STRING(DB_NAME), &db);


    if(ok != SQLITE_OK)
    {
        fprintf(stderr,"Cannot open database: %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return 0;
    }

    char * sql;
    char str[100];
    //clear array
    memset(&str[0],0,100);  

    sprintf(str,"CREATE TABLE %s;", TO_STRING(TABLE_NAME));
    sql = str;

    ok = sqlite3_exec(db, sql, 0, 0, &err_msg);

    if (ok != SQLITE_OK ){

      fprintf(stderr, "SQL error: %s\n", err_msg);

      sqlite3_free(err_msg);        
      sqlite3_close(db);

      return 0;
  }

    sqlite3_close(db);

    return 0;

}

Your CREATE TABLE query is invalid. Try something like this: CREATE TABLE 'mytable'('id' INTEGER); You need at least one field in the table — Simone Cifani, May 10 '17 at 13:41
Ah yes, I added several fields and both the table and the database have been created now. This solved the problem, thank you! — TheAlPaca02, May 10 '17 at 13:48

score 0 · Accepted Answer · edited May 23 '17 at 12:10

To turn @Simone Cifani 's comment into a valid answer. The problem was that my CREATE TABLE query was invalid as I did not add any fields to the table. Following solution solved the problem.

Note that I also swapped the sprintf function for the asprintf function, which I highly suggest you read up on if you are planning to do anything similar to this. This thread explains the working of the asprintf function and the difference with the sprintf function very clearly.

#define _GNU_SOURCE 

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

#define REAL_TO_STRING(s) #s
#define TO_STRING(s) REAL_TO_STRING(s)   

#ifndef DB_NAME
  #define DB_NAME Sensor.db
#endif

#ifndef TABLE_NAME
  #define TABLE_NAME SensorData
#endif

#define DBCONN sqlite3 

int main()
{

    char *err_msg = 0;
    DBCONN * db;
    int rc = sqlite3_open(TO_STRING(DB_NAME), &db);

    if(rc != SQLITE_OK)
    {
        fprintf(stderr,"Cannot open database: %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return 0;
    }

    char * sql;
    int size = 0;

    size  = asprintf(&sql,"CREATE TABLE %s (id INT, value REAL, ts INT);", TO_STRING(TABLE_NAME));

    if(size == -1)
        printf("asprintf failed in main\n");

    rc = sqlite3_exec(db, sql, 0, 0, &err_msg);

    if (rc != SQLITE_OK ){

      fprintf(stderr, "SQL error: %s\n", err_msg);
      sqlite3_free(err_msg);        
      sqlite3_close(db);
      free(sql);
      return 0;
  }

    free(sql);
    sqlite3_close(db);
    return 0;

}

C & Sqlite3 - Variable database & table naming - SQL Syntax error

1 Answers1