SQL Error: Syntax Near "(": syntax error (C++ Interface)

I'm trying to run an SQLite Query in my C++ program which removes all duplicate records in one of my database tables. However, I keep encountering various syntax errors.

Here's my SQL table:

Table Name: spamtrigrams

ID          TRIGRAM       
----------  ----------  
1           Sam's nice ham        
2           Tuesday was cold       
3           Alex stood up
4           Mark passed out      
5           this database is
6           date with a
7           disco stew pot
++10000 

Here's my C++ code:

  sql = "DELETE from spamtrigrams WHERE id in(" \
    "SELECT id from spamtrigrams" \
    "EXCEPT(SELECT min(id);"  \
    "from spamtrigrams," \
    "group by TRIGRAM," \
    "having count(1) > 1," \
    "union all," \
    "SELECT min(id)," \
    "from spamtrigrams," \
    "group by TRIGRAM," \
    "having count(1) = 1));";
ret = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
if (ret != SQLITE_OK) {
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
}
else {
    fprintf(stdout, "Duplicates Deleted\n");

Is anybody able to tell me why this error occurs?

Thanks.

Answers


It looks like you are missing some spaces and have extra characters here and there:

sql = "DELETE from spamtrigrams WHERE id in(" \
"SELECT id from spamtrigrams" \ // <<= Here's a missing space
"EXCEPT(SELECT min(id);"  \     // <<= Here's an extra semicolon
"from spamtrigrams," \          // <<= Here's an extra comma
"group by TRIGRAM," \           // <<= Here's an extra comma
"having count(1) > 1," \        // <<= Here's an extra comma
"union all," \                  // <<= Here's an extra comma
"SELECT min(id)," \             // <<= Here's an extra comma
"from spamtrigrams," \          // <<= Here's an extra comma
"group by TRIGRAM," \           // <<= Here's an extra comma
"having count(1) = 1));";

In C++, when you put two string literals together, separated only by white space, the compiler treats them as a single literal. Therefore, it's OK to drop backslashes at the end of each line (or drop double quotes at the end of each line, if you prefer it like that).


dont know if this is your (only) problem, or, if this is a typo in posting the question, but you have redundant ; in "EXCEPT(SELECT min(id);"

this is in addition to the missing spaces mentioned in a referent answer, while I was writing this one


The problem isn't in the string concatenation, but in the number of commas of the query. Whatever DBMS you use, you don't have to put so many commas, and a ';' in SELECT min(id) (that is seen as the end of SELECT statement).

For this reason, the DBMS engine doesn't recognize the statement and it prints a syntax error.

Your query, in your C++ program, should look like this:

sql = "DELETE from spamtrigrams WHERE id in(" \
    "SELECT id from spamtrigrams " \
    "EXCEPT(SELECT min(id) "  \
    "from spamtrigrams " \
    "group by TRIGRAM " \
    "having count(1) > 1 " \
    "union all " \
    "SELECT min(id) " \
    "from spamtrigrams " \
    "group by TRIGRAM " \
    "having count(1) = 1));";

Furthermore, don't forget to add a whitespace between the keywords and the identifiers.


Need Your Help

adding a second for statement to function

jquery function datepicker

Updating my code to reflect my attempt at incorporating both for statements.

Sqoop and MSSQL with window auth on linux fails

sql-server linux apache sqoop

I m using apache sqoop to connect mSSQL server using window authentication but