SQLite Forum

sqlite3_prepare_v2 does not seem to work.
Login

sqlite3_prepare_v2 does not seem to work.

(1) By anonymous on 2021-02-25 12:02:38 [link] [source]

I'm trying to get my head around sqlite3. I'm using the <sqlite3.h> lib that comes with Xcode. The code is quite simple, but does not completely work as expected.

#include <sqlite3.h>

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

int main(int argc, const char * argv[]) { sqlite3 *db; if (sqlite3_open("the_insert.db", &db)) { printf("Could not open the_insert.dbn"); exit(-1); } printf("Database %s is openn", "the_insert.db"); // // Prepare a statement for multiple use: // ===================================== // const char *query = "INSERT INTO someTable (second, third) VALUES (?,?)"; int sqlSize = (int)strlen(query) + 1; sqlite3_stmt *stmt;

int err = sqlite3_prepare_v2(db, query, sqlSize, &stmt, NULL); printf("err: %dn", err);

if (sqlite3_prepare_v2(db, query, sqlSize, &stmt, NULL)) { printf("Error executing prepare statement: n"); //, sqlite3_errstr()); sqlite3_close(db); exit(-1); }

printf("preparedn"); // I NEVER GET THIS FAR! /* * The prepared statement executed multiple times * ============================================== */ for (...) { sqlite3_bind_int (stmt, 1, ...); sqlite3_bind_text(stmt, 2, ...);

   if (sqlite3_step(stmt))
   {
      printf("execution failed\n");
      sqlite3_finalize(stmt);         // Only when things go wrong
      return ;
   }
  sqlite3_reset(stmt);

} // // Get rid of the memory allocated for stmt; mandatory! sqlite3_finalize(stmt);
sqlite3_close(db); return 0; }

The output is: Database the_insert.db is open. Error executing prepare statement:

What is wrong with my code? What can there be wrong with my use of sqlite3_prepare_v2()?

Well... the preview doesn't look very readable; what can I do?

(2) By David Raymond (dvdraymond) on 2021-02-25 12:37:06 in reply to 1 [link] [source]

Well... the preview doesn't look very readable; what can I do?

At the top of the text box there's a drop down for what style to use. You can always change it to Plain Text to get rid of any fancy behavior.

If you click on the words "Markup style:" there, and then on "Markdown Formatting Rules" you get a guide of all the Markdown stuff. There's a section on "Literal/Verbatim Text - Code Blocks"

The most basic way to mark a section as without style is to use backticks before and after. Triple backticks seems best when going around a big block.

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

int main(int argc, const char * argv[])
{
  sqlite3 *db;
  if (sqlite3_open("the_insert.db", &db))
  {
    printf("Could not open the_insert.db\n");
    exit(-1);
  }
  printf("Database %s is open\n", "the_insert.db");
  //
  // Prepare a statement for multiple use:
  // =====================================
  //
  const char *query = "INSERT INTO someTable (second, third) VALUES (?,?)";
  int sqlSize = (int)strlen(query) + 1;
  sqlite3_stmt *stmt;
  
  int err = sqlite3_prepare_v2(db, query, sqlSize, &stmt, NULL);
  printf("err: %d\n", err);
  
  if (sqlite3_prepare_v2(db, query, sqlSize, &stmt, NULL))
  {
    printf("Error executing prepare statement: \n");  //, sqlite3_errstr());
    sqlite3_close(db);
    exit(-1);
  }

  printf("prepared\n"); // I NEVER GET THIS FAR!
  /*
   * The prepared statement executed multiple times
   * ==============================================
   */
  for (...) {
      sqlite3_bind_int (stmt, 1, ...);
      sqlite3_bind_text(stmt, 2, ...);

       if (sqlite3_step(stmt))
       {
          printf("execution failed\n");
          sqlite3_finalize(stmt);         // Only when things go wrong
          return ;
       }
      sqlite3_reset(stmt);
  } 
  //
  // Get rid of the memory allocated for stmt; mandatory!
  sqlite3_finalize(stmt);   
  sqlite3_close(db);  return 0;
}

(3) By Keith Medcalf (kmedcalf) on 2021-02-25 12:50:46 in reply to 1 [link] [source]

What is the error code & error message? Do you think that might provide a clue?

(4) By anonymous on 2021-02-25 13:20:16 in reply to 3 [link] [source]

Keith, thanks for the reply. There seems no error code, nor error message. When I use sqlite3_errmsg(db) the table name appears, which is obviously not the cause.

(5.2) By Keith Medcalf (kmedcalf) on 2021-02-25 13:57:04 edited from 5.1 in reply to 4 [link] [source]

sqlite3_prepare* calls return an error code telling you what is wrong. More detail is available if you ask for it.

You can print out the error message after the function returns an error indication by querying the connection and printing the result:

printf("%s: %s\n", sqlite3_errstr(sqlite3_extended_errcode(db)), sqlite3_errmsg(db));

(6) By anonymous on 2021-02-25 20:12:56 in reply to 5.2 [link] [source]

The message returned is: "SQL logic error: no such table: someTable".
I had that message before when I used only: sqlite3_errmsg(db).
But this is no help at all because I can asure that this table is in place.
Trouble is; I can't put a picture here to prove it.
There can of course be a "SQL logic error", but if you see it, tell me please.

(7) By Larry Brasfield (larrybr) on 2021-02-25 20:49:30 in reply to 6 [link] [source]

You should pass SQLITE_OPEN_READWRITE as the 3rd argument to sqlite3_open_v2(). The problem is that the older sqlite3_open() API auto-creates a database, but it begins life as an empty vessel.

But this is no help at all because I can asure that this table is in place.

Doubtful. However, make your case that the said table truly is in the database opened or auto-created with your sqlite3_open() call. I can assure you that there are one or more holes in that argument.

Is the current directory for your program's process the same as the directory in which "the_insert.db" resides? And is that the very same database that you are sure has a table named "someTable"?

(8) By anonymous on 2021-02-26 05:04:31 in reply to 7 [link] [source]

Well I activated the code that uses the SQLite prepare_v2 API function. All works fine except that this introduced to new memory leaks.

To avoid a discussion here is the destructor:
    ~DBase()
    {
        sqlite3_free(prepared_SQL_stmt);
        sqlite3_free(ErrMessage);
        sqlite3_close(DB);
    }

This may provoke the ire of the SQLite team:
Executing sqlite3_free() calls without a sqlite3_malloc() is weird.
But I will do (almost) everything to deal with the memory leaks. All the leaks are related to the libsqlite3.so.0.8.6 (SQLite v3.30) and the address 0x483C7F3 (this is a dynamic address of this shared library) that executes a malloc().

(9) By anonymous on 2021-02-26 12:09:28 in reply to 7 [link] [source]

@ Larry Brasfield,
This remark: "Is the current directory for your program's process the same as the directory in which "the_insert.db" resides? And is that the very same database that you are sure has a table named "someTable"?" made me thinking; and looking in the directory where Xcode used to put the executable.

Guess what!

1. Database and path created by Xcode:
/Users/janhkila/Library/Developer/Xcode/DerivedData/SQLite_Multiple_Insert_00-dksaovkjvqrrrzdodwcmnprrbxey/Build/Products/Debug/the_insert.db

2. Database and path created by me:
/Users/janhkila/SQL-Lite/the_insert.db

So, there are two identical databases "the_inser.db" both with a table "someTable", only in different directories.
Thank you very match Larry! And off course mr. Apple/Xcode.  Well not really.

(10) By anonymous on 2021-02-26 12:29:03 in reply to 7 [source]

All is well now!

The second path as mentioned before is correct and expected because the program and the database are in the same directory.

To be able to get it to work, I have to use: "sqlite3_open_v2(path, &db, SQLITE_OPEN_READWRITE, NULL)", to open the database.

Thanks all. Up to the next hurdle on this rather bumpy road.