Android Question Number of affected rows after insert/update using wait for

AscySoft

Active Member
Licensed User
Hello
On the new sugestions of adding/updating rows in a database/table i follow your code from here https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/

B4X:
For i = 1 To ...
   sql.AddNonQueryToBatch("INSERT INTO table1 VALUES ..."
Next
Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log("NonQuery: " & Success)
I have two questions:
1) IS there a possibility to return number of affected rows?

In my previouse code I did this with a globabl variable that was counting all the changes in the table1
B4X:
SQL1.BeginTransaction
Try
   for i = 1 to ...
  SQL1.ExecNonQuery ("INSERT INTO table1 VALUES ...")
   var = var + SQL1.ExecQuerySingleResult($"SELECT changes() FROM ${table1} LIMIT 1"$)
   next
SQL1.TransactionSuccessful
Catch
'the transaction will be cancelled
End Try
SQL1.EndTransaction
log("Affected rows: " & var)
2) if SQL_NonQueryComplete (Success As Boolean), the parameter for success is false, does this mean that no insert /update was commited to the database/table? Is this method (AddNonQueryToBatch) using some internal BeginTransaction/EndTransaction?
 

RB Smissaert

Well-Known Member
Licensed User
Hello
On the new sugestions of adding/updating rows in a database/table i follow your code from here https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/

B4X:
For i = 1 To ...
   sql.AddNonQueryToBatch("INSERT INTO table1 VALUES ..."
Next
Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log("NonQuery: " & Success)
I have two questions:
1) IS there a possibility to return number of affected rows?

In my previouse code I did this with a globabl variable that was counting all the changes in the table1
B4X:
SQL1.BeginTransaction
Try
   for i = 1 to ...
  SQL1.ExecNonQuery ("INSERT INTO table1 VALUES ...")
   var = var + SQL1.ExecQuerySingleResult($"SELECT changes() FROM ${table1} LIMIT 1"$)
   next
SQL1.TransactionSuccessful
Catch
'the transaction will be cancelled
End Try
SQL1.EndTransaction
log("Affected rows: " & var)
2) if SQL_NonQueryComplete (Success As Boolean), the parameter for success is false, does this mean that no insert /update was commited to the database/table? Is this method (AddNonQueryToBatch) using some internal BeginTransaction/EndTransaction?
> 1) IS there a possibility to return number of affected rows?

B4X:
Sub GetNumberOfChanges() As Long
 Dim strSQL As String
 Dim lRows As String
 strSQL = "SELECT changes()"
 lRows =  SQL1.ExecQuerySingleResult(strSQL)
 Return lRows
End Sub
RBS
 

RB Smissaert

Well-Known Member
Licensed User
This is exactly what I wanted, I guess that this function needs to be called after the
B4X:
Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log("NonQuery: " & Success)
I will try that!

What about question no 2?
I am using this for simple non-select queries, eg:

update table1 set field1 = 'b' where field1 = 'a'

It will tell you how many rows were updated.

Not sure what the result will be if you use it after multiple queries in a loop in a transaction. It may give the
changes produced by the last query in the loop. Need to do some testing.

Not sure about 2), never have used SQL_NonQueryComplete yet.


RBS
 
Top