CODEWITHSUNDEEP
c++sqlsqlitesyntax-error
Alexandre Libert
Alexandre Libert

Reputation: 41

WHERE column = value, only work with INTEGER value

I use sqlite on a c++ project, but I have a problem when i use WHERE on a column with TEXT values

I created a sqlite database:

    CREATE TABLE User( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(24))

When i try to get the value of the column with VARCHAR values, it doesn't work, and return me a STATUS_CODE 101 just after the sqlite3_step : 

int res = 0;
sqlite3_stmt *request;
char *sqlSelection = (char *)"SELECT * FROM User WHERE name='bob' ";
int id = 0;

res = sqlite3_prepare_v2(db, sqlSelection, strlen(sqlSelection), &request, NULL);

if (!res){
while (res == SQLITE_OK || res == SQLITE_ROW){
  res = sqlite3_step(request);
  if (res == SQLITE_OK || res == SQLITE_ROW ){
    id = sqlite3_column_int(request, 0);
    printf("User exist %i \n",id);
  }
}
sqlite3_finalize(request);

I also tried with LIKE but it also doesn't work

SELECT * FROM User WHERE name LIKE '%bob%'

But when I execute the same code but for an INTERGER value

SELECT * FROM User WHERE id=1

It work fine.

In DB Browser for SQLite all requests work fine.

Upvotes: 0

Views: 140

Answers (2)

Alexandre Libert
Alexandre Libert

Reputation: 41

The solution was to replace the VARCHAR fields by TEXT. SQLite for c++ seems to don't manage VARCHAR fields when they are used after the WHERE

Upvotes: 0

Pemassi
Pemassi

Reputation: 642

To solve the problem I searched what status code 101 means.

Here is what they said.

(101) SQLITE_DONE

The SQLITE_DONE result code indicates that an operation has completed. The SQLITE_DONE result code is most commonly seen as a return value from sqlite3_step() indicating that the SQL statement has run to completion. But SQLITE_DONE can also be returned by other multi-step interfaces such as sqlite3_backup_step().

https://sqlite.org/rescode.html

So, you're getting 101 because there is no more result from SELECT SQL.

Upvotes: 1

Related Questions