Getting the number of rows affected

Show & Tell
#1

In lots of places you want to know how many rows were affected by a SQL statement

Many DB’s have a way to do this but its not exposed by Xojo so you have to do it for yourself

mySQL : after a query do select row_count()
MS Sql : : after a query do select @@rowcount
PostgreSQL : after a query do GET DIAGNOSTICS v_RowCountInt = ROW_COUNT
SQLite : after a query do select changes()

2 Likes
#2

In SQLite you should be able to get this using PRAGMA count_changes
however it is deprecated, and replaced by an SQLite function that probably needs a declare to expose it to Xojo

#3

Thanks for pointing out I had missed sqlite

the Changes function should do it

changes()

The changes() function returns the number of database rows that were changed or inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers. The changes() SQL function is a wrapper around the sqlite3_changes() C/C++ function and hence follows the same rules for counting changes.

Added it to the note above

#4

the Pragama is deprecated, but sqlite3_changes() is not

#5

right
select changes()
is a nice simple sql function to that API call so no need for a declare