ExecNonQuery

stefanobusetto · May 15, 2012

if you execute an update
is there any way to know how many rows has been updated?
thanks

Mahares · May 15, 2012

When you did an UPDATE, you probably had a WHERE clause in your SQL statement. Use part of it to find the number of updated records. Perhaps you are thinking of a better way than the below :

B4X:

dim SQL1 as SQL 'in process global

Dim txt as string  'globals
Dim UpdatedRecos as Int   'globals

txt="UPDATE MyTable SET City= 'Rome' WHERE Country='Italy'"
SQL1.ExecNonQuery(txt)

txt="SELECT count(*) FROM myTable WHERE City= 'Rome' AND Country= 'Italy'"
UpdatedRecs=SQL1.ExecQuerySingleResult(txt)
msgbox(UpdatedRecs,"Number of updated records.")

stefanobusetto · May 15, 2012

so you know there is a record with the given key
but you are not sure you updated it

Mahares · May 15, 2012

I am not sure if there is a better way, unless you make a copy of the original table and compare it to the updated one to know for sure. That is too much work though. I am cutious too. If anyone can help this biggie, it is probably someone like Klaus.

stefanobusetto · May 15, 2012

the best solution i can think of is
ExecNonQuery returns the number of rows affected
by the sql statement
thanks anyway

Mahares · May 15, 2012

I did not think ExecNonQuery returns any rows in a SQLite table, since the UPDATE is an action query. How would you then display the number of rows using ExecNonQuery?

stefanobusetto · May 15, 2012

Not the rows but how many rows have been affected

Mahares · May 15, 2012

If you can show the code that can do it, that is great. If not, perhaps someone else can point me to the proper place, where can get some guidance. If you are referring to a rowcount, I have only seen it in reference to a CURSOR, like in Cursor1.rowcount

mc73 · May 15, 2012

You can add a field named say 'updated'. Then put its value to 1. After updating, check the records' count with updated=1. When finished with the query, set updated to 0.

eps · May 16, 2012

Do a select count query before the update...

Mahares · May 16, 2012

@mc73: That is clever. However, you are talking about altering the table by adding a new field(column). If having to calculate the number of updated records is a frequent task. That would be the way to do it. Below is how I see the code then:

B4X:

Dim SQL1 as SQL 'in process global
Dim txt as string  'globals
Dim UpdatedRecos as Int   'globals

txt="UPDATE MyTable SET City= 'Rome', MyUpdate =1  WHERE Country='Italy'"
SQL1.ExecNonQuery(txt)

txt="SELECT count(*) FROM myTable WHERE MyUpdate = 1" 
UpdatedRecs=SQL1.ExecQuerySingleResult(txt)
Msgbox(UpdatedRecs,"Number of updated records.")

txt="UPDATE MyTable SET MyUpdate =0  WHERE MyUpdate = 1" 
SQL1.ExecNonQuery(txt)

eps · May 16, 2012

No need for "myupdate" column, and not sure why you're assigning the sql to txt, just exec it.

Just do a select count query, which has matching where clauses to the update, then do the update.

stefanobusetto · May 16, 2012

the idea is good
but in a multi user environment if you want to be sure
you have updated the record you must at least add a couple
of fields last_user and last_datetime
something like a timestamp
i did it for same applications
but it is a little bit too complex
thanks anyway

eps · May 16, 2012

stefanobusetto said:
the idea is good
but in a multi user environment if you want to be sure
you have updated the record you must at least add a couple
of fields last_user and last_datetime
something like a timestamp
i did it for same applications
but it is a little bit too complex
thanks anyway

I wasn't aware this was a multi user environment... It's the first time you've mentioned this. If you ask the wrong question or don't give enough information you'll invariably get the wrong answer!

Ricky D · May 16, 2012

ExecNonQuery returns an int - the number of rows affected by the INSERT, UPDATE & DELETE commands

B4X:

Dim updated As Int
updated = ExecNonQuery("UPDATE Table SET field1=value1 WHERE Field2=value2")

klaus · May 16, 2012

ExecNonQuery returns an int - the number of rows affected by the INSERT, UPDATE & DELETE commands

No, ExecNonQuery returns void.

But you can use the changes() query below:

B4X:

Dim changes As Int
changes = SQL1.ExecQuerySingleResult("SELECT changes() FROM " & DBTableName)

Best regards.

Mahares · May 16, 2012

Klaus: You are an amazing machine. I was wondering when you are going to get on this. I tested it and it works. And above all , it is a very simple solution. Although I am not the original issuer of this thread, I have benefited from it immensely due to the willingness of the forum users to offer candid and different points of view.

klaus · May 16, 2012

I was wondering when you are going to get on this.

Not really complicated.
- Ricky's answer looked wrong to me
- I looked at the SQL library documentation, and saw returns void
- Tested it in one of my programs, confirmed my thought
- I looked at the SQLite documentation, found the changes() function
- Tested it in my program to prove that it works
- Posted the answer.

That's it.

Best regards.

Erel · May 16, 2012

I looked at the SQLite documentation, found the changes() function

Nice one. I wasn't familiar with this keyword.

NJDude · May 16, 2012

For future reference, a list of SQLite Functions and some very informative website about SQLite

ExecNonQuery

Active Member

Expert

Active Member

Expert

Active Member

Expert

Active Member

Expert

Well-Known Member

Expert

Expert

Expert

Active Member

Expert

Well-Known Member

Expert

Expert

Expert

B4X founder

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency