Solved Sqlite: Number of affected rows?

I see that the connection object exposes the LastRowID, but is there a way to get the number of changed rows for a query? such as mentioned in Count The Number Of Rows Modified

 

You can pass a callback to ExecuteNonQuery which is of type Action<int> and contains the amount of affected rows.

See:
Oxide/SQLite.cs at master · OxideMod/Oxide · GitHub
Oxide/SQLite.cs at master · OxideMod/Oxide · GitHub
Oxide/SQLite.cs at master · OxideMod/Oxide · GitHub
System.Data.SQLite: Artifact Content

Also, the SQLite/MySQL docs on here really need maintenance. Maybe I'll submit a pull request later for some reworks.

 

Yeah but that requires a callback, i'm trying to condense my queries to reduce the number of nested callbacks that I use to manipulate my data.

As a result i'm currently using an INSERT OR IGNORE query to add my users, so i don't have to do the whole "SELECT WHERE..." to check if the record already exist.

But i've been trying to figure out a way to return in this very same query whether the insert command was actually executed.

my current candidate is:

Code:

INSERT OR IGNORE INTO players (steam_id,parent_id)  VALUES (@0,@1);
SELECT
SUM(CASE WHEN steam_id = @0 AND parent_id = @1 THEN 1 ELSE 0 END) AS existing,
SUM(CASE WHEN parent_id = @0 AND `banned` IS NOT NULL THEN 1 ELSE 0 END) AS strikecount

Which inserts the query and counts how many rows have the exact same arguments, and also returns the strike count for this user.
The problem is that "existing" will be zero only if the parent_id doesn't match. So it's not a reliable way to tell the user "this user already exists"
[DOUBLEPOST=1462476520][/DOUBLEPOST]Aha! i think i've got it so i'll add it to my reply so other people can benefit from it.

Code:

INSERT OR IGNORE INTO players (steam_id,parent_id) VALUES(@0,@1);
SELECT
changes() as added,
SUM(CASE WHEN parent_id = @1 AND `banned` IS NOT NULL THEN 1 ELSE 0 END) AS strikecount
FROM `players`

This one inserts a new user if it doesn't already exist, and then returns if it did insert an entry (added) and the strikecount.
So 3 actions in one query with two separate data returns.

 

Personally, I'd take a couple of ugly callbacks over ugly mixed queries any time of the day.

 

Well as long as the data is handled safely, i'd say, the SQL language exist to be used.

 

You still shouldn't mix Queries and NonQueries. It's possible, but throwing two things together which should be executed seperately can be confusing to the reader.
Just personal preference, I guess.

 

Doesn't it already show how many rows are affected? At least when I execute it in Mysql it already returns the amount of rows affected.

 

on on Sqlite tho, unless i am completely retarded and missed something obvious? Where do you get the affect row count on Mysql Troubled?

 

Pretty sure he means the Action<int> callback I already mentioned.

 

Get affected rows on ExecuteNonQuery

 

Keep in mind that we're using an Oxide wrapper on top of C#'s sqlite library.
The Oxide wrapper is sort of concurrent (Basically, a single handler executes all queries in its own thread and then schedules the callback on the main thread), so our Oxide wrapper of ExecuteNonQuery uses a callback instead of a return value.

 

Allright!

Sorry it took me a while to understand but after re-reading the Sqlite extension it became clear what you guys ment. I forgot that NonQuery has an optional callback.

 

Ah allright wasn't aware of that.