Updating table with prepared statement and bindings

[ckgoo...@gmail.com]

Hello.  This is almost certainly an SQLite3 question rather than Spatialite but I hope someone can give me some pointers.

I have my C++ program and I have lots of examples in my code where I use a prepared statement where the SQL contains ‘?’ place markers and I bind variables to them.  All works great when it is a SELECT statement.

Now I have an UPDATE statement.  The SQL works fine from the command line.  My C++ compiles fine.  The return codes from all the SQLite3 function calls return OK.

But my table isn’t updated with the new values.

I am executing my statement with the step function.

It is like the transaction isn’t being committed perhaps?

I would welcome any thoughts on how to get it to work.  Reading documentation and asking AI hasn’t helped so far.

Best, Chris

[a.fu...@lqt.it]

On Sun, 5 Jan 2025 09:07:15 -0000, ckgoo...@gmail.com wrote:
> Now I have an UPDATE statement. The SQL works fine from the command
> line. My C++ compiles fine. The return codes from all the SQLite3
> function calls return OK.
> But my table isn't updated with the new values.
> I am executing my statement with the step function.
>

Hallo Chris,

I personally use prepared statements every day for my software
developed in C, and I assure you that they work perfectly not
only for SELECTs but also for UPDATEs and DELETEs.

Just a tip; to check if the SQL statement that will be
executed is correct you can use the sqlite3_expanded_sql()
function as shown in following code snippet:

----------------
sqlite3_stmt *stmt; // to be initialized somewhere
char *sql;
int ret;

// binding
sqlite3_reset(stmt);
sqlite3_clear_bindings(stmt);
sqlite3_bind_int(stmt, 1, 10);
sqlite3_bind_null(stmt, 2);
sqlite3_bind_text(stmt, 3, "some string", -1, SQLITE_STATIC);

// printing the actual SQL statement that will be executed
sql = sqlite3_expanded_sql(stmt);
printf("%s\n", sql);
sqlite3_free(sql); // it's a dynamic allocation to be freed

// executing the SQL statement
ret = sqlite3_step(stmt_out);
----------------

this often helps to quickly discover that there is
some error, and where it lies.

> It is like the transaction isn't being committed perhaps?
>

SQLite3 is transactional; if you have opened a transaction
(by excuting a BEGIN) which is not then closed correctly
(by executing a COMMIT) all changes will be completely lost.
That's how it works.

best regards,
Sandro

[ckgoo...@gmail.com]

Hi Sandro,
Thanks for your tip of expanding the SQL. It was really useful and let me see where I'd gone wrong. I simply didn't match up my variables and question marks correctly. All works wonderfully now.
Many thanks, Chris

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/spatialite-users/488923d17da1f8a84b6ba322e2b1c8dd%40lqt.it.