sqlite3 c++ function: INSERT statement not executed when it comes as an argument

Question

I am implementing a function that receives any SQL statement and then executes it.

void dbExec(std::string str, bool vertical)
{
   sqlite3 *db; // Create db object
   char *zErrMsg = 0;
   int rc;
   const char *sql = str.c_str(); // Convert string to char
   sqlite3_stmt *stmt = NULL; // SQL statement

   /* Open Database */
   rc = sqlite3_open("db/survey.db",&db);
   if (rc)
   {
      fprintf(stderr, "DBOPEN_ERR: %s\n", sqlite3_errmsg(db));
   }

   /* Execute SQL statement */
   rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); // Prepare statement

   if (rc != SQLITE_OK )
   { // Check error
      fprintf(stderr, "DB error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }

   int cols = sqlite3_column_count(stmt); // Number of columns
   const char *data = NULL; // data char pointer

   if (vertical)
   {
      sqlite3_step( stmt );
      for (int i = 0; i < cols; i++)
      {
         // Print column name and info
         data = (const char*)sqlite3_column_text( stmt, i );
         std::cout << std::setw(20) << sqlite3_column_name(stmt,i)
                   << std::setw(30) << (data ? data : "[NULL]" );
         printf("\n");
      }
   }
   else
   {
      for (int i = 0; i < cols; i++)
      {
         // Print column name
         std::cout << std::setw(15) << sqlite3_column_name(stmt,i);
      }
      printf("\n");

      while ( sqlite3_step( stmt ) == SQLITE_ROW )
      {
         // print each row
         for (int i = 0; i < cols; i++)
         {
            data = (const char*)sqlite3_column_text( stmt, i );
            std::cout << std::setw(15) << (data ? data : "[NULL]" );
         }
         printf("\n");
      }
   }

   /* Close Database */
   sqlite3_close(db);
}

When the str argument is:

SELECT * FROM TABLE

it works perfect. If the str argument is:

INSERT INTO TABLE (COL1) VALUES(100)

it doesn't work.

However, if inside of the function I add the following line:

str = "INSERT INTO TABLE (COL1) VALUES(100)";

it works perfect. I tried many things but I still can't figure out what's going on... Any ideas?

Thanks!

EDIT: The function dbExec is being called in this way:

void addBorehole()
{
   std::string statement;
   statement = "INSERT INTO BOREHOLE (TOTAL_DEPTH) VALUES (45)";

   dbExec(statement, false);
}

*"When I input in str..."* - there is no such "input" operation in this post. And considering blatantly overruling that operation and hard-coding the "same" string within your function functions correctly, perhaps your real problem lays in the code you chose *not* to post. — WhozCraig, Jun 07 '17 at 11:13
Thanks for your reply. I just updated the question with the full code. — Muthahu6, Jun 07 '17 at 11:23
There are lots of errors in this code, but none of them should prevent the INSERT from executing. And you have not shown the full code; provide a [mcve]. — CL., Jun 07 '17 at 11:34
Just a hint: Pass a `std::string` as `const std::string&` to avoid copying the whole string and just pass a reference. This doesn't solve your problem but is best practice. Could you also provide the call to `dbExec`?. — Andre Kampling, Jun 07 '17 at 11:35
@AndreKampling thanks for the hint. I added the call to dbExec — Muthahu6, Jun 07 '17 at 11:48
Also you mixed using `printf` and `std::cout`, that is a bad practice because you are mixing C and C++. Just use `std::cout`, if you want a new line like you do with printf you can do the following: `std::cout << std::endl;`. — Andre Kampling, Jun 07 '17 at 12:05
@CL. with 'doesn't work' I mean that it is not inserting a new record in the table. — Muthahu6, Jun 07 '17 at 12:22
@AndreKampling thanks for your comments, I am not a software engineer and I am learning how to code by myself to make software for my job, so any comments and hints are welcome. — Muthahu6, Jun 07 '17 at 12:22
You're welcome! So what does the `sqlite3_prepare_v2` return as error code? [These](https://sqlite.org/rescode.html) are the error codes that are possible. — Andre Kampling, Jun 07 '17 at 12:29
@AndreKampling no error is printed when SQLITE_OK is checked. It behaves as if the SQL statement was executed successfully. I tried changing the permissions in the db and running as root because I thought it was a problem with the permissions but still didn't work. — Muthahu6, Jun 07 '17 at 12:37
The actual statement evaluation is done at `sqlite3_step( stmt );`. You don't check the return code of that. Please also check that and tell us about that. — Andre Kampling, Jun 07 '17 at 12:47
@AndreKampling well, I found that sqlite3_step( stmt ) == SQLITE_BUSY, but this is the only process opening the database :/ any ideas? — Muthahu6, Jun 07 '17 at 13:27
OK! found the answer: put sqlite3_finalize( stmt ); before closing the database. Now it's working as expected. Thank you for your help! — Muthahu6, Jun 07 '17 at 13:53

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Your problem is that the database is busy (SQLITE_BUSY). The documentation sais:

[...] SQLITE_BUSY indicates a conflict with a separate database connection, probably in a separate process [...]

So there must be a process that is blocking your database. To get the process which is blocking the database you can do the following (Copied from the Stack Exchange network).

Linux:

$ fuser development.db

This command will show what process is locking the file:

> development.db: 5430

Just kill the process...

kill -9 5430

Windows:

PowerShell method:

IF((Test-Path -Path $FileOrFolderPath) -eq $false) {
    Write-Warning "File or directory does not exist."       
}
Else {
    $LockingProcess = CMD /C "openfiles /query /fo table | find /I ""$FileOrFolderPath"""
    Write-Host $LockingProcess
}

More details How to find out which process is locking a file or folder in Windows

The other method for Windows would be to use the ProcessExplorer.

score 0 · Answer 2 · answered Jun 07 '17 at 14:05

0

OK, the problem was solved by writing the following line before closing the database:

sqlite3_finalize( stmt );

Info: https://sqlite.org/c3ref/close.html

If the database connection is associated with unfinalized prepared statements or unfinished sqlite3_backup objects then sqlite3_close() will leave the database connection open and return SQLITE_BUSY.

However, I still didn't get why it worked when I hardcoded the statement inside of the function.

Thank you!

answered Jun 07 '17 at 14:05

Muthahu6

1
2

Maybe you run the hardcoded version first when there was no database access before, so that the database was not busy. You can test it again with your code. – Andre Kampling Jun 07 '17 at 18:32

sqlite3 c++ function: INSERT statement not executed when it comes as an argument

2 Answers2