Can't insert into a database using sqlite3_bind_text

Question

Hello I have an issue with this function recup(). The purpose of recup() is to insert all of the words found in the string chaine, word by word, into a database. The words come from chaine which is set by the user using a fgets. The problem I am facing is that it is only inserting words that have the same length as the first word of chaine. For example, if I write "I want a cat", only "I" and "a" will be inserted.

int recup(char *chaine){

    char tab[30] ={0};
    char delim[] = " ";
    char *ptr = strtok(chaine,delim);

    int rc =0;
    char *sql ="INSERT INTO newTable SELECT * FROM dbTable where lemme =?";

    sqlite3_stmt *stmt = 0;

    rc = sqlite3_prepare_v2(db,sql,-1,&stmt,0); 

    do {
        // Expected word is printed here
        printf("%s\n",ptr);
        strcpy(tab,ptr);

        // For some reason, only words that match the length
        // of the first word are being inserted
        rc = sqlite3_bind_text(stmt,1,tab,-1,SQLITE_STATIC);  
        rc = sqlite3_step(stmt);
        ptr = strtok(NULL,delim);
    } while(ptr != NULL);
    rc = sqlite3_finalize(stmt); 
    return 0;
}


#include <ansi_c.h>
#include <sqlite3.h>
#include "fdata.h"
sqlite3 *db;   

int main(int argc, char* argv[]) { 
      int rc = 0;
      char chaine[100] = {0};
      rc = sqlite3_open("lexique.db",&db);
      check(rc);
      createNewT();
      question(&chaine);
      recup(&chaine);
      sqlite3_close(db); 
      return 0;
}

Thank you for posting a question. Please include a main() function as part of your Compilable, Minimal, Complete, and Verifiable Example. This helps us help you. — Gardener
– Gardener, Commented Mar 18, 2019 at 10:24
Hmm..., you never test the rc return value. It is bad not to do it, and very bad to not even print it when things go wrong. And anyway, why do you use that strange SQL statement if you just want to insert words from the input text? — Serge Ballesta
– Serge Ballesta, Commented Mar 18, 2019 at 10:34
You're right I should check rc. I need this SQL statement to use a variable in the where. — Quentin Lam
– Quentin Lam, Commented Mar 18, 2019 at 11:19
You should use SQLITE_TRANSIENT instead of SQLITE_STATIC in your call to sqlite3_bind_text. Perhaps it's related to the problems you're experiencing. — Daniel Kamil Kozar
– Daniel Kamil Kozar, Commented Mar 18, 2019 at 12:52

Serge Ballesta · Accepted Answer · 2019-03-18 12:49:15Z

The C SQLite API is not very tolerant, and requires strict semantics. Here your problem is that you try to reuse a SQL statement in a loop (which is right) without resetting it (which is wrong). The current flow is:

sqlite3_open
sqlite3_prepare_v2
sqlite3_bind
sqlite3_step
sqlite3_bind => ERROR SQLITE_MISUSE

While the documentation for sqlite3_step says (emphasize mine):

SQLITE_DONE means that the statement has finished executing successfully. sqlite3_step() should not be called again on this virtual machine without first calling sqlite3_reset() to reset the virtual machine back to its initial state.

I assume that the following sqlite3_step causes a statement reset, which will allow the 3rd word to be processed, but you lose every second word.

The loop should be:

do {
    printf("%s\n",ptr);
    strcpy(tab,ptr);


    rc = sqlite3_bind_text(stmt,1,tab,-1,SQLITE_STATIC);
    if (rc) {
        // process error
    }
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        // process error
    }
    sqlite3_reset(stmt);
    ptr = strtok(NULL,delim);
} while(ptr);

user3657941 · Accepted Answer · 2019-03-18 10:29:13Z

1

This is a mcve showing how to use strtok to tokenize your example string:

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

int main(int argc, char *argv[])
{
    char *test = "I want a cat";
    char chaime[strlen(test) + 1];
    char *delim = " ";
    char *ptr = NULL;

    strcpy(chaime, test);
    ptr = strtok(chaime, delim);
    while (ptr) {
        printf("%s\n", ptr);
        ptr = strtok(NULL, delim);
    }
    return 0;
}

Output

[user@machine]: ./program
I
want
a
cat

Note

If you can give us something like this that demonstrates your problem, it will make it easier to help you.

answered Mar 18, 2019 at 10:29

user3657941

1 Comment

Quentin Lam Over a year ago

In my program the printf of ptr match what I should have but once I check the result in my database only the words with the same length as the first word have been inserted.

Gardener · Accepted Answer · 2019-03-18 11:15:12Z

The strtok() function modifies the string that is passed into it. So, it is necessary to create a copy of the input string. See @David Cullen's excellent answer which creates a mcve.

See the comments in the corrected code that explain what is happening.

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

int recup(char *chaine){


  char tab[30] ={0};
  char delim[] = " ";
  ///// strtok will modify its inut string argument, so create a copy of the input
  strcpy(tab, chaine);
  // ptr will contain the current last token that was parsed
  char *ptr = strtok(tab,delim); // pass the copy to strtok, ptr points to first word

  int rc =0;
  char *sql ="INSERT INTO newTable SELECT * FROM dbTable where lemme =?";

  sqlite3_stmt *stmt = 0;

  rc = sqlite3_prepare_v2(db,sql,-1,&stmt,0);


  while (ptr) {
    printf("%s\n",ptr);  // print the word that was just tokenized

    rc = sqlite3_bind_text(stmt,1,ptr,-1,SQLITE_STATIC);
    rc = sqlite3_step(stmt);
    ptr = strtok(NULL,delim);
  }
  rc = sqlite3_finalize(stmt);
  return 0;
}

Collectives™ on Stack Overflow

Can't insert into a database using sqlite3_bind_text

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related