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

[AscySoft]

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/

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

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]

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/

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

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?

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

RBS

 

[AscySoft]

SQL1.ExecQuerySingleResult("SELECT changes()")

This is exactly what I wanted, I guess that this function needs to be called after the

Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log("NonQuery: " & Success)

I will try that!

What about question no 2?

 

[RB Smissaert]

This is exactly what I wanted, I guess that this function needs to be called after the

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

 

[Erel]

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?

Yes and yes.